Mysql/Apache偶尔停止工作

Question

我的问题是:我的网络服务器偶尔停止工作。Apache仍然响应请求，但无法连接到mysql服务器。根据我阅读syslog的方式，这是因为内存不足。apache和mysql都运行在同一台服务器上。

我买了更多的RAM，但问题依然存在。所以我开始认为这是另一回事。

从有趣的日志行转储，就在问题发生之前

SYSlog：

Jul 30 19:28:54 ubuntu kernel: [1972483.311125] mysqld invoked oom-killer: gfp_mask=0x201da, order=0, oom_adj=0, oom_score_adj=0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311130] mysqld cpuset=/ mems_allowed=0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311133] Pid: 14076, comm: mysqld Not tainted 3.2.0-31-generic #50-Ubuntu 
Jul 30 19:28:54 ubuntu kernel: [1972483.311135] Call Trace: 
Jul 30 19:28:54 ubuntu kernel: [1972483.311144]  [<ffffffff810bfb3d>] ? cpuset_print_task_mems_allowed+0x9d/0xb0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311148]  [<ffffffff8111a991>] dump_header+0x91/0xe0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311149]  [<ffffffff8111ad15>] oom_kill_process+0x85/0xb0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311151]  [<ffffffff8111b0ba>] out_of_memory+0xfa/0x220 
Jul 30 19:28:54 ubuntu kernel: [1972483.311154]  [<ffffffff81120aac>] __alloc_pages_nodemask+0x8dc/0x8f0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311158]  [<ffffffff81157b36>] alloc_pages_current+0xb6/0x120 
Jul 30 19:28:54 ubuntu kernel: [1972483.311162]  [<ffffffff81117617>] __page_cache_alloc+0xb7/0xd0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311164]  [<ffffffff81119874>] filemap_fault+0x234/0x3e0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311167]  [<ffffffff8116e10b>] ? mem_cgroup_update_page_stat+0x2b/0x110 
Jul 30 19:28:54 ubuntu kernel: [1972483.311170]  [<ffffffff81139ec2>] __do_fault+0x72/0x550 
Jul 30 19:28:54 ubuntu kernel: [1972483.311174]  [<ffffffff816585ef>] ? schedule+0x3f/0x60 
Jul 30 19:28:54 ubuntu kernel: [1972483.311176]  [<ffffffff8113d55a>] handle_pte_fault+0xfa/0x200 
Jul 30 19:28:54 ubuntu kernel: [1972483.311178]  [<ffffffff8113da18>] handle_mm_fault+0x1f8/0x350 
Jul 30 19:28:54 ubuntu kernel: [1972483.311181]  [<ffffffff8165e020>] do_page_fault+0x150/0x520 
Jul 30 19:28:54 ubuntu kernel: [1972483.311185]  [<ffffffff81055efd>] ? set_next_entity+0xad/0xd0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311207]  [<ffffffff810a0538>] ? do_futex+0xd8/0x1b0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311209]  [<ffffffff810a071a>] ? sys_futex+0x10a/0x1a0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311211]  [<ffffffff8165ac75>] page_fault+0x25/0x30 
Jul 30 19:28:54 ubuntu kernel: [1972483.311213] Mem-Info: 
Jul 30 19:28:54 ubuntu kernel: [1972483.311214] Node 0 DMA per-cpu: 
Jul 30 19:28:54 ubuntu kernel: [1972483.311216] CPU    0: hi:    0, btch:   1 usd:   0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311217] Node 0 DMA32 per-cpu: 
Jul 30 19:28:54 ubuntu kernel: [1972483.311218] CPU    0: hi:  186, btch:  31 usd:   0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311221] active_anon:326798 inactive_anon:109587 isolated_anon:0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311221]  active_file:235 inactive_file:327 isolated_file:91 
Jul 30 19:28:54 ubuntu kernel: [1972483.311222]  unevictable:0 dirty:0 writeback:94 unstable:0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311223]  free:13266 slab_reclaimable:2305 slab_unreclaimable:13295 
Jul 30 19:28:54 ubuntu kernel: [1972483.311224]  mapped:268 shmem:11 pagetables:36727 bounce:0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311225] Node 0 DMA free:8344kB min:340kB low:424kB high:508kB active_anon:3616kB inactive_anon:3672kB active_file:0kB inactive_file:4kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15648kB mlocked:0kB dirty:0kB writeback:88kB mapped:4kB shmem:0kB slab_reclaimable:24kB slab_unreclaimable:128kB kernel_stack:0kB pagetables:72kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:197 all_unreclaimable? yes 
Jul 30 19:28:54 ubuntu kernel: [1972483.311232] lowmem_reserve[]: 0 2000 2000 2000 
Jul 30 19:28:54 ubuntu kernel: [1972483.311234] Node 0 DMA32 free:44720kB min:44712kB low:55888kB high:67068kB active_anon:1303576kB inactive_anon:434676kB active_file:940kB inactive_file:1304kB unevictable:0kB isolated(anon):0kB isolated(file):364kB present:2048244kB mlocked:0kB dirty:0kB writeback:288kB mapped:1068kB shmem:44kB slab_reclaimable:9196kB slab_unreclaimable:53052kB kernel_stack:4464kB pagetables:146836kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:9872 all_unreclaimable? yes 
Jul 30 19:28:54 ubuntu kernel: [1972483.311241] lowmem_reserve[]: 0 0 0 0 
Jul 30 19:28:54 ubuntu kernel: [1972483.311242] Node 0 DMA: 22*4kB 15*8kB 13*16kB 6*32kB 3*64kB 1*128kB 3*256kB 3*512kB 3*1024kB 1*2048kB 0*4096kB = 8352kB 
Jul 30 19:28:54 ubuntu kernel: [1972483.311248] Node 0 DMA32: 292*4kB 98*8kB 211*16kB 176*32kB 464*64kB 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 1*4096kB = 44752kB 
Jul 30 19:28:54 ubuntu kernel: [1972483.311253] 16506 total pagecache pages 
Jul 30 19:28:54 ubuntu kernel: [1972483.311254] 15829 pages in swap cache 
Jul 30 19:28:54 ubuntu kernel: [1972483.311255] Swap cache stats: add 5050304, delete 5034475, find 192659029/193055410 
Jul 30 19:28:54 ubuntu kernel: [1972483.311256] Free swap  = 0kB 
Jul 30 19:28:54 ubuntu kernel: [1972483.311257] Total swap = 1046524kB 
Jul 30 19:28:54 ubuntu kernel: [1972483.317835] 524269 pages RAM 
Jul 30 19:28:54 ubuntu kernel: [1972483.317837] 11336 pages reserved 
Jul 30 19:28:54 ubuntu kernel: [1972483.317838] 113246 pages shared 
Jul 30 19:28:54 ubuntu kernel: [1972483.317839] 497331 pages non-shared

发生问题后的日志条目：

Jul 30 19:28:57 ubuntu kernel: [1972483.318629] [14390]     0 14390    75449      258   0       0             0 apache2
Jul 30 19:28:57 ubuntu kernel: [1972483.318631] Out of memory: Kill process 32611 (mysqld) score 30 or sacrifice child
Jul 30 19:28:57 ubuntu kernel: [1972483.321875] Killed process 32611 (mysqld) total-vm:917688kB, anon-rss:10424kB, file-rss:68kB
Jul 30 19:29:09 ubuntu kernel: [1972497.928479] apache2 invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0
Jul 30 19:29:09 ubuntu kernel: [1972497.928485] apache2 cpuset=/ mems_allowed=0
Jul 30 19:29:09 ubuntu kernel: [1972497.928489] Pid: 14205, comm: apache2 Not tainted 3.2.0-31-generic #50-Ubuntu
Jul 30 19:29:09 ubuntu kernel: [1972497.928491] Call Trace:
Jul 30 19:29:10 ubuntu kernel: [1972497.928501]  [<ffffffff810bfb3d>] ? cpuset_print_task_mems_allowed+0x9d/0xb0
Jul 30 19:29:11 ubuntu kernel: [1972497.928505]  [<ffffffff8111a991>] dump_header+0x91/0xe0
Jul 30 19:29:12 ubuntu kernel: [1972497.928507]  [<ffffffff8111ad15>] oom_kill_process+0x85/0xb0
Jul 30 19:29:18 ubuntu kernel: [1972497.928509]  [<ffffffff8111b0ba>] out_of_memory+0xfa/0x220
Jul 30 19:29:19 ubuntu kernel: [1972497.928511]  [<ffffffff81120aac>] __alloc_pages_nodemask+0x8dc/0x8f0
Jul 30 19:29:19 ubuntu kernel: [1972497.928515]  [<ffffffff81116eb0>] ? sleep_on_page+0x20/0x20
Jul 30 19:29:19 ubuntu kernel: [1972497.928520]  [<ffffffff81159663>] alloc_pages_vma+0xb3/0x190
Jul 30 19:29:19 ubuntu kernel: [1972497.928522]  [<ffffffff81139bdc>] do_anonymous_page.isra.38+0x7c/0x2f0
Jul 30 19:29:19 ubuntu kernel: [1972497.928524]  [<ffffffff8113d641>] handle_pte_fault+0x1e1/0x200
Jul 30 19:29:19 ubuntu kernel: [1972497.928526]  [<ffffffff81142154>] ? vma_adjust+0x464/0x490
Jul 30 19:29:19 ubuntu kernel: [1972497.928528]  [<ffffffff8113da18>] handle_mm_fault+0x1f8/0x350
Jul 30 19:29:19 ubuntu kernel: [1972497.928530]  [<ffffffff811411a9>] ? find_vma+0x49/0x70
Jul 30 19:29:19 ubuntu kernel: [1972497.928534]  [<ffffffff8165e020>] do_page_fault+0x150/0x520
Jul 30 19:29:19 ubuntu kernel: [1972497.928538]  [<ffffffff81012728>] ? __switch_to+0x138/0x360
Jul 30 19:29:19 ubuntu kernel: [1972497.928541]  [<ffffffff81055efd>] ? set_next_entity+0xad/0xd0
Jul 30 19:29:19 ubuntu kernel: [1972497.928543]  [<ffffffff8105609a>] ? finish_task_switch+0x4a/0xf0
Jul 30 19:29:19 ubuntu kernel: [1972497.928547]  [<ffffffff81657f9c>] ? __schedule+0x3cc/0x6f0
Jul 30 19:29:19 ubuntu kernel: [1972497.928549]  [<ffffffff8165ac75>] page_fault+0x25/0x30
Jul 30 19:29:19 ubuntu kernel: [1972497.928551] Mem-Info:
Jul 30 19:29:19 ubuntu kernel: [1972497.928552] Node 0 DMA per-cpu:
Jul 30 19:29:19 ubuntu kernel: [1972497.928554] CPU    0: hi:    0, btch:   1 usd:   0
Jul 30 19:29:19 ubuntu kernel: [1972497.928555] Node 0 DMA32 per-cpu:
Jul 30 19:29:19 ubuntu kernel: [1972497.928556] CPU    0: hi:  186, btch:  31 usd:   0
Jul 30 19:29:19 ubuntu kernel: [1972497.928559] active_anon:324675 inactive_anon:108699 isolated_anon:2590
Jul 30 19:29:19 ubuntu kernel: [1972497.928559]  active_file:136 inactive_file:319 isolated_file:32
Jul 30 19:29:19 ubuntu kernel: [1972497.928560]  unevictable:0 dirty:0 writeback:256 unstable:0
Jul 30 19:29:19 ubuntu kernel: [1972497.928561]  free:13266 slab_reclaimable:2257 slab_unreclaimable:13383
Jul 30 19:29:19 ubuntu kernel: [1972497.928561]  mapped:145 shmem:14 pagetables:37093 bounce:0
Jul 30 19:29:19 ubuntu kernel: [1972497.928563] Node 0 DMA free:8344kB min:340kB low:424kB high:508kB active_anon:2728kB inactive_anon:2752kB active_file:8kB inactive_file:124kB unevictable:0kB isolated(anon):1104kB isolated(file):0kB present:15648kB mlocked:0kB dirty:0kB writeback:76kB mapped:0kB shmem:0kB slab_reclaimable:24kB slab_unreclaimable:132kB kernel_stack:0kB pagetables:164kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:4232 all_unreclaimable? yes
Jul 30 19:29:19 ubuntu kernel: [1972497.928569] lowmem_reserve[]: 0 2000 2000 2000
Jul 30 19:29:19 ubuntu kernel: [1972497.928571] Node 0 DMA32 free:44720kB min:44712kB low:55888kB high:67068kB active_anon:1295972kB inactive_anon:432044kB active_file:536kB inactive_file:1152kB unevictable:0kB isolated(anon):9256kB isolated(file):128kB present:2048244kB mlocked:0kB dirty:0kB writeback:948kB mapped:580kB shmem:56kB slab_reclaimable:9004kB slab_unreclaimable:53400kB kernel_stack:3168kB pagetables:148208kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:6289 all_unreclaimable? yes
Jul 30 19:29:19 ubuntu kernel: [1972497.928578] lowmem_reserve[]: 0 0 0 0
Jul 30 19:29:19 ubuntu kernel: [1972497.928580] Node 0 DMA: 4*4kB 4*8kB 13*16kB 7*32kB 5*64kB 1*128kB 3*256kB 3*512kB 3*1024kB 1*2048kB 0*4096kB = 8352kB
Jul 30 19:29:19 ubuntu kernel: [1972497.928585] Node 0 DMA32: 29*4kB 41*8kB 259*16kB 221*32kB 453*64kB 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 1*4096kB = 44748kB
Jul 30 19:29:19 ubuntu kernel: [1972497.928590] 20810 total pagecache pages
Jul 30 19:29:19 ubuntu kernel: [1972497.928592] 20308 pages in swap cache
Jul 30 19:29:19 ubuntu kernel: [1972497.928593] Swap cache stats: add 5083803, delete 5063495, find 192661978/193059753
Jul 30 19:29:19 ubuntu kernel: [1972497.928594] Free swap  = 0kB
Jul 30 19:29:19 ubuntu kernel: [1972497.928595] Total swap = 1046524kB
Jul 30 19:29:19 ubuntu kernel: [1972497.935591] 524269 pages RAM
Jul 30 19:29:19 ubuntu kernel: [1972497.935593] 11336 pages reserved
Jul 30 19:29:19 ubuntu kernel: [1972497.935594] 93848 pages shared
Jul 30 19:29:19 ubuntu kernel: [1972497.935595] 495184 pages non-shared

Mysql错误日志不提供任何其他信息。

什么可能导致这个问题，我能做些什么来解决它呢？如果我重新启动服务器，问题就会消失几个星期。

编辑

/proc/全部门办法：

Filename                Type        Size    Used    Priority
/dev/vda5                               partition   1046524 0   -1
/swapfile.img                           file        3145724 0   -2

Answer 1

这显然是mysql问题。banyek的答案中的monit解决方案是好的，但它只是一种对症治疗，并不能解决原因。

第二:更多的公羊是不够的。启用大量交换(如果我正确地看到，您有2 gig的ram和64位系统--也许4G的交换就足够了)。这很重要。不，启用交换不会使您的系统变慢，这是一个常见的误解。但是你的系统会更快一些(这不是开玩笑，如果你愿意的话，我会在评论中解释)，当然也要稳定得多。

有时，系统的交换将无法启用。签入/proc/swaps，看看您的系统中是否有一个交换分区/交换文件，它是否足够大(您的ram两次就可以了)。

第三件重要的事情:在mysql崩溃之前，可能会执行一些loooooong查询。如果也有交换的话，情况尤其如此！因为如果你有交换，在那里你的内存泄漏，你的系统不会崩溃，只是它会稍微慢一点。幸运的是，mysql非常支持查找执行异常慢的查询。这些内容可以在名为mysql-slow.log的文件中找到，或者在/var/log目录下的某个地方找到。

因此，启用交换，找出哪个查询使mysql变得如此慢，找出是什么将这个查询提供给mysql守护进程并解决了这个问题。

Answer 2

你能在这里粘贴你的my.cnf文件吗？如果你也能粘贴，那就太好了。(连同你的机器规格)。

您可以使用monit，并在杀死它时强制它重新启动mysql，但是这并不是最伟大的想法，因为它无法解决问题的根源--这可能是因为缺乏RAM。在这里，我粘贴了一个monit配置代码片段，您可以使用它来重新加载已杀死的mysql。

    check process mysql with pidfile /var/run/mysqld/mysql.pid
        group mysql
        start program = "/etc/init.d/mysql start"
        stop program = "/etc/init.d/mysql stop"
        if failed unix "/var/run/mysqld/mysqld.sock" then restart
        if 5 restarts within 5 cycles then timeout

但是，如果您能够修复您的系统可以使用的RAM的数量，那就更好了。