MySQL 临时表与dstat mysql临时表监控插件开发

临时表简介

MySQL在执行SQL查询时可能会用到临时表，一般而言，用到临时表就意味着性能较低。MySQL临时表可分：内存临时表和磁盘临时表，磁盘临时表使用MyISAM存储，而内存临时表则使用Memory引擎。MySQL使用内存临时表来存放查询的中间结果集，如果中间结果集大于临时表的容量设定，又或者中间结果集含TEXT或BLOB列类型字段，则MySQL会把其转化为磁盘临时表。Linux平台缺省是/tmp,/tmp目录小的系统要注意啦。

临时表特性

下面列举几个内存临时表的特性

1.表结构(.frm)在磁盘，数据在内存

2.缺省使用哈希索引

3.定长存储(BTW：即使是varchar也是定长)

4.只支持表锁

5.不支持TEXT和BLOB列类型

相关参数

1 大小参数

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 数量参数

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)

通过ps_helper我们可以细化这些临时表到底是谁的临时表

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)

优化临时表

分2个阶段：系统设计初期和产品上线后

在系统设计初期，优化却入点有：

1.创建索引  <==对ORDER BY 或 GROUP BY的列上创建索引

2.拆分表  <==大的列(如BLOB或TEXT)一般不会用作谓词，在表设计时可独立到另一张表

而产品上线后，我们只能对业务或SQL进行优化

1.拆分SQL <==临时表主要用于排序和分组，很多业务都是要求排序后再取出详细的数据，这种情况下可以把排序操作和查询所有信息的操作分开，以降低排序或分组时临时表的大小，提升排序或分组的效率

2.优化业务，去掉排序分组等操作

dstat MySQL 临时表监控插件开发

### Author: linwaterbin@gmail.com
### UPDATE: 2014-2-24
### FUNCTION: analyze mysql temp table use
# init MySQL authority
global mysql_user
mysql_user = os.getenv(\'DSTAT_MYSQL_USER\')
global mysql_pwd
mysql_pwd = os.getenv(\'DSTAT_MYSQL_PWD\')
global mysql_host
mysql_host = os.getenv(\'DSTAT_MYSQL_HOST\')
global mysql_db
mysql_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

监控测试图如下：

By DataHacker

2014-2-26

Good Luck！

转载于:http://blog.itpub.net/26515977/viewspace-1208262/