MySQL Tuning Parameters
From LongJump Support Wiki
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
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