DreamCompute: Preventing MySQL From Crashing? "Creeping" Memory Leak


#1

Greetings,

I set up a 1GB DreamCompute instance dedicated solely to MySQL and successfully migrated all of my databases from Dreamhost VPS. Everything seems to be working great. However, I noticed that mysqld memory usage quickly grows and sort of peaks out at about 700MB (as expected), but then it slowly “creeps” up over the course of 1-2 days. Then when it reaches about 770MB, the process gets killed by OOM Killer and restarts within a few seconds. It’s not a massive downtime, but I would like it to be stable like it was on Dreamhost MySQL VPS.

I reviewed the variables from Dreamhost MySQL VPS and tried to replicate them into my DreamCompute instance. I am using MySQL version 5.7.21. Here are the variables that I changed from default in the my.cnf file, everything else is set to the defaults. The biggest change is an increase memory to the innodb_buffer_pool_size to 512M, which is a bit more than VPS.:

sql_mode = "NO_ENGINE_SUBSTITUTION"
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 2097152
innodb_log_file_size = 20971520
innodb_open_files = 1000
innodb_strict_mode = OFF
innodb_write_io_threads = 6
innodb_read_io_threads = 6
join_buffer_size = 1048576
key_buffer_size = 88080384
max_connect_errors = 10000
max_connections = 151
max_allowed_packet = 32M
myisam_recover_options = "BACKUP,FORCE"
performance_schema = 0
query_cache_limit = 5242880
query_cache_size = 52428800
read_buffer_size = 1048576
slave_net_timeout = 3600
slow_query_log = ON
sort_buffer_size = 1048576
sync_binlog = 0
thread_stack = 262144
wait_timeout = 14400

I’m kind of noobish with MySQL administration, so I’m hoping someone with more experience can provide some advice to keeping my MySQL instance a bit more stable on my 1GB instance, OOM Killer strategies and making the database faster/efficient at the same time.

Thanks
Kind regards

EDIT: I Added the actual syslog of OOM killing the process, along with specific memory details:

Feb 19 12:47:26 database kernel: [232293.914462] systemd invoked oom-killer: gfp_mask=0x24201ca, order=0, oom_score_adj=0
Feb 19 12:47:26 database kernel: [232293.914473] systemd cpuset=/ mems_allowed=0
Feb 19 12:47:26 database kernel: [232293.914501] CPU: 0 PID: 1 Comm: systemd Not tainted 4.4.0-112-generic #135-Ubuntu
Feb 19 12:47:26 database kernel: [232293.914503] Hardware name: OpenStack Foundation OpenStack Nova, BIOS Bochs 01/01/2011
Feb 19 12:47:26 database kernel: [232293.914505]  0000000000000286 156f9a38aac2d7f6 ffff88003e3f79d8 ffffffff813fc233
Feb 19 12:47:26 database kernel: [232293.914510]  ffff88003e3f7b90 ffff88003ae39c00 ffff88003e3f7a48 ffffffff8120dafe
Feb 19 12:47:26 database kernel: [232293.914512]  ffffffff81cd8367 0000000000000000 ffffffff81e6b1a0 0000000000000206
Feb 19 12:47:26 database kernel: [232293.914514] Call Trace:
Feb 19 12:47:26 database kernel: [232293.914552]  [<ffffffff813fc233>] dump_stack+0x63/0x90
Feb 19 12:47:26 database kernel: [232293.914567]  [<ffffffff8120dafe>] dump_header+0x5a/0x1c5
Feb 19 12:47:26 database kernel: [232293.914578]  [<ffffffff811946a2>] oom_kill_process+0x202/0x3c0
Feb 19 12:47:26 database kernel: [232293.914580]  [<ffffffff81194ac9>] out_of_memory+0x219/0x460
Feb 19 12:47:26 database kernel: [232293.914601]  [<ffffffff8119aad5>] __alloc_pages_slowpath.constprop.88+0x965/0xb00
Feb 19 12:47:26 database kernel: [232293.914604]  [<ffffffff8119aef6>] __alloc_pages_nodemask+0x286/0x2a0
Feb 19 12:47:26 database kernel: [232293.914613]  [<ffffffff811e483c>] alloc_pages_current+0x8c/0x110
Feb 19 12:47:26 database kernel: [232293.914616]  [<ffffffff81190c6b>] __page_cache_alloc+0xab/0xc0
Feb 19 12:47:26 database kernel: [232293.914617]  [<ffffffff8119317a>] filemap_fault+0x14a/0x3f0
Feb 19 12:47:26 database kernel: [232293.914629]  [<ffffffff812a5d56>] ext4_filemap_fault+0x36/0x50
Feb 19 12:47:26 database kernel: [232293.914632]  [<ffffffff811bfe70>] __do_fault+0x50/0xe0
Feb 19 12:47:26 database kernel: [232293.914634]  [<ffffffff811c39c2>] handle_mm_fault+0xfa2/0x1820
Feb 19 12:47:26 database kernel: [232293.914647]  [<ffffffff8106b687>] __do_page_fault+0x197/0x400
Feb 19 12:47:26 database kernel: [232293.914649]  [<ffffffff8106b957>] trace_do_page_fault+0x37/0xe0
Feb 19 12:47:26 database kernel: [232293.914655]  [<ffffffff81063f29>] do_async_page_fault+0x19/0x70
Feb 19 12:47:26 database kernel: [232293.914670]  [<ffffffff81849af8>] async_page_fault+0x28/0x30
Feb 19 12:47:26 database kernel: [232293.914673] Mem-Info:
Feb 19 12:47:26 database kernel: [232293.914706] active_anon:222308 inactive_anon:1437 isolated_anon:0
Feb 19 12:47:26 database kernel: [232293.914706]  active_file:930 inactive_file:1009 isolated_file:0
Feb 19 12:47:26 database kernel: [232293.914706]  unevictable:913 dirty:0 writeback:0 unstable:0
Feb 19 12:47:26 database kernel: [232293.914706]  slab_reclaimable:5010 slab_unreclaimable:3447
Feb 19 12:47:26 database kernel: [232293.914706]  mapped:1613 shmem:2687 pagetables:1372 bounce:0
Feb 19 12:47:26 database kernel: [232293.914706]  free:12728 free_pcp:181 free_cma:0
Feb 19 12:47:26 database kernel: [232293.914712] Node 0 DMA free:4544kB min:716kB low:892kB high:1072kB active_anon:5868kB inactive_anon:144kB active_file:1184kB inactive_file:1368kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15992kB managed:15908kB mlocked:0kB dirty:0kB writeback:0kB mapped:1036kB shmem:144kB slab_reclaimable:204kB slab_unreclaimable:332kB kernel_stack:608kB pagetables:616kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:15984 all_unreclaimable? yes
Feb 19 12:47:26 database kernel: [232293.914720] lowmem_reserve[]: 0 958 958 958 958
Feb 19 12:47:26 database kernel: [232293.914723] Node 0 DMA32 free:46368kB min:44336kB low:55420kB high:66504kB active_anon:883364kB inactive_anon:5604kB active_file:2536kB inactive_file:2668kB unevictable:3652kB isolated(anon):0kB isolated(file):0kB present:1032184kB managed:1000192kB mlocked:3652kB dirty:0kB writeback:0kB mapped:5416kB shmem:10604kB slab_reclaimable:19836kB slab_unreclaimable:13456kB kernel_stack:3152kB pagetables:4872kB unstable:0kB bounce:0kB free_pcp:724kB local_pcp:724kB free_cma:0kB writeback_tmp:0kB pages_scanned:50828 all_unreclaimable? yes
Feb 19 12:47:26 database kernel: [232293.914729] lowmem_reserve[]: 0 0 0 0 0
Feb 19 12:47:26 database kernel: [232293.914731] Node 0 DMA: 0*4kB 44*8kB (UM) 52*16kB (UM) 29*32kB (UM) 4*64kB (M) 3*128kB (UM) 5*256kB (UM) 1*512kB (U) 0*1024kB 0*2048kB 0*4096kB = 4544kB
Feb 19 12:47:26 database kernel: [232293.914743] Node 0 DMA32: 80*4kB (UEH) 374*8kB (UEH) 361*16kB (UMEH) 177*32kB (UMEH) 98*64kB (UMEH) 42*128kB (UMEH) 10*256kB (UM) 8*512kB (UMH) 3*1024kB (UH) 1*2048kB (M) 2*4096kB (M) = 46368kB
Feb 19 12:47:26 database kernel: [232293.914757] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Feb 19 12:47:26 database kernel: [232293.914760] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Feb 19 12:47:26 database kernel: [232293.914761] 5236 total pagecache pages
Feb 19 12:47:26 database kernel: [232293.914768] 0 pages in swap cache
Feb 19 12:47:26 database kernel: [232293.914770] Swap cache stats: add 0, delete 0, find 0/0
Feb 19 12:47:26 database kernel: [232293.914771] Free swap  = 0kB
Feb 19 12:47:26 database kernel: [232293.914772] Total swap = 0kB
Feb 19 12:47:26 database kernel: [232293.914773] 262044 pages RAM
Feb 19 12:47:26 database kernel: [232293.914774] 0 pages HighMem/MovableOnly
Feb 19 12:47:26 database kernel: [232293.914774] 8019 pages reserved
Feb 19 12:47:26 database kernel: [232293.914775] 0 pages cma reserved
Feb 19 12:47:26 database kernel: [232293.914776] 0 pages hwpoisoned
Feb 19 12:47:26 database kernel: [232293.914777] [ pid ]   uid  tgid total_vm      rss nr_ptes nr_pmds swapents oom_score_adj name
Feb 19 12:47:26 database kernel: [232293.914782] [  348]     0   348     9237     1217      21       3        0             0 systemd-journal
Feb 19 12:47:26 database kernel: [232293.914785] [  451]     0   451    23693      174      16       3        0             0 lvmetad
Feb 19 12:47:26 database kernel: [232293.914787] [  452]     0   452    10744      661      24       3        0         -1000 systemd-udevd
Feb 19 12:47:26 database kernel: [232293.914789] [  550]   100   550    25081      177      19       3        0             0 systemd-timesyn
Feb 19 12:47:26 database kernel: [232293.914790] [  940]     0   940     4030      442      11       3        0             0 dhclient
Feb 19 12:47:26 database kernel: [232293.914792] [ 1054]     0  1054     1305       29       8       3        0             0 iscsid
Feb 19 12:47:26 database kernel: [232293.914794] [ 1055]     0  1055     1430      877       8       3        0           -17 iscsid
Feb 19 12:47:26 database kernel: [232293.914796] [ 1063]     0  1063    68647      712      37       3        0             0 accounts-daemon
Feb 19 12:47:26 database kernel: [232293.914797] [ 1072]     0  1072     6932      401      19       3        0             0 cron
Feb 19 12:47:26 database kernel: [232293.914799] [ 1078]     0  1078     6511      358      18       3        0             0 atd
Feb 19 12:47:26 database kernel: [232293.914801] [ 1091]     0  1091     1099      300       8       3        0             0 acpid
Feb 19 12:47:26 database kernel: [232293.914802] [ 1094]     0  1094     7136      326      19       3        0             0 systemd-logind
Feb 19 12:47:26 database kernel: [232293.914807] [ 1099]     0  1099    16377      509      35       3        0         -1000 sshd
Feb 19 12:47:26 database kernel: [232293.914809] [ 1101]   104  1101    64098      276      27       3        0             0 rsyslogd
Feb 19 12:47:26 database kernel: [232293.914811] [ 1105]   107  1105    10722      408      26       3        0          -900 dbus-daemon
Feb 19 12:47:26 database kernel: [232293.914813] [ 1113]     0  1113    70365     2553      31       6        0          -900 snapd
Feb 19 12:47:26 database kernel: [232293.914814] [ 1114]     0  1114   159018     1000      31       4        0             0 lxcfs
Feb 19 12:47:26 database kernel: [232293.914818] [ 1150]     0  1150     3343       36      11       3        0             0 mdadm
Feb 19 12:47:26 database kernel: [232293.914820] [ 1159]     0  1159    69294      179      38       3        0             0 polkitd
Feb 19 12:47:26 database kernel: [232293.914822] [ 1203]     0  1203     3618      374      12       3        0             0 agetty
Feb 19 12:47:26 database kernel: [232293.914824] [13137]     0 13137     3664      356      11       3        0             0 agetty
Feb 19 12:47:26 database kernel: [232293.914825] [13141]     0 13141     3664      329      12       3        0             0 agetty
Feb 19 12:47:26 database kernel: [232293.914827] [18086]   112 18086   397607   194828     458       5        0             0 mysqld
Feb 19 12:47:26 database kernel: [232293.914829] [ 7609]     0  7609     1126      151       8       3        0             0 apt.systemd.dai
Feb 19 12:47:26 database kernel: [232293.914831] [ 7616]     0  7616     1126      384       8       3        0             0 apt.systemd.dai
Feb 19 12:47:26 database kernel: [232293.914833] [ 7654]     0  7654    11326     1286      26       3        0             0 apt-get
Feb 19 12:47:26 database kernel: [232293.914834] [ 7835]     0  7835    11326      871      24       3        0             0 apt-get
Feb 19 12:47:26 database kernel: [232293.914836] [ 7838]     0  7838     1126      137       8       3        0             0 sh
Feb 19 12:47:26 database kernel: [232293.914837] [ 7839]     0  7839     1126      385       8       3        0             0 update-motd-upd
Feb 19 12:47:26 database kernel: [232293.914839] [ 7853]     0  7853    40982    16848      85       3        0             0 apt-check
Feb 19 12:47:26 database kernel: [232293.914841] [ 8040]     0  8040    12235      392      28       3        0             0 cron
Feb 19 12:47:26 database kernel: [232293.914843] [ 8042]     0  8042     1126      151       8       3        0             0 sh
Feb 19 12:47:26 database kernel: [232293.914844] [ 8045]     0  8045     2809      271      10       3        0             0 bash
Feb 19 12:47:26 database kernel: [232293.914846] [ 8053]     0  8053    12235      342      28       3        0             0 cron
Feb 19 12:47:26 database kernel: [232293.914848] [ 8061]     0  8061     1126      138       8       3        0             0 sh
Feb 19 12:47:26 database kernel: [232293.914850] [ 8062]     0  8062     2808      400      10       3        0             0 bash
Feb 19 12:47:26 database kernel: [232293.914851] [ 8072]     0  8072    12861      446      29       3        0             0 sudo
Feb 19 12:47:26 database kernel: [232293.914853] [ 8073]     0  8073     6222      167      15       3        0             0 systemctl
Feb 19 12:47:26 database kernel: [232293.914854] [ 8074]     0  8074    12235      305      28       3        0             0 cron
Feb 19 12:47:26 database kernel: [232293.914856] [ 8077]     0  8077    14775      102      33       3        0             0 sshd
Feb 19 12:47:26 database kernel: [232293.914858] [ 8090]     0  8090     1126      140       8       3        0             0 sh
Feb 19 12:47:26 database kernel: [232293.914877] [ 8091]     0  8091     2805      280      10       3        0             0 bash
Feb 19 12:47:26 database kernel: [232293.914879] [ 8098]     0  8098    12235      318      28       3        0             0 cron
Feb 19 12:47:26 database kernel: [232293.914881] [ 8099]     0  8099     1126      148       8       3        0             0 sh
Feb 19 12:47:26 database kernel: [232293.914882] [ 8100]     0  8100     2795      284      10       3        0             0 bash
Feb 19 12:47:26 database kernel: [232293.914884] [ 8101]     0  8101     8039      274      20       3        0             0 cron
Feb 19 12:47:26 database kernel: [232293.914885] Out of memory: Kill process 18086 (mysqld) score 768 or sacrifice child
Feb 19 12:47:26 database kernel: [232293.916359] Killed process 18086 (mysqld) total-vm:1590428kB, anon-rss:779312kB, file-rss:0kB
Feb 19 12:47:27 database systemd[1]: mysql.service: Main process exited, code=killed, status=9/KILL
Feb 19 12:47:27 database systemd[1]: mysql.service: Unit entered failed state.
Feb 19 12:47:27 database systemd[1]: mysql.service: Failed with result 'signal'.

#3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.