MySQL DBA system Learning (9) Slow query log slow lookup logging function

Slow query log (does not include time to acquire locks)

1, open the Slow query log function

The parameters for opening the slow query log feature in previous versions are--log_slow_queries specified in the my.cnf file, but are now specified in the new version with Parameters--slow_query_log and--slow_query_log_file

Slow_query_log=1

Slow_query_log_file=/tmp/mysqlslow.log

Restart MySQL

Mysql> Show variables like "%slow%";

+---------------------+--------------------+

| variable_name | Value |

+---------------------+--------------------+

| log_slow_queries | On |

| Slow_launch_time | 2 |

| Slow_query_log | On |

| Slow_query_log_file | /tmp/mysqlslow.log |

+---------------------+--------------------+

4 rows in Set (0.00 sec)

2, slow query-related parameters long_query_time

When the query exceeds the time specified by Long_query_time, it is recorded in the slow query log file, which defaults to 10 seconds.

Mysql> Show variables like "%long%";

+--------------------+-----------+

| variable_name | Value |

+--------------------+-----------+

| Long_query_time | 10.000000 |

Let's take a short time to try and see what happens.

Mysql> set session long_query_time=1; Tune to 1 seconds.

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like "%long%";

+--------------------+----------+

| variable_name | Value |

+--------------------+----------+

| Long_query_time | 1.000000 |

| Max_long_data_size | 1048576 |

+--------------------+----------+

2 rows in Set (0.00 sec)

mysql> use test;

Database changed

Mysql> CREATE TABLE T as select * from Information_schema.tables;

Query OK, rows affected (0.12 sec)

records:53 duplicates:0 warnings:0

mysql> INSERT INTO T-select * from T;

Query OK, 3392 rows affected (0.11 sec)

records:3392 duplicates:0 warnings:0

This time it's been 1 seconds, so we need to log the SQL statement.

mysql> INSERT INTO T-select * from T;

^[[aquery OK, 6784 rows affected (1.15 sec)

records:6784 duplicates:0 warnings:0

mysql> system More/tmp/mysqlslow.log

/usr/local/mysql/libexec/mysqld, Version:5.1.70-log (Source distribution). Star

Ted with:

TCP port:3306 Unix Socket:/tmp/mysql.sock

Time Id Command Argument

# time:130903 18:46:28

# User@host:root[root] @ localhost []

# query_time:1.150157 lock_time:0.000205 rows_sent:0 rows_examined:13568

Use test;

SET timestamp=1378205188;

INSERT INTO T-select * from T;