最後更新: 2017-10-25

介紹

Checking

show variables like '%tmp%';

+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| max_tmp_tables    | 32       |
| slave_load_tmpdir | /tmp     |
| tmp_table_size    | 16777216 |
| tmpdir            | /tmp     |
+-------------------+----------+

Remark

設定 tmpdir 位置

[mysqld]
tmpdir = /ram-cache

show variables like 'max_heap_table_size';

+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

SHOW GLOBAL STATUS like 'Created_tmp_%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3978  |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 4011  |
+-------------------------+-------+

說明

The server creates temporary tables under conditions such as these:

 - Evaluation of DISTINCT combined with ORDER BY may require a temporary table.
 - Evaluation of multiple-table UPDATE statements
 - Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause,

or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

Created_tmp_tables: in-memory 時加一
Created_tmp_disk_tables: initially or by converting an in-memory table 都會加一

[server uses an on-disk table]

Presence of a BLOB or TEXT column in the table. This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns

Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.

[Storage Engines Used for Temporary Tables]

If an internal temporary table is created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table.
( tmp_table_size & max_heap_table_size smaller 決定 convert memory2disk)

The internal_tmp_disk_storage_engine system variable determines which storage engine the server uses to manage on-disk internal temporary tables. Permitted values are INNODB (the default) and MYISAM.

[Temporary Table Storage Format]

In-memory temporary tables are managed by the MEMORY storage engine,
which uses fixed-length row format. VARCHAR and VARBINARY column values are padded to the maximum column length

Created_tmp_files

How many temporary files mysqld has created.

Tuning

當 Created_tmp_disk_tables 接近 Created_tmp_tables 時

那要加大

tmp_table_size = 32M
max_heap_table_size = 32M

Remark

If percentage of tables created on disk exceeds 50% then

first check your database is indexed properly specially for joining and group by columns

*_buffer_size Setting

# All allocated per connection

• read_buffer_size
• sort_buffer_size
• join_buffer_size
• read_rnd_buffer_size

join_buffer_size

The minimum size of the buffer that is used for plain index scans,

range index scans, and joins that do not use indexes and thus perform full table scans.

It is allocated for each full join(JOINs without indexes) between two tables

the "Select_full_join" status variable for a count of full JOINs

Normally, the best way to get fast joins is to add indexes.

Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.

sort_buffer_size