Optimizing MySQL, Intermediate results
In the past few weeks we’ve been looking into different MySQL configuration options and were running many simple tests. Even though all tests were different and were targeting different configuration options – one thing was common – there were no simultaneous connections and queries to the same tables.
Before we move to next tests, where we are going to run similar queries against one or two tables at the same time, I would like to present test result summary and most important configuration options. These recommendations are good enough for most common situations and servers.
Configuration options, that made huge difference, comparing to the default values
- Query cache must be enabled and configured
query_cache_type=1 query_cache_limit=1M query_cache_size=256M query_cache_min_res_unit=512
2. Various read and sort buffers must be set
key_buffer_size=128M join_buffer_size=8M myisam_sort_buffer_size=1M sort_buffer_size=1M read_buffer_size=4M read_rnd_buffer_size=4M
3. Set InnoDB tables to be stored each in separate file
innodb_file_per_table=1
4. Tweak MyISAM table
myisam_use_mmap=1
Configuration options, that help to run more complex queries
- Let MySQL have larger in-memory temporary tables
tmp_table_size=256M max_heap_table_size=256M
2. Tweak InnoDB engine
innodb_buffer_pool_size=134217728 innodb_flush_log_at_trx_commit=2 innodb_thread_concurrency=8 innodb_use_sys_malloc=1 innodb_buffer_pool_size=128M
3. Additional tweaks – open file limit, skip name resolve, maximum size of a single packet, number of opened tables to keep in memory
open_files_limit=50000 skip-name-resolve table_open_cache=4096 table_definition_cache=4096
Combined my.cnf, ready to be copied and deployed
[mysqld] query_cache_type=1 query_cache_limit=1M query_cache_size=256M query_cache_min_res_unit=512 key_buffer_size=128M join_buffer_size=8M myisam_sort_buffer_size=1M sort_buffer_size=1M read_buffer_size=4M read_rnd_buffer_size=4M innodb_file_per_table=1 myisam_use_mmap=1 tmp_table_size=256M max_heap_table_size=256M innodb_buffer_pool_size=134217728 innodb_flush_log_at_trx_commit=2 innodb_thread_concurrency=8 innodb_use_sys_malloc=1 innodb_buffer_pool_size=128M open_files_limit=50000 skip-name-resolve table_open_cache=4096 table_definition_cache=4096
Read more about all MySQL system variables here: https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html