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

The following is about what is the MySQL general query and slow query log. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on what is the MySQL general query and slow query log.

The logs in MySQL include error log, binary log, general query log, slow query log, and so on. This paper mainly introduces two commonly used functions: general query log and slow query log.

1) General query log: records the established client connections and executed statements.

2) slow query log: record all queries whose execution time exceeds long_query_time seconds or queries that do not use indexes

(1) General query log

When learning generic log queries, you need to know the commands commonly used in two databases:

1) show variables like'% version%';mysql > show variables like'% version%' +-+ | Variable_name | Value | +-+- -+ | innodb_version | 5.6.37 | | protocol_version | 10 | | slave_type_conversions | version | 5.6.37-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86 sec 64 | | version_compile_os | Linux | +-+-- + 7 rows in set (0.00 sec)

The above command displays something related to the version number in the current database.

1) show variables like'% general%';mysql > show variables like'% general%' +-+-+ | Variable_name | Value | +-+- -+ | general_log | OFF | | general_log_file | / var/lib/mysql/nginx-test.log | +-- + 2 rows in set (0.00 sec) can be viewed Whether the current generic log query is enabled. If the value of general_log is ON, it is enabled, and if it is OFF, it is off (off by default). 1) show variables like'% log_output%';mysql > show variables like'% log_output%';+-+-+ | Variable_name | Value | +-+ | log_output | FILE | +-+-+ 1 row in set (0.00 sec)

View the format of the current slow query log output, either FILE (hostname.log stored in the data file of the database) or TABLE (mysql.general_log stored in the database)

Question: how to open the MySQL generic query log and how to set the generic log output format to be output?

Enable general log query: set global general_log=on; turns off general log query: set global general_log=off; sets general log output to table mode: set global log_output='TABLE'; sets general log output to file mode: set global log_output='FILE'; sets general log output to table and file mode: set global log_output='FILE,TABLE'

(note: the above command only takes effect for the current time. When the MySQL restart fails, you need to configure my.cnf if you want to take effect permanently.)

The effect of the log output is as follows:

The structure of the log table recorded to mysql.general_ is as follows:

Mysql > desc general_log +-+-+ | Field | Type | Null | Key | Default | | Extra | +-+-+ | event | _ time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | user_host | mediumtext | NO | | NULL | | thread_id | bigint (21) unsigned | NO | | NULL | | server_id | int (10) unsigned | NO | | NULL | | command_type | varchar (64) | NO | | NULL | | argument | mediumtext | NO | | NULL | | +-| -+-- + 6 rows in set (0.00 sec)

The my.cnf file is configured as follows:

General_log=1 # 1 means to enable general log query. A value of 0 means to disable general log query. Log_output=FILE,TABLE# sets the output format of general log to file and table (2) slow 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 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). 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.

Generally speaking, a slow query occurs in a large table (for example, there are millions of data in a table), and the fields of the query conditions are not indexed. In this case, the fields to match the query conditions are scanned all over the table, and it takes time to check the long_query_time.

Is a slow query statement.

Question: how to check whether the current slow log is open?

Enter the command in MySQL:

Show variables like'% quer%'

Mysql > show variables like'% quer%' +-- +-+ | Variable_name | Value | +- -+-+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | have_query _ cache | YES | | log_queries_not_using_indexes | ON | | log_throttle_queries_not_using_indexes | 0 | long_query_time | 10.000000 | | query_alloc _ block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache _ type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | ON | | slow _ query_log_file | / var/log/mysql/mysql_slow.log | +-- +-- + 15 rows in set (0.00 sec)

Mainly master the following parameters:

(1) the value of slow_query_log is ON to enable slow log, and OFF is to disable slow log.

(2) the value of 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 slow query is file, and 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 of a statement exceeds this threshold, it is a slow query statement, and the default value is 10 seconds.

(4) if the value of log_queries_not_using_indexes 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 temporarily enabled during performance tuning.

Question: set the output log format of MySQL slow query to file or table, or both?

Through the command: show variables like'% log_output%'

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

You can see the format of the output by the value of log_output, and the value above is FILE,TABLE. Of course, we can also format the output as text, or record both the text and the database table with the following commands:

# slow log output to table (i.e. mysql.slow_log) set globallog_output='TABLE';# slow log output only to text (i.e. files specified by slow_query_log_file) setglobal log_output='FILE';# slow log output to both text and table setglobal log_output='FILE,TABLE'

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:

Mysql > mysql > select * from mysql.slow_log limit 1 +- -+- -+-+ | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | | thread_id | +-+ -+- -+-+ | 2018-02-07 11:16:55 | root [root] @ [121.196.203.51] | 00:00:00 | 00:00:00 | 13 | 40 | jp_core_db | 0 | 0 | select pd.lastAuction from Product pd where pd .status ='O' and pd.auctionStatus ='A' | 1621 | +-+-- -+- -+-+ 1 row in set (0.00 sec)

The slow query log is recorded in the mysql_slow.log file in the following format:

# Time: 180118 14 root 58 root User@Host: root [root] @ localhost [] Id: 15 percent Query_time: 0.000270 Lock_time: 0.000109 Rows_sent: 0 Rows_examined: 6SET timestamp=1516258717;delete from user where User='app'

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

Question: how to query the current number of slow query statements?

There is a variable in MySQL that specifically records the number of current slow query statements:

Enter the command: show global status like'% slow%'

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

(note: for all the above commands, if the parameters are set through the shell of MySQL, if you restart MySQL, all the set parameters will become invalid. If you want to take effect permanently, you need to write the configuration parameters into the my.cnf file.

Supplementary knowledge: how to use MySQL's own slow query log analysis tool mysqldumpslow to analyze logs?

Mysqldumpslow-s c-t 10 slow-query.log

The specific parameters are set as follows:

-s indicates how it is sorted. C, t, l, r are sorted by the number of records, time, query time, and the number of records returned. Ac, at, al, and ar indicate the corresponding flashbacks.

-t means top, followed by data indicating how many previous entries are returned.

Regular expression matching can be written after-g, which is case-insensitive.

[root@nginx-test / var/log/mysql] # mysqldumpslow-s c-T2 / var/log/mysql/mysql_slow.log Reading mysql slow query log from / var/log/mysql/mysql_slow.logCount: 125448 Time=0.00s (131s) Lock=0.00s (3s) Rows=2.2 (272835), 2users@2hosts select productauc0_.productAuctionId as productA1_12_, productauc0_.auctionIndex as auctionI2_12_, productauc0_.bidCoins as bidCoins3_12_, productauc0_.bidPrice as bidPrice4_12_, productauc0_.bidStep as bidStep5_12_ Productauc0_.bidTime as bidTime6_12_, productauc0_.bidder as bidder7_12_, productauc0_.buyFlag as buyFlag8_12_, productauc0_.categoryCode as category9_12_, productauc0_.createTime as createT10_12_, productauc0_.currentAuctionDetailId as current11_12_, productauc0_.currentBidPrice as current12_12_, productauc0_.currentBidTime as current13_12_, productauc0_.currentBidder as current14_12_, productauc0_.effectCoin as effectC15_12_, productauc0_.effetcPoint as effetcP16_12_, productauc0_.endTime as endTime17_12_, productauc0_.newUserFlag as newUser18_12_ Productauc0_.productCode as product19_12_, productauc0_.productCost as product20_12_, productauc0_.productName as product21_12_, productauc0_.productPrice as product22_12_, productauc0_.refundRate as refundR23_12_, productauc0_.startPrice as startPr24_12_, productauc0_.startTime as startTi25_12_, productauc0_.status as status26_12_, productauc0_.updateTime as updateT27_12_ from ProductAuction productauc0_ where productauc0_.status='S'Count: 66216 Time=0.00s (127s) Lock=0.00s (2s) Rows=1.7 (115074) Root [root] @ [121.196.203.51] select productauc0_.productAuctionId as productA1_12_, productauc0_.auctionIndex as auctionI2_12_, productauc0_.bidCoins as bidCoins3_12_, productauc0_.bidPrice as bidPrice4_12_, productauc0_.bidStep as bidStep5_12_, productauc0_.bidTime as bidTime6_12_, productauc0_.bidder as bidder7_12_, productauc0_.buyFlag as buyFlag8_12_, productauc0_.categoryCode as category9_12_, productauc0_.createTime as createT10_12_, productauc0_.currentAuctionDetailId as current11_12_ Productauc0_.currentBidPrice as current12_12_, productauc0_.currentBidTime as current13_12_, productauc0_.currentBidder as current14_12_, productauc0_.effectCoin as effectC15_12_, productauc0_.effetcPoint as effetcP16_12_, productauc0_.endTime as endTime17_12_, productauc0_.firstBidTime as firstBi18_12_, productauc0_.newUserFlag as newUser19_12_, productauc0_.noviceReturnFlag as noviceR20_12_, productauc0_.productCode as product21_12_, productauc0_.productCost as product22_12_, productauc0_.productName as product23_12_, productauc0_.productPrice as product24_12_ Productauc0_.refundRate as refundR25_12_, productauc0_.startPrice as startPr26_12_, productauc0_.startTime as startTi27_12_, productauc0_.status as status28_12_, productauc0_.updateTime as updateT29_12_ from ProductAuction productauc0_ where productauc0_.status='S'

The parameters in the above are as follows:

There are 125448 Count:125448 statements; the longest execution time of Time=0.00s (131s) is 0.00s, and the cumulative total time of 131s locketers is 0.0s (3s) the longest waiting time for locks is 0s, and the cumulative waiting time of Rowsboxes 2.2 (272835) is 2.2 rows sent to the client and the cumulative function sent to the client is 272835

Http://blog.csdn.net/a600423444/article/details/6854289

(note: the mysqldumpslow script is written in Perl, the specific usage of mysqldumpslow will be discussed later.)

Question: 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, we can execute the following statement:

Select sleep (1)

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

Is there anything you don't understand about the above MySQL general query and slow query log? Or if you want to know more about it, you can continue to follow our industry information section.

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.