Question

Help with basic mysqld.cnf configurations.

  • Posted April 25, 2020
  • MySQL

I have a Digital Ocean server droplet running 2vCPUs and 2GBs RAM. I started with the 1vCPU 512 to build and have expanded it. The default mysqld.cnf have never been messed with and I have issues every now and then with it. I get the following message:

Out of memory: Kill process 1069 (mysqld) score 220 or sacrifice child

It then kills the process taking all my Wordpress sites down and is causing DB errors in my tables.

It also then states:

Killed process 1069 (mysqld) total-vm:1642480kB, anon-rss:448208kB, file-rss:0kB, shmem-rss:0kB

I am guessing by the numbers I have 1.5GBs of RAM free by the total-vm kB? is this correct or did it consume that much when it failed. Memory loads typically stay at the 39-43% on the droplet dashboard but they do spike hard during the Out of Memory message. The max on the CPU I ever see is around the 15% mark.

I have 4 Wordpress sites running on the server droplet and I am thinking that the config on the mysqld.cnf is wrong but I don’t know what I am doing. I am going to be increasing the server to 4GBs in less than a week as I am adding a 5th Wordpress site to the server but I figured I might want to address the memory issue first before launching another site.

My PHP settings for all installs are as follows:

PHP Memory Limit:	256 MB
PHP Time Limit:	        300
PHP Max Input Vars:	3500
MySQL Version:		5.7.29

What is enabled and disabled in the mysqld.cnf:

key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10

query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size         = 100M

Thank you for any help on this before I increase stuff and possibly make it worst :)

Ross

Subscribe
Share

The big bold stuff is the disabled items, not sure how the hash caused that. Ross


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Same as you - 2cpu / 2GB ram. While its recommended not to use swap on ssd’s I do have swap enabled:

Filename Type Size Used Priority /swapfile file 3145724 0 -2

Been up and running three years with zero downtime and zero crashes.

This is mine:

For advice on how to change settings please see

http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysql]

CLIENT

port = 3306 socket = /var/lib/mysql/mysql.sock

[mysqld]

GENERAL

default_storage_engine = InnoDB explicit_defaults_for_timestamp = 1 interactive_timeout = 180 pid_file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock symbolic-links = 0 user = mysql wait_timeout = 180 join_buffer_size = 256K skip-name-resolve

MyISAM

key_buffer_size = 8M

SAFETY

max_allowed_packet = 16M

DATA STORAGE

datadir = /var/lib/mysql

CACHES AND LIMITS

max_connections = 150 max_heap_table_size = 32M query_cache_size = 0 query_cache_type = 0 table_definition_cache = 1024 table_open_cache = 2048 thread_cache_size = 50 tmp_table_size = 32M

INNODB

innodb_buffer_pool_size = 1024M innodb_buffer_pool_instances = 1 innodb_flush_method = O_DIRECT

LOGGING

log_error = /var/log/mysqld.log log_queries_not_using_indexes = 0 slow_query_log = 0 slow_query_log_file = /var/log/mysql-slow.log long_query_time = 5 innodb_log_file_size = 128M

Use this to work out the correct values for your server: https://www.mysqlcalculator.com

Hi there @rosswaters,

In this case, I could suggest starting with a few general optimizations like"

  • Optimize your WordPress website as much as possible, for example: delete any plugins that you do not need, enable CDN and add a good caching plugin. This should reduce the number of requests hitting the server

  • As you only have 2GB of RAM I could suggest setting your PHP memory limit to 128MB unless you actually need 256MB

  • The MySQL settings look good, I could suggest taking a look at this answer here on howto Tweak MySQL/MariaDB configuration for increased performance and stability with mysqltuner:

https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

  • Another thing that I could suggest is to add swap so that you could have some buffer in case your server runs out of RAM, you can do that by following the steps here:

https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-16-04

As you mentioned, increasing your server’s RAM would also be beneficial.

Let me know how it goes! Regards, Bobby