long_query_time not working

MySQL & MariaDB Other MySQL® Questions
1

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,

2

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:
    [url]https://bugs.launchpad.net/percona-server/+bug/1016991[/url]

  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

3

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

  1. 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

4

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

5

Thanks so much chavasree.

Worked great! Saved a lot of time.

Regards,
Robert

The Guy from Beijing.