Mysql slow query log-Jarvis
----使用log_slow_queries参数打开慢查询，由于该参数已经过时，因此在err日志中将出现提示信息----修改my.cnf文件，添加log_slow_queries参数# vi /opt/mysql5.5/my.cnf# cat /opt/mysql5.5/my.cnf |grep '^log_slow'log_slow_queries = /tmp/mysqlslow.log----清空err日志内容：# cat /dev/null > /tmp/mysql3306.err# service mysql start
Starting MySQL.... ----查看err日志的信息# tail -f /tmp/mysql3306.err
130801 02:26:28 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data1308012:26:28 The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.1308012:26:28 You need to use --log-bin to make --binlog-format work.1308012:26:28 InnoDB: The InnoDB memory heap is disabled1308012:26:28 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
1308012:26:28 InnoDB: Compressed tables use zlib 1.2.3
1308012:26:28 InnoDB: Initializing buffer pool, size = 128.0M
1308012:26:28 InnoDB: Completed initialization of buffer pool
1308012:26:28 InnoDB: highest supported file format is Barracuda.
1308012:26:28InnoDB: Waiting for the background threads to start
1308012:26:30 InnoDB: 1.1.8 started; log sequence number 3069452
1308012:26:30 Event Scheduler: Loaded 0 events
1308012:26:30 /opt/mysql5.5/bin/mysqld: ready for connections.
Version: '5.5.22-log'socket: '/tmp/mysql.sock'port: 3306Source distribution----使用slow_query_log和slow_query_log_file# vi /opt/mysql5.5/my.cnf
# cat /opt/mysql5.5/my.cnf |grep '^slow_query'slow_query_log = 1
slow_query_log_file = /tmp/mysqlslow.log1
# service mysql start
Starting MySQL...
# mysql
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%slow%';
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| log_slow_queries | ON |
| slow_launch_time | 10 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/mysqlslow.log1 |
+---------------------+---------------------+
4 rows in set (0.00 sec)----关于slow_launch_time参数，首先修改一下参数值mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)----进行一下相关操作，查看/tmp/mysqlslow.log1的内容mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t as select * from information_schema.tables;
Query OK, 85 rows affected (0.38 sec)
Records: 85Duplicates: 0Warnings: 0
mysql> insert into t select * from t;
Query OK, 85 rows affected (0.05 sec)
Records: 85Duplicates: 0Warnings: 0
mysql> insert into t select * from t;
Query OK, 170 rows affected (0.03 sec)
Records: 170Duplicates: 0Warnings: 0
mysql> insert into t select * from t;
Query OK, 340 rows affected (0.05 sec)
Records: 340Duplicates: 0Warnings: 0
mysql> insert into t select * from t;
Query OK, 680 rows affected (0.08 sec)
Records: 680Duplicates: 0Warnings: 0
mysql> insert into t select * from t;
Query OK, 1360 rows affected (0.29 sec)
Records: 1360Duplicates: 0Warnings: 0
mysql> insert into t select * from t;
Query OK, 2720 rows affected (1.49 sec)
Records: 2720Duplicates: 0Warnings: 0----在这里已经超过1s了，查看/tmp/mysqlslow.log1# tail -f /tmp/mysqlslow.log1
# Time: 1308012:36:25
# User@Host: root @ localhost []
# Query_time: 2.274219Lock_time: 0.000322 Rows_sent: 0Rows_examined: 5440
use test;
SET timestamp=1375295785;
insert into t select * from t;----log_queries_not_using_indexes参数实验mysql> show variables like '%indexes%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> set log_queries_not_using_indexes = 1;
ERROR 1229 (HY000): Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%indexes%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> desc t;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES| | NULL | |
| VERSION | bigint(21) unsigned | YES| | NULL | |
| ROW_FORMAT | varchar(10) | YES| | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES| | NULL | |
| AVG_ROW_LENGTH| bigint(21) unsigned | YES| | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES| | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES| | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES| | NULL | |
| DATA_FREE | bigint(21) unsigned | YES| | NULL | |
| AUTO_INCREMENT| bigint(21) unsigned | YES| | NULL | |
| CREATE_TIME | datetime | YES| | NULL | |
| UPDATE_TIME | datetime | YES| | NULL | |
| CHECK_TIME | datetime | YES| | NULL | |
| TABLE_COLLATION | varchar(32) | YES| | NULL | |
| CHECKSUM | bigint(21) unsigned | YES| | NULL | |
| CREATE_OPTIONS| varchar(255) | YES| | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.05 sec)----下面的命令是查看索引的mysql> show index from t;
Empty set (0.01 sec)
mysql> select * from t where engine='xxx';
Empty set (0.18 sec)
# Time: 1308012:43:43
# User@Host: root @ localhost []
# Query_time: 0.185773Lock_time: 0.148868 Rows_sent: 0Rows_examined: 5440
SET timestamp=1375296223;
select * from t where engine='xxx';
页:
[1]