MySQL Threads or what is this

Posted on

MySQL Threads or what is this – Managing your servers can streamline the performance of your team by allowing them to complete complex tasks faster. Plus, it can enable them to detect problems early on before they get out of hand and compromise your business. As a result, the risk of experiencing operational setbacks is drastically lower.

But the only way to make the most of your server management is to perform it correctly. And to help you do so, this article will share nine tips on improving your server management and fix some problem about linux, mysql, performance-tuning, , .

top output showing mysql threads

How can I reduce that number? I have a quad core machine. Do I need to edit thread_pool_size and by reducing them do I get better performance?

Should a lot of queries or something happen could is choke the machine?

max_connections = 500
max_user_connections = 25
key_buffer = 64M
myisam_sort_buffer_size = 32M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 16K
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 160M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1

open_files_limit = 8192

max_allowed_packet = 16M

key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Solution :

Seems like you have broken indexes, or even likely – “slow” queries with no indexes.

Keep in mind that there is VERY dangerous to increase max_connections blindly. Each thread bite alot of memory for buffering, and, probably, you run out of RAM and get heavy paging and slowdown instead acceleration.

Turn on slow queries logging:

SET @@global.log_slow_queries = 1;
SET @@global.log_queries_not_using_indexes = 1;

and check for rows with high value in Rows_examined:

Leave a Reply

Your email address will not be published.