Website bottleneck analysis-MYSQL performance analysis, website bottleneck mysql Performance

Website bottleneck analysis-MYSQL performance analysis, website bottleneck mysql Performance

1. Slow query settings and Analysis

Search for slow query parameters

mysql> show variables like 'long%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 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 the slow query parameter (recommended by the author to 1 s)

mysql>  set global long_query_time=1;
mysql> set global slow_query_log='ON';

 

In addition to the above settings, some friends will also set the statements without indexes in the query to on (that is, log_queries_not_using_indexes). You can determine whether to add according to the current requirements.

In this way, you can perform optimization based on the corresponding logs. Of course, the above section is mainly used to optimize mysql statements.

 

2. View and analyze show processlist and show full processlist

Show processlist: mainly includes the following columns:

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

 

In addition to the optimization of a log in No. 1, of course, show processlist is also a method. If the mysql process is always under a high load, show processlist is also a good choice, I will not describe the detailed parameter introduction. I believe there is still a lot of information on the Internet.