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.

mysqlBefore 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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *