本篇内容介绍了“生产数据库意外重启的分析”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

排查

先交代一下数据库版本:

mysql>status--------------mysqlVer14.14Distrib5.7.22-22,forLinux(x86_64)using6.2Connectionid:59568Currentdatabase:Currentuser:root@localhostSSL:NotinuseCurrentpager:stdoutUsingoutfile:''Usingdelimiter:;Serverversion:5.7.22-22-logPerconaServer(GPL),Release22,Revisionf62d93cProtocolversion:10

崩溃故障排除绝不是一项有趣的任务,特别是如果MySQL没有报告崩溃的原因。例如,当MySQL内存不足时。

数据库邮件告警提醒发来的消息:

Type:mysqlTags:生产主库Host:172.16.1.66:3306Level:criticalItem:connectValue:downMessage:mysqlserverdown

登录 Grafana 监控面板,数据库连接在哪个时间段曾有幅度的增长。

顺手检查一下之前的服务器邮件监控告警记录,上一个时间点,内存占用率99%,这说明了数据库连接的幅度增长,可能是压垮服务器的最后一根稻草。

其实导致OOM的直接原因并不复杂,就是因为服务器内存不足,内核需要回收内存,回收内存就是kill掉服务器上使用内存最多的程序,而MySQL服务可能就是使用内存最多,所以就OOM了。

Type:osTags:66数据库Host:172.16.1.66:Level:criticalItem:memoryValue:99%Message:toomorememoryusage查看系统日志

我们带着这个疑问来排查一下日志:

#查看日志tail-500f/var/log/messages#以下是oom-killerNov2714:55:48itstyledb1kernel:mysqldinvokedoom-killer:gfp_mask=0x201da,order=0,oom_score_adj=0Nov2714:55:48itstyledb1kernel:mysqldcpuset=/mems_allowed=0-1Nov2714:55:48itstyledb1kernel:CPU:2PID:895Comm:mysqldKdump:loadedNottainted3.10.0-862.3.2.el7.x86_64#1Nov2714:55:48itstyledb1kernel:Hardwarename:HuaweiRH1288V3/BC11HGSC0,BIOS3.2205/16/2016

小伙伴们继续往下看:

0pagesHighMem/MovableOnlyNov2714:55:48itstyledb1kernel:291281pagesreservedNov2714:55:48itstyledb1kernel:[pid]uidtgidtotal_vmrssnr_ptesswapentsoom_score_adjnameNov2714:55:48itstyledb1kernel:[468]046828271432662550systemd-journalNov2714:55:48itstyledb1kernel:[490]049011492224553-1000systemd-udevdNov2714:55:48itstyledb1kernel:[787]078713877182796-1000auditdNov2714:55:48itstyledb1kernel:[810]8181014552813489-900dbus-daemonNov2714:55:48itstyledb1kernel:[815]0815559561604660abrtdNov2714:55:48itstyledb1kernel:[816]0816553279643460abrt-watch-logNov2714:55:48itstyledb1kernel:[818]0818121607220904950NetworkManagerNov2714:55:48itstyledb1kernel:[822]082254154916330irqbalanceNov2714:55:48itstyledb1kernel:[823]997823134634976013060polkitdNov2714:55:48itstyledb1kernel:[825]082565944220410systemd-logindNov2714:55:48itstyledb1kernel:[830]08303157828211390crondNov2714:55:48itstyledb1kernel:[839]083927522210310agettyNov2714:55:48itstyledb1kernel:[1142]011421434541149726720tunedNov2714:55:48itstyledb1kernel:[1144]01144282031159246-1000sshdNov2714:55:48itstyledb1kernel:[1145]01145974386941033280rsyslogdNov2714:55:48itstyledb1kernel:[1369]013692252620442560masterNov2714:55:48itstyledb1kernel:[1371]8913712259632462510qmgrNov2714:55:48itstyledb1kernel:[5140]0514051021617152390mysqld_exporterNov2714:55:48itstyledb1kernel:[9430]0943055966378627900snmpdNov2714:55:48itstyledb1kernel:[30320]273032022951376139283754343781636620mysqldNov2714:55:48itstyledb1kernel:[688]89688225522714600pickupNov2714:55:48itstyledb1kernel:Outofmemory:Killprocess30320(mysqld)score984orsacrificechildNov2714:55:48itstyledb1kernel:Killedprocess30320(mysqld)total-vm:91805504kB,anon-rss:55713500kB,file-rss:0kB,shmem-rss:0kBNov2714:56:00itstyledb1systemd:mysqld.service:mainprocessexited,code=killed,status=9/KILLNov2714:56:00itstyledb1systemd:Unitmysqld.serviceenteredfailedstate.Nov2714:56:00itstyledb1systemd:mysqld.servicefailed.Nov2714:56:00itstyledb1systemd:mysqld.serviceholdofftimeover,schedulingrestart.Nov2714:56:01itstyledb1systemd:StartingMySQLServer...

当out of memory发生时,outofmemory函数会选择一个内核认为犯有分配过多内存 “罪行”的进程,并杀死该进程。显然 Mysql 就是哪个“罪人”。

随后 MySql 会自动重启。重启以后,内存是下来了,但是临近下班的时候,差不多又又又占满了。

[root@itstyledb1~]#free-mtotalusedfreesharedbuff/cacheavailableMem:558035497624110585349Swap:32064250367028

找到MySql进程,执行以下top -p pid,内存使用52.4g

PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND935mysql20079.7g52.4g7336S0.396.1255:44.76mysqld计算内存使用

1)查看MySQL全局占用多少内存

SELECT(@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size)/1024/1024ASMEMORY_MB;

查询结果为:

+----------------+|MEMORY_MB|+----------------+|20512.00000000|+----------------+

2)查看performance_schema占用多少内存

SELECTSUBSTRING_INDEX(event_name,'/',2)AScode_area,sys.format_bytes(SUM(current_alloc))AScurrent_allocFROMsys.x$memory_global_by_current_bytesGROUPBYSUBSTRING_INDEX(event_name,'/',2)ORDERBYSUM(current_alloc)DESC;

查询结果为:

+---------------------------+---------------+|code_area|current_alloc|+---------------------------+---------------+|memory/performance_schema|349.80MiB|+---------------------------+---------------+

3)查看每个线程占用多少内存

SELECT((@@read_buffer_size+@@read_rnd_buffer_size+@@sort_buffer_size+@@join_buffer_size+@@binlog_cache_size+@@thread_stack+@@max_allowed_packet+@@net_buffer_length))/(1024*1024)ASMEMORY_MB;

查询结果为:

+-----------+|MEMORY_MB|+-----------+|87.5156|+-----------+

查看当前线程

showfullprocesslist

最终结果为:

+-----------+|MEMORY_MB|+-----------+|87.5156*37|+-----------+

4)查看 memory 存储引擎占用多少内存

SELECTSUM(max_data_length)/1024/1024ASMEMORY_MBFROMinformation_schema.tablesWHEREENGINE='memory';

查询结果为:

+---------------+|MEMORY_MB|+---------------+|3857.37713909|+---------------+

以上四项加起来差不多也就27975MB,差不错28G的样子,但是 MySql 进程显示占用了52.4G,那么剩下24.4G去哪了?

线程池

此线程池非彼连接池,其实两者是有很大区别的,连接池一般在客户端设置,而线程池是在DB服务器上配置;另外连接池可以取到避免了连接频繁创建和销毁,但是无法取到控制MySQL活动线程数的目标,在高并发场景下,无法取到保护DB的作用。比较好的方式是将连接池和线程池结合起来使用。

关于线程池的一些参数:

mysql>showvariableslike'thread%';+-------------------------------+---------------------------+|Variable_name|Value|+-------------------------------+---------------------------+|thread_handling|one-thread-per-connection||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|12||thread_pool_stall_limit|500|+-------------------------------+---------------------------+thread_handling:

该参数是配置线程模型,默认情况是one-thread-per-connection,也就是不启用线程池。将该参数设置为pool-of-threads即启用了线程池。

threadpoolsize:

该参数是设置线程池的Group的数量,默认为系统CPU的个数,充分利用CPU资源。

threadpooloversubscribe:

该参数设置group中的最大线程数,每个group的最大线程数为threadpooloversubscribe+1,注意listener线程不包含在内。

threadpoolhighpriomode:

高优先级队列的控制参数,有三个值(transactions/statements/none),默认是transactions,三个值的含义如下:

transactions:对于已经启动事务的语句放到高优先级队列中,不过还取决于后面的threadpoolhighpriotickets参数

statements:这个模式所有的语句都会放到高优先级队列中,不会使用到低优先级队列

none:这个模式不使用高优先级队列

threadpoolhighpriotickets:

该参数控制每个连接最多语序多少次被放入高优先级队列中,默认为4294967295,注意这个参数只有在threadpoolhighpriomode为transactions的时候才有效果。

threadpoolidle_timeout:

worker线程最大空闲时间,默认为60秒,超过限制后会退出。

threadpoolmax_threads:

该参数用来限制线程池最大的线程数,超过该限制后将无法再创建更多的线程,默认为100000。

threadpoolstall_limit:

该参数设置timer线程的检测group是否异常的时间间隔,默认为500ms。

最终配置如下:

#threadpoolthread_handling=pool-of-threads#Group的数量,默认为系统CPU的个数,充分利用CPU资源thread_pool_size=24#每个group的最大线程数为thread_pool_oversubscribe+1thread_pool_oversubscribe=3performance_schema=off#extraconnection,防止线程池满的情况下无法登录MySQLextra_max_connections=8extra_port=33333

备注:线程池在Percona,MariaDB,Oracle MySQL企业版中提供,Oracle MySQL社区版并不提供。

线程池貌似并不会直接导致内存不回收,网上有说同时开启Thread pool和PS会出现内存泄露,但是 目前Percona server 5.7.21-20+版本已经修复了这个问题,显然是不存在的。

慢查询

由于是生产环境,这个问题拖得时间有点长,那么慢查询会不会影响内存使用问题呢?带着这个问题,查看了慢查询后台列表,在数据库奔溃的前一个时间段,的确有不少慢查询语句。但是这并不能在一定程度上说明问题,由于服务器的 MySql 服务在杀死之前,内存已经见底,此时连接数并不多,也就三四十来个左右,大多处于休眠状态,并且此时已经占用了大部分的Swap空间。也就是说,在资源有限的情况下必定会出现不少慢查询语句。

“生产数据库意外重启的分析”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!