Introduction to the development of a temporary MySQL table and a temporary dstatmysql table monitoring plug-in bitsCN.com
Temporary table features
Related parameters
Mysql> show global variables like '% table_size '; + rows + ---------- + | Variable_name | Value | + rows + ---------- + | max_heap_table_size | 16777216 | tmp_table_size | 16777216 | + ------------------- + ---------- + 2 rows in set (0.00 sec)
Mysql> show global status like 'created _ tmp % '; + rows + ------- + | Variable_name | Value | + rows + ------- + | Created_tmp_disk_tables | 7 | Created_tmp_files | 6 | Created_tmp_tables | 90 | + rows + ------- + 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; + keys + ------------ + ------------------- + ----------------- + keys + ------------------------ + | query | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp _ Tables_per_query | tmp_tables_to_disk_pct | + Region + ------------ + ------------------- + --------------------- + Region + -------------------- + | 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 | + rows + ------------ + --------------------- + --------------- + -------------------------- + ------------------------ + 5 rows in set (0.00 sec)
Optimize temporary tables
Dstat MySQL temporary table monitoring plug-in development
### Author: linwaterbin@gmail.com ### UPDATE: 2014-2-24 ### FUNCTION: analyze mysql temp table use # init MySQL authority global mysql_user = OS. getenv ('dstat _ MYSQL_USER ') global mysql_pwd = OS. getenv ('dstat _ MYSQL_PWD ') global mysql_host = OS. getenv ('dstat _ MYSQL_HOST ') global 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) handle T: 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 region from Region;" cur = self. db. cursor (MySQLdb. cursors. dictCursor) cur.exe cute (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) failed t Exception, e: for name in self. vars: self. val [name] =-1
BitsCN.com
