Configuring MySQL to use minimal memory

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:

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
  • Bob

    Note that setting:

    thread_stack

    Gave me problems when doing some queries with binary data and when extracting that binary data with a trigger into "human readable" format into another table.
    I removed the "thread_stack" line/config; Memory usage did not go up and the problem did not appear again.

  • Bob

    Also talking about memory usage, with this configuration on Windows Embedded Standard 7 SP1, I am getting 66MB memory usage. Default (noinstall-zip) I get about 300MB and trimming it down myself I was able to get 100MB.