Skip to content

# SQL: querying for status difference over time

MySQL INFORMATION_SCHEMA Monitoring SQL October 20, 2009

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
1
Leave a Reply

1 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
0 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
How to calculate a good InnoDB log file size – recap | code.openark.org

[…] SQL: querying for status difference over time […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.