MySQL數(shù)據(jù)庫服務(wù)器逐漸變慢該如何分析與解決_MySQL

第一步 檢查系統(tǒng)的狀態(tài)
通過操作系統(tǒng)的一些工具檢查系統(tǒng)的狀態(tài)，比如CPU、內(nèi)存、交換、磁盤的利用率，根據(jù)經(jīng)驗(yàn)或與系統(tǒng)正常時(shí)的狀態(tài)相比對，有時(shí)系統(tǒng)表面上看起來看空閑，這也可能不是一個正常的狀態(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ù)量，將會使系統(tǒng)便慢。
2.) 如果這個數(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).最簡單的，加大RAM
2).減少RAM的需求
1.3磁盤IO問題
處理方式：做raid10提高性能
1.4網(wǎng)絡(luò)問題
telnet一下MySQL對外開放的端口，如果不通的話，看看防火墻是否正確設(shè)置了。另外，看看MySQL是不是開啟了skip-networking的選項(xiàng)，如果開啟請關(guān)閉。
第二步 檢查mysql參數(shù) 2.1 幾個不被注意的mysql參數(shù) 2.1.1 max_connect_errors
max_connect_errors默認(rèn)值為10，如果受信帳號錯誤連接次數(shù)達(dá)到10則自動堵塞，需要flush hosts來解除。如果你得到象這樣的一個錯誤：
Host ’hostname’ is blocked because of many connection errors.
Unblock with ’mysqladmin flush-hosts’
這意味著，mysqld已經(jīng)得到了大量(max_connect_errors)的主機(jī)’hostname’的在中途被中斷了的連接請求。在max_connect_errors次失敗請求后，mysqld認(rèn)定出錯了(象來字一個黑客的攻擊)，并且阻止該站點(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)答一個連接報(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在收到連接請求的時(shí)候，會根據(jù)請求包中獲得的ip來反向追查請求者的主機(jī)名。然后再根據(jù)返回的主機(jī)名又一次去獲取ip。如果兩次獲得的ip相同，那么連接就成功建立了。在DNS不穩(wěn)定或者局域網(wǎng)內(nèi)主機(jī)過多的情況下，一次成功的連接將會耗費(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ù)請極力控制在10秒以下。
2.1.5 master-connect-retry
參數(shù)含義：當(dāng)重新建立主從連接時(shí)，如果連接建立失敗，間隔多久后重試。默認(rèn)是60秒，請按照合理的情況去設(shè)置參數(shù)。

第三步 檢查mysql 相關(guān)狀態(tài)值 3.1關(guān)注連接數(shù)
如果連接數(shù)達(dá)到了最大連接數(shù)，那不管 有多少資源，用戶都會阻塞在外面。
修改mysql最大連接數(shù)：
打開my.ini，修改max_connections=100(默認(rèn)為100)。
請根據(jù)硬件情況調(diào)整到合適的大小，一般經(jīng)驗(yàn)值可設(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)負(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í)間太長，表示MySQL效率有問題。必要的時(shí)候可以將對應(yīng)的進(jìn)程kill掉。
殺死休眠的進(jìn)程kill ID號
mysql> kill 633;
Query OK, 0 rows affected (0.00 sec)

3.關(guān)注TIME參數(shù)，看看正在運(yùn)行的用戶進(jìn)程有多少是長時(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í)間過長，那就是查詢設(shè)計(jì)的有問題，仔細(xì)關(guān)注下超長時(shí)間的查詢，并打開slow_query_log。
3.3 關(guān)注慢查詢（slow query）日志
日志必然會拖慢系統(tǒng)速度，特別是CPU資源，所以如果CPU資源充分，可以一直打開，如果不充足，那就在需要調(diào)整的時(shí)候，或者在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查詢的鎖定時(shí)間。
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)鍵字分布。在分析期間，使用一個讀取鎖定對表進(jìn)行鎖定。這對于MyISAM, BDB和InnoDB表有作用。對于MyISAM表，本語句與使用myisamchk -a相當(dāng)。
CHECK TABLE
語法：
CHECK TABLE tbl_name [, tbl_name] … [option] …
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢查一個或多個表是否有錯誤。CHECK TABLE對MyISAM和InnoDB表有作用。對于MyISAM表，關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)被更新。
CHECK TABLE也可以檢查視圖是否有錯誤，比如在視圖定義中被引用的表已不存在。
CHECKSUM TABLE
語法：
CHECKSUM TABLE tbl_name [, tbl_name] … [ QUICK | EXTENDED ]
報(bào)告一個表校驗(yàn)和。
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列的表）進(jìn)行了很多更改，則應(yīng)使用OPTIMIZE TABLE。被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。
OPTIMIZE TABLE只對MyISAM, BDB和InnoDB表起作用。

附錄： 一.Sar命令獲得
安裝sysstat 系統(tǒng)狀態(tài)包
[root@mysql1 ~]# yum info sysstat
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
Name : sysstat
Arch : i386
Version : 7.0.2
Release : 3.el5
Size : 383 k
Repo : installed
Summary : sar 和 iostat 系統(tǒng)監(jiān)視命令。
URL : http://perso.orange.fr/sebastien.godard/
License : GPL
Description: 該軟件包為 Linux 提供了 sar 和 iostat 工具。sar 和 iostat
: 使系統(tǒng)能夠監(jiān)視磁盤、網(wǎng)絡(luò)、以及其它 IO 活動。
:
[root@mysql1 ~]# yum install sysstat
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package sysstat.i386 0:7.0.2-3.el5 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

============================================================================================
Package Arch Version Repository Size
============================================================================================
Installing:
sysstat i386 7.0.2-3.el5 CentOS 169 k

Transaction Summary
============================================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total download size: 169 k
Is this ok [y/N]: y
Downloading Packages:
sysstat-7.0.2-3.el5.i386.rpm | 169 kB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : sysstat 1/1

Installed:
sysstat.i386 0:7.0.2-3.el5

Complete!
sar 命令行的常用格式：

在linux中使用sar調(diào)優(yōu)系統(tǒng)性能
關(guān)鍵字: sar
sar默認(rèn)在linux下沒有安裝,需要我們手工安裝,一般建議源碼方式安裝,下載類似sysstat-6.1.3.tar.gz
然后configure make make install即可使用.
sar 命令行的常用格式：
sar [options] [-A] [-o file] t [n]
在命令行中，n 和t 兩個參數(shù)組合起來定義采樣間隔和次數(shù)，t為采樣間隔，是必須有
的參數(shù)，n為采樣次數(shù)，是可選的，默認(rèn)值是1，-o file表示將命令結(jié)果以二進(jìn)制格式
存放在文件中，file 在此處不是關(guān)鍵字，是文件名。options 為命令行選項(xiàng)，sar命令
的選項(xiàng)很多，下面只列出常用選項(xiàng)：
-A：所有報(bào)告的總和。
-u：CPU利用率
-v：進(jìn)程、I節(jié)點(diǎn)、文件和鎖表狀態(tài)。
-d：硬盤使用報(bào)告。
-r：沒有使用的內(nèi)存頁面和硬盤塊。
-g：串口I/O的情況。
-b：緩沖區(qū)使用情況。
-a：文件讀寫情況。
-c：系統(tǒng)調(diào)用情況。
-R：進(jìn)程的活動情況。
-y：終端設(shè)備活動情況。
-w：系統(tǒng)交換活動。
下面將舉例說明。
例一：使用命令行 sar -u t n
例如，每60秒采樣一次，連續(xù)采樣5次，觀察CPU 的使用情況，并將采樣結(jié)果以二進(jìn)制
形式存入當(dāng)前目錄下的文件zhou中，需鍵入如下命令：
# sar -u -o zhou 60 5
屏幕顯示：
SCO_SV　　　scosysv　3.2v5.0.5　i80386　　　10/01/2001
14:43:50　　　%usr　　　%sys　　%wio　　　　%idle(-u)
14:44:50　　　0　　　　　1　　　　4　　　　　　94
14:45:50　　　0　　　　　2　　　　4　　　　　　93
14:46:50　　　0　　　　　2　　　　2　　　　　　96
14:47:50　　　0　　　　　2　　　　5　　　　　　93
14:48:50　　　0　　　　　2　　　　2　　　　　　96
Average　　　 0　　　　　2　　　　4　　　　　　94
在顯示內(nèi)容包括：
%usr：CPU處在用戶模式下的時(shí)間百分比。
%sys：CPU處在系統(tǒng)模式下的時(shí)間百分比。
%wio：CPU等待輸入輸出完成時(shí)間的百分比。
%idle：CPU空閑時(shí)間百分比。
在所有的顯示中，我們應(yīng)主要注意%wio和%idle，%wio的值過高，表示硬盤存在I/O瓶頸，
%idle值高，表示CPU較空閑，如果%idle值高但系統(tǒng)響應(yīng)慢時(shí)，有可能是CPU等待分配內(nèi)存，
此時(shí)應(yīng)加大內(nèi)存容量。%idle值如果持續(xù)低于10，那么系統(tǒng)的CPU處理能力相對較低，表
明系統(tǒng)中最需要解決的資源是CPU。
如果要查看二進(jìn)制文件zhou中的內(nèi)容，則需鍵入如下sar命令：
# sar -u -f zhou
可見，sar命令即可以實(shí)時(shí)采樣，又可以對以往的采樣結(jié)果進(jìn)行查詢。
例二：使用命行sar -v t n
例如，每30秒采樣一次，連續(xù)采樣5次，觀察核心表的狀態(tài)，需鍵入如下命令：
# sar -v 30 5
屏幕顯示：
SCO_SV scosysv 3.2v5.0.5 i80386 10/01/2001
10:33:23 proc-sz ov inod-sz ov file-sz ov lock-sz　　 (-v)
10:33:53　305/　321 　0　1337/2764　 0　1561/1706　0　40/　128
10:34:23　308/　321 　0　1340/2764　 0　1587/1706　0　37/　128
10:34:53　305/　321 　0　1332/2764　 0　1565/1706　0　36/　128
10:35:23　308/　321 　0　1338/2764　 0　1592/1706　0　37/　128
10:35:53　308/　321　 0　1335/2764　 0　1591/1706　0　37/　128
顯示內(nèi)容包括：
proc-sz：目前核心中正在使用或分配的進(jìn)程表的表項(xiàng)數(shù)，由核心參數(shù)MAX-PROC控制。
inod-sz：目前核心中正在使用或分配的i節(jié)點(diǎn)表的表項(xiàng)數(shù)，由核心參數(shù)
MAX-INODE控制。
file-sz： 目前核心中正在使用或分配的文件表的表項(xiàng)數(shù)，由核心參數(shù)MAX-FILE控
制。
ov：溢出出現(xiàn)的次數(shù)。
Lock-sz：目前核心中正在使用或分配的記錄加鎖的表項(xiàng)數(shù)，由核心參數(shù)MAX-FLCKRE
控制。
顯示格式為
實(shí)際使用表項(xiàng)/可以使用的表項(xiàng)數(shù)
顯示內(nèi)容表示，核心使用完全正常，三個表沒有出現(xiàn)溢出現(xiàn)象，核心參數(shù)不需調(diào)整，如
果出現(xiàn)溢出時(shí)，要調(diào)整相應(yīng)的核心參數(shù)，將對應(yīng)的表項(xiàng)數(shù)加大。
例三：使用命行sar -d t n
例如，每30秒采樣一次，連續(xù)采樣5次，報(bào)告設(shè)備使用情況，需鍵入如下命令：
# sar -d 30 5
屏幕顯示：
SCO_SV scosysv 3.2v5.0.5 i80386 10/01/2001
11:06:43 device　%busy　　　avque　　　r+w/s　　blks/s　　avwait avserv (-d)
11:07:13 wd-0　　　1.47　　　2.75　　　4.67　　　14.73　　 5.50 3.14
11:07:43 wd-0　　　0.43　　　18.77　　 3.07　　　8.66　　　25.11 1.41
11:08:13 wd-0　　　0.77　　　2.78　　　2.77　　　7.26　　　4.94 2.77
11:08:43 wd-0　　　1.10　　　11.18　　 4.10　　　11.26　　 27.32 2.68
11:09:13 wd-0　　　1.97　　　21.78　　 5.86　　　34.06　　　69.66 3.35
Average wd-0　　　1.15　　　12.11　　 4.09　　　15.19　　　31.12 2.80
顯示內(nèi)容包括：
device： sar命令正在監(jiān)視的塊設(shè)備的名字。
%busy： 設(shè)備忙時(shí)，傳送請求所占時(shí)間的百分比。
avque： 隊(duì)列站滿時(shí)，未完成請求數(shù)量的平均值。
r+w/s： 每秒傳送到設(shè)備或從設(shè)備傳出的數(shù)據(jù)量。
blks/s： 每秒傳送的塊數(shù)，每塊512字節(jié)。
avwait： 隊(duì)列占滿時(shí)傳送請求等待隊(duì)列空閑的平均時(shí)間。
avserv： 完成傳送請求所需平均時(shí)間（毫秒）。
在顯示的內(nèi)容中，wd-0是硬盤的名字，%busy的值比較小，說明用于處理傳送請求的有
效時(shí)間太少，文件系統(tǒng)效率不高，一般來講，%busy值高些，avque值低些，文件系統(tǒng)
的效率比較高，如果%busy和avque值相對比較高，說明硬盤傳輸速度太慢，需調(diào)整。
例四：使用命行sar -b t n
例如，每30秒采樣一次，連續(xù)采樣5次，報(bào)告緩沖區(qū)的使用情況，需鍵入如下命令：
# sar -b 30 5
屏幕顯示：
SCO_SV scosysv 3.2v5.0.5 i80386 10/01/2001
14:54:59 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s (-b)
14:55:29　0　　147　　100　 5　　21　　78　　 0　　　0
14:55:59　0　　186　　100　 5　　25　　79　　 0　　　0
14:56:29　4　　232 　　98　 8　　58　　86　　 0　　　0
14:56:59　0　　125　　100　 5　　23　　76　　 0　　　0
14:57:29　0　　 89　　100　 4　　12　　66　　 0　　　0
Average　 1　　156 　　99　 5　　28　　80　　 0　　　0
顯示內(nèi)容包括：
bread/s： 每秒從硬盤讀入系統(tǒng)緩沖區(qū)buffer的物理塊數(shù)。
lread/s： 平均每秒從系統(tǒng)buffer讀出的邏輯塊數(shù)。
%rcache： 在buffer cache中進(jìn)行邏輯讀的百分比。
bwrit/s： 平均每秒從系統(tǒng)buffer向磁盤所寫的物理塊數(shù)。
lwrit/s： 平均每秒寫到系統(tǒng)buffer邏輯塊數(shù)。
%wcache： 在buffer cache中進(jìn)行邏輯讀的百分比。
pread/s： 平均每秒請求物理讀的次數(shù)。
pwrit/s： 平均每秒請求物理寫的次數(shù)。
在顯示的內(nèi)容中，最重要的是%cache和%wcache兩列，它們的值體現(xiàn)著buffer的使用效
率，%rcache的值小于90或者%wcache的值低于65，應(yīng)適當(dāng)增加系統(tǒng)buffer的數(shù)量，buffer
數(shù)量由核心參數(shù)NBUF控制，使%rcache達(dá)到90左右，%wcache達(dá)到80左右。但buffer參數(shù)
值的多少影響I/O效率，增加buffer，應(yīng)在較大內(nèi)存的情況下，否則系統(tǒng)效率反而得不到
提高。
例五：使用命行sar -g t n
例如，每30秒采樣一次，連續(xù)采樣5次，報(bào)告串口I/O的操作情況，需鍵入如下命令：
# sar -g 30 5
屏幕顯示：
SCO_SV scosysv 3.2v5.0.5 i80386　　11/22/2001
17:07:03 　ovsiohw/s　 ovsiodma/s　　ovclist/s (-g)
17:07:33　　　0.00　　　0.00　　　0.00
17:08:03　　　0.00　　　0.00　　　0.00
17:08:33　　　0.00　　　0.00　　　0.00
17:09:03　　　0.00　　　0.00　　　0.00
17:09:33　　　0.00　　　0.00　　　0.00
Average 　　　0.00　　　0.00　　　0.00
顯示內(nèi)容包括：
ovsiohw/s：每秒在串口I/O硬件出現(xiàn)的溢出。
ovsiodma/s：每秒在串口I/O的直接輸入輸出通道高速緩存出現(xiàn)的溢出。
ovclist/s ：每秒字符隊(duì)列出現(xiàn)的溢出。
在顯示的內(nèi)容中，每一列的值都是零，表明在采樣時(shí)間內(nèi)，系統(tǒng)中沒有發(fā)生串口I/O溢
出現(xiàn)象。
sar命令的用法很多，有時(shí)判斷一個問題，需要幾個sar命令結(jié)合起來使用，比如，懷疑
CPU存在瓶頸，可用sar -u 和sar -q來看，懷疑I/O存在瓶頸，可用sar -b、sar -u和
sar-d來看，以上舉出的五例僅僅是其中的一部分，有興趣的朋友不妨一試。

二．vmstat命令輸出分成六個部分：
(1)進(jìn)程procs：
r：在運(yùn)行隊(duì)列中等待的進(jìn)程數(shù) 。
b：在等待io的進(jìn)程數(shù) 。
(2)內(nèi)存memoy：
swpd：現(xiàn)時(shí)可用的交換內(nèi)存（單位KB）。
free：空閑的內(nèi)存（單位KB）。
buff: 緩沖去中的內(nèi)存數(shù)（單位：KB）。
cache：被用來做為高速緩存的內(nèi)存數(shù)（單位：KB）。
（3） swap交換頁面
si: 從磁盤交換到內(nèi)存的交換頁數(shù)量，單位：KB/秒。
so: 從內(nèi)存交換到磁盤的交換頁數(shù)量，單位：KB/秒。
(4) io塊設(shè)備:
bi: 發(fā)送到塊設(shè)備的塊數(shù)，單位：塊/秒。
bo: 從塊設(shè)備接收到的塊數(shù)，單位：塊/秒。
(5)system系統(tǒng)：
in: 每秒的中斷數(shù)，包括時(shí)鐘中斷。
cs: 每秒的環(huán)境（上下文）切換次數(shù)。
（6）cpu中央處理器：
cs：用戶進(jìn)程使用的時(shí)間 。以百分比表示。
sy：系統(tǒng)進(jìn)程使用的時(shí)間。 以百分比表示。
id：中央處理器的空閑時(shí)間 。以百分比表示。
如果 r經(jīng)常大于 4 ，且id經(jīng)常小于40，表示中央處理器的負(fù)荷很重。 如果bi，bo 長期不等于0，表示物理內(nèi)存容量太小。

三．根據(jù)mysql狀態(tài)調(diào)整系統(tǒng)參數(shù)
mysql> show global status;
可以列出MySQL服務(wù)器運(yùn)行各種狀態(tài)值，另外，查詢MySQL服務(wù)器配置信息語句：
mysql> show variables;
一、慢查詢
mysql> show variables like ‘%slow%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+——————+——-+
mysql> show global status like ‘%slow%’;
+———————+——-+
| Variable_name | Value |
+———————+——-+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+———————+——-+
配置中打開了記錄慢查詢，執(zhí)行時(shí)間超過2秒的即為慢查詢，系統(tǒng)顯示有4148個慢查詢，你可以分析慢查詢?nèi)罩荆页鲇袉栴}的SQL語句，慢查詢時(shí)間不宜設(shè)置過長，否則意義不大，最好在5秒以內(nèi)，如果你需要微秒級別的慢查詢，可以考慮給MySQL打補(bǔ)丁：http://www.percona.com/docs/wiki/release:start，記得找對應(yīng)的版本。
打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點(diǎn)點(diǎn)影響，如果你的MySQL是主-從結(jié)構(gòu)，可以考慮打開其中一臺從服務(wù)器的慢查詢?nèi)罩荆@樣既可以監(jiān)控慢查詢，對系統(tǒng)性能影響又小。
二、連接數(shù)
經(jīng)常會遇見”MySQL: ERROR 1040: Too many connections”的情況，一種是訪問量確實(shí)很高，MySQL服務(wù)器抗不住，這個時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過小：
mysql> show variables like ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 256 |
+—————–+——-+
這臺MySQL服務(wù)器最大連接數(shù)是256，然后查詢一下服務(wù)器響應(yīng)的最大連接數(shù)：
mysql> show global status like ‘Max_used_connections’;
MySQL服務(wù)器過去的最大連接數(shù)是245，沒有達(dá)到服務(wù)器連接數(shù)上限256，應(yīng)該沒有出現(xiàn)1040錯誤，比較理想的設(shè)置是：
Max_used_connections / max_connections * 100% ≈ 85%
最大連接數(shù)占上限連接數(shù)的85%左右，如果發(fā)現(xiàn)比例在10%以下，MySQL服務(wù)器連接數(shù)上限設(shè)置的過高了。
三、Key_buffer_size
key_buffer_size是對MyISAM表性能影響最大的一個參數(shù)，下面一臺以MyISAM為主要存儲引擎服務(wù)器的配置：
mysql> show variables like ‘key_buffer_size’;
+—————–+————+
| Variable_name | Value |
+—————–+————+
| key_buffer_size | 536870912 |
+—————–+————+
分配了512MB內(nèi)存給key_buffer_size，我們再看一下key_buffer_size的使用情況：
mysql> show global status like ‘key_read%’;
+————————+————-+
| Variable_name | Value |
+————————+————-+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+————————+————-+
一共有27813678764個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計(jì)算索引未命中緩存的概率：
key_cache_miss_rate = Key_reads / Key_read_requests * 100%
比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬盤，已經(jīng)很BT了，key_cache_miss_rate在0.1%以下都很好(每1000個請求有一個直接讀硬盤)，如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當(dāng)減少。
MySQL服務(wù)器還提供了key_blocks_*參數(shù)：
mysql> show global status like ‘key_blocks_u%’;
+————————+————-+
| Variable_name | Value |
+————————+————-+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+————————+————-+
Key_blocks_unused表示未使用的緩存簇(blocks)數(shù)，Key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)，比如這臺服務(wù)器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
四、臨時(shí)表
mysql> show global status like ‘created_tmp%’;
+————————-+———+
| Variable_name | Value |
+————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+————————-+———+
每次創(chuàng)建臨時(shí)表，Created_tmp_tables增加，如果是在磁盤上創(chuàng)建臨時(shí)表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件文件數(shù)，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服務(wù)器Created_tmp_disk_tables / Created_tmp_tables * 100% = 1.20%，應(yīng)該相當(dāng)好了。我們再看一下MySQL服務(wù)器對臨時(shí)表的配置：
mysql> show variables where Variable_name in (‘tmp_table_size’, ‘max_heap_table_size’);
+———————+———–+
| Variable_name | Value |
+———————+———–+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+———————+———–+
只有256MB以下的臨時(shí)表才能全部放內(nèi)存，超過的就會用到硬盤臨時(shí)表。
五、Open Table情況
mysql> show global status like ‘open%tables%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 919 |
| Opened_tables | 1951 |
+—————+——-+
Open_tables表示打開表的數(shù)量，Opened_tables表示打開過的表數(shù)量，如果Opened_tables數(shù)量過大，說明配置中table_cache(5.1.3之后這個值叫做table_open_cache)值可能太小，我們查詢一下服務(wù)器table_cache值：
mysql> show variables like ‘table_cache’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| table_cache | 2048 |
+—————+——-+
比較合適的值為：
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%
六、進(jìn)程使用情況
mysql> show global status like ‘Thread%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+——————-+——-+
如果我們在MySQL服務(wù)器配置文件中設(shè)置了thread_cache_size，當(dāng)客戶端斷開之后，服務(wù)器處理此客戶的線程將會緩存起來以響應(yīng)下一個客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限)。Threads_created表示創(chuàng)建過的線程數(shù)，如果發(fā)現(xiàn)Threads_created值過大的話，表明MySQL服務(wù)器一直在創(chuàng)建線程，這也是比較耗資源，可以適當(dāng)增加配置文件中thread_cache_size值，查詢服務(wù)器thread_cache_size配置：
mysql> show variables like ‘thread_cache_size’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 64 |
+——————-+——-+
示例中的服務(wù)器還是挺健康的。
七、查詢緩存(query cache)
mysql> show global status like ‘qcache%’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+————————-+———–+
MySQL查詢緩存變量解釋：
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進(jìn)行整理，從而得到一個空閑塊。
Qcache_free_memory：緩存中的空閑內(nèi)存。
Qcache_hits：每次查詢在緩存中命中時(shí)就增大
Qcache_inserts：每次插入一個查詢時(shí)就增大。命中次數(shù)除以插入次數(shù)就是不中比率。
Qcache_lowmem_prunes：緩存出現(xiàn)內(nèi)存不足并且必須要進(jìn)行清理以便為更多查詢提供空間的次數(shù)。這個數(shù)字最好長時(shí)間來看;如果這個數(shù)字在不斷增長，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少。(上面的 free_blocks和free_memory可以告訴您屬于哪種情況)
Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。
Qcache_queries_in_cache：當(dāng)前緩存的查詢(和響應(yīng))的數(shù)量。
Qcache_total_blocks：緩存中塊的數(shù)量。
我們再查詢一下服務(wù)器關(guān)于query_cache的配置：
mysql> show variables like ‘query_cache%’;
+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———–+
各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小大小
query_cache_size：查詢緩存大小
query_cache_type：緩存類型，決定緩存什么樣的查詢，示例中表示不緩存 select sql_no_cache 查詢
query_cache_wlock_invalidate：當(dāng)有其他客戶端正在對MyISAM表進(jìn)行寫操作時(shí)，如果查詢在query cache中，是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，默認(rèn)是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。
查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。
查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當(dāng)減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點(diǎn)小，要不就是碎片太多。
查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服務(wù)器 查詢緩存碎片率 = 20.46%，查詢緩存利用率 = 62.26%，查詢緩存命中率 = 1.94%，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
八、排序使用情況
mysql> show global status like ‘sort%’;
+——————-+————+
| Variable_name | Value |
+——————-+————+
| Sort_merge_passes | 29 |
| Sort_range | 37432840 |
| Sort_rows | 9178691532 |
| Sort_scan | 1860569 |
+——————-+————+
Sort_merge_passes 包括兩步。MySQL 首先會嘗試在內(nèi)存中做排序，使用的內(nèi)存大小由系統(tǒng)變量 Sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到內(nèi)存中，MySQL 就會把每次在內(nèi)存中排序的結(jié)果存到臨時(shí)文件中，等 MySQL 找到所有記錄之后，再把臨時(shí)文件中的記錄做一次排序。這再次排序就會增加 Sort_merge_passes。實(shí)際上，MySQL 會用另一個臨時(shí)文件來存再次排序的結(jié)果，所以通常會看到 Sort_merge_passes 增加的數(shù)值是建臨時(shí)文件數(shù)的兩倍。因?yàn)橛玫搅伺R時(shí)文件，所以速度可能會比較慢，增加 Sort_buffer_size 會減少 Sort_merge_passes 和 創(chuàng)建臨時(shí)文件的次數(shù)。但盲目的增加 Sort_buffer_size 并不一定能提高速度。另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點(diǎn)的好處，參見：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/
九、文件打開數(shù)(open_files)
mysql> show global status like ‘open_files’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_files | 1410 |
+—————+——-+
mysql> show variables like ‘open_files_limit’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| open_files_limit | 4590 |
+——————+——-+
比較合適的設(shè)置：Open_files / open_files_limit * 100% <= 75%
十、表鎖情況
mysql> show global status like ‘table_locks%’;
+———————–+———–+
| Variable_name | Value |
+———————–+———–+
| Table_locks_immediate | 490206328 |
| Table_locks_waited | 2084912 |
+———————–+———–+
Table_locks_immediate表示立即釋放表鎖數(shù)，Table_locks_waited表示需要等待的表鎖數(shù)，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因?yàn)镮nnoDB是行鎖而MyISAM是表鎖，對于高并發(fā)寫入的應(yīng)用InnoDB效果會好些。示例中的服務(wù)器Table_locks_immediate / Table_locks_waited = 235，MyISAM就足夠了。
十一、表掃描情況
mysql> show global status like ‘handler_read%’;
+———————–+————-+
| Variable_name | Value |
+———————–+————-+
| Handler_read_first | 5803750 |
| Handler_read_key | 6049319850 |
| Handler_read_next | 94440908210 |
| Handler_read_prev | 34822001724 |
| Handler_read_rnd | 405482605 |
| Handler_read_rnd_next | 18912877839 |
+———————–+————-+
各字段解釋參見http:///database/201203/123031.html，調(diào)出服務(wù)器完成的查詢請求次數(shù)：
mysql> show global status like ‘com_select’;
+—————+———–+
| Variable_name | Value |
+—————+———–+
| Com_select | 222693559 |
+—————+———–+
計(jì)算表掃描率：
表掃描率 = Handler_read_rnd_next / Com_select
如果表掃描率超過4000，說明進(jìn)行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。

作者 sundy2sundy

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com