MySQL configuration tunning

Proper MySQL configuration is one of the most important aspects in terms of performance. Optimizing the MySQL configuration can provide up to 65% performance improvement. MySQL by default is configured to use far fewer resources than the average hardware can accommodate. InnoDB, the primary table storage engine type can use the in-memory buffer pool to cache table indexes and data. Less disk I/O is needed to get data from hard drives when the value of the in-memory buffer pool is set higher. A general recommendation is to set this parameter up to 80% of the available RAM for a dedicated database server. If you are running webserver and database server on one server it’s recommended to split the entire memory pool into two parts.

Setting for the key parameter “innodb_buffer_pool_size”

Server Type	innodb_buffer_pool_size
Combined webserver and database server, 6 GB RAM	2-3 GB
Dedicated database server, 6 GB RAM	5 GB
Dedicated database server, 12 GB RAM	10 GB
Dedicated database server, 24 GB RAM	18 GB

Further important MySQL configuration settings

innodb_buffer_pool_size = [80% of available RAM]
innodb_thread_concurrency = 2 * [numberofCPUs] + 2
innodb_flush_log_at_trx_commit = 2
thread_concurrency = [number of CPUs] * 3
thread_cache_size = 32
table_cache = 1024

# If you have some more memory available, query_cache_size could be increased more. I suggest you to not go over 512MB for query_cache_size, or there's a chance to get some performance degradation

query_cache_size = 64M
query_cache_limit = 2M
join_buffer_size = 8M
tmp_table_size = 256M
key_buffer = 32M
innodb_autoextend_increment=512
max_allowed_packet = 16M
max_heap_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1

Tip: https://tools.percona.com/wizard