mysqldumpslow error
I am using debian squeeze and mysql 5.1.45. I am trying to use mysqldumpslow -s c -t 10, but I keep getting the following error: Can't determine basedir from 'my_print_defaults mysqld' output: --general_log=on. Does anyone have any idea as to why? I am not getting anything from google. TIA, LS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldumpslow
Yeah, It can only parse the local file. If you expect to parse the remote one, just download it,haha. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/19 Eric Bergen eric.ber...@gmail.com I don't think so. mysqldumpslow parses the log file on the local server. On Fri, Dec 17, 2010 at 3:01 AM, partha sarathy par...@mafiree.com wrote: Is it possible to run mysqldumpslow on a remote host? -Partha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Re: mysqldumpslow
I don't think so. mysqldumpslow parses the log file on the local server. On Fri, Dec 17, 2010 at 3:01 AM, partha sarathy par...@mafiree.com wrote: Is it possible to run mysqldumpslow on a remote host? -Partha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldumpslow
Is it possible to run mysqldumpslow on a remote host? -Partha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldumpslow output interpretation
Anil Doppalapudi wrote: Hi List, Any update on this Thanks Anil Hi List, Below is the output of mysqldumpslow. In the output query execution time is showing -ve value how to interpret the below output i.e how to interpret -ve value for query execution time please advice Nobody has any ideas so nobody has replied. Maybe post a bug report about it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldumpslow output interpretation
Hi List,
Any update on this
Thanks
Anil
Hi List,
Below is the output of mysqldumpslow. In the output query execution time is
showing -ve value how to interpret the below output i.e how to interpret
-ve value for query execution time please advice
Count: 1 Time=4294967223.00s (-73s) Lock=0.00s (0s) Rows=0.0 (0),
ebizb11[ebizb11]@
UPDATE MM_EMAIL,MM_GROUP SET MM_EMAIL.STATUS='S' WHERE
MM_EMAIL.EMAILADD=lower('S') AND MM_GROUP.ID=MM_EMAIL.GROUPID AND MM_GROUP
.DEPTID=N
Count: 1 Time=4294967223.00s (-73s) Lock=0.00s (0s) Rows=0.0 (0),
ebizb11[ebizb11]@
SELECT LIST.NAME,DEPT.NAME FROM MM_LIST LIST, MM_DEPARTMENT DEPT WHERE
LIST.DEPTID=DEPT.ID AND LIST.LISTTYPE = 'S' AND LIST.STATUS
= 'S' AND LIST.CREATEDDATE DATE_SUB(NOW(),INTERVAL N DAY)
Thanks
Anil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldumpslow errors
Hi! Steven == Steven Roussey [EMAIL PROTECTED] writes: Description: Steven 1. Handling of string simplification confused, crashes perl! Steven 2. Format of slow log had changed a while back, so now the Steven timing information is broken in mysqldumpslow How-To-Repeat: Steven uploaded to mysql secret: n54-slow.log.gz Steven gunzip n54-slow.log.gz Steven mysqldumpslow n54-slow.log Fix: Steven 1. Change: Stevens/'([^\\\']|\\.|\'\')+'/'S'/g; Stevens/([^\\\]|\\.|\\)+/S/g; Steven To: Steven s/''/'S'/g; Steven s//S/g; Steven s/(\\')//g; Steven s/(\\)//g; Steven s/'[^']+'/'S'/g; Steven s/[^]+/S/g; Steven I'm not a perl wizzard, so there is likely a better way. But this Steven actually works. :) Steven 2. Change: Steven s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; Steven To: Steven s/^# Query_time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; cut Applied to 4.0.2 source. Thanks! Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldumpslow errors
Description: 1. Handling of string simplification confused, crashes perl! 2. Format of slow log had changed a while back, so now the timing information is broken in mysqldumpslow How-To-Repeat: uploaded to mysql secret: n54-slow.log.gz gunzip n54-slow.log.gz mysqldumpslow n54-slow.log Fix: 1. Change: s/'([^\\\']|\\.|\'\')+'/'S'/g; s/([^\\\]|\\.|\\)+/S/g; To: s/''/'S'/g; s//S/g; s/(\\')//g; s/(\\)//g; s/'[^']+'/'S'/g; s/[^]+/S/g; I'm not a perl wizzard, so there is likely a better way. But this actually works. :) 2. Change: s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; To: s/^# Query_time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; Submitter-Id: submitter ID Originator:Steven Roussey Organization: Network54 MySQL support: [none anymore :( ] Synopsis: synopsis of the problem (one line) Severity: serious Priority: [ low | medium | high ] (one line) Category: mysql Class: sw-bug Release: mysql-3.23.47 (Official MySQL binary) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.47-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 5 days 20 hours 57 min 33 sec Threads: 81 Questions: 693745099 Slow queries: 2238 Opens: 967352 Flush tables: 1 Open tables: 1510 Queries per second avg: 1367.112 Environment: machine, os, target, libraries (multiple lines) System: Linux trinity 2.4.16-0.13smp #1 SMP Fri Dec 14 05:10:37 EST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 11-16 13:02 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283580 10-03 11:10 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314604 10-03 10:52 /usr/lib/libc.a -rw-r--r--1 root root 178 10-03 10:52 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BUGs in mysqldumpslow + fixes
(BTW, mysqldumpslow does not seem to be in the RPMs for v4.) mysqldumpslow (perl itself, I guess) dumps core and acts incorrectly when dealing with simplifying strings. Fix: Change: s/'([^\\\']|\\.|\'\')+'/'S'/g; s/([^\\\]|\\.|\\)+/S/g; To: s/''/'S'/g; s//S/g; s/(\\')//g; s/(\\)//g; s/'[^']+'/'S'/g; s/[^]+/S/g; I'm not a perl wizzard, so there is likely a better way. But this actually works. :) Also, there is a bug with getting the query and lock times. Change: s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; To: s/^# Query_time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; Sincerely, Steven Roussey http://Network54.com/?pp=e sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldumpslow
I'm trying to find what causes mysqldumpslow to crash for us every time the log is anything but trivial. The following does not crash it, but it does give incorrect results: # User@Host: apache[apache] @ morpheus.e [1.1.1.1] # Query_time: 8 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 replace into forums_posts_new_1 (f,m,p,r,d,dm,a,au,l,i,u,aut,e,t,m,h ) values (7451,1014880635,1014815708,1014815708,'no','no','yes','no',0,3538664738 ,'af34b901b897b5ce59c7bf7df3fb620c','Ex','','Re: HTV','Dont worry BR BR Chow from Japan.',''); Gives Reading mysql slow query log from test.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), apache[apache]@morpheus.e # Query_time: N Lock_time: N Rows_sent: N Rows_examined: N replace into forums_posts_new_1 (f,m,p,r,d,dm,a,au,l,i,u,aut,e,t,m,h ) values (N,N,N,N,'S','S','S','S',N,N,'S','S',''S'Re: HTV'S'Dont worry BR BR Chow from Japan.'S'') I think it is the '' at the two locations... Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
where is mysqldumpslow?
From the manual (section 4.9.5 'The Slow Query Log'): The slow query log can be used to find queries that take a long time to execute and are thus candidates for optimisation. With a large log, that can become a difficult task. You can pipe the slow query log through the mysqldumpslow command to get a summary of the queries which appear in the log. Where is mysqldumpslow? Thanks, José Ceferino Ortega - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Updated mysqldumpslow command
Attached is an updated version of the mysqldumpslow command that's
supplied in the mysql distribution.
The mysqldumpslow, for those who've not tried it, makes it very
easy to summarize and analyze the contents of the 'slow query log'.
This update was prompted by the need to support the newer format
of the slow query log, which now includes user and time information.
I took the opportunity to add assorted extra useful bells and whistles.
It's well worth experimenting with this handy utility.
Enjoy.
Tim.
#!/my/gnu/bin/perl
# mysqldumpslow - parse and summarize the MySQL slow query log
# Original version by Tim Bunce, sometime in 2000.
# Further changes by Tim Bunce, 8th March 2001.
use strict;
use Getopt::Long;
# t=time, l=lock time, r=rows
# at, al, and ar are the corresponding averages
my %opt = (
s = 'at',
h = '*',
);
GetOptions(\%opt,
'v+', # verbose
'd+', # debug
's=s', # what to sort by (t, at, l, al, r, ar etc)
'r!', # reverse the sort order (largest last instead of first)
't=i', # just show the top n queries
'a!', # don't abstract all numbers to N and strings to 'S'
'n=i', # abstract numbers with at least n digits within names
'g=s', # grep: only consider stmts that include this string
'h=s', # hostname of db server for *-slow.log filename (can be wildcard)
'i=s', # name of server instance (if using mysql.server startup script)
'l!', # don't subtract lock time from total time
) or die "Bad option";
unless (@ARGV) {
my $defaults = `my_print_defaults mysqld`;
my $basedir = ($defaults =~ m/--basedir=(.*)/)[0]
or die "Can't determine basedir from 'my_print_defaults mysqld' output:
$defaults";
warn "basedir=$basedir\n" if $opt{v};
my $datadir = ($defaults =~ m/--datadir=(.*)/)[0];
if (!$datadir or $opt{i}) {
# determine the datadir from the instances section of /etc/my.cnf, if any
my $instances = `my_print_defaults instances`;
die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults"
unless $instances;
my @instances = ($instances =~ m/^--(\w+)-/mg);
die "No -i 'instance_name' specified to select among known instances:
@instances.\n"
unless $opt{i};
die "Instance '$opt{i}' is unknown (known instances: @instances)\n"
unless grep { $_ eq $opt{i} } @instances;
$datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0]
or die "Can't determine --$opt{i}-datadir from 'my_print_defaults
instances' output: $instances";
warn "datadir=$datadir\n" if $opt{v};
}
@ARGV = $datadir/$opt{h}-slow.log;
die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV;
}
warn "\nReading mysql slow query log from @ARGV\n";
my @pending;
my %stmt;
$/ = ";\n#";# read entire statements using paragraph mode
while ( defined($_ = shift @pending) or defined($_ = ) ) {
warn "[[$_]]\n" if $opt{d}; # show raw paragraph being read
my @chunks = split /^\/.*Version.*started
with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m;
if (@chunks 1) {
unshift @pending, map { length($_) ? $_ : () } @chunks;
warn "".join("\n",@chunks)."" if $opt{d};
next;
}
s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//;
my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) :
('','');
s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//;
my ($t, $l, $r) = ($1, $2, $3);
$t -= $l unless $opt{l};
# remove fluff that mysqld writes to log when it (re)starts:
s!^/.*Version.*started with:.*\n!!mg;
s!^Tcp port: \d+ Unix socket: \S+\n!!mg;
s!^Time.*Id.*Command.*Argument.*\n!!mg;
s/^use \w+;\n//;# not consistently added
s/^SET timestamp=\d+;\n//;
s/^[]*\n//mg; # delete blank lines
s/^[]*/ /mg; # normalize leading whitespace
s/\s*;\s*(#\s*)?$//;# remove trailing semicolon(+newline-hash)
next if $opt{g} and !m/$opt{g}/io;
unless ($opt{a}) {
s/\b\d+\b/N/g;
s/\b0x[0-9A-Fa-f]+\b/N/g;
s/'.*?'/'S'/g;
s/".*?"/"S"/g;
# -n=8: turn log_20001231 into log_
s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n};
# abbreviate massive "in (...)" statements and similar
s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg;
}
my $s = $stmt{$_} ||= { users={}, hosts={} };
$s-{c} += 1;
$s-{t} += $t;
$s-{l} += $l;
$s-{r} += $r;
$s-{users}-{$user}++ if $user;
$s-{hosts}-{$host}++ if $host;
warn "{{$_}}\n\n" if $opt{d}; # show processed statement string
}
foreach (keys %stmt) {
my $v
