Mysql效能瓶頸深度定位分析
我們在效能測試過程中，經常會遇到Mysql出現效能瓶頸的情況，對於資料庫來說，所謂的效能瓶頸無非是慢SQL、CPU高、IO高、記憶體高，其中前三個舉實際例子來進行效能分析，最後記憶體高只是方法性說明（實際測試專案中沒遇到過）：
首先我們要保證沒有資料庫配置方面的效能問題，畢竟在效能測試前，對一些基本配置要擼一遍，避免犯低階錯誤。
本文結合實際專案例子進行分析（絕對硬核），包括內容：一、慢SQL定位分析；二、高CPU定位分析；三、高IO定位分析；四、高記憶體定位分析。
一、慢SQL定位分析
首先業務系統慢，肯定是體現在響應時間上，所以在效能測試中，如果發現慢我們就從響應時間上進行拆分，最後拆到mysql，那就是分析慢SQL，同樣如果在高併發時發現mysql程序佔CPU很高，也是優先分析是否存在慢SQL，而且判斷慢SQL還是比較簡單的，對於Mysq就是看慢日誌查詢。
1、首先是開啟慢日誌查詢：
#檢視是否開啟，以及存放路徑show variables like ‘%slow_query_log%’；#開啟set global slow_query_log = 1；#記錄慢日誌的時間，預設情況下為10秒show variables like ‘%long_query_time%’#檢視慢日誌條數show global status like ‘%slow_queries%’
使用set global slow_query_log=1；開啟慢查詢日誌只對當前資料庫生效，如果MySQL重啟後則會失效。想要永久生效，就必須修改配置檔案，其實沒這必要，我們都是臨時開啟，分析效能問題而已（分析完了，還得關了）。
2、測試過程獲取慢SQL
要手工分析日誌，查詢和分析SQL，顯然是個體力活，MySql提供了日誌分析工具mysqldumpslow
#得到返回記錄集最多的10個SQLMysqldumpslow –s r –t 10 /usr/local/mysql/data/localhost-slow。log#得到訪問次數最多的10個SQLMysqldumpslow –s c –t 10 /usr/local/mysql/data/localhost-slow。log#得到按照時間排序的前10條裡面含有左連線的查詢Mysqldumpslow –s t –t 10 –g “left join” /usr/local/mysql/data/localhost-slow。log#另外建議在使用這些命令時結合|和more使用，否則可能出現爆破情況Mysqldumpslow –s r –t 10 /usr/local/mysql/data/localhost-slow。log | more
引數含義s： 表示按照何種方式排序c：訪問次數l：鎖定時間r：返回記錄t：查詢時間al：平均鎖定時間t：返回前面多少條的資料g：後面搭配一個正則表示式
除此之外，你們也可以透過APM監控（全鏈路監控），也是能監控到慢SQL（當然壓測過程中不建議依賴一些重型工具）：
APM監控慢SQL
3、初步Explain分析
這是最基礎的功能，獲取到慢SQL，當然是要實際驗證一下有多慢，是否索引配置了，拿一條實際測試專案的SQL語句來分析：
explain SELECT count（c。id） FROM administrative_check_content c LEFT JOIN administrative_check_report_enforcers e ON c。report_id=e。report_id LEFT JOIN administrative_check_report r ON c。report_id = r。id WHERE e。enforcer_id= ‘ec66d95c8c6d437b9e3a460f93f1a592’；
可以分析出這條語句，86%的時間是花在了Sending data（所謂的“Sending data”並不是單純地傳送資料，而是包括“收集 ［檢索］ + 傳送資料”）：
Explain分析
透過Explain解釋也能看出索引已經加了（enforcer_id_index），而且透過索引幾乎全表檢索了30084條資料，如下：
Explain解釋
一般如果索引沒加或是加的不合理，透過這麼一分析也就能馬上看出來，可以說索引問題是導致慢SQL的最主要原因之一，也是影響業務系統性能的關鍵因素，以下以本次壓測專案的例子來說，沒加索引時最高TPS只有200，加了索引最高TPS達到900，如下所示：
Grafana檢視
在Explain解釋語句中，跟索引有關的列我們主要關注以下幾個：
（1）type
這列很重要，顯示了連線使用了哪種類別，有無使用到索引。一般從最好到最差的連線型別為const、eq_reg、ref、range、indexhe和ALL。
（2）possible_keys
possible_keys列指出MySQL能使用哪個索引在該表中找到行。注意，該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以透過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢效能。如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢。
（3） key
key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
（4）key_len
key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用索引的長度，在不損失精確性的情況下，長度越短越好。
（5）ref
ref列顯示使用哪個列或常數與key一起從表中選擇行。這一列涉及到多表關聯的欄位，const表示常數關聯。 ref很多時候也是和索引有關聯影響的地方。
4、用show profile進行sql分析
開啟分析也很簡單，使用臨時開啟執行set profiling=1即可（這個功能會快取最近查詢的分析語句，預設15條，最多100條，適合在壓測結束後開展sql分析，用完後再設成0關閉），如下：
#顯示是否開啟Profiling，以及最多儲存多少條show variables like ‘%profil%’；#開啟Profilingset profiling=1；#執行你的SQL#在這裡我們主要是執行前面所找到的慢SQL#檢視分析show profiles；
透過show profiles我們可以看到我們上面執行的那條SQL（Query_ID=18，為了確保監視最新的資料，Query_ID最好取25）：
show profiles
執行：show profile cpu，memory，block io for query 18；
show profile
可以看出也是Sending data總共消耗0。39秒，其中CPU_user時間佔比較高（簡單的一條SQL語句消耗這些時間就算很高了），另外還能看到這條SQL的IO開銷（因為查詢，都是ops out塊輸出）。
也可以透過SQL查表來檢視以上記錄：
select QUERY_ID，SEQ，STATE，DURATION，CPU_USER，CPU_SYSTEM，BLOCK_OPS_IN，BLOCK_OPS_OUT from information_schema。PROFILING where QUERY_ID = 18
另外說明一下這個show profile語句：
show profile cpu， block io， memory，swaps，context switches，source for query ［Query_ID］；# Show profile後面的一些引數：# - All：顯示所有的開銷資訊# - Cpu：顯示cpu相關開銷# - Block io：顯示塊IO相關開銷# - Context switches： 上下文切換相關開銷# - Memory：顯示記憶體相關開銷# - Source：顯示和source_function，source_file，source_line相關的開銷資訊
結論：
透過一條慢SQL我們就能追本溯源找到它慢的原因，這樣就能很好的指導效能調優了（效能測試工程師可以不會調優，但是不會效能分析還真不行，一遇到問題只會說慢，卻不會告訴開發人員到底哪慢的測試工程師，應該很難讓開發人員敬仰，甚至難以收穫尊重！）。
二、高CPU定位分析
1、SQL引起的高CPU
在效能壓測過程中，導致資料庫CPU很高的原因有很多種，一般和慢SQL也有關（因為每條SQL要麼佔CPU高，要麼佔IO高，大體是這樣），那麼如何分析到是某些SQL引起的呢？
（1）首先定位佔用CPU高的程序
透過TOP命令找到Mysql佔用CPU高，再看mysql程序下有多少執行緒是佔用CPU高的：
# top -p ［pid］ Htop -p 44662 H
top -H
可以看到有6個在Running，CPU都挺高的，36個在Sleeping，其中兩個Sleeping的CPU也挺高的。
（2）我們在mysql中使用 SHOW FULL PROCESSLIST； 查詢（透過FULL不僅能顯示所有連線的執行緒，而且能顯示出正在執行的完整SQL語句），如下：
PROCESSLIST
如果想透過過濾的方式，獲取指定SQL，可以直接到mysql的information_schema庫裡查詢：
SELECT * FROM `information_schema`。`PROCESSLIST` where host like ‘172。16。1。133%’
可以看到有不少是Sending data狀態的，我們挑選其中最複雜的一條語句來分析：
（SELECT r。id，c。check_action_name，check_date，check_end_date，c。id AS check_content_id，c。check_object_name AS checkObjectName，c。update_time，c。verify FROM administrative_check_content c LEFT JOIN administrative_check_report r ON c。report_id=r。id LEFT JOIN administrative_check_report_enforcers e ON r。id=e。report_id WHERE e。enforcer_id= ‘ec66d95c8c6d437b9e3a460f93f1a592’ ） UNION ALL （ SELECT r。id，r。check_action_name，check_date，check_end_date，‘0’，‘無’ AS checkObjectName，r。update_time，false FROM administrative_check_report r LEFT JOIN administrative_check_report_enforcers e ON r。id=e。report_id WHERE e。enforcer_id= ‘ec66d95c8c6d437b9e3a460f93f1a592’ AND r。check_content_id=‘0’ ） ORDER BY update_time DESC，check_content_id LIMIT 0， 15
（3）把這條語句用前面提到
show profile
進行分析：
show profile
可以看到有兩Sending data佔用時間都挺高的，花費時間也高，其實這語句用了聯合查詢，我們可以把SQL語句拆分了繼續分析（複雜語句都是由簡單語句組成，比較不爽的是有時候分離出來的語句也很慢），拆出一條語句繼續分析：
SELECT r。id，c。check_action_name，check_date，check_end_date，c。id AS check_content_id，c。check_object_name AS checkObjectName，c。update_time，c。verify FROM administrative_check_content c LEFT JOIN administrative_check_report r ON c。report_id=r。id LEFT JOIN administrative_check_report_enforcers e ON r。id=e。report_id WHERE e。enforcer_id= ‘ec66d95c8c6d437b9e3a460f93f1a592’
執行這條拆分出來的語句，查詢時間都需要0。8秒多，如下：
執行 EXPLAIN 分析，看到透過索引查詢到的內容多達30084行，如下所示：
一般像這樣的系統進行壓測，多半是資料分佈不合理，測試資料沒有反應真實的業務場景，明顯資料分佈不均衡，一個ID號就關聯三萬條資料，使用索引的效率都沒能體現出來。
總結：
透過SHOW PROCESSLIST；我們可以知道Mysql當前的執行緒狀態，以及主要資源消耗在哪方面；再結合show profile分析具體佔用CPU高的SQL，可以進一步定位出SQL引起高CPU的原因，到這一步無疑就能指導開發人員的最佳化方向了。
2、其他原因引起的高CPU
基本上和上面的分析思路差不多，排除SQL原因（SQL引起的問題主要集中於CPU或IO，IO高有時候也會間接導致CPU高），其他原因引起的高CPU，可透過mysql show processlist + show status + kill Id的方式進行定位。
（1）首先，
透過SHOW PROCESSLIST查詢mysql執行緒狀態，我們需要重點了解State列不同狀態所代表的含義：
Checking table正在檢查資料表（這是自動的）。Closing tables正在將表中修改的資料重新整理到磁碟中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁碟空間是否已經滿了或者磁碟是否正處於重負中。Connect Out複製從伺服器正在連線主伺服器。Copying to tmp table on disk由於臨時結果集大於tmp_table_size，正在將臨時表從記憶體儲存轉為磁碟儲存以此節省記憶體（如果臨時表過大會導致mysql將臨時表寫入硬碟的時間過長，會影響整體效能）。Creating tmp table正在建立臨時表以存放部分查詢結果。deleting from main table伺服器正在執行多表刪除中的第一部分，剛刪除第一個表。deleting from reference tables伺服器正在執行多表刪除中的第二部分，正在刪除其他表的記錄。Flushing tables正在執行FLUSH TABLES，等待其他執行緒關閉資料表。Killed傳送了一個kill請求給某執行緒，那麼這個執行緒將會檢查kill標誌位，同時會放棄下一個kill請求。MySQL會在每次的主迴圈中檢查 kill標誌位，不過有些情況下該執行緒可能會過一小段才能死掉。如果該執行緒程被其他執行緒鎖住了，那麼kill請求會在鎖釋放時馬上生效。Locked被其他查詢鎖住了。Sending data正在處理SELECT查詢的記錄，同時正在把結果傳送給客戶端。Sorting for group正在為GROUP BY做排序。Sorting for order正在為ORDER BY做排序。Opening tables這個過程應該會很快，除非受到其他因素的干擾。例如，在執ALTER TABLE或LOCK TABLE語句行完以前，資料表無法被其他執行緒開啟。正嘗試開啟一個表。Removing duplicates正在執行一個SELECT DISTINCT方式的查詢，但是MySQL無法在前一個階段最佳化掉那些重複的記錄。因此，MySQL需要再次去掉重複的記錄，然後再把結果傳送給客戶端。Reopen table獲得了對一個表的鎖，但是必須在表結構修改之後才能獲得這個鎖。已經釋放鎖，關閉資料表，正嘗試重新開啟資料表。Repair by sorting修復指令正在排序以建立索引。Repair with keycache修復指令正在利用索引快取一個一個地建立新索引。它會比Repair by sorting慢些。Searching rows for update正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關的記錄之前就完成了。Sleeping正在等待客戶端傳送新請求。（Sleeping過多也是問題，比如wait_timeout設定過大，導致MySQL裡大量的SLEEP程序無法及時釋放，拖累系統性能，不過也不能把它設定的過小，否則你可能會遭遇到“MySQL has gone away”之類的問題）。System lock正在等待取得一個外部的系統鎖。如果當前沒有執行多個mysqld伺服器同時請求同一個表，那麼可以透過增加——skip-external-locking引數來禁止外部系統鎖。Upgrading lockINSERT DELAYED正在嘗試取得一個鎖表以插入新記錄。Updating正在搜尋匹配的記錄，並且修改它們。User Lock正在等待GET_LOCK（）。Waiting for tables該執行緒得到通知，資料表結構已經被修改了，需要重新開啟資料表以取得新的結構。然後，為了能的重新開啟資料表，必須等到所有其他執行緒關閉這個 表。以下幾種情況下會產生這個通知：FLUSH TABLES tbl_name， ALTER TABLE， RENAME TABLE， REPAIR TABLE， ANALYZE TABLE，或OPTIMIZE TABLE。waiting for handler insertINSERT DELAYED已經處理完了所有待處理的插入操作，正在等待新的請求。
以上大部分狀態對應很快的操作，只要有一個執行緒保持同一個狀態好幾秒鐘，那麼可能是有問題發生了，需要檢查一下。
還有其他的狀態沒在上面中列出來，不過它們大部分只是在檢視伺服器是否有存在錯誤是才用得著。
（2）其次
，透過
show status
查詢當前Mysql的執行狀態
瞭解以下狀態值及含義，如果在日常運維過程有做這方面的記錄，那麼當系統出現效能異常時，能做個狀態值的比較對，偏離過大的就是需要關注的點（其實可以把這些引數值加入到運維監控系統，作為關注指標），如下：
Aborted_clients 由於客戶沒有正確關閉連線已經死掉，已經放棄的連線數量。Aborted_connects 嘗試已經失敗的MySQL伺服器的連線的次數。Connections 試圖連線MySQL伺服器的次數。Created_tmp_tables 當執行語句時，已經被創造了的隱含臨時表的數量。Delayed_insert_threads 正在使用的延遲插入處理器執行緒的數量。Delayed_writes 用INSERT DELAYED寫入的行數。Delayed_errors 用INSERT DELAYED寫入的發生某些錯誤（可能重複鍵值）的行數。Flush_commands 執行FLUSH命令的次數。Handler_delete 請求從一張表中刪除行的次數。Handler_read_first 請求讀入表中第一行的次數。Handler_read_key 請求數字基於鍵讀行。Handler_read_next 請求讀入基於一個鍵的一行的次數。Handler_read_rnd 請求讀入基於一個固定位置的一行的次數。Handler_update 請求更新表中一行的次數。Handler_write 請求向表中插入一行的次數。Key_blocks_used 用於關鍵字快取的塊的數量。Key_read_requests 請求從快取讀入一個鍵值的次數。Key_reads 從磁碟物理讀入一個鍵值的次數。Key_write_requests 請求將一個關鍵字塊寫入快取次數。Key_writes 將一個鍵值塊物理寫入磁碟的次數。Max_used_connections 伺服器啟動後同時使用的連線的最大數目。Not_flushed_key_blocks 在鍵快取中已經改變但是還沒被清空到磁碟上的鍵塊。Not_flushed_delayed_rows 在INSERT DELAY佇列中等待寫入的行的數量。Open_tables 當前開啟表的數量。Open_files 開啟檔案的數量。Open_streams 開啟流的數量（主要用於日誌記載）Opened_tables 已經開啟的表的數量。Questions 發往伺服器的查詢的數量。Slow_queries 要花超過long_query_time時間的查詢數量。Threads_connected 當前開啟的連線的數量。Threads_running 不在睡眠（啟用）的執行緒數量。Uptime 伺服器工作了多少秒。Uptime_since_flush_status 最近一次使用FLUSH STATUS 的時間（以秒為單位）
（
3）最後
，可以嘗試kill id（id在SHOW PROCESSLIST中顯示 ），關掉疑似佔CPU高的執行緒，以確認是否能讓CPU降下來。
對於mysql來說，慢SQL及死鎖以外的CPU問題確實不好定位，要求對資料庫系統及效能非常瞭解，而對於我們做效能測試的，能做的就是逐層分析，縮小問題範圍，實在不行，只能用kill id的方式來試錯排查。
三、高IO定位分析
其實高IO也可能導致CPU高，因為磁碟I/O比較慢，會導致CPU一直等待磁碟I/O請求。分析資料庫IO屬於基本技能（畢竟大部分資料庫調優到了極致，最後的瓶頸也可能會是IO，而且IO調優的難度會高一些）。
（1）首先用萬能的top命令檢視程序
［root@localhost ~］# toptop - 11：53：04 up 702 days， 56 min， 1 user， load average： 7。18， 6。70， 6。47Tasks： 576 total， 1 running， 575 sleeping， 0 stopped， 0 zombieCpu（s）： 7。7%us， 3。4%sy， 0。0%ni， 77。6%id， 11。0%wa， 0。0%hi， 0。3%si， 0。0%stMem： 49374024k total， 32018844k used， 17355180k free， 115416k buffersSwap： 16777208k total， 117612k used， 16659596k free， 5689020k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND14165 mysql 20 0 8822m 3。1g 4672 S 162。3 6。6 89839：59 mysqld40610 mysql 20 0 25。6g 14g 8336 S 121。7 31。5 282809：08 mysqld49023 mysql 20 0 16。9g 5。1g 4772 S 4。6 10。8 34940：09 mysqld
很明顯是前面兩個mysqld程序導致整體負載較高。而且，從 Cpu（s） 這行的統計結果也能看的出來，%us 和 %wa 的值較高，表示當前比較大的瓶頸可能是在使用者程序消耗的CPU以及磁碟I/O等待上。
（2）我們先分析下磁碟I/O的情況
執行 sar -d 1或（iostat -d -x -k 1）命令（每秒重新整理） 確認磁碟I/O是否真的較大：
［root@localhost ~］# sar -d 1Linux 2。6。32-431。el6。x86_64 （localhost。localdomain） 06/05/2020 _x86_64_ （8 CPU）11：54：31 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util11：54：32 AM dev8-0 5338。00 162784。00 1394。00 30。76 5。24 0。98 0。19 100。0011：54：33 AM dev8-0 5134。00 148032。00 32365。00 35。14 6。93 1。34 0。19 100。1011：54：34 AM dev8-0 5233。00 161376。00 996。00 31。03 9。77 1。88 0。19 100。0011：54：35 AM dev8-0 4566。00 139232。00 1166。00 30。75 5。37 1。18 0。22 100。0011：54：36 AM dev8-0 4665。00 145920。00 630。00 31。41 5。94 1。27 0。21 100。0011：54：37 AM dev8-0 4994。00 156544。00 546。00 31。46 7。07 1。42 0。20 100。00
%util 達到或接近100%，說明產生的I/O請求太多，qvgqu-sz也很高，I/O系統已經滿負荷。
IO高的判斷標準:
%util接近100% （磁碟 IO 使用率，表示IO請求多），
await 遠大於svctm （IO平均等待時間遠大於平均服務時間，表示IO響應慢），avgqu-sz比較大 （IO平均佇列長度）
（3）再利用 iotop 確認到底哪些程序消耗的磁碟I/O資源最多：
［root@localhost ~］# iotopTotal DISK READ： 59。52 M/s | Total DISK WRITE： 598。63 K/sTID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND16397 be/4 mysql 7。98 M/s 0。00 B/s 0。00 % 95。67 % mysqld ——basedir=/usr/local/mysql5。7 ——datadir=/usr/local/mysql5。7/data ——port=3306 7295 be/4 mysql 9。95 M/s 0。00 B/s 0。00 % 93。72 % mysqld ——basedir=/usr/local/mysql5。7 ——datadir=/usr/local/mysql5。7/data ——port=330614295 be/4 mysql 9。86 M/s 0。00 B/s 0。00 % 94。53 % mysqld ——basedir=/usr/local/mysql5。7 ——datadir=/usr/local/mysql5。7/data ——port=330614288 be/4 mysql 13。38 M/s 0。00 B/s 0。00 % 92。21 % mysqld ——basedir=/usr/local/mysql5。7 ——datadir=/usr/local/mysql5。7/data ——port=330614292 be/4 mysql 13。54 M/s 0。00 B/s 0。00 % 91。96 % mysqld ——basedir=/usr/local/mysql5。7 ——datadir=/usr/local/mysql5。7/data ——port=3306
可以看到，埠號是3306的例項消耗的磁碟I/O資源比較多，那就看看這個例項裡都有什麼查詢在跑。
（4）可以用上面提到的SHOW PROCESSLIST方法，也可以用mysqladmin命令工具
我們需要看到當前都有哪些SQL在執行：
（以下用mysqladmin的方式，該命令mysql自帶，可建立軟連結方便呼叫，ln -s /usr/local/mysql/bin/mysqladmin /usr/bin）：
［root@localhost ~］# mysqladmin -uroot -p123456 pr|grep -v Sleep+——+——+——————+——+————-+——-+————————+————————————————————————————————————————————————-+| Id |User| Host | db |Command|Time | State | Info |+——+——+——————+——+————-+——-+————————+————————————————————————————————————————————————-+| 25 |root| 172。16。1。133：45921 | db | Query | 68 | Sending data | select max（Fvideoid） from （select Fvideoid from t where Fvideoid>404612 order by Fvideoid） t1 || 26 |root| 172。16。1。133：45923 | db | Query | 65 | Sending data | select max（Fvideoid） from （select Fvideoid from t where Fvideoid>484915 order by Fvideoid） t1 || 28 |root| 172。16。1。133：45928 | db | Query | 130 | Sending data | select max（Fvideoid） from （select Fvideoid from t where Fvideoid>404641 order by Fvideoid） t1 || 27 |root| 172。16。1。133：45930 | db | Query | 167 | Sending data | select max（Fvideoid） from （select Fvideoid from t where Fvideoid>324157 order by Fvideoid） t1 || 36 |root| 172。16。1。133：45937 | db | Query | 174 | Sending data | select max（Fvideoid） from （select Fvideoid from t where Fvideoid>324346 order by Fvideoid） t1 |+——+——+——————+——+————-+——-+————————+————————————————————————————————————————————————-
可以看到有不少慢查詢還未完成，從slow query log中也能發現，這類SQL發生的頻率很高。
這是一個非常低效的SQL寫法，導致需要對整個主鍵進行掃描，但實際上只需要取得一個最大值而已，從slow query log中可看到：
Rows_sent： 1 Rows_examined： 541305
每次都要掃描500多萬行資料，卻只為讀取一個最大值，效率非常低。
經過分析，這個SQL稍做簡單改造即可在個位數毫秒級內完成，提升了N次方。
改造的方法是：對查詢結果做一次倒序排序，取得第一條記錄即可。而原先的做法是對結果正序排序，取最後一條記錄。
總結：
mysql的IO分析思路挺簡單，首先透過top關注%wa（指CPU等待磁碟寫入完成的時間，平時為0，越高表示磁碟越忙）的波動是否較大；其次分析下磁碟I/O情況，並找到哪些程序佔用IO資源最多；最後還是用SHOW PROCESSLIST或mysqladmin檢視哪些語句的頻繁呼叫在佔用IO。
四、高記憶體定位分析
要在linux下分析記憶體佔用高低，對於新手來說不太容易，因為涉及MemFree、虛擬記憶體（Swap）和Buffers、Cached的概念要搞明白，直觀性不如windows，另外mysql本身預設沒有開啟記憶體 / 快取監控（只對performance_schema進行了記憶體開銷的統計），一般的mysql監控軟體在這方面也很難直觀的暴露問題。
（1）首先分析記憶體也可以用萬能的TOP命令，看看是否mysql程序佔用記憶體高 ：
以上這張圖，比較容易看出是記憶體佔用高，因為free、buffers、cached都不高，那麼大部分被used掉的記憶體就屬於被程序佔用，而且沒有Swap（被禁用了，一般正常情況下，swap交換分割槽的used值如果在不斷的變化，就說明核心在不斷進行記憶體和swap的資料交換，很可能是記憶體不夠用了），那麼現在mysql佔用63。3%就可以判斷確實記憶體高了。
（2）排查是否大量SQL執行佔用記憶體高
檢視mysql裡的執行緒，觀察是否有長期執行或阻塞的sql，也是用到萬能的show full processlist；，如果沒有發現相關執行緒（具體參考上面提到的State列不同狀態含義）異常現象，就可以排除該原因。
（3）檢視mysql記憶體/快取的相關配置，以便排查mysql連線使用完後是否沒有真正釋放記憶體
Mysql的記憶體消耗一般分為兩種：global級共享記憶體、session級私有記憶體。
執行如下命令，即可查詢global級共享記憶體分配情況：
show variables where variable_name in （‘innodb_buffer_pool_size’，‘innodb_log_buffer_size’，‘innodb_additional_mem_pool_size’，‘query_cache_size’，‘key_buffer_size’）；
show variables
session級私有記憶體，主要是資料庫連線私有記憶體使用，查詢命令如下：
show variables where variable_name in （‘tmp_table_size’，‘sort_buffer_size’，‘read_buffer_size’，‘read_rnd_buffer_size’，‘join_buffer_size’，‘thread_stack’， ‘binlog_cache_size’）；
show variables
按理用mysql查詢命令，就能查到當前各項記憶體或快取的使用情況，但是mysql預設是沒有開啟記憶體監控的，透過以下語句就能查出大部分監控項都是未開啟的：
SELECT * FROM performance_schema。setup_instruments WHERE NAME LIKE ‘%memory%’ and NAME not LIKE ‘%memory/performance_schema%’；
我們可以用update語句批次開啟（屬於臨時性開啟，重啟mysql後又還原為關閉）：
mysql> update performance_schema。setup_instruments set enabled = ‘yes’ WHERE NAME LIKE ‘%memory%’ and NAME not LIKE ‘%memory/performance_schema%’；> Affected rows： 310> 時間： 0。002s
然後透過以下語句就可以查出mysql所有記憶體的使用：
SELECT SUBSTRING_INDEX（event_name，‘/’，2） AS code_area， sys。format_bytes（SUM（current_alloc）） AS current_alloc FROM sys。x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX（event_name，‘/’，2） ORDER BY SUM（current_alloc） DESC；
查到佔用記憶體最高的是memory/innodb，如下：
可以進一步細化查詢memory/innodb：
SELECT * FROM sys。memory_global_by_current_bytes WHERE event_name LIKE ‘memory/innodb%’；
對於記憶體使用預估，在網上有人推薦了一款記憶體計算器，統計網址：MySQL Memory Calculator
MySQL Memory Calculator
（說明：上圖左列為mysql預設配置，右列為當前資料庫的配置
[透過show variables可以查到]
，可以預估出記憶體使用最大值，如上圖，輕微調大一些配置，就能達到7119MB的記憶體量；如果預計到的結果不符合要求，就說明當前配置不合理，需要進行調整）。
mysql的資料庫記憶體/快取最佳化真沒什麼經驗，以下是網上提供的一個最佳化過程配置項（實際要設定多大，得看自己機器的記憶體有多大，結合記憶體計算器算一算，看看有沒有超標）：
key_buffer_size = 32M //key_buffer_size指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度。只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。由於我的資料庫引擎為innodb，大部分表均為innodb，此處取預設值一半32M。query_cache_size = 64M //查詢快取大小，當開啟時候，執行查詢語句會進行快取，讀寫都會帶來額外的記憶體消耗，下次再次查詢若命中該快取會立刻返回結果。預設改選項為關閉，開啟則需要調整引數項query_cache_type=ON。此處採用預設值64M。tmp_table_size = 64M //範圍設定為64-256M最佳，當需要做類似group by操作生成的臨時表大小，提高聯接查詢速度的效果，調整該值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%，處於這樣一個狀態之下，效果較好，如果網站大部分為靜態內容，可設定為64M，如果為動態頁面，則設定為100M以上，不宜過大，導致記憶體不足I/O堵塞。此處我們設定為64M。innodb_buffer_pool_size = 8196M //這個引數主要作用是快取innodb表的索引，資料，插入資料時的緩衝。專用mysql伺服器設定的大小： 作業系統記憶體的70%-80%最佳。由於我們的伺服器還部署有其他應用，估此處設定為8G。此外，這個引數是非動態的，要修改這個值，需要重啟mysqld服務。設定的過大，會導致system的swap空間被佔用，導致作業系統變慢，從而減低sql查詢的效率。innodb_additional_mem_pool_size = 16M //用來存放Innodb的內部目錄，這個值不用分配太大，系統可以自動調。不用設定太高。通常比較大資料設定16Ｍ夠用了，如果表比較多，可以適當的增大。如果這個值自動增加，會在error log有中顯示的。此處我們設定為16M。innodb_log_buffer_size = 8M //InnoDB的寫操作，將資料寫入到記憶體中的日誌快取中，由於InnoDB在事務提交前，並不將改變的日誌寫入到磁碟中，因此在大事務中，可以減輕磁碟I/O的壓力。通常情況下，如果不是寫入大量的超大二進位制資料（a lot of huge blobs），4MB-8MB已經足夠了。此處我們設定為8M。max_connections = 800 //最大連線數，根據同時線上人數設定一個比較綜合的數字，最大不超過16384。此處我們根據系統使用量綜合評估，設定為800。sort_buffer_size = 2M //是一個connection級引數，在每個connection第一次需要使用這個buffer的時候，一次性分配設定的記憶體。並不是越大越好，由於是connection級的引數，過大的設定+高併發可能會耗盡系統記憶體資源。官方文件推薦範圍為256KB~2MB，這裡我們設定為2M。read_buffer_size = 2M //（資料檔案儲存順序）是MySQL讀入緩衝區的大小，將對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區，read_buffer_size變數控制這一緩衝區的大小，如果對錶的順序掃描非常頻繁，並你認為頻繁掃描進行的太慢，可以透過增加該變數值以及記憶體緩衝區大小提高其效能，read_buffer_size變數控制這一提高表的順序掃描的效率 資料檔案順序。此處我們設定得比預設值大一點，為2M。read_rnd_buffer_size = 250K //是MySQL的隨機讀緩衝區大小，當按任意順序讀取行時（列如按照排序順序）將分配一個隨機讀取緩衝區，進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要大量資料可適當的調整該值，但MySQL會為每個客戶連線分配該緩衝區所以儘量適當設定該值，以免記憶體開銷過大。表的隨機的順序緩衝 提高讀取的效率。此處設定為跟預設值相似，250KB。join_buffer_size = 250K //多表參與join操作時的分配快取，適當分配，降低記憶體消耗，此處我們設定為250KB。thread_stack = 256K //每個連線執行緒被建立時，MySQL給它分配的記憶體大小。當MySQL建立一個新的連線執行緒時，需要給它分配一定大小的記憶體堆疊空間，以便存放客戶端的請求的Query及自身的各種狀態和處理資訊。Thread Cache 命中率：Thread_Cache_Hit = （Connections - Threads_created） / Connections * 100%；命中率處於90%才算正常配置，當出現“mysql-debug： Thread stack overrun”的錯誤提示的時候需要增加該值。此處我們配置為256K。binlog_cache_size = 250K // 為每個session 分配的記憶體，在事務過程中用來儲存二進位制日誌的快取。作用是提高記錄bin-log的效率。沒有什麼大事務，dml也不是很頻繁的情況下可以設定小一點，如果事務大而且多，dml操作也頻繁，則可以適當的調大一點。前者建議是1048576 –1M；後者建議是： 2097152 – 4194304 即 2–4M。此處我們根據系統實際，配置為250KB。table_definition_cache = 400 // 開發模式：從1400設定為400，記憶體從150M降到90M；服務模式：從1400設定為400，記憶體從324M降到227M
總結：
預設情況下按預設配置，很少能出現記憶體不足問題（畢竟現在的資料庫產品，管理記憶體還是挺成熟的），因為按照預設的配置，佔記憶體總計576。2MB，只是在使用過程中，很多人配置了不合理的引數（為了追求高效能，沒有平衡好配置和硬體的關係）或是執行例項異常，導致記憶體爆了。
