Configuration of the number of temporary files created on the MySQL server _ MySQL

Configure bitsCN.com for 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 | 1, 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_table les * 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.

BitsCN.com