CPU 占用過高常見現(xiàn)象
在使用 MySQL 的過程中會遇到各種瓶頸問題，常見的是 IO 瓶頸，但是有時候會出現(xiàn)服務(wù)器 CPU 使用率超過 100%，應(yīng)用頁面訪問慢，登錄服務(wù)器負(fù)載很高。而導(dǎo)致這個問題竟然是 MySQL 進(jìn)程，按理說如果 MySQL 運(yùn)行穩(wěn)定，服務(wù)器的 CPU 資源并不會跑滿，如果出現(xiàn)這個問題，初步可以斷定，是 MySQL 實例中出現(xiàn)了問題 SQL。
CPU 占用過高常見原因
CPU 占用過高常見原因：
- 服務(wù)器硬件問題
- 內(nèi)存溢出
- 業(yè)務(wù)高并發(fā)
如果是業(yè)務(wù)高并發(fā)引起，可以理解為一種業(yè)務(wù)繁忙的狀態(tài)，有可能業(yè)務(wù)猛增，有可能是定期或者臨時的并發(fā)窗口：
- 數(shù)據(jù)庫對象設(shè)計不合理
- 觸發(fā)器導(dǎo)致
- 表索引設(shè)計不合理
- 數(shù)據(jù)庫鎖導(dǎo)致，如行鎖沖突、行鎖等待、鎖超時、死鎖等
- 系統(tǒng)架構(gòu)沒有緩存中間件
- 讀寫分離配置不合理
- OLTP 系統(tǒng)承載了 OLAP 的業(yè)務(wù)需求
- 未合理升級改造為集群環(huán)境
- 未配置異構(gòu)數(shù)據(jù)分析系統(tǒng)
- MySQL 系統(tǒng)參數(shù)設(shè)置不合理
- 問題 SQL 導(dǎo)致
SQL 問題導(dǎo)致 CPU 使用率過高是最常見的現(xiàn)象，比如 group by、order by、join 等，這些很大程度影響 SQL 執(zhí)行效率，從而占用大量的系統(tǒng)資源。
說了這么多常見原因，其實總結(jié)一句話來說就是現(xiàn)有系統(tǒng)的現(xiàn)有配置下的現(xiàn)有環(huán)境提供不了所需要的數(shù)據(jù)查詢、分析、執(zhí)行能力，針對這個問題，首先我們要發(fā)現(xiàn)問題的所在，就是說我們要準(zhǔn)確的定位問題，然后針對問題進(jìn)行優(yōu)化，再考慮其他升級改造的事情。
定位分析問題 SQL
光說不練假把式，接下來我們比劃比劃。
首先，搭建一套模擬環(huán)境，這里我讓我的得力助手 DBdeployer 來幫我搭建一套模擬環(huán)境。
DBdeployer 是用 Go 語言實現(xiàn)的一款非常強(qiáng)大與高效的部署數(shù)據(jù)庫環(huán)境的開源工具，我們完全可以用它來部署開發(fā)、測試環(huán)境。如果你對這個助手感興趣，可以看我的另外一篇 Chat。
MySQL 安裝部署：我是如何“秒級”部署 MySQL 集群
使用 DBdeployer 來部署一個單點 5.7.27 版本的 MySQL，并配置一個模擬 CPU 占比高的測試環(huán)境。
[root@localhost ~]# dbdeployer deploy single 5.7.27
Database installed in /dbdata/sandboxes/msb_5_7_27
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started
創(chuàng)建測試表：
[root@localhost ~]# cd /dbdata/sandboxes/msb_5_7_27/
[root@localhost msb_5_7_27]# ./use
mysql [localhost:5727] {msandbox} ((none)) > use test
Database changed
mysql [localhost:5727] {msandbox} (test) > create table t_cpu(id int primary key auto_increment);
Query OK, 0 rows affected (0.39 sec)
插入大量模擬測試數(shù)據(jù)：
mysql [localhost:5727] {msandbox} (test) > insert into t_cpu values(),(),(),(),();
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql [localhost:5727] {msandbox} (test) > insert into t_cpu select id+(select count(*) from t_cpu) from t_cpu;
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
......
......
mysql [localhost:5727] {msandbox} (test) > insert into t_cpu select id+(select count(*) from t_cpu) from t_cpu;
Query OK, 5242880 rows affected (1 min 8.84 sec)
Records: 5242880 Duplicates: 0 Warnings: 0
mysql [localhost:5727] {msandbox} (test) > select count(*) from t_cpu;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (2.22 sec)
至此我們已經(jīng)造了 10485760 條數(shù)據(jù)。
有 1000W+ 的數(shù)據(jù)量了，我們模擬一個業(yè)務(wù)場景，讓 CPU 嗨（high）起來。
執(zhí)行一個慢查詢：
mysql [localhost:5727] {msandbox} (test) > select * from t_cpu order by rand() limit 1;
另外開一個會話，top 看看進(jìn)程：
top - 15:21:20 up 33 days, 23:10, 3 users, load average: 0.38, 0.19, 0.21
Tasks: 202 total, 2 running, 200 sleeping, 0 stopped, 0 zombie
%Cpu(s): 23.3 us, 3.6 sy, 0.0 ni, 73.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8173716 total, 2867684 free, 1059276 used, 4246756 buff/cache
KiB Swap: 2097148 total, 2097140 free, 8 used. 6761912 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32232 root 20 0 1443252 356688 11748 S 107.0 4.4 2:03.82 mysqld
296 root 20 0 67044 15588 1440 S 0.7 0.2 325:32.46 plymouthd
600 root 20 0 162032 2360 1580 R 0.3 0.0 0:04.21 top
1901 gnome-i+ 20 0 714116 22436 9168 S 0.3 0.3 98:46.20 gsd-color
1 root 20 0 191264 4192 2632 S 0.0 0.1 18:39.97 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:01.28 kthreadd
4 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 0:00.95 ksoftirqd/0
7 root rt 0 0 0 0 S 0.0 0.0 0:02.08 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
通過 top 我們發(fā)現(xiàn)有一個 32232 的進(jìn)程使得 CPU 使用率已經(jīng)超過了 100%。
接下來我們具體分析分析，究竟是什么導(dǎo)致 CPU 使用率達(dá)到 100%以上的。
檢查內(nèi)存使用情況
[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 7982 1033 2801 40 4147 6604
Swap: 2047 0 2047
內(nèi)存使用率還可以，應(yīng)該不是內(nèi)存影響導(dǎo)致的問題。
檢查服務(wù)器線程
方法一：
top -H -p <mysqld 進(jìn)程 id>
先通過 top 找出占用 CPU 使用率 100% 的 MySQL 進(jìn)程 32232，在具體查看該進(jìn)程下的線程情況。
[root@localhost ~]# top -H -p 32232
top - 15:30:32 up 33 days, 23:20, 3 users, load average: 0.18, 0.15, 0.18
Threads: 28 total, 1 running, 27 sleeping, 0 stopped, 0 zombie
%Cpu(s): 24.4 us, 3.2 sy, 0.0 ni, 72.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8173716 total, 2867420 free, 1059520 used, 4246776 buff/cache
KiB Swap: 2097148 total, 2097140 free, 8 used. 6761668 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32272 root 20 0 1443252 356688 11748 R 99.7 4.4 2:25.74 mysqld
32244 root 0 -20 1443252 356688 11748 S 3.7 4.4 0:08.68 mysqld
32236 root 20 0 1443252 356688 11748 S 1.7 4.4 0:01.45 mysqld
32237 root 20 0 1443252 356688 11748 S 1.7 4.4 0:01.43 mysqld
32238 root 20 0 1443252 356688 11748 S 1.3 4.4 0:01.43 mysqld
32239 root 20 0 1443252 356688 11748 S 1.3 4.4 0:01.42 mysqld
32241 root 20 0 1443252 356688 11748 S 0.3 4.4 0:00.75 mysqld
32250 root 20 0 1443252 356688 11748 S 0.3 4.4 0:00.98 mysqld
32232 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.31 mysqld
32233 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32234 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.23 mysqld
32235 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.31 mysqld
32240 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.74 mysqld
32242 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.72 mysqld
32243 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.69 mysqld
32247 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.16 mysqld
32248 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.23 mysqld
32249 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.02 mysqld
32251 root 20 0 1443252 356688 11748 S 0.0 4.4 0:08.04 mysqld
32252 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32253 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32254 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32255 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32256 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.18 mysqld
32257 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.03 mysqld
32258 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32259 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
32260 root 20 0 1443252 356688 11748 S 0.0 4.4 0:00.00 mysqld
結(jié)果可以明顯的看到 32272 這個線程的 CPU 使用率異常。
方法二：
pidstat -t -p <mysqld 進(jìn)程 id> 1 5
先通過 top 找出占用 CPU 使用率 100% 的 MySQL 進(jìn)程 32232，使用 pidstat 工具在具體查看該進(jìn)程下的線程情況。
pidstat -t -p 32232 1 5
Average: UID TGID TID %usr %system %guest %CPU CPU Command
Average: 0 32232 - 85.00 12.60 0.00 97.60 - mysqld
Average: 0 - 32232 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32233 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32234 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32235 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32236 1.00 0.40 0.00 1.40 - |__mysqld
Average: 0 - 32237 0.80 0.40 0.00 1.20 - |__mysqld
Average: 0 - 32238 0.80 0.40 0.00 1.20 - |__mysqld
Average: 0 - 32239 1.00 0.20 0.00 1.20 - |__mysqld
Average: 0 - 32240 0.20 0.00 0.00 0.20 - |__mysqld
Average: 0 - 32241 0.00 0.20 0.00 0.20 - |__mysqld
Average: 0 - 32242 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32243 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32244 1.20 1.80 0.00 3.00 - |__mysqld
Average: 0 - 32247 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32248 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32249 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32250 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32251 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32252 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32253 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32254 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32255 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32256 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32257 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32258 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32259 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32260 0.00 0.00 0.00 0.00 - |__mysqld
Average: 0 - 32272 80.00 9.80 0.00 89.80 - |__mysqld
結(jié)果可以明顯的看到 32272 這個線程的 CPU 使用率異常。
查看 MySQL 進(jìn)程
在 MySQL 中看看當(dāng)前連接是否有什么異常。
方法一：
mysql [localhost:5727] {msandbox} ((none)) > show full processlist;
+----+----------+-----------+------+---------+------+---------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------+---------------------------------------------+
| 3 | msandbox | localhost | test | Query | 7 | Creating sort index | select * from t_cpu order by rand() limit 1 |
| 4 | msandbox | localhost | NULL | Query | 0 | starting | show full processlist |
+----+----------+-----------+------+---------+------+---------------------+---------------------------------------------+
2 rows in set (0.00 sec)
方法二：
mysql [localhost:5727] {msandbox} ((none)) > select * from information_schema.processlist;
+----+----------+-----------+------+---------+------+--------------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+----------+-----------+------+---------+------+--------------+----------------------------------------------+
| 5 | msandbox | localhost | test | Query | 2 | Sending data | select * from t_cpu order by rand() limit 1 |
| 6 | msandbox | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist |
| 4 | msandbox | localhost | NULL | Sleep | 1401 | | NULL |
| 3 | msandbox | localhost | test | Sleep | 1405 | | NULL |
+----+----------+-----------+------+---------+------+--------------+----------------------------------------------+
4 rows in set (0.00 sec)
參數(shù)說明：
- id：標(biāo)識位，MySQL 進(jìn)程 ID，使用 kill id，可以殺掉某一個進(jìn)程
- user：當(dāng)前連接的用戶
- host：當(dāng)前連接的主機(jī)
- db：連接的數(shù)據(jù)庫
- command：執(zhí)行的數(shù)據(jù)庫操作類型
- sleep：休眠狀態(tài)
- Query：查詢狀態(tài)
- connect：連接狀態(tài)
- time：已經(jīng)執(zhí)行的時間，單位秒
- info：已經(jīng)執(zhí)行的 SQL
- state：SQL 執(zhí)行的狀態(tài)，結(jié)果是 SQL 語句整個執(zhí)行狀態(tài)中的一個，其中包含很多狀態(tài)，我們整理如下表：
MySQL 5.7 官方參考MySQL 8.0 官方參考
|
狀態(tài) |
含義 |
|
After create |
當(dāng)線程使用函數(shù)創(chuàng)建表（包括內(nèi)部臨時表）最后階段會出現(xiàn)這個狀態(tài)，即使由于某些錯誤未能創(chuàng)建成功，也會標(biāo)識該狀態(tài) |
|
altering table |
服務(wù)端正在執(zhí)行 ALTER TABLE |
|
Analyzing |
線程正在計算 MyISAM 引擎表鍵值分布（例如 ANALYZE TABLE） |
|
checking permissions |
線程正在檢查服務(wù)端是否有執(zhí)行語句所需要的的權(quán)限 |
|
Checking table |
線程正在執(zhí)行表的檢查工作 |
|
cleaning up |
線程已經(jīng)處理了一個命令，并準(zhǔn)備釋放內(nèi)存并重置某些狀態(tài) |
|
Closing tables |
線程正在將表中修改的數(shù)據(jù)刷新到磁盤，同時正在關(guān)閉已經(jīng)用完的表。是一個能很快完成的動作，如果持續(xù)完不成，需要確認(rèn)磁盤空間是否用滿或者磁盤使用的負(fù)載很高 |
|
converting HEAP to ondisk |
線程正在將內(nèi)部臨時表從 MEMORY 表轉(zhuǎn)換為磁盤表 |
|
copy to tmp table |
線程正在執(zhí)行一個 ALTER TABLE 語句。在創(chuàng)建一個新表，然后將老表記錄復(fù)制到新表之前將是此狀態(tài) |
|
Copying to group table |
如果語句具有不同的條件 ORDER BY 和 GROUP BY 標(biāo)準(zhǔn)，則按組對行進(jìn)行排序并將其復(fù)制到臨時表 |
|
Copying to tmp table |
服務(wù)端正在復(fù)制到內(nèi)存中的臨時表 |
|
Copying to tmp table on disk |
服務(wù)端正在復(fù)制到磁盤上的臨時表，臨時結(jié)果集變的太大（參考 8.4.4 節(jié)，MySQL 內(nèi)部臨時表的使用），線程正在將臨時表從內(nèi)存更改為基于磁盤的格式來節(jié)省內(nèi)存 |
|
Creating index |
線程正在執(zhí)行 MyISAM 表的 ALTER TABLE ... ENABLE KEYS |
|
Creating sort index |
線程正在處理 SELECT 使用內(nèi)部臨時表 |
|
creating table |
線程正在創(chuàng)建表，包括創(chuàng)建臨時表 |
|
Creating tmp table |
線程正在內(nèi)存中或者磁盤上創(chuàng)建臨時表，如果臨時表在內(nèi)存中創(chuàng)建之后轉(zhuǎn)換到磁盤，狀態(tài)為 Copying to tmp table on disk |
|
committing alter table to storage engine |
服務(wù)端完成 ALTER TABLE 并提交結(jié)果 |
|
deleting from main table |
服務(wù)端正在執(zhí)行多表刪除的第一部分，僅刪除第一個表，保存列和偏移量用于刪除其他表 |
|
deleting from reference tables |
服務(wù)端正在執(zhí)行多表刪除的第二部分，在其他表中刪除匹配的行 |
|
discard_or_import_tablespace |
線程正在執(zhí)行 ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE 語句 |
|
end |
結(jié)束狀態(tài)，結(jié)束之前需要清理 ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE |
|
executing |
線程正在執(zhí)行語句 |
|
Execution of init_command |
線程正在執(zhí)行變量 init_command system 中的語句 |
|
freeing items |
線程執(zhí)行了一條命令，在此期間完成了一些項目的資源釋放，涉及如查詢緩存，這個狀態(tài)之后通常為 cleaning up |
|
FULLTEXT initialization |
服務(wù)端正在準(zhǔn)備執(zhí)行 natural-language 的全文檢索 |
|
init |
該狀態(tài)在 ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE 初始化之前，服務(wù)端在該狀態(tài)下會刷新二進(jìn)制日志和 InnoDB 日志，清理查詢緩存 |
|
Killed |
有發(fā)送了一個 KILL 線程的請求，下次終止前會檢查 kill 標(biāo)志位。MySQL 會在每次主循環(huán)中檢查 kill 標(biāo)志位，有時該線程并不能立馬殺掉會保留短暫時間，如果該線程被其他線程鎖定，那么 kill 會在鎖釋放后生效 |
|
logging slow query |
線程正在往慢日志中寫入語句 |
|
login |
連接線程的初始狀態(tài)，直到客戶端成功完成驗證 |
|
manage keys |
服務(wù)端開啟或者關(guān)閉表的索引 |
|
Opening tables |
線程正在打開表，這應(yīng)該是一個很快的過程，除非有些事情組織了打開表。例如 ALTER TABLE or a LOCK TABLE 會在結(jié)束前阻止打開表操作。再就是有必要檢查 table_open_cache 參數(shù)的值設(shè)置是否足夠大。 |
|
optimizing |
服務(wù)端正在對查詢進(jìn)行初始化優(yōu)化 |
|
preparing |
查詢優(yōu)化狀態(tài) |
|
Purging old relay logs |
線程正在刪除不需要的中繼日志 |
|
query end |
處理完查詢，在 freeing items 之前的狀態(tài) |
|
Receiving from client |
服務(wù)端正在讀取客戶端的包，該狀態(tài)在 5.7.8 叫 Reading from net |
|
Removing duplicates |
查詢正在使用 SELECT DISTINCT，這個過程 MySQL 在早期階段無法完成，因此需要一個額外的階段在把結(jié)果返回給客戶端前需要刪除重復(fù)行 |
|
removing tmp table |
線程在執(zhí)行完 SELECT 后刪除中間臨時表，如果沒有使用臨時表，無該狀態(tài) |
|
rename |
重命名表 |
|
rename result table |
線程正在執(zhí)行 ALTER TABLE 語句，創(chuàng)建一個新表，重命名新表替換老表 |
|
Reopen tables |
線程給表加鎖，但是發(fā)現(xiàn)在獲取表后發(fā)現(xiàn)表結(jié)構(gòu)發(fā)生了變化。當(dāng)釋放鎖，關(guān)閉表之后嘗試重新打開表 |
|
Repair by sorting |
使用排序創(chuàng)建索引修復(fù)代碼 |
|
preparing for alter table |
服務(wù)端正準(zhǔn)備就地執(zhí)行 ALTER TABLE |
|
Repair done |
線程已完成對 MyISAM 表的多線程修復(fù) |
|
Repair with keycache |
創(chuàng)建索引緩存修復(fù)代碼，比 Repair by sorting 慢的多 |
|
Rolling back |
線程在回滾事務(wù) |
|
Saving state |
對于 MyISAM 表的修復(fù)和分析操作，線程將新表保存到.MYI 文件頭，狀態(tài)包含行數(shù)，AUTO_INCREMENT 計數(shù)器，鍵值分布之類的信息 |
|
Searching rows for update |
線程正在執(zhí)行第一階段查詢未更新的數(shù)據(jù)，如果更新了索引所在行就會執(zhí)行有該狀態(tài) |
|
Sending data |
線程正在讀取和處理 SELECT 的行記錄，發(fā)送給客戶端，由于在這個狀態(tài)下的操作會執(zhí)行大量的磁盤訪問（讀），因此它通常是查詢生命周期最長的狀態(tài)。 |
|
Sending to client |
服務(wù)端正在給客戶端發(fā)送包，在 5.7.8 該狀態(tài)叫 Writing to net |
|
setup |
線程開始執(zhí)行 ALTER TABLE 操作 |
|
Sorting for group |
線程正在做排序來滿足 GROUP BY |
|
Sorting for order |
線程正在做排序來滿足 ORDER BY |
|
Sorting index |
線程正在做索引頁優(yōu)化來使得 MyISAM 表優(yōu)化操作期間有更高效的訪問 |
|
Sorting result |
對于一個 SELECT 語句，類似不使用臨時表的 Creating sort index |
|
starting |
語句執(zhí)行的第一個狀態(tài) |
|
statistics |
服務(wù)端正在計算統(tǒng)計信息對一個查詢創(chuàng)建執(zhí)行計劃，如果持續(xù)很長時間，服務(wù)端可能正在執(zhí)行其他的操作 |
|
System lock |
線程調(diào)用了 mysqllocktables()，并且一直未更新狀態(tài)，這個狀態(tài)非常常見，可能有很多原因造成。 |
|
update |
線程正準(zhǔn)備執(zhí)行更新操作 |
|
Updating |
線程正在檢索要更新的行并且更新行 |
|
updating main table |
服務(wù)端正在執(zhí)行多表更新的第一部分，僅更新第一個表，保存列和偏移量用于更新其他表 |
|
updating reference tables |
服務(wù)端正在執(zhí)行多表更新的第二部分，在其他表中更新匹配的行 |
|
User lock |
線程正在使用 GET_LOCK()請求或等待一個意向鎖，對于 SHOW PROFILE 這個狀態(tài)意味著線程請求鎖（但是不等鎖） |
|
User sleep |
線程正在調(diào)用 SLEEP() |
|
Waiting for commit lock |
FLUSH TABLES WITH READ LOCK 正在等待一個提交鎖 |
|
Waiting for global read lock |
FLUSH TABLES WITH READ LOCK 正在等待全局讀鎖或者設(shè)置系統(tǒng)全局變量 read_only |
|
Waiting for tables |
線程得到一個表結(jié)構(gòu)變化的通知，需要重新打開表獲取新的表結(jié)構(gòu)，該線程需要等待其他線程關(guān)閉該表。這個通知發(fā)生在另外的線程執(zhí)行了 FLUSH TABLES，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE or OPTIMIZE TABLE |
|
Waiting for table flush |
線程正在執(zhí)行 FLUSH TABLES，正在等待其他線程關(guān)閉他們的表，或者是線程得到一個表結(jié)構(gòu)變化的通知，需要重新打開表獲取新的表結(jié)構(gòu)，該線程需要等待其他線程關(guān)閉該表。這個通知發(fā)生在另外的線程執(zhí)行了 FLUSH TABLES，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE or OPTIMIZE TABLE |
|
Waiting for lock_type lock |
服務(wù)端正在等待 THR_LOCK 鎖，或者是元數(shù)據(jù)鎖定子系統(tǒng)的鎖，其中 lock_type 是鎖的類型，等待 THR_LOCK 鎖（Waiting for table level lock），等待元數(shù)據(jù)鎖（Waiting for event metadata lock、Waiting for global read lock、Waiting for schema metadata lock、Waiting for stored function metadata lock、Waiting for stored procedure metadata lock、Waiting for table metadata lock、Waiting for trigger metadata lock） |
|
Waiting on cond |
線程正在等待條件變?yōu)?true 的狀態(tài)，沒有具體可用的狀態(tài)信息 |
|
Writing to net |
服務(wù)端正在網(wǎng)絡(luò)中寫包，在 5.7.8 中該狀態(tài)叫 Sending to client |
通過結(jié)果我們可以大致看看如下的情況：
- 是否有大量的未執(zhí)行查詢語句
- 是否有正在執(zhí)行的 DML 語句
- 是否有執(zhí)行了很長時間的 DDL 語句
- 是否有鎖等情況
我們模擬的這個環(huán)境，show 的結(jié)果只有兩條，結(jié)果非常明顯，但是真正的生產(chǎn)環(huán)境不止這兩條結(jié)果，通過這個查詢結(jié)果，可以大致分析一下，并不能準(zhǔn)確定位，準(zhǔn)確的定位問題，需要進(jìn)行后續(xù)的操作。
根據(jù)問題線程 id 定位 MySQL 中的 SQL：
mysql [localhost:5727] {msandbox} ((none)) > select a.user,a.host,a.db,b.thread_os_id,b.thread_id,a.id processlist_id,a.command,a.time,a.state,a.info from information_schema.processlist a,performance_schema.threads b where a.id = b.processlist_id and b.thread_os_id=32272;
+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+
| user | host | db | thread_os_id | thread_id | processlist_id | command | time | state | info |
+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+
| msandbox | localhost | test | 32272 | 32 | 7 | Query | 2 | Sending data | select * from t_cpu order by rand() limit 1 |
+----------+-----------+------+--------------+-----------+----------------+---------+------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
- thread_os_id=32272，代表的是操作系統(tǒng)級別的線程 id
- thread_id=32，代表的是 MySQL 內(nèi)部級別的線程 id
- processlist_id=7，代表的是會話發(fā)起者的進(jìn)程 id，可 kill 的 id
根據(jù)問題線程 id 查看其他監(jiān)控指標(biāo)：
mysql [localhost:5727] {msandbox} (performance_schema) > select * from performance_schema.events_statements_current where thread_id in (select thread_id from performance_schema.threads where thread_os_id = 32272)G;
*************************** 1. row ***************************
THREAD_ID: 32
EVENT_ID: 11
END_EVENT_ID: 11
EVENT_NAME: statement/sql/select
SOURCE:
TIMER_START: 93213362472684000
TIMER_END: 93221551162460000
TIMER_WAIT: 8188689776000
LOCK_TIME: 993000000
SQL_TEXT: select * from t_cpu order by rand() limit 1
DIGEST: 83dacab9df764f11291d0001e420012e
DIGEST_TEXT: SELECT * FROM `t_cpu` ORDER BY `rand` ( ) LIMIT ?
CURRENT_SCHEMA: test
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 20971521
CREATED_TMP_DISK_TABLES: 1
CREATED_TMP_TABLES: 1
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 1
SORT_SCAN: 1
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
ERROR:
No query specified
通過這個結(jié)果我們可以查看具體的 SQL，看到有使用臨時表、使用了排序等信息。
CREATED_TMP_DISK_TABLES: 1
CREATED_TMP_TABLES: 1
SORT_ROWS: 1
SORT_SCAN: 1
其他排查手段之慢查詢?nèi)罩?/h1>
查詢慢日志情況：
mysql [localhost:5727] {msandbox} ((none)) > show variables like 'slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /dbdata/sandboxes/msb_5_7_27/data/localhost-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set (0.01 sec)
在慢日志中查看慢 SQL：
cat /dbdata/sandboxes/msb_5_7_27/data/localhost-slow.log
# Time: 2021-08-17T09:11:06.768816Z
# User@Host: msandbox[msandbox] @ localhost [] Id: 5
# Query_time: 7.984593 Lock_time: 0.000184 Rows_sent: 1 Rows_examined: 20971521
SET timestamp=1629191466;
select * from t_cpu order by rand() limit 1;
可以很清楚的查到慢 SQL：
select * from t_cpu order by rand() limit 1
執(zhí)行了有 7.984593 秒，返回結(jié)果為 1 行。
其他排查手段之排查日志
開啟排查日志功能：
更改排查日志為 TABLE 方式，默認(rèn)為 FILE 方式
mysql [localhost:5727] {msandbox} ((none)) > set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5727] {msandbox} ((none)) > set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)
執(zhí)行慢查詢：
select * from t_cpu order by rand() limit 1
查詢排查日志內(nèi)容：
mysql [localhost:5727] {msandbox} ((none)) > SELECT * from mysql.general_log ORDER BY event_time DESC;
+----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------------------------+
| 2021-08-17 17:19:19.863849 | msandbox[msandbox] @ localhost [] | 2 | 0 | Query | SELECT * from mysql.general_log ORDER BY event_time DESC |
| 2021-08-17 17:18:45.017615 | msandbox[msandbox] @ localhost [] | 5 | 0 | Query | select * from t_cpu order by rand() limit 1 |
+----------------------------+-----------------------------------+-----------+-----------+--------------+----------------------------------------------------------+
2 rows in set (0.00 sec)
結(jié)果顯示了剛才的慢 SQL，還有我們查詢排查日志結(jié)果的 SQL，其實慢日志會記錄所有的 SQL，我們只能通過這個日志內(nèi)容查看一些不太正常的 SQL，比如大量相同的 SQL；定期出現(xiàn)的 SQL；分析一個特定的事務(wù)等，排查日志模式非常影響 MySQL 性能，慎用。
關(guān)閉排查日志模式：
mysql [localhost:5727] {msandbox} ((none)) > set global general_log=OFF;
Query OK, 0 rows affected (0.05 sec)
mysql [localhost:5727] {msandbox} ((none)) > truncate table mysql.general_log;
Query OK, 0 rows affected (0.05 sec)
到目前為止，介紹了幾種常用的 MySQL 占用 CPU 過高的定位方法，你是否掌握了。找到問題了，接下來，我們就需要看看如何優(yōu)化這個問題了。
優(yōu)化一下也不難
前面介紹了幾種常用的排查 MySQL 占用 CPU 過高問題排查的方法，那么當(dāng)我們遇到問題了，如何解決，如何優(yōu)化呢？
首先，最暴力的方法，如果我們確定這個 SQL 可以被干掉，我們直接 kill id 即可。id 為 process 的 id。
其次，具體問題具體分析，如何排查問題是如何發(fā)生的，根據(jù)不同的問題作出相應(yīng)的解決方法。
1. 優(yōu)化 SQL，從邏輯上優(yōu)化 SQL，降低 SQL 復(fù)雜度，降低 MySQL 執(zhí)行成本。
2. 對 where、join、max()、min()、order by、group by 等子句用到的字段，創(chuàng)建相應(yīng)的索引。
3. 二級索引的正確使用。
4. 參數(shù)優(yōu)化：
- 增加 tmp_table_size 大小
- 增加 max_heap_table_size 大小
- 調(diào)整 key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size 參數(shù)大小
其他常用優(yōu)化方法
1. 檢查 MySQL 連接數(shù)當(dāng)前使用是否超過限制
如果超出限制，而且之前的連接沒有得到釋放，那新的連接肯定會連接不到，造成連接延遲，影響效率。
2. MySQL 的 timeout 參數(shù)設(shè)置問題
這里介紹兩個參數(shù)：
- wait_timeout：MySQL 在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)，其取值范圍在 windows 系統(tǒng)中為 1-2147483，linux 系統(tǒng)中為 1-31536000，默認(rèn)值 28800。
- interactive_time：MySQL 在關(guān)閉一個交互的連接之前所要等待的秒數(shù)（如 mysql gui tool 中的連接），其取值范圍隨 wait_timeout 變動，默認(rèn)值 28800。
交互連接即在 mysql_real_connect() 函數(shù)中使用了 CLIENT_INTERACTIVE 選項。簡單說，就是通過 MySQL 客戶端連接數(shù)據(jù)庫是交互式連接，通過 jdbc 連接數(shù)據(jù)庫是非交互式連接。在 MySQL 默認(rèn)設(shè)置下，當(dāng)一個連接的空閑時間超過 8 小時后，當(dāng)業(yè)務(wù)出現(xiàn)了高峰期，肯定會造成有太多的 TCP 連接沒關(guān)閉，數(shù)據(jù)庫連接數(shù)肯定會不夠用。從而會產(chǎn)生 CPU 占用過高，服務(wù)器告警等問題。因 EPG 的一個訪問一次對數(shù)據(jù)庫操作量不大，查詢完數(shù)據(jù)就完成 ok 了，wait_timeout 設(shè)置在 120s 內(nèi)就可以了。
在 MySQL 的官網(wǎng)了解到修改以上兩個參數(shù)必須修改 interactive_timeout。如果修改 interactive_timeout，則 wait_timeout 也發(fā)生變化，如果只修改 wait_timeout，不生效。
3. 老生常談，規(guī)范的 SQL 也是非常必要的
好了，至此我們基本學(xué)習(xí)完本節(jié)內(nèi)容了，內(nèi)容比較多，當(dāng)然也有不足之處，歡迎朋友們指正補(bǔ)充。
