How much is the proper mysql tmp_table_size optimization and tmptablesize optimization?

How much is the proper mysql tmp_table_size optimization and tmptablesize optimization?

You can set the tmp_table_size option to increase the size of a temporary table, for example, a temporary table generated BY the advanced group by operation. If you increase the value, MySQL will also increase the size of the heap table, which can improve the join query speed. We recommend that you optimize the query as much as possible, make sure that the temporary tables generated during the query process are in the memory. do not generate a hard disk-based MyISAM table because the temporary tables are too large.

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 increases each time a temporary table is created. If the size of a temporary table exceeds tmp_table_size, a temporary table is created on the disk. Created_tmp_disk_tables increases, and Created_tmp_files indicates the number of temporary files created by the MySQL service, ideal Configuration:

Created_tmp_disk_tables/Created_tmp_tables * 100% <= 25%. For example, the above server Created_tmp_disk_tables/Created_tmp_tables * 100% = 1.20% should be quite good.

The default value is 16 Mb. It can be adjusted to-. The thread is exclusive. The memory is too large and may be insufficient for I/O congestion.

If a dynamic page is larger than MB, if most of the website content is static, 64 MB is enough.

Tmp_table_size Optimization

Database connections suddenly increase to 1000

Check that there are no LOCK operation statements.

However, there are obviously a lot of SQL statements for copy to tmp table. This syntax takes a long time to read, and the table will be locked and the update statements of the relevant table will be queued. If you execute such a copyt to tmp table statement multiple times, more statements will be blocked.
Too many connections lead to slow mysql processing.

The reason why the copy to tmp talbe statement is generated is that the size of the temporary table set in the parameter is smaller than the size of the result set when Order By or Group By is required for query, the table will be placed on the disk. At this time, the IO on the hard disk is much worse than the internal sales. It takes a lot of time. In addition, if another Mysql parameter max_heap_table_size is smaller than tmp_table_size, the system uses the max_heap_table_size value as the maximum temporary memory table. If the value is greater than this, the hard disk is rewritten.
Our mysql parameters are:

Tmp_table_size 33554432 (33.5 M)
Max_heap_table_size 16777216 (16.7 M)
Relatively small.
We recommend that you increase it to MB. We should have enough memory.

In addition, join_buffer_size (the cache that affects the join performance between tables) is smaller than 131072 (131 K), which can be increased by a little.

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

[Mysqld]
Tmp_table_size = 200 M

Mysql> show processlist;
Mysql> show columns from wp_posts;

In the first left join on Clause of an SQL statement, the userid of LEFT JOIN _ myuser AS t3 ON t1.userid = t3.userid _ mydata is involved in the condition comparison operation. Create an INDEX for the _ mydata TABLE based on the field userid: mysql> alter table '_ mydata' add index ('userid') to increase the tmp_table_size value.
In the mysql configuration file, the default size of tmp_table_size is 32 MB. If a temporary table exceeds this size, MySQL generates an error in The table tbl_name is full format. If you do many advanced group by queries, increase The tmp_table_size value. This is an official mysql 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 32 MB (the default) to 64 MB 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.
INDEX indexes should be created based on the fields used in condition judgment in clauses such as WHERE, JOIN, MAX (), MIN (), and order.
The index is used to quickly find rows with a specific value in a column. Without an index, MySQL has to start with the first record and then read the entire table until it finds the relevant rows. The larger the table, the more time it takes. If the table has an index on the queried columns, MySQL can quickly find the data file at a location without considering all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in Tree B.
According to the mysql development documentation:

Index is used:
Quickly finds the rows matching a WHERE clause
When JOIN is executed, rows are retrieved from other tables.
Find the MAX () or MIN () value for a specific index Column
If sorting or grouping is performed on the leftmost prefix of an available key (for example, order by key_part_1, key_part_2), sort or group a table. If all key values follow DESC, the key is read in reverse order.
In some cases, a query can be optimized to retrieve values without consulting data files. If all columns used for some tables are numeric and constitute the leftmost prefix of some keys, the values can be retrieved from the index tree for faster speed.
Assume that you have issued 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 taken out directly. If the separate single-row and column indexes exist on col1 and col2, the optimizer tries to determine which index will find fewer rows and find more restrictive indexes and use this index to retrieve rows.
When you dynamically set the size of tmp_table_size, use:

Set global tmp_table_size = 64*1024*1024
Set global tmp_table_size = 64 M
#1232-Incorrect argument type to variable 'tmp _ table_size'