systemadmin.es > DBA > Script para alertar de la evolución de las slow queries

Script para alertar de la evolución de las slow queries

Una de las cosas que los programadores tienden a olvidar es ver la evolución de las slow queries de un servidor de bases de datos. Hoy vamos a ver un script para alertar si las slow queries se han duplicado.

Para poder hacer el script adecuadamente se debe tener en cuenta que perfil tienen las slow queries en un servidor. Tomando como ejemplo un servidor de bases de datos cualquiera de una página web podríamos ver algo similar a lo siguiente:

Evolución slow queries

Evolución slow queries

Y un zoom de la parte final para ver mejor los datos:

Zoom de la evolución de las slow queries

Zoom de la evolución de las slow queries

Los picos que podemos ver se trata de algo periódico cada domingo porque es entonces cuando se realizan algunas tareas de mantenimiento de la base de datos como son los optimize. En este caso concreto los backups una parte del tiempo se hacían con snapshots de LVM y otra parte del tiempo mediante un MySQL slave.

Podemos darnos cuenta también como en el gran pico un gran incremento de slow queries provoca un incremento muchísimo mayor del tiempo total de slow queries (suma del total del tiempo de las slow queries):

Pico de slow queries

Pico de slow queries

Por lo que resulta interesante poder monitorizar también este valor, no sólo un contador de las slow queries. Un contador de slow queries sólo indica el número que se han producido, pero no nos indica si han sido muchas de pequeñas o pocas de grandes. Por ejemplo, si por algún problema de programación o de falta de índices se generan las mismas slow queries pero estas pasan de tardar 1 segundo a tardar 3 segundos, podríamos ver un número similar de ellas pero no nos daríamos cuenta que la aplicación va mucho peor si sólo miramos el contador.

Por otro lado, para el caso del ejemplo podemos ver como el tiempo total de bloqueos de las tablas son valores razonables excepto por los grandes picos que indicaban, en este caso, una tabla que contenía demasiados datos. En el caso de ejemplo no es algo a destacar demasiado, pero nos puede servir para otros casos para poder evaluar si llega le ha llegado el momento a alguna tabla a pasarla de MyISAM a InnoDB o se deberá segmentar o particionar.

Tiempo total de bloqueos de tablas

Tiempo total de bloqueos de tablas

Por lo tanto el script lo haremos teniendo en cuenta:

  • Existen tareas en la base de datos como son el optimize y los backups que provocan picos de forma periódica.
  • Un incremento en un valor puede provocar un incremento muchísimo mayor en otro valor.

Es por eso que deberemos monitorizar todos los valores disponibles comparados no solo con el dia anterior, sino también con el mismo día de la semana anterior para evitar falsos positivos.

El script en su primer versión sería:

#!/usr/bin/perl

my $data="";
my $hora="";
my $luser="";
my $host="";

my $querytime="";
my $locktime="";

my %totalslows_ayer;
my %totaltiempo_ayer;
my %tiempolock_ayer;

my %totalslows_antesayer;
my %totaltiempo_antesayer;
my %tiempolock_antesayer;

my %totalslows_semanaanterior;
my %totaltiempo_semanaanterior;
my %tiempolock_semanaanterior;


my $ayer = time - 24 * 60 * 60;
($day, $month, $year) = (localtime($ayer))[3,4,5];
$year-=100;
$month++;
$stringayer=sprintf("%02d%02d%02d",$year,$month,$day);

my $antesayer = time - 2 * 24 * 60 * 60;
($day, $month, $year) = (localtime($antesayer))[3,4,5];
$year-=100;
$month++;
$stringantesayer=sprintf("%02d%02d%02d",$year,$month,$day);

my $semanaanterior = time - 7 * 24 * 60 * 60;
($day, $month, $year) = (localtime($semanaanterior))[3,4,5];
$year-=100;
$month++;
$stringsemanaanterior=sprintf("%02d%02d%02d",$year,$month,$day);

while ($line = <>)
{
        #print "LINE:".$line;

        if($line =~ m/^# Time:\s+(\d+)\s+(\d+:\d+:\d+)/i)
                $data=$1; $hora=$2;

        if($line =~ m/^# User\@Host:\s+(\w+)\[?\w*\]?\s+\@\s+\[?([\w\.]+)\]?/i)
                $luser=$1; $host=$2;

        if($line =~ m/^# Query_time:\s+([\d\.]+)\s+Lock_time:\s+([\d\.]+)\s+Rows_sent:\s+([\d\.]+)\s+Rows_examined:\s+([\d\.]+)/i)
        {

                if($data eq $stringayer)
                {
                        $totalslows_ayer{$luser}++;
                        $totaltiempo_ayer{$luser}+=$1;
                        $tiempolock_ayer{$luser}+=$2;

                }

                if($data eq $stringsemanaanterior)
                {
                        $totalslows_semanaanterior{$luser}++;
                        $totaltiempo_semanaanterior{$luser}+=$1;
                        $tiempolock_semanaanterior{$luser}+=$2;
                }

                if($data eq $stringantesayer)
                {
                        $totalslows_antesayer{$luser}++;
                        $totaltiempo_antesayer{$luser}+=$1;
                        $tiempolock_antesayer{$luser}+=$2;
                }
        }
}

#print "============= total slows ================\n";
while ( my ($key, $value) = each(%totalslows_ayer) )
{
        print "ALERTA -   el usuario $key ha doblado el numero de slowqueries (ayer: $value antes ayer: $totalslows_antesayer{$key} semana pasada: $totalslows_semanaanterior{$key}\n" if (($value>$totalslows_antesayer{$key}*2)&&($value>$totalslows_semanaanterior{$key}*2));
        print "WARNING -  el usuario $key ha doblado el numero de slowqueries (ayer: $value antes ayer: $totalslows_antesayer{$key} semana pasada: $totalslows_semanaanterior{$key}\n" if (($value>$totalslows_antesayer{$key}*2)||($value>$totalslows_semanaanterior{$key}*2));
}

#print "============= total tiempo ================\n";
while ( my ($key, $value) = each(%totaltiempo_ayer) )
{
        print "ALERTA -  el usuario $key ha doblado el tiempo total de las slowqueries (ayer: $value antes ayer: $totaltiempo_antesayer{$key} semana pasada: $totaltiempo_semanaanterior{$key}\n" if (($value>$totaltiempo_antesayer{$key}*2)&&($value>$totaltiempo_semanaanterior{$key}*2));
        print "WARNING -  el usuario $key ha doblado el tiempo total de las slowqueries (ayer: $value antes ayer: $totaltiempo_antesayer{$key} semana pasada: $totaltiempo_semanaanterior{$key}\n" if (($value>$totaltiempo_antesayer{$key}*2)||($value>$totaltiempo_semanaanterior{$key}*2));
}

#print "============= total lock ================\n";
while ( my ($key, $value) = each(%tiempolock_ayer) )
{
        print "ALERTA -  el usuario $key ha doblado el tiempo de locks (ayer: $value antes ayer: $tiempolock_antesayer{$key} semana pasada: $tiempolock_semanaanterior{$key}\n" if (($value>$tiempolock_antesayer{$key}*2)&&($value>$tiempolock_semanaanterior{$key}*2));
        print "WARNING -  el usuario $key ha doblado el tiempo de locks (ayer: $value antes ayer: $tiempolock_antesayer{$key} semana pasada: $tiempolock_semanaanterior{$key}\n" if (($value>$tiempolock_antesayer{$key}*2)||($value>$tiempolock_semanaanterior{$key}*2));
}

La parte a destacar es la siguiente:

  • Se da una ALERTA cuando tanto comparando el día anterior con el anterior al mismo Y el mismo día de la semana anterior se duplican los valores:
    if (($value>$tiempolock_antesayer{$key}*2)&&
    ($value>$tiempolock_semanaanterior{$key}*2));
    
  • Se da un WARNING cuando comparando el día anterior con el anterior al mismo O el día anterior con el de la semana pasada duplica el valor:
    if (($value>$tiempolock_antesayer{$key}*2)||
    ($value>$tiempolock_semanaanterior{$key}*2));
    

Según se comporte el script se deberá hacer una segunda versión que se integre con el Nagios para evitar tener que usarlo con el cron.

One comment to “Script para alertar de la evolución de las slow queries”

  1. Me parece la típica tontería que le gusta a los jefecillos pero que no
    sirve para nada más que enviar correos innecesarios con cosas que
    tampoco le vas a poder hacer nada.

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>