Reasonable configuration of the number of temporary files created on the MySQL server

How many temporary files can be created on the MySQL server? The following gives a detailed analysis and discussion on the reasonable configuration of the number of temporary files created on the MySQL server for your reference.

 
 

  
  
mysql> show global status like 'created_tmp%';  
  
  
+-------------------------+---------+  
  
  
| Variable_name | Value |  
  
  
+-------------------------+---------+  
  
  
| Created_tmp_disk_tables | 21197 |  
  
  
| Created_tmp_files | 58 |  
  
  
| Created_tmp_tables | 1771587 |  
  
  
+-------------------------+---------+ 
 
 

Created_tmp_tables is added each time you create a temporary table. If you create a temporary table on the disk, Created_tmp_disk_tables is added. Created_tmp_files indicates the number of temporary file files created by the MySQL service. The ideal configuration is:

 
 

  
  
Created_tmp_disk_tables / Created_tmp_tables * 100%  
 
 

For example, the above server Created_tmp_disk_tables/Created_tmp_tables * 100% = 1.20% should be quite good. Let's take a look at the configuration of temporary tables on the MySQL server:

 
 

  
  
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');  
  
  
+---------------------+-----------+  
  
  
| Variable_name | Value |  
  
  
+---------------------+-----------+  
  
  
| max_heap_table_size | 268435456 |  
  
  
| tmp_table_size | 536870912 |  
  
  
+---------------------+-----------+ 
 
 

Only temporary tables smaller than MB can be fully stored in the memory. If the memory limit is exceeded, the temporary hard disk table will be used.
 

How to enable MySQL slow Query

Mysql multi-Table query implementation

Introduction to MySQL CONVERT Functions

Take you to understand mysql Variables

Provides you with an in-depth understanding of MySQL user permissions.