MySQL memory usage crashing server

Question

I just want to preface this with the fact I am new to server admin work like this, but am very interested and eager to learn. I'm hosting a small WordPress site on Digital Ocean, the site is relatively new and has little to no traffic. The droplet is running a typical LAMP stack and has 1GB memory, which in my experience has been sufficient in the past as I tend to not use a lot of plugins and use fairly light themes. The only plugin I installed that I haven't used before is WordFence. Anyways, during development, I occasionally get an error that reads "Error establishing connection to the DB", sometimes the site will not load at all and connecting via SSH is super sluggish. Today I experienced the issue and after it resolved itself, I SSH'd in to see what I can find. Below is what I was able to gather:

top constantly list mysql up top as it's usage is high

1724 mysql 20 0 1333488 405436 0 S 0.7 40.5 0:11.53 mysqld

grep -Ei 'oom|out of memory' /var/log/syslog

Jan 25 15:18:40 droplet kernel: [599977.961722] oom_kill_process.cold+0xb/0x10 Jan 25 15:18:40 droplet kernel: [599977.961936] [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name Jan 25 15:18:40 droplet kernel: [599977.962031] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mysql.service,task=mysqld,pid=31337,uid=112 Jan 25 15:18:40 droplet kernel: [599977.962130] Out of memory: Killed process 31337 (mysqld) total-vm:1342776kB, anon-rss:439804kB, file-rss:0kB, shmem-rss:0kB, UID:112 pgtables:1264kB oom_score_adj:0 Jan 25 15:18:40 droplet kernel: [599978.039990] oom_reaper: reaped process 31337 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB Jan 25 15:26:50 droplet kernel: [600468.028506] systemd invoked oom-killer: gfp_mask=0x100cca(GFP_HIGHUSER_MOVABLE), order=0, oom_score_adj=0 Jan 25 15:26:50 droplet kernel: [600468.028534] oom_kill_process.cold+0xb/0x10 Jan 25 15:26:50 droplet kernel: [600468.028658] [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name Jan 25 15:26:50 droplet kernel: [600468.028754] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mysql.service,task=mysqld,pid=67924,uid=112 Jan 25 15:26:50 droplet kernel: [600468.028885] Out of memory: Killed process 67924 (mysqld) total-vm:1310584kB, anon-rss:385228kB, file-rss:0kB, shmem-rss:0kB, UID:112 pgtables:1132kB oom_score_adj:0 Jan 25 15:26:50 droplet kernel: [600468.092875] oom_reaper: reaped process 67924 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB 

Being new to this, I'm unsure if this is of concern (possibly an attack of some sort) or if it's simply a configuration Digital Ocean's 1-click WordPress install does or possibly that 1GB of memory isn't enough anymore. Any insights would be greatly appreciated!

Answer 1

I'm not an expert in MySQL but I've spent a bit of time reducing MySQL 8.0 memory usage on a non-critical personal server I run and it's working fine. Others may post better answers - go with them over this.

I run an AWS EC2 t3a.nano server with five Wordpress instances, MySQL 8.x., PHP 7.x, and a couple of other things with 512MB RAM and 1GB swap, so 1GB is plenty for a low volume site. You can add some swap space if you like, your OS will page out data it doesn't need so that running processes have more RAM available.

You need to configure MySQL to run with limited memory, things like reducing RAM sizes, buffers, and turning off performance schema. Reducing PHP RAM usage will also help reduce the chance of out of memory killer kicking in. MySQL 8.0 still uses a lot more RAM than MySQL 5.6, though an expert may be able to reduce it more than I have.

Here's the key parts from my MySQL 8 config - noting here that I'm not an expert on MySQL and you should do some research to understand this configuration before applying it. Some of these might be the defaults, but I generally set most parameters near their lowest allowable value.

Your config files might be in different locations. Make sure you take a backup ideally of the whole server before starting any changes, and back up your files before you change them.

/etc/mysql/conf.d/mysql.cnf

# global settings performance_schema = OFF innodb_buffer_pool_size=50M innodb_flush_method=O_DIRECT innodb_log_buffer_size=1048576 innodb_log_file_size=4194304 innodb_max_undo_log_size=10485760 innodb_sort_buffer_size=64K innodb_ft_cache_size=1600000 innodb_max_undo_log_size=10485760 max_connections=20 key_buffer_size=1M # per-thread settings thread_stack=140K thread_cache_size = 2 read_buffer_size=8200 read_rnd_buffer_size=8200 max_heap_table_size=16K tmp_table_size=128K temptable_max_ram=2097152 bulk_insert_buffer_size=0 join_buffer_size=128 net_buffer_length=1K 

/etc/mysql/mysql.conf.d/mysqld.cnf

key_buffer_size = 16M thread_stack = 256K 

Here's some tweaks I did to the PHP config to reduce RAM usage

/etc/php/7.4/fpm/pool.d/www.conf

pm.max_children = 3 pm.start_servers = 1 pm.min_spare_servers = 1 pm.max_spare_servers = 1 pm.process_idle_timeout = 20s; pm.max_requests = 50 php_admin_value[memory_limit] = 64M 

Server Stats

Here's a few stats from the servers 'top' command

Overall

• Mem 448MB, 42MB free, 190MB used, 216MB buffer / cache
• Swap 1024, 725MB free, 300MB used

Processes

• MySQL Virt 1459020 Res 44696 CPU 3% Mem 10%
• PHP FPM 7.x Virt 223124 Res 32464 CPU 0% Mem 7%
• Nginx Virt 63932 Res 7520 CPU 0% Mem 2%