Recently found out my MySQL server hits 90% high CPU utilization when simulating over concurrent 100-500 threads request
with the default settings plus following in my.cnf
max_connections = 500 max_allowed_packet = 16M
I notice the max_connection can hit up to 500, threads_created can also go high to 200-500 and i'm thinking this has actually cause abnormally high CPU
Hence instead of using default settings i adjusted
innodb_buffer_pool_size = 2G #32bit linux server innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_thread_concurrency = 16 innodb_flush_method = O_DIRECT innodb_additional_mem_pool_size = 20M table_cache = 1028 thread_cache_size = 16 key_buffer_size=32M query_cache_size=32M join_buffer_size=1M
With the same load testing, the CPU dived down to 10% and below…
However i notice the max_connection never hits 500 anymore.
It is less than 50 now…
Is this caused by thread_cache_size i've adjusted? by default it is 0.
Or is there something wrong somewhere … I'm wondering in that case if the mysql server is correctly tested with the max connection. I want to test how if concurrent threads can hit the max_connections but somehow it never hit with the same amount i tested before.
Since the change, it never hits above 50 now.
Any idea?
Best Answer
I believe not. You improved many settings such that your queries will complete much quicker resulting in fewer threads.
thread_cache_size
should kick in when there is a burst of connections and reduce the associated overhead as per http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-optimization-tips.html