Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question

Question

Help with basic mysqld.cnf configurations.

Posted April 25, 2020 786 views
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

Add a comment
By:
rosswaters
edited by bobbyiliev

Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question
Add a comment
1 comment

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.

×
4 answers
WelshPaul April 25, 2020

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

Reply Report
  • rosswaters April 25, 2020

    I looked at that the other day but do not know what some of the parameters pertain to. Could you give me advice on what some of the parameters are based on?

    I am in the process of waiting for some dead time to scale up the server to 4 GBs RAM even though I double that is the issue at this point. The cost of upgrading is marginal so it is worth it in the long run for growth.

    Thanks for any info you can give me on the parameters,
    Ross

    Reply Report
WelshPaul April 27, 2020

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

defaultstorageengine = InnoDB
explicitdefaultsfortimestamp = 1
interactivetimeout = 180
pidfile = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
user = mysql
waittimeout = 180
joinbuffersize = 256K
skip-name-resolve

MyISAM

keybuffersize = 8M

SAFETY

maxallowedpacket = 16M

DATA STORAGE

datadir = /var/lib/mysql

CACHES AND LIMITS

maxconnections = 150
maxheaptablesize = 32M
querycachesize = 0
querycachetype = 0
tabledefinitioncache = 1024
tableopencache = 2048
threadcachesize = 50
tmptablesize = 32M

INNODB

innodbbufferpoolsize = 1024M
innodbbufferpoolinstances = 1
innodbflushmethod = O_DIRECT

LOGGING

logerror = /var/log/mysqld.log
logqueriesnotusingindexes = 0
slowquerylog = 0
slowquerylogfile = /var/log/mysql-slow.log
longquerytime = 5
innodblogfile_size = 128M

Reply Report
  • rosswaters April 27, 2020

    Thanks, WelshPaul,

    Can I ask, how much RAM does your server have? Forgot, do you have swap space on your server for overhead memory?

    Thanks again for letting me know what others have in there mysqld.conf

    Ross

    Reply Report
bobbyiliev April 25, 2020

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

How To Add Swap Space on Ubuntu 16.04
by Justin Ellingwood
One of the easiest way of increasing the responsiveness of your server and guarding against out of memory errors in your applications is to add some swap space. In this guide, we will cover how to add a swap file to an Ubuntu 16.04 server. <$>[warning] [label...
Reply Report
  • rosswaters April 25, 2020

    The site is fine until backups are made, at least from every log and report I have run. One of the 4 sites is a membership site and every page basically has logic on determining who you are and if you are logged in and if what do you own as far as what is available to you.

    It is this site that is causing the issues, and basically only when the site is being backed up. I will check to see if perl is installed on the server and run the mysqltuner.

    The site in question also states it needs 256MB at least for PHP, I am thinking it needs more because it is exhausting memory during the backups.

    There are two options for backups, running a mysql dump kills the server, if it runs php code to pull the data it runs out of memory but leaves the site up most of the time. I am using Duplicator Pro and I have contacted them about the issue and will see what they say before changing anything in the system at this point.

    Reply Report
WelshPaul April 27, 2020

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.

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help