INFORMATION_SCHEMA.GLOBAL_STATUS: watch out

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.