Post on 10-Apr-2015
Estadísticas y gráficas con MySQL
Estadísticas● MySQL ofrece:
● AVG(expresión): Media de los valores● COUNT(expresión): Número de elementos del
grupo● MAX(expresión): Valor máximo de la expresión● MIN(expresión): Valor mínimo de la expresión● SUM(expresión): Suma de todos los valores● STDDEV(expresión): Desviación típica de los
valores
Estadísticas● Ejemplos sencillos:
mysql> SELECT COUNT(*) FROM home;mysql> SELECT AVG(tamano) FROM home;mysql> SELECT MAX(tamano) – MIN(tamano) FROM home;
Estadísticas● Caso práctico: calcular el percentil
● MySQL no tiene una función para hacerlo● Vamos a buscar una forma eficiente de calcularlo
Estadísticas● Para comprobar que es eficiente, vamos a
probarlo en una tabla grande$ mysqladmin -uroot create pruebas$ mysql -uroot pruebas -e “CREATE TABLE bigtable (num_1 INT, num_2 INT, num_3 INT, num_4 INT, num_5 INT, date_1 DATE, date_2 DATE, date_3 DATE, date_4 DATE, date_5 DATE, str_1 VARCHAR(200), str_2 VARCHAR(200), str_3 VARCHAR(200), str_4 VARCHAR(50000));”$ mysql -uroot pruebas -e “REATE INDEX ON bigtable (num_1 ASC)”
Estadísticas● Editamos big-table.pl
#!/usr/bin/perl
my @base = ("a".."z", "A".."Z", 0..9);
for my $i (0..700000) {
for my $num (0..4) { print rand 1000000000, "\t"; }
for my $dat (0..4) { print rand 12,"/",rand 28,"/2010\t"; }
for my $str (0..2) { print map($base[rand @base], 0..200), "\t"; }
print map ($base[rand @base], 0..50000), "\n";
}
Estadísticas● Creamos unos 4Gb de datos para la tabla
$ perl ./big-table.pl > bigtable.txt
● Poblamos la tabla$ mysqlimport -uroot pruebas /home/formacion/bigtable.txt
● Puede llevar un rato...
Estadísticas● Vamos a calcular el percentil, teniendo en
cuenta que:● Vamos a ordenar los datos según el campo
“num_1” (que está indexado)● Vamos a recoger solamente el primer individuo
del percentil
Estadísticas● Editamos percentil.sqluse pruebas;set @percentil=0.95;select 0,count(*) into @actual,@total from bigtable;select @actual:=@actual+1/@total as p,num_1 from bigtable having p >= @percentil order by num_1 limit 1 into @percentil,@indice;select * from bigtable where num_1=@indice limit 1;
Estadísticas● Ejecutamos el script:
● $ mysql -uroot -vvv < percentil.sql
Gráficas● La idea: utilizar la salida de una consulta para
generar gráficas● Características:
● Buscar un proceso que se pueda automatizar● Generar distintos tipos de gráficas● Desde línea de comandos
Gráficas● Una posibilidad: gnuplot
● Muy potente– Control muy preciso– Funciones matemáticas– Flexibilidad al generar la salida
● Inconvenientes– Sintaxis extremadamente abstrusa– De bajo nivel
Gráficas● Posibilidad más asequible: Google Chart API
● Ventajas– Muchos modelos de gráficas preconfigurados– Mucho más sencillo de usar que gnuplot
● Desventajas– Requiere conexión a internet– Límite de 10k de datos por gráfica
● Más informaciónhttp://code.google.com/intl/es-ES/apis/chart/image_charts.html
Gráficas● El mecanismo para hacer gráficos es
construir una URL donde se indique● El tipo de gráfico● La serie (o series) con los datos a graficar● Las etiquetas de los datos● El tamaño● Los colores● ...
Gráficas● Por ejemplo:
http://chart.apis.google.com/chart?cht=p3&chs=500x200&chd=t:10,20,50&chl=uno|dos|tres
● cht: tipo de gráfico● chs: tamaño del gráfico● chd: serie de datos● chl: etiquetas de cada dato
Gráficas
Gráficas● Utilizando Google Chart API con MySQL
● Proceso:– Una consulta genera una lista de datos– Se filtra por un script que construye la URL– Se descarga la imagen con wget
Gráficas● Ejemplo: Un gráfico de tarta con los 10
ficheros más pesados de la tabla ficheros.home● Editamos el script “uso-disco-home.pl”
#!/usr/bin/perlsub urlencode { my ($str) = @_; $str =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg; return $str;}$ancho="900"; $alto="300";$color="chco=00FF00|0000FF|FF0000|FF00FF|00FFFF|FFFF00";$url="http://chart.apis.google.com/chart?cht=p3&chs=".$ancho."x".$alto."&".$color;$chd="chd=t:"; $chdl='chl=';while(<>) { my ($nombre, $tamano) = $_ =~ /(.*?)\s+(.*)/; $chdl .= urlencode($nombre) . '|'; $chd .= $tamano . ","; }chop($chdl); chop($chd);print "$url&",$chdl,"&",$chd;
Gráficas● Los 10 ficheros más pesados, con su tamaño
en Kb:SELECT nombre,tamano/1000 FROM home ORDER BY tamano DESC LIMIT 10;
● Comando para generar la gráfica:$ wget $(mysql -uroot ficheros -e "SELECT nombre,tamano/1000000 FROM home ORDER BY tamano DESC LIMIT 10" -s | perl uso-disco-home.pl) --output-document=grafica.jpg
Gráficas● Resultado
Gráficas● Ejemplo: Uso de memoria del sistema
● Creamos una tabla apropiada● Recolectamos datos con un script● Construimos la URL● Generamos el gráfico
Gráficas● Crear la tabla y recolectar datos
$ mysqladmin -uroot create graficas$ mysql -uroot graficas -e “CREATE TABLE usomemoria (hora TIME, uso INT)”$ while true > do MEM_USADA=$(free | tail -n +3 | head -n 1 | sed 's/\s\+/:/g' | cut -d : -f 4)> MOMENTO=$(date +%H:%M:%S)> echo "Tomando muestra..."; echo -e "$MOMENTO\t$MEM_USADA" >> usomemoria.txt> sleep 1 done
Gráficas● Dejar que recolecte datos durante unos
minutos● Mientras, abrir google-chrome o algo similar
que aumente o disminuya el uso de memoria
sub urlencode {
my ($str) = @_;
$str =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
return $str;
}
$ancho="900";$alto="300";
$rango_ejes="chxr=0,0,1,0.1|1,0,100,10";
$titulo="chtt=".urlencode("HISTORIAL DE USO DE MEMORIA");
$url="cht=lc&chs=".$ancho."x".$alto."&".$titulo."&".$rango_ejes;
$chd="chd=t:"; $chdl='chl=';
my $contador = 0;
while(<>) {
my ($fecha, $mem) = $_ =~ /(.*?)\s+(.*)/;
$chd .= $mem . ",";
if ($contador++ % 10 == 0) { $chdl.=$fecha.'|'; }
}
chop($chdl); chop($chd);
print "$url&",$chdl,"&",$chd;
Gráficas● Matamos el recolector de datos (Control+C)● Generamos la gráfica
$ wget http://chart.apis.google.com/chart --post-data=$(mysql -uroot graficas -e "SELECT MAX(uso) INTO @max FROM usomemoria; SELECT hora,(uso/@max)*100 FROM usomemoria" -s | perl uso-memoria.pl) --output-document=graficamemoria.jpg
Gráficas● Resultado
Gráficas● Google Chart API tiene muchas más opciones
● Más tipos de gráficas● Más de una serie de datos por gráfica● Añadir y ajustar las leyendas● Añadir anotaciones a la gráfica● Dibujar cuadrículas de referencia● ...