The confusion over global and session status

I was trying to demonstrate to a client how to monitor queries that generate internal temporary tables. With an EXPLAIN plan you see ‘Creating temporary’. Within MySQL you can use the SHOW STATUS to look at queries that create temporary tables.

There is the issue that the act of monitoring impacts the results, SHOW STATUS actually creates a temporary table. You can see in this example.

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 155   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 156   |
+-------------------------+-------+
3 rows in set (0.00 sec)

What has perplexed me in the past, and I can’t explain is that SHOW SESSION STATUS for this example does not increment. It’s confusing to tell a client to use SHOW SESSION STATUS for SQL statements, but the behavior is different with SHOW GLOBAL STATUS. For example, no increment.

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Let’s look at a query that creates a temporary table.

mysql> explain select t1.* from t1,t2 where t1.c1 = t2.c2 order by t2.c2, t1.c1;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer  |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.03 sec)

If we use session status we get an increment of 1.

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select SQL_NO_CACHE t1.* from t1,t2 where t1.c1 = t2.c2 order by t2.c2, t1.c1;
Empty set (0.00 sec)

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
3 rows in set (0.00 sec)

If we use global status, in this case it’s and idle server so I know there is no other activity, however in a real world situation that isn’t possible.

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 171   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select SQL_NO_CACHE t1.* from t1,t2 where t1.c1 = t2.c2 order by t2.c2, t1.c1;
Empty set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 173   |
+-------------------------+-------+
3 rows in set (0.00 sec)