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

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • http://karwin.blogspot.com Bill Karwin

    Other tips:

    Avoid the MEMORY storage engine.

    Avoid creating indexes on partitioned tables, given http://bugs.mysql.com/bug.php?id=69325

    Some buffers in 5.6 are now sized by formula based on max_connections, aren't they? So reducing that would be helpful to reduce memory footprint.

    The table_definition_cache is used as a soft limit on the InnoDB in-memory data dictionary in 5.6, and some people have reported saving a lot of memory by reducing this variable.

    Could reducing the max_allowed_packet save memory? Of course it will mean you can't create result sets with large rows.

    • http://www.tocker.ca/ Morgan Tocker

      Thanks Bill!

      Bug #69325 is fixed in 5.6.16 :)

      RE max_connections: Yes. One specific example I am aware of is that performance schema fixed memory allocation will be lower.

      RE table_definition_cache: Great suggestion.

      RE max_allowed_packet: This is a max upper bound, with net_buffer_length being the minimum. It could make sense to decrease it, but this may also be a functional change as it may now refuse queries.

  • Mark Leith

    If memory is the biggest concern, and instrumentation is not, then setting performance_schema=0 will help too..

    • http://www.tocker.ca/ Morgan Tocker

      I actually have a note on that in the above text, hidden below the settings :)

      > 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.

  • Tim Callaghan

    As a fun but insane task it would be interesting to see how the server performs with these settings!

  • http://wpdigest.kr/ Aesop Han

    i'm having messages 'Could not connect: Too many connections'. So I'd like to 'max_connection' in wp-config.php. Is it possible and How to ?

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

  • Lessan Vaezi

    Thanks Bill!

    I simply added a low table_definition_cache (=50) to the default my.cnf and reduced memory footprint from ~500Mb to ~80Mb. This is on a virtual server for development purposes with only one user and a few dozen tables. It was the only option that had a significant effect on memory usage.

  • roymustang

    Hello.

    I'm having problens to understand where MySql 5.6, windows version, safe his parameters. I changed the INNODB_BUFFER_POOL_SIZE to 10M but instance stay getting 100M. The file I'm changing is "my.ini" in
    "C:Program Files (x86)MySQLMySQL Server 5.6" directory