How to tune mysql easily

General recommendation:

You can get a brief recommendation about MySQL by using the Perl script of mysql-tuner

How to use processlist?

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

Why there are so many sleep processes?

By default, mysqld will timeout database connections based on two server options:

Both are 28,800 seconds(8 hours) by default.

You must set these as follows in my.cnf (take effect after mysqld is restarted)

[mysqld]
interactive_timeout=180
wait_timeout=180

If you don’t wakt to restart mysql, then run these tow commands

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

Leave a Reply

Your email address will not be published. Required fields are marked *