Logs in MySQL include: error logs, binary logs, generic query logs, slow query logs, etc. Here are two commonly used functions: general query log and slow query log.
1) Slow query logs:Log all queries that are executed for more than long_query_time seconds or that do not use indexes
2) General query log:Record the client connections established and executed statements.
1: About MySQL slow query:
MySQL's slow query log is a kind of logging provided by MySQL, which is used to record statements that the response time exceeds the threshold in MySQL, specifically referring to SQL with a running time of more than long_query_time value, which will be recorded in the slow query log (logs can be written to files or database tables, if the performance requirements are high, it is recommended to write files). By default, the MySQL database does not enable slow query logs, and the default value of the long_query_time is 10 (i.e., 10 seconds, usually set to 1 second), that is, statements that run for more than 10 seconds are slow query statements.
Generally speaking, slow queries occur in large tables (for example, a table has millions of data), and the fields of the query conditions are not indexed, at this time, the fields to match the query conditions will be scanned in the whole table, which takes time to check the long_query_time.
It is a slow query statement.
Question: How can I check the current opening status of the slow query log?
Enter the command in MySQL:
The following parameters are mainly mastered:
(1) The value of slow_query_log is ON to turn on the slow query log, and OFF is to turn off the slow query log.
(2) The value of slow_query_log_file is the recorded slow query log to the file (note: the default name is hostname.log, whether the slow query log is written to the specified file, you need to specify the output log format of the slow query as a file, and the related command is: show variables like '%log_output%'; to see the format of the output).
(3) long_query_time specifies the threshold for slow query, 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 utilize the index will be recorded (Note: If the log_queries_not_using_indexes is only set to ON and the slow_query_log is set to OFF, the setting will not take effect at this time, that is, the setting will take effect if the value of the slow_query_log is set to ON) , which is generally temporarily turned on when performance is tuned.
2. General query log
If the value of general_log is ON is on, forOFF is closed(Off by default).
Check the format of the current slow query log output, which can be FILE (a hostname.log stored in a data file in a digital database) or a TABLE (mysql.general_log stored in a database)
(Note: The above command is only effective for the current time, when MySQL restarts are invalid, if you want it to take effect permanently, you need to configure my.cnf)
The rendering of the log output is as follows:
my.cnf file is configured as follows:
general_log=1 #为1表示开启通用日志查询, a value of 0 indicates that the generic log query is closed
log_output=FILE,TABLE# Sets the output format of the general log to file and table
|
Posted on 3/19/2019 4:31:34 PM
|
|
|
|
