相关参数

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)

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)

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)

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