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

cenalulu

                       
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.
                       
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/90603/showart_2083978.html