MySQL automatically restarts under heavy load

MySQLmysql5

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.

Best Answer

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)

Related Topic