How to set slow query log

MySQL & MariaDB Other MySQL® Questions
1

Hi

I want to set slow query log which consists of all SQL statements that took more than long_query_time seconds to execute.

Now default value is set for long_query_time ie 10. Show variables shows

log_slow_queries - OFF

long_query_time - 10

I want to enable log_slow_queries and set long_query_time =1

I found in mysql manual to set slow query log as follows in my.cnf

mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

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

Even after changing this in my.cnf, the show Global variables shows as

log_slow_queries - OFF

long_query_time - 10

It looks like same before and the new value not set.

In my linux server, my.cnf settings are

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

Default to using old password format for compatibility with mysql 3.x

clients (those using the mysqlclient10 compatibility package).

old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

~
~
“/etc/my.cnf” 21L, 454C

Any one say how to find slow log queries. I’m using RHEL 5 and MySQL 5.0.22

thanks

2

have u restarted the mysql server after making your changes in the my.cnf file

3

S . I restarted using command - service mysqld restart

General query log captures in mysqld.log.

slow query log only not enabled.

After restarting mysql server log_slow_queries=off

is there any other way to enable log_slow_queries

thanks

4

This is one of the most called query:

select * from delivery_zones where
ST_Contains(zone, POINT(latitude,longitude))
and restaurant_id = 100228 and
(
(weekdays & 16 or weekdays is null)
and (
(hour_starts_at is null and hour_ends_at is null)
or (hour_starts_at is null and hour_ends_at >= ‘13:15:00’)
or (hour_starts_at <= ‘13:15:00’ and hour_ends_at is null)
or (‘13:15:00’ BETWEEN hour_starts_at AND hour_ends_at)
)
and (
(starts_at is null and ends_at is null)
or (starts_at is null and ends_at >= ‘2018-09-21 13:15:00’)
or (starts_at <= ‘2018-09-21 13:15:00’ and ends_at is null)
or (‘2018-09-21 13:15:00’ BETWEEN starts_at AND ends_at)
)
)
and active = 1
and delivery_zones.deleted_at is null

order by layer asc;

(i’ve replaced the real latitude and longitude when posting here, for privacy)
I would like to avoid the resulting filesort

this is the explain:

1,SIMPLE,delivery_zones,range,zone,active,weekdays,restaurant_id,deleted_at,hours,starts_ends,zone,34,1,69.93,Using where; Using filesort

There is also another query called tons of time in a row (about 700-800 times per access) but it doesn’t result in slow log, and I don’t know why, probably it’s already optimized

5

Hi gandalf did you intend to update this post? It’s from 2007! :slight_smile: