SQL: querying for status difference over time

The InnoDB plugin has a nice INFORMATION_SCHEMA concept: resetting tables. For example, the INNODB_CMP table lists information about compression operation. A similar table, INNODB_CMP_RESET, provides the same information, but resets the values. The latter can be used to measure, for example, number of compression operations over time.

I wish to present a SQL trick which does the same, without need for resetting tables. Suppose you have some status table, and you wish to measure the change in status per second, per minute etc. The trick is to query for the value twice in the same query, with some pause in between, and make the difference calculation.

For sake of simplicity, I’ll demonstrate using 5.1’s INFORMATION_SCHEMA.GLOBAL_STATUS. Please refer to INFORMATION_SCHEMA.GLOBAL_STATUS: watch out for some discussion on this.

In our example, we wish to measure the number of questions per second. Getting the number of questions is done with:

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'questions';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| QUESTIONS     | 3619           |
+---------------+----------------+
1 row in set (0.00 sec)

Applying the trick, thus solving the problem:

SELECT SUM(value) AS questions_per_sec FROM (
  SELECT -VARIABLE_VALUE AS value
    FROM INFORMATION_SCHEMA.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'questions'
  UNION ALL
  SELECT SLEEP(1)
    FROM DUAL
  UNION ALL
  SELECT VARIABLE_VALUE
    FROM INFORMATION_SCHEMA.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'questions'
) s1;
+-------------------+
| questions_per_sec |
+-------------------+
|               126 |
+-------------------+
1 row in set (1.01 sec)

Make a one minute measurement with SLEEP(60), then divide SUM by 60.

Note on transactional tables

The above trick will not work when reading values from transactional tables, and with isolation level >= REPEATABLE-READ, since, by definition, you must get the same value back while in the same transaction. So this works on MyISAM, MEMORY, functions and otherwise non transactional data sources.