Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of slow query log in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Due to the slow query of the mysql managed by myself recently, in order to query the sql statement of slow query, the slow query log of mysql is started. There are two ways to start slow query:

1. Log in to mysql directly and change the mysql parameters. (note: administrator permission is required to log in to the account)

Mysql > set global log_slow_queries=on

Mysql > set global slow_query_log_file=/data/mysql.slow.log

Mysql > set global long_query_time=1

If this is the case, it will not take effect after restarting the database.

2. Directly change the my.conf

[mysqld]

Log-slow-queries = / data/mysql.slow.log

Long_query_time = 1

I used the second method this time, but after restarting mysql, I found that the slow query log did not take effect. I queried the mysql log and found that the log reported the following error:

110907 18:28:04 InnoDB: Started; log sequence number 0 4245951191

/ usr/libexec/mysqld: File'/ data/mysql.slow.log' not found (Errcode: 13)

110907 18:28:04 [ERROR] Could not use / data/mysql.slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

This situation can generally be solved by doing the following:

Touch / data/mysql.slow.log

Chown mysql.mysql / data/mysql.slow.log

Chmod Omurr / data/mysql.slow.log

But still not, once again a careful analysis of the log found that the main reason / data/mysql.slow.log file can not be found, but in fact / data/mysql.slow.log file exists; there is no way to suddenly find the reason, first change the slow query file path to / var/log/mysql-slow.log to try, this is it.

110907 18:39:57 mysqld_safe mysqld from pid file / var/run/mysqld/mysqld.pid ended

110907 18:39:57 mysqld_safe Starting mysqld daemon with databases from / var/lib/mysql

110907 18:39:57 [Warning]'--log_slow_queries' is deprecated and will be removed in a future release. Please use'--slow_query_log'/'--slow_query_log_file'' instead.

110907 18:39:57 InnoDB: Started; log sequence number 0 4245951191

110907 18:39:57 [Note] Event Scheduler: Loaded 0 events

110907 18:39:57 [Note] / usr/libexec/mysqld: ready for connections.

Version: '5.1.52 socket:' / var/lib/mysql/mysql.sock' port: 3306 Source distribution

[root@localhost log] # ll | grep mysql

-rw-r-. 1 mysql mysql 512181 Sep 7 18:39 mysqld.log

-rw-r--r--. 1 mysql mysql 422 Sep 7 18:41 mysql-slow.log

[root@localhost log] # cat mysql-slow.log

/ usr/libexec/mysqld, Version: 5.1.52-log (Source distribution). Started with:

Tcp port: 0 Unix socket: / var/lib/mysql/mysql.sock

Time Id Command Argument

# Time: 110907 18:41:29

#: root [root] @ localhost []

# Query_time: 1.512086 Lock_time: 0.000001 Rows_sent: 26 Rows_examined: 2443129

Use idmanage

SET timestamp=1315392089

Select type,count (*) from client where status=1 group by type

After my own analysis, the reason should be: it does not seem to support writing data across the hard disk.

My actual situation is like this, I used to run out of space, and then I hung up a hard drive and went in, and the path of the new hard drive mount is / data.

[root@localhost log] # df-h

Filesystem Size Used Avail Use% Mounted on

/ dev/mapper/VolGroup-lv_root

7.0g 6.6g 12m 100% /

Tmpfs 250m 0 250m 0% / dev/shm

/ dev/sda1 485m 29M 432m 7% / boot

/ dev/mapper/VolGroup-lv_file

485m 11m 449m 3% / file

/ dev/sdb1 9.9G 8.1G 1.3G 87% / data

This is the end of this article on "sample Analysis of slow query log in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.