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

long_query_time not working

chavasreechavasree EntrantCurrent User Role Beginner
I changed this variable long_query_time=10 during runtime and unable to see any difference as the slow query log is logging each and every query and filling up the disk space. i ran the below command but of no use.

set GLOBAL long_query_time=10;

but i don't see any change..i still see queries with querytime=0.0000012 are still writing to slow_query log file.

i disabled slow query log and it stops writing to slow query log.. but once i enable it again with long_query_time=10 i still see queries with 0 secs logging into it.. and my settings are below.

mysql> show variables like '%slow%';
+
+
+
| Variable_name | Value |
+
+
+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/logs/slow/slow-queries.log |
+
+
+


+
+
+
| Variable_name | Value |
+
+
+
| long_query_time | 10.000000 |


Please help.

Thanks,

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi chavasree;

    This could be a few things:

    1) If you are using a version of Percona Server older than 5.5.37, then it could be this bug:
    https://bugs.launchpad.net/percona-server/+bug/1016991

    2) It could be that you have log_queries_not_using_indexes enabled, which would cause queries quicker than 10 seconds to be logged if they are not using an index.

    3) It could also be the fact that changing the long_query_time only affects new connections, so if you have connections that remain open for long periods of time, they may not be picking up the new setting.

    -Scott
  • chavasreechavasree Entrant Current User Role Beginner
    here are my answers.. i am using latest version of mysql

    MySql Version: 5.6.19

    and this is the current setting of the variable.

    log_queries_not_using_indexes =0

    3) It could also be the fact that changing the long_query_time only affects new connections, so if you have connections that remain open for long periods of time, they may not be picking up the new setting.

    Yes you might be right, i have some connections already open and the current setting doesn't apply to it, even though i tried using flush hosts, flush logs.. nothing worked for me, looks like i need to kill those connections and let the app re-connect it..

    what do you say.

    thanks,
    Sree
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi chavasree;

    Yeah you would need your app to reconnect one way or another. The brute force way of killing the threads would work assuming your app auto-connects, but just be careful that your app can handle it gracefully, especially if this is a production database. =)

    -Scott
  • Robert GuyRobert Guy Entrant Inactive User Role Beginner
    Thanks so much chavasree.
    log_queries_not_using_indexes =0

    Worked great! Saved a lot of time.

    Regards,
    Robert

    The Guy from Beijing.
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.