Genral log (Common log) and slow log (slow Japanese)

General log:
The geleral log records every query or command received by the server. General log records the query or command, whether it is correct or even contains a syntax error, the record format is {time, ID, command, argument }. It is also because the MySQL server needs to constantly record logs, enabling General log will produce a large amount of system overhead. Therefore, MySQL disables General log by default. You can enable the General log function or change the log storage path by modifying the MySQL global variable.
Note:For MySQL, If You Want To enable slow log and general log, you need to restart. From MySQL 5.1.6, general query log and slow query log support writing to files or database tables, in addition, logs can be dynamically modified at the global level when they are enabled and output.

1. First, check log_output to check whether the log is output to a file or database.
Mysql> show variables like 'Log _ output ';
+ ----------------- + ------- +
| Variable_name | value |
+ ----------------- + ------- +
| Log_output | file |
+ ----------------- + ------- +
1 row in SET (0.00 Sec)
The above results show that the value of log_output is file, which indicates that it is output to the log file. If it is table, it is output to the corresponding log table in the default 'mysql' database, the default engine for this table is CSV.

2. Run the following command to view the default General log configuration of MySQL.
Mysql> show global variables like '% General % ';
+ ------------------ + --------------------------- +
| Variable_name | value |
+ ------------------ + --------------------------- +
| General_log | off |
| General_log_file |/var/lib/MySQL/ubuntu. log |
+ ------------------ + --------------------------- +
2 rows in SET (0.00 Sec)
The value of general_log is off, so general_log is disabled currently. The value of the general_log_file variable is the path of the log file.
3. Run the set global general_log = on command to enable general log.
4. Finally, we can view the corresponding log information from the/var/lib/MySQL/ubuntu. log file.

 

 

Slow log:
The general log Content is relatively simple and does not contain the execution time or other information that can be obtained only after the query is completed. On the contrary, slow log records the content.
1. First, let's take a look at the global variables related to slow logs.
Mysql> show global variables like '% slow % ';
+ --------------------- + -------------------------------- +
| Variable_name | value |
+ --------------------- + -------------------------------- +
| Log_slow_queries | on |
| Slow_launch_time | 2 |
| Slow_query_log | on |
| Slow_query_log_file |/var/lib/MySQL/ubuntu-slow.log |
+ --------------------- + -------------------------------- +
4 rows in SET (0.00 Sec)
The author's MySQL has enabled the slow log option. The value of the slow_launch_time variable indicates capturing all queries whose execution time exceeds 2 seconds. Slow log can record queries that do not use indexes, and it can also record slow management commands.
When log_queries_not_using_indexes is enabled, the slow log will be recorded if no index is used.
Mysql> show global variables like '% not_using % ';
+ ------------------------------- + ------- +
| Variable_name | value |
+ ------------------------------- + ------- +
| Log_queries_not_using_indexes | off |
+ ------------------------------- + ------- +
1 row in SET (0.00 Sec)

 

The slow log format is:
# Time: 111108 19:38:00
# [Email protected]: Root [root] @ localhost []
# Query_time: 15.268541 lock_time: 0.000237 rows_sent: 1 rows_examined: 102
Use mytest;
Set timestamp = 1320752280;
Select count (A. B) from mytable A, mytable B, mytable C, mytable D;

 

Slow log does provide a lot of useful information, but it does not mean that the query will always be slow. If the same query appears multiple times in the slow log, it does need to be optimized. However, if it appears only once or twice, it may be caused by other objective reasons, for example, some locks, I/O Disk Physical Problems, network problems, and so on.
The slow_launch_time of slow logs is measured in seconds. You can use a third-party patch on the Internet to change the slow_launch_time to a millisecond level for more detailed log record and analysis. However, you need to re-compile MySQL.

 

We usually look for the following information in the log: long query, query with a large impact, and new query. This may require us to write scripts or use some third-party tools for log analysis.
The general log system has a high overhead and is generally not recommended to be enabled.

 

Genral log (Common log) and slow log (slow Japanese)