mysql tmp_table_size Optimized Settings for what is appropriate

Increase the size of a temporary table by setting the tmp_table_size option, such as the temporary table generated by the advanced GROUP operation. If this value is increased, MySQL will increase the size of heap table at the same time, which can improve the speed of join query. It is recommended to optimize the query as much as possible to ensure that the temporary table generated during the query process is in memory, so as to avoid the generation of MyISAM table based on hard disk.

mysql > show global status like 'created_tmp%';

+ -- -- -- -- -- � + - +

| Variable_name | Value |

+ -- -- -- -- -- -- - + - +

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files 58 | |

1771587 | | Created_tmp_tables |

+ + -- -- -- -- -- -- � � +

Every time to create a temporary table, Created_tmp_tables increase, if the temporary table size is over tmp_table_size is on disk to create a temporary table, Created_tmp_disk_tables will increase, Created_tmp_files said MySQL services to create temporary file file number, ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% < Created_tmp_disk_tables/Created_tmp_tables * 100% = 1.20%, should be quite good

The default is 16M, can be adjusted to 64-256 best, thread exclusive, too large may not have enough memory I/O blockage

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

tmp_table_size optimization

Database connection suddenly increased to 1000 problem

There is no LOCK operation statement.

But obviously there are a lot of copy to tmp table SQL statement, this article language reading time is longer, and the table will be added to read lock, related tables update statements will be into the queue. If the copyt to tmp table statement is executed more times, it will cause more statements to be blocked.
Too many connections cause slow mysql processing.

copy to tmp talbe statement causes is query requires Order By or Group By etc need to use the result set, the parameters set in the temporary table size less than the size of the result set, will put the table in the disk, this time on the hard drive IO than sale in domestic market. It also takes a lot more time. max_heap_table_size is smaller than tmp_table_size, so the system will set the value of max_heap_table_size as the upper limit of the maximum memory temporary table. If the value is greater than this, the hard disk will be overwritten.
The two parameters of our mysql are:

tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
The relatively small.
Suggested increase to hundreds of M. I think we have enough memory.

In addition, join_buffer_size (the cache that affects join performance between tables) is 131072 (131K), which is small and can be increased by 1 point.

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

[mysqld]
tmp_table_size=200M

mysql > show processlist;
mysql > show columns from wp_posts;

In the first clause of the SQL statement: LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata userid is involved in the condition comparison operation. The _mydata table has an index based on field userid: mysql > ALTER TABLE '_mydata' ADD INDEX (' userid ') increases the value of tmp_table_size.
In the mysql configuration file, the default size for tmp_table_size is 32M. If a temporary table exceeds this size, MySQL generates an error in the form The table tbl_name is full. If you do a lot of advanced GROUP BY queries, increase tmp_table_size. This is the mysql official explanation of 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 this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
WHERE, JOIN, MAX(), MIN(), ORDER, BY, INDEX, WHERE, JOIN, MAX(), ORDER, BY
Indexes are used to quickly find rows with a specific value of 1 on a column. Without an index, MySQL has to start with the first record and then read through the entire table until it finds the relevant rows. The bigger the table, the more time it takes. If the table has one index for the column of the query, MySQL can quickly reach one location to search into the middle of the data file without having to consider all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reads. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in the B tree.
According to the development documentation for mysql:

Index index is used for:
Quickly find the rows that match 1 WHERE clause
When a join (JOIN) is executed, rows are retrieved from other tables.
Find the MAX() or MIN() values for the specific index column
If sorting or grouping is performed on the leftmost prefix of 1 available key (for example, ORDER BY key_part_1,key_part_2), sort or group 1 table. If all key value parts follow DESC, the keys are read in reverse order.
In some cases, a query can be optimized to retrieve a value without consulting a data file. If all columns used for some tables are numeric and form the leftmost prefix of some keys, values can be retrieved from the index tree for faster results.
Suppose you issue the following SELECT statement:

mysql > select * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multi-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the more restrictive index and use that index to fetch rows by determining which index will find fewer rows.
When setting the size of tmp_table_size dynamically, use:

set global tmp_table_size=64*1024*1024
set global tmp_table_size=64M
#1232 - Incorrect argument type to variable 'tmp_table_size'