To view the slow query log for MySQL, first turn on the slow query record for MySQL.
Check that the MySQL slow query record is turned on,
Mysql> Show variables like ' slow_query% '; +---------------------+--------------------------------+| variable_name | Value |+---------------------+--------------------------------+| Slow_query_log | On | | Slow_query_log_file | /var/log/mysql/mysql-slow.log |+---------------------+--------------------------------+
Slow_query_log on means that slow query logging is turned on slow_query_log_file indicates the location of the slow query log file record
Mysql> Show variables like ' long_query_time '; +-----------------+----------+| variable_name | Value |+-----------------+----------+| Long_query_time | 2.000000 |+-----------------+----------+
Indicates that a query over 2 seconds is a slow query and will be logged into the slow query log.
How to analyze slow query log?
MySQL comes with a tool for parsing slow query logs mysqldumpslow
Mysqldumpslow–s c–t Slow-query.log
The specific parameters are set as follows:
-S means the sort by how, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;
-T is the meaning of top, followed by the data to return the first number of bars;
-G can write regular expression matching, case insensitive.
The count:2978 statement appeared 2,978 times;
Time=59.72s (177857s) The maximum time to execute 59.72s, the total time spent 177857s;
lock=0.0s (2s) Wait for the maximum time to lock 0s, the cumulative waiting lock time is 2s;
rows=1.0 (2857) The maximum number of rows sent to the client is 1.0, and the cumulative function sent to the client is 2857;
###############################################################################
Included operation commands for MySQL connection number
1.mysql> Show status like ' threads% ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| threads_cached | 58 |
| threads_connected | 57 | # # #这个数值指的是打开的连接数
| threads_created | 3676 |
| threads_running | 4 | # # #这个数值指的是激活的连接数, this value is generally much lower than the connected value
+-------------------+-------+
The threads_connected is the same as the show processlist result, indicating the current number of connections. To be exact, threads_running is the current number of concurrent
2. This is the maximum number of connections that are currently set for the query database
Mysql> Show variables like '%max_connections% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
The maximum number of connections to the database can be set in/ETC/MY.CNF
[Mysqld]
max_connections = 1000
The 3.max_connections parameter can be used to control the maximum number of connections for a database:
Mysql> Show variables like '%connect% ';
+--------------------------+-------------------+
| variable_name | Value |
+--------------------------+-------------------+
| character_set_connection | Latin1 |
| collation_connection | Latin1_swedish_ci |
| Connect_timeout | 10 |
| Init_connect | |
| max_connect_errors | 10 |
| max_connections | 4000 |
| max_user_connections | 0 |
+--------------------------+-------------------+
MySQL Slow query analysis