Analysis on-mysql performance of website bottleneck analysis

I. About slow query setup and analysis

Find Slow Query parameters

' long% ' ; +-----------------+----------+| Variable_name   | Value    10.000000 |+-----------------+----------+

Mysql> Show variables like ' slow% '; +---------------------+--------------------------------+| Variable_name       | Value                          |+---------------------+--------------------------------+| slow_launch_time    | 2                              | | slow_ Query_log      | OFF                            | | slow_query_log_file |/var/lib/mysql/ubuntu-slow.log |+---------------------+------------------------- -------+

Mysql> Show variables like '%queries% '; +-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF   | | log_slow_queries              | On    |+-------------------------------+-------+

Set slow query parameters (author recommended set to 1s)

Mysql>  set global long_query_time=1;
mysql> set global slow_query_log= ' on ';

Some friends in addition to setting the above will also set the query in the non-indexed statement also to on (that is, log_queries_not_using_indexes), you can according to the current needs to determine whether the need to increase.

So that everyone can be based on the corresponding log to optimize, of course, the above is mainly for the MySQL statement optimization

Second, show Processlist, show full processlist view and analysis

Show Processlist: There are mainly the following columns

+----------+------+-----------+------+---------+------+-------+------------------+| Id       | User | Host      | db   | Command | Time | State | Info             |+----------+------+-----------+------+---------+------+-------+------------------+

In addition to one in the optimization of a log, of course, show processlist is also a way, if the MySQL process has been in a high load, show processlist is also a good choice, detailed parameters introduction I will not introduce, I believe there is a lot of information on the Internet.

Analysis on-mysql performance of website bottleneck analysis