如果mysql系統(tǒng)突然慢了怎么辦？

huangxifeng607

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


第一步 檢查系統(tǒng)的狀態(tài)
通過操作系統(tǒng)的一些工具檢查系統(tǒng)的狀態(tài)，比
如CPU、內(nèi)存、交換、磁盤的利用率，根據(jù)經(jīng)驗或與系統(tǒng)正常時的狀態(tài)相比對，有時系統(tǒng)表面上看起來
看空閑，這也可能不是一個正常的狀態(tài)，因為cpu可能正等待IO的
完成。除此之外，還應(yīng)觀注那些占用系統(tǒng)資源(cpu、內(nèi)存)的
進程。
1.1 使用sar來檢查操作系統(tǒng)是否存在IO問題
#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指的是用戶進程使用的cpu資源的百分比；
Ø %sys指的是
系統(tǒng)資源使用cpu資源的百分比；
Ø %wio指的是等待io完
成的百分比，這是值得觀注的一項；
Ø %idle即空閑的百分比。
如
果wio列的值很大，如在35%以上，說明
系統(tǒng)的IO存在瓶頸，CPU花費了很大的時
間去等待I/O的完成。Idle很小說明系
統(tǒng)CPU很忙。像以上的示例，可以看到wio平
均值為11，說明I/O沒什么特別的問題，
而idle值為零，說明cpu已經(jīng)滿負荷運
行了。
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: 磁盤寫的數(shù)據(jù)量稍
大，如果是大文件的寫，10M以內(nèi)基本不用擔心，如果是小文件寫2M以內(nèi)基本正常

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

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

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.顯示所有進程
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效率有問題。必要的時候可以將對應(yīng)的進程kill掉。
殺死休眠的進程kill ID號
mysql> kill 633;
Query OK, 0 rows affected (0.00 sec)

3.關(guān)注TIME參數(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過多，平均時間過長，那就是查詢設(shè)計的有問題，仔細關(guān)注下超長時間的查詢，
并打開slow_query_log。
3.3 關(guān)注慢查詢（slow query）日志
日志必然會拖慢系統(tǒng)速度，特別是CPU資源，所以如果CPU資
源充分，可以一直打開，如果不充足，那就在需要調(diào)整的時候，或者在replication從服務(wù)器上打開（針對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萬
行以內(nèi)（有索引），建議控制在200萬行
2.       表內(nèi)索引的使用。
3.       表如果update，delete，insert頻繁，可以考慮optimize table優(yōu)化下文件存
放，索引，存儲空間。
4.       表內(nèi)update，insert，delete查詢的鎖定時間。
5.       select for update如果條件字段無索引的話，會引起的是鎖全表而不是行鎖，請關(guān)注。
6.       如果查詢包括GROUP BY但你想要避免
排序結(jié)果的消耗，你可以指定ORDER BY NULL禁止排序。
3.3.2定期分析表
ANALYZE TABLE
語法：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name] ...
本語句用于分析和存儲表的關(guān)鍵字分布。在分析期間，使用一個讀取鎖定對表進行鎖定。這對于MyISAM,
BDB和InnoDB表有作用。對于MyISAM表，
本語句與使用myisamchk -a相當。
CHECK TABLE
語法：
CHECK TABLE tbl_name [, tbl_name] ...
[option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED |
CHANGED}
檢查一個或多個表是否有錯誤。CHECK TABLE對MyISAM和InnoDB表有作用。對于MyISAM表，關(guān)鍵字統(tǒng)計數(shù)據(jù)被更新。
CHECK TABLE也可以檢查視圖是否有錯誤，比如在視圖定義中被引用的表已不存在。
CHECKSUM TABLE
語法：
CHECKSUM TABLE tbl_name [, tbl_name] ... [
QUICK | EXTENDED ]
報告一個表校驗和。
3.3.3使用optimize table
OPTIMIZE TABLE
語法：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name] ...
如果已經(jīng)刪除了表的一大部分，或者如果您已經(jīng)對含有可變長度行的表（含有VARCHAR,
BLOB或TEXT列的表）進行了很多更改，則應(yīng)使用OPTIMIZE TABLE。被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操
作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，并整
理數(shù)據(jù)文件的碎片。
OPTIMIZE TABLE只對MyISAM, BDB和InnoDB表起作用。

               
               
               

本文來自ChinaUnix博客，如果查看原文請點：http://blog.chinaunix.net/u2/66215/showart_2184882.html