一次Mysql异常规律性挂起问题解决

缘起

    之前在vps上挂了blog,稳定运行了有2年了,但是最近突然vps启动一天左右之后就出现mysql数据库连接不上的问题,很是烦恼。

    开始的时候懒得查原因,直接每次down了重启了事,后来被搞烦了,就尝试来解决一下。


折腾过程

    1.问题表现

    大体表现是没次启动一天左右即每隔10min打印一段重新启动mysqld服务的message(在/var/log/mysqld.log中看到),然后mysql服务是彻底挂掉链接不上的状态,而此时重启机器后,又是一切正常,但是此时的mysqld进程的内存占用非常多,几乎占满所有的物理内存。

    错误日志如下:

    

2018-02-10 10:24:47 21499 [Note] Plugin 'FEDERATED' is disabled.
2018-02-10 10:24:47 21499 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-02-10 10:24:47 21499 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-10 10:24:47 21499 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-02-10 10:24:47 21499 [Note] InnoDB: Memory barrier is not used
2018-02-10 10:24:47 21499 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-02-10 10:24:47 21499 [Note] InnoDB: Using Linux native AIO
2018-02-10 10:24:47 21499 [Note] InnoDB: Using CPU crc32 instructions
2018-02-10 10:24:47 21499 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-02-10 10:24:47 21499 [Note] InnoDB: Completed initialization of buffer pool
180210 10:24:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180210 10:34:47 mysqld_safe Logging to '/var/log/mysqld.log'.
180210 10:34:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2018-02-10 10:34:47 0 [Warning] Using unique option prefix thread_cache instead of thread_cache_size is deprecated and will be removed in a future release. Please use the full name instead.
2018-02-10 10:34:47 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2018-02-10 10:34:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-02-10 10:34:47 0 [Note] /usr/sbin/mysqld (mysqld 5.6.39) starting as process 23198 ...
2018-02-10 10:34:47 23198 [Warning] Buffered warning: Could not increase number of max_open_files to more than 4096 (request: 4210)
2018-02-10 10:34:47 23198 [Warning] Buffered warning: Changed limits: table_open_cache: 1943 (requested 2000)
2018-02-10 10:34:47 23198 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2018-02-10 10:34:47 23198 [Note] Plugin 'FEDERATED' is disabled.
2018-02-10 10:34:47 23198 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-02-10 10:34:47 23198 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-10 10:34:47 23198 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-02-10 10:34:47 23198 [Note] InnoDB: Memory barrier is not used
2018-02-10 10:34:47 23198 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-02-10 10:34:47 23198 [Note] InnoDB: Using Linux native AIO
2018-02-10 10:34:47 23198 [Note] InnoDB: Using CPU crc32 instructions
2018-02-10 10:34:47 23198 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-02-10 10:34:47 23198 [Note] InnoDB: Completed initialization of buffer poo

    

    2、增大最大打开文件数限制

        由于看到日志中有打开文件数超出限制的错误(Could not increase number of max_open_files to more than 4096 (request: 4210)),开始尝试增大打开文件数的限制,发现增大之后和之前一样,在开机时没有问题,运行一段时间后出现问题

        于是,这种方案被放弃~~~


    3、减小mysql连接数以及缓存cache大小

        由于看到在开机启动时即有90%以上的内存占用,怀疑本次问题是由于内存不够导致的,于是尝试较少数据库最大连接数以及cache的大小。

        经过验证发现,减少连接数之后,打开文件超出限制的问题没在出现了,但是内存占用依然是在90%,问题依然没有解决


    4、关闭mysql性能监控表

        经过继续研究,发现mysqltuner.pl可以输出详细的数据库状态来,安装完成后发现,开机时的内存也是非常非常高了,P_S Max memory usage: 0B 这一项的数据非常高,几乎就是物理内存的大小

        然后晚上也没有找到任何关于这个变量的说明,应该是分配的物理内存吧。

        最后,在一篇5.6版本mysql加入性能监控功能的文章中,终于找到问题所在;由于mysql默认开启了性能监控,用于监控日常数据库的操作性能等等数据,但是这个东西貌似有bug或者时没有做好数量限制,导致这块的东西非常多,以至于占用了非常高的内存,最后导致运行一段时间后的崩溃,并且不断尝试拉起的问题。


    在my.conf的[mysqld]段中加入下面两行即可解决

    

table_definition_cache = 400
performance_schema=0

    然后重新启动数据库进程,世界终于清静了,内存占用急剧下降,从原来的500MB下降到只有20~30MB,而通过最近的观察,也非常稳定,没有任何的down掉的情况


总结

    只能说mysql在这一块做的确实不是很好,一个512MB内存的vps上,数据库总的容量才只有70MB,最后居然能被性能数据的表搞到内存爆炸。。。。。而且在日志中,根本找不到任何有用的信息,只能看到restart every 10 minutes。。。实在是坑爹~~~


参考文档

https://dba.stackexchange.com/questions/83077/mysql-server-5-6-mysqld-high-memory-consumption

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html

https://stackoverflow.com/questions/23003626/mysql-optimization-mysqls-maximum-memory-usage-is-dangerously-high-but-still


发表评论

必填

选填

选填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。