MySQL Tuning Parameters

These section provides some typical MySQL configuration values that are appropriate for a production system. These values assume:

• 100Mb Primary Ethernet Card
• 32 GB Memory
• 2 Processors
• 2 Cores per Processor
• Switched Ethernet Port
• 64-bit Red Hat Enterprise Linux ES 4

Important: Before changing parameters, take the server and MySQL down. Do not adjust parameters while MySQL is running. Doing so can potentially cause the database to be corrupted. The settings shown here are an example. Consult the latest MySQL document for information on the best settings to use.

my.cnf / my.ini Options

interactive-timeout = 7200
wait-timeout = 7200
back_log = 200
max_connections = 350
max_connect_errors = 10
table_cache = 8192
max_allowed_packet = 48M
binlog_cache_size = 8M
max_heap_table_size = 120M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache = 200
thread_concurrency = 24 
query_cache_size = 500M 
query_cache_limit = 1M 
ft_min_word_len = 4 
thread_stack = 256K 
tmp_table_size = 120M 

InnoDB-Specific Options

innodb_additional_mem_pool_size = 48M
innodb_buffer_pool_size = 23G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 24
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
odb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 300