INFORMATION_SCHEMA.GLOBAL_STATUS: watch out

October 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)

tags:
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

6 Comments to "INFORMATION_SCHEMA.GLOBAL_STATUS: watch out"

  1. Roland Bouman wrote:

    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

  2. shlomi wrote:

    Ronald,

    Great!
    Thanks

  3. Gavin Towey wrote:

    what's wrong with IN ?

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

  4. shlomi wrote:

    @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

  5. Sheeri wrote:

    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 )

  6. shlomi wrote:

    Sheeri,

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

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org