生产数据库意外重启的分析
本篇内容介绍了“生产数据库意外重启的分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
成都创新互联公司是创新、创意、研发型一体的综合型网站建设公司，自成立以来公司不断探索创新，始终坚持为客户提供满意周到的服务，在本地打下了良好的口碑，在过去的十多年时间我们累计服务了上千家以及全国政企客户，如服务器托管等企业单位，完善的项目管理流程，严格把控项目进度与质量监控加上过硬的技术实力获得客户的一致赞誉。
排查
先交代一下数据库版本：
MySQL> status -------------- mysql Ver 14.14 Distrib 5.7.22-22, for Linux (x86_64) using 6.2 Connection id: 59568 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c Protocol version: 10
崩溃故障排除绝不是一项有趣的任务，特别是如果MySQL没有报告崩溃的原因。例如，当MySQL内存不足时。
数据库邮件告警提醒发来的消息：
Type: mysql Tags: 生产主库 Host: 172.16.1.66:3306 Level: critical Item: connect Value: down Message: mysql server down
登录 Grafana 监控面板，数据库连接在哪个时间段曾有幅度的增长。
顺手检查一下之前的服务器邮件监控告警记录，上一个时间点，内存占用率99%，这说明了数据库连接的幅度增长，可能是压垮服务器的最后一根稻草。
其实导致OOM的直接原因并不复杂，就是因为服务器内存不足，内核需要回收内存，回收内存就是kill掉服务器上使用内存最多的程序，而MySQL服务可能就是使用内存最多，所以就OOM了。
Type: os Tags: 66数据库 Host: 172.16.1.66: Level: critical Item: memory Value: 99% Message: too more memory usage
查看系统日志
我们带着这个疑问来排查一下日志：
# 查看日志 tail -500f /var/log/messages # 以下是 oom-killer Nov 27 14:55:48 itstyledb1 kernel: mysqld invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0 Nov 27 14:55:48 itstyledb1 kernel: mysqld cpuset=/ mems_allowed=0-1 Nov 27 14:55:48 itstyledb1 kernel: CPU: 2 PID: 895 Comm: mysqld Kdump: loaded Not tainted 3.10.0-862.3.2.el7.x86_64 #1 Nov 27 14:55:48 itstyledb1 kernel: Hardware name: Huawei RH1288 V3/BC11HGSC0, BIOS 3.22 05/16/2016
小伙伴们继续往下看：
0 pages HighMem/MovableOnly Nov 27 14:55:48 itstyledb1 kernel: 291281 pages reserved Nov 27 14:55:48 itstyledb1 kernel: [ pid ] uid tgid total_vm rss nr_ptes swapents oom_score_adj name Nov 27 14:55:48 itstyledb1 kernel: [ 468] 0 468 28271 4326 62 55 0 systemd-journal Nov 27 14:55:48 itstyledb1 kernel: [ 490] 0 490 11492 2 24 553 -1000 systemd-udevd Nov 27 14:55:48 itstyledb1 kernel: [ 787] 0 787 13877 18 27 96 -1000 auditd Nov 27 14:55:48 itstyledb1 kernel: [ 810] 81 810 14552 81 34 89 -900 dbus-daemon Nov 27 14:55:48 itstyledb1 kernel: [ 815] 0 815 55956 1 60 466 0 abrtd Nov 27 14:55:48 itstyledb1 kernel: [ 816] 0 816 55327 9 64 346 0 abrt-watch-log Nov 27 14:55:48 itstyledb1 kernel: [ 818] 0 818 121607 220 90 495 0 NetworkManager Nov 27 14:55:48 itstyledb1 kernel: [ 822] 0 822 5415 49 16 33 0 irqbalance Nov 27 14:55:48 itstyledb1 kernel: [ 823] 997 823 134634 97 60 1306 0 polkitd Nov 27 14:55:48 itstyledb1 kernel: [ 825] 0 825 6594 42 20 41 0 systemd-logind Nov 27 14:55:48 itstyledb1 kernel: [ 830] 0 830 31578 28 21 139 0 crond Nov 27 14:55:48 itstyledb1 kernel: [ 839] 0 839 27522 2 10 31 0 agetty Nov 27 14:55:48 itstyledb1 kernel: [ 1142] 0 1142 143454 114 97 2672 0 tuned Nov 27 14:55:48 itstyledb1 kernel: [ 1144] 0 1144 28203 11 59 246 -1000 sshd Nov 27 14:55:48 itstyledb1 kernel: [ 1145] 0 1145 97438 694 103 328 0 rsyslogd Nov 27 14:55:48 itstyledb1 kernel: [ 1369] 0 1369 22526 20 44 256 0 master Nov 27 14:55:48 itstyledb1 kernel: [ 1371] 89 1371 22596 32 46 251 0 qmgr Nov 27 14:55:48 itstyledb1 kernel: [ 5140] 0 5140 5102 1617 15 239 0 mysqld_exporter Nov 27 14:55:48 itstyledb1 kernel: [ 9430] 0 9430 55966 378 62 790 0 snmpd Nov 27 14:55:48 itstyledb1 kernel: [30320] 27 30320 22951376 13928375 43437 8163662 0 mysqld Nov 27 14:55:48 itstyledb1 kernel: [ 688] 89 688 22552 271 46 0 0 pickup Nov 27 14:55:48 itstyledb1 kernel: Out of memory: Kill process 30320 (mysqld) score 984 or sacrifice child Nov 27 14:55:48 itstyledb1 kernel: Killed process 30320 (mysqld) total-vm:91805504kB, anon-rss:55713500kB, file-rss:0kB, shmem-rss:0kB Nov 27 14:56:00 itstyledb1 systemd: mysqld.service: main process exited, code=killed, status=9/KILL Nov 27 14:56:00 itstyledb1 systemd: Unit mysqld.service entered failed state. Nov 27 14:56:00 itstyledb1 systemd: mysqld.service failed. Nov 27 14:56:00 itstyledb1 systemd: mysqld.service holdoff time over, scheduling restart. Nov 27 14:56:01 itstyledb1 systemd: Starting MySQL Server...
当out of memory发生时，outofmemory函数会选择一个内核认为犯有分配过多内存 “罪行”的进程，并杀死该进程。显然 Mysql 就是哪个“罪人”。
随后 MySql 会自动重启。重启以后，内存是下来了，但是临近下班的时候，差不多又又又占满了。
[root@itstyledb1 ~]# free -m total used free shared buff/cache available Mem: 55803 54976 241 10 585 349 Swap: 32064 25036 7028
找到MySql进程，执行以下top -p pid，内存使用52.4g
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 935 mysql 20 0 79.7g 52.4g 7336 S 0.3 96.1 255:44.76 mysqld
计算内存使用
1)查看MySQL全局占用多少内存
SELECT (@@innodb_buffer_pool_size +@@innodb_log_buffer_size +@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;
查询结果为：
+----------------+ | MEMORY_MB | +----------------+ | 20512.00000000 | +----------------+
2）查看performance_schema占用多少内存
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
查询结果为：
+---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/performance_schema | 349.80 MiB | +---------------------------+---------------+
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) AS MEMORY_MB;
查询结果为：
+-----------+ | MEMORY_MB | +-----------+ | 87.5156 | +-----------+
查看当前线程
show full processlist
最终结果为：
+-----------+ | MEMORY_MB | +-----------+ | 87.5156*37| +-----------+
4）查看 memory 存储引擎占用多少内存
SELECT SUM(max_data_length)/1024/1024 AS MEMORY_MB FROM information_schema.tables WHERE ENGINE='memory';
查询结果为：
+---------------+ | MEMORY_MB | +---------------+ | 3857.37713909 | +---------------+
以上四项加起来差不多也就27975MB，差不错28G的样子，但是 MySql 进程显示占用了52.4G，那么剩下24.4G去哪了？
线程池
此线程池非彼连接池，其实两者是有很大区别的，连接池一般在客户端设置，而线程池是在DB服务器上配置；另外连接池可以取到避免了连接频繁创建和销毁，但是无法取到控制MySQL活动线程数的目标，在高并发场景下，无法取到保护DB的作用。比较好的方式是将连接池和线程池结合起来使用。
关于线程池的一些参数：
mysql> show variables like '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。
最终配置如下：
#thread pool thread_handling=pool-of-threads #Group的数量，默认为系统CPU的个数，充分利用CPU资源 thread_pool_size=24 #每个group的最大线程数为thread_pool_oversubscribe+1 thread_pool_oversubscribe=3 performance_schema=off #extra connection，防止线程池满的情况下无法登录MySQL extra_max_connections = 8 extra_port = 33333
备注：线程池在Percona,MariaDB,Oracle MySQL企业版中提供，Oracle MySQL社区版并不提供。
线程池貌似并不会直接导致内存不回收，网上有说同时开启Thread pool和PS会出现内存泄露，但是 目前Percona server 5.7.21-20+版本已经修复了这个问题，显然是不存在的。
慢查询
由于是生产环境，这个问题拖得时间有点长，那么慢查询会不会影响内存使用问题呢？带着这个问题，查看了慢查询后台列表，在数据库奔溃的前一个时间段，的确有不少慢查询语句。但是这并不能在一定程度上说明问题，由于服务器的 MySql 服务在杀死之前，内存已经见底，此时连接数并不多，也就三四十来个左右，大多处于休眠状态，并且此时已经占用了大部分的Swap空间。也就是说，在资源有限的情况下必定会出现不少慢查询语句。
“生产数据库意外重启的分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
网站标题：生产数据库意外重启的分析
文章路径：http://scyanting.com/article/giegie.html
