Optimising MySQL settings - mysqld running out of memory

Question

I'm a bit confused about which settings to change in my my.conf file to optimise for my server (the mysql server keeps crashing due to our high traffic).

Here's the my.cnf file:

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer              = 16M
max_allowed_packet      = 16M
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 1M
query_cache_size        = 16M

And the server specs are:

CPU Cores   2 cores
RAM (Memory)    1GB
SSD (Disk Space)    20GB

Running Ubuntu 12.04 LTS

Here are my partitions:

NAME   FSTYPE   SIZE MOUNTPOINT LABEL
vda              20G            
├─vda1 ext4     476M /boot      
├─vda2 swap     477M [SWAP]     
└─vda3 ext4    19.1G / 

Any help would be much appreciated.

Looking at the error logs, I can see that mysqld was out of memory:

Feb 17 11:02:06 111488 kernel: [8276839.559141] Out of memory: Kill process 20719 (mysqld) score 75 or sacrifice child
Feb 17 11:02:06 111488 kernel: [8276839.559209] Killed process 20719 (mysqld) total-vm:1347424kB, anon-rss:30524kB, file-rss:0kB

Answer 1

General answer from the serverguide wiki:

MySQL Tuner

MySQL Tuner is a useful tool that will connect to a running MySQL instance and offer suggestions for how it can be best configured for your workload. The longer the server has been running for, the better the advice mysqltuner can provide. In a production environment, consider waiting for at least 24 hours before running the tool. You can get install mysqltuner from the Ubuntu repositories:

sudo apt-get install mysqltuner

Then once its been installed, run it:

 mysqltuner

and wait for its final report. The top section provides general information about the database server, and the bottom section provides tuning suggestions to alter in your my.cnf. Most of these can be altered live on the server without restarting, look through the official MySQL documentation (link in Resources section) for the relevant variables to change in production. The following is part of an example report from a production database which shows there may be some benefit from increasing the amount of query cache:

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 1.4G)
    query_cache_size (> 32M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 22G)

One final comment on tuning databases: Whilst we can broadly say that certain settings are the best, performance can vary from application to application. For example, what works best for Wordpress might not be the best for Drupal, Joomla or proprietary applications. Performance is dependent on the types of queries, use of indexes, how efficient the database design is and so on. You may find it useful to spend some time searching for database tuning tips based on what applications you're using it for. Once you get past a certain point any adjustments you make will only result in minor improvements, and you'll be better off either improving the application, or looking at scaling up your database environment through either using more powerful hardware or by adding slave servers.

---

• Watch the performance metrics section of the mysqltuner report. Keep maximum possible memory under 50%.
• tmp_table_size, max_heap_table_size variable: keep these equal and high.
• join_buffer_size, increase in small amounts; it will be multiplied by max_connections.
• innodb_buffer_pool_size. Make it high.

Answer 2

another possibly fix is to use the following in [mysqld] section of your /etc/mysql/my.cnf configuration.

performance_schema = off

Source: https://serverfault.com/questions/564748/mysql-mariadb-crashes-frequently

Answer 3

Resolution for docker compose

mysql:
    volumes:
        - ./config/mysql/my.cnf:/etc/mysql/conf.d/my.cnf

Config content ./config/mysql/my.cnf

[mysqld]
performance_schema = off