用一条语句查看一段时间内status变量的变化

最新推荐文章于 2021-07-15 16:44:40 发布

原创最新推荐文章于 2021-07-15 16:44:40 发布 · 203 阅读

CC 4.0 BY-SA版权

本文介绍了一种SQL技巧，通过一次查询中两次获取状态表数据并计算差值来测量状态变化速率，例如每秒或每分钟的变化数。此方法适用于非事务性表，如MyISAM或MEMORY表。

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.