MySQL的效能指標計算和最佳化方法

標籤：隨機   5.4   好的   handle   oba   rev   計算   選項   佔用   

MySQL的效能指標計算和最佳化方法
1 QPS計算(每秒查詢數)

針對MyISAM引擎為主的DB

mysql> show global status like ‘questions‘;
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Questions | 2805815665 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like ‘uptime‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 17115114 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> select 2805815665/17115114;
+---------------------+
| 2805815665/17115114 |
+---------------------+
| 163.9379 |
+---------------------+
1 row in set (0.00 sec)
QPS=questions/uptime=163，mysql自啟動以來的平均QPS，如果要計算某一時間段內的QPS，可在高峰期間擷取間隔時間t2-t1，然後分別計算出t2和t1時刻的q值，QPS=(q2-q1)/(t2-t1)

針對InnnoDB引擎為主的DB
mysql> show global status like ‘com_update‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 78299864 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like ‘com_insert‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_insert | 80467605 |
+---------------+----------+
1 row in set (0.01 sec)
mysql> show global status like ‘com_select‘;
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 3366989053 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like ‘com_delete‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_delete | 36038170 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> show global status like ‘uptime‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 17115204 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> select (78299864+80467605+36038170+3366989053)/78299864;
+--------------------------------------------------+
| (78299864+80467605+36038170+3366989053)/78299864 |
+--------------------------------------------------+
| 45.4892 |
+--------------------------------------------------+
1 row in set (0.00 sec)
QPS=(com_update+com_insert+com_delete+com_select)/uptime=45,某一時間段內的QPS查詢方法同上。

2 TPS計算(每秒事務數)
mysql> show global status like ‘com_commit‘;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_commit | 510648259 |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> show global status like ‘com_rollback‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_rollback | 95227537 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> show global status like ‘uptime‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 17115356 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> select (510648259+95227537)/17115356;
+-------------------------------+
| (510648259+95227537)/17115356 |
+-------------------------------+
| 35.3995 |
+-------------------------------+
1 row in set (0.00 sec)
TPS=(com_commit+com_rollback)/uptime=35
3 線程串連數和命中率
mysql> show global status like ‘threads_%‘;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 74 |//代表當前此時此刻線程緩衝中有多少空閑線程
| Threads_connected | 106 |//代表當前已建立串連的數量，因為一個串連就需要一個線程，所以也可以看成當前被使用的線程數
| Threads_created | 16760 |//代表從最近一次服務啟動，已建立線程的數量
| Threads_running | 6 |//代表當前啟用的（非睡眠狀態）線程數
+-------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like ‘connections‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Connections | 18352277 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> select 1-16760/18352277;
+------------------+
| 1-16760/18352277 |
+------------------+
| 0.9991 |
+------------------+
1 row in set (0.00 sec)

線程快取命中率=1-Threads_created/Connections = 99.991%

設定線程緩衝個數
mysql> show variables like ‘%thread_cache_size%‘;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 128 |
+-------------------+-------+
1 row in set (0.00 sec)
根據Threads_connected可預估thread_cache_size值應該設定多大，一般來說250是一個不錯的上限值，如果記憶體足夠大，也可以設定成thread_cache_size值和threads_connected值相同；

或者通過觀察threads_created值，如果該值很大或一直在增長，可以適當增加thread_cache_size的值；在休眠狀態下每個線程大概佔用256KB左右的記憶體，所以當記憶體足夠時，設定太小也不會節約太多記憶體，除非該值已經超過幾千。

4 表緩衝
mysql> show global status like ‘open_tables%‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 1822 |
+---------------+-------+
1 row in set (0.00 sec)

設定開啟表的緩衝和表定義緩衝
mysql> show variables like ‘table_open_cache‘;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 3000 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like ‘table_defi%‘;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 1400 |
+------------------------+-------+
1 row in set (0.01 sec)

針對MyISAM：

mysql每開啟一個表，都會讀入一些資料到table_open_cache 緩衝 中，當mysql在這個緩衝中找不到相應的資訊時，才會去磁碟上直接讀取，所以該值要設定得足夠大以避免需要重新開啟和重新解析表的定義，一般設定為max_connections的10倍，但最好保持在10000以內。
還有種依據就是根據狀態open_tables的值進行設定，如果發現open_tables的值每秒變化很大，那麼可能需要增大table_open_cache的值。
table_definition_cache 通常簡單設定為伺服器中存在的表的數量，除非有上萬張表。

針對InnoDB：
與MyISAM不同，InnoDB的open table和open file並無直接聯絡，即開啟frm表時其相應的ibd檔案可能處於關閉狀態；

故InnoDB只會用到table_definiton_cache，不會使用table_open_cache；
其frm檔案儲存於table_definition_cache中，而idb則由innodb_open_files決定(前提是開啟了innodb_file_per_table)，最好將innodb_open_files設定得足夠大，使得伺服器可以保持所有的.ibd檔案同時開啟。

5 最大串連數

mysql> show global status like ‘Max_used_connections‘;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1004 |
+----------------------+-------+
1 row in set (0.00 sec)
設定max_connections大小

mysql> show variables like ‘max_connections%‘;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in set (0.00 sec)
通常max_connections的大小應該設定為比Max_used_connections狀態值大，Max_used_connections狀態值反映伺服器串連在某個時間段是否有尖峰，如果該值大於max_connections值，代表用戶端至少被拒絕了一次，可以簡單地設定為符合以下條件：Max_used_connections/max_connections=0.8

6 Innodb 快取命中率
mysql> show global status like ‘innodb_buffer_pool_read%‘;
+---------------------------------------+---------------+
| Variable_name | Value |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 730802 |//預讀的頁數
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 3801141425753 |//從緩衝池中讀取的次數
| Innodb_buffer_pool_reads | 2755728 |//表示從物理磁碟讀取的頁數
+---------------------------------------+---------------+
5 rows in set (0.00 sec)
mysql> select 3801141425753/(3801141425753+730802+2755728);
+----------------------------------------------+
| 3801141425753/(3801141425753+730802+2755728) |
+----------------------------------------------+
| 1.0000 |
+----------------------------------------------+
1 row in set (0.00 sec)

緩衝池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=100%

如果該值小於99.9%，建議就應該增大innodb_buffer_pool_size的值了，該值一般設定為記憶體總大小的75%-85%，或者計算出作業系統所需緩衝+mysql每個串連所需的記憶體（例如排序緩衝和暫存資料表）+MyISAM金鑰快取，剩下的記憶體都給innodb_buffer_pool_size，不過也不宜設定太大，會造成記憶體的頻繁交換，預熱和關閉時間長等問題。

7 MyISAM Key Buffer命中率和緩衝區使用率

mysql> show global status like ‘key_%‘;
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6692 |
| Key_blocks_used | 6698 |
| Key_read_requests | 51726731594 |
| Key_reads | 5423302 |
| Key_write_requests | 11733536354 |
| Key_writes | 77164219 |
+------------------------+-------------+
7 rows in set (0.00 sec)
mysql> show variables like ‘%key_cache_block_size%‘;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| key_cache_block_size | 1024 |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like ‘%key_buffer_size%‘;
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)
緩衝區的使用率=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size)=18.31%

讀命中率=1-Key_reads /Key_read_requests=99.99%

寫命中率=1-Key_writes / Key_write_requests =99.34%

可看到緩衝區的使用率並不高，如果很長一段時間後還沒有使用完所有的鍵緩衝，可以考慮把緩衝區調小一點。

金鑰快取命中率可能意義不大，因為它和應用相關，有些應用在95%的命中率下就工作良好，有些則需要99.99%，所以從經驗上看，每秒的緩衝未叫用次數更重要，假設一個獨立磁碟每秒能做100個隨機讀，那麼每秒有5個緩衝未命中可能不會導致I/O繁忙，但每秒80個就可能出現問題。

每秒緩衝未命中=Key_reads/uptime=0.33

8 暫存資料表使用方式

mysql> show global status like ‘Created_tmp%‘;
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Created_tmp_disk_tables | 19226325 |
| Created_tmp_files | 117 |
| Created_tmp_tables | 56265812 |
+-------------------------+----------+
3 rows in set (0.00 sec)

mysql> show variables like ‘%tmp_table_size%‘;
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
1 row in set (0.00 sec)
可看到總共建立了56265812 張暫存資料表，其中有19226325 張涉及到了磁碟IO，大概比例佔到了0.34，證明資料庫應用中排序，join語句涉及的資料量太大，需要最佳化SQL或者增大tmp_table_size的值，我設的是64M。該比值應該控制在0.2以內。

9 binlog cache使用方式
mysql> show status like ‘Binlog_cache%‘;
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Binlog_cache_disk_use | 645946 |
| Binlog_cache_use | 61175970 |
+-----------------------+----------+
2 rows in set (0.01 sec)
mysql> show variables like ‘binlog_cache_size‘;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)
Binlog_cache_disk_use表示因為我們binlog_cache_size設計的記憶體不足導致緩衝二進位日誌用到了臨時檔案的次數

Binlog_cache_use 表示 用binlog_cache_size緩衝的次數

當對應的Binlog_cache_disk_use 值比較大的時候 我們可以考慮適當的調高 binlog_cache_size 對應的值

10 Innodb log buffer size的大小設定

mysql> show variables like ‘%innodb_log_buffer_size%‘;
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> show status like ‘innodb_log_waits‘;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_log_waits | 0 |
+------------------+-------+
1 row in set (0.00 sec)
innodb_log_buffer_size設定為8M，應該足夠大了；Innodb_log_waits表示因log buffer不足導致等待的次數，如果該值不為0，可以適當增大innodb_log_buffer_size的值。

11 表掃描情況判斷

mysql> show global status like ‘Handler_read%‘;
+-----------------------+---------------+
| Variable_name | Value |
+-----------------------+---------------+
| Handler_read_first | 121257954 |
| Handler_read_key | 79172593389 |
| Handler_read_last | 1192685 |
| Handler_read_next | 1397568482926 |
| Handler_read_prev | 48594574644 |
| Handler_read_rnd | 2584877785 |
| Handler_read_rnd_next | 1083694866736 |
+-----------------------+---------------+
7 rows in set (0.00 sec)
Handler_read_first：使用索引掃描的次數，該值大小說不清系統效能是好是壞
Handler_read_key：通過key進行查詢的次數，該值越大證明系統效能越好
Handler_read_next：使用索引進行排序的次數
Handler_read_prev：此選項表明在進行索引掃描時，按照索引倒序從資料檔案裡取資料的次數，一般就是ORDER BY ... DESC
Handler_read_rnd：該值越大證明系統中有大量的沒有使用索引進行排序的操作，或者join時沒有使用到index
Handler_read_rnd_next：使用資料檔案進行掃描的次數，該值越大證明有大量的全表掃描，或者合理地建立索引，沒有很好地利用已經建立好的索引

12 Innodb_buffer_pool_wait_free

mysql> show global status like ‘Innodb_buffer_pool_wait_free‘;
+------------------------------+-------+
| Variable_name | Value | |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
+------------------------------+-------+
1 row in set (0.00 sec)
該值不為0表示buffer pool沒有閒置空間了，可能原因是innodb_buffer_pool_size設定太大，可以適當減少該值。

13 join操作資訊

mysql> show global status like ‘select_full_join‘;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Select_full_join | 10403 |
+------------------+-------+
1 row in set (0.00 sec)
該值表示在join操作中沒有使用到索引的次數，值很大說明join語句寫得很有問題

mysql> show global status like ‘select_range‘;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Select_range | 22450380 |
+---------------+----------+
1 row in set (0.00 sec)
該值表示第一個表使用ranges的join數量，該值很大說明join寫得沒有問題，通常可查看select_full_join和select_range的比值來判斷系統中join語句的效能情況

mysql> show global status like ‘Select_range_check‘;
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Select_range_check | 0 |
+--------------------+-------+
1 row in set (0.00 sec)
如果該值不為0需要檢查表的索引是否合理，表示在表n+1中重新評估表n中的每一行的索引是否開銷最小所做的聯結數，意味著表n+1對該聯結而言並沒有有用的索引。

mysql> show GLOBAL status like ‘select_scan‘;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Select_scan | 116037811 |
+---------------+-----------+
1 row in set (0.00 sec)
select_scan表示掃描第一張表的串連數目，如果第一張表中每行都參與聯結，這樣的結果並沒有問題；如果你並不想要返回所有行但又沒有使用到索引來尋找到所需要的行，那麼計數很大就很糟糕了。

14 慢查詢

mysql> show global status like ‘Slow_queries‘;
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Slow_queries | 114111 |
+---------------+--------+
1 row in set (0.00 sec)
該值表示mysql啟動以來的慢查詢個數，即執行時間超過long_query_time的次數，可根據Slow_queries/uptime的比值判斷單位時間內的慢查詢個數，進而判斷系統的效能。

15表鎖資訊

mysql> show global status like ‘table_lock%‘;
+-----------------------+------------+
| Variable_name | Value |
+-----------------------+------------+
| Table_locks_immediate | 1644917567 |
| Table_locks_waited | 53 |
+-----------------------+------------+
2 rows in set (0.00 sec)
這兩個值的比值：Table_locks_waited /Table_locks_immediate 趨向於0，如果值比較大則表示系統的鎖阻塞情況比較嚴重

MySQL的效能指標計算和最佳化方法