mysql CPU高負(fù)載問題排查
MySQL導(dǎo)致的CPU高負(fù)載問題
今天下午發(fā)現(xiàn)了一個(gè)MySQL導(dǎo)致的向上服務(wù)器負(fù)載高的問題，事情的背景如下：
在某個(gè)新服務(wù)器上，新建了一個(gè)MySQL的實(shí)例，該服務(wù)器上面只有MySQL這一個(gè)進(jìn)程，但是CPU的負(fù)載卻居高不下，使用top命令查詢的結(jié)果如下：
[dba_mysql@dba-mysql ~]$ top top - 17:12:44 up 104 days, 20 min, 2 users, load average: 1.06, 1.02, 1.00 Tasks: 218 total, 1 running, 217 sleeping, 0 stopped, 0 zombie Cpu0 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 :100.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16318504k total, 7863412k used, 8455092k free, 322048k buffers Swap: 5242876k total, 0k used, 5242876k free, 6226588k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 75373 mysql 20 0 845m 699m 29m S 100.0 4.4 112256:10 mysqld 43285 root 20 0 174m 40m 19m S 0.7 0.3 750:40.75 consul 116553 root 20 0 518m 13m 4200 S 0.3 0.1 0:05.78 falcon-agent 116596 nobody 20 0 143m 6216 2784 S 0.3 0.0 0:00.81 python 124304 dba_mysq 20 0 15144 1420 1000 R 0.3 0.0 0:02.09 top 1 root 20 0 21452 1560 1248 S 0.0 0.0 0:02.43 init
從上面的結(jié)果中，可以看到，8核的cpu只有一個(gè)核上面的負(fù)載是100%，其他的都是0%，而按照CPU使用率排序的結(jié)果也是mysqld的進(jìn)程占用CPU比較多。
之前從來沒有遇到過這個(gè)問題，當(dāng)時(shí)第一反應(yīng)是在想是不是有些業(yè)務(wù)層面的問題，比如說一些慢查詢一直在占用CPU的資源，于是登陸到MySQL上使用show processlist查看了當(dāng)前的進(jìn)程，發(fā)現(xiàn)除了有少許update操作之外，沒有其他的SQL語句在執(zhí)行。于是我又查看了一眼慢日志，發(fā)現(xiàn)慢日志中的SQL語句執(zhí)行時(shí)間都很短，大多數(shù)都是由于未使用索引導(dǎo)致的，但是掃描的記錄數(shù)都很少，只有幾百行，這樣看起來業(yè)務(wù)層面的問題是不存在的。
排除了業(yè)務(wù)層面的問題，現(xiàn)在看看數(shù)據(jù)庫層面的問題，查看了一眼buffer pool，可以看到這個(gè)值是：
mysql--dba_admin@127.0.0.1:(none) 17:20:35>>show variables like '%pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 5242880 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 5242880 | | thread_pool_high_prio_mode | transactions | | thread_pool_high_prio_tickets | 4294967295 | | thread_pool_idle_timeout | 60 | | thread_pool_max_threads | 100000 | | thread_pool_oversubscribe | 3 | | thread_pool_size | 8 | | thread_pool_stall_limit | 500 | +-------------------------------------+----------------+ 17 rows in set (0.01 sec)
從這個(gè)結(jié)果來看，buffer pool的大小只有5M大小，肯定是有問題的，一般情況下，線上環(huán)境的buffer pool都是1G往上，于是我查看了my.cnf配置文件，在配置文件中發(fā)現(xiàn)這個(gè)實(shí)例在啟動(dòng)的時(shí)候，innodb_buffer_pool_size的設(shè)置是0M，是的，沒有看錯(cuò)，是0M。這里不得不提另外一個(gè)參數(shù)，我們可以看到innodb_buffer_pool_size的大小和innodb_buffer_pool_chunk_size的大小一樣，這個(gè)chunk的概念是內(nèi)存塊，也就是說每次申請buffer pool的時(shí)候，是以"內(nèi)存塊"為單位申請的，一個(gè)buffer pool當(dāng)中包含多個(gè)內(nèi)存塊，所以buffer pool size的大小需要是chunk size的整數(shù)倍。
由于innodb_buffer_pool_chunk_size本身的值為5M，當(dāng)我們設(shè)置它為0M時(shí)，它會(huì)自動(dòng)的將其大小設(shè)置為5M的倍數(shù)，所以我們的innodb_buffer_pool_size值是5M。
既然buffer pool的值比較小，那么我將它改成1G的大小，看看這個(gè)問題還會(huì)不會(huì)發(fā)生：
mysql--dba_admin@127.0.0.1:(none) 17:20:41>>set global innodb_buffer_pool_size=1073741824; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql--dba_admin@127.0.0.1:(none) 17:23:34>>show variables like '%pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 5242880 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 1074790400 | | thread_pool_high_prio_mode | transactions | | thread_pool_high_prio_tickets | 4294967295 | | thread_pool_idle_timeout | 60 | | thread_pool_max_threads | 100000 | | thread_pool_oversubscribe | 3 | | thread_pool_size | 8 | | thread_pool_stall_limit | 500 | +-------------------------------------+----------------+ 17 rows in set (0.00 sec)
操作如上，這樣我們修改buffer pool的值為1G，我們設(shè)置的值是1073741824，而實(shí)際的值變成了1074790400，這個(gè)原因在上面已經(jīng)說過了，就是chunk size的值影響的。
此時(shí)使用top命令觀察CPU使用情況：
[dba_mysql@dba-mysql ~]$ top top - 22:19:09 up 104 days, 5:26, 2 users, load average: 0.45, 0.84, 0.86 Tasks: 218 total, 1 running, 217 sleeping, 0 stopped, 0 zombie Cpu0 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 1.0%us, 0.0%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 1.0%us, 0.0%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 0.7%us, 0.0%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16318504k total, 8008140k used, 8310364k free, 322048k buffers Swap: 5242876k total, 0k used, 5242876k free, 6230600k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 43285 root 20 0 174m 40m 19m S 1.0 0.3 753:07.38 consul 116842 root 20 0 202m 17m 5160 S 1.0 0.1 0:21.30 python 75373 mysql 20 0 1966m 834m 29m S 0.7 5.2 112313:36 mysqld 116553 root 20 0 670m 14m 4244 S 0.7 0.1 0:44.31 falcon-agent 116584 root 20 0 331m 11m 3544 S 0.7 0.1 0:37.92 python2.6 1 root 20 0 21452 1560 1248 S 0.0 0.0 0:02.43 init
可以發(fā)現(xiàn)，CPU的使用率已經(jīng)下去了，為了防止偶然現(xiàn)象，我又重新把buffer pool的大小改成了最初的5M的值，發(fā)現(xiàn)之前的問題又復(fù)現(xiàn)了，也就是說，設(shè)置大的buffer pool確實(shí)是一種解決方法。
到這里，問題是解決了，但是這個(gè)問題背后引發(fā)的一些東西卻值得思考，小的buffer pool為什么會(huì)導(dǎo)致其中一個(gè)CPU的使用率是100%？
這里，我能想到的一個(gè)原因是5M的buffer pool太小了，會(huì)導(dǎo)致業(yè)務(wù)SQL在讀取數(shù)據(jù)的時(shí)候和磁盤頻繁的交互，而磁盤的速度比較慢，所以會(huì)提高IO負(fù)載，導(dǎo)致CPU的負(fù)載過高，至于為什么只有一個(gè)CPU的負(fù)載比較高，其他的近乎為0，這個(gè)問題可能還需要查一查，如果有知道的朋友，還請不吝賜教。
以上就是mysql CPU高負(fù)載問題排查的詳細(xì)內(nèi)容，更多關(guān)于MySQL cpu高負(fù)載的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了MySQL主從復(fù)制原理以及需要注意的地方，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-11-11
這篇文章主要介紹了mysql alter table修改表命令整理的相關(guān)資料,需要的朋友可以參考下2016-10-10
與MSSQL對比學(xué)習(xí)MYSQL的心得（八）--插入 更新 刪除
這一篇《與MSSQL對比學(xué)習(xí)MYSQL的心得（八）》將會(huì)講解MYSQL的插入、更新和刪除語句2014-08-08
MySQL這個(gè)數(shù)據(jù)庫是一個(gè)客戶端-服務(wù)器結(jié)構(gòu)的程序,下面這篇文章主要給大家介紹了關(guān)于MySQL中庫的基本操作指南,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02
MYSQL性能優(yōu)化之分庫分表與不停機(jī)修改mysql表結(jié)構(gòu)，需要的朋友可以參考下2012-02-02
MySQL算術(shù)/比較/邏輯/位/運(yùn)算符與正則舉例詳解
每種數(shù)據(jù)庫都支持SQL語句,但是它們也都有各自支持的運(yùn)算符,下面這篇文章主要給大家介紹了關(guān)于MySQL算術(shù)/比較/邏輯/位/運(yùn)算符與正則的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02
對于客戶端驗(yàn)證用戶輸入的身份證是否符合格式的代碼，需要的朋友可以參考下。2010-11-11
這篇文章主要給大家介紹了關(guān)于mysql索引最左原則的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-07-07
MySQL配置文件my.cnf優(yōu)化詳解(mysql5.5)
這篇文章主要介紹了MySQL配置文件my.cnf優(yōu)化詳解,需要的朋友可以參考下2014-12-12
這篇文章主要介紹了MySQL存儲(chǔ)過程概念、原理與常見用法,結(jié)合實(shí)例形式詳細(xì)分析了mysql存儲(chǔ)過程的概念、原理、創(chuàng)建、刪除、調(diào)用等各種常用技巧與相關(guān)注意事項(xiàng),需要的朋友可以參考下2019-07-07