pgFouine – Análisis de logs PostgreSQL en Debian

analisis-log-postgres-pgfouine

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 via stderr se pudieran encontrar inconsistencias en el informe generado. Nosotros usaremos la via del syslog.

1.- Configurar correctamente los log de PostgreSQL

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 asi:

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 asi:

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();

2.- Configurar syslog para que registre la bitácora de PostgreSQL

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

3.- Ejemplos ó opciones de uso de la herramienta

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…

Generar análisis de log:

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

analisis-log-postgres-pgfouine

Ejemplo resumido del informe básico generado por pgFouine

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

frequent-errors-postgres

pgFouine – Errores frecuentes

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

Hourly-statistics-pgfouine

pgFouine – Estadísticas por dia y hora

log-graficos-postgres_hourly_select_queries

log-graficos-postgres_hourly_select_queries

log-graficos-postgres_hourly_all_queries

log-graficos-postgres_hourly_all_queries

log-graficos-postgres_hourly_queries_per_second

log-graficos-postgres_hourly_queries_per_second

log-graficos-postgres_hourly_write_queries

log-graficos-postgres_hourly_write_queries

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

pgfouine-titulo

pgFouine – Titulo personalizado

Para mas información de los tipos de reportes que se pueden generar:

http://pgfouine.projects.pgfoundry.org/reports.html

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

Consideraciones o posibles errores
  • 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.

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.

Anuncios

2 comentarios en “pgFouine – Análisis de logs PostgreSQL en Debian

  1. Saludos hice los pasos en dos servidores , en uno funciona sin problemas y en el otro en el log parte del log sale esto #011#011#011#011#011#011 y los demas datos estan sin problemas chequee y parece todo bien habra algo que me salte

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s