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
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
At the end of doing so, 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
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
First published: 16th August 2018