MySQL - Optimize For Minimal Memory

If you are just deploying a small low-traffic site that requires a MySQL database, such as a wordpress server, then you probably care more about using a small instance that doesn't require much RAM. If you deploy a small instance and stick with the defaults, you could end up with your MySQL process getting killed when your VPS runs out of memory, resulting in Wordpress showing the "Error establishing a database connection" message.

5.7 and 5.6

On Ubuntu 16.04 for MySQL 5.7, the file to edit can be found at /etc/mysql/mysql.conf.d/mysqld.cnf.

Edit the file's existing configuration variables for the following (adding where necessary)

[mysqld]

# innodb
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# caches
thread_cache_size=0
host_cache_size=0
query_cache_size=0

max_connections=10
key_buffer_size=8

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

Merged Configuration

Merging the settings in, my file looked like so (but your options such as bind-address may need to be different)

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
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
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

# Innodb tuning
innodb_buffer_pool_size = 5M
innodb_log_buffer_size  = 256K
innodb_ft_cache_size    = 1600000

# cache and misc
query_cache_size        = 0
max_connections         = 10
key_buffer_size         = 8
max_allowed_packet      = 64M
thread_cache_size       = 0

# per thread or per operation settings
thread_stack            = 131072
sort_buffer_size        = 32K
read_buffer_size        = 8200
read_rnd_buffer_size    = 8200
max_heap_table_size     = 16K
tmp_table_size          = 1K
bulk_insert_buffer_size = 0
join_buffer_size        = 128
net_buffer_length       = 1K
innodb_sort_buffer_size = 64K

# binlog
binlog_cache_size       = 4K
binlog_stmt_cache_size  = 4K
expire_logs_days        = 10
max_binlog_size         = 100M


myisam-recover-options  = BACKUP

log_error = /var/log/mysql/error.log

Be sure to check your configuration afterwards by running mysql --help.

MySQL 5.5

For those running MySQL 5.5, you need to unset the following variables from the example above, b ecause they were added in 5.6, and having them in will stop MySQL from starting up.

host_cache_size
innodb_ft_cache_size
innodb_ft_total_cache_size
innodb_sort_buffer_size

Use With Docker

If one is using a MySQL 5.7 docker image, one can simply take the first configuration (rather than the merged one), and inject it through the use of a volume like so (assuming we called it mysql.cnf):

version: "3.6"

services:
  db:
    image: mysql:5.7
    container_name: db
    volumes:
      - db-data:/var/lib/mysql
      - ./mysql.cnf:/etc/mysql/conf.d/my.cnf
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
      MYSQL_DATABASE: ${DB_NAME}
      MYSQL_USER: ${DB_USER}
      MYSQL_PASSWORD: ${DB_PASSWORD}

volumes:
  db-data:
    driver: local

References

• Configuring MySQL to use minimal memory