如果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 幾個(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
[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命令輸出分成六個(gè)部分：... 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)驗(yàn)或與系統(tǒng)正常時(shí)的狀態(tài)相比對(duì)，有時(shí)系統(tǒng)表面上看起來
看空閑，這也可能不是一個(gè)正常的狀態(tài)，因?yàn)閏pu可能正等待IO的
完成。除此之外，還應(yīng)觀注那些占用系統(tǒng)資源(cpu、內(nèi)存)的
進(jìn)程。
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指的是用戶進(jìn)程使用的cpu資源的百分比；
Ø %sys指的是
系統(tǒng)資源使用cpu資源的百分比；
Ø %wio指的是等待io完
成的百分比，這是值得觀注的一項(xiàng)；
Ø %idle即空閑的百分比。
如
果wio列的值很大，如在35%以上，說明
系統(tǒng)的IO存在瓶頸，CPU花費(fèi)了很大的時(shí)
間去等待I/O的完成。Idle很小說明系
統(tǒng)CPU很忙。像以上的示例，可以看到wio平
均值為11，說明I/O沒什么特別的問題，
而idle值為零，說明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: 磁盤寫的數(shù)據(jù)量稍
大，如果是大文件的寫，10M以內(nèi)基本不用擔(dān)心，如果是小文件寫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ù)量遠(yuǎn)大于系統(tǒng)中cpu的數(shù)量，將會(huì)使系統(tǒng)便慢。
2.) 如果這個(gè)數(shù)量是cpu的4倍的話，說明系統(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)存問題
主
要查看頁導(dǎo)入的數(shù)值（swap中的si），如果該值比較大就要考慮內(nèi)
存，大概方法如下：
1).最簡(jiǎn)單的，加大RAM　
　　
2).減
少RAM的需求
1.3磁盤IO問題
處理方式：做raid10提高性能
1.4網(wǎng)絡(luò)問題
telnet一下MySQL對(duì)外開放的端口，如果不
通的話，看看防火墻是否正確設(shè)置了。另外，看看MySQL是不是開啟了skip-networking的選項(xiàng)，如果開啟請(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來解除。如果你得到象這樣的一個(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ò)了(象來字一個(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)注
[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在收到連接請(qǐng)求的時(shí)候，會(huì)根據(jù)請(qǐng)求包中獲得的ip來
反向追查請(qǐng)求者的主機(jī)名。然后再根據(jù)返回的主機(jī)名又一次去獲取ip。如果兩次獲得的ip相同，那么連接就成功建立了。在DNS不
穩(wěn)定或者局域網(wǎng)內(nèi)主機(jī)過多的情況下，一次成功的連接將會(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ù)庫讀取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ù)：
打開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';
檢查下最大的過往使用連接數(shù)，這個(gè)值在max_connections的85%左右是比較合適的，如果過高則是max_connections過少或者系統(tǒng)負(fù)荷過高了。

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ùn)行時(shí)間太長(zhǎng)，表示MySQL效率有問題。必要的時(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過多，平均時(shí)間過長(zhǎng)，那就是查詢?cè)O(shè)計(jì)的有問題，仔細(xì)關(guān)注下超長(zhǎng)時(shí)間的查詢，
并打開slow_query_log。
3.3 關(guān)注慢查詢（slow query）日志
日志必然會(huì)拖慢系統(tǒng)速度，特別是CPU資源，所以如果CPU資
源充分，可以一直打開，如果不充足，那就在需要調(diào)整的時(shí)候，或者在replication從服務(wù)器上打開（針對(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萬
行以內(nèi)（有索引），建議控制在200萬行
2.       表內(nèi)索引的使用。
3.       表如果update，delete，insert頻繁，可以考慮optimize table優(yōu)化下文件存
放，索引，存儲(chǔ)空間。
4.       表內(nèi)update，insert，delete查詢的鎖定時(shí)間。
5.       select for update如果條件字段無索引的話，會(huì)引起的是鎖全表而不是行鎖，請(qǐng)關(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] ...
本語句用于分析和存儲(chǔ)表的關(guān)鍵字分布。在分析期間，使用一個(gè)讀取鎖定對(duì)表進(jìn)行鎖定。這對(duì)于MyISAM,
BDB和InnoDB表有作用。對(duì)于MyISAM表，
本語句與使用myisamchk -a相當(dāng)。
CHECK TABLE
語法：
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
語法：
CHECKSUM TABLE tbl_name [, tbl_name] ... [
QUICK | EXTENDED ]
報(bào)告一個(gè)表校驗(yàn)和。
3.3.3使用optimize table
OPTIMIZE TABLE
語法：
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來重新利用未使用的空間，并整
理數(shù)據(jù)文件的碎片。
OPTIMIZE TABLE只對(duì)MyISAM, BDB和InnoDB表起作用。

               
               
               

本文來自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u2/66215/showart_2184882.html