1

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.

2
  • That is a bummer.
    – wogsland
    Feb 12, 2017 at 17:31
  • 1
    The mysqltuner script can tell you if MySQL is wasting memory that you can reclaim. Otherwise, you need to add more memory. Feb 12, 2017 at 21:42

2 Answers 2

5

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)

2
  • You are absolutely right! Please take a look at the edit. How can I prevent the killing :) ? Feb 12, 2017 at 21:40
  • What are your server specs? Maybe you are simply asking too much from your installation. Anyway, as a simple (but ineffective, due to bad performance) solution you can add more swap space.
    – shodanshok
    Feb 12, 2017 at 21:45
2

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

Add more memory.

2
  • How can I limit the memory MySQL consumes? Feb 12, 2017 at 21:41
  • 1
    You may try mysqltuner or similar but the real solution is to add more memory.
    – user9517
    Feb 12, 2017 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.