1.表級鎖狀態

mysql> show status like 'table%';

+----------------------------+-----------+

| Variable_name? ? ? ? ? ? ? | Value? ? ?|

+----------------------------+-----------+

| Table_locks_immediate? ? ? | 139306577 |

| Table_locks_waited? ? ? ? ?| 0? ? ? ? ?|

| Table_open_cache_hits? ? ? | 0? ? ? ? ?|

| Table_open_cache_misses? ? | 0? ? ? ? ?|

| Table_open_cache_overflows | 0? ? ? ? ?|

+----------------------------+-----------+

5 rows in set (0.00 sec)

● Table_locks_immediate:產生表級鎖定的次數;

● Table_locks_waited:出現表級鎖定爭用而發生等待的次數; 兩個狀態值都是從系統啟動后開始記錄,沒出現一次對應的事件則數量加 1。如果這里的

Table_locks_waited 狀態值比較高,那么說明系統中表級鎖定爭用現象比較嚴重,就需要進一步分析為 什么會有較多的鎖定資源爭用了。

2.InnoDB行級鎖狀態

mysql> show status like 'innodb_row_lock%';

+-------------------------------+---------+

| Variable_name? ? ? ? ? ? ? ? ?| Value? ?|

+-------------------------------+---------+

| Innodb_row_lock_current_waits | 2? ? ? ?|

| Innodb_row_lock_time? ? ? ? ? | 8351372 |

| Innodb_row_lock_time_avg? ? ? | 248? ? ?|

| Innodb_row_lock_time_max? ? ? | 1678? ? |

| Innodb_row_lock_waits? ? ? ? ?| 33578? ?|

+-------------------------------+---------+

5 rows in set (0.00 sec)

Innodb 的行級鎖定狀態變量不僅記錄了鎖定等待次數,還記錄了鎖定總時長,每次平均時長,以及 最大時長,此外還有一個非累積狀態量顯示了當前正在等待鎖定的等待數量。對各個狀態量的說明如 下:

● Innodb_row_lock_current_waits:當前正在等待鎖定的數量;

● Innodb_row_lock_time:從系統啟動到現在鎖定總時間長度;

● Innodb_row_lock_time_avg:每次等待所花平均時間;

● Innodb_row_lock_time_max:從系統啟動到現在等待最常的一次所花的時間; ●Innodb_row_lock_waits:系統啟動后到現在總共等待的次數;

對于這 5 個狀態變量,比較重要的主要是 Innodb_row_lock_time_avg(等待平均時長), Innodb_row_lock_waits(等待總次數)以及 Innodb_row_lock_time(等待總時長)這三項。尤其是當等 待次數很高,而且每次等待時長也不小的時候,我們就需要分析系統中為什么會有如此多的等待,然后 根據分析結果著手指定優化計劃。

此外,Innodb 出了提供這五個系統狀態變量之外,還提供的其他更為豐富的即時狀態信息供我們分 析使用。可以通過如下方法查看:

1. 通過創建 Innodb Monitor 表來打開 Innodb 的 monitor 功能:

mysql> create table innodb_monitor(a int) engine=innodb; Query OK, 0 rows affected (0.07 sec)

2. 然后通過使用“SHOW INNODB STATUS”查看細節信息(由于輸出內容太多就不在此記錄了);

可能會有讀者朋友問為什么要先創建一個叫 innodb_monitor 的表呢?因為創建該表實際上就是告訴 Innodb 我們開始要監控他的細節狀態了,然后 Innodb 就會將比較詳細的事務以及鎖定信息記錄進入 MySQL 的 error log 中,以便我們后面做進一步分析使用。

查看鎖詳細信息：

mysql> select * from information_schema.INNODB_LOCKS;

Empty set (0.01 sec)

[ec2-user@ip-172-31-7-8 ~]$ sudo tail -n 100 /var/local/mysql/data/ip-172-31-7-8-slow.log

mysql> explain

3.Binlog 相關參數及優化策略

mysql> show variables like '%binlog%';

+-----------------------------------------+----------------------+

| Variable_name? ? ? ? ? ? ? ? ? ? ? ? ? ?| Value? ? ? ? ? ? ? ? |

+-----------------------------------------+----------------------+

| binlog_cache_size? ? ? ? ? ? ? ? ? ? ? ?| 1048576? ? ? ? ? ? ? |

| binlog_checksum? ? ? ? ? ? ? ? ? ? ? ? ?| CRC32? ? ? ? ? ? ? ? |

| binlog_direct_non_transactional_updates | OFF? ? ? ? ? ? ? ? ? |

| binlog_error_action? ? ? ? ? ? ? ? ? ? ?| IGNORE_ERROR? ? ? ? ?|

| binlog_format? ? ? ? ? ? ? ? ? ? ? ? ? ?| MIXED? ? ? ? ? ? ? ? |

| binlog_gtid_simple_recovery? ? ? ? ? ? ?| OFF? ? ? ? ? ? ? ? ? |

| binlog_max_flush_queue_time? ? ? ? ? ? ?| 0? ? ? ? ? ? ? ? ? ? |

| binlog_order_commits? ? ? ? ? ? ? ? ? ? | ON? ? ? ? ? ? ? ? ? ?|

| binlog_row_image? ? ? ? ? ? ? ? ? ? ? ? | FULL? ? ? ? ? ? ? ? ?|

| binlog_rows_query_log_events? ? ? ? ? ? | OFF? ? ? ? ? ? ? ? ? |

| binlog_stmt_cache_size? ? ? ? ? ? ? ? ? | 32768? ? ? ? ? ? ? ? |

| binlogging_impossible_mode? ? ? ? ? ? ? | IGNORE_ERROR? ? ? ? ?|

| innodb_api_enable_binlog? ? ? ? ? ? ? ? | OFF? ? ? ? ? ? ? ? ? |

| innodb_locks_unsafe_for_binlog? ? ? ? ? | OFF? ? ? ? ? ? ? ? ? |

| max_binlog_cache_size? ? ? ? ? ? ? ? ? ?| 18446744073709547520 |

| max_binlog_size? ? ? ? ? ? ? ? ? ? ? ? ?| 1073741824? ? ? ? ? ?|

| max_binlog_stmt_cache_size? ? ? ? ? ? ? | 18446744073709547520 |

| simplified_binlog_gtid_recovery? ? ? ? ?| OFF? ? ? ? ? ? ? ? ? |

| sync_binlog? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 0? ? ? ? ? ? ? ? ? ? |

+-----------------------------------------+----------------------+

19 rows in set (0.00 sec)

“binlog_cache_size":在事務過程中容納二進制日志 SQL 語句的緩存大小。二進制日志緩存是服 務器支持事務存儲引擎并且服務器啟用了二進制日志(—log-bin 選項)的前提下為每個客戶端分配的內 存,注意,是每個Client都可以分配設置大小的binlog cache空間。如果讀者朋友的系統中經常會出現 多語句事務的華,可以嘗試增加該值的大小,以獲得更有的性能。當然,我們可以通過 MySQL 的以下兩 個狀態變量來判斷當前的 binlog_cache_size 的狀況:Binlog_cache_use 和 Binlog_cache_disk_use。

“max_binlog_cache_size”:和"binlog_cache_size"相對應,但是所代表的是 binlog 能夠使用的 最大 cache 內存大小。當我們執行多語句事務的時候,max_binlog_cache_size 如果不夠大的話,系統可 能 會 報 出 “ Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”的錯誤。

“max_binlog_size”:Binlog 日志最大值,一般來說設置為 512M 或者 1G,但不能超過 1G。該大小 并不能非常嚴格控制 Binlog 大小,尤其是當到達 Binlog 比較靠近尾部而又遇到一個較大事務的時候,系 統為了保證事務的完整性,不可能做切換日志的動作,只能將該事務的所有 SQL 都記錄進入當前日志, 直到該事務結束。這一點和 Oracle 的 Redo 日志有點不一樣,因為 Oracle 的 Redo 日志所記錄的是數據文 件的物理位置的變化,而且里面同時記錄了 Redo 和 Undo 相關的信息,所以同一個事務是否在一個日志中 對 Oracle 來說并不關鍵。而 MySQL 在 Binlog 中所記錄的是數據庫邏輯變化信息,MySQL 稱之為 Event, 實際上就是帶來數據庫變化的 DML 之類的 Query 語句。

| 1048576 | | OFF | | 4294967295 | | 1073741824 | |0 |

“sync_binlog”:這個參數是對于 MySQL 系統來說是至關重要的,他不僅影響到 Binlog 對 MySQL 所 帶來的性能損耗,而且還影響到 MySQL 中數據的完整性。對于“sync_binlog”參數的各種設置的說明如 下:

● ??sync_binlog=0,當事務提交之后,MySQL 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中 的信息到磁盤,而讓 Filesystem 自行決定什么時候來做同步,或者 cache 滿了之后才同步到磁 盤。

● ??sync_binlog=n,當每進行 n 次事務提交之后,MySQL 將進行一次 fsync 之類的磁盤同步指令來 將 binlog_cache 中的數據強制寫入磁盤。

在 MySQL 中系統默認的設置是 sync_binlog=0,也就是不做任何強制性的磁盤刷新指令,這時候的性 能是最好的,但是風險也是最大的。因為一旦系統 Crash,在 binlog_cache 中的所有 binlog 信息都會被 丟失。而當設置為“1”的時候,是最安全但是性能損耗最大的設置。因為當設置為 1 的時候,即使系統 Crash,也最多丟失 binlog_cache 中未完成的一個事務,對實際數據沒有任何實質性影響。從以往經驗 和相關測試來看,對于高并發事務的系統來說,“sync_binlog”設置為 0 和設置為 1 的系統寫入性能差 距可能高達 5 倍甚至更多。

大家都知道,MySQL 的復制(Replication),實際上就是通過將 Master 端的 Binlog 通過利用 IO 線 程通過網絡復制到 Slave 端,然后再通過 SQL 線程解析 Binlog 中的日志再應用到數據庫中來實現的。所 以,Binlog 量的大小對 IO 線程以及 Msater 和 Slave 端之間的網絡都會產生直接的影響。

MySQL 中 Binlog 的產生量是沒辦法改變的,只要我們的 Query 改變了數據庫中的數據,那么就必須 將該 Query 所對應的 Event 記錄到 Binlog 中。那我們是不是就沒有辦法優化復制了呢?當然不是,在 MySQL 復制環境中,實際上是是有 8 個參數可以讓我們控制需要復制或者需要忽略而不進行復制的 DB 或

者 Table ●

● ● ● ● ● ● ●

的,分別為:

Binlog_Do_DB:設定哪些數據庫(Schema)需要記錄 Binlog; Binlog_Ignore_DB:設定哪些數據庫(Schema)不要記錄 Binlog; Replicate_Do_DB:設定需要復制的數據庫(Schema),多個 DB 用逗號(“,”)分隔; Replicate_Ignore_DB:設定可以忽略的數據庫(Schema); Replicate_Do_Table:設定需要復制的 Table; Replicate_Ignore_Table:設定可以忽略的 Table;

Replicate_Wild_Do_Table:功能同 Replicate_Do_Table,但可以帶通配符來進行設置; Replicate_Wild_Ignore_Table:功能同 Replicate_Ignore_Table,可帶通配符設置;

通過上面這八個參數,我們就可以非常方便按照實際需求,控制從 Master 端到 Slave 端的 Binlog 量盡可能的少,從而減小 Master 端到 Slave 端的網絡流量,減少 IO 線程的 IO 量,還能減少 SQL 線程的 解析與應用 SQL 的數量,最終達到改善 Slave 上的數據延時問題。

實際上,上面這八個參數中的前面兩個是設置在 Master 端的,而后面六個參數則是設置在 Slave 端 的。雖然前面兩個參數和后面六個參數在功能上并沒有非常直接的關系,但是對于優化 MySQL 的 Replication 來說都可以啟到相似的功能。當然也有一定的區別,其主要區別如下:

● 如果在 Master 端設置前面兩個參數,不僅僅會讓 Master 端的 Binlog 記錄所帶來的 IO 量減少, 還會讓 Master 端的 IO 線程就可以減少 Binlog 的讀取量,傳遞給 Slave 端的 IO 線程的 Binlog 量自然就會較少。這樣做的好處是可以減少網絡 IO,減少 Slave 端 IO 線程的 IO 量,減少 Slave 端的 SQL 線程的工作量,從而最大幅度的優化復制性能。當然,在 Master 端設置也存在一定的 弊端,因為 MySQL 的判斷是否需要復制某個 Event 不是根據產生該 Event 的 Query 所更改的數據

所在的 DB,而是根據執行 Query 時刻所在的默認 Schema,也就是我們登錄時候指定的 DB 或者運 行“USE DATABASE”中所指定的 DB。只有當前默認 DB 和配置中所設定的 DB 完全吻合的時候 IO 線程才會將該 Event 讀取給 Slave 的 IO 線程。所以如果在系統中出現在默認 DB 和設定需要復制 的 DB 不一樣的情況下改變了需要復制的 DB 中某個 Table 的數據的時候,該 Event 是不會被復制 到 Slave 中去的,這樣就會造成 Slave 端的數據和 Master 的數據不一致的情況出現。同樣,如 果在默認 Schema 下更改了不需要復制的 Schema 中的數據,則會被復制到 Slave 端,當 Slave 端 并沒有該 Schema 的時候,則會造成復制出錯而停止;

● 而如果是在 Slave 端設置后面的六個參數,在性能優化方面可能比在 Master 端要稍微遜色一 點,因為不管是需要還是不需要復制的 Event 都被會被 IO 線程讀取到 Slave 端,這樣不僅僅增 加了網絡 IO 量,也給 Slave 端的 IO 線程增加了 Relay Log 的寫入量。但是仍然可以減少 Slave 的 SQL 線程在 Slave 端的日志應用量。雖然性能方面稍有遜色,但是在 Slave 端設置復制過濾機 制,可以保證不會出現因為默認 Schema 的問題而造成 Slave 和 Master 數據不一致或者復制出錯 的問題。

5.Slow Query Log 相關參數及使用建議

mysql> show variables like 'log_slow%';

+---------------------------+-------+

| Variable_name? ? ? ? ? ? ?| Value |

+---------------------------+-------+

| log_slow_admin_statements | ON? ? |

| log_slow_slave_statements | OFF? ?|

+---------------------------+-------+

2 rows in set (0.00 sec)

mysql> show variables like 'long_query%';

+-----------------+----------+

| Variable_name? ?| Value? ? |

+-----------------+----------+

| long_query_time | 2.000000 |

+-----------------+----------+

1 row in set (0.00 sec)

“ log_slow_queries”參數顯示了系統是否已經打開 Slow Query Log 功能,而 “long_query_time”參數則告訴我們當前系統設置的 Slow Query 記錄執行時間超過多長的 Query。在 MySQL AB 發行的 MySQL 版本中 Slow Query Log 可以設置的最短慢查詢時間為 1 秒,這在有些時候可能沒 辦法完全滿足我們的要求,如果希望能夠進一步縮短慢查詢的時間限制,可以使用 Percona 提供的 microslow-patch(件成為 msl Patch)來突破該限制。msl patch 不僅僅能將慢查詢時間減小到毫秒級 別,同時還能通過一些特定的規則來過濾記錄的 SQL,如僅記錄涉及到某個表的 Slow Query 等等附加功 能。考慮到篇幅問題,這里就不介紹 msl patch 給我們帶來的更為詳細的功能和使用,大家請參考官方 介 紹 ( http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch- installation-walk-through/)

6.Query Cache 的相關系統參數變量和狀態變量

mysql> show variables like '%query_cache%';

+------------------------------+----------+

| Variable_name? ? ? ? ? ? ? ? | Value? ? |

+------------------------------+----------+

| have_query_cache? ? ? ? ? ? ?| YES? ? ? |

| query_cache_limit? ? ? ? ? ? | 2097152? |

| query_cache_min_res_unit? ? ?| 4096? ? ?|

| query_cache_size? ? ? ? ? ? ?| 67108864 |

| query_cache_type? ? ? ? ? ? ?| OFF? ? ? |

| query_cache_wlock_invalidate | OFF? ? ? |

+------------------------------+----------+

6 rows in set (0.00 sec)

● ??“have_query_cache”:該 MySQL 是否支持 Query Cache;

● ??“query_cache_limit”:Query Cache 存放的單條 Query 最大 Result Set ,默認 1M;

● ??“query_cache_min_res_unit”:Query Cache 每個 Result Set 存放的最小內存大小,默認

4k;

● ??“query_cache_size”:系統中用于 Query Cache 內存的大小;

● ??“query_cache_type”:系統是否打開了 Query Cache 功能;

● ??“query_cache_wlock_invalidate”:針對于 MyISAM 存儲引擎,設置當有 WRITE LOCK 在某個

Table 上面的時候,讀請求是要等待 WRITE LOCK 釋放資源之后再查詢還是允許直接從 Query Cache 中讀取結果,默認為 FALSE(可以直接從 Query Cache 中取得結果)。

以上參數的設置主要是“query_cache_limit”和“query_cache_min_res_unit”兩個參數的設置需 要做一些針對于應用的相關調整。如果我們需要 Cache 的 Result Set 一般都很小(小于 4k)的話,可 以 適 當 將 “ query_cache_min_res_unit ” 參 數 再 調 小 一 些 , 避 免 造 成 內 存 的 浪 費 , “query_cache_limit”參數則不用調整。而如果我們需要Cache的 Result Set 大部分都大于4k的話, 則最好將“query_cache_min_res_unit”調整到和 Result Set 大小差不多,“query_cache_limit”的 參數也應大于 Result Set 的大小。當然,可能有些時候我們比較難準確的估算 Result Set 的大小, 那么當 Result Set 較大的時候,我們也并不是非得將“query_cache_min_res_unit”設置的和每個 Result Set 差不多大,是每個結果集的一半或者四分之一大小都可以,要想非常完美的完全不浪費任何 內存確實也是不可能做到的。

如果我們要了解Query Cache的使用情況,則可以通過Query Cache相關的狀態變量來獲取,如通過 如下命令:

mysql> show status like 'Qcache%';

+-------------------------+----------+

| Variable_name? ? ? ? ? ?| Value? ? |

+-------------------------+----------+

| Qcache_free_blocks? ? ? | 1? ? ? ? |

| Qcache_free_memory? ? ? | 67091112 |

| Qcache_hits? ? ? ? ? ? ?| 0? ? ? ? |

| Qcache_inserts? ? ? ? ? | 0? ? ? ? |

| Qcache_lowmem_prunes? ? | 0? ? ? ? |

| Qcache_not_cached? ? ? ?| 15023846 |

| Qcache_queries_in_cache | 0? ? ? ? |

| Qcache_total_blocks? ? ?| 1? ? ? ? |

+-------------------------+----------+

8 rows in set (0.00 sec)

● ??“Qcache_free_blocks”:Query Cache 中目前還有多少剩余的 blocks。如果該值顯示較大, 則說明 Query Cache 中的內存碎片較多了,可能需要尋找合適的機會進行整理()。

● ??“Qcache_free_memory”:Query Cache 中目前剩余的內存大小。通過這個參數我們可以較為準 確的觀察出當前系統中的 Query Cache 內存大小是否足夠,是需要增加還是過多了;

● ??“Qcache_hits”:多少次命中。通過這個參數我們可以查看到 Query Cache 的基本效果;

● ??“Qcache_inserts”:多少次未命中然后插入。通過“Qcache_hits”和“Qcache_inserts”兩

個參數我們就可以算出 Query Cache 的命中率了:

Query Cache 命中率 = Qcache_hits / ( Qcache_hits + Qcache_inserts );

● ??“Qcache_lowmem_prunes”:多少條 Query 因為內存不足而被清除出 Query Cache。通過 “Qcache_lowmem_prunes”和“Qcache_free_memory”相互結合,能夠更清楚的了解到我們系

統中Query Cache的內存大小是否真的足夠,是否非常頻繁的出現因為內存不足而有Query被換

出

● ??“Qcache_not_cached”:因為 query_cache_type 的設置或者不能被 cache 的 Query 的數量;

● ??“Qcache_queries_in_cache”:當前 Query Cache 中 cache 的 Query 數量;

● ??“Qcache_total_blocks”:當前 Query Cache 中的 block 數量;

Query Cache 的限制

Query Cache 由于存放的都是邏輯結構的 Result Set,而不是物理的數據頁,所以在性能提升的同

時,也會受到一些特定的限制。

a) ??5.1.17 之前的版本不能 Cache 幫定變量的 Query,但是從 5.1.17 版本開始,Query Cache 已經

開始支持幫定變量的 Query 了;

b) ??所有子查詢中的外部查詢 SQL 不能被 Cache;

c) ??在 Procedure,Function 以及 Trigger 中的 Query 不能被 Cache;

d) ??包 其他很多每次執行可能得到不一樣結果的函數的 Query 不能被 Cache。

總結

以上是生活随笔為你收集整理的mysql performance tuning_MySQL Performance tuning的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。