主页 / server / 问题 / 832139
Accepted
Martin Dimitrov
Asked: 2017-02-13 09:15:06 +0800 CST

MySQL在重负载下自动重启

  • 772

我正在做性能测试。当我达到 100-200 个并发用户时，MySQL只需关闭并重新启动，日志文件中不会出现任何错误。非常非常奇怪。MySQL版本是 5.6.34，运行在CentOS 6. 这是我的配置方式：

[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

这是如何MySQL开始的：

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)

提前致谢！

正如@shodanshok 所建议的那样，我查看了/var/log/messages. 我找到了这个：

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

所以 indeadMySQL被内核杀死了，但我怎样才能避免这种情况呢？谢谢。

mysql mysql5

2 个回答

  1. Best Answer

    很可能是被内核的OOM杀手杀死了，幸存者mysqld-safe重启了主进程。检查dmesg有趣的消息（尝试dmesg | grep -i oom）

    • 5

  2. 所以indead MySQL被内核杀死了，但我怎样才能避免这种情况呢？

    添加更多内存。

    • 2

相关问题