Development of MySQL temporary table and dstat mysql temporary table monitoring plug-in

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) in the disk, data in the memory 2. hash indexes are used by default. 3. fixed-length storage (BTW: fixed-length storage even for varchar) 4. only table locks are supported. the 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;+-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+| query                                                             | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct |+-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+| 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_schema` . ... |          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_schema` . ... |          2 |                 4 |               2 |                        2 |                     50 |+-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+5 rows in set (0.00 sec)

Optimize temporary tables
There are two phases: Initial System Design and initial system design after the product is launched. The optimization points include: 1. create an index <= create an index on the column of order by or group by. 2. sharding tables <= Large columns (such as BLOB or TEXT) are generally not used as predicates. During table design, they can be independent to another table. After the product is launched, we can only optimize the business or SQL 1. split SQL <= temporary tables are mainly used for sorting and grouping. Many businesses require sorting before extracting detailed data. In this case, you can separate sorting operations from querying all information, to reduce the size of temporary tables in sorting or grouping, and improve the efficiency of sorting or grouping 2. optimize Services, remove sorting groups, and other operations

Dstat MySQL temporary table monitoring plug-in development

### Author: linwaterbin@gmail.com### UPDATE: 2014-2-24### FUNCTION: analyze mysql temp table use# init MySQL authorityglobal mysql_usermysql_user = os.getenv('DSTAT_MYSQL_USER')global mysql_pwdmysql_pwd = os.getenv('DSTAT_MYSQL_PWD')global mysql_hostmysql_host = os.getenv('DSTAT_MYSQL_HOST')global mysql_dbmysql_db = os.getenv('DSTAT_MYSQL_DB')class dstat_plugin(dstat):    """    Plugin for MySQL 5 Temp Table Usage.    """    def __init__(self):        self.name = 'mysql5 tmp usage'        #self.format = ('d',12,50)        self.nick = ('mem', 'disk','mem-disk-pct',)        self.vars = ('memory_tmp_tables', 'disk_tmp_tables','avg_mem_to_disk_pct',)        self.type = 's'        self.width = 12         self.scale = 50    def check(self):         global MySQLdb        import MySQLdb        try:            self.db = MySQLdb.connect(user=mysql_user,passwd=mysql_pwd,host=mysql_host,db=mysql_db)        except:            raise Exception, 'Cannot interface with MySQL server'    def extract(self):        try:            query="""select sum(memory_tmp_tables) as memory_tmp_tables,sum(disk_tmp_tables) as disk_tmp_tables,avg(tmp_tables_to_disk_pct) as avg_mem_to_disk_pct from statements_with_temp_tables;"""            cur = self.db.cursor(MySQLdb.cursors.DictCursor)            cur.execute(query)            for record in cur.fetchall():                  self.val['memory_tmp_tables'] =record['memory_tmp_tables']                  self.val['disk_tmp_tables'] = record['disk_tmp_tables']                  self.val['avg_mem_to_disk_pct'] = record['avg_mem_to_disk_pct']            if step == op.delay:                self.set1.update(self.set2)        except Exception, e:            for name in self.vars:                self.val[name] = -1

The monitoring test diagram is as follows:



By DataHacker2014-2-26Good Luck!