I need to performance tune a mysql server and need help.
I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are about 100 other innodb tables on the same database. The MyISAM table is pretty much read only and used by user for searches.
The server has a max_connection value of 800, under load test of about 300 concurrent users the query time starts to increase significantly. Without load the queries take about 500ms to run.
The goal is to significantly improve the performance of searches on the MyISAM table.
The size of the tables in MB:
data_size index_size total engine
8991 6920 15911 InnoDB
1110 718 1829 MyISAM
The following are my settings:
connect_timeout 10
innodb_additional_mem_pool_size 24117248
innodb_buffer_pool_size 12G
innodb_commit_concurrency 0
innodb_flush_log_at_trx_commit 1
innodb_log_buffer_size 11534336
innodb_log_file_size 449839104
innodb_open_files 300
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
join_buffer_size 131072
key_buffer_size 2147483648
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
max_allowed_packet 134217728
max_connections 800
max_delayed_threads 20
max_join_size 18446744073709551615
max_user_connections 0
myisam_max_sort_file_size 107374182400
myisam_mmap_size 18446744073709551615
myisam_sort_buffer_size 70254592
open_files_limit 2048
query_cache_limit 104857600
query_cache_min_res_unit 4096
query_cache_size 419430400
query_cache_type ON
read_buffer_size 262144
socket MySQL
sort_buffer_size 2097152
table_type InnoDB
thread_cache_size 100
thread_handling one-thread-per-connection
thread_stack 262144
tmp_table_size 1073741824
