I’ve got a db-server, dual quad-core, MySQL 5, with 8Gb of memory (64bits CentOS). My db is arround 70Gb and some tables have indexes around 2Gb.
I am playing arround with the mysql config-values, using the tuning-script, because performance is going down, for example it’s using all the swap memory of the OS and the load will jump up and the whole server will become unresponsive at some times. I guess it has to do with the key_buffer size, which is setup pretty high right now.
What is the best size for the key_buffer in this scenario? Should it be big enough to hold more then one index or doesn’t it work that way?
P.S.: A quick summary of the config-values:
key_buffer = 5020Mmax_allowed_packet = 1024Mtable_cache = 4096sort_buffer_size = 512Mread_buffer_size = 512Mmyisam_sort_buffer_size = 512Mthread_cache = 32query_cache_limit = 16Mquery_cache_size = 256Mthread_concurrency = 8net_read_timeout = 3600net_write_timeout = 3600group_concat_max_len = 10485760max_heap_table_size = 128Mmax_connections = 250tmp_table_size = 512Mjoin_buffer_size = 512Mopen_files_limit = 25000