引言

在生產(chǎn)環(huán)境中，MySQL作為一個(gè)關(guān)鍵的數(shù)據(jù)庫(kù)組件，其性能對(duì)整個(gè)系統(tǒng)的穩(wěn)定性至關(guān)重要。然而，有時(shí)候我們可能會(huì)遇到MySQL CPU使用率過(guò)高的問(wèn)題，這可能導(dǎo)致系統(tǒng)性能下降，應(yīng)用頁(yè)面訪問(wèn)減慢，甚至影響到用戶體驗(yàn)。本文將詳細(xì)介紹如何排查和解決MySQL CPU過(guò)高的問(wèn)題，幫助您迅速恢復(fù)正常的數(shù)據(jù)庫(kù)性能。

首先我們要明白什么是CPU使用率：

CPU使用率是指在單位時(shí)間內(nèi)CPU處于非空閑狀態(tài)的時(shí)間比，反映了CPU的繁忙程度。某個(gè)進(jìn)程的CPU使用率就是這個(gè)進(jìn)程在一段時(shí)間內(nèi)占用的CPU時(shí)間占總的時(shí)間的百分比。比如在雙核CPU某個(gè)開啟多線程的進(jìn)程1s內(nèi)占用了CPU0 0.6s, CPU1 0.9s, 那么它的占用率是150%。這里不深入闡述，網(wǎng)上文章很多。

CPU占用過(guò)高原因分析

CPU 占用過(guò)高常見原因：

• 服務(wù)器硬件問(wèn)題
• 內(nèi)存溢出
• 高并發(fā)業(yè)務(wù)中業(yè)務(wù)設(shè)計(jì)不合理導(dǎo)致
  • 數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)不合理
  • 表索引設(shè)計(jì)不合理
  • 數(shù)據(jù)庫(kù)鎖導(dǎo)致，如行鎖沖突、行鎖等待、鎖超時(shí)、死鎖等
  • 系統(tǒng)架構(gòu)沒有緩存中間件
  • 讀寫分離配置不合理
  • 未合理升級(jí)改造為集群環(huán)境
  • MySQL 系統(tǒng)參數(shù)設(shè)置不合理
  • 問(wèn)題 SQL 導(dǎo)致

SQL 問(wèn)題導(dǎo)致 CPU 使用率過(guò)高是最常見的現(xiàn)象，比如 group by、order by、join 等，這些很大程度影響 SQL 執(zhí)行效率，從而占用大量的系統(tǒng)資源。

說(shuō)了這么多常見原因，其實(shí)總結(jié)一句話來(lái)說(shuō)就是現(xiàn)有系統(tǒng)的現(xiàn)有配置下的現(xiàn)有環(huán)境提供不了所需要的數(shù)據(jù)查詢、分析、執(zhí)行能力，針對(duì)這個(gè)問(wèn)題，首先我們要發(fā)現(xiàn)問(wèn)題的所在，就是說(shuō)我們要準(zhǔn)確的定位問(wèn)題，然后針對(duì)問(wèn)題進(jìn)行優(yōu)化，再考慮其他升級(jí)改造的事情。

檢查MySQL運(yùn)行情況

可以看到CPU使用率非常高，內(nèi)存使用較低，可以排除不是內(nèi)存影響的。而且內(nèi)存資源還有很大空間。

因此要解決問(wèn)題，可以從兩方面入手：

• 優(yōu)化Mysql參數(shù)配置，發(fā)揮服務(wù)器硬件性能，通過(guò)合適的參數(shù)配置提升Mysql性能（以空間換時(shí)間，見效快，成本高）
• 找到問(wèn)題原因，優(yōu)化問(wèn)題sql、添加合理的索引、引入緩存等

方案一：MySQL配置參數(shù)優(yōu)化

查看服務(wù)器資源

查看服務(wù)器內(nèi)存：

[java@localhost ~]$ grep MemTotal /proc/meminfo 
MemTotal:       266419264 kB   	// 約256G

查看服務(wù)器CPU個(gè)數(shù)：

[java@localhost ~]$ lscpu
架構(gòu)：                           aarch64
CPU 運(yùn)行模式：                   64-bit
字節(jié)序：                         Little Endian
CPU:                             64
在線 CPU 列表：                  0-63
每個(gè)核的線程數(shù)：                 1
每個(gè)座的核數(shù)：                   32
座：                             2
NUMA 節(jié)點(diǎn)：                      2
廠商 ID：                        HiSilicon
型號(hào)：                           0
型號(hào)名稱：                       Kunpeng-920
步進(jìn)：                           0x1
Frequency boost:                 disabled
CPU 最大 MHz：                   2600.0000
CPU 最小 MHz：                   200.0000
BogoMIPS：                       200.00
L1d 緩存：                       4 MiB
L1i 緩存：                       4 MiB
L2 緩存：                        32 MiB
L3 緩存：                        64 MiB
NUMA 節(jié)點(diǎn)0 CPU：                 0-31
NUMA 節(jié)點(diǎn)1 CPU：                 32-63
Vulnerability Itlb multihit:     Not affected
Vulnerability L1tf:              Not affected
Vulnerability Mds:               Not affected
Vulnerability Meltdown:          Not affected
Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Vulnerability Spectre v1:        Mitigation; __user pointer sanitization
Vulnerability Spectre v2:        Not affected
Vulnerability Srbds:             Not affected
Vulnerability Tsx async abort:   Not affected
標(biāo)記：                           fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm jscvt fcma dcpop asimddp asimdfhm ssbs

可以看到服務(wù)器有兩個(gè)物理CPU，每個(gè)物理CPU有32個(gè)內(nèi)核數(shù)，即總共64個(gè)邏輯CPU數(shù)。
一般情況下，邏輯cpu=物理CPU個(gè)數(shù)×每顆核數(shù)

觀察MySQL狀態(tài)

MySQL的運(yùn)行狀態(tài)是我們排查性能問(wèn)題的第一步。通過(guò)查看全局狀態(tài)變量，我們可以獲取系統(tǒng)的整體運(yùn)行情況。以下是一些關(guān)鍵的狀態(tài)變量和信息：

Threads_running 和 Threads_connected

SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Threads_running 表示當(dāng)前正在執(zhí)行的線程數(shù)量。
Threads_connected 表示當(dāng)前已連接到MySQL的線程數(shù)量。
如果 Threads_running 較高，而 Threads_connected 較低，可能表明存在某些長(zhǎng)時(shí)間運(yùn)行的查詢，或者可能是由于連接池配置不當(dāng)導(dǎo)致連接被頻繁創(chuàng)建和銷毀。

InnoDB 相關(guān)狀態(tài)

SHOW ENGINE INNODB STATUS;

查看InnoDB引擎狀態(tài)，關(guān)注以下信息：

Innodb_row_lock_current_waits：表示當(dāng)前正在等待的行鎖數(shù)量。
Innodb_deadlocks：顯示發(fā)生的死鎖次數(shù)。
高的行鎖等待和死鎖次數(shù)可能表明業(yè)務(wù)邏輯或查詢需要優(yōu)化，或者存在并發(fā)訪問(wèn)沖突。

Key_reads 和 Key_writes

SHOW GLOBAL STATUS LIKE 'Key_reads';
SHOW GLOBAL STATUS LIKE 'Key_writes';

Key_reads：表示從磁盤讀取索引塊的次數(shù)。
Key_writes：表示向磁盤寫入索引塊的次數(shù)。
高的 Key_reads 可能暗示著索引未能完全放入內(nèi)存中，需要調(diào)整 key_buffer_size 參數(shù)。而頻繁的 Key_writes 可能表明索引的寫入操作較為頻繁，需要考慮索引的優(yōu)化。

Created_tmp_disk_tables

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

表示在磁盤上創(chuàng)建的臨時(shí)表的數(shù)量。過(guò)多的磁盤臨時(shí)表可能表明某些查詢需要優(yōu)化，或者 tmp_table_size 參數(shù)設(shè)置過(guò)小。

Uptime

SHOW STATUS LIKE 'Uptime';

表示MySQL服務(wù)的運(yùn)行時(shí)間。如果CPU問(wèn)題突然發(fā)生，檢查這個(gè)值，看是否與問(wèn)題的時(shí)間點(diǎn)相關(guān)。

其他關(guān)鍵狀態(tài)變量

瀏覽MySQL官方文檔以獲取更多有關(guān)全局狀態(tài)變量的信息，根據(jù)具體情況添加監(jiān)控和分析。

通過(guò)這些狀態(tài)變量，我們可以初步了解MySQL的整體運(yùn)行情況，從而有針對(duì)性地繼續(xù)深入排查問(wèn)題。在分析狀態(tài)時(shí)，可以使用各種監(jiān)控工具，如pt-mysql-summary或MySQL Enterprise Monitor，以更方便地查看和理解MySQL的狀態(tài)信息。

Mysql參數(shù)設(shè)置

數(shù)據(jù)庫(kù)屬于IO密集型的應(yīng)用程序，其主職責(zé)就是數(shù)據(jù)的管理及存儲(chǔ)工作。而我們知道，從內(nèi)存中讀取一個(gè)數(shù)據(jù)庫(kù)的時(shí)間是微秒級(jí)別，而從一塊普通硬盤上讀取一個(gè) IO是在毫秒級(jí)別，二者相差3個(gè)數(shù)量級(jí)。所以，要優(yōu)化數(shù)據(jù)庫(kù)，首先第一步需要優(yōu)化的就是IO，盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO。

SELECT version(); // 版本：8.0.30

 // 索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引
show global variables like 'key_buffer_size';  // 默認(rèn)值：8M

set global key_buffer_size=1024*1024*64

show global variables like 'max_allowed_packet'; // 默認(rèn)值：64M

show global variables like 'table_open_cache'; // 默認(rèn)值：4000

set global table_open_cache=16000

// sort_buffer_size是MySql執(zhí)行排序使用的緩沖大小
show global variables like 'sort_buffer_size'; // 默認(rèn)值：256KB

set global sort_buffer_size=1024*1024*16

show global variables like 'net_buffer_length'; // 默認(rèn)值：16KB

 //read_buffer_size 是MySql讀入緩沖區(qū)大小。
show global variables like 'read_buffer_size'; // 默認(rèn)值：128KB

set global read_buffer_size=1024*1024*8

 // tmp_table_size是MySql的heap （堆積）表緩沖大小
show global variables like 'tmp_table_size'; // 默認(rèn)值：16M

set global tmp_table_size=1024*1024*128

 // read_rnd_buffer_size 是MySql的隨機(jī)讀緩沖區(qū)大小
show global variables like 'read_rnd_buffer_size'; // 默認(rèn)值：256KB

set global read_rnd_buffer_size=1024*1024*4

// thread_cache_size可以重新利用保存在緩存中線程的數(shù)量     
show global variables like 'thread_cache_size'; // 默認(rèn)值：8

set global thread_cache_size=64



// MySql的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，
// 當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多，介于MySql會(huì)為每個(gè)連接提供連接緩沖區(qū)，就會(huì)開銷越多的內(nèi)存
show global variables like 'max_connections'; // 最多連接數(shù), 默認(rèn)：151

set global max_connections=5000;

show global variables like 'max_connect_errors'; // 默認(rèn)值：100

set global max_connect_errors=1000;

show global variables like 'open_files_limit'; // 默認(rèn)值：1M

show global variables like 'innodb_data_file_path';    

// InnoDB
// 對(duì)InnoDB表性能影響最大的一個(gè)參數(shù)。InnoDB緩沖池用于緩存數(shù)據(jù)和索引，對(duì)于讀取頻繁的表，適當(dāng)調(diào)整緩沖池大小可以顯著提升性能。
將
// innodb_buffer_pool_size設(shè)置為系統(tǒng)中Mysql可用內(nèi)存的70%左右。這確保了大部分?jǐn)?shù)據(jù)和索引都可以在內(nèi)存中緩存，減少磁盤I/O操作。
show global variables like 'innodb_buffer_pool_size'; // 默認(rèn)值：128M

set global innodb_buffer_pool_size=1024*1024*1024*32 //32G

//InnoDB事務(wù)日志文件大小
 show global variables like 'innodb_log_file_size';

// InnoDB存儲(chǔ)引擎的事務(wù)日志所使用的緩沖區(qū)
show global variables like 'innodb_log_buffer_size';  // 默認(rèn)值：16M

set global innodb_log_buffer_size=1024*1024*128

show global variables like 'sync_binlog';

set global sync_binlog=1000

可根據(jù)自己服務(wù)器性能動(dòng)態(tài)調(diào)整，但重啟后會(huì)失效，最好同時(shí)修改my.cnf配置文件：

通過(guò)參數(shù)調(diào)優(yōu)后的MySQL狀態(tài)：

方案二：SQL問(wèn)題分析定位解決

MySQL的查詢分析是排查性能問(wèn)題的關(guān)鍵步驟。通過(guò)檢查慢查詢?nèi)罩竞褪褂眯阅芊治龉ぞ?，我們可以找到潛在的性能瓶頸。

• 啟用慢查詢?nèi)罩?br />首先，確保MySQL的慢查詢?nèi)罩竟δ芤褑⒂?。在MySQL配置文件中添加以下配置：

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/slowlog/slow-query.log
long_query_time = 1

slow_query_log 啟用慢查詢?nèi)罩尽?br />slow_query_log_file 設(shè)置慢查詢?nèi)罩疚募窂健?br />long_query_time 定義慢查詢的時(shí)間閾值（單位：秒），這里設(shè)置為1秒。

或者使用MySQL客戶端：

 
-- 啟動(dòng)慢查詢?nèi)罩?
set global slow_query_log='ON';

-- 設(shè)置慢查詢存儲(chǔ)文件地址
set global slow_query_log_file='/usr/local/mysql/slowlog/slow-query.log';
 
-- 設(shè)置儲(chǔ)存sql條件，sql 執(zhí)行時(shí)間高于0.001秒存入日志文件
set global long_query_time=0.001;

-- 開啟 記錄沒有使用索引查詢語(yǔ)句
set global log-queries-not-using-indexes = on

• 分析慢查詢?nèi)罩?br />使用以下命令查看慢查詢?nèi)罩局械膬?nèi)容：

tail -f /usr/local/mysql/slowlog/slow-query.log

或者使用MySQL客戶端：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

通過(guò)檢查慢查詢?nèi)罩荆R(shí)別執(zhí)行時(shí)間長(zhǎng)的查詢。注意關(guān)注查詢的執(zhí)行計(jì)劃，以便理解MySQL是如何處理這些查詢的。

• 使用慢查詢分析工具
  使用工具如pt-query-digest來(lái)分析慢查詢?nèi)罩荆?/li>

pt-query-digest /path/to/slow-query.log

該工具能夠生成詳細(xì)的報(bào)告，包括執(zhí)行時(shí)間最長(zhǎng)的查詢、查詢頻率、索引使用情況等信息。通過(guò)這些信息，您可以確定哪些查詢需要優(yōu)化，以提高其性能。

• Explain命令
  對(duì)于特定的查詢，使用EXPLAIN命令來(lái)查看其執(zhí)行計(jì)劃：

EXPLAIN SELECT * FROM your_table WHERE your_condition;

EXPLAIN命令將顯示MySQL執(zhí)行查詢時(shí)的執(zhí)行計(jì)劃，包括使用的索引、訪問(wèn)表的方式等。通過(guò)分析執(zhí)行計(jì)劃，您可以了解查詢的性能瓶頸，并進(jìn)行相應(yīng)的優(yōu)化。

• 優(yōu)化查詢
  根據(jù)慢查詢?nèi)罩竞蛨?zhí)行計(jì)劃的分析結(jié)果，對(duì)性能較差的查詢進(jìn)行優(yōu)化?？赡艿膬?yōu)化方式包括：

• 優(yōu)化查詢語(yǔ)句，避免全表掃描。
• 優(yōu)化 SQL，降低 SQL 復(fù)雜度，降低 MySQL 執(zhí)行成本。
• 確保查詢涉及的列都有合適的索引。
• 考慮分表、分區(qū)表等策略，以減少單表的數(shù)據(jù)量。

通過(guò)以上步驟，您將能夠更好地理解哪些查詢對(duì)系統(tǒng)性能有負(fù)面影響，并有針對(duì)性地進(jìn)行優(yōu)化，提高整體性能。

結(jié)論

通過(guò)以上步驟，您應(yīng)該能夠定位和解決MySQL CPU使用率過(guò)高的問(wèn)題。請(qǐng)注意，每個(gè)生產(chǎn)環(huán)境都是獨(dú)特的，可能需要根據(jù)實(shí)際情況進(jìn)行適當(dāng)調(diào)整。保持監(jiān)控和定期優(yōu)化是確保MySQL性能穩(wěn)定的關(guān)鍵。希望這篇文章對(duì)您解決MySQL性能問(wèn)題提供了幫助。如果有任何問(wèn)題或建議，請(qǐng)隨時(shí)留言。

到此這篇關(guān)于MySQL生產(chǎn)環(huán)境CPU使用率過(guò)高的排查與解決方案的文章就介紹到這了,更多相關(guān)MySQL CPU使用率過(guò)高內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論