Menu
My MySQL version details are
How do I enable the MySQL slow query log?
307
The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.
By default, the slow query log file is located at /var/lib/mysql/hostname-slow. log. We can also set up another location as shown in listing 03 using the slow_query_log_file parameter. We can also indicate to log queries not using indexes, as shown in the listing 04.
When you are done troubleshooting, disable the slow query log. To do this, run the mysql program again, and then type the following command: SET GLOBAL slow_query_log = 'OFF'; You should only enable the slow query log for as long as it is necessary to troubleshoot performance issues.
MySQL allows logging slow queries to either a log file or a table, with a configured query duration threshold. Parsing the slow log with tools such as EverSQL Query Optimizer will allow you to quickly locate the most common and slowest SQL queries in the database.
By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using log_slow_admin_statements and log_queries_not_using_indexes , as described later. By default, the slow query log is disabled.
Since 5.6.1, log_sloq_queries is replaced by slow_query_log - dev.mysql.com/doc/refman/5.6/en/…
Version 5.1.6 and above:
1. Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';
2. Enable any other desired options. Here are some common examples:
Log details for queries expected to retrieve all rows instead of using an index:
set global log_queries_not_using_indexes = 'ON' Set the path to the slow query log:
set global slow_query_log_file ='/var/log/mysql/slow-query.log'; Set the amount of time a query needs to run before being logged:
set global long_query_time = 20; (default is 10 seconds) 3. Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%'; Versions below 5.1.6:
Edit the /etc/my.cnf file with your favorite text editor vi /etc/my.cnf
Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:
log-slow-queries=/var/log/mysql/slow-query.log
3. Enable additional options as needed. Here are the same commonly used examples from above:
Set the amount of time a query needs to run before being logged:
`long_query_time=20 (default is 10 seconds)` Log details for queries expected to retrieve all rows instead of using an index:
`log-queries-not-using-indexes` 4. Restart the MySQL service:
service mysqld restart 5. Confirm the change is active by entering the MySQL shell and running the following:
show variables like '%slow%'; Update:1
According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.
Message: Unknown system variable %s And, as you can see on the same page, the SQLSTATE 99003 is not defined.
refer this link:
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
80
If your server is above 5.1.6 you can set the slow query log in the runtime itself. For which you have to execute this queries.
set global log_slow_queries = 1; set global slow_query_log_file = <some file name>; Or alternatively you can set the this options in the my.cnf/my.ini option files
log_slow_queries = 1; slow_query_log_file = <some file name>; Refer: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_slow_query_log_file
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
