mysql突然慢了怎么辦？（算是原創(chuàng)吧）

pkman110

如果mysql系統(tǒng)突然慢了怎么辦？
Write by Saver.Li
mail:pkman110@163.com
QQ:792218
歡迎愛(ài)好mysql的朋友完善該文檔，歡迎轉(zhuǎn)載,完善后mail一份給我就好，嘿嘿。
第一步 檢查系統(tǒng)的狀態(tài)        2
1.1 使用sar來(lái)檢查操作系統(tǒng)是否存在IO問(wèn)題        2
1.2 使用vmstat監(jiān)控內(nèi)存 cpu資源        2
1.2.1 CPU問(wèn)題        3
1.2.2內(nèi)存問(wèn)題        3
1.3磁盤(pán)IO問(wèn)題        3
1.4網(wǎng)絡(luò)問(wèn)題        3
第二步 檢查mysql參數(shù)        3
2.1 幾個(gè)不被注意的mysql參數(shù)        3
2.1.1 max_connect_errors        3
2.1.2 connect_timeout        4
2.1.3 skip-name-resolve        4
2.1.4 slave-net-timeout=seconds        4
2.1.5 master-connect-retry        4
第三步 檢查mysql 相關(guān)狀態(tài)值        4
3.1關(guān)注連接數(shù)        4
3.1.1 mysqladmin -uroot status        5
3.1.2 show full processlist        5
3.1.3使用mysqlreport關(guān)注Connections，Threads        5
3.2關(guān)注下系統(tǒng)鎖情況        6
3.2.1 mysql> show status like '%lock%';        6
3.2.2使用mysqlreport關(guān)注Table Locks，InnoDB Lock        7
3.3 關(guān)注慢查詢（slow query）日志        7
3.3.1關(guān)注慢查詢涉及的表的相關(guān)狀態(tài)        8
3.3.2定期分析表        8
3.3.3使用optimize table        8
附錄：        9
一.Sar命令獲得        9
在linux中使用sar調(diào)優(yōu)系統(tǒng)性能        10
二．vmstat命令輸出分成六個(gè)部分：        15
三．根據(jù)mysql狀態(tài)調(diào)整系統(tǒng)參數(shù)        16



第一步 檢查系統(tǒng)的狀態(tài)
通過(guò)操作系統(tǒng)的一些工具檢查系統(tǒng)的狀態(tài)，比如CPU、內(nèi)存、交換、磁盤(pán)的利用率，根據(jù)經(jīng)驗(yàn)或與系統(tǒng)正常時(shí)的狀態(tài)相比對(duì)，有時(shí)系統(tǒng)表面上看起來(lái)看空閑，這也可能不是一個(gè)正常的狀態(tài)，因?yàn)閏pu可能正等待IO的完成。除此之外，還應(yīng)觀注那些占用系統(tǒng)資源(cpu、內(nèi)存)的進(jìn)程。
1.1 使用sar來(lái)檢查操作系統(tǒng)是否存在IO問(wèn)題
#sar -u 2 10 -- 即每隔2秒檢察一次，共執(zhí)行20次。
結(jié)果示例：
注：在redhat下，%system就是所謂的%wio。
Linux 2.4.21-20.ELsmp (YY075) 05/19/2005
10:36:07 AM CPU %user %nice %system %idle
10:36:09 AM all 0.00 0.00 0.13 99.87
10:36:11 AM all 0.00 0.00 0.00 100.00
10:36:13 AM all 0.25 0.00 0.25 99.49
10:36:15 AM all 0.13 0.00 0.13 99.75
10:36:17 AM all 0.00 0.00 0.00 100.00
其中：
Ø %usr指的是用戶進(jìn)程使用的cpu資源的百分比；
Ø %sys指的是系統(tǒng)資源使用cpu資源的百分比；
Ø %wio指的是等待io完成的百分比，這是值得觀注的一項(xiàng)；
Ø %idle即空閑的百分比。
如果wio列的值很大，如在35%以上，說(shuō)明系統(tǒng)的IO存在瓶頸，CPU花費(fèi)了很大的時(shí)間去等待I/O的完成。Idle很小說(shuō)明系統(tǒng)CPU很忙。像以上的示例，可以看到wio平均值為11，說(shuō)明I/O沒(méi)什么特別的問(wèn)題，而idle值為零，說(shuō)明cpu已經(jīng)滿負(fù)荷運(yùn)行了。
1.2 使用vmstat監(jiān)控內(nèi)存 cpu資源
[root@mysql1 ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0     72  25428  54712 672264    0    0    14    43   53   59  1  1 98  0  0　

vmstat 的輸出那些信息值得關(guān)注?
　　io bo: 磁盤(pán)寫(xiě)的數(shù)據(jù)量稍大，如果是大文件的寫(xiě)，10M以內(nèi)基本不用擔(dān)心，如果是小文件寫(xiě)2M以內(nèi)基本正常

1.2.1 CPU問(wèn)題
下面幾列需要被察看，以確定cpu是否有問(wèn)題
Processes in the run queue (procs r)
User time (cpu us)
System time (cpu sy)
Idle time (cpu id)

問(wèn)題情況:
1.) 如果processes in run queue (procs r)的數(shù)量遠(yuǎn)大于系統(tǒng)中cpu的數(shù)量，將會(huì)使系統(tǒng)便慢。
2.) 如果這個(gè)數(shù)量是cpu的4倍的話，說(shuō)明系統(tǒng)正面臨cpu能力短缺,這將使系統(tǒng)運(yùn)行速度大幅度降低
3.) 如果cpu的idle時(shí)間經(jīng)常為0的話，或者系統(tǒng)占用時(shí)間(cpu sy)是用戶占用時(shí)間(cpu us)兩輩的話，系統(tǒng)面臨缺少cpu資源
解決方案 :
解決這些情況，涉及到調(diào)整應(yīng)用程序，使其能更有效的使用cpu，同時(shí)增加cpu的能力或數(shù)量
1.2.2內(nèi)存問(wèn)題
主要查看頁(yè)導(dǎo)入的數(shù)值（swap中的si），如果該值比較大就要考慮內(nèi)存，大概方法如下：
1).最簡(jiǎn)單的，加大RAM　　　
2).減少RAM的需求
1.3磁盤(pán)IO問(wèn)題
處理方式：做raid10提高性能
1.4網(wǎng)絡(luò)問(wèn)題
telnet一下MySQL對(duì)外開(kāi)放的端口，如果不通的話，看看防火墻是否正確設(shè)置了。另外，看看MySQL是不是開(kāi)啟了skip-networking的選項(xiàng)，如果開(kāi)啟請(qǐng)關(guān)閉。
第二步 檢查mysql參數(shù)
2.1 幾個(gè)不被注意的mysql參數(shù)
2.1.1 max_connect_errors
max_connect_errors默認(rèn)值為10，如果受信帳號(hào)錯(cuò)誤連接次數(shù)達(dá)到10則自動(dòng)堵塞，需要flush hosts來(lái)解除。如果你得到象這樣的一個(gè)錯(cuò)誤：
Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
這意味著，mysqld已經(jīng)得到了大量(max_connect_errors)的主機(jī)'hostname'的在中途被中斷了的連接請(qǐng)求。在max_connect_errors次失敗請(qǐng)求后，mysqld認(rèn)定出錯(cuò)了(象來(lái)字一個(gè)黑客的攻擊)，并且阻止該站點(diǎn)進(jìn)一步的連接，直到某人執(zhí)行命令mysqladmin flush-hosts。
內(nèi)網(wǎng)連接的話，建議設(shè)置在10000以上，已避免堵塞，并定期flush hosts。
2.1.2 connect_timeout
指定MySQL服務(wù)等待應(yīng)答一個(gè)連接報(bào)文的最大秒數(shù)，超出該時(shí)間，MySQL向客戶端返回 bad handshake。默認(rèn)值是5秒，在內(nèi)網(wǎng)高并發(fā)環(huán)境中建議設(shè)置到10-15秒，以便避免bad hand shake。建議同時(shí)關(guān)注thread_cache_size并設(shè)置thread_cache_size為非0值，大小具體調(diào)整。
2.1.3 skip-name-resolve
skip-name-resolve能大大加快用戶獲得連接的速度，特別是在網(wǎng)絡(luò)情況較差的情況下。MySQL在收到連接請(qǐng)求的時(shí)候，會(huì)根據(jù)請(qǐng)求包中獲得的ip來(lái)反向追查請(qǐng)求者的主機(jī)名。然后再根據(jù)返回的主機(jī)名又一次去獲取ip。如果兩次獲得的ip相同，那么連接就成功建立了。在DNS不穩(wěn)定或者局域網(wǎng)內(nèi)主機(jī)過(guò)多的情況下，一次成功的連接將會(huì)耗費(fèi)很多不必要的時(shí)間。假如MySQL服務(wù)器的ip地址是廣域網(wǎng)的，最好不要設(shè)置skip-name-resolve。
2.1.4 slave-net-timeout=seconds
  參數(shù)含義：當(dāng)slave從主數(shù)據(jù)庫(kù)讀取log數(shù)據(jù)失敗后，等待多久重新建立連接并獲取數(shù)據(jù)。默認(rèn)值是3600秒，如果需要保證同步性，如此NC的參數(shù)請(qǐng)極力控制在10秒以下。
2.1.5 master-connect-retry
參數(shù)含義：當(dāng)重新建立主從連接時(shí)，如果連接建立失敗，間隔多久后重試。默認(rèn)是60秒，請(qǐng)按照合理的情況去設(shè)置參數(shù)。

第三步 檢查mysql 相關(guān)狀態(tài)值
3.1關(guān)注連接數(shù)
如果連接數(shù)達(dá)到了最大連接數(shù)，那不管 有多少資源，用戶都會(huì)阻塞在外面。
修改mysql最大連接數(shù)：
打開(kāi)my.ini，修改max_connections=100(默認(rèn)為100)。
請(qǐng)根據(jù)硬件情況調(diào)整到合適的大小，一般經(jīng)驗(yàn)值可設(shè)為3000。Windows服務(wù)器大概支持量為1500-1800個(gè)連接，linux服務(wù)器可以支持到8000個(gè)左右。
請(qǐng)將max_user_connections設(shè)0--------這個(gè)0代表不限制單用戶的最大連接數(shù)，其最大連接值可以等于max_connections值。
mysql> show global status like 'Max_used_connections';
檢查下最大的過(guò)往使用連接數(shù)，這個(gè)值在max_connections的85%左右是比較合適的，如果過(guò)高則是max_connections過(guò)少或者系統(tǒng)負(fù)荷過(guò)高了。

3.1.1 mysqladmin -uroot status
[root@mysql1 ~]# mysqladmin -uroot status
Uptime: 1742276  Threads: 2  Questions: 2538  Slow queries: 0  Opens: 145  Flush tables: 1  Open tables: 23  Queries per second avg: 0.1
3.1.2 show full processlist
1.顯示所有進(jìn)程
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.如果正在運(yùn)行的語(yǔ)句太多，運(yùn)行時(shí)間太長(zhǎng)，表示MySQL效率有問(wèn)題。必要的時(shí)候可以將對(duì)應(yīng)的進(jìn)程kill掉。
殺死休眠的進(jìn)程kill ID號(hào)
mysql> kill 633;
Query OK, 0 rows affected (0.00 sec)

3.關(guān)注TIME參數(shù)，看看正在運(yùn)行的用戶進(jìn)程有多少是長(zhǎng)時(shí)間占用的，具體分析下。
3.1.3使用mysqlreport關(guān)注Connections，Threads
__ Connections _________________________________________________________
Max used            3 of  200      %Max:   1.50
Total          30.16k     0.7/s
。。。。。。
__ Threads _____________________________________________________________
Running             1 of    2
Cached              1 of  300      %Hit:  99.99
Created             3     0.0/s
Slow                0       0/s
3.2關(guān)注下系統(tǒng)鎖情況
3.2.1 mysql> show status like '%lock%';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Com_lock_tables               | 0       |
| Com_unlock_tables             | 0       |
| Innodb_row_lock_current_waits | 0       |
| Innodb_row_lock_time          | 0       |
| Innodb_row_lock_time_avg      | 0       |
| Innodb_row_lock_time_max      | 0       |
| Innodb_row_lock_waits         | 0       |
| Table_locks_immediate         | 2667760 |
| Table_locks_waited            | 0       |
+-------------------------------+---------+


3.2.2使用mysqlreport關(guān)注Table Locks，InnoDB Lock
__ Questions ___________________________________________________________
Total           3.38M    81.4/s
  DMS           2.88M    69.3/s  %Total:  85.11
  QC Hits     382.70k     9.2/s           11.32
  Com_         90.50k     2.2/s            2.68
  COM_QUIT     30.15k     0.7/s            0.89
  +Unknown         18     0.0/s            0.00
Slow 1 s           92     0.0/s            0.00  %DMS:   0.00  Log: OFF
。。。。。。
__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate       2.67M    64.2/s
。。。。。。
__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms
。。。。。。
如果wait過(guò)多，平均時(shí)間過(guò)長(zhǎng)，那就是查詢?cè)O(shè)計(jì)的有問(wèn)題，仔細(xì)關(guān)注下超長(zhǎng)時(shí)間的查詢，并打開(kāi)slow_query_log。
3.3 關(guān)注慢查詢（slow query）日志
日志必然會(huì)拖慢系統(tǒng)速度，特別是CPU資源，所以如果CPU資源充分，可以一直打開(kāi)，如果不充足，那就在需要調(diào)整的時(shí)候，或者在replication從服務(wù)器上打開(kāi)（針對(duì)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.3.1關(guān)注慢查詢涉及的表的相關(guān)狀態(tài)
1.        表內(nèi)記錄數(shù)。盡量控制在500萬(wàn)行以內(nèi)（有索引），建議控制在200萬(wàn)行
2.        表內(nèi)索引的使用。
3.        表如果update，delete，insert頻繁，可以考慮optimize table優(yōu)化下文件存放，索引，存儲(chǔ)空間。
4.        表內(nèi)update，insert，delete查詢的鎖定時(shí)間。
5.        select for update如果條件字段無(wú)索引的話，會(huì)引起的是鎖全表而不是行鎖，請(qǐng)關(guān)注。
6.        如果查詢包括GROUP BY但你想要避免排序結(jié)果的消耗，你可以指定ORDER BY NULL禁止排序。
3.3.2定期分析表
ANALYZE TABLE
語(yǔ)法：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
本語(yǔ)句用于分析和存儲(chǔ)表的關(guān)鍵字分布。在分析期間，使用一個(gè)讀取鎖定對(duì)表進(jìn)行鎖定。這對(duì)于MyISAM, BDB和InnoDB表有作用。對(duì)于MyISAM表，本語(yǔ)句與使用myisamchk -a相當(dāng)。
CHECK TABLE
語(yǔ)法：
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢查一個(gè)或多個(gè)表是否有錯(cuò)誤。CHECK TABLE對(duì)MyISAM和InnoDB表有作用。對(duì)于MyISAM表，關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)被更新。
CHECK TABLE也可以檢查視圖是否有錯(cuò)誤，比如在視圖定義中被引用的表已不存在。
CHECKSUM TABLE
語(yǔ)法：
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
報(bào)告一個(gè)表校驗(yàn)和。
3.3.3使用optimize table
OPTIMIZE TABLE
語(yǔ)法：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
如果已經(jīng)刪除了表的一大部分，或者如果您已經(jīng)對(duì)含有可變長(zhǎng)度行的表（含有VARCHAR, BLOB或TEXT列的表）進(jìn)行了很多更改，則應(yīng)使用OPTIMIZE TABLE。被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操作會(huì)重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來(lái)重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。
OPTIMIZE TABLE只對(duì)MyISAM, BDB和InnoDB表起作用。