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:

# per thread or per operation settings

#settings that relate to the binary log (if enabled)

Published by


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.

  • Other tips:

    Avoid the MEMORY storage engine.

    Avoid creating indexes on partitioned tables, given

    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.

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

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

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


    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?

      • 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


    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.

  • 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):


    This is because these variables were introduced in 5.6