Preventing MySQL From Crashing? “Creeping” Memory Leak

Question

I set up a fresh 1GB VPS server instance dedicated solely to MySQL. 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.

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:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
innodb_buffer_pool_size = 384M
innodb_log_buffer_size = 2097152
innodb_log_file_size = 20971520
innodb_strict_mode = OFF
join_buffer_size = 1048576
key_buffer_size = 88080384
max_connect_errors = 10000
max_connections = 151
myisam_recover_options = "BACKUP,FORCE"
performance_schema = 0
read_buffer_size = 1048576
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.

EDIT: I added some extra files for additional information:

SHOW GLOBAL STATUS: https://pastebin.com/SSVEJrQc

SHOW GLOBAL VARIABLES: https://pastebin.com/gV5yGdFR

SHOW ENGINE INNODB STATUS: https://pastebin.com/suHwbpiP

/var/log/mysql/error.log : https://pastebin.com/cRFGrNTp (combined past couple days)

The my.cnf file is shown at the top. I made some changes to it to reduce some of the memory usage, such as dropping the innodb_buffer_pool_size down to 384M instead of 512M. According to "top -c", the memory usage quickly rose to around 550MB and slowly creeped up to about 740M, no crash yet.

Also, the server instance has 1GB of RAM, so I'm not sure why MySQL has to crash at around 770M. It's just a fresh install of Ubuntu 16.04 and MySQL, nothing else at all, no apache or php.

EDIT: I've included more data, I'm running instance on a Dreamhost DreamCompute cloud server:

"top" Results: https://pastebin.com/RNBYMf0b

"df -h" results:

Filesystem      Size  Used Avail Use% Mounted on
udev            488M     0  488M   0% /dev
tmpfs           100M   11M   89M  11% /run
/dev/vda1        78G   22G   56G  29% /
tmpfs           497M     0  497M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           497M     0  497M   0% /sys/fs/cgroup
tmpfs           100M     0  100M   0% /run/user/1000

"iostat -x": https://pastebin.com/xUNu7fEi

"mysqltuner": https://pastebin.com/pVC3kN1C

I'm seeing a lot of "tmpfs" directories (using RAM)? Is there anything that should or shouldn't be on this list? Also, I've just now updated the my.cnf with the values Wilson Hauck provided and will restart mysqld.

Edit: Regarding "ulimit", after reading more about it, I edited /etc/security/limits.conf and added the following: "* - nofile 40000" . Now when I use "sudo sh -c "ulimit -n", it shows 40000 instead of 1024.

The "iostat -x" and "mysqltuner" reports were created right after I restarted MySQL because they wouldn't install due to "not enough memory". Thanks Wilson for the mysqltuner suggestion, that looks like a great program.

UPDATE: 2/25/2018: With all of the recommended settings set, I had mysql again crash about 10 hours ago. Here are the details from syslog:

Feb 25 09:41:52 database kernel: [739566.195215] snapd invoked oom-killer: gfp_mask=0x24201ca, order=0, oom_score_adj=-900
Feb 25 09:41:52 database kernel: [739566.195276] snapd cpuset=/ mems_allowed=0
Feb 25 09:41:52 database kernel: [739566.195310] CPU: 0 PID: 1228 Comm: snapd Not tainted 4.4.0-112-generic #135-Ubuntu
Feb 25 09:41:52 database kernel: [739566.195311] Hardware name: OpenStack Foundation OpenStack Nova, BIOS Bochs 01/01/2011
Feb 25 09:41:52 database kernel: [739566.195313]  0000000000000286 245c0a16fc0af5b0 ffff8800000839d8 ffffffff813fc233
Feb 25 09:41:52 database kernel: [739566.195317]  ffff880000083b90 ffff88003ae39c00 ffff880000083a48 ffffffff8120dafe
Feb 25 09:41:52 database kernel: [739566.195319]  ffffffff81cd8367 0000000000000000 ffffffff81e6b1a0 0000000000000206
Feb 25 09:41:52 database kernel: [739566.195321] Call Trace:
Feb 25 09:41:52 database kernel: [739566.195361]  [<ffffffff813fc233>] dump_stack+0x63/0x90
Feb 25 09:41:52 database kernel: [739566.195375]  [<ffffffff8120dafe>] dump_header+0x5a/0x1c5
Feb 25 09:41:52 database kernel: [739566.195383]  [<ffffffff811946a2>] oom_kill_process+0x202/0x3c0
Feb 25 09:41:52 database kernel: [739566.195385]  [<ffffffff81194ac9>] out_of_memory+0x219/0x460
Feb 25 09:41:52 database kernel: [739566.195394]  [<ffffffff8119aad5>] __alloc_pages_slowpath.constprop.88+0x965/0xb00
Feb 25 09:41:52 database kernel: [739566.195396]  [<ffffffff8119aef6>] __alloc_pages_nodemask+0x286/0x2a0
Feb 25 09:41:52 database kernel: [739566.195404]  [<ffffffff811e483c>] alloc_pages_current+0x8c/0x110
Feb 25 09:41:52 database kernel: [739566.195406]  [<ffffffff81190c6b>] __page_cache_alloc+0xab/0xc0
Feb 25 09:41:52 database kernel: [739566.195407]  [<ffffffff8119317a>] filemap_fault+0x14a/0x3f0
Feb 25 09:41:52 database kernel: [739566.195418]  [<ffffffff812a5d56>] ext4_filemap_fault+0x36/0x50
Feb 25 09:41:52 database kernel: [739566.195419]  [<ffffffff811bfe70>] __do_fault+0x50/0xe0
Feb 25 09:41:52 database kernel: [739566.195421]  [<ffffffff811c39c2>] handle_mm_fault+0xfa2/0x1820
Feb 25 09:41:52 database kernel: [739566.195433]  [<ffffffff810bbc6c>] ? set_next_entity+0x9c/0xb0
Feb 25 09:41:52 database kernel: [739566.195443]  [<ffffffff8106b687>] __do_page_fault+0x197/0x400
Feb 25 09:41:52 database kernel: [739566.195445]  [<ffffffff8106b957>] trace_do_page_fault+0x37/0xe0
Feb 25 09:41:52 database kernel: [739566.195450]  [<ffffffff81063f29>] do_async_page_fault+0x19/0x70
Feb 25 09:41:52 database kernel: [739566.195464]  [<ffffffff81849af8>] async_page_fault+0x28/0x30
Feb 25 09:41:52 database kernel: [739566.195465] Mem-Info:
Feb 25 09:41:52 database kernel: [739566.195470] active_anon:220242 inactive_anon:1399 isolated_anon:0
Feb 25 09:41:52 database kernel: [739566.195470]  active_file:799 inactive_file:1712 isolated_file:0
Feb 25 09:41:52 database kernel: [739566.195470]  unevictable:913 dirty:1 writeback:0 unstable:0
Feb 25 09:41:52 database kernel: [739566.195470]  slab_reclaimable:5664 slab_unreclaimable:3906
Feb 25 09:41:52 database kernel: [739566.195470]  mapped:1931 shmem:2691 pagetables:1544 bounce:0
Feb 25 09:41:52 database kernel: [739566.195470]  free:12712 free_pcp:113 free_cma:0
Feb 25 09:41:52 database kernel: [739566.195473] Node 0 DMA free:4548kB min:716kB low:892kB high:1072kB active_anon:5548kB inactive_anon:12kB active_file:1068kB inactive_file:1968kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15992kB managed:15908kB mlocked:0kB dirty:4kB writeback:0kB mapped:808kB shmem:364kB slab_reclaimable:220kB slab_unreclaimable:500kB kernel_stack:368kB pagetables:936kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:19320 all_unreclaimable? yes
Feb 25 09:41:52 database kernel: [739566.195491] lowmem_reserve[]: 0 958 958 958 958
Feb 25 09:41:52 database kernel: [739566.195495] Node 0 DMA32 free:46300kB min:44336kB low:55420kB high:66504kB active_anon:875420kB inactive_anon:5584kB active_file:2128kB inactive_file:4880kB unevictable:3652kB isolated(anon):0kB isolated(file):0kB present:1032184kB managed:1000192kB mlocked:3652kB dirty:0kB writeback:0kB mapped:6916kB shmem:10400kB slab_reclaimable:22436kB slab_unreclaimable:15124kB kernel_stack:3376kB pagetables:5240kB unstable:0kB bounce:0kB free_pcp:452kB local_pcp:452kB free_cma:0kB writeback_tmp:0kB pages_scanned:42084 all_unreclaimable? yes
Feb 25 09:41:52 database kernel: [739566.195502] lowmem_reserve[]: 0 0 0 0 0
Feb 25 09:41:52 database kernel: [739566.195504] Node 0 DMA: 1*4kB (U) 6*8kB (UME) 81*16kB (UME) 32*32kB (UM) 8*64kB (ME) 7*128kB (ME) 3*256kB (UM) 0*512kB 0*1024kB 0*2048kB 0*4096kB = 4548kB
Feb 25 09:41:52 database kernel: [739566.195514] Node 0 DMA32: 107*4kB (MEH) 240*8kB (ME) 249*16kB (UMEH) 141*32kB (UMEH) 108*64kB (UMEH) 59*128kB (UMEH) 20*256kB (UMEH) 13*512kB (UME) 9*1024kB (UMH) 0*2048kB 0*4096kB = 46300kB
Feb 25 09:41:52 database kernel: [739566.195526] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Feb 25 09:41:52 database kernel: [739566.195560] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Feb 25 09:41:52 database kernel: [739566.195561] 5817 total pagecache pages
Feb 25 09:41:52 database kernel: [739566.195576] 0 pages in swap cache
Feb 25 09:41:52 database kernel: [739566.195581] Swap cache stats: add 0, delete 0, find 0/0
Feb 25 09:41:52 database kernel: [739566.195582] Free swap  = 0kB
Feb 25 09:41:52 database kernel: [739566.195583] Total swap = 0kB
Feb 25 09:41:52 database kernel: [739566.195584] 262044 pages RAM
Feb 25 09:41:52 database kernel: [739566.195585] 0 pages HighMem/MovableOnly
Feb 25 09:41:52 database kernel: [739566.195585] 8019 pages reserved
Feb 25 09:41:52 database kernel: [739566.195586] 0 pages cma reserved
Feb 25 09:41:52 database kernel: [739566.195587] 0 pages hwpoisoned
Feb 25 09:41:52 database kernel: [739566.195588] [ pid ]   uid  tgid total_vm      rss nr_ptes nr_pmds swapents oom_score_adj name
Feb 25 09:41:52 database kernel: [739566.195593] [  348]     0   348     9237     1199      21       3        0             0 systemd-journal
Feb 25 09:41:52 database kernel: [739566.195595] [  451]     0   451    25742       46      17       3        0             0 lvmetad
Feb 25 09:41:52 database kernel: [739566.195597] [  452]     0   452    10744      376      24       3        0         -1000 systemd-udevd
Feb 25 09:41:52 database kernel: [739566.195599] [  550]   100   550    25081       61      19       3        0             0 systemd-timesyn
Feb 25 09:41:52 database kernel: [739566.195601] [  940]     0   940     4030      222      11       3        0             0 dhclient
Feb 25 09:41:52 database kernel: [739566.195603] [ 1054]     0  1054     1305       29       8       3        0             0 iscsid
Feb 25 09:41:52 database kernel: [739566.195604] [ 1055]     0  1055     1430      877       8       3        0           -17 iscsid
Feb 25 09:41:52 database kernel: [739566.195606] [ 1063]     0  1063    68647     1056      37       3        0             0 accounts-daemon
Feb 25 09:41:52 database kernel: [739566.195608] [ 1072]     0  1072     6932      491      19       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195609] [ 1078]     0  1078     6511      337      18       3        0             0 atd
Feb 25 09:41:52 database kernel: [739566.195611] [ 1091]     0  1091     1099      300       8       3        0             0 acpid
Feb 25 09:41:52 database kernel: [739566.195613] [ 1094]     0  1094     7136       99      19       3        0             0 systemd-logind
Feb 25 09:41:52 database kernel: [739566.195614] [ 1099]     0  1099    16377      273      35       3        0         -1000 sshd
Feb 25 09:41:52 database kernel: [739566.195616] [ 1101]   104  1101    64098      348      27       3        0             0 rsyslogd
Feb 25 09:41:52 database kernel: [739566.195618] [ 1105]   107  1105    10722      380      27       3        0          -900 dbus-daemon
Feb 25 09:41:52 database kernel: [739566.195619] [ 1113]     0  1113    70365     2590      31       6        0          -900 snapd
Feb 25 09:41:52 database kernel: [739566.195621] [ 1114]     0  1114   158952     1017      31       4        0             0 lxcfs
Feb 25 09:41:52 database kernel: [739566.195623] [ 1150]     0  1150     3343       36      11       3        0             0 mdadm
Feb 25 09:41:52 database kernel: [739566.195624] [ 1159]     0  1159    69294      181      38       3        0             0 polkitd
Feb 25 09:41:52 database kernel: [739566.195626] [ 1203]     0  1203     3618      374      12       3        0             0 agetty
Feb 25 09:41:52 database kernel: [739566.195627] [13137]     0 13137     3664      356      11       3        0             0 agetty
Feb 25 09:41:52 database kernel: [739566.195629] [13141]     0 13141     3664      329      12       3        0             0 agetty
Feb 25 09:41:52 database kernel: [739566.195631] [ 4475]   112  4475   345228   190282     441       4        0             0 mysqld
Feb 25 09:41:52 database kernel: [739566.195635] [28991]     0 28991     1126      141       8       3        0             0 apt.systemd.dai
Feb 25 09:41:52 database kernel: [739566.195638] [28998]     0 28998     1126      383       8       3        0             0 apt.systemd.dai
Feb 25 09:41:52 database kernel: [739566.195640] [29036]     0 29036    11324     1282      27       3        0             0 apt-get
Feb 25 09:41:52 database kernel: [739566.195641] [29217]     0 29217    11324      867      23       3        0             0 apt-get
Feb 25 09:41:52 database kernel: [739566.195643] [29220]     0 29220     1126      157       8       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195645] [29221]     0 29221     1126      367       9       3        0             0 update-motd-upd
Feb 25 09:41:52 database kernel: [739566.195646] [29235]     0 29235    40967    16609      84       3        0             0 apt-check
Feb 25 09:41:52 database kernel: [739566.195648] [29570]     0 29570    12235      358      28       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195649] [29571]     0 29571     1126      144       8       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195651] [29572]     0 29572     2809      278      10       3        0             0 bash
Feb 25 09:41:52 database kernel: [739566.195653] [29579]     0 29579    12235      290      28       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195654] [29580]     0 29580     1126      163       7       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195656] [29581]     0 29581     2809       91       9       3        0             0 bash
Feb 25 09:41:52 database kernel: [739566.195658] [29582]     0 29582    14775      135      30       3        0             0 sshd
Feb 25 09:41:52 database kernel: [739566.195659] [29584]     0 29584    12235      358      28       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195661] [29589]     0 29589    14775      134      32       3        0             0 sshd
Feb 25 09:41:52 database kernel: [739566.195662] [29592]     0 29592    12855      431      30       3        0             0 sudo
Feb 25 09:41:52 database kernel: [739566.195664] [29597]     0 29597     1126       70       8       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195665] [29598]     0 29598     2809      124       9       3        0             0 bash
Feb 25 09:41:52 database kernel: [739566.195667] [29602]     0 29602    12235      357      28       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195668] [29603]     0 29603     1126       92       8       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195670] [29604]     0 29604     2809      321      10       3        0             0 bash
Feb 25 09:41:52 database kernel: [739566.195672] [29615]     0 29615     5787       36      13       3        0             0 systemctl
Feb 25 09:41:52 database kernel: [739566.195673] [29620]     0 29620    12235      276      28       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195675] [29621]     0 29621    11236      413      26       3        0             0 sudo
Feb 25 09:41:52 database kernel: [739566.195677] [29623]     0 29623     1126      139       8       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195678] [29624]     0 29624     2807      234      10       3        0             0 bash
Feb 25 09:41:52 database kernel: [739566.195680] [29628]     0 29628    14775       75      30       3        0             0 sshd
Feb 25 09:41:52 database kernel: [739566.195681] [29629]     0 29629    12235      476      28       3        0             0 cron
Feb 25 09:41:52 database kernel: [739566.195683] [29641]     0 29641     6945      111      18       3        0             0 sudo
Feb 25 09:41:52 database kernel: [739566.195684] [29642]     0 29642     6945       87      18       3        0             0 sudo
Feb 25 09:41:52 database kernel: [739566.195686] [29643]     0 29643     1126      152       8       3        0             0 sh
Feb 25 09:41:52 database kernel: [739566.195687] [29644]     0 29644      345        1       5       3        0             0 bash
Feb 25 09:41:52 database kernel: [739566.195689] Out of memory: Kill process 4475 (mysqld) score 750 or sacrifice child
Feb 25 09:41:52 database kernel: [739566.197374] Killed process 4475 (mysqld) total-vm:1380912kB, anon-rss:761128kB, file-rss:0kB
Feb 25 09:41:53 database kernel: [739566.658202] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20807 DF PROTO=TCP SPT=54168 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739566.658214] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20808 DF PROTO=TCP SPT=49412 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739566.658291] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20809 DF PROTO=TCP SPT=54293 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739566.666146] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20810 DF PROTO=TCP SPT=53588 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739566.666160] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20811 DF PROTO=TCP SPT=53612 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database systemd[1]: Started MySQL Community Server.
Feb 25 09:41:53 database CRON[29570]: (CRON) info (No MTA installed, discarding output)
Feb 25 09:41:53 database systemd[1]: mysql.service: Main process exited, code=killed, status=9/KILL
Feb 25 09:41:53 database systemd[1]: mysql.service: Unit entered failed state.
Feb 25 09:41:53 database systemd[1]: mysql.service: Failed with result 'signal'.
Feb 25 09:41:53 database systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Feb 25 09:41:53 database systemd[1]: Stopped MySQL Community Server.
Feb 25 09:41:53 database systemd[1]: Starting MySQL Community Server...
Feb 25 09:41:53 database kernel: [739567.046536] [UFW BLOCK] IN=ens3 OUT=      MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=42 ID=21475 DF PROTO=TCP SPT=37118 DPT=22 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739567.066187] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20869 DF PROTO=TCP SPT=54293 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739567.066313] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20870 DF PROTO=TCP SPT=49412 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739567.066324] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.*** DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20871 DF PROTO=TCP SPT=54168 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:53 database kernel: [739567.082221] [UFW BLOCK] IN=ens3 OUT= MAC=fa:16:3e:bc:28:e3:44:f4:77:a7:c0:20:08:00 SRC=***.***.***.***DST=***.***.***.*** LEN=40 TOS=0x00 PREC=0x00 TTL=61 ID=20873 DF PROTO=TCP SPT=53612 DPT=3306 WINDOW=0 RES=0x00 RST URGP=0
Feb 25 09:41:54 database kernel: [739567.524532] audit: type=1400 audit(1519551714.232:296): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/29710/status" pid=29710 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Feb 25 09:41:54 database kernel: [739567.524603] audit: type=1400 audit(1519551714.232:297): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=29710 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=0
Feb 25 09:41:54 database kernel: [739567.524670] audit: type=1400 audit(1519551714.232:298): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/29710/status" pid=29710 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=112 ouid=112
Feb 25 09:41:56 database systemd[1]: Started MySQL Community Server.

According to MySQLtuner, it says: "Maximum possible memory usage: 664.8M (66.99% of installed RAM)", so it must be exceeding this somehow.

Answer 1

• Your query_cache_size is 52428800 = 50M
• innodb_buffer_pool_size = 512M
• key_buffer_size = 88080384 = 84M

That's already ~650M, which leaves MySQL about 50M for 'other stuff'.

If you tweak down some of these settings, chances are that you can stay below 700M. Unless your version of MySQL really has a memory leak.

Try reducing some of these numbers by another 50M in total.

Bonus:

Just found this calculator: http://www.mysqlcalculator.com/. Might help you figure this out more accurately. Can't speak to how accurate this tool is though

Answer 2

To answer part of your question:

Also, the server instance has 1GB of RAM, so I'm not sure why MySQL has to crash at around 770M. It's just a fresh install of Ubuntu 16.04 and MySQL, nothing else at all, no apache or php.

A server doesn't have "nothing else at all" even if you don't install anything else yourself, there's always background stuff going on as well, and whatever your VPS image includes. You can use top (or if available my preferred tool, htop) to see what's running and what's using memory.

In the OOM kill log, for instance, apt-check has an rss size of 16848, meaning it's taking another 70GB of RAM by itself (see this answer on OOM kiler logs - the numbers indicate 4kB blocks). Additionally, many of the system "directories" such as /tmp are actually stored in RAM rather than on disk. You can see if that's the caes on your machine by running df -h - anything listed with tmpfs as its filesystem is stored in RAM, and if it has space being used, is using RAM as well.

Put various things together, and it's plausible that various system processes can take a decent chunk of that RAM overhead, even without any other processes you specifically installed.

Answer 3

Suggestions to consider for your my.cnf/ini [mysqld] section,

ulimit -n 40000   # at your Linux command prompt to raise n open files limit
table_open_cache=10000  # from 2000 to support 1M+ opened in 2 days
table_definition_cache=2500  # from default to support 2000+ opened in 2 days
open_files_limit=30000  # from 5000 to support 900,000 + opened in 2 days
max_connections=50  # from 151 to support 17 max_used_connections
read_rnd_buffer_size=128K  # from 256k default to reduce RD RPS
innodb_change_buffer_max_size=15  # from 25% of innodb_buffer_pool_size 1% used
innodb_log_buffer_size=12M  # from 2M to cover 30 minutes of log
innodb_log_file_size=120M  # from ~ 20M to cover a few days
#max_allowed_packet=16M  # lead with # for default of 1M m_a_p

if you need more than 1M, in your SESSION SET @max_allowed_packet=nnnnnnnn; up to 1G and 1G is the LIMIT.

query_cache_size=0  # from 16M - it is already OFF, do not waste RAM on it
query_cache_limit=1K  # from 1M to conserve more RAM
query_cache_min_res_unit=512  # from 4096 to store more small results, if ever used
innodb_buffer_pool_instances=8  # from 1 to minimize mutex contention 

will be fine with you UNDER 1G per instance of innodb_buffer_pool_size

innodb_lru_scan_depth=128  # from 1024 which is causing page_cleaner warnings
innodb_page_cleaners=64  # from 1 to auto follow = innodb_buffer_pool_instances
thread_cache_size=50  # from 8 default to support 17 max_used with room for growth.

from my perspective, backup your current my.cnf/ini and implement all. There are more opportunities for another day. Could we use Stack Overflow's chat next week ?