Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

I would say yes. Every time a connection is closed, MySQL compares the number of threads sitting idle in the thread cache to the value of thread_cache_size. If there are already that many threads in the cache, the thread serving the disconnecting session is destroyed. If not, the thread is dropped into the cache.

Each time a new connection is made, the server will check the thread cache to see if there's one available in the cache to service the new connection. If so, it's removed from the cache and assigned to the new connection. If not, a new thread is spawned.

Clearly, reusing a thread is a less cpu-intensive operation than creating and destroying, so yes, having a non-zero thread cache is helpful particularly when you are connecting and disconnecting frequently... at the probable cost of increasing memory utilization somewhat, since threads in the cache (from what I can tell) do not release any memory they've allocated to themselves, even when that memory is above the baseline that gets allocated to every new thread.

The threads_created counter tells you how many connections could not be served from the thread cache.

share|improve this answer

Is this caused by thread_cache_size i've adjusted?

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

Use persistent connections to the database to avoid connection overhead. If you cannot use persistent connections and you are initiating many new connections to the database, you may want to change the value of the thread_cache_size variable.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.