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 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

監控測試圖如下：



By DataHacker2014-2-26Good Luck！