You are here

Home » Blogs » Shinguz's blog

Temporary tables and MySQL STATUS information

Fri, 2016-07-08 18:42 — Shinguz

When analysing MySQL configuration and status information at customers it is always interesting to see how the applications behave. This can partially be seen by the output of the SHOW GLOBAL STATUS command. See also Reading MySQL fingerprints.

Today we wanted to know where the high Com_create_table and the twice as high Com_drop_table is coming from. One suspect was TEMPORARY TABLES. But are real temporary tables counted as Com_create_table and Com_drop_table at all? This is what we want to find out today. The tested MySQL version is 5.7.11.

Caution: Different MySQL or MariaDB versions might behave differently!

Session 1 Global Session 2
CREATE TABLE t1 (id INT);
Query OK, 0 rows affected		    
Com_create_table +1
Opened_table_definitions +1		 Com_create_table +1
Opened_table_definitions +1		  
 
CREATE TABLE t1 (id INT);
ERROR 1050 (42S01): Table 't1' already exists		    
Com_create_table +1
Open_table_definitions +1
Open_tables +1
Opened_table_definitions +1
Opened_tables +1		 Com_create_table + 1
Open_table_definitions +1
Open_tables +1
Opened_table_definitions +1
Opened_tables +1		  
 
CREATE TABLE t1 (id INT);
ERROR 1050 (42S01): Table 't1' already exists		    
Com_create_table + 1 Com_create_table + 1  
 
DROP TABLE t1;
Query OK, 0 rows affected		    
Com_drop_table +1
Open_table_definitions -1
Open_tables -1		 Com_drop_table +1
Open_table_definitions -1
Open_tables -1		  
 
DROP TABLE t1;
ERROR 1051 (42S02): Unknown table 'test.t1'		    
Com_drop_table -1 Com_drop_table -1  
 
CREATE TEMPORARY TABLE ttemp (id INT);
Query OK, 0 rows affected		    
Com_create_table +1
Opened_table_definitions +2
Opened_tables +1		 Com_create_table +1
Opened_table_definitions +2
Opened_tables +1		  
 
CREATE TEMPORARY TABLE ttemp (id INT);
ERROR 1050 (42S01): Table 'ttemp' already exists		    
Com_create_table +1 Com_create_table +1  
 
DROP TABLE ttemp;
Query OK, 0 rows affected		    
Com_drop_table +1 Com_drop_table +1  
 
CREATE TEMPORARY TABLE ttemp (id int);
Query OK, 0 rows affected		   CREATE TEMPORARY TABLE ttemp (id int);
Query OK, 0 rows affected
Com_create_table +1
Opened_table_definitions +2
Opened_tables +1		 Com_create_table +2
Opened_table_definitions +4
Opened_tables +2		 Com_create_table +1
Opened_table_definitions +2
Opened_tables +1
 
DROP TABLE ttemp;
Query OK, 0 rows affected		   DROP TABLE ttemp;
Query OK, 0 rows affected
Com_drop_table +1 Com_drop_table +2 Com_drop_table +1

Conclusion

  • A successful CREATE TABLE command opens and closes a table definition.
  • A non successful CREATE TABLE command opens the table definition and the file handle of the previous table. So a faulty application can be quite expensive.
  • A further non successful CREATE TABLE command has no other impact.
  • A DROP TABLE command closes a table definition and the file handle.
  • A CREATE TEMPORARY TABLE opens 2 table definitions and the file handle. Thus behaves different than CREATE TABLE
  • But a faulty CREATE TEMPORARY TABLE seems to be much less intrusive.
  • Open_table_definitions and Open_tables is always global, also in session context.
Taxonomy upgrade extras: 
status
temporary table