I’ve been experimenting with MySQL Fabric – the official tool in development to provide HA and sharding. Part of this experimentation has meant that I have been running 7 copies of the MySQL Server on my laptop at once, which can consume a lot of memory 🙂
So today I wanted to write about how to configure MySQL to use less memory, which may also be beneficial in situations where peak performance is not important. For example: a developer VM environment.
Previous configuration guides
Before I get started, let me point to some of my previous configuration guides:
- Reducing MySQL durability for testing environments – losing the ability to recover all changes in exchange for a performance gain.
- Improving MySQL’s default configuration – explaining the motivation of changing the defaults for MySQL 5.6.
- What to tune in MySQL 5.6 after installation – explaining what changes to make in production for MySQL 5.6.
Configuring MySQL to use minimal memory
The MySQL defaults have to balance performance with what is considered reasonable for what may be a development system with other applications needing to run alongside MySQL. In many cases, this will mean 4-8GB, but on virtual machines (or in my case with 7 copies of mysqld running), there is a lot less available.
Obligatory warning: If you are running these settings on a machine with 1GB+ RAM, you should expect worse performance when compared to the defaults.
| Setting | Default | Minimum |
| innodb_buffer_pool_size | 128M | 5M |
| innodb_log_buffer_size | 1M | 256K |
| query_cache_size | 1M | 0 |
| max_connections | 151 | 1 (although 10 might be more reasonable) |
| key_buffer_size | 8388608 | 8 |
| thread_cache_size | (autosized) | 0 |
| host_cache_size | (autosized) | 0 |
| innodb_ft_cache_size | 8000000 | 1600000 |
| innodb_ft_total_cache_size | 640000000 | 32000000 |
| thread_stack | 262144 | 131072 |
| sort_buffer_size | 262144 | 32K |
| read_buffer_size | 131072 | 8200 |
| read_rnd_buffer_size | 262144 | 8200 |
| max_heap_table_size | 16777216 | 16K |
| tmp_table_size | 16777216 | 1K |
| bulk_insert_buffer_size | 8388608 | 0 |
| join_buffer_size | 262144 | 128 |
| net_buffer_length | 16384 | 1K |
| innodb_sort_buffer_size | 1M | 64K |
| binlog_cache_size | 32K | 4K |
| binlog_stmt_cache_size | 32K | 4K |
(Note: you may be able to save a little more memory by disabling InnoDB or performance schema, but I don’t recommend these two steps.)
To summarize these changes:
# /etc/my.cnf: innodb_buffer_pool_size=5M innodb_log_buffer_size=256K query_cache_size=0 max_connections=10 key_buffer_size=8 thread_cache_size=0 host_cache_size=0 innodb_ft_cache_size=1600000 innodb_ft_total_cache_size=32000000 # 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