programing

MySQL 스왑 사용 및 최종 oom_killer의 원인

sourcetip 2022. 10. 2. 23:25
반응형

MySQL 스왑 사용 및 최종 oom_killer의 원인

우리는 며칠 전에 나타난 문제로 고민하고 있다.oom_killer가 mysqld를 중지하기 시작하고 전체 메모리 및 스왑 사용량을 보고했습니다.시스템은 스왑 없이 mysql이 동작할 수 있는 충분한 메모리를 갖추고 있어야 합니다.

메모리가 innodb_buffer_pool_size보다 훨씬 더 많이 소비되는 이유를 알 수 있을까요?

  • CentOS 7.2.1511 x86_64
  • MariaDB 10.1.17 x86_64
  • 150 GB RAM 8 CPU
  • VMWare vSphere 6

my.cnf

[MYSQLD]
# Added for xtrabackup
innodb_data_file_path=ibdata1:1000M:autoextend
user=mysql
basedir=/usr/
datadir=/var/lib/mysql/data
tmpdir=/var/lib/mysql/tmp
ignore-db-dirs=lost+found
socket=/var/lib/mysql/mysql.sock
pid-file=mysqld.pid
port=3306
log-error=error.log
slow_query_log=1
long_query_time=10
log-queries-not-using-indexes=1
### INNODB OPTIONS 
innodb-buffer-pool-size=110G # Used to be 124GB which has worked for a long time
innodb-flush-log-at-trx-commit=2
innodb-file-per-table=1
innodb-data-file-path = ibdata1:1000M:autoextend
## You may want to tune the below depending on number of cores and disk sub
innodb-read-io-threads=4
innodb-write-io-threads=4
innodb-doublewrite=1
innodb-log-file-size=1024M
innodb-log-buffer-size=96M
innodb-buffer-pool-instances=8
innodb-log-files-in-group=2
innodb-thread-concurrency=0
#innodb-file-format=barracuda
innodb-flush-method = O_DIRECT
# Deprecated
#innodb-locks-unsafe-for-binlog=1
innodb-autoinc-lock-mode=2
## avoid statistics update when doing e.g show tables
innodb-stats-on-metadata=0
default-storage-engine=innodb

server-id=1
binlog_format=row
expire-logs-days=2
log-bin
log-basename=master
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max-allowed-packet = 512M
skip-name-resolve
memlock=0
sysdate-is-now=1
max-connections=512
thread-cache-size=512
query-cache-type = 0
query-cache-size = 0
table-open_cache=1024
lower-case-table-names=0
performance_schema=on
#wait_timeout=3650
#connection closed errors
wait_timeout=28800

df - h

Filesystem                        Size  Used Avail Use% Mounted on
/dev/mapper/centos-root            77G  8.4G   69G  11% /
devtmpfs                           76G     0   76G   0% /dev
tmpfs                              76G     0   76G   0% /dev/shm
tmpfs                              76G  8.6M   76G   1% /run
tmpfs                              76G     0   76G   0% /sys/fs/cgroup
/dev/mapper/mysql-lvol001         800G  478G  323G  60% /var/lib/mysql
/dev/sda1                         497M  276M  221M  56% /boot
tmpfs                              16G     0   16G   0% /run/user/0

/var/log/module

Sep 25 11:42:31 xsql1md kernel: mysqld invoked oom-killer: gfp_mask=0x42d0, order=3, oom_score_adj=0
Sep 25 11:42:31 xsql1md kernel: mysqld cpuset=/ mems_allowed=0
Sep 25 11:42:31 xsql1md kernel: CPU: 5 PID: 18376 Comm: mysqld Not tainted 3.10.0-327.36.1.el7.x86_64 #1
Sep 25 11:42:32 xsql1md kernel: Hardware name: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, BIOS 6.00 09/21/2015
Sep 25 11:42:32 xsql1md kernel: ffff8825a3352280 0000000074fbe109 ffff881f875278b0 ffffffff81636301
Sep 25 11:42:32 xsql1md kernel: ffff881f87527940 ffffffff8163129c ffff88230babe3c0 0000000000000010
Sep 25 11:42:32 xsql1md kernel: ffffffff8197e700 ffff881f87527990 0000000000000001 ffff8825a503c220
Sep 25 11:42:32 xsql1md kernel: Call Trace:
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81636301>] dump_stack+0x19/0x1b
Sep 25 11:42:32 xsql1md kernel: [<ffffffff8163129c>] dump_header+0x8e/0x214
Sep 25 11:42:32 xsql1md kernel: [<ffffffff8116d21e>] oom_kill_process+0x24e/0x3b0
Sep 25 11:42:32 xsql1md kernel: [<ffffffff8116da46>] out_of_memory+0x4b6/0x4f0
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81173c36>] __alloc_pages_nodemask+0xaa6/0xba0
Sep 25 11:42:32 xsql1md kernel: [<ffffffff811b4aca>] alloc_pages_current+0xaa/0x170
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81515060>] sk_page_frag_refill+0x70/0x160
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81577763>] tcp_sendmsg+0x263/0xc20
Sep 25 11:42:32 xsql1md kernel: [<ffffffff815a1c04>] inet_sendmsg+0x64/0xb0
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81288ba3>] ? selinux_socket_sendmsg+0x23/0x30
Sep 25 11:42:32 xsql1md kernel: [<ffffffff815112a0>] sock_sendmsg+0xb0/0xf0
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81511811>] SYSC_sendto+0x121/0x1c0
Sep 25 11:42:32 xsql1md kernel: [<ffffffff8163b3b8>] ? __schedule+0x2d8/0x900
Sep 25 11:42:32 xsql1md kernel: [<ffffffff8151229e>] SyS_sendto+0xe/0x10
Sep 25 11:42:32 xsql1md kernel: [<ffffffff81646a09>] system_call_fastpath+0x16/0x1b
Sep 25 11:42:32 xsql1md kernel: Mem-Info:
Sep 25 11:42:32 xsql1md kernel: Node 0 DMA per-cpu:
Sep 25 11:42:32 xsql1md kernel: CPU    0: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    1: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    2: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    3: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    4: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    5: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    6: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    7: hi:    0, btch:   1 usd:   0
Sep 25 11:42:32 xsql1md kernel: Node 0 DMA32 per-cpu:
Sep 25 11:42:32 xsql1md kernel: CPU    0: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    1: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    2: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    3: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    4: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    5: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    6: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    7: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: Node 0 Normal per-cpu:
Sep 25 11:42:32 xsql1md kernel: CPU    0: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    1: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    2: hi:  186, btch:  31 usd:   1
Sep 25 11:42:32 xsql1md kernel: CPU    3: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    4: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    5: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    6: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: CPU    7: hi:  186, btch:  31 usd:   0
Sep 25 11:42:32 xsql1md kernel: active_anon:37766688 inactive_anon:1096008 isolated_anon:256#012 active_file:127 inactive_file:502 isolated_file:0#012 unevictable:0 dirty:0 writeback:13 unstable:0#012 free:173856 slab_reclaimable:13999 slab_unreclaimable:11990#012 mapped:0 shmem:102 pagetables:83121 bounce:0#012 free_cma:0
Sep 25 11:42:32 xsql1md kernel: Node 0 DMA free:15860kB min:4kB low:4kB high:4kB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15992kB managed:15908kB mlocked:0kB dirty:0kB writeback:0kB mapped:0kB shmem:0kB slab_reclaimable:0kB slab_unreclaimable:16kB kernel_stack:0kB pagetables:0kB unstable:0kB bounce:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Sep 25 11:42:32 xsql1md kernel: lowmem_reserve[]: 0 2806 153956 153956
Sep 25 11:42:32 xsql1md kernel: Node 0 DMA32 free:605088kB min:1228kB low:1532kB high:1840kB active_anon:1797784kB inactive_anon:456032kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:3129280kB managed:2875692kB mlocked:0kB dirty:0kB writeback:0kB mapped:4kB shmem:0kB slab_reclaimable:1748kB slab_unreclaimable:920kB kernel_stack:192kB pagetables:9096kB unstable:0kB bounce:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Sep 25 11:42:32 xsql1md kernel: lowmem_reserve[]: 0 0 151149 151149
Sep 25 11:42:32 xsql1md kernel: Node 0 Normal free:74476kB min:66344kB low:82928kB high:99516kB active_anon:149268968kB inactive_anon:3928000kB active_file:508kB inactive_file:2008kB unevictable:0kB isolated(anon):1024kB isolated(file):0kB present:157286400kB managed:154777020kB mlocked:0kB dirty:0kB writeback:52kB mapped:0kB shmem:408kB slab_reclaimable:54248kB slab_unreclaimable:47024kB kernel_stack:6944kB pagetables:323388kB unstable:0kB bounce:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:1870 all_unreclaimable? no
Sep 25 11:42:32 xsql1md kernel: lowmem_reserve[]: 0 0 0 0
Sep 25 11:42:32 xsql1md kernel: Node 0 DMA: 1*4kB (U) 0*8kB 1*16kB (U) 1*32kB (U) 1*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (R) 3*4096kB (M) = 15860kB
Sep 25 11:42:32 xsql1md kernel: Node 0 DMA32: 110*4kB (UEM) 71*8kB (UEM) 95*16kB (UEM) 65*32kB (UEM) 399*64kB (UEM) 272*128kB (EM) 144*256kB (UM) 73*512kB (UEM) 51*1024kB (UM) 0*2048kB 101*4096kB (UMR) = 605120kB
Sep 25 11:42:32 xsql1md kernel: Node 0 Normal: 7935*4kB (UEM) 4299*8kB (UEM) 402*16kB (UEM) 68*32kB (UEM) 14*64kB (UM) 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 75636kB
Sep 25 11:42:32 xsql1md kernel: Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Sep 25 11:42:32 xsql1md kernel: 34049 total pagecache pages
Sep 25 11:42:32 xsql1md kernel: 33035 pages in swap cache
Sep 25 11:42:32 xsql1md kernel: Swap cache stats: add 3213156, delete 3180121, find 33753/44214
Sep 25 11:42:32 xsql1md kernel: Free swap  = 0kB
Sep 25 11:42:32 xsql1md kernel: Total swap = 3145724kB
Sep 25 11:42:32 xsql1md kernel: 40107918 pages RAM
Sep 25 11:42:32 xsql1md kernel: 0 pages HighMem/MovableOnly
Sep 25 11:42:32 xsql1md kernel: 690763 pages reserved
Sep 25 11:42:32 xsql1md kernel: [ pid ]   uid  tgid total_vm      rss nr_ptes swapents oom_score_adj name
Sep 25 11:42:32 xsql1md kernel: [  581]     0   581     9204      101      21       62             0 systemd-journal
Sep 25 11:42:32 xsql1md kernel: [  609]     0   609    69151        0      32      412             0 lvmetad
Sep 25 11:42:32 xsql1md kernel: [  616]     0   616    11635        1      24      647         -1000 systemd-udevd
Sep 25 11:42:32 xsql1md kernel: [  773]     0   773    12797        2      25      107         -1000 auditd
Sep 25 11:42:32 xsql1md kernel: [  795]     0   795     4830       34      13       45             0 irqbalance
Sep 25 11:42:32 xsql1md kernel: [  796]     0   796    55471        0      40      191             0 rsyslogd
Sep 25 11:42:32 xsql1md kernel: [  797]    81   797     9786        0      19      150          -900 dbus-daemon
Sep 25 11:42:32 xsql1md kernel: [  804]     0   804    62201      102      64      300             0 vmtoolsd
Sep 25 11:42:32 xsql1md kernel: [  805]     0   805    80995        0      77     4258             0 firewalld
Sep 25 11:42:32 xsql1md kernel: [  806]     0   806     7646       15      18       71             0 systemd-logind
Sep 25 11:42:32 xsql1md kernel: [  807]    70   807     8043        0      20       76             0 avahi-daemon
Sep 25 11:42:32 xsql1md kernel: [  811]    70   811     8043        0      19       63             0 avahi-daemon
Sep 25 11:42:32 xsql1md kernel: [  812]     0   812    50842       10      39      114             0 gssproxy
Sep 25 11:42:32 xsql1md kernel: [  874]     0   874   132085       53      77      485             0 NetworkManager
Sep 25 11:42:32 xsql1md kernel: [  973]     0   973    13266        0      28      149             0 wpa_supplicant
Sep 25 11:42:32 xsql1md kernel: [  975]   999   975   133079        0      57     1763             0 polkitd
Sep 25 11:42:32 xsql1md kernel: [ 1397]     0  1397    20640        0      43      214         -1000 sshd
Sep 25 11:42:32 xsql1md kernel: [ 1399]     0  1399   138291       88      88     2556             0 tuned
Sep 25 11:42:32 xsql1md kernel: [ 1420]    65  1420   110056        0      47      260             0 nslcd
Sep 25 11:42:32 xsql1md kernel: [ 1444]    29  1444    11652        0      26      213             0 rpc.statd
Sep 25 11:42:32 xsql1md kernel: [ 1455]    32  1455    16227       15      34      122             0 rpcbind
Sep 25 11:42:32 xsql1md kernel: [ 1623]     0  1623    31582       21      19      137             0 crond
Sep 25 11:42:32 xsql1md kernel: [ 1685]     0  1685    27509        1      10       32             0 agetty
Sep 25 11:42:32 xsql1md kernel: [ 2128]     0  2128    23471       19      45      262             0 master
Sep 25 11:42:32 xsql1md kernel: [ 2159]    89  2159    24039       19      47      269             0 qmgr
Sep 25 11:42:32 xsql1md kernel: [12645]     0 12645    37290       14      74      311             0 sshd
Sep 25 11:42:32 xsql1md kernel: [12649]     0 12649    28879       89      14       41             0 bash
Sep 25 11:42:32 xsql1md kernel: [18021]   899 18021 53059499 38825145   82029   770667             0 mysqld
Sep 25 11:42:32 xsql1md kernel: [18383]    89 18383    24022      273      48        0             0 pickup
Sep 25 11:42:32 xsql1md kernel: Out of memory: Kill process 18021 (mysqld) score 986 or sacrifice child
Sep 25 11:42:32 xsql1md kernel: Killed process 18021 (mysqld) total-vm:212237996kB, anon-rss:155300580kB, file-rss:0kB
Sep 25 11:42:37 xsql1md systemd: mariadb.service: main process exited, code=killed, status=9/KILL
Sep 25 11:42:37 xsql1md systemd: Unit mariadb.service entered failed state.
Sep 25 11:42:37 xsql1md systemd: mariadb.service failed.
Sep 25 11:42:42 xsql1md systemd: mariadb.service holdoff time over, scheduling restart.
Sep 25 11:42:42 xsql1md systemd: Starting MariaDB database server...
Sep 25 11:42:43 xsql1md mysqld: 2016-09-25 11:42:43 139704796522624 [Note] /usr/sbin/mysqld (mysqld 10.1.17-MariaDB) starting as process 18575 ...

free - m (지금)

              total        used        free      shared  buff/cache   available
Mem:         153973       15558      135954           3        2459      138145
Swap:          3071          55        3016

이 문제를 해결한 것은 MariaDB JDBC 드라이버 파라미터 useServerPrepStmts=false를 설정하는 것입니다.개발자가 낙타케이스를 사용하지 않았기 때문에 무시되었습니다.

OS에서 설정swappiness = 1이렇게 하면 불필요한 살인이 방지될 수 있습니다.

그 동안,SHOW VARIABLES;그리고.SHOW GLOBAL STATUS;차가 충돌할 수도 있는 바쁜 시간에 말이죠.my.cnf에 표시되어 있는 값은 150GB의 RAM에 대해 적절한 것 같습니다.아마 뭔가 미묘한 일이 일어나고 있을 것입니다.

나는 네가 왜 VM에 있는지 궁금해.VM은 보통 150GB가 아닌 작은 머신의 일부를 분할하는 데 사용됩니다(VM에는 사소한 성능 저하가 있습니다).

언급URL : https://stackoverflow.com/questions/39686402/what-is-causing-mysql-swap-usage-and-eventually-oom-killer

반응형