Skip to content

# INFORMATION_SCHEMA.GLOBAL_STATUS: watch out

MySQL INFORMATION_SCHEMA October 14, 2009October 14, 2009

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         | VARIABLE_VALUE |
>     +-----------------------+----------------+
>     | INNODB_OS_LOG_WRITTEN | 512            |
>     +-----------------------+----------------+
>     1 row in set (0.00 sec)

>     node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'questions';
>     +---------------+----------------+
>     | VARIABLE_NAME | VARIABLE_VALUE |
>     +---------------+----------------+
>     | QUESTIONS     | 28             |
>     +---------------+----------------+
>     1 row in set (0.00 sec)

#### Watch out #1

As with all INFORMATION_SCHEMA 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, select_scan, created_tmp_tables. Ironically, when we used SHOW GLOBAL STATUS and had to parse the results in our application code, we only issued the query once. But with the convenience of INFORMATION_SCHEMA, it’s much easier (and makes more sense!) to query per variable.

#### Watch out #2

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 table which looks just like GLOBAL_STATUS, like this:

>     node1> CREATE TABLE memory_global_status LIKE INFORMATION_SCHEMA.GLOBAL_STATUS;
>     Query OK, 0 rows affected (0.00 sec)
>     node1> INSERT INTO memory_global_status SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;
>     Query OK, 291 rows affected (0.01 sec)
>     Records: 291  Duplicates: 0  Warnings: 0

We can now query the memory_global_status table, having ‘friezed’ the status, for as many times as we wish, with no real cost.

But let’s take a look at:

>     node1> SHOW TABLE STATUS LIKE 'memory_global_status'\G
>     *************************** 1. row ***************************
>                Name: memory_global_status
>              Engine: MEMORY
>             Version: 10
>          Row_format: Fixed
>                Rows: 291
>      Avg_row_length: 3268
>         Data_length: 1050624
>     Max_data_length: 16755036
>        Index_length: 0
>           Data_free: 0
>      Auto_increment: NULL
>         Create_time: NULL
>         Update_time: NULL
>          Check_time: NULL
>           Collation: utf8_general_ci
>            Checksum: NULL
>      Create_options:
>             Comment:
>     1 row in set (0.00 sec)

Ouch! How did we get Avg_row_length: 3268, and Data_length: 1050624? That’s quite more then we expected. Well, most of the values in GLOBAL_STATUS are just intgers. But some, just a few, are textual, and so the table definition is:

>     node1> SHOW CREATE TABLE INFORMATION_SCHEMA.GLOBAL_STATUS \G
>     *************************** 1. row ***************************
>            Table: GLOBAL_STATUS
>     Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
>       `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
>       `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
>     ) ENGINE=MEMORY DEFAULT CHARSET=utf8
>     1 row in set (0.00 sec)

A MEMORY tables works with FIXED row format, which means we need to allocate 64 utf8 characters for VARIABLE_NAME, plus 1024 utf8 characters for VARIABLE_VALUE. This makes for: (1+64*3) + 2+(1024*3) = 3267 (the missing byte is to indicate the NULLable values).

I’m not sure why the table definition is as such. VARIABLE_NAME can be safely declared as ascii, and, as far as I can see, so can VARIABLE_VALUE. There are a few ON/OFF values (I’ve expressed my opinion and concerns on these here and here; why not just use 0/1?). SSL_CIPHER seems like the only variable which can get long enough to justify the 1024 characters.

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. One can also add a HASH index on the VARIABLE_NAME parameter to avoid using full table scans upon reading each value.

node1 [localhost] {msandbox} (test) > SHOW TABLE STATUS LIKE ‘memory_global_status’\G
*************************** 1. row ***************************
Name: memory_global_status
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 291
Avg_row_length: 3268
Data_length: 1050624
Max_data_length: 16755036
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

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

###
6
Leave a Reply

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

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

Subscribe
Notify of
Guest
Roland Bouman

Hi Shlomi,

there is an easy way around it without the memory table. Just use something like

select MAX(IF(variable_name = ‘INNODB_OS_LOG_WRITTEN’, VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN
, MAX(IF(variable_name = ‘QUESTIONS’, VARIABLE_VALUE, NULL)) AS QUESTIONS
from information_schema.GLOBAL_STATUS

Author
shlomi

Ronald,

Great!
Thanks

Guest
Gavin Towey

what’s wrong with IN ?

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME IN (‘innodb_os_log_written’, ‘questions’);

Author
shlomi

@Gavin,

Heh, on first reading your comment I thought to myself I should delete this post altogether. But I then realized the flaw is with the examples I provided, which were misleading.

Using “IN” is just fine, but does not differ from reading the entire table. The problem is with iterating the table.

Look at Roland’s comment: he got my meaning: I wish to provide with properly named columns+values, not with VARIABLE_NAME, VARIABLE_VALUE.

So the flaw is that I did not provide the kind of result I really was expecting, hence the confusion. I apologize for not being clear.

Regards

Guest
Sheeri

Shlomi — you can use this information to create a MySQL Event that will get lots of information.

Or use the mysar tool to do something similar. (https://launchpad.net/mysar
and
http://www.pythian.com/news/4230/mysar-a-sar-like-utility-for-mysql )

Author
shlomi

Sheeri,

Whoa, I was in the midst of developing a similar tool. I was unaware of mysar!
Thanks,
Shlomi

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.