Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

thread_cache_size reduce CPU and max connection?

Tags:

mysql

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?

like image 723
flyclassic Avatar asked Dec 29 '11 07:12

flyclassic


People also ask

What is Max connections in MySQL?

MySQL Connection Limits At provision, Databases for MySQL sets the maximum number of connections to your MySQL database to 200. You can raise this value by Changing the MySQL Configuration.

How many threads can MySQL handle?

The default number of groups is 16. For guidelines on setting this variable, see Section 5.6. 3.4, “Thread Pool Tuning”. The maximum number of threads per group is 4096 (or 4095 on some systems where one thread is used internally).


1 Answers

Creating new threads (since I'm guessing your application doesn't use connection pooling) takes a decent amount of overhead. With MySQL re-using threads in this manner (up to 16 of them) it is no longer utilizing the additional CPU to create threads as often and it also is likely finishing the operations in a timely manner, therefore closing the connection faster and therefore holding less connections open at a time.

Just a guess :-)

like image 155
1tiger1 Avatar answered Nov 15 '22 00:11

1tiger1