MySQL SHOW GLOBAL STATUS
A friendly reminder: when checking your MySQL server’s status for certain status counters, be sure you know what you are looking at. Some counters are GLOBAL-only, but many of them are both SESSION and GLOBAL. Let me show an example of the difference.
If I want to get the number of temporary tables created on disk per second, I would look at the ‘Created_tmp_disk_tables’ status variables and divide that by the ‘Uptime’
mysql> SHOW STATUS LIKE '%uptime';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Uptime | 2981332 |
+---------------+---------+
1 row in set (0.04 sec)
SHOW STATUS LIKE '%tmp%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 984448 |
| Created_tmp_tables | 2 |
+-------------------------+--------+
3 rows in set (0.04 sec)
Wait, ‘Created_tmp_disk_tables’ is 0? Oh right, that’s my session status.
SHOW GLOBAL STATUS LIKE '%tmp%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Created_tmp_disk_tables | 8526619 |
| Created_tmp_files | 984498 |
| Created_tmp_tables | 48701810 |
+-------------------------+----------+
3 rows in set (0.04 sec)
That’s better. So don’t forget to check the correct STATUS for what you are looking for.
