mysql show status調優

通過show status 來最佳化MySQL 資料庫
關鍵字: mysql
1, 查看MySQL 伺服器配置資訊
1. mysql> show variables;
2, 查看MySQL 伺服器啟動並執行各種狀態值
1. mysql> show global status;
3, 慢查詢
1. mysql> show variables like '%slow%';
2. +------------------+-------+
3. | Variable_name | Value |
4. +------------------+-------+
5. | log_slow_queries | OFF |
6. | slow_launch_time | 2 |
7. +------------------+-------+
8. mysql> show global status like '%slow%';
9. +---------------------+-------+
10. | Variable_name | Value |
11. +---------------------+-------+
12. | Slow_launch_threads | 0 |
13. | Slow_queries | 279 |
14. +---------------------+-------+
配置中關閉了記錄慢查詢（最好是開啟，方便最佳化），超過2 秒即為慢查詢，一共有279 條慢查詢
4, 串連數
1. mysql> show variables like 'max_connections';
2. +-----------------+-------+
3. | Variable_name | Value |
4. +-----------------+-------+
5. | max_connections | 500 |
6. +-----------------+-------+
7.
8. mysql> show global status like 'max_used_connections';
9. +----------------------+-------+
10. | Variable_name | Value |
11. +----------------------+-------+
12. | Max_used_connections | 498 |
13. +----------------------+-------+
設定的最大串連數是500，而響應的串連數是498
max_used_connections / max_connections * 100% = 99.6% （理想值 85%）

5, key_buffer_size
key_buffer_size是對 MyISAM表效能影響最大的一個參數 ,不過資料庫中多為 Innodb
1.
mysql> show variables like
'key_buffer_size'
;
2.
+-----------------+----------+
3.
| Variable_name
| Value
|
4.
+-----------------+----------+
5.
| key_buffer_size |
67108864
|
6.
+-----------------+----------+
7.
8.
mysql> show global status like
'key_read%'
;
9.
+-------------------+----------+
10.
| Variable_name
| Value
|
11.
+-------------------+----------+
12.
| Key_read_requests |
25629497
|
13.
| Key_reads
|
66071
|
14.
+-------------------+----------+
一共有 25629497個索引讀取請求，有 66071個請求在記憶體中沒有找到直接從硬碟讀取索引，計算索引未命中緩衝的機率：
key_cache_miss_rate＝ Key_reads / Key_read_requests * 100% =0.27%
需要適當加大 key_buffer_size
1.
mysql> show global status like
'key_blocks_u%'
;
2.
+-------------------+-------+
3.
| Variable_name
| Value |
4.
+-------------------+-------+
5.
| Key_blocks_unused |
10285
|
6.
| Key_blocks_used
|
47705
|
7.
+-------------------+-------+
Key_blocks_unused表示未使用的緩衝簇 (blocks)數，Key_blocks_used表示曾經用到的最大的 blocks數 Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）
6,暫存資料表
1. ql>mysowshalobgl s ltustaeik p%'tmed_atcre' �
2. -+----------+---------------------+-�
3. | Vename_ablari |ealuV �
4. -+----------+---------------------+-�
5. | Cs |letabsk_dimp__ttedrea3371844 �
6. | Clesfimp__ttedrea | 1244 �
7. | Csbletamp__ttedrea | 0282154 �
8. -+----------+---------------------+-�
每次建立暫存資料表， Created_tmp_tables增加，如果是在磁碟上建立暫存資料表， Created_tmp_disk_tables也增加,Created_tmp_files表示
MySQL服務建立的臨時檔案檔案數：
Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 99% （理想值 <= 25%）
1. ql>mysowshesablriva erewh ename_ablariV in ( e'ize_sablp_ttm' , 'izee_sbl_taap_hemax' ;�

2. --+--------+--------------------+-�
3. | Vename_ablari| ueVal �
4. --+--------+--------------------+-�
5. | mze_siletabp_heaax_| 877221134 �
6. | tze_siletabmp_| 877221134 �
7. --+--------+--------------------+-�
需要增加 tmp_table_size
7,open table的情況
1. ql>mysowshalobgl s ltustaeik s%'letabn%ope' �
2. +-----+---------------+-�
3. | Vename_ablari| Vealu�
4. +-----+---------------+-�
5. | Oesbl_tapen| 0241 �
6. | Osbletaed_pen| 4651 �
7. +-----+---------------+-�
Open_tables 表示開啟表的數量， Opened_tables表示開啟過的表數量，如果 Opened_tables數量過大，說明配置中 table_cache(5.1.3之後這個值叫做 table_open_cache)值可能太小，我們查詢一下伺服器 table_cache值
1.
mysql> show variables like
'table_cache'
;
2.
+---------------+-------+
3.
| Variable_name | Value |
4.
+---------------+-------+
5.
| table_cache
|
1024
|
6.
+---------------+-------+
Open_tables / Opened_tables * 100% =69% 理想值（ >= 85%） Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)
8, 進程使用方式
1. ql>mysowshalobgl s ltustaeik d%'eaThr' �
2. -+------+-----------------+-�
3. | Vename_ablari | lueVa �
4. -+------+-----------------+-�
5. | Tedach_cadshre | 31 �
6. | Ttedeconn_cadshre | 923 �
7. | Tdterea_cadshre | 1429 �
8. | Tginunn_radshre | 4 �
9. -+------+-----------------+-�
如果我們在 MySQL伺服器設定檔中設定了 thread_cache_size，當用戶端斷開之後，伺服器處理此客戶的線程將會緩衝起來以響應下一個客戶而不是銷毀（前提是緩衝數未達上限）。 Threads_created表示建立過的線程數，如果發現 Threads_created值過大的話，表明 MySQL伺服器一直在建立線程，這也是比較耗資源，可以適當增加設定檔中 thread_cache_size值，查詢服務器 thread_cache_size配置：
1. ql>mysowshesablriva keli 'ize_schecaad_hre't �

2.
+-------------------+-------+
3.
| Variable_name
| Value |
4.
+-------------------+-------+
5.
| thread_cache_size |
32
|
6.
+-------------------+-------+
9, 查詢快取(query cache)
1.
mysql> show global status like
'qcache%'
;
2.
+-------------------------+----------+
3.
| Variable_name
| Value
|
4.
+-------------------------+----------+
5.
| Qcache_free_blocks
|
2226
|
6.
| Qcache_free_memory
|
10794944
|
7.
| Qcache_hits
|
5385458
|
8.
| Qcache_inserts
|
1806301
|
9.
| Qcache_lowmem_prunes
|
433101
|
10.
| Qcache_not_cached
|
4429464
|
11.
| Qcache_queries_in_cache |
7168
|
12.
| Qcache_total_blocks
|
16820
|
13.
+-------------------------+----------+
Qcache_free_blocks：緩衝中相鄰記憶體塊的個數。數目大說明可能有片段。 FLUSH QUERY CACHE會對緩衝中的片段進行整理，從而得到一個空閑塊。 Qcache_free_memory：緩衝中的空閑記憶體。 Qcache_hits：每次查詢在緩衝中命中時就增大 Qcache_inserts：每次插入一個查詢時就增大。叫用次數除以插入次數就是不中比率。 Qcache_lowmem_prunes：緩衝出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能片段非常嚴重，或者記憶體很少。 (上面的 free_blocks和 free_memory可以告訴您屬於哪種情況） Qcache_not_cached：不適合進行緩衝的查詢的數量，通常是由於這些查詢不是 SELECT語句或者用了 now()之類的函數。 Qcache_queries_in_cache：當前緩衝的查詢（和響應）的數量。 Qcache_total_blocks：緩衝中塊的數量。我們再查詢一下伺服器關於 query_cache的配置：
1.
mysql> show variables like
'query_cache%'
;
2.
+------------------------------+----------+
3.
| Variable_name
| Value
|
4.
+------------------------------+----------+
5.
| query_cache_limit
|
33554432
|
6.
| query_cache_min_res_unit
|
4096
|
7.
| query_cache_size
|
33554432
|
8.
| query_cache_type
| ON
|
9.
| query_cache_wlock_invalidate | OFF
|
10.
+------------------------------+----------+
各欄位的解釋： query_cache_limit：超過此大小的查詢將不緩衝 query_cache_min_res_unit：緩衝塊的最小大小 query_cache_size：查詢快取大小 query_cache_type：緩衝類型，決定緩衝什麼樣的查詢，樣本中表示不緩衝 select sql_no_cache查詢query_cache_wlock_invalidate：當有其他用戶端正在對 MyISAM表進行寫操作時，如果查詢在 query cache中，是否返回 cache結果還是等寫操作完成再讀表擷取結果。
query_cache_min_res_unit的配置是一柄 ”雙刃劍 ”，預設是 4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體片段和浪費。
查詢快取片段率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢快取片段率超過 20%，可以用 FLUSH QUERY CACHE整理緩衝片段，或者試試減小 query_cache_min_res_unit，如果你的查詢都是小資料量的話。查詢快取利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%查詢快取利用率在 25%以下的話說明 query_cache_size設定的過大，可適當減小；查詢快取利用率在 80％以上而且
Qcache_lowmem_prunes > 50的話說明 query_cache_size可能有點小，要不就是片段太多。查詢快取命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%樣本伺服器查詢快取片段率＝ 20.46％，查詢快取利用率＝ 62.26％，查詢快取命中率＝ 1.94％，命中率很差，可能寫操作比較
頻繁吧，而且可能有些片段。
10,排序使用方式
1. ql>mysowshalobgl s ltustaeik 't%sor' �
2. --+--------+-----------------+-�
3. | Vename_ablari | lueVa �
4. --+--------+-----------------+-�
5. | Ssesase_prg_meort | 3621 �
6. | Seng_raort | 88881 �
7. | Sws_roort | 11491835 �
8. | San_scort | 26955 �
9. --+--------+-----------------+-�
Sort_merge_passes 包括兩步。 MySQL首先會嘗試在記憶體中做排序，使用的記憶體大小由系統變數 Sort_buffer_size決定，如果它的大小不夠把所有的記錄都讀到記憶體中， MySQL 就會把每次在記憶體中排序的結果存到臨時檔案中，等 MySQL 找到所有記錄之後，再把臨時檔案中的記錄做一次排序。這再次排序就會增加 Sort_merge_passes。實際上， MySQL 會用另一個臨時檔案來存再次排序的結果，所以通常會看到 Sort_merge_passes 增加的數值是建臨時檔案數的兩倍。因為用到了臨時檔案，所以速度可能會比較慢，增加 Sort_buffer_size會減少 Sort_merge_passes 和建立臨時檔案的次數。但盲目的增加 Sort_buffer_size並不一定能提高速度，見 How fast can you sort data with MySQL?（引自 http://qroom.blogspot.com/2007/09/mysql-select-sort.html）
另外，增加 read_rnd_buffer_size(3.2.3是 record_rnd_buffer_size)的值對排序的操作也有一點的好處，參見： http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is- read_rnd_buffer_size/
11.檔案開啟數 (open_files)

1.
mysql> show global status like
'open_files'
;
2.
+---------------+-------+
3.
| Variable_name | Value |
4.
+---------------+-------+
5.
| Open_files
|
821
|
6.
+---------------+-------+
7.
8.
mysql> show variables like
'open_files_limit'
;
9.
+------------------+-------+
10.
| Variable_name
| Value |
11.
+------------------+-------+
12.
| open_files_limit |
65535
|
13.
+------------------+-------+
比較合適的設定： Open_files / open_files_limit * 100% <= 75％正常
12, 表鎖情況
1. ql>mysowshalobgl s ltustaeik s%'ck_loletab' �
2. --+-----+-----------------------+-�
3. | Vename_ablari| Vueal �
4. --+-----+-----------------------+-�
5. | Teiatmedimks_oce_labl| 4794254 �
6. | Tditewaks_oce_labl| 82512 �
7. --+-----+-----------------------+-�
Table_locks_immediate表示立即釋放表鎖數， Table_locks_waited表示需要等待的表鎖數，如果 Table_locks_immediate / Table_locks_waited > 5000，最好採用 InnoDB引擎，因為 InnoDB是行鎖而 MyISAM是表鎖，對於高並發寫入的應用 InnoDB效果會好些 .
13. 表掃描情況
1.
mysql> show global status like
'handler_read%'
;
2.
+-----------------------+-----------+
3.
| Variable_name
| Value
|
4.
+-----------------------+-----------+
5.
| Handler_read_first
|
108763
|
6.
| Handler_read_key
|
92813521
|
7.
| Handler_read_next
|
486650793
|
8.
| Handler_read_prev
|
688726
|
9.
| Handler_read_rnd
|
9321362
|
10.
| Handler_read_rnd_next |
153086384
|
11.
+-----------------------+-----------+
各欄位解釋參見 http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，調出伺服器完成的查詢請求次數：
1. ql>mysowshalobgl s ltustaeik 'ctele_scom' �
2. --+-----+---------------+-�

3. | Vename_ablari| Vealu �
4. --+-----+---------------+-�
5. | Ctecselom_| 7146932 �
6. --+-----+---------------+-�
計算表掃描率：
表掃描率 ＝ Handler_read_rnd_next / Com_select
如果表掃描率超過 4000，說明進行了太多表掃描，很有可能索引沒有建好，增加 read_buffer_size值會有一些好處，但最好不要超過 8MB。