Processing MySQL slow SQL

 
When the slow_query_log value is on (the default value is off, the default value is 10 s, and accurate to microseconds), the default value (when the log_output value is fifl) will record this slow query: the slow_query_log_file value is specified in the file.
Mysql> select @ Global. log_output;
+ --------------------- +
| @ Global. log_output |
+ --------------------- +
| File |
+ --------------------- +
1 row in SET (0.00 Sec)

Note that mysql5.1 supports storing slow query log records in mysq. slow_log, but you need to set the log_output variable value to table:

Mysql> set @ Global. log_output = 'table ';
Error 2006 (hy000): MySQL server has gone away
No connection. Trying to reconnect...
Connection ID: 6
Current Database: *** none ***

Query OK, 0 rows affected (0.00 Sec)
Before enabling slow SQL:
Mysql> show variables like '% slow % ';
+ --------------------- + -------------------------------------- +
| Variable_name | value |
+ --------------------- + -------------------------------------- +
| Log_slow_queries | off |
| Slow_launch_time | 2 |
| Slow_query_log | off |
| Slow_query_log_files |/var/MySQL/data/localhost-slow.log |
+ --------------------- + -------------------------------------- +
4 rows in SET (0.00 Sec)
Enable slow SQL
Mysql> set @ Global. slow_query_log = on;
Query OK, 0 rows affected (0.00 Sec)

Mysql> System CAT/var/MySQL/data/localhost-slow.log
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
Mysql>
Go to the/var/MySQL/data/directory and view
Localhost-slow.log mysqldumpslow
[Root @ localhost data] # mysqldumpslow localhost-slow.log

Reading MySQL slow query log from localhost-slow.log
Count: 1 time = 0.00 S (0 s) Lock = 0.00 S (0 s) rows = 0.0 (0), 0users @ 0 hosts

Generally, General log is not enabled for performance purposes. Slow log can locate SQL statements with performance problems, while general log records all SQL statements.
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.
 
Mysql> select version ();
+ ------------ +
| Version () |
+ ------------ +
| 5.5.15-log |
+ ------------ +
1 row in SET (0.00 Sec)
 
Set the log output mode to a file (if log_output = table is set, the log results will be recorded in the table named gengera_log, and the default engine of this table is CSV ):
 
Mysql> set global log_output = file;
Query OK, 0 rows affected (0.00 Sec)
 
Set the log file path for general log:
 
Mysql> set global general_log_file = '/tmp/General. log ';
Query OK, 0 rows affected (0.00 Sec)
 
Enable General log:
 
Mysql> set global general_log = on;
Query OK, 0 rows affected (0.00 Sec)
 
After a while, close the General log:
 
Mysql> set global general_log = off;
Query OK, 0 rows affected (0.01 Sec)
 
Check the tmp/General. log information. You can see which SQL queries, updates, deletes, and inserts frequently. For example, if some tables do not change frequently and the query volume is large, the data can be fully cached. If tables with low requirements on Master/Slave latency, the data can be read to the slave database.