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:

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

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.

View all posts by morgo

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.
- 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..
- 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!
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.
- Geoff Fawkes
  
  Lessan, how did you get table_definition_cache = 50?
  
  The manual says the minimum value is 400?
  
  https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache
  - Lessan Vaezi
    
    Interesting. I got it by trial and error, as I was adjusting different options.
    
    Maybe I was using a previous version of MySQL that accepted 50 as a valid value? After I stumbled upon it, I reset my.cnf and verified the ~500Mb memory footprint, then adjusted just the table_definition_cache setting and verified that it reduced memory usage. I’m not sure what version of MySQL I was using at the time, and what the original value of table_definition_cache was before I adjusted it.
    - Geoff Fawkes
      
      Thanks for the reply, I’m trying to get our MySQL memory footprint down, but I don’t think I should lower table_definition_cache that low, it does reduce memory footprint but it also reduced performance (slightly).
      
      The manual says the default value is “400 + (table_open_cache / 2)” so our table_definition_cache was 1400, I can’t find much information on the negatives of having a very small table_definition_cache and a larger table_open_cache though, I guess just more disk read/write ops.
      
      I might just have to change it again and monitor it over a few days, but it takes 24-48 hours for our MySQL to reach it’s maximum memory usage, the server’s not running out of memory, just getting within 500MB.
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
Observador Tecnológico

Hi, Thanks for you excellent help.
We have a VM server whit Linux Centos 6,6, 64 BIT, 3.5 GB RAM, 1CPU core, 40 hosting accounts, small sites. The mysqld process take 320 MB of memory…is normal? Can you helpme whit recommended my.cnf configuration? Thanks.
Sergei G

performance schema reduced my memory consumption by order of magnitude: from 468572 to 63004.
vanilla

only <= 5.5 ?
Programster

For those running MySQL 5.5, you will need to comment out or remove the following variables in the configuration provided in order for the configuration to work (otherwise your mysql service will not start/restart):

host_cache_size
innodb_ft_cache_size
innodb_ft_total_cache_size
innodb_sort_buffer_size

This is because these variables were introduced in 5.6