pgFouine – Análisis de logs PostgreSQL en Debian

PgFouine, analiza las bitácoras (logs) de la base de datos PostgreSQL para generar informes detallados sobre el uso y abuso de consultas SQL que bien sabemos tienen una gran incidencia en el rendimiento y la carga que tendrá el manejador de base de datos y el servidor que lo aloje, con fin de optimizar y realizar los ajustes necesarios para mejorar el desempeño del mismo. Esta escrito en el lenguaje de programación PHP y se distribuye como software libre por lo que podrían generar los informes con logo personalizado, agregar particularidades, funcionalidades, mejorarlo, etc…

¿Como funciona pgFouine?

PgFouine realiza un análisis al archivo de log generado por PostgreSQL con el cual genera informes, que nos indicara que consultas SQL son las que mas tardan, cuales son mas frecuentes, cuantas consultas DML están presentes en ese archivo log, inclusive generando gráficos los cuales nos facilitara mucho la tarea de determinar o hacer seguimiento al comportamiento de nuestras bases de datos.

En un sistema operativo Debian GNU/Linux ó alguno de sus derivados(Canaima, Mint, Ubuntu) lo instalamos con un sencillo:

#aptitude install pgfouine

También pueden descargar el código fuente y luego de descomprimir el .tar.gz ejecutar con php el archivo pgfouine.php

pgFouine puede ser usado de dos maneras. Configurando los logs que genera PostgreSQL para usar las facilidades de syslog del sistema operativo, o mediante salida estándar stderr que es como viene por defecto la configuración del log generado por el manejador de base de datos. El propio equipo de pgFouine recomienda usar la vía del syslog por que resulta mas eficiente para su software ya que mediante la vía stderr se pudieran encontrar inconsistencias en el informe generado. Nosotros usaremos la vía del syslog.

Abrir con el editor preferido (nano, vi, vim, emacs, edit, etc… ) el archivo de configuración de postgresql: /etc/postgresql/8.4/main/postgresql.conf

ubicar la sección:

#——————————————————————————

# ERROR REPORTING AND LOGGING

#——————————————————————————

descomentar la siguiente linea:

#log_destination = 'stderr'

y dejarla así:

log_destination = 'syslog'

descomentar las siguientes lineas:

#syslog_facility = 'LOCAL0' 
#syslog_ident = 'postgres'

y dejarla asi:

syslog_facility = 'LOCAL0' 
syslog_ident = 'postgres'

descomentar la siguiente linea:

#silent_mode = off

y dejarla así:

silent_mode = on

Activar la recolección de log todas las consultas (querys) ejecutadas

Descomentar la siguiente linea

#log_min_duration_statement = -1

y dejarla asi:

log_min_duration_statement = 0

PD: Luego configurar a su propio gusto.

Descomentar y configurar el formato de cada linea de la bitácora a ser generada:

#log_line_prefix = '%t '

formato de log generado:

2012-06-20 03:15:59 VET LOG: duration: 0.365 ms statement: SELECT version();

y dejarla así (Recomendado por PgFouine Team):

log_line_prefix = '%t [%p]: [%l-1] '

formato de log generado:

2012-06-20 03:26:18 VET [5314]: [1-1] LOG: duration: 0.371 ms statement: SELECT version();

ó dejarla así (Recomendado por mi)

log_line_prefix = '%t [%p]: [%l-1] user:%u, db:%d, host:%h, command:%i '

formato de log generado:

2012-06-20 03:39:57 VET [5551]: [1-1] user:postgres, db:sigesp, host:127.0.0.1, command:SELECT LOG: duration: 53.132 ms statement: SELECT version();

En /etc/rsyslog.conf ó /etc/rsyslog.d/50-default.conf depende de la versión de Debian que estén usando.

agregar la siguiente linea:

local0.* -/var/log/postgresql/postgresql-8.4-main.log

Los siguientes dos pasos se realizan ya que por defecto al usar la opción de syslog, comienzan a llenarse /var/log/syslog y /var/log/messages con logs de postgresql

Donde consigan la siguiente linea (probablemente linea 62):

*.*;auth,authpriv.none -/var/log/syslog

dejarla así:

*.*;auth,authpriv.none,local0 -/var/log/syslog

Donde consigan la siguiente linea (probablemente linea 95):

mail,news.none -/var/log/messages

dejarla así:

local0,mail,news.none -/var/log/messages

Reiniciar los servicios (daemons) de postgresql y syslog

#/etc/init.d/postgresql restart  y    #/etc/init.d/syslogd restart

ó

#service postgresql restart      y       #service rsyslog restart

Generar análisis básico: Genera información de cantidad y porcentaje de tipos de consultas realizadas (select, insert, update, delete), consultas mas ejecutadas, información detallada de consultas que han demorado mas tiempo, etc…

Análisis de logs PostgreSQL

Generar análisis de log:

lenin@i5:~$pgfouine -file postgresql-8.4-main.log > informe-basico-log.html

Generar informe de errores frecuentes: Nos lista todos los errores ocurridos en nuestro manejador de base de datos, indicando la cantidad de ocurrencias de cada uno, así como su fecha y hora.

pgfouine -file /var/log/postgresql/postgresql-8.4-main.log -reports n-mostfrequenterrors > errores-frecuentes.html

Generar historial de todas las consultas ejecutadas: Nos presenta un listado completo de todos los querys ejecutados, es visualmente mas agradable que ver los logs directo de una consola negra, a quien le asignen analizar y verificar linea a linea un archivo de log te agradecerá mucho el uso de PgFouine.

pgfouine -file /var/log/postgresql/postgresql-8.4-main.log -reports history > history.html

Generar Gráficos: PgFouine entre los parametros que acepta se encuentra uno interesante y que nos entrega el analisis de logs en forma grafica. Se debera usar la opción -format html-with-graphs que como el nombre indica generara html con imagenes.

pgfouine -file /var/log/postgresql/postgresql-8.4-main.log -report log-graficos-postgres.html=overall,bytype,hourly -format html-with-graphs

Generar múltiples reportes en un solo análisis: pgFouine nos permite generar nuestros informes de análisis con los bloques o opciones que realmente nos interesan o son pertinentes a nuestra necesidad, así como la cantidad de archivos de informes que le indiquemos.

pgfouine -file /var/log/postgresql/postgresql-8.4-main.log -report todos-queries.html=overall,bytype,slowest,n-mosttime,n-mostfrequent,n-slowestaverage -report todos-x-hora.html=overall,hourly -report todos-error.html=overall,n-mostfrequenterrors -format html-with-graphs

ese comando nos generara los siguientes archivos de análisis de logs:

lenin@i5:/home/lenin/logs# tree

. ├── todos-error.html ├── todos-queries.html ├── todos-x-hora_hourly_queries_per_second.png └── todos-x-hora.html

Generar informes con titulo personalizado: Si es una sola palabra con colocar la misma a continuación del parámetro -title bastara, si son varias palabras encerramos la cadena completa con doble comillas.

lenin@i5:~$ pgfouine -file postgresql-8.4-main.log -title «Analisis Log’s – Tu Base de Datos Libre» > titulo-simple.html

En un terminal vean las paginas del manual para mas información de todas las opciones disponibles $man pgfouine

• Esta receta a sido probada en Debian Squeeze y Debian Wheezy tanto en PostgreSQL 9.1 como 8.4, también en Ubuntu, hay que ser cuidadoso por la cantidad de cambios en archivos de configuración.

• Si no han realizado los pasos correctamente cuando ejecuten el pgFouine les arrojara los siguientes errores:

root@pc:/home/lhernandez# pgfouine -file /var/log/postgresql/postgresql-8.4-main.log > log.html

pgFouine did not find any valid PostgreSQL log line in your log file:

* check that PostgreSQL uses an english locale for logging (lc_messages in your postgresql.conf), * check that you use the -logtype option (syslog, stderr) according to your log file, * if you use syslog and log_line_prefix, check that your log_line_prefix has a trailing space, * if you use stderr, check that your log_line_prefix is of the form ‘%t [%p]: [%l-1] ‘. If you think your log file and your options are correct, please contact the author (gsmet on #postgresql@freenode or guillaume-pg at smet dot org).

También pudiera ocurrir ese error por que el archivo tenia logs anteriores a la configuración con lineas formadas incorrectamente.

• Si les aparece este error:

Error: file /var/log/postgresql/postgresql-8.4-main.log cannot be read

O están colocando mal la ruta o nombre del archivo ó no tienen suficiente permisología para leer el archivo.

• Si el archivo log ocupa mucho espacio en disco ó en donde generan el análisis el hardware es precario pudieran tener este error:

PHP Fatal error:  Allowed memory size of 262144 bytes exhausted (tried to allocate 49152 bytes) in /usr/bin/pgfouine on line 223

La solución es incrementar la cantidad de memoria en el archivo de configuración de PHP5. En /etc/php5/cli/php.ini buscan la variable memory_limit y incrementan su valor.

• Para mas información sobre pgFouine: http://pgfouine.projects.pgfoundry.org

PD: Poco antes de la fecha de la publicación de este articulo descubrí que existe una alternativa nueva, en constante desarrollo y todo indica que es mejor. Desarrollada en Perl. Su nombre es pgBadger. Después que lo pruebe si da los resultados que promete, es posible que escriba sobre ella.