NickTheGreek 160 Report post Posted January 27, 2018 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 http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html Quote Share this post Link to post Share on other sites
