MySQL重點在於效能及相關分析指令詳解

一、MySQL效能監控關注點

QPS（每秒Query 量）：這裡的QPS 其實是指MySQL Server 每秒執行的Query總量：
QPS =  Queries / Seconds

TP交易量）： 在MySQL Server 中並沒有直接事務計數器，我們只能透過回滾和提交計數器來計算出系統的事務量。所以，我們需要透過以下方式來得到客戶端應用程式所要求的TPS 值：
TPS = (Com_commit + Com_rollback) / Seconds

Key Buffer 命中率：Key Buffer 命中率代表了MyISAM 類型表的索引的Cache命中率。此命中率的大小將直接影響MyISAM 類型表的讀寫效能。 Key Buffer 命
中率實際上包括讀取命中率和寫入命中率兩種，MySQL 中並沒有直接給出這兩個命中率的值，但是可以通過如下方式計算出來：
key_buffer_read_hits = (1 - Key_reads / Key_read_requests) * 100%
key_buffer_write_hits= (1 - Key_writes / Key_write_requests) * 100%

Innodb Buffer 命中率：這裡Inno


Innodb Buffer 命中率：這裡Inno

Innodb Buffer 命中率：這裡Innoguffer


Innodb Buffer 命中率：這裡Innodbuffer kufferx空間。類似Key buffer，我們

同樣可以根據My​​SQL Server 提供的對應狀態值計算出其命中率：🎜innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%aches🎜 ，那麼對Query Cache 命中率進行監控也是有必要的，因為他可能告訴我們是否在正確的使用Query Cache。 🎜Query Cache 命中率的計算方式如下：🎜Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%🎜🎜

Table Cache 狀態量：Table Cache 的目前狀態量可以幫助我們判斷系統參數table_open_cache 的設定是否合理。如果狀態變數Open_tables 與Opened_tables 之間的比率過低，則代表Table Cache 設定太小：
SHOW STATUS LIKE 'Open%';

Thread Cache 命中率：Thread Cache 命中率能夠直接反應出我們的系統參數thread_cache_size設定的是否合理。一個合理的thread_cache_size 參數能夠
節約大量建立新連線時所需要消耗的資源。
Thread Cache 命中率計算方式如下：
Thread_cache_hits = (1 - Threads_created / Connections) * 100%

鎖定狀態：鎖定狀態包括表鎖和行鎖兩種，我們可以透過系統鎖定變數造成其他執行緒等待的次數，以及鎖定等待時間資訊。
SHOW  STATUS  LIKE '%lock%';
透過鎖定相關的系統變量，我們可以得到表鎖總次數，其中造成其他現線程等待的次數。同時還可以得到非常詳細的行鎖信息，如行鎖總次數，行鎖總時間，每次行鎖等待時間，行鎖造成最大等待時間以及當前等待行鎖的線程數。透過對這些量的監控，我們可以清楚的了解到系統整體的鎖定是否嚴重。如當Table_locks_waited 與Table_locks_immediate 的比值較大，則表示我們的表格鎖定造成的阻斷較為嚴重，可能需要調整Query 語句，或變更儲存引擎，亦或需要調整業務邏輯。當然，具體改善方式必須根據實際場景來判斷。而Innodb_row_lock_waits 較大，表示Innodb 的行鎖定也較嚴重，且影響了其他執行緒的正常處理。同樣需要找出原因並解決。造成Innodb 行鎖定嚴重的原因可能是Query 語句所利用的索引不夠合理（Innodb 行鎖是基於索引來鎖定的），造成間隙鎖過大。也可能是系統本身處理能力有限，則需要從其他方面（如硬體設備）來考慮解決。

複製延遲量：複製延遲量將直接影響了Slave 資料庫處於不一致狀態的時間長短。
在Slave 節點上執行「SHOW SLAVE STATUS」指令，取Seconds_Behind_Master 項目的值來了解Slave 目前的延遲量（單位：秒）。

Tmp table 狀況：Tmp Table 的狀況主要是用於監控MySQL 使用臨時表的量是否過多，是否有臨時表過大而不得不從記憶體中換出到磁碟檔案上。臨時表使用狀態資訊可以透過以下方式取得：
SHOW STATUS LIKE 'Created_tmp%';
+-------------------------+-- -----+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
如果Created_tmp_tables 非常大，則可能是系統中排序操作過多，或是表連接方式不是很優化。而如果是Created_tmp_disk_tables Created_tmp_tables 的比率過高，如超過10%，則我們需要考慮是否tmp_table_size 這個系統參數所設定的足夠大。


Binlog Cache 使用狀況：Binlog Cache 用於存放尚未寫入磁碟的Binlog 資訊。
相關狀態變數如下：
SHOW STATUS LIKE 'Binlog_cache%'；
Binlog_cache_disk_use 值不為0，則表示Binlog Cache 大小可能不夠，可以增加binlog_cache_size 系統參數大小。

Innodb_log_waits 量：Innodb_log_waits 狀態變數直接反應出Innodb Log Buffer 空間不足造成等待的次數。
SHOW STATUS LIKE 'Innodb_log_waits';
該變數值發生的頻率將直接影響系統的寫入效能，所以當該值達到每秒1 次時就該增加系統參數innodb_log_buffer_size 的值，畢竟這是一個系統共用的緩存，適當增加並不會造成記憶體不足的問題。

二、性能分析指令詳解

SHOW STATUS;
FLUSH STATUS;

查看當前連接數SHOW STATUS LIKE 'Thread_%';
Thread_cached:被緩存的線程的數量
Thread_running：處於激活狀態的線程的個數：Thread_running的數量
Thread_created：總共被建立的執行緒的個數

Thread cache hits 
Thread_connected = SHOW GLOBAL STATUS LIKE Thread_created;
Connections = SHOW GLALConnectionConnection STATUS KEp. ) * 100

查看活動連接內容
SHOW PROCESSLIST;

如果TCH數小於90%,創建連接耗費了時間,增大Thread_cached數量

QPS （每秒查詢引擎STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
QPS=Questions/Uptime 

TPS（每秒傳輸的事物處理個數），即伺服器每秒處理的事務數，如果是InnoDB會會顯示，沒有InnoDB就不會顯示。

Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';
Uptime = SHOW GLOBAL STATUS LIKE 'UpUptime';一定要即時監控,如果接近架構搭建時的測試峰值,願上帝與你同在

Read/Writes Ratio
Qcache_hits = SHOW GLOBAL STATUS LIKE 'Qcache_hits';
Com_select = SHOW GLOBAL STATUS KE^ComAL STATs_Comh_select; =？ Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100

讀寫比,優化資料庫的重要依據,讀的多就去優化讀,寫的多就去優化寫
就去SHOW GLOBAL STATUS LIKE 'Slow_queries';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
SQPM=Slow_queries / (Uptime/60)

Slowconer /Questions Ratioio_sg; SHOW GLOBAL STATUS LIKE 'Questions';
S/Q=Slow_queries/Questions 

新版上線時要著重關注慢查詢

Full_join per minute
Select_fullfull_inSTAT; LIKE 'Uptime';
FJPM=Select_full_join / (Uptime/60)

沒有使用索引而造成的full_join,優化索引去吧

Innodb buffer read hits
Innodb_buffer_pool_ pool_read_requests = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
IFRH=(1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

InnoDB Bufferx95%-99% 25% 'Open_tables';
Opened_tables= SHOW GLOBAL STATUS LIKE 'Opened_tables';
table_cache= SHOW GLOBAL STATUS LIKE 'table_cache';

table_cache應該大於Open_tables 小於Opened_tables 
at 🜎 reated_tmp_tables = show global status like 'Created_tmp_tables';
Created_tmp_disk_tables = show global status like 'Created_tmp_disk_tables';

TDR=(Created_tmp_disk_tables/Created_tmp_tables)*100
＜
';

Innodb_row_lock_current_waits

The number of row locks currently being waited for 。 lock, in milliseconds. Added in MySQL 5.0.3.

Innodb_row_lock_time_max

The maximum time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.
toaIndeal propernodb_row_lock_wk wah. Added in MySQL 5.0.3.