Development of MySQL temporary table and dstatmysql temporary table monitoring plug-in

MySQL may use temporary tables when executing SQL queries. Generally, using temporary tables means low performance. MySQL temporary tables can be divided into: Memory temporary tables and disk temporary tables, Disks

MySQL may use temporary tables when executing SQL queries. Generally, using temporary tables means low performance. MySQL temporary tables can be divided into: Memory temporary tables and disk temporary tables, Disks

Temporary table Introduction

MySQL may use temporary tables when executing SQL queries. Generally, using temporary tables means low performance. MySQL temporary tables can be divided into Memory temporary tables and disk temporary tables. Disk temporary tables are stored using MyISAM, while Memory temporary tables use the Memory engine. MySQL uses a memory temporary table to store the intermediate result set of the query. If the intermediate result set is larger than the capacity setting of the temporary table, or the intermediate result set contains TEXT or BLOB column fields, mySQL converts it to a temporary disk table. For Linux, the default value is/tmp, And the/tmp directory is small.

Temporary table features

The following lists the features of several temporary memory tables.

1. Table Structure (. frm) on disk, data in memory

2. Hash indexes are used by default.

3. fixed-length storage (BTW: Even varchar)

4. Only table locks are supported.

5. TEXT and BLOB column types are not supported

Related Parameters

1 size parameter

MIN {tmp_table_size, max_heap_table_size}

Mysql> show global variables like '% table_size ';
+ --------------------- + ---------- +
| Variable_name | Value |
+ --------------------- + ---------- +
| Max_heap_table_size | 16777216 |
| Tmp_table_size | 16777216 |
+ --------------------- + ---------- +
2 rows in set (0.00 sec)

2. quantity parameters

Mysql> show global status like 'created _ tmp % ';
+ ------------------------- + ------- +
| Variable_name | Value |
+ ------------------------- + ------- +
| Created_tmp_disk_tables | 7 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 90 |
+ ------------------------- + ------- +
3 rows in set (0.00 sec)

Through ps_helper, We can refine who the temporary tables are.

Mysql> SELECT query, exec_count, memory_tmp_tables, disk_tmp_tables, avg_tmp_tables_per_query, tmp_tables_to_disk_pct FROM statements_with_temp_tables LIMIT 5;
+ Users + ------------ + ------------------- + ----------------- + -------------------------- + ------------------------ +
| Query | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct |
+ Users + ------------ + ------------------- + ----------------- + -------------------------- + ------------------------ +
| Select if ('locate '(? , '...' COMPRESSED _ size') DESC | 2 | 4 | 2 | 2 | 50 |
| Select if ('locate '(? , '... MPRESSED_SIZE' =? ),? ,... | 2 | 4 | 2 | 2 | 50 |
| Select if ('isnull' ('inform... '= 'performance _ scheme'... | 2 | 4 | 2 | 2 | 50 |
| Select if ('isnull' ('inform... by_thread_by_event_name '... | 2 | 4 | 2 | 2 | 50 |
| Show full fields from 'stateme... ', 'performance _ scheme'... | 2 | 4 | 2 | 2 | 50 |
+ Users + ------------ + ------------------- + ----------------- + -------------------------- + ------------------------ +
5 rows in set (0.00 sec)