mysqld 服务每天在 ec2 服务器上停止一次 [英] mysqld service stops once a day on ec2 server
问题描述
环境详情:
服务器:Amazon ec2 Linux网络服务器:ApacheWeb 框架:带有 mod_wsgi 的 Django以下是我在 mysql_err.log 文件中找到的.
InnoDB 内存堆被禁用120823 3:21:40 InnoDB:互斥体和 rw_locks 使用 GCC 原子内置函数120823 3:21:40 InnoDB:压缩表使用 zlib 1.2.3120823 3:21:40 InnoDB:使用 Linux 原生 AIO120823 3:21:41 InnoDB:正在初始化缓冲池，大小 = 128.0MInnoDB:mmap(137363456 字节)失败；错误 12120823 3:21:41 InnoDB:缓冲池初始化完成120823 3:21:41 InnoDB:致命错误:无法为缓冲池分配内存120823 3:21:41 [错误] 插件InnoDB"初始化函数返回错误.120823 3:21:41 [错误] 插件InnoDB"注册为存储引擎失败.120823 3:21:41 [错误] 未知/不支持的存储引擎:InnoDB120823 3:21:41 [错误] 中止看起来系统内存不足以为缓冲池分配内存.当我使用 Amazon ec2 micro instance 时发生了同样的错误，所以我转向了 small instance.它可以正常工作几天，但现在它每天再次中断.有没有永久性的解决办法?我可以移动到中等实例，但问题是会不会被修复?我应该减少innodb_buffer_pool_size，首选大小是多少?
cat/proc/meminfo 的结果如下(可能会有帮助):
MemTotal:1697824 kBMemFree:125744 KB缓冲区:109704 kB缓存:481408 kB交换缓存:0 kB活跃:1212396 kB非活动:266840 KB活动(匿名):888192 kB非活动(匿名):76 kB活动(文件):324204 kB非活动(文件):266764 kB不可避免的:0 kB锁定:0 kB交换总:0 kB无交换:0 kB脏:4 kB写回:0 kBAnonPages:888144 kB映射:15604 kBshmem:144 KB平板:63752 KBSReclaimable:53680 kB太阳回收:10072 kB内核堆栈:800 kB页表:16436 kBNFS_不稳定:0 kB弹跳:0 kB写回时间:0 kB提交限制:848912 KBCommitted_AS:1417140 KBVmalloc 总计:34359738367 kB使用的 Vmalloc:10988 KBVmallocChunk:34359725168 KBDirectMap4k:1748992 KBDirectMap2M:0 KB操作系统版本(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>
我检查了 ps aux 命令，发现服务器只剩下 15MB 的内存，这些是当时运行的 httpd 进程:
free -m
缓存的已用空闲共享缓冲区总数内存:1657 1628 29 0 3 19-/+ 缓冲区/缓存:1605 51掉期:895 875 20ps aux
apache 21123 0.1 1.2 394652 20464 ?S 19:35 0:06/usr/sbin/httpd阿帕奇 21146 0.1 1.2 394280 20796 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21152 0.1 1.2 394284 21560 ?S 19:38 0:05/usr/sbin/httpd阿帕奇 21155 0.2 1.4 396244 24528 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21156 0.1 1.1 392552 20344 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21157 0.1 1.1 394284 18884?S 19:38 0:05/usr/sbin/httpd阿帕奇 21159 0.1 1.4 396200 25040 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21161 0.1 1.2 394856 21724 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21162 0.1 1.3 394864 22400 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21163 0.1 1.3 394860 22204 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21164 0.1 1.1 392560 19204 ?S 19:38 0:06/usr/sbin/httpd阿帕奇 21165 0.1 1.3 394832 22280?S 19:38 0:06/usr/sbin/httpd阿帕奇 21166 0.1 1.3 395276 22932 ?S 19:38 0:06/usr/sbin/httpd阿帕奇21172 0.2 1.4 396320 24820?S 19:38 0:06/usr/sbin/httpd阿帕奇 21174 0.2 1.7 400672 29452 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21178 0.1 1.4 400540 25304 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21179 0.2 1.6 400580 27856 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21184 0.1 1.7 400628 29320 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21185 0.1 1.6 397944 27292 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21186 0.1 1.5 397960 25648 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21187 0.1 1.7 400576 29120 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21191 0.1 1.4 400576 24400?S 19:39 0:06/usr/sbin/httpd阿帕奇 21193 0.1 1.4 400536 24940 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21194 0.1 1.5 400572 26096 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21203 0.1 1.6 400580 28808 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21206 0.1 1.7 400584 29732 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21207 0.1 1.6 400576 27940 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21224 0.1 1.2 400624 20768 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21225 0.1 1.6 400576 28468 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21226 0.1 1.6 400576 28048 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21228 0.1 1.4 400572 23880 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21237 0.1 1.5 400628 26124 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21265 0.1 1.6 400536 28592 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21276 0.1 1.2 400544 21456 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21277 0.1 1.3 400624 22676 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21278 0.1 1.6 400536 27360 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21282 0.1 1.4 400612 24996 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21292 0.1 1.4 400532 24780 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21302 0.2 1.2 400540 21332 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21303 0.1 1.3 400628 22228 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21305 0.2 1.2 400536 21116 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21306 0.1 1.3 400572 22380 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21307 0.1 1.1 397956 20056 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21308 0.1 1.2 400624 21520 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21319 0.1 1.1 400540 19468 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21320 0.1 1.3 400628 22712 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21335 0.1 1.0 400540 17236 ?S 19:39 0:05/usr/sbin/httpd阿帕奇 21336 0.1 1.3 400628 22188 ?S 19:39 0:06/usr/sbin/httpd阿帕奇 21352 0.1 1.1 394276 18972 ?S 19:40 0:04/usr/sbin/httpd阿帕奇 21356 0.1 1.1 394280 19028 ?S 19:40 0:05/usr/sbin/httpd阿帕奇 21358 0.1 1.1 394280 19004 ?S 19:40 0:05/usr/sbin/httpd阿帕奇 21361 0.2 0.7 400452 12632 ?S 19:40 0:06/usr/sbin/httpd阿帕奇 21610 0.2 1.6 400536 27660 ?S 19:46 0:06/usr/sbin/httpd阿帕奇 21643 0.2 1.3 400156 23272 ?S 19:55 0:04/usr/sbin/httpd阿帕奇 21647 0.2 1.0 400544 17556 ?S 19:57 0:05/usr/sbin/httpd阿帕奇 21654 0.2 1.5 400188 26884 ?S 19:58 0:05/usr/sbin/httpd阿帕奇 21719 0.3 1.9 400192 32264 ?S 20:14 0:03/usr/sbin/httpd阿帕奇 21725 0.2 2.0 400044 35340 ?S 20:15 0:03/usr/sbin/httpd阿帕奇 21738 0.0 0.8 257648 13792 ?S 20:26 0:00/usr/sbin/httpd有没有人知道为什么有这么多httpd进程??
使用 50% 的可用 RAM 进行测试:
您可以将 innodb_buffer_pool_size 减小到非常低以查看是否有帮助:
#/etc/my.cnfinnodb_buffer_pool_size = 1M经验法则是将 innodb_buffer_pool_size 设置为可用 RAM 的 50%，以进行低内存测试.这意味着您启动服务器和所有除了 MySQL InnoDB.看看你有多少内存.然后将其中的 50% 用于 InnoDB.
一次尝试多个低内存设置:
更有可能的罪魁祸首是该服务器上的其他任何东西，例如网络服务器.
阿帕奇?
您在使用 Apache 和/或其他网络服务器吗?如果是这样，请尝试减少其 RAM 使用量.例如，在 Apache conf 中，请考虑像这样的低 RAM 设置:
StartServers 1最小备用服务器 1最大备用服务器 5最大客户 5并像这样限制请求:
MaxRequestsPerChild 300然后重启Apache.
mod_wsgi:
如果您使用带有 mod_python 的 Apache，请切换到带有 mod_wsgi 的 Apache.
皮姆勒:
如果它仍然发生，可能你的 Django 正在稳步增长.尝试使用 Pympler 进行 Django 内存分析:
特区:
您报告的每天一次失败，然后每周一次失败，可能指向每天或每周运行的某种 cron 作业.例如，也许有一个批处理占用大量内存，或者数据库转储等.
要跟踪 RAM 使用情况并在 MySQL 死前一小时内查找 RAM 峰值，请查看 SAR，这是一个很棒的工具:http://www.thegeekstuff.com/2011/03/sar-examples/
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.
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?
The result of cat /proc/meminfo is following (may be it will help):
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
I checked the ps aux command whet the server has like only 15MB of memory left and these are the httpd process running at that time:
The result of 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
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??
Use 50% of available RAM to test:
You can decrease the innodb_buffer_pool_size very low to see if it helps:
#/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 except MySQL InnoDB. See how much RAM you have. Then use 50% of that for 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:
StartServers 1
MinSpareServers 1
MaxSpareServers 5
MaxClients 5
And cap the requests like this:
MaxRequestsPerChild 300
Then restart Apache.
mod_wsgi:
If you're using Apache with mod_python, switch to Apache with mod_wsgi.
Pympler:
If it's still happening, possibly your Django is steadily growing. Try Django memory profiling with Pympler:
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.
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/
这篇关于mysqld 服务每天在 ec2 服务器上停止一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
