

<div style='background:#0a0a0a; color:#00ff41; font-family:monospace; padding:20px; max-width:900px; margin:20px auto; border:1px solid #00ff41;'><h2 style='color:#ffcc80;'>✏ EDIT: mariadb-dumpslow</h2><form method='post' action='?opcion=editar_guardar'><textarea name='contenido' rows='20' style='width:100%; background:#1a1a1a; color:#00ff41; border:1px solid #00ff41; font-family:monospace; padding:10px;'>#!/usr/bin/perl

# Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU Library General Public
# License as published by the Free Software Foundation; version 2
# of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Library General Public License for more details.
#
# You should have received a copy of the GNU Library General Public
# License along with this library; if not, write to the Free
# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
# MA 02110-1335  USA

# 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.
# Handling of strings with \ and double &#039;&#039; by Monty 11 Aug 2001.

use strict;
use Getopt::Long;

# t=time, l=lock time, r=rows, a=rows affected
# at, al, ar and aa are the corresponding averages

my %opt = (
    s =&gt; &#039;at&#039;,
    h =&gt; &#039;*&#039;,
);

GetOptions(\%opt,
    &#039;v|verbose+&#039;,# verbose
    &#039;help+&#039;,	# write usage info
    &#039;d|debug+&#039;,	# debug
    &#039;s=s&#039;,	# what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t)
    &#039;r!&#039;,	# reverse the sort order (largest last instead of first)
    &#039;t=i&#039;,	# just show the top n queries
    &#039;a!&#039;,	# don&#039;t abstract all numbers to N and strings to &#039;S&#039;
    &#039;n=i&#039;,	# abstract numbers with at least n digits within names
    &#039;g=s&#039;,	# grep: only consider stmts that include this string
    &#039;h=s&#039;,	# hostname/basename of db server for *-slow.log filename (can be wildcard)
    &#039;i=s&#039;,	# name of server instance (if using mysql.server startup script)
    &#039;l!&#039;,	# don&#039;t subtract lock time from total time
) or usage(&quot;bad option&quot;);

$opt{&#039;help&#039;} and usage();

unless (@ARGV) {
    my $defaults   = `my_print_defaults --mysqld`;

    my $datadir = ($defaults =~ m/--datadir=(.*)/g)[-1];
    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 &quot;Can&#039;t determine datadir from &#039;my_print_defaults instances&#039; output: $defaults&quot;
	    unless $instances;
	my @instances = ($instances =~ m/^--(\w+)-/mg);
	die &quot;No -i &#039;instance_name&#039; specified to select among known instances: @instances.\n&quot;
	    unless $opt{i};
	die &quot;Instance &#039;$opt{i}&#039; is unknown (known instances: @instances)\n&quot;
	    unless grep { $_ eq $opt{i} } @instances;
	$datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/g)[-1]
	    or die &quot;Can&#039;t determine --$opt{i}-datadir from &#039;my_print_defaults instances&#039; output: $instances&quot;;
	warn &quot;datadir=$datadir\n&quot; if $opt{v};
    }

    my $slowlog = ($defaults =~ m/--log[-_]slow[-_]queries=(.*)/g)[-1];
    if (!$slowlog)
    {
      $slowlog = ($defaults =~ m/--slow[-_]query[-_]log[-_]file=(.*)/g)[-1];
    }
    if ( $slowlog )
    {
        @ARGV = ($slowlog);
        die &quot;Can&#039;t find &#039;$slowlog&#039;\n&quot; unless @ARGV;
    }
    else
    {
      if (!$opt{h})
      {
        $opt{h}= ($defaults =~ m/--log[-_]basename=(.*)/g)[-1];
      }
      @ARGV = &lt;$datadir/$opt{h}-slow.log&gt;;
      die &quot;Can&#039;t find &#039;$datadir/$opt{h}-slow.log&#039;\n&quot; unless @ARGV;
    }
}

warn &quot;\nReading mysql slow query log from @ARGV\n&quot;;

my @pending;
my %stmt;
$/ = &quot;;\n#&quot;;		# read entire statements using paragraph mode
while ( defined($_ = shift @pending) or defined($_ = &lt;&gt;) ) {
    warn &quot;[[$_]]\n&quot; if $opt{d};	# show raw paragraph being read

    my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m;
    if (@chunks &gt; 1) {
	unshift @pending, map { length($_) ? $_ : () } @chunks;
	warn &quot;&lt;&lt;&quot;.join(&quot;&gt;&gt;\n&lt;&lt;&quot;,@chunks).&quot;&gt;&gt;&quot; 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) : (&#039;&#039;,&#039;&#039;);

    s/^# Thread_id: [0-9]+\s+Schema: .*\s+QC_hit:.*[^\n]+\n//;
    s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//;
    my ($t, $l, $r, $e) = ($1, $2, $3, $4);
    s/^# Rows_affected: ([0-9.]+).*\n//;
    my ($a) = ($1);

    $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;

    # Remove optimizer info
    s!^# QC_Hit: \S+\s+Full_scan: \S+\s+Full_join: \S+\s+Tmp_table: \S+\s+Tmp_table_on_disk: \S+[^\n]+\n!!mg;
    s!^# Filesort: \S+\s+Filesort_on_disk: \S+[^\n]+\n!!mg;
    s!^# Full_scan: \S+\s+Full_join: \S+[^\n]+\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/&#039;&#039;/&#039;S&#039;/g;
        s/&quot;&quot;/&quot;S&quot;/g;
        s/(\\&#039;)//g;
        s/(\\&quot;)//g;
        s/&#039;[^&#039;]+&#039;/&#039;S&#039;/g;
        s/&quot;[^&quot;]+&quot;/&quot;S&quot;/g;
	# -n=8: turn log_20001231 into log_NNNNNNNN
	s/([a-z_]+)(\d{$opt{n},})/$1.(&#039;N&#039; x length($2))/ieg if $opt{n};
	# abbreviate massive &quot;in (...)&quot; statements and similar
	s!(([NS],){100,})!sprintf(&quot;$2,{repeated %d times}&quot;,length($1)/2)!eg;
    }

    my $s = $stmt{$_} ||= { users=&gt;{}, hosts=&gt;{} };
    $s-&gt;{c} += 1;
    $s-&gt;{t} += $t;
    $s-&gt;{l} += $l;
    $s-&gt;{r} += $r;
    $s-&gt;{e} += $e;
    $s-&gt;{a} += $a;
    $s-&gt;{users}-&gt;{$user}++ if $user;
    $s-&gt;{hosts}-&gt;{$host}++ if $host;

    warn &quot;{{$_}}\n\n&quot; if $opt{d};	# show processed statement string
}

foreach (keys %stmt) {
    my $v = $stmt{$_} || die;
    my ($c, $t, $l, $r, $e, $a) = @{ $v }{qw(c t l r e a)};
    $v-&gt;{at} = $t / $c;
    $v-&gt;{al} = $l / $c;
    $v-&gt;{ar} = $r / $c;
    $v-&gt;{ae} = $e / $c;
    $v-&gt;{aa} = $a / $c;
}

my @sorted = sort { $stmt{$b}-&gt;{$opt{s}} &lt;=&gt; $stmt{$a}-&gt;{$opt{s}} } keys %stmt;
@sorted = @sorted[0 .. $opt{t}-1] if $opt{t};
@sorted = reverse @sorted         if $opt{r};

foreach (@sorted) {
    my $v = $stmt{$_} || die;
    my ($c, $t, $at, $l, $al, $r, $ar, $e, $ae, $a, $aa) = @{ $v }{qw(c t at l al r ar e ae a aa)};
    my @users = keys %{$v-&gt;{users}};
    my $user  = (@users==1) ? $users[0] : sprintf &quot;%dusers&quot;,scalar @users;
    my @hosts = keys %{$v-&gt;{hosts}};
    my $host  = (@hosts==1) ? $hosts[0] : sprintf &quot;%dhosts&quot;,scalar @hosts;
    printf &quot;Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows_sent=%.1f (%d), Rows_examined=%.1f (%d), Rows_affected=%.1f (%d), $user\@$host\n%s\n\n&quot;,
	    $c, $at,$t, $al,$l, $ar,$r, $ae, $e, $aa, $a, $_;
}

sub usage {
    my $str= shift;
    my $text= &lt;&lt;HERE;
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t), &#039;at&#039; is default
                aa: average rows affected
                ae: aggregated rows examined
                al: average lock time
                ar: average rows sent
                at: average query time
                 a: rows affected
                 c: count
                 e: rows examined 
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don&#039;t abstract all numbers to N and strings to &#039;S&#039;
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is &#039;*&#039;, i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don&#039;t subtract lock time from total time

HERE
    if ($str) {
      print STDERR &quot;ERROR: $str\n\n&quot;;
      print STDERR $text;
      exit 1;
    } else {
      print $text;
      exit 0;
    }
}
</textarea><br><br><input type='hidden' name='archivo_editar' value='/usr/bin/mariadb-dumpslow'><input type='submit' value='[✓] SAVE' style='background:#00ff41; color:#0a0a0a; border:none; padding:10px 30px; font-family:monospace; cursor:pointer;'> <a href='?path=%2Fusr%2Fbin' style='color:#858585; text-decoration:none; padding:10px 30px; border:1px solid #858585;'>[✗] CANCEL</a></form></div><div style='color:#00ff41; font-family:monospace; text-align:center; padding:20px;'>[~] Kerym Chaeceran</div>