Shulou(Shulou.com)06/01 Report--

Note content: MySQL slow query log

Note date: 2017-12-10

The composition of MySQL log file system general query log slow query log

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, usually including error log files, binary logs, generic logs, slow query logs, and so on. These logs can help us locate internal events in mysqld, database performance failures, record the change history of data, users restore the database, and so on.

The composition of the MySQL journal file system

1. Error log: records problems that occur when starting, running, or stopping mysqld.

two。 General query log: records established client connections and executed statements.

3. Update log: a statement that records change data. This log is no longer in use in MySQL 5.1.

4. Binary log: a statement that records all change data. It is also used for master-slave replication.

5. Slow log: records all queries that take more than long_query_time seconds to execute or that do not use indexes.

6.Innodb log: innodb redo log

7. Relay log: the log of the data to be updated obtained from the library from the main library.

By default, all logs are created in the mysqld data directory. You can force mysqld to close and reopen the log file (or in some cases switch to a new log) by refreshing the log. When you execute a FLUSH LOGS statement or execute mysqladmin flush-logs or mysqladmin refresh, the log is aging. In the case of MySQL replication, more log files, called replacement logs, are maintained from the replication server.

General query log

When learning general log queries, you need to know several common commands in the database:

1.show variables like'% version%'

This command displays the information related to the version number in the current database. Example:

Mysql > show variables like'% version%' +-+-+ | Variable_name | Value | +-+-+ | innodb_version | 5.7.14 | | protocol_version | 10 | | slave_type_conversions | version | 10.2.6-MariaDB | | version_comment | MariaDB Server | | version_compile_machine | x86room64 | | version_compile_os | Linux | | version_malloc_library | system | | version_ssl_library | YaSSL 2.4.2 | | wsrep_patch_version | | | wsrep_25.19 | +-+-+ 10 rows in set (0.01sec) mysql > |

The following command is used to check whether the current generic log query is enabled. If the value of general_log is ON, it is on, and if it is OFF, it is off (off by default).

2.show variables like'% general%'

Example:

Mysql > show variables like'% general%' +-+-+ | Variable_name | Value | +-+-+ | general_log | OFF | | general_log_file | server.log | +-- -+ 2 rows in set (0.00 sec) mysql >

The following command is used to view the format of the current generic query log output. The value of log_output can be FILE (hostname.log stored in the data file of the database) or TABLE (mysql.general_log stored in the database).

3.show variables like'% log_output%'

Example:

Mysql > show variables like'% log_output%';+-+-+ | Variable_name | Value | +-+-+ | log_output | FILE | +-+-+ 1 row in set (0.00 sec) mysql >

How to turn on MySQL generic query logs and how to record them?

Enable general log query: set global general_log=on

Turn off generic log query: set global general_log=off

Set up general logging to database table: set globallog_output='TABLE'

Set generic logging to a local file: set globallog_output='FILE'

Set up generic logging to database tables and local files: set global log_output='FILE,TABLE'

Example:

Mysql > set global general_log=on;Query OK, 0 rows affected (0.01 sec) mysql > set global log_output='FILE,TABLE';Query OK, 0 rows affected (0.03 sec) mysql > select * from mysql.general_log + -+ | event_time | user_host | thread_id | server_id | command_type | argument | +-- -+-- + | 2017-12-10 17 localhost 47purse 35.177238 | root [root] @ localhost [] | 189 | 1 | Query | select * from mysql.general_log | | 2017- 12-10 17 localhost 47 localhost 52.608628 | root [root] @ localhost [] | 189 | 1 | Query | select * from mysql.general_log | 2017-12-10 17 localhost 47 localhost 55.138903 | root [root] @ localhost [] | 1 | Query | select * from mysql.general_log | +-- -- + 3 rows in set (0.00 sec) mysql >

The data recorded in the mysql.general_ log table is as follows:

By default, the name of the generic query log file recorded in the local file is prefixed with the hostname and suffix .log, which can be found using the find command. Example:

[root@server ~] # find /-name "server.log" / data/mariadb/server.log [root@server ~] # tail / data/mariadb/server.logTime Id Command Argument171210 17:46:39 189 Query set global log_output='FILE TABLE'171210 17:47:35 189 Query select * from mysql.general_log171210 17:47:52 189 Query select * from mysql.general_log171210 17:47:55 189 Query select * from mysql.general_log171210 17:50:57 189 Quit 171210 17:52:01 190 Connect root@localhost as anonymous on 190 Query select @ @ version_comment limit 1171210 17:52:09 190 Query select * from mysql.general_log171210 17:52:59 190 Quit [root@server ~] #

Note: the above command is only temporary and will expire when MySQL is restarted. If you want to take effect permanently, you need to configure the my.cnf file.

The my.cnf file needs to be configured as follows:

General_log=1 # 1 means to enable general log query, and a value of 0 means to turn off general log query log_output=FILE,TABLE # set the output format of general log to files and tables

If the generic log query is not enabled, the general_log table is empty:

Mysql > select * from mysql.general_log;Empty set (0.00 sec) mysql >

Slow query log

   MySQL's slow log is a kind of log record provided by MySQL, which is used to record statements whose response time in MySQL exceeds the threshold. Specifically, SQL statements whose running time exceeds the long_query_ time value will be recorded in the slow log (logs can be written to files or database tables. If high performance requirements are required, it is recommended to write files). Therefore, the slow query log records the relevant SQL statements that affect the performance of the database in the mysql server. Through the analysis of these special SQL statements, it is improved to improve the performance of the database. By default, slow log is not enabled in MySQL database, and the default value of long_query_time is 10 (that is, 10 seconds, usually set to 1 second), that is, statements running for more than 10 seconds are slow query statements.

In   , slow query occurs in a table with a large database (for example, a table contains millions of data), and the fields of the query condition are not indexed. At this time, the fields to match the query condition are scanned throughout the table, and the query time exceeds the threshold defined by long_query_time (the default value). These slow query statements will be recorded in the slow query log.

Use the following command to view whether the current slow query log is enabled:

Show variables like'% quer%'

Example:

Mysql > show variables like'% quer%' +-- +-+ | Variable_name | Value | +-- -+ | expensive_subquery_limit | 100 | ft_query_expansion_limit | 20 | have_query_cache | YES | | log_queries_not_using_indexes | OFF | | long_query_time | 10.000000 | query_alloc_block_size | 16384 | | query_cache_limit | 1048576 | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_strip_comments | OFF | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | | | 24576 | | slow_query_log | OFF | | slow_query_log_file | server-slow.log | | wsrep_sst_donor_rejects_queries | OFF | +-- -+ 16 rows in set (0.00 sec) mysql >

You need to pay attention to the following parameters:

The value of    1.slow_query_log: ON: enable slow log, and OFF: disable slow log.

The value of    2.slow_query_log_file: is the recorded slow log to the file (Note: the default name is hostname.log. If the slow log is written in the specified file, you need to specify that the output log format of the slow query is the file. The related command is: show variables like'% log_output%'; to check the output format).

   3.long_query_time: specifies a slow query threshold, that is, if the execution time exceeds this threshold, it is a slow query statement, and the default value is 10 seconds.

   4.log_queries_not_using_indexes: if the value is set to ON, all queries that do not take advantage of the index will be recorded (Note: if only log_queries_not_using_indexes is set to ON and slow_query_log is set to OFF, this setting will not take effect, that is, the setting takes effect if the value of slow_query_log is set to ON). Generally, it will be enabled temporarily during performance tuning.

   5.min_examined_row_limit: query check returns SQL that is less than the row specified in this parameter is not recorded in the slow query log

   6.log_slow_queries: specifies whether to enable slow log (this parameter will be replaced by slow_query_log for compatibility reservation)

Like general query logs, slow logs are recorded by using the show variables like'% log_output%'; statement to view the logs:

Mysql > show variables like'% log_output%';+-+-+ | Variable_name | Value | +-+-+ | log_output | FILE,TABLE | +-+-+ 1 row in set (0.00 sec) mysql >

The logging is set up in the same way as before:

Set up general logging to database table: set globallog_output='TABLE'

Set generic logging to a local file: set globallog_output='FILE'

Set up generic logging to database tables and local files: set global log_output='FILE,TABLE'

Enable slow query log:

Mysql > set global slow_query_log=on;Query OK, 0 rows affected (0.00 sec) mysql > set global long_query_time=1; # sets the threshold to 1 second Query OK, 0 rows affected (0 sec) mysql > set session long_query_time=1; # sets the threshold for session level to 1 second Query OK, 0 rows affected (0 sec) mysql > show variables like'% long_query_time%' # View threshold Default is 10 seconds +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 1.000000 | +-+-+ 1 row in set (1.000000 sec) Mysql > show variables like'% slow_query_log%' # check the slow log status. ON is enabled. Default is OFF+-+-+ | Variable_name | Value | +-+-+ | slow_query_log | ON | | slow_query_log_ File | server-slow.log | +-+-+ 2 rows in set (0.01sec) mysql > show global status like'% slow%' # View the number of records in slow query +-+-+ | Variable_name | Value | +-+-+ | Slow_launch_threads | 0 | Slow_queries | 0 | +- -+-+ 2 rows in set (0.06 sec) mysql > show variables like 'log_queries_not_using_indexes' # View log_queries_not_using_indexes status +-- +-+ | Variable_name | Value | +-- +-+ | log_queries_not_using_ Indexes | OFF | +-- +-+ 1 row in set (0.00 sec) mysql >

If it still appears as the default value after the modification, just log in to mysql again, or add the global keyword after show.

About the data in the table of the slow query log and the data format analysis in the text:

In the myql.slow_ log table of slow query, the format is as follows:

Query statement:

Select * from mysql.slow_log

At this point, take a look at the number of records in the slow query:

Mysql > show global status like'% slow%' +-+-+ | Variable_name | Value | +-+-+ | Slow_launch_threads | 0 | Slow_queries | 374 | +-+-+ 2 rows in set (0.00 sec) mysql >

Both the slow query log and the general query log use the same local file:

[root@server ~] # find /-name 'server.log'/data/mariadb/server.log [root@server ~] # tail / data/mariadb/server.log 197 Field List INNODB_SYS_INDEXES 197 Field List INNODB_SYS_VIRTUAL 197 Field List INNODB_MUTEXES 197 Field List INNODB_SYS_SEMAPHORE_WAITS 171210 21:43:19 197 Query show tables171210 21:43:36 197 Query select * from ALL_ PLUGINS171210 21:44:09 197Query select * from myql.slow_log171210 21:44:15 197Query select * from mysql.slow_log171210 21:44:23 197Query select * from mysql.slow_log171210 22:00:12 197Quit [root@server ~] #

As you can see, both the table and the file record information such as which sql statement caused the slow query (sql_text), the query time (query_time), the table lock time (Lock_time), and the number of rows scanned (rows_examined).

How to generate slow query statements manually

In fact, in the learning process, how to know that the set slow query is effective? Quite simply, we can manually generate a slow query statement. For example, if the value of our slow query log_query_time is set to 1, then execute the following statement:

Selectsleep (1)

This statement is a slow query statement, and then you can check whether it exists in the corresponding log output file or table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.