반응형
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
반응형
'programing' 카테고리의 다른 글
| jQuery를 사용하여 "disabled" 속성을 제거하려면 어떻게 해야 합니까? (0) | 2022.10.02 |
|---|---|
| JavaScript에서 파일 입력 요소에 대해 "클릭" 이벤트를 프로그래밍 방식으로 실행할 수 있습니까? (0) | 2022.10.02 |
| JAXB란 무엇이며 왜 사용해야 합니까? (0) | 2022.10.02 |
| MySQL BETWEEN 쿼리가 인덱스를 사용하지 않음 (0) | 2022.10.02 |
| Java에서 자동 박스와 언박스를 사용하는 이유는 무엇입니까? (0) | 2022.10.02 |