Linux mysqld 服务每天在 ec2 服务器上停止一次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12114746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqld service stops once a day on ec2 server
提问by Aamir Adnan
Environment Details:
环境详情：
Server: Amazon ec2 Linux
Web Server: Apache
Web Framework: Django with mod_wsgi
Following I have found in the mysql_err.log file.
下面我在 mysql_err.log 文件中找到了。
The InnoDB memory heap is disabled
120823 3:21:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120823 3:21:40 InnoDB: Compressed tables use zlib 1.2.3
120823 3:21:40 InnoDB: Using Linux native AIO
120823 3:21:41 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
120823 3:21:41 InnoDB: Completed initialization of buffer pool
120823 3:21:41 InnoDB: Fatal error: cannot allocate memory for the buffer pool
120823 3:21:41 [ERROR] Plugin 'InnoDB' init function returned error.
120823 3:21:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120823 3:21:41 [ERROR] Unknown/unsupported storage engine: InnoDB
120823 3:21:41 [ERROR] Aborting
Looks like the system memory is not sufficient to allocate memory to buffer pool. Same error happens when I was using Amazon ec2 micro instance, So I moved to the small instance. It works fine for some days but now it is breaking once in a day again. Is there a permanent fix for that? I can move to medium instance but the issue is will that be fixed or not? Should I decrease the innodb_buffer_pool_size, what is the preferred size?
看起来系统内存不足以为缓冲池分配内存。我在使用时发生了同样的错误Amazon ec2 micro instance，所以我移到了small instance. 它可以正常工作几天，但现在它每天再次中断。有没有永久性的解决办法？我可以移动到中等实例，但问题是会不会被修复？我应该减少innodb_buffer_pool_size，首选大小是多少？
The result of cat /proc/meminfois following (may be it will help):
结果cat /proc/meminfo如下（可能会有所帮助）：
MemTotal: 1697824 kB
MemFree: 125744 kB
Buffers: 109704 kB
Cached: 481408 kB
SwapCached: 0 kB
Active: 1212396 kB
Inactive: 266840 kB
Active(anon): 888192 kB
Inactive(anon): 76 kB
Active(file): 324204 kB
Inactive(file): 266764 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 0 kB
SwapFree: 0 kB
Dirty: 4 kB
Writeback: 0 kB
AnonPages: 888144 kB
Mapped: 15604 kB
Shmem: 144 kB
Slab: 63752 kB
SReclaimable: 53680 kB
SUnreclaim: 10072 kB
KernelStack: 800 kB
PageTables: 16436 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 848912 kB
Committed_AS: 1417140 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 10988 kB
VmallocChunk: 34359725168 kB
DirectMap4k: 1748992 kB
DirectMap2M: 0 kB
OS version (uname -a):
Linux ip-10-246-134-149 3.2.21-1.32.6.amzn1.x86_64 #1 SMP Sat Jun 23 02:32:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
操作系统版本（uname -a）：
Linux ip-10-246-134-149 3.2.21-1.32.6.amzn1.x86_64 #1 SMP Sat Jun 23 02:32:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
I checked the ps auxcommand whet the server has like only 15MB of memory left and these are the httpdprocess running at that time:
我检查了ps aux命令 whet 服务器只剩下 15MB 的内存，这些是当时httpd正在运行的进程：
The result of free -m
的结果 free -m
total used free shared buffers cached
Mem: 1657 1628 29 0 3 19
-/+ buffers/cache: 1605 51
Swap: 895 875 20
The result of ps aux
的结果 ps aux
apache 21123 0.1 1.2 394652 20464 ? S 19:35 0:06 /usr/sbin/httpd
apache 21146 0.1 1.2 394280 20796 ? S 19:38 0:06 /usr/sbin/httpd
apache 21152 0.1 1.2 394284 21560 ? S 19:38 0:05 /usr/sbin/httpd
apache 21155 0.2 1.4 396244 24528 ? S 19:38 0:06 /usr/sbin/httpd
apache 21156 0.1 1.1 392552 20344 ? S 19:38 0:06 /usr/sbin/httpd
apache 21157 0.1 1.1 394284 18884 ? S 19:38 0:05 /usr/sbin/httpd
apache 21159 0.1 1.4 396200 25040 ? S 19:38 0:06 /usr/sbin/httpd
apache 21161 0.1 1.2 394856 21724 ? S 19:38 0:06 /usr/sbin/httpd
apache 21162 0.1 1.3 394864 22400 ? S 19:38 0:06 /usr/sbin/httpd
apache 21163 0.1 1.3 394860 22204 ? S 19:38 0:06 /usr/sbin/httpd
apache 21164 0.1 1.1 392560 19204 ? S 19:38 0:06 /usr/sbin/httpd
apache 21165 0.1 1.3 394832 22280 ? S 19:38 0:06 /usr/sbin/httpd
apache 21166 0.1 1.3 395276 22932 ? S 19:38 0:06 /usr/sbin/httpd
apache 21172 0.2 1.4 396320 24820 ? S 19:38 0:06 /usr/sbin/httpd
apache 21174 0.2 1.7 400672 29452 ? S 19:39 0:06 /usr/sbin/httpd
apache 21178 0.1 1.4 400540 25304 ? S 19:39 0:06 /usr/sbin/httpd
apache 21179 0.2 1.6 400580 27856 ? S 19:39 0:06 /usr/sbin/httpd
apache 21184 0.1 1.7 400628 29320 ? S 19:39 0:06 /usr/sbin/httpd
apache 21185 0.1 1.6 397944 27292 ? S 19:39 0:05 /usr/sbin/httpd
apache 21186 0.1 1.5 397960 25648 ? S 19:39 0:05 /usr/sbin/httpd
apache 21187 0.1 1.7 400576 29120 ? S 19:39 0:06 /usr/sbin/httpd
apache 21191 0.1 1.4 400576 24400 ? S 19:39 0:06 /usr/sbin/httpd
apache 21193 0.1 1.4 400536 24940 ? S 19:39 0:05 /usr/sbin/httpd
apache 21194 0.1 1.5 400572 26096 ? S 19:39 0:06 /usr/sbin/httpd
apache 21203 0.1 1.6 400580 28808 ? S 19:39 0:05 /usr/sbin/httpd
apache 21206 0.1 1.7 400584 29732 ? S 19:39 0:06 /usr/sbin/httpd
apache 21207 0.1 1.6 400576 27940 ? S 19:39 0:06 /usr/sbin/httpd
apache 21224 0.1 1.2 400624 20768 ? S 19:39 0:06 /usr/sbin/httpd
apache 21225 0.1 1.6 400576 28468 ? S 19:39 0:05 /usr/sbin/httpd
apache 21226 0.1 1.6 400576 28048 ? S 19:39 0:06 /usr/sbin/httpd
apache 21228 0.1 1.4 400572 23880 ? S 19:39 0:06 /usr/sbin/httpd
apache 21237 0.1 1.5 400628 26124 ? S 19:39 0:06 /usr/sbin/httpd
apache 21265 0.1 1.6 400536 28592 ? S 19:39 0:06 /usr/sbin/httpd
apache 21276 0.1 1.2 400544 21456 ? S 19:39 0:05 /usr/sbin/httpd
apache 21277 0.1 1.3 400624 22676 ? S 19:39 0:05 /usr/sbin/httpd
apache 21278 0.1 1.6 400536 27360 ? S 19:39 0:06 /usr/sbin/httpd
apache 21282 0.1 1.4 400612 24996 ? S 19:39 0:06 /usr/sbin/httpd
apache 21292 0.1 1.4 400532 24780 ? S 19:39 0:05 /usr/sbin/httpd
apache 21302 0.2 1.2 400540 21332 ? S 19:39 0:06 /usr/sbin/httpd
apache 21303 0.1 1.3 400628 22228 ? S 19:39 0:06 /usr/sbin/httpd
apache 21305 0.2 1.2 400536 21116 ? S 19:39 0:06 /usr/sbin/httpd
apache 21306 0.1 1.3 400572 22380 ? S 19:39 0:06 /usr/sbin/httpd
apache 21307 0.1 1.1 397956 20056 ? S 19:39 0:05 /usr/sbin/httpd
apache 21308 0.1 1.2 400624 21520 ? S 19:39 0:06 /usr/sbin/httpd
apache 21319 0.1 1.1 400540 19468 ? S 19:39 0:05 /usr/sbin/httpd
apache 21320 0.1 1.3 400628 22712 ? S 19:39 0:05 /usr/sbin/httpd
apache 21335 0.1 1.0 400540 17236 ? S 19:39 0:05 /usr/sbin/httpd
apache 21336 0.1 1.3 400628 22188 ? S 19:39 0:06 /usr/sbin/httpd
apache 21352 0.1 1.1 394276 18972 ? S 19:40 0:04 /usr/sbin/httpd
apache 21356 0.1 1.1 394280 19028 ? S 19:40 0:05 /usr/sbin/httpd
apache 21358 0.1 1.1 394280 19004 ? S 19:40 0:05 /usr/sbin/httpd
apache 21361 0.2 0.7 400452 12632 ? S 19:40 0:06 /usr/sbin/httpd
apache 21610 0.2 1.6 400536 27660 ? S 19:46 0:06 /usr/sbin/httpd
apache 21643 0.2 1.3 400156 23272 ? S 19:55 0:04 /usr/sbin/httpd
apache 21647 0.2 1.0 400544 17556 ? S 19:57 0:05 /usr/sbin/httpd
apache 21654 0.2 1.5 400188 26884 ? S 19:58 0:05 /usr/sbin/httpd
apache 21719 0.3 1.9 400192 32264 ? S 20:14 0:03 /usr/sbin/httpd
apache 21725 0.2 2.0 400044 35340 ? S 20:15 0:03 /usr/sbin/httpd
apache 21738 0.0 0.8 257648 13792 ? S 20:26 0:00 /usr/sbin/httpd
Can any one one has an idea about it why there is so much httpd process??
任何人都可以知道为什么有这么多 httpd 进程吗？
回答by joelparkerhenderson
Use 50% of available RAM to test:
使用 50% 的可用 RAM 进行测试：
You can decrease the innodb_buffer_pool_size very low to see if it helps:
您可以将 innodb_buffer_pool_size 减小到非常低以查看是否有帮助：
#/etc/my.cnf
innodb_buffer_pool_size = 1M
A rule of thumb is to set innodb_buffer_pool_size to 50% of available RAM for your low memory testing. This means you start the server and everything exceptMySQL InnoDB. See how much RAM you have. Then use 50% of that for InnoDB.
经验法则是将 innodb_buffer_pool_size 设置为可用 RAM 的 50%，以进行低内存测试。这意味着您启动服务器和除MySQL InnoDB之外的所有内容。看看你有多少内存。然后将其中的 50% 用于 InnoDB。
To try many low-memory settings at once:
一次尝试多个低内存设置：
A more likely culprit is whatever else is on that server, such as a webserver.
更有可能的罪魁祸首是该服务器上的其他任何东西，例如网络服务器。
Apache?
阿帕奇？
Are you using Apache and/or another webserver? If so, try to decrease its RAM usage. For example in Apache conf, consider low RAM settings like these:
您使用的是 Apache 和/或其他网络服务器吗？如果是这样，请尝试减少其 RAM 使用量。例如在 Apache conf 中，请考虑像这样的低 RAM 设置：
StartServers 1
MinSpareServers 1
MaxSpareServers 5
MaxClients 5
And cap the requests like this:
并像这样限制请求：
MaxRequestsPerChild 300
Then restart Apache.
然后重新启动Apache。
mod_wsgi:
mod_wsgi：
If you're using Apache with mod_python, switch to Apache with mod_wsgi.
如果您使用带有 mod_python 的 Apache，请切换到带有 mod_wsgi 的 Apache。
Pympler:
皮姆勒：
If it's still happening, possibly your Django is steadily growing. Try Django memory profiling with Pympler:
如果它仍然发生，可能你的 Django 正在稳步增长。尝试使用 Pympler 进行 Django 内存分析：
SAR:
特区：
Your report of once-per-day failures, then once-per-week failures, could point to some kind of cron job running daily or weekly. For example, perhaps there's a batch process that takes up a lot of RAM, or a database dump, etc.
您每天或每周一次的失败报告，然后是每周一次的失败，可能指向某种每天或每周运行的 cron 作业。例如，也许有一个批处理占用大量 RAM，或者数据库转储等。
To track RAM use and look for RAM spikes in the hour before MySQL dies, take a look at SAR, which is a great tool: http://www.thegeekstuff.com/2011/03/sar-examples/
要在 MySQL 死机前一小时跟踪 RAM 使用情况并查找 RAM 峰值，请查看 SAR，这是一个很棒的工具：http: //www.thegeekstuff.com/2011/03/sar-examples/
回答by Rush
You have to decrease you innodb_buffer_pool_size = <60-80% of your main memory)
您必须减少 innodb_buffer_pool_size = <60-80% 的主内存）
Solution for Innodb Error:
Innodb错误的解决方法：
110603 7:34:15 [ERROR] Plugin ‘InnoDB' init function returned error.
110603 7:34:15 [ERROR] Plugin ‘InnoDB' registration as a STORAGE ENGINE failed.
110603 7:34:15 [ERROR] Unknown/unsupported storage engine: InnoDB
110603 7:34:15 [ERROR] Aborting
10603 7:34:15 [Note] /usr/sbin/mysqld: Shutdown complete
I moved the ib_logfile0 and ib_logfile01 to bak and start Mysql again. Now this time, it is working fine
[root@xxx mysql]# mv ib_logfile0 ib_logfile0-bak
[root@xxx mysql]# mv ib_logfile1 ib_logfile1-bak
Source: http://www.onaxer.com/tag/error-plugin-innodb-init-function-returned-error/
来源：http: //www.onaxer.com/tag/error-plugin-innodb-init-function-returned-error/
回答by berto
Like others have mentioned, the problem appears to be your system running low on RAM and MySQL is blowing up due to that. Below is how to go about narrowing down where your system's memory is being used and how to recover from the database going down.
就像其他人提到的那样，问题似乎是您的系统内存不足，而 MySQL 正在因此而爆炸。下面是如何缩小系统内存的使用范围以及如何从数据库故障中恢复。
Take a look at collectdand its plugins. Some of the applicable ones may be the processes pluginand the memory plugin. With those you can see your systems' memory usage and what processes are taking up most of it.
看看collectd及其插件。一些适用的可能是进程插件和内存插件。通过这些，您可以查看系统的内存使用情况以及哪些进程占用了大部分内存。
Depending on how you are running Django, you can configure the worker processes to only process a certain number of requests and then terminate. That way if there is some sort of memory leak in your application it will not persist past that number of requests. For example, if you use Gunicorn, you can use the --max-requestsoption. Setting it to 500 will drop the worker after it has processed 500 requests.
根据您运行 Django 的方式，您可以将工作进程配置为仅处理一定数量的请求，然后终止。这样，如果您的应用程序中存在某种内存泄漏，它就不会持续超过该数量的请求。例如，如果您使用Gunicorn，则可以使用--max-requests选项。将其设置为 500 将在处理 500 个请求后丢弃工作人员。
The above combined with stats collection will show you some interesting memory usage trends.
以上结合统计信息收集将向您展示一些有趣的内存使用趋势。
As for the database going down, you can setup process supervision so that if MySQL does die, it will be relaunched automatically. MySQL in latest version of Ubuntu uses Upstart to do just that. If the process dies, Upstart will bring it back up immediately. If you're using another distro that doesn't have this built-in, take a look at Supervisor. While this doesn't fix the problem it will at least mitigate its effects. This should not be seen as the fix but rather a way to keep your application running in case something does go wrong.
至于数据库宕机，你可以设置进程监督，这样如果MySQL确实死了，它会自动重新启动。最新版本的 Ubuntu 中的 MySQL 使用 Upstart 来做到这一点。如果进程终止，Upstart 会立即将其恢复。如果您使用的是另一个没有内置此功能的发行版，请查看Supervisor。虽然这不能解决问题，但至少会减轻其影响。这不应被视为修复，而是一种在出现问题时保持应用程序运行的方法。
回答by Hammad
Once I got stuck in similar issues, I was really frustrated that my users see this ugly message that Error Establishing DB Connection. Instead of resolving the exact issues I found this repoto work like a charm for me (temporarily). After that I got debugged it by my friend and he just fine tuned my server with some configuration changes. But I've still added this script to my crontab every 10 minutes and then check if the server is crashed (which for my case crashed eventually whenever I run VNCServer on my server) and then restart it
一旦我陷入类似的问题，我真的很沮丧，因为我的用户看到了错误建立数据库连接的丑陋消息。我发现这个 repo对我来说就像一个魅力（暂时），而不是解决确切的问题。之后，我的朋友对其进行了调试，他只是通过一些配置更改对我的服务器进行了微调。但是我仍然每 10 分钟将此脚本添加到我的 crontab 中，然后检查服务器是否崩溃（对于我的情况，每当我在我的服务器上运行 VNCServer 时最终崩溃），然后重新启动它
回答by Vivek
Increasing the available RAM by adding new Swap space might also help. Steps are here
通过添加新的交换空间来增加可用 RAM 也可能有所帮助。步骤在这里
Make sure that you create /swapfile of the size smaller than the available space shown by
确保您创建的 /swapfile 的大小小于显示的可用空间
df -h
For example for me output of df- h was:
例如对我来说 df-h 的输出是：
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 7.8G 1.2G 6.3G 16% /
none 4.0K 0 4.0K 0% /sys/fs/cgroup
udev 492M 12K 492M 1% /dev
tmpfs 100M 336K 99M 1% /run
So I created using 2 G
所以我使用 2 G 创建
sudo fallocate -l 2G /swapfile
And then just start the service
然后启动服务
sudo /etc/init.d/mysql restart
Hope this helps. All the best.
希望这可以帮助。祝一切顺利。
回答by Vishal Patel
I found answer adds on this discussion and worked for me: https://www.digitalocean.com/community/questions/mysql-server-keeps-stopping-unexpectedly?answer=26016
我发现答案增加了这个讨论并为我工作：https: //www.digitalocean.com/community/questions/mysql-server-keeps-stopping-unexpectedly?answer=26016
you have to do both innodb_buffer_pool_sizeto something reasonable like 32Mon my.confin /etc/mysql/my.cnf, and you also may need to modify /etc/apache2/mods-enabled/mpm_prefork.confto reduce the number of connections started by apache;
您必须同时对in 上的32M等innodb_buffer_pool_size合理的内容进行操作，并且您可能还需要进行修改以减少 apache 启动的连接数；my.conf/etc/mysql/my.cnf/etc/apache2/mods-enabled/mpm_prefork.conf
<IfModule mpm_prefork_module>
StartServers 3
MinSpareServers 3
MaxSpareServers 5
MaxRequestWorkers 25
MaxConnectionsPerChild 0
</IfModule>
