MySQL Slow Query Log

Start MySQL Server:

/etc/init.d/mysqld start

Syntax for /etc/my.cnf in Red Hat Family Machine :

[mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

  • The 2nd line under [mysqld] turns on slow query log and logs all slow queries to log-slow-queries.log in the MySQL data directory.
  • The 1st line indicates that any queries that took more than 1 seconds to execute need to be logged.
  • The last line tells MySQL to log *any* queries that do not use indexes regardless of the setting in the second line.

You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

mysqldumpslow

http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html

shell> mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t1 select * from t1.