{"id":1194,"date":"2009-10-14T21:03:32","date_gmt":"2009-10-14T19:03:32","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1194"},"modified":"2009-10-14T21:49:32","modified_gmt":"2009-10-14T19:49:32","slug":"information_schema-global_status-watch-out","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/information_schema-global_status-watch-out","title":{"rendered":"INFORMATION_SCHEMA.GLOBAL_STATUS: watch out"},"content":{"rendered":"

MySQL 5.1 boasts some new and useful INFORMATION_SCHEMA<\/strong> tables. Among them is the GLOBAL_STATUS <\/strong>table.<\/p>\n

At last, it is possible to ask questions like:<\/p>\n

\n
node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_os_log_written';\r\n+-----------------------+----------------+\r\n| VARIABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | VARIABLE_VALUE |\r\n+-----------------------+----------------+\r\n| INNODB_OS_LOG_WRITTEN | 512\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------------+----------------+\r\n1 row in set (0.00 sec)\r\n\r\nnode1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'questions';\r\n+---------------+----------------+\r\n| VARIABLE_NAME | VARIABLE_VALUE |\r\n+---------------+----------------+\r\n| QUESTIONS\u00a0\u00a0\u00a0\u00a0 | 28\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------+----------------+\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n
Watch out #1<\/h4>\n
As with all INFORMATION_SCHEMA<\/strong> tables, to get a single row one needs to materialize the entire table. To ask the above two questions, the table will materialize twice. This means gathering all the information — twice. To get 20 values, we materialize the table 20 times. It not only takes time, but also increases some of the status variables themselves, like questions<\/strong>, select_scan<\/strong>, created_tmp_tables<\/strong>. Ironically, when we used SHOW GLOBAL STATUS<\/strong> and had to parse the results in our application code, we only issued the query once. But with the convenience of INFORMATION_SCHEMA<\/strong>, it’s much easier (and makes more sense!) to query per variable.<\/p>\n
Watch out #2<\/h4>\n
So if we’re to access a handful of status variables, and wish to only materialize the table once, what can we do? An easy solution is to create a MEMORY<\/strong> table which looks just like GLOBAL_STATUS<\/strong>, like this:<\/p>\n
\n
node1> CREATE TABLE memory_global_status LIKE INFORMATION_SCHEMA.GLOBAL_STATUS;\r\nQuery OK, 0 rows affected (0.00 sec)\r\nnode1> INSERT INTO memory_global_status SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;\r\nQuery OK, 291 rows affected (0.01 sec)\r\nRecords: 291\u00a0 Duplicates: 0\u00a0 Warnings: 0<\/pre>\n<\/blockquote>\n
We can now query the memory_global_status<\/strong> table, having ‘friezed’ the status, for as many times as we wish, with no real cost.<\/p>\n
But let’s take a look at:<\/p>\n
\n
node1> SHOW TABLE STATUS LIKE 'memory_global_status'\\G\r\n*************************** 1. row ***************************\r\n           Name: memory_global_status\r\n         Engine: MEMORY\r\n        Version: 10\r\n     Row_format: Fixed\r\n           Rows: 291\r\n Avg_row_length: 3268\r\n    Data_length: 1050624\r\nMax_data_length: 16755036\r\n   Index_length: 0\r\n      Data_free: 0\r\n Auto_increment: NULL\r\n    Create_time: NULL\r\n    Update_time: NULL\r\n     Check_time: NULL\r\n      Collation: utf8_general_ci\r\n       Checksum: NULL\r\n Create_options:\r\n        Comment:\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n
Ouch! How did we get Avg_row_length: 3268<\/strong>, and Data_length: 1050624<\/strong>? That’s quite more then we expected. Well, most of the values in GLOBAL_STATUS<\/strong> are just intgers. But some, just a few, are textual, and so the table definition is:<\/p>\n
\n
node1> SHOW CREATE TABLE INFORMATION_SCHEMA.GLOBAL_STATUS \\G\r\n*************************** 1. row ***************************\r\n       Table: GLOBAL_STATUS\r\nCreate Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (\r\n  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',\r\n  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL\r\n) ENGINE=MEMORY DEFAULT CHARSET=utf8\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n
A MEMORY<\/strong> tables works with FIXED row format, which means we need to allocate 64 utf8 characters for VARIABLE_NAME<\/strong>, plus 1024 utf8 characters for VARIABLE_VALUE<\/strong>. This makes for: (1+64*3) + 2+(1024*3) = 3267 (the missing byte is to indicate the NULLable values).<\/p>\n
I’m not sure why the table definition is as such. VARIABLE_NAME<\/strong> can be safely declared as ascii<\/strong>, and, as far as I can see, so can VARIABLE_VALUE<\/strong>. There are a few ON<\/strong>\/OFF<\/strong> values (I’ve expressed my opinion and concerns on these here<\/a> and here<\/a>; why not just use 0<\/strong>\/1<\/strong>?). SSL_CIPHER<\/strong> seems like the only variable which can get long enough to justify the 1024 characters.<\/p>\n
If you don’t mind about truncating those text values, or don’t mind about text values at all (we usually care about the counters), you can altogether disregard them when SELECTing from GLOBAL_STATUS<\/strong>. One can also add a HASH<\/strong> index on the VARIABLE_NAME<\/strong> parameter to avoid using full table scans upon reading each value.<\/p>\n
node1 [localhost] {msandbox} (test) > SHOW TABLE STATUS LIKE ‘memory_global_status’\\G
\n*************************** 1. row ***************************
\nName: memory_global_status
\nEngine: MEMORY
\nVersion: 10
\nRow_format: Fixed
\nRows: 291
\nAvg_row_length: 3268
\nData_length: 1050624
\nMax_data_length: 16755036
\nIndex_length: 0
\nData_free: 0
\nAuto_increment: NULL
\nCreate_time: NULL
\nUpdate_time: NULL
\nCheck_time: NULL
\nCollation: utf8_general_ci
\nChecksum: NULL
\nCreate_options:
\nComment:
\n1 row in set (0.00 sec)<\/div>\n","protected":false},"excerpt":{"rendered":"
MySQL 5.1 boasts some new and useful INFORMATION_SCHEMA tables. Among them is the GLOBAL_STATUS table. At last, it is possible to ask questions like: node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘innodb_os_log_written’; +———————–+—————-+ | VARIABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | VARIABLE_VALUE | +———————–+—————-+ | INNODB_OS_LOG_WRITTEN | 512\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | +———————–+—————-+ 1 row in set (0.00 sec) node1> SELECT * […]<\/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],"class_list":["post-1194","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-information_schema"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-jg","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1194","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=1194"}],"version-history":[{"count":15,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1194\/revisions"}],"predecessor-version":[{"id":1375,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1194\/revisions\/1375"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}