MySQL tmp_table_size optimization settings for how much appropriate _mysql

Increase the size of a temporary table by setting the Tmp_table_size option, such as a temporary table generated by an advanced group by operation. If you raise this value, MySQL will also increase the size of the heap table, to improve the speed of the join query, we recommend optimizing the query, to ensure that the temporary table generated during the query in memory, to avoid the temporary table is too large, resulting in the generation of MyISAM table based on the hard disk.

Mysql> show global status like ' created_tmp% ';

+ —————————— –+ ——— +

| variable_name | Value |

+ ———————————-+ ——— +

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files | 58 |

| Created_tmp_tables | 1771587 |

+ —————————— –+ ——— –+

Each time you create a temporary table, the created_tmp_tables increases, and if the temporary table size exceeds tmp_table_size, the temporary table is created on disk, and Created_tmp_disk_tables is added, Created_tmp_ Files represent the number of temporary file files created by the MySQL service, and the ideal configuration is:

Created_tmp_disk_tables/created_tmp_tables * 100% <= 25% For example the server above Created_tmp_disk_tables/created_tmp_tables * 100% =1.20%, it should be pretty good.

default to 16M, adjustable to 64-256 best , thread exclusive, too large may not have enough memory I/O Jam

If the dynamic page to be larger, 100M or more, if most of the site is static content, generally 64M enough.

Tmp_table_size optimization

Problems with a sudden increase in database connectivity to 1000

Look, there is no lock operation statement.

But there are obviously a lot of SQL statements in copy to TMP table that are read for a long time, and the table is read-locked, and the UPDATE statement for the related table is queued. Multiple executions of such COPYT to TMP table statements can result in more statements being blocked.
Too many connections cause MySQL to process slowly.

The copy to TMP Talbe statement arises because the query requires a result set that needs to be used by the order by or group by, when the size of the temporary table set in the parameter is smaller than the result set, the table is placed on disk, and the IO on the hard drive is much worse than the sales. The time spent is also much more. In addition to MySQL, another parameter max_heap_table_size than tmp_table_size hours, then the system will max_heap_table_size value as the maximum memory temp table upper limit, larger than this, rewrite the hard disk.
Our MySQL these two parameters are:

Tmp_table_size 33554432 (33.5M)
Max_heap_table_size 16777216 (16.7M)
relatively small.
The proposal was added to hundred M. We should have enough memory.

In addition, Join_buffer_size (a cache that affects the performance of join between tables) is 131072 (131K) Smaller and can be added a little.

[Root@mail ~]# vi/etc/my.cnf

[Mysqld]
tmp_table_size=200m

Mysql> show Processlist;
Mysql> show columns from wp_posts;

In the first left JOIN in clause of the SQL statement: The userid of the _myuser as T3 on T1.userid=t3.userid _mydata is involved in the conditional comparison operation. An index is established for the _mydata table based on the field UserID: mysql> ALTER table ' _mydata ' Add INDEX (' userid ') increases the tmp_table_size value.
In MySQL's configuration file, the default size of the Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces a full form of the table Tbl_name error, and if you do many advanced GROUP by queries, increase the tmp_table_size value. This is the MySQL official explanation for this option:

Tmp_table_size

This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where, isn't possible, try to ensure temp Orary tables are always stored in memory. Watching the processlist for queries with temporary tables, take too long to resolve can give your an early tha T tmp_table_size needs to be upped. Be aware this memory is also allocated per-thread. An example where upping this worked as more is a server where I upped this from 32MB (the default) to 64MB with Immediat E effect. The quicker resolution of queries resulted in less threads active at any one time, with being all-round for the s Erver, and available memory.
For where, JOIN, MAX (), MIN (), the conditions in the clause in the order by, and so on, the fields used in the judgment should be indexed index.
Indexes are used to quickly find rows that use a specific value on a column. Without an index, MySQL had to start with the first record and then read through the entire table until it found the relevant rows. The bigger the table, the more time it takes. If the table has an index to the query's column, MySQL can quickly reach a location to search the middle of the data file, and there is no need to consider all the data. If a table has 1000 rows, this is at least 100 times times faster than sequential reads. All MySQL indexes (PRIMARY, unique, and index) are stored in the B-tree.
According to MySQL's development documentation:

Indexed index is used to:
Quickly find a row that matches a WHERE clause
When a join is performed, rows are retrieved from other tables.
Find the Max () or min () value for a specific index column
Sort or Group A table if you sort or group on the leftmost prefix of an available key (for example, order by key_part_1,key_part_2). If all key values partially follow DESC, the keys are read in reverse order.
In some cases, a query can be optimized to retrieve values without consulting the data file. If all the columns used for some tables are numeric and form the leftmost prefix of some keys, the value can be retrieved from the index tree for faster.
Suppose you issue the following SELECT statement:

Mysql> SELECT * from Tbl_name WHERE col1=val1 and Col2=val2; If a multiple-column index exists on col1 and col2, the appropriate row can be taken out directly. If separate row-column indexes exist on col1 and col2, the optimizer attempts to determine which index will find fewer rows and find more restrictive indexes and use the index to fetch rows.
To dynamically set the size of a tmp_table_size, use:

Set Global tmp_table_size=64*1024*1024
Set Global tmp_table_size=64m
#1232-incorrect argument type to variable ' tmp_table_size '