MySQL不断崩溃 [英] MySQL keeps crashing

问题描述

我在云端有一台运行 Ubuntu 12.10 的服务器，有 512Mb 的 RAM 只是为了运行一个 wordpress 网站(大约每天访问 1000 次).

I Have a server in the cloud running Ubuntu 12.10, with 512Mb of RAM just to run a wordpress website (with approx 1000 visits/day).

MySQL 总是崩溃然后我启用了 4Gb 交换以查看是否可以工作...但仍然崩溃...我每次都需要重新启动服务...

MySQL was always crashing then I enabled a 4Gb swap to see if can works... but still crashing... and I need to restart the service every time...

检查来自 mysql 的错误日志我注意到 InnoDB 似乎处于一个试图恢复某些东西的循环中，但我认为它不能......有人可以帮助我吗?

Checking the error log from mysql I noticed that InnoDB appears to be in a loop trying to recover something but I think it can't... can anyone help me?

131009 17:56:57 InnoDB: 5.5.32 started; log sequence number 242183133
131009 17:56:57 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:56:57 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:56:57 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:56:57 [Note] Event Scheduler: Loaded 0 events
131009 17:56:57 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:57:25 [Note] Plugin 'FEDERATED' is disabled.
131009 17:57:25 InnoDB: The InnoDB memory heap is disabled
131009 17:57:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:57:25 InnoDB: Compressed tables use zlib 1.2.7
131009 17:57:25 InnoDB: Using Linux native AIO
131009 17:57:25 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:57:25 InnoDB: Completed initialization of buffer pool
131009 17:57:25 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131009 17:57:25  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131009 17:57:26  InnoDB: Waiting for the background threads to start
131009 17:57:27 InnoDB: 5.5.32 started; log sequence number 242183133
131009 17:57:27 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:57:27 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:57:27 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:57:27 [Note] Event Scheduler: Loaded 0 events
131009 17:57:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:58:05 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:05 InnoDB: The InnoDB memory heap is disabled
131009 17:58:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:05 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:05 InnoDB: Using Linux native AIO
131009 17:58:05 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:05 InnoDB: Completed initialization of buffer pool
131009 17:58:06 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: The log sequence number in the ib_logfiles!
131009 17:58:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131009 17:58:06  InnoDB: Waiting for the background threads to start
131009 17:58:07 InnoDB: 5.5.32 started; log sequence number 242183143
131009 17:58:07 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:58:07 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:58:07 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:58:07 [Note] Event Scheduler: Loaded 0 events
131009 17:58:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:58:33 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:33 InnoDB: The InnoDB memory heap is disabled
131009 17:58:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:33 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:33 InnoDB: Using Linux native AIO
131009 17:58:33 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:34 InnoDB: Completed initialization of buffer pool
131009 17:58:34 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 242183143
131009 17:58:34  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 242183153
131009 17:58:34  InnoDB: Waiting for the background threads to start
131009 17:58:35 InnoDB: 5.5.32 started; log sequence number 242183153
131009 17:58:35 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:58:35 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:58:35 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:58:35 [Note] Event Scheduler: Loaded 0 events
131009 17:58:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:58:44 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:44 InnoDB: The InnoDB memory heap is disabled
131009 17:58:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:44 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:44 InnoDB: Using Linux native AIO
131009 17:58:45 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:45 InnoDB: Completed initialization of buffer pool
131009 17:58:45 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: The log sequence number in the ib_logfiles!
131009 17:58:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131009 17:58:45  InnoDB: Waiting for the background threads to start
131009 17:58:47 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:47 InnoDB: The InnoDB memory heap is disabled
131009 17:58:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:47 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:47 InnoDB: Using Linux native AIO
131009 17:58:47 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:47 InnoDB: Completed initialization of buffer pool
131009 17:58:47 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: The log sequence number in the ib_logfiles!
131009 17:58:47  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131009 17:58:47  InnoDB: Waiting for the background threads to start
131009 17:58:48 InnoDB: 5.5.32 started; log sequence number 242183153
131009 17:58:48 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:58:48 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:58:48 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:58:48 [Note] Event Scheduler: Loaded 0 events
131009 17:58:48 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

推荐答案

有一些事情可以帮助您的安装运行而不会出现任何崩溃.我过去在使用 WordPress 时遇到过类似问题，很可能是您没有正确使用 InnoDB.从上面日志的外观以及您添加了一个重要交换的帖子中的详细信息来看，它可能没有做任何事情，或者使问题变得更糟......

There's a couple of things that will help your installation run without any crashes. I've had similar issues in the past with WordPress, and it's likely that you're either not utilising your InnoDB correctly. From the looks of the log above, as well as details from the post you've added a significant Swap, which has likely either done nothing, or made the problem worse...

首先，您的 4GB 交换内存对于这么小的服务器来说太高了.您的大部分进程可能是通过交换内存使用的.将交换内存减少到 512mb 或 1gb 并从那里进行故障排除.第二件事是检查交换配置.如果它太高，那么您的系统将积极使用交换内存 - 这会浪费周期并减慢数据恢复速度，如果它太低，那么您可能根本就没有它.默认值为 60，我建议从 10 开始，然后从那里开始，直到找到服务器的最佳位置".有关如何在您的系统上更改它，请参阅此 Ubuntu 文章:https://help.ubuntu.com/community/SwapFaq#What_is_swappiness_and_how_do_I_change_it.3F

First thing's first, your 4GB swap memory is far too high for such a small server. The majority of your processes are likely being used through swap memory. Reduce the swap memory to 512mb or 1gb and troubleshoot from there. Second thing to do is check out the swapiness configuration. If it's too high then your system will aggressively use swap memory - which wastes cycles, and slows data recovery, if it's too low then you might as well not have it at all. The default is 60, I recommend starting at 10 and working your way up from there until you find a "sweet spot" for your server. See this Ubuntu article for how to change it on your system: https://help.ubuntu.com/community/SwapFaq#What_is_swappiness_and_how_do_I_change_it.3F

您的 InnoDB 缓冲区很小，只有 128.0MB.4GB 交换内存可能永远不会被 MySQL 触及(所以你的交换可能正在被 Apache/PHP 使用，如果有的话).这可能就是它崩溃的原因.将您的 InnoDB 至少增加一倍，实际上我建议尝试在该缓冲区中拥有完整的 DB，但我知道由于 RAM 限制，这实际上是不可能的.尝试将 RAM 大小设为 1/2，看看它与之前相比如何运行，然后根据您的结果降低或增加它.MySQL Workbench 有一个服务器状态"工具，可以告诉您您的 InnoDB 缓冲区使用情况，理想情况下，您希望它以低于 100%(理想情况下为 80-90%)的速度运行，并有足够的冗余空间，以防出现任何峰值.

Your InnoDB buffer is pretty small at 128.0MB. The 4GB swap memory is likely never touched by MySQL (so your swap is probably being used by Apache/PHP if at all). That's probably why it's crashing. Increase your InnoDB by at least double, in reality I'd suggest trying to have the full DB in that buffer, but I understand it's not really possible due to RAM constraints. Try to aim for 1/2 your RAM size, see how it runs compared to before and lower or increase it depending on your results. MySQL Workbench has a 'Server Status' tool that telly you your InnoDB Buffer Usage, ideally you want it to run at just under 100% (80-90% in an ideal world) with enough redundant space in case you get any spikes.

快速说明:如果您将整个数据库放入 RAM 中，您将需要做得更健壮频繁的数据库备份(它是不稳定的，如果一切都在那里并且系统断电，那你就搞砸了).

A quick note: If you put your entire DB into RAM you'll need to do more robust & frequent db backups (it's volatile, and if everything's in there and the system looses power that's you screwed).

如果这些仍然没有产生任何重大影响，那么请为您的 WP 站点寻找更强大的缓存.加载每个请求的每个页面将非常繁重，并且可以轻松解决.查看 MySQL 上的服务器统计页面，并查看您的站点每秒运行的查询数，与每秒 InnoDB 读/写数相比.

If these still don't make any big impacts, then look into more robust cacheing for your WP site. Loading every page on every request is going to be pretty taxing, and could be resolved without too much effort. Check out the Server Stats page on MySQL and look up how many Queries Per Second your site is running, compared to InnoDB reads/writes per second.

这篇关于MySQL不断崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！