{"id":945,"date":"2009-10-20T11:31:43","date_gmt":"2009-10-20T09:31:43","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=945"},"modified":"2009-10-20T11:31:43","modified_gmt":"2009-10-20T09:31:43","slug":"sql-querying-for-status-difference-over-time","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-querying-for-status-difference-over-time","title":{"rendered":"SQL: querying for status difference over time"},"content":{"rendered":"

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

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.<\/p>\n

For sake of simplicity, I’ll demonstrate using 5.1’s INFORMATION_SCHEMA.GLOBAL_STATUS<\/strong>. Please refer to INFORMATION_SCHEMA.GLOBAL_STATUS: watch out<\/a> for some discussion on this.<\/p>\n

In our example, we wish to measure the number of questions per second. Getting the number of questions is done with:<\/p>\n

\n
SELECT<\/strong> * FROM<\/strong> INFORMATION_SCHEMA.GLOBAL_STATUS WHERE<\/strong> VARIABLE_NAME = 'questions';\r\n+---------------+----------------+\r\n| VARIABLE_NAME | VARIABLE_VALUE |\r\n+---------------+----------------+\r\n| QUESTIONS\u00a0\u00a0\u00a0\u00a0 | 3619\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------+----------------+\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n
Applying the trick, thus solving the problem:<\/p>\n
\n
SELECT<\/strong> SUM<\/strong>(value) AS<\/strong> questions_per_sec FROM<\/strong> (\r\n  SELECT<\/strong> -VARIABLE_VALUE AS<\/strong> value\r\n    FROM<\/strong> INFORMATION_SCHEMA.GLOBAL_STATUS\r\n    WHERE<\/strong> VARIABLE_NAME = 'questions'\r\n  UNION<\/strong> ALL<\/strong>\r\n  SELECT<\/strong> SLEEP(1)\r\n    FROM<\/strong> DUAL\r\n  UNION<\/strong> ALL<\/strong>\r\n  SELECT<\/strong> VARIABLE_VALUE\r\n    FROM<\/strong> INFORMATION_SCHEMA.GLOBAL_STATUS\r\n    WHERE<\/strong> VARIABLE_NAME = 'questions'\r\n) s1;\r\n+-------------------+\r\n| questions_per_sec |\r\n+-------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 126 |\r\n+-------------------+\r\n1 row in set (1.01 sec)<\/pre>\n<\/blockquote>\n
Make a one minute measurement with SLEEP(60)<\/strong>, then divide SUM<\/strong> by 60.<\/p>\n
Note on transactional tables<\/h4>\n
The above trick will not work when reading values from transactional tables, and with isolation level >= REPEATABLE-READ<\/strong>, 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[24,46,21],"class_list":["post-945","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-information_schema","tag-monitoring","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-ff","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=945"}],"version-history":[{"count":31,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/945\/revisions"}],"predecessor-version":[{"id":1422,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/945\/revisions\/1422"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}