Mysql CPU异常飙升

最近一周CPU一直是90以上,不停的收到zabbix的报警信息,虽然有一部分原因是我们有一个后台的sql写的很烂,这个需要一个时间去过渡,但是之前好歹是可以正常运行的,但是这两天频繁的报警,基本一天cpu的占用都不会下降,而且波动很厉害。
 
机器配置如下:
 
 
目前有几个点需要停机优化,一个是mysql的配置文件,目前的配置文件如下:
[root@DBServer1 ~]# lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    1
Core(s) per socket:    2
Socket(s):             8
NUMA node(s):          8
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 37
Stepping:              1
CPU MHz:               2133.409
BogoMIPS:              4266.81
Hypervisor vendor:     VMware
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              24576K
NUMA node0 CPU(s):     0-15
NUMA node1 CPU(s):     
NUMA node2 CPU(s):     
NUMA node3 CPU(s):     
NUMA node4 CPU(s):     
NUMA node5 CPU(s):     
NUMA node6 CPU(s):     
NUMA node7 CPU(s):     
 
内存:
[root@DBServer1 ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         15947       2501      13445          2         17        501
-/+ buffers/cache:       1982      13964
Swap:         8191          0       8191
 
从这里我刚入行,个人能看出来的一些优化点是numa没关,cpu内存分配不均。数据库使用的是虚拟机:
[root@DBServer1 ~]# grep -i numa /var/log/dmesg
NUMA: Allocated memnodemap from 100000 - 180840
NUMA: Using 20 for the hash shift.
 
考虑到这个问题可能分配不均导致使用了swap,结果使用top命令查看并没有使用swap。use为0
 
#########################################################
[client]
xxx略

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = xxx
socket          = /xxx/mysql.sock
pid-file        = /xxx/mysqlxxx.pid
log-bin=mysql-bin
server-id=17
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 8M
table_open_cache = 1024
join_buffer_size = 8M
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 0
query_cache_type= 0
max_tmp_tables = 256
tmp_table_size = 128M
max_heap_table_size=128M
thread_cache_size=32
binlog_format=mixed
thread_concurrency =16
max_connections         = 8096
max_user_connections    = 0
max_connect_errors      = 99999999
wait_timeout            = 1800
interactive_timeout     = 1800
slow_query_log          = 1
long_query_time         = 1
slow_query_log_file     = slow-queries.log
back_log                = 600
myisam_repair_threads   = 1
myisam-recover          = DEFAULT
expire_logs_days        = 7
skip-name-resolve      
lower_case_table_names = 1
init-connect='SET NAMES utf8'
character-set-server=utf8
skip-character-set-client-handshake
log-bin=mysql-bin        
server-id=225



[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
需要调整的有innodb_buffer_pool_size要调大一些。
 
# 通过perf top排查

Snip20180115_18.png

 
发现如上的三个内容占用高,其中通过google得知和锁有关系,因此进一步排查死锁问题。
 
# show engine innodb status
mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
180115 17:38:35 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 44 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6920448 1_second, 6920269 sleeps, 690288 10_second, 19774 background, 19764 flush
srv_master_thread log flush and writes: 6977803
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 179513620, signal count 146147168
Mutex spin waits 65648061092, rounds 139062256955, OS waits 121553159
RW-shared spins 54847572, rounds 1128724122, OS waits 21152090
RW-excl spins 10026179, rounds 267906127, OS waits 5306233
Spin rounds per wait: 2.12 mutex, 20.58 RW-shared, 26.72 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
180115 17:34:57
*** (1) TRANSACTION:
TRANSACTION 96348A84, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1
MySQL thread id 6644424, OS thread handle 0x7ff1020ee700, query id 1662241960 192.168.11.16 sxit updating
delete from offlinemsg where destuserid='97070' and srcmsgid='6931225542621777490' and terminaltype='2'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 195888 n bits 568 index `index_destuserid` of table `lhdc`.`offlinemsg` trx id 96348A84 lock_mode X waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00017b2e; asc   {.;;
 1: len 8; hex 800000000ea2425f; asc       B_;;

*** (2) TRANSACTION:
TRANSACTION 96348A83, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 1
MySQL thread id 6639251, OS thread handle 0x7ff1281f2700, query id 1662241980 192.168.11.16 sxit update
INSERT INTO iostokenstatus(userid, devicetoken, status, manual) VALUES('85058', '3e61b8155d484d7cdc52f54bf9575f2df679ec0be93ea8cc09278cb0aaef1041', '1', '0') ON DUPLICATE KEY UPDATE devicetoken = '3e61b8155d484d7cdc52f54bf9575f2df679ec0be93ea8cc09278cb0aaef1041', status = '1', manual = '0'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 195888 n bits 568 index `index_destuserid` of table `lhdc`.`offlinemsg` trx id 96348A83 lock_mode X
Record lock, heap no 27 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00017b2e; asc   {.;;
 1: len 8; hex 800000000ea2425f; asc       B_;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 57905 n bits 240 index `PRIMARY` of table `lhdc`.`iostokenstatus` trx id 96348A83 lock_mode X locks rec but not gap waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 00014c42; asc   LB;;
 1: len 6; hex 000096348a84; asc    4  ;;
 2: len 7; hex 79000cc00d26a1; asc y    & ;;
 3: len 30; hex 336536316238313535643438346437636463353266353462663935373566; asc 3e61b8155d484d7cdc52f54bf9575f; (total 64 bytes);
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 5a5c74e4; asc Z\t ;;
 6: len 1; hex 30; asc 0;;
发现的确是有死锁,不过过一会就消失了,换成了新的死锁。timeout时间为50.
 
## 慢查询日志,慢查询日志普遍为1s左右,没有占用时间太长的,show full processlist也没有占用太久的查询线程。
已邀请:

lamber - 90后

赞同来自:

补一个dstat的图
dstat.png

lamber - 90后

赞同来自:

查看表锁也没有内容:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
Empty set (0.03 sec)

lamber - 90后

赞同来自:

仔细看一下innodb buffer pool的命中率相当低,需要增加innodb_buffer_pool_size的大小。

card_2005

赞同来自:

建议申请重启先把innodb_buffer_pool_size增大,大小建议8~12g都行(参考你的数据集大小),重启顺便调下其他参数
我没看到你的版本信息,如果是5.7buffer pool支持动态调整,不用重启的
 
 
 
G
M
T
 
 
Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu
 AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu
 
 
 
 
 
 
 
 
 
Text-to-speech function is limited to 200 characters
 
 Options : History : Feedback : DonateClose

要回复问题请先登录注册