Shulou(Shulou.com)06/01 Report--

The abnormal growth of tmp space indicates that the author: sylar all rights reserved. [the article is allowed to be reprinted, but the source address must be indicated in the form of a link, otherwise legal liability will be investigated.]

I. background note

Recently, zabbix always has the following alarm message:

Second, problem analysis

According to the zabbix alarm, query the space immediately. At this time, the root directory space is larger and will not go to the police.

Check the error log of mysql and find the following error message at this point in time

According to the error above, the tmp space growth is abnormal, and the table is added to it (.myi format ends, and myisam is mentioned, so make this judgment)

Query the database to confirm whether there is a directory pointing to / tmp. According to the screenshot below, we know that the / tmp directory is the directory where the mysql temporary table is located.

The / tmp directory is 16m, and a maximum of 32 can be created, which is enough for ordinary things to use. You need to locate exactly what circumstances require such a large number of temporary tables, so as to exceed this size and use disk space.

III. Mysql internal temporary tables (in-memory and on-disk) 3.1 definition of internal temporary tables

Unlike temporary tables created by hand, temporary files, called internal temporary table;, may be used to store query results during sql execution. This process is done automatically by Mysql and cannot be manually intervened by users.

These tables are stored either in memory using the memory engine or on disk using the MyISAM engine

When some SQL commands are executed in the MySQL database, it may need to create some internal temporary tables to complete more complex sorting or grouping queries. MySQL's temporary tables are divided into in-memory and on-disk. If possible, MySQL always uses in-memory 's temporary table first, and when the temporary table becomes too large, it may also be transferred as on-disk 's temporary table.

3.2 Source code display for internal temporary table creation

The MySQL source code is as follows

Internal temporary table optimization and suggestions

About the optimization of MySQL internal temporary table (internal temporary table):

(1) consider how to avoid SQL commands to create temporary tables as much as possible

For a database with very busy query connections, frequent use of queries that need to create temporary tables is itself a performance bottleneck. You need to re-examine the structure of your data tables and the relationships between tables, reconsider primary keys and indexes, and restructure the data structure to reduce the need for different ORDER BY and GROUP BY in your application.

Split into multiple queries with fewer levels of association, or use View tables.

Because the view table has been used in the front-line environment, it is recommended that when the view table is created, it should be re-created according to business requirements to reduce tmp consumption

(2) try to ensure that temporary tables are created in memory rather than disk

If you really can't avoid creating temporary tables, you need to make sure that these temporary tables can be created in memory as much as possible. Avoid using BLOB and TEXT type fields in structural design and query commands, or consider using the SUBSTRRING (colum,length) function to convert them to string types; telling the database to use in-memory temporary tables with the SQLSMALLRESULT option; using View to simplify queries; and using RAM disk memory disks to store temporary tables in the MySQL database (make sure you don't use BLOB and TEXT fields).

(3) how to avoid On-Disk Temporary Tables

Here are the official instructions:

The best solution is to avoid using the BLOB and TEXT types unless you really need them. If you can't avoid them, you may be able to use the ORDER BY SUBSTRRING (colum,length) trick to convert the values to character strings. Wihich will permit in-memory temporary tables. Just be sure that you are using a short engough substring that the temporary table doesn't grow larger than max_heap_table_size or tmp_table_size, or MySQL will convert the table to an on-disk MyISAM table. If the Extra column of EXPLAIN contains "Using temporary", the query uses an implicit temporary table. The best solution for Chinese translation is to avoid using BLOB and text types unless you really need them. If you can't avoid them, you can use ORDER BY SUBSTRRING (colum,length) to convert the value to a string. The use of memory temporary tables will be allowed. Just make sure that you are using a substring of a short engough temporary table and do not grow to more than the size of the max_heap_table_size tmp_table_size, otherwise a disk MyISAM table will be created. If the additional column explained contains "use temporary", the query uses an implicit temporary table. 6, BLOB and TEXT caused by the internal disk temporary table description 1, because the Memory storage engine does not support BLOB and TEXT types, so contains BLOB and TEXT type fields of the query, when it needs to use implicit temporary tables, they have to use on-disk MyISAM temporary table, even if its query results may be only a few simple rows of data.

This can lead to a serious performance bottleneck, and even if you can configure to store MySQL's temporary tables on RAM disk, you will still need to use many expensive operating system call functions. In practice, it is also found that the temporary tables of some SQL statements cannot even be used by RAM disk at all (in this case, the SQL query command will fail because the temporary table cannot be created).

2. BLOB and TEXT are two data types. Both data types are used to store large amounts of data. The former is saved in binary form, while the latter is preserved in character form.

These two data types are fundamentally different from other data types. In the MYSQL database, these two data types are treated as objects with entities. The storage engine will also save them in a special way. The BLOB data type stores data in a binary way. When using binary to store data, the system does not have the requirement of character set, nor does it set sorting rules. Instead, TEXT stores data in character form, which is limited by character sets and collations.

3. Because the capacity of these two data types is relatively large, the temporary table will suddenly become very large when operating on these types of fields.

At this point, it is easy to exceed the limits of the above two parameters. The system converts the memory temporary table to the disk temporary table. For this reason, these two data types increase the chances of generating disk temporary tables.

4. Different storage types have different support for data types.

If a storage type does not support certain data types, the system will directly use disk temporary tables, even if the data does not exceed its specified size.

To put it simply, for the storage engine, if it does not support certain data types, then when operating on these data types, the system can only use disk temporary tables, not disk temporary tables. For example, for the Memory storage engine, it does not support BLOB and TEXT data types. In the operation of the system, if BLOB and TEXT columns are used and implicit temporary tables are needed, the query will not use memory temporary tables, but will directly use disk temporary tables. This is true even if the columns in both data types do not store much data. Obviously this will greatly reduce the performance of the database.

VII. Summary

[summary]

1. If things exceed tmptablesize, disk temporary tables will be used. If there is no operation, memory temporary tables will be used. 2. If BLOB and TEXT columns are used, "and implicit temporary tables are needed", disk temporary tables are used directly and memory is not used. Even if the limit of maxheaptable_size is not exceeded, (varchar,char uses temporary tables in memory). 3, use explain to view, in fact, you can use explain analysis before each implementation, if you use the temporary table will be realistic in extra.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.