Not the answer you need?
Register and ask your own question!

How to set slow query log

julianjulian ContributorCurrent User Role Beginner
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

Comments

  • babuno5babuno5 Entrant Current User Role Beginner
    have u restarted the mysql server after making your changes in the my.cnf file
  • julianjulian Contributor Current User Role Beginner
    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
  • gandalfgandalf Contributor Current User Role Beginner
    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
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi gandalf did you intend to update this post? It's from 2007! :)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.