0%

MySQL OOM 处理

MySQL OOM 处理

测试数据库发生 OOM 问题,记录日志及解决办法。

CentOS 版本:6.7 x86_64
MySQL 版本:5.7.12
kernel 版本:2.6.32-573

MySQL OOM 处理

系统日志输出:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
Sep 21 16:12:28 localhost kernel: mysqld invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0
Sep 21 16:12:28 localhost kernel: mysqld cpuset=/ mems_allowed=0
Sep 21 16:12:28 localhost kernel: Pid: 20824, comm: mysqld Not tainted 2.6.32-573.el6.x86_64 #1
Sep 21 16:12:28 localhost kernel: Call Trace:
Sep 21 16:12:28 localhost kernel: [<ffffffff810d6dd1>] ? cpuset_print_task_mems_allowed+0x91/0xb0
Sep 21 16:12:28 localhost kernel: [<ffffffff8112a5d0>] ? dump_header+0x90/0x1b0
Sep 21 16:12:28 localhost kernel: [<ffffffff81232a7c>] ? security_real_capable_noaudit+0x3c/0x70
Sep 21 16:12:28 localhost kernel: [<ffffffff8112aa52>] ? oom_kill_process+0x82/0x2a0
Sep 21 16:12:28 localhost kernel: [<ffffffff8112a991>] ? select_bad_process+0xe1/0x120
Sep 21 16:12:28 localhost kernel: [<ffffffff8112ae90>] ? out_of_memory+0x220/0x3c0
Sep 21 16:12:28 localhost kernel: [<ffffffff8113786c>] ? __alloc_pages_nodemask+0x93c/0x950
Sep 21 16:12:28 localhost kernel: [<ffffffff8117035a>] ? alloc_pages_vma+0x9a/0x150
Sep 21 16:12:28 localhost kernel: [<ffffffff81152b1d>] ? handle_pte_fault+0x73d/0xb20
Sep 21 16:12:28 localhost kernel: [<ffffffff810537b7>] ? pte_alloc_one+0x37/0x50
Sep 21 16:12:28 localhost kernel: [<ffffffff8118be29>] ? do_huge_pmd_anonymous_page+0xb9/0x3b0
Sep 21 16:12:28 localhost kernel: [<ffffffff81153199>] ? handle_mm_fault+0x299/0x3d0
Sep 21 16:12:28 localhost kernel: [<ffffffff81066053>] ? perf_event_task_sched_out+0x33/0x70
Sep 21 16:12:28 localhost kernel: [<ffffffff8104f156>] ? __do_page_fault+0x146/0x500
Sep 21 16:12:28 localhost kernel: [<ffffffff8153817e>] ? thread_return+0x4e/0x7d0
Sep 21 16:12:28 localhost kernel: [<ffffffff8153e78e>] ? do_page_fault+0x3e/0xa0
Sep 21 16:12:28 localhost kernel: [<ffffffff8153bb35>] ? page_fault+0x25/0x30
Sep 21 16:12:28 localhost kernel: Mem-Info:
Sep 21 16:12:28 localhost kernel: Node 0 DMA per-cpu:
Sep 21 16:12:28 localhost kernel: CPU 0: hi: 0, btch: 1 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 1: hi: 0, btch: 1 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 2: hi: 0, btch: 1 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 3: hi: 0, btch: 1 usd: 0
Sep 21 16:12:28 localhost kernel: Node 0 DMA32 per-cpu:
Sep 21 16:12:28 localhost kernel: CPU 0: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 1: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 2: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 3: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: Node 0 Normal per-cpu:
Sep 21 16:12:28 localhost kernel: CPU 0: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 1: hi: 186, btch: 31 usd: 30
Sep 21 16:12:28 localhost kernel: CPU 2: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: CPU 3: hi: 186, btch: 31 usd: 0
Sep 21 16:12:28 localhost kernel: active_anon:1555339 inactive_anon:332686 isolated_anon:0
Sep 21 16:12:28 localhost kernel: active_file:21589 inactive_file:21442 isolated_file:0
Sep 21 16:12:28 localhost kernel: unevictable:0 dirty:39195 writeback:0 unstable:0
Sep 21 16:12:28 localhost kernel: free:25816 slab_reclaimable:8890 slab_unreclaimable:10707
Sep 21 16:12:28 localhost kernel: mapped:3508 shmem:37 pagetables:11523 bounce:0
Sep 21 16:12:28 localhost kernel: Node 0 DMA free:15728kB min:124kB low:152kB high:184kB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15336kB mlocked:0kB dirty:0kB writeback:0kB mapped:0kB shmem:0kB slab_reclaimable:0kB slab_unreclaimable:0kB kernel_stack:0kB pagetables:0kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Sep 21 16:12:28 localhost kernel: lowmem_reserve[]: 0 2970 8020 8020
Sep 21 16:12:28 localhost kernel: Node 0 DMA32 free:45136kB min:24980kB low:31224kB high:37468kB active_anon:2112200kB inactive_anon:584284kB active_file:10564kB inactive_file:10124kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:3041728kB mlocked:0kB dirty:19804kB writeback:0kB mapped:68kB shmem:4kB slab_reclaimable:5484kB slab_unreclaimable:1668kB kernel_stack:176kB pagetables:1800kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:10688 all_unreclaimable? no
Sep 21 16:12:28 localhost kernel: lowmem_reserve[]: 0 0 5050 5050
Sep 21 16:12:28 localhost kernel: Node 0 Normal free:42400kB min:42472kB low:53088kB high:63708kB active_anon:4109156kB inactive_anon:746460kB active_file:75792kB inactive_file:75644kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:5171200kB mlocked:0kB dirty:136976kB writeback:0kB mapped:13964kB shmem:144kB slab_reclaimable:30076kB slab_unreclaimable:41160kB kernel_stack:6240kB pagetables:44292kB unstable:0kB bounce:0kB writeback_tmp:0kB pages_scanned:37952 all_unreclaimable? no
Sep 21 16:12:28 localhost kernel: lowmem_reserve[]: 0 0 0 0
Sep 21 16:12:28 localhost kernel: Node 0 DMA: 2*4kB 1*8kB 2*16kB 2*32kB 2*64kB 1*128kB 0*256kB 0*512kB 1*1024kB 1*2048kB 3*4096kB = 15728kB
Sep 21 16:12:28 localhost kernel: Node 0 DMA32: 599*4kB 470*8kB 304*16kB 140*32kB 60*64kB 40*128kB 51*256kB 13*512kB 1*1024kB 0*2048kB 0*4096kB = 45196kB
Sep 21 16:12:28 localhost kernel: Node 0 Normal: 1635*4kB 1018*8kB 574*16kB 304*32kB 93*64kB 20*128kB 2*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 42620kB
Sep 21 16:12:28 localhost kernel: 59465 total pagecache pages
Sep 21 16:12:28 localhost kernel: 16337 pages in swap cache
Sep 21 16:12:28 localhost kernel: Swap cache stats: add 8300762, delete 8284425, find 1851709760/1852280532
Sep 21 16:12:28 localhost kernel: Free swap = 9912132kB
Sep 21 16:12:28 localhost kernel: Total swap = 10239996kB
Sep 21 16:12:28 localhost kernel: 2097151 pages RAM
Sep 21 16:12:28 localhost kernel: 89559 pages reserved
Sep 21 16:12:28 localhost kernel: 84494 pages shared
Sep 21 16:12:28 localhost kernel: 1927295 pages non-shared
Sep 21 16:12:28 localhost kernel: [ pid ] uid tgid total_vm rss cpu oom_adj oom_score_adj name
Sep 21 16:12:28 localhost kernel: [ 636] 0 636 2663 67 0 -17 -1000 udevd
Sep 21 16:12:28 localhost kernel: [ 1477] 0 1477 23289 81 1 -17 -1000 auditd
Sep 21 16:12:28 localhost kernel: [ 1511] 0 1511 62991 1202 2 0 0 rsyslogd
Sep 21 16:12:28 localhost kernel: [ 1602] 0 1602 4561 77 1 0 0 irqbalance
Sep 21 16:12:28 localhost kernel: [ 1620] 32 1620 4744 20 1 0 0 rpcbind
Sep 21 16:12:28 localhost kernel: [ 1642] 29 1642 5837 2 0 0 0 rpc.statd
Sep 21 16:12:28 localhost kernel: [ 1657] 0 1657 1197 90 1 0 0 mdadm
Sep 21 16:12:28 localhost kernel: [ 1684] 81 1684 5391 96 2 0 0 dbus-daemon
Sep 21 16:12:28 localhost kernel: [ 1705] 0 1705 47232 2 0 0 0 cupsd
Sep 21 16:12:28 localhost kernel: [ 1737] 0 1737 1020 1 0 0 0 acpid
Sep 21 16:12:28 localhost kernel: [ 1749] 68 1749 9494 175 0 0 0 hald
Sep 21 16:12:28 localhost kernel: [ 1750] 0 1750 5100 2 2 0 0 hald-runner
Sep 21 16:12:28 localhost kernel: [ 1782] 0 1782 5630 2 0 0 0 hald-addon-inpu
Sep 21 16:12:28 localhost kernel: [ 1805] 68 1805 4502 2 1 0 0 hald-addon-acpi
Sep 21 16:12:28 localhost kernel: [ 1824] 0 1824 96534 103 0 0 0 automount
Sep 21 16:12:28 localhost kernel: [ 1981] 0 1981 1566 1 1 0 0 mcelog
Sep 21 16:12:28 localhost kernel: [ 2089] 0 2089 20218 47 3 0 0 master
Sep 21 16:12:28 localhost kernel: [ 2096] 89 2096 20280 133 2 0 0 qmgr
Sep 21 16:12:28 localhost kernel: [ 2118] 0 2118 45214 2 1 0 0 abrtd
Sep 21 16:12:28 localhost kernel: [ 2156] 0 2156 5276 9 1 0 0 atd
Sep 21 16:12:28 localhost kernel: [ 2172] 0 2172 16082 46 2 0 0 certmonger
Sep 21 16:12:28 localhost kernel: [ 2195] 0 2195 1016 2 2 0 0 mingetty
Sep 21 16:12:28 localhost kernel: [ 2197] 0 2197 1016 2 0 0 0 mingetty
Sep 21 16:12:28 localhost kernel: [ 5013] 0 5013 92272 314 3 0 0 php
Sep 21 16:12:28 localhost kernel: [ 5030] 0 5030 91990 314 1 0 0 php
Sep 21 16:12:28 localhost kernel: [ 5140] 0 5140 92185 325 0 0 0 php
Sep 21 16:12:28 localhost kernel: [ 1427] 0 1427 2868 116 1 0 0 mysqld_safe
Sep 21 16:12:28 localhost kernel: [13252] 0 13252 25628 137 1 0 0 sshd
Sep 21 16:12:28 localhost kernel: [13279] 511 13279 26549 226 0 0 0 sshd
Sep 21 16:12:28 localhost kernel: [13284] 511 13284 14359 155 0 0 0 sftp-server
Sep 21 16:12:28 localhost kernel: [17029] 0 17029 25628 137 1 0 0 sshd
Sep 21 16:12:28 localhost kernel: [17042] 511 17042 25715 156 0 0 0 sshd
Sep 21 16:12:28 localhost kernel: [17044] 511 17044 14348 135 2 0 0 sftp-server
Sep 21 16:12:28 localhost kernel: [18981] 0 18981 97300 171 0 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19000] 48 19000 100856 4499 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19001] 48 19001 101126 5024 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19002] 48 19002 101128 5015 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19003] 48 19003 101184 5138 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19004] 48 19004 101262 5173 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19005] 48 19005 101113 5181 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19006] 48 19006 103955 7278 0 0 0 httpd
Sep 21 16:12:28 localhost kernel: [19007] 48 19007 103892 5011 0 0 0 httpd
Sep 21 16:12:28 localhost kernel: [ 7033] 48 7033 101150 4990 0 0 0 httpd
Sep 21 16:12:28 localhost kernel: [ 9907] 48 9907 103906 5150 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [ 9943] 48 9943 100195 4298 0 0 0 httpd
Sep 21 16:12:28 localhost kernel: [ 9944] 48 9944 103931 4977 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [26057] 48 26057 101127 4790 1 0 0 httpd
Sep 21 16:12:28 localhost kernel: [26921] 48 26921 101067 4969 3 0 0 httpd
Sep 21 16:12:28 localhost kernel: [31980] 0 31980 25629 10 1 0 0 sshd
Sep 21 16:12:28 localhost kernel: [31982] 500 31982 25629 55 3 0 0 sshd
Sep 21 16:12:28 localhost kernel: [31983] 500 31983 27084 2 1 0 0 bash
Sep 21 16:12:28 localhost kernel: [32369] 500 32369 41514 2 3 0 0 su
Sep 21 16:12:28 localhost kernel: [ 355] 0 355 27104 146 2 0 0 bash
Sep 21 16:12:28 localhost kernel: [27397] 99 27397 3635 180 1 0 0 nmon
Sep 21 16:12:28 localhost kernel: [15221] 0 15221 25628 138 3 0 0 sshd
Sep 21 16:12:28 localhost kernel: [15496] 511 15496 25699 145 2 0 0 sshd
Sep 21 16:12:28 localhost kernel: [15497] 511 15497 14349 153 1 0 0 sftp-server
Sep 21 16:12:28 localhost kernel: [ 2380] 0 2380 25628 138 2 0 0 sshd
Sep 21 16:12:28 localhost kernel: [ 2627] 511 2627 25663 121 2 0 0 sshd
Sep 21 16:12:28 localhost kernel: [ 2629] 511 2629 14312 116 2 0 0 sftp-server
Sep 21 16:12:28 localhost kernel: [17337] 0 17337 29215 122 0 0 0 crond
Sep 21 16:12:28 localhost kernel: [17464] 0 17464 16558 44 0 -17 -1000 sshd
Sep 21 16:12:28 localhost kernel: [14863] 497 14863 1043341 101433 3 0 0 java
Sep 21 16:12:28 localhost kernel: [26148] 0 26148 1184739 199553 2 0 0 java
Sep 21 16:12:28 localhost kernel: [ 3941] 0 3941 2662 92 2 -17 -1000 udevd
Sep 21 16:12:28 localhost kernel: [ 3942] 0 3942 2662 92 1 -17 -1000 udevd
Sep 21 16:12:28 localhost kernel: [ 408] 498 408 2251205 1478808 0 0 0 mysqld
Sep 21 16:12:28 localhost kernel: [ 978] 0 978 25628 697 0 0 0 sshd
Sep 21 16:12:28 localhost kernel: [ 1029] 503 1029 25628 347 3 0 0 sshd
Sep 21 16:12:28 localhost kernel: [ 1038] 503 1038 27084 252 2 0 0 bash
Sep 21 16:12:28 localhost kernel: [ 1147] 503 1147 41511 362 1 0 0 su
Sep 21 16:12:28 localhost kernel: [ 1517] 0 1517 27084 266 1 0 0 bash
Sep 21 16:12:28 localhost kernel: [ 4617] 0 4617 35003 325 0 0 0 crond
Sep 21 16:12:28 localhost kernel: [ 4618] 0 4618 35003 325 1 0 0 crond
Sep 21 16:12:28 localhost kernel: [ 4619] 0 4619 35003 325 2 0 0 crond
Sep 21 16:12:28 localhost kernel: [ 4620] 0 4620 35003 325 0 0 0 crond
Sep 21 16:12:28 localhost kernel: [ 4621] 0 4621 35003 325 2 0 0 crond
Sep 21 16:12:28 localhost kernel: [ 4665] 0 4665 95366 7116 3 0 0 php
Sep 21 16:12:28 localhost kernel: [ 4666] 0 4666 20233 649 2 0 0 sendmail
Sep 21 16:12:28 localhost kernel: [ 4675] 0 4675 20224 650 2 0 0 postdrop
Sep 21 16:12:28 localhost kernel: [ 4689] 0 4689 95496 7123 3 0 0 php
Sep 21 16:12:28 localhost kernel: [ 4691] 0 4691 95431 7174 3 0 0 php
Sep 21 16:12:28 localhost kernel: [ 4692] 0 4692 95455 7122 2 0 0 php
Sep 21 16:12:28 localhost kernel: [ 4695] 0 4695 20233 651 0 0 0 sendmail
Sep 21 16:12:28 localhost kernel: [ 4696] 0 4696 20233 650 3 0 0 sendmail
Sep 21 16:12:28 localhost kernel: [ 4697] 0 4697 20233 649 2 0 0 sendmail
Sep 21 16:12:28 localhost kernel: [ 4699] 0 4699 20224 651 2 0 0 postdrop
Sep 21 16:12:28 localhost kernel: [ 4700] 0 4700 20224 651 3 0 0 postdrop
Sep 21 16:12:28 localhost kernel: [ 4701] 0 4701 20224 652 0 0 0 postdrop
Sep 21 16:12:28 localhost kernel: [ 4707] 0 4707 95507 7232 3 0 0 php
Sep 21 16:12:28 localhost kernel: [ 4709] 0 4709 20233 650 0 0 0 sendmail
Sep 21 16:12:28 localhost kernel: [ 4710] 0 4710 20224 651 0 0 0 postdrop
Sep 21 16:12:28 localhost kernel: [22878] 89 22878 20238 666 0 0 0 pickup
Sep 21 16:12:28 localhost kernel: [18462] 89 18462 20240 661 0 0 0 trivial-rewrite
Sep 21 16:12:28 localhost kernel: [18573] 89 18573 20247 673 0 0 0 bounce
Sep 21 16:12:28 localhost kernel: [18574] 0 18574 20312 793 0 0 0 local
Sep 21 16:12:28 localhost kernel: [20055] 0 20055 20312 793 0 0 0 local
Sep 21 16:12:28 localhost kernel: [20056] 89 20056 20274 714 1 0 0 cleanup
Sep 21 16:12:28 localhost kernel: [20058] 89 20058 20274 716 3 0 0 cleanup
Sep 21 16:12:28 localhost kernel: [20063] 89 20063 20247 671 3 0 0 bounce
Sep 21 16:12:28 localhost kernel: [20064] 89 20064 20274 714 0 0 0 cleanup
Sep 21 16:12:28 localhost kernel: [25666] 0 25666 20312 793 1 0 0 local
Sep 21 16:12:28 localhost kernel: [27049] 89 27049 20247 672 1 0 0 bounce
Sep 21 16:12:28 localhost kernel: [31585] 0 31585 92315 3716 0 0 0 php
Sep 21 16:12:28 localhost kernel: [31586] 0 31586 58126 993 3 0 0 php
Sep 21 16:12:28 localhost kernel: [31587] 0 31587 58126 993 0 0 0 php
Sep 21 16:12:28 localhost kernel: Out of memory: Kill process 408 (mysqld) score 324 or sacrifice child
Sep 21 16:12:28 localhost kernel: Killed process 408, UID 498, (mysqld) total-vm:9004820kB, anon-rss:5910380kB, file-rss:4852kB

检查发现内核(>= 2.6.32-303)在配置 vm.swappiness=0 后会出现 OOM 现象,修改 /etc/sysctl.conf 文件即可。

1
2
vm.swappiness=1
sysctl -p