MySQL資料庫伺服器逐漸層慢分析

標籤：style   blog   io   color   ar   os   使用   for   sp   

第一步 檢查系統的狀態

1.1 使用sar來檢查作業系統是否存在IO問題

#sar -u 2 10 — 即每隔2秒檢察一次，共執行20次。

     

[[email protected] tester]# sar -u 2 1010:51:22 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle10:51:24 AM     all     13.74      0.00      5.60     42.49      0.00     38.1710:51:26 AM     all     20.15      0.00     11.99     43.37      0.00     24.4910:51:28 AM     all     14.07      0.00      9.72     56.78      0.00     19.4410:51:30 AM     all     37.24      0.00     11.99     30.61      0.00     20.1510:51:32 AM     all     33.25      0.00     10.23     44.50      0.00     12.0210:51:34 AM     all     22.76      0.00     12.28     49.10      0.00     15.8610:51:36 AM     all     17.97      0.00      8.10     43.29      0.00     30.6310:51:38 AM     all     19.44      0.00      9.21     53.20      0.00     18.1610:51:40 AM     all     26.85      0.00     12.53     38.36      0.00     22.2510:51:42 AM     all     22.25      0.00      9.21     42.46      0.00     26.09Average:        all     22.77      0.00     10.08     44.41      0.00     22.74

其中：
%usr指的是使用者進程使用的cpu資源的百分比；
%sys指的是系統資源使用cpu資源的百分比；
%wio指的是等待io完成的百分比，這是值得觀注的一項；
%idle即閒置百分比。
如果wio列的值很大，如在35%以上，說明系統的IO存在瓶頸，CPU花費了很大的時間去等待I/O的完成。Idle很小說明系統CPU很忙。像以上的樣本，可以看到wio平均值為11，說明I/O沒什麼特別的問題，而idle值為零，說明cpu已經滿負荷運行了。

1.2 使用vmstat監控記憶體 cpu資源

# vmstat

[[email protected] tester]# vmstatprocs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st 0  5      0 2654300 181588 2070476    0    0     0    31    4    0  0  0 99  0  0

vmstat 的輸出那些資訊值得關注?
io bo: 磁碟寫的資料量稍大，如果是大檔案的寫，10M以內基本不用擔心，如果是小檔案寫2M以內基本正常

 

1.3 使用iostat查看io讀寫情況

#iostat -x -k -d 1 10 

-d 採樣時間為1秒，採樣2次

-x 顯示更詳細的io裝置統計資訊

-k 以kB為單位顯示讀寫資訊

linux # iostat -x -k -d 1 10Linux 2.6.16.60-0.21-smp (linux)     06/13/12……Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %utilsda               0.00  9915.00    1.00   90.00     4.00 34360.00   755.25    11.79  120.57   6.33  57.60

以上各列的含義如下：

• rrqm/s: 每秒對該裝置的讀請求被合并次數，檔案系統會對讀取同塊(block)的請求進行合并
• wrqm/s: 每秒對該裝置的寫請求被合并次數
• r/s: 每秒完成的讀次數
• w/s: 每秒完成的寫次數
• rkB/s: 每秒讀資料量(kB為單位)
• wkB/s: 每秒寫資料量(kB為單位)
• avgrq-sz:平均每次IO操作的資料量(扇區數為單位)
• avgqu-sz: 平均等待處理的IO請求隊列長度
• await: 平均每次IO請求等待時間(包括等待時間和處理時間，毫秒為單位)
• svctm: 平均每次IO請求的處理時間(毫秒為單位)
• %util: 採用周期內用於IO操作的時間比率，即IO隊列非空的時間比率

 

對於以上樣本輸出，我們可以擷取到以下資訊：

1. 每秒向磁碟上寫30M左右資料(wkB/s值)
2. 每秒有91次IO操作(r/s+w/s)，其中以寫操作為主體
3. 平均每次IO請求等待處理的時間為120.57毫秒，處理耗時為6.33毫秒
4. 等待處理的IO請求隊列中，平均有11.79個請求駐留

1.4網路問題

telnet一下MySQL對外開放的連接埠，如果不通的話，看看防火牆是否正確設定了。

看看MySQL是不是開啟了skip-networking的選項，如果開啟請關閉。

 

第二步 檢查mysql參數

2.1.1 max_connect_errors

max_connect_errors預設值為10，如果受信帳號錯誤串連次數達到10則自動堵塞，需要flush hosts來解除。如果你得到象這樣的一個錯誤：
Host ’hostname’ is blocked because of many connection errors.
Unblock with ’mysqladmin flush-hosts’
這意味著，mysqld已經得到了大量(max_connect_errors)的主機’hostname’的在中途被中斷了的串連請求。在max_connect_errors次失敗請求後，mysqld認定出錯了(象來字一個駭客的攻擊)，並且阻止該網站進一步的串連，直到某人執行命令mysqladmin flush-hosts。
內網串連的話，建議設定在10000以上，已避免堵塞，並定期flush hosts。

2.1.2 connect_timeout

指定MySQL服務等待應答一個串連報文的最大秒數，超出該時間，MySQL向用戶端返回 bad handshake。預設值是5秒，在內網高並發環境中建議設定到10-15秒，以便避免bad hand shake。建議同時關注thread_cache_size並設定thread_cache_size為非0值，大小具體調整。

2.1.3 skip-name-resolve
skip-name-resolve能大大加快使用者獲得串連的速度，特別是在網路情況較差的情況下。MySQL在收到串連請求的時候，會根據請求包中獲得的ip來反向追查要求者的主機名稱。然後再根據返回的主機名稱又一次去擷取ip。如果兩次獲得的ip相同，那麼串連就成功建立了。在DNS不穩定或者區域網路內主機過多的情況下，一次成功的串連將會耗費很多不必要的時間。假如MySQL伺服器的ip地址是廣域網路的，最好不要設定skip-name-resolve。

2.1.4 slave-net-timeout=seconds
參數含義：當slave從主要資料庫讀取log資料失敗後，等待多久重建立立串連並擷取資料。預設值是3600秒，如果需要保證同步性，如此NC的參數請極力控制在10秒以下。

2.1.5 master-connect-retry
參數含義：當重建立立主從串連時，如果串連建立失敗，間隔多久後重試。預設是60秒，請按照合理的情況去設定參數。

 

第三步 檢查mysql 相關狀態值

3.1關注串連數
如果串連數達到了最大串連數，那不管有多少資源，使用者都會阻塞在外面。
修改mysql最大串連數：
開啟my.ini，修改max_connections=100(預設為100)。

mysql> show variables like ‘max_connections‘;

請根據硬體情況調整到合適的大小，一般經驗值可設為3000。Windows伺服器大概支援量為1500-1800個串連，linux伺服器可以支援到8000個左右。
請將max_user_connections設0——–這個0代表不限制單使用者的最大串連數，其最大串連值可以等於max_connections值。
mysql> show global status like ‘Max_used_connections‘;
檢查下最大的過往使用串連數，這個值在max_connections的85%左右是比較合適的，如果過高則是max_connections過少或者系統負荷過高了。

 

3.1.1 mysqladmin -uroot status

mysql> status;--------------Connection id:          1Current database:Current user:           [email protected]SSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ‘‘Using delimiter:        ;Server version:         5.5.39-log MySQL Community Server (GPL)Protocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    latin1Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /tmp/mysql.sockUptime:                 2 min 17 secThreads: 1  Questions: 9  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.065

3.1.2 show full processlist   (show processlist state 只顯示100條，顯示全部用 show full processlist )

1.顯示所有進程

mysql> show full processlist;+—–+——+———–+——+———+——+——-+———————–+| Id  | User | Host      | db   | Command | Time | State | Info                  |+—–+——+———–+——+———+——+——-+———————–+| 629 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist || 633 | root | localhost | NULL | Sleep   |   11 |       | NULL                  |+—–+——+———–+——+———+——+——-+———————–+2 rows in set (0.00 sec)

2.如果正在啟動並執行語句太多，已耗用時間太長，表示MySQL效率有問題。必要的時候可以將對應的進程kill掉。

殺死休眠的進程kill ID號
mysql> kill 633;
Query OK, 0 rows affected (0.00 sec)

 

3.2 關注慢查詢（slow query）日誌
日誌必然會拖慢系統速度，特別是CPU資源，所以如果CPU資源充分，可以一直開啟，如果不充足，那就在需要調整的時候，或者在replication從伺服器上開啟（針對select）
mysql> show variables like ‘%slow%’;
+———————+—————————————-+
| Variable_name       | Value                                  |
+———————+—————————————-+
| log_slow_queries    | OFF                                    |
| slow_launch_time    | 2                                      |
| slow_query_log      | OFF                                    |
| slow_query_log_file | /data0/mysql/3306/data/mysql1-slow.log |
+———————+—————————————-+
4 rows in set (0.00 sec)
 
mysql> set  GLOBAL slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

3.2.1關注慢查詢涉及的表的相關狀態
1.       表內記錄數。盡量控制在500萬行以內（有索引），建議控制在200萬行
2.       表內索引的使用。
3.       表如果update，delete，insert頻繁，可以考慮optimize table最佳化下檔案存放，索引，儲存空間。
4.       表內update，insert，delete查詢的鎖定時間。
5.       select for update如果條件欄位無索引的話，會引起的是鎖全表而不是行鎖，請關注。
6.       如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。

 

MySQL資料庫伺服器逐漸層慢分析