Tmp_table_size---> Optimized MYSQL Experience Summary

A problem with a sudden increase in database connectivity to 1000

Viewed, there are no lock action statements. However, there are obviously many SQL statements from copy to TMP table, which reads longer, and the table is read-locked, and the UPDATE statement for the related table is queued. More execution of such a COPYT to TMP table statement causes more statements to be blocked. Too many connections cause MySQL processing to be slow. The copy to TMP Talbe statement is generated because the query requires an order by or group BY, etc., when the size of the temporary table set in the parameter is smaller than the size of the result set, the table is placed on disk, and the IO on the hard disk is much worse than the domestic sales. It takes a lot more time. In addition to mysql another parameter max_heap_table_size than tmp_table_size hours, then the system will put the value of max_heap_table_size as the maximum memory temporary table upper limit, larger than this, overwrite the hard disk. Our MySQL two parameters are: Tmp_table_size 33554432 (33.5M) max_heap_table_size 16777216 (16.7M) is relatively small. The proposal is increased to hundreds of M. We should have enough memory. In addition Join_buffer_size (cache that affects join performance between tables) is 131072 (131K) Smaller and can be added a little. [Email protected] ~]# VI/ETC/MY.CNF

[Mysqld]

tmp_table_size=200m



Mysql> show Processlist;
Mysql> show columns from wp_posts; The first left join of the SQL statement in the ON clause: the userid of the left join _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 the MySQL configuration file, the default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value. This is the official MySQL 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, and where this is not possible, try to ensure temp Orary tables is always stored in memory. Watching the processlist for queries with temporary tables so take too long to resolve can give you an early warning tha T tmp_table_size needs to be upped. Be aware this memory is also allocated per-thread. An example where upping this worked for 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 being active at any one time, with all-round benefits for the S Erver, and available memory.

The field that is used in the conditions in the WHERE, JOIN, MAX (), MIN (), ORDER by clauses should be indexed by index.
The index is used to quickly find a row with 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 line. The larger the table, the more time it takes. If the table has an index to the column of the query, MySQL can quickly reach a location to find 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 the MySQL development documentation:
Indexed index is used to:
Quickly find the row that matches a WHERE clause
When a junction (join) is executed, rows are retrieved from other tables.
Find Max () or min () values for a specific index column
Sort or Group A table if sorting or grouping is done on the leftmost prefix of an available key (for example, order by key_part_1,key_part_2). If all key values are partially followed by DESC, the key is read in reverse order.
In some cases, a query can be optimized to retrieve values without consulting the data file. If all columns used for some tables are numeric and form the leftmost prefix of some keys, the values can be retrieved from the index tree for faster.

Assume that 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 row can be fetched directly. If separate single-row column indexes exist on col1 and col2, the optimizer tries to find a more restrictive index by deciding which index will find fewer rows and using that index to fetch rows.

When setting the Tmp_table_size size dynamically, use:

Set Global tmp_table_size=64*1024*1024

Instead of:

Set Global tmp_table_size=64m

Otherwise, an error will occur:

#1232-incorrect argument type to variable ' tmp_table_size '

Tmp_table_size---> Optimized MYSQL Experience Summary