临时表简介

临时表特性
相关参数
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

bitsCN.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn