mysqldumpslow not displaying query time

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have MySQL Percona server (5.5.15-55) installed.

I tried to eliminate slow quires. I’ve enabled slow query log (please see details below) but the “mysqldumpslow” script is parsing them with time equal zero second. Is this some issue or do I have to change something in the configuration?

mysqldumpslow -l -s c -t 10 /var/log/mysql/mysql-slow.log…Count: 33 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), .00s…

mysql> SHOW VARIABLES LIKE ‘%slow%’; ±-----------------------------------±------------------------+| Variable_name | Value |±-----------------------------------±------------------------+| log_slow_filter | || log_slow_queries | ON || log_slow_rate_limit | 1 || log_slow_slave_statements | OFF || log_slow_sp_statements | ON || log_slow_verbosity | || slow_launch_time | 2 || slow_query_log | ON || slow_query_log_file | /var/log/mysql/mysql-slow.log || slow_query_log_timestamp_always | OFF || slow_query_log_timestamp_precision | second || slow_query_log_use_global_control | |±-----------------------------------±------------------------+

Reagrds

2

Try log_slow_verbosity=full

3

gmouse wrote on Thu, 16 February 2012 10:39

Hi,

Thanks for reply.

I’ve noticed that there is a time stamp in the slow query log itself so maybe this tool is not parsing it properly?

…# Time: 120106 5:45:54…

4

What worked for me was removing lines from the slow log file that mention the Schema.

sed -i.bak ‘/# Schema:/d’ ./mysql-slow.log

The command above will remove all lines containing #Schema: <databasename> and back up the original file to mysql-slow.log.bak
Then the mysqldumpslow works like expected.

I would prefer to have used the suggested pt-query-digest but I don’t have the tool installed in my development machine, when I wanted to analyze percona logs from a production server.