DROP TABLE ld_metric;

CREATE TABLE ld_metric 
  (
    lm_id               INT  PRIMARY KEY,
    lm_dt               DATETIME,
    lm_first_id         INT,
    lm_secs_since_start INT,
    lm_n_rows           BIGINT,
    lm_cpu              INT,
    lm_io_stat          ANY,
    lm_read_time        BIGINT,
    lm_read_pct         FLOAT,
    lm_rows_per_s       FLOAT,
    lm_cpu_pct          FLOAT,
    lm_rusage           ANY
  );

CREATE INDEX lm_dt 
          ON ld_metric (lm_dt);

CREATE PROCEDURE io_stat ()
  {
    RETURN VECTOR ( sys_stat ('disk_reads'), 
                    sys_stat ('ra_count'), 
                    sys_stat ('ra_pages'), 
                    sys_stat ('tc_read_aside'), 
                    sys_stat ('tc_unused_read_aside'), 
                    sys_stat ('tc_merge_reads'), 
                    sys_stat ('tc_merge_read_pages')
                  );
  }

-- getrusage returns:
--   0. user cpu msec 
--   1. sys cpu msec 
--   2. max resident set 
--   3. min flt
--   4. maj flt
--   5. n swap 
--   6. blocking input
--   7. blocking output

CREATE PROCEDURE ld_sample (IN is_first INT := 0)
  {
    DECLARE  ru               ANY;

    DECLARE  now              DATETIME;

    DECLARE  id, 
             n_rows, 
             read_time, 
             last_read_time, 
             elapsed          INT;

    id := sequence_next ('lm');

    now := curdatetime ();

    n_rows := 
      (
        SELECT cl_sys_stat (key_table, name_part (key_name, 2), 'touches') 
          FROM sys_keys 
         WHERE key_name = 'DB.DBA.RDF_QUAD'
      );

    ru := getrusage ();

    IF (is_first)
      {
        INSERT 
          INTO ld_metric ( lm_id, 
                           lm_dt, 
                           lm_first_id, 
                           lm_cpu, 
                           lm_read_time, 
                           lm_n_rows, 
                           lm_rusage, 
                           lm_secs_since_start, 
                           lm_io_stat
                         ) 
          VALUES ( id, 
                   now, 
                   id, 
                   ru[0] + ru[1], 
                   sys_stat ('read_cum_time'), 
                   n_rows, 
                   ru, 
                   0, 
                   io_stat()
                 );
      }
    ELSE
      {
        DECLARE  last_dt, 
                 start_dt    DATETIME;

        DECLARE  first_id, 
                 last_rows, 
                 last_cpu,
                 last_read   INT;

        SELECT     lm_id,     lm_dt  
          INTO     first_id,  start_dt 
          FROM     ld_metric 
          WHERE    lm_dt < now 
                   AND lm_first_id = lm_id 
          ORDER BY lm_dt DESC;

        SELECT     lm_dt,   lm_n_rows,  lm_cpu,    lm_read_time 
          INTO     last_dt, last_rows,  last_cpu,  last_read  
          FROM     ld_metric 
          WHERE    lm_dt < now 
          ORDER BY lm_dt DESC;

      INSERT 
        INTO ld_metric ( lm_id, 
                         lm_dt, 
                         lm_first_id, 
                         lm_cpu, 
                         lm_read_time, 
                         lm_n_rows, 
                         lm_rusage, 
                         lm_secs_since_start, 
                         lm_io_stat) 
                VALUES ( id, 
                         now, 
                         first_id, 
                         ru[0] + ru[1], 
                         sys_stat ('read_cum_time'), 
                         n_rows, 
                         ru, 
                         datediff ('second', start_dt, now), 
                         io_stat ()
                       );

    elapsed := datediff ('second', last_dt, now);

      UPDATE ld_metric 
         SET lm_read_pct   = (sys_stat ('read_cum_time') - last_read) / 10 / (0.0001 + elapsed),
             lm_cpu_pct    = (((ru[0] + ru[1]) - last_cpu) / 10) / (0.001 + elapsed),
             lm_rows_per_s = (n_rows - last_rows) / (0.001 + elapsed)
       WHERE lm_id = id;
    }

  COMMIT WORK;

  }


CREATE PROCEDURE ld_meter_run (IN s_delay INT)
  {
    ld_sample (1);
    WHILE (1)
      {
        DELAY (s_delay);
        EXEC ('ld_sample (0)', NULL, NULL, NULL);
      }
  }


-- Query for read rate in MB/s
-- ---------------------------
-- SELECT (io1 - io0) / 128.0 / datediff ('second', dt0, dt1) 
--   FROM ( SELECT a.lm_io_stat[0] AS io1, 
--                 b.lm_io_stat[0] AS io0, 
--                         a.lm_dt AS dt1, 
--                         b.lm_dt AS dt0 
--            FROM ld_metric AS a, 
--                 ld_metric AS b 
--           WHERE b.lm_id = a.lm_id - 1
--        ) AS f;