大网数据广告活动
服务器运维问答

MySQL automatically restarts under heavy load

· 52CE 我爱测

I am doing a performance test. When I reach 100-200 concurrent users, MySQL simply shuts down and starts again without any errors in the log files. Very, very strange. MySQL version is 5.6.34, running on CentOS 6 . Here is how I …

问题原文

原帖链接 · Martin Dimitrov · CC BY-SA 3.0

I am doing a performance test. When I reach 100-200 concurrent users, MySQL simply shuts down and starts again without any errors in the log files. Very, very strange. MySQL version is 5.6.34, running on CentOS 6. Here is how I have configured it:

[mysqld]
innodb_buffer_pool_size = 512M
max_connections = 9000
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
datadir = /data/mysql
socket = /data/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION

And here is how MySQL starts:

170212 19:06:15 mysqld_safe Number of processes running now: 0
170212 19:06:15 mysqld_safe mysqld restarted
2017-02-12 19:06:19 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-02-12 19:06:19 0 [Note] /usr/sbin/mysqld (mysqld 5.6.34) starting as process 26911 ...
2017-02-12 19:06:19 26911 [Note] Plugin 'FEDERATED' is disabled.
2017-02-12 19:06:19 26911 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-02-12 19:06:19 26911 [Note] InnoDB: The InnoDB memory heap is disabled
2017-02-12 19:06:19 26911 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-12 19:06:19 26911 [Note] InnoDB: Memory barrier is not used
2017-02-12 19:06:19 26911 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-02-12 19:06:19 26911 [Note] InnoDB: Using Linux native AIO
2017-02-12 19:06:19 26911 [Note] InnoDB: Using CPU crc32 instructions
2017-02-12 19:06:19 26911 [Note] InnoDB: Initializing buffer pool, size = 512.0M
2017-02-12 19:06:19 26911 [Note] InnoDB: Completed initialization of buffer pool
2017-02-12 19:06:19 26911 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-12 19:06:19 26911 [Note] InnoDB: The log sequence numbers 310826427 and 310826427 in ibdata files do not match the log sequence number 310826447 in the ib_logfiles!
2017-02-12 19:06:19 26911 [Note] InnoDB: Database was not shutdown normally!
2017-02-12 19:06:19 26911 [Note] InnoDB: Starting crash recovery.
2017-02-12 19:06:19 26911 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-02-12 19:06:19 26911 [Note] InnoDB: Restoring possible half-written data pages 
2017-02-12 19:06:19 26911 [Note] InnoDB: from the doublewrite buffer...
2017-02-12 19:06:19 26911 [Note] InnoDB: 128 rollback segment(s) are active.
2017-02-12 19:06:19 26911 [Note] InnoDB: Waiting for purge to start
2017-02-12 19:06:19 26911 [Note] InnoDB: 5.6.34 started; log sequence number 310826447
2017-02-12 19:06:19 26911 [Note] Server hostname (bind-address): '*'; port: 3306
2017-02-12 19:06:19 26911 [Note] IPv6 is available.
2017-02-12 19:06:19 26911 [Note]   - '::' resolves to '::';
2017-02-12 19:06:19 26911 [Note] Server socket created on IP: '::'.
2017-02-12 19:06:19 26911 [Note] Event Scheduler: Loaded 0 events
2017-02-12 19:06:19 26911 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.34'  socket: '/data/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Thanks in advance!


As suggested by @shodanshok, I looked in /var/log/messages. I found this:

Feb 12 18:06:08 prosveta kernel: Out of memory: Kill process 23277 (mysqld) score 351 or sacrifice child
Feb 12 18:06:08 prosveta kernel: Killed process 23277, UID 27, (mysqld) total-vm:3908768kB, anon-rss:2086896kB, file-rss:1916kB
Feb 12 18:06:08 prosveta kernel: php-cgi invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0
Feb 12 18:06:08 prosveta kernel: php-cgi cpuset=/ mems_allowed=0
Feb 12 18:06:08 prosveta kernel: Pid: 30182, comm: php-cgi Not tainted 2.6.32-642.11.1.el6.x86_64 #1

So indead MySQL was killed by the kernel but how can I avoid this? Thanks.

已采纳解答

原帖链接 · shodanshok · CC BY-SA 3.0

It is probably being killed by the kernel's OOM killer, and the surviving mysqld-safe restarts the main process. Check dmesg for interesting messages (try dmesg | grep -i oom)

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-02-12)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。