[MySQL學習] MySQL 5.6 improvement for troubleshooting

本文基于sveta（oracle的principle technical support engineer ）的博文”my eighteen mysql 5.6 favorite troubleshooting improvements”，原文位址如下：https://blogs.oracle.com/svetasmirnova/entry/my_18_mysql_5_6

原文針對每個點介紹的比較粗略，這裡會對内容做一些擴充，也是我看這篇部落格時的筆記，聚合了查閱的相關資料

1.對update/insert/delete進行explain

在5.5及之前的版本中，隻能對select進行explain，輸出查詢計劃，通常的做法是将dml轉換為select，但優化器在對dml和查詢，可能做不同的優化。

簡單的測試表sbtest，例如：

mysql> explain delete from sbtest1 where k = 100;

+—-+————-+———+——-+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |

| 1 | simple | sbtest1 | range | primary,k | k | 4 | null | 1 | using where |

1 row in set (0.00 sec)

不過嘗試了下,explain extended對于dml不記錄warning，而對于select，可以從warning中檢視到具體的查詢計劃資訊     

2. information_schema.optimizer_trace表（後續擴充研究）

這是5.6新增的表，用于記錄最近的幾次查詢計劃數，比起5.5，這其中記錄的資訊更加具體，不過也複雜很多，不是很好讀懂，官方提供的文檔在此：

http://dev.mysql.com/doc/internals/en/optimizer-tracing.html

表結構如下：

query

查詢的sql

trace

查詢計劃路徑，格式為json

missing_bytes_beyond_max_mem_size

由于超出optimizer_trace_max_mem_size限制，導緻的截斷位元組數

insufficient_privileges

某些情況下，使用者執行的sql引用了sql security definer試圖或者存儲過程，可能在某些對象上沒有權限，将trace置為空，并将該列設定為1

每個線程單獨儲存各自的查詢路徑資料，從這個表中也隻能獲得各自的資料。

預設情況下，這個特性是關閉的，我們可以通過如下打開：

set optimizer_trace=”enabled=on”;

optimizer_trace有兩個字段：

“enabled=on,one_line=off” ,可以通過set 進行字元串更新，前者表示打開optimizer_trace，後者表示列印的查詢計劃是否以一行顯示，還是以json樹的形式顯示

我們可以在session級别來設這這個參數。

預設optimizer_trace_limit值為1，是以隻會儲存一條記錄。這個設定需要重連session才能生效，另外一個變量optimizer_trace_offset通常與之配合使用，預設值為-1

例如，offset=-1, limit=1将顯示最近一次trace

offset=-2,limit=1将顯示最近的前一個trace。

offset=-5,limit=5 将最近的5次trace列印出來

總的來說：

當offset大于0時，則會顯示老的從offset開始的limit個trace，也就是說，新的trace沒有記下來。

當offset小于0時，則會顯示最新的-offset開始的limit個trace，也就是說，隻顯示新的trace

注意重設變量會導緻trace被清空

另外由于trace資料是存儲在記憶體中的，是以還需要設定optimizer_trace_max_mem_size來限制記憶體的使用量，否則意外的設定可能導緻記憶體爆掉。這是session級别，不應該設定的過大

optimizer_trace_limit和optimizer_trace_offset也影響占用記憶體大小，但不應該超過optimizer_trace_max_mem_size

從optimizer_trace表列印的資訊來看，即使是一條簡單的select語句，也會列印出非常龐大的樹形結構，通過set @@end_markers_in_json=on可以使其更便于閱讀。

<a href="http://dev.mysql.com/doc/internals/en/tracing-example.html">官方示例</a>

針對optimizer_trace的開銷，dimitrik大神有做過測試，連結如下：

http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html

根據其測試，當打開optimizer trace時，約有不到10%的性能下降，打開innodb_stats_persistent時，幾乎沒有退化(未去證明)

3.以json模式輸出explain

執行格式為 explain format=json [query]

4.更多的information_schema表

通過選項innodb_monitor_enable、innodb_monitor_disable、innodb_monitor_reset來調整每個計數器，例如想開啟某個計數器，就執行

set global innodb_monitor_enable = “dml_%”  //可以用比對符來做計數器名

也可以直接用”%”來代替所有的計數器

set global innodb_monitor_reset_all = ‘%';

innodb_sys_%包含了innodb資料詞典等資訊，例如表，外鍵，列等。。。

mysql&gt; show tables like ‘innodb_sys_%';

+———————————————+

| tables_in_information_schema (innodb_sys_%) |

| innodb_sys_datafiles |

| innodb_sys_tablestats |

| innodb_sys_indexes |

| innodb_sys_tables |

| innodb_sys_fields |

| innodb_sys_tablespaces |

| innodb_sys_foreign_cols |

| innodb_sys_columns |

| innodb_sys_foreign |

innodb_buffer_pool_stats包含了每個buffer pool執行個體的資訊。

另外還有其他，例如更多的壓縮表資訊展示。。。。

5.将所有的死鎖資訊全寫入錯誤日志中

控制選項：innodb_print_all_deadlocks

6.物化innodb表的統計資訊

控制選項：innodb_stats_persistent

當開啟該選項後，就會将表的統計資訊記錄到ibdata中，隻有手動執行analyze table才會對其進行更新。

7. innodb隻讀事務(需要跟進)

<a href="http://webcache.googleusercontent.com/search?q=cache:wazmhpunc9cj:blogs.innodb.com/wp/2011/12/better-scaling-of-read-only-workloads/+http://blogs.innodb.com/wp/2011/12/better-scaling-of-read-only-workloads/&amp;cd=1&amp;hl=zh-cn&amp;ct=clnk&amp;gl=sg">官方部落格介紹</a>

預設開啟事務是read write,可以在開啟事務時指定： start transaction read only;

如果以autocommit運作select，則視其為read only

根據官方描述，隻讀事務減少了建立read view的開銷，因為這是個全局鎖競争的熱點。

後面再深入研究其具體實作。

有兩個參數來控制這個行為：

相應的也有兩個參數來控制将轉儲檔案中記錄的page讀入bp

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_load_now">innodb_buffer_pool_load_now</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_load_at_startup">innodb_buffer_pool_load_at_startup</a>

沒什麼好說的，衆望所歸

11.row模式複制時，不記錄全部資料前鏡像(減少網絡傳輸)

設定為full，跟之前版本行為相同

設定為minimal，隻在前鏡像記錄那些可以标記一條記錄的列，例如主鍵值；隻記錄後鏡像中修改過的列

設定為noblob，在沒有blob/text類型列時，行為和all相同，當blob列不作為标示列或被修改的列時，就不在binlog中記錄。

13.更好的處理錯誤或warning資訊

和存儲過程中擷取錯誤/警告資訊有關。

以下為performance schema的一些新特性

14.可以觀察某些特定表的io操作;

15,可以觀察某些特定sql事件（events_statements_*）

16,events_stages_*表

17,可以對ps資訊進行聚合，例如根據使用者名，host等，由于ps也存儲了曆史資訊，可以聚合這些資訊做性能分析

18,新的host_cache表，cache的域名被儲存在記憶體中，這樣就無需查詢dns伺服器，之前版本這些資訊對使用者是不可見的。