Cómo monitorizar PostgreSQL

Cómo monitorizar PostgreSQL con la ayuda de Pandora FMS

PostgreSQL es un sistema gestor de bases de datos (SGBD) de gran estabilidad y potencia que permite manejar una cantidad de datos muy elevada. En sus más de 16 años de desarrollo activo por una comunidad de desarrolladores que trabajan de forma desinteresada, altruista y libre, ha conseguido convertirse en un SGBD de referencia a la altura de otras soluciones propietarias.

Para conseguir que el software funcione al máximo rendimiento y aproveche mejor los recursos, es fundamental optimizar sus parámetros. Pero como siempre, la optimización sin monitorización no sirve de nada.

Sin monitorización, jamás podrá descubrir reducciones del rendimiento general o detectar el comportamiento anómalo de un elemento, por lo que los errores podrían producirse en su base de datos sin que nada lo avisase y, cuando se diese cuenta del error, podría ser demasiado tarde.

Un sistema de monitorización le permitirá solucionar cualquier problema incipiente que se produzca en su empresa en un corto espacio de tiempo ya que le indicará dónde se ha producido el problema y por qué. Con una buena optimización y monitorización de PostgreSQL, esta base de datos será apta incluso para grandes empresas.

Todo ello dará lugar a una mejor experiencia de uso y fiabilidad, e incluso reducirá el tiempo invertido en el mantenimiento del propio PostgreSQL y herramientas relacionadas y, por lo tanto, ahorrará costes.

OBTENCIÓN DE DATOS DE INTERÉS

– Herramientas del sistema

Existen varias herramientas en Linux/Unix que nos permiten recoger información útil sobre el impacto de PostgreSQL en el sistema. A continuación destacamos algunas de ellas.

*ps

Este programa, incluido en casi todos los sistemas Linux/Unix, nos permitirá obtener información sobre el uso de CPU, la memoria RAM de los procesos de PostgreSQL, las conexiones de clientes al servidor y su actividad, entre otras cosas.

Por ejemplo, con una llamada como la siguiente:

ps aux | grep “postgres” | grep -v “grep”

Conseguiríamos esta información:

postgres  1289  0.0  0.3  51676  7900 ?   S    11:31   0:01 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf

postgres  1303  0.0  0.0  21828  1148 ?   Ss   11:31   0:00 postgres: logger process

postgres  1305  0.0  0.0  51676  1568 ?   Ss   11:31   0:02 postgres: writer process

postgres  1306  0.0  0.0  51676  1324 ?   Ss   11:31   0:02 postgres: wal writer process

postgres  1307  0.0  0.1  52228  2452 ?   Ss   11:31   0:00 postgres: autovacuum launcher process

postgres  1308  0.0  0.0  22016  1432 ?   Ss   11:31   0:00 postgres: stats collector process

La tercera y la cuarta columna representan el porcentaje del uso de la CPU y de la memoria RAM, respectivamente. Es importante prestar atención al total de estos dos datos, ya que un consumo muy elevado de CPU y de memoria RAM pueden ocasionar lentitud y caídas del servidor.

Es importante señalar que cuando aparecen datos al realizar esa llamada, significa que el servidor PostgreSQL se encuentra activo.

*Vmstat:

Este programa, también incluido normalmente en las instalaciones de sistemas Linux/Unix, nos permitirá obtener datos relacionados a la memoria común y SWAP, a la entrada y salida, al sistema y a la CPU. La primera línea siempre muestra una media desde el último reinicio.

---

---

Para obtener una vista de los datos actuales en un intervalo de tiempo, y con un número de repeticiones definidas, se puede realizar una llamada como la siguiente, donde el primer argumento es el tiempo en segundos que medirá cada línea y el segundo es el número líneas que aparecerán:

vmstat 1 5

Producirá información de este tipo:

procs ———–memory———- —swap– —–io—- -system– —-cpu—-

r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa

0  0      0 188972 179496 669392    0    0    24    27  525  461  1  1 96  1

0  0      0 188972 179496 669392    0    0     0     0 1449 1689  2  2 97  0

0  0      0 188972 179496 669392    0    0     0     0 2403 1605  2  2 97  0

2  0      0 188972 179500 669392    0    0     0    16 2041 2502  2  3 94  2

0  0      0 188972 179500 669392    0    0     0     0 1599 1868  1  1 98  0

La información que resultará más útil para el tema que nos incumbe se encuentra en la última columna, “wa”, que muestra el tiempo de espera por operaciones de Entrada/Salida. Será conveniente vigilar que no supere un valor de 15-20, ya que el sistema se puede “colapsar” debido a un excesivo acceso al disco.

También será útil comprobar qué columnas “si” y “so” del uso de la memoria SWAP están a 0, ya que su uso significa que la memoria RAM del servidor, probablemente, esté saturada.

*netstat

Este programa, que muestra información sobre el estado de las conexiones de red, nos permitirá comprobar el número de conexiones en espera que tiene el equipo que aloja el servidor PostgreSQL, con una consulta como la siguiente:

netstat -ntu | grep “TIME_WAIT” | wc -l

Un número alto de conexiones en espera puede indicar problemas de respuesta del servidor a consecuencia de otros problemas.

**Vistas y tablas internas de PostgreSQL

PostgreSQL dispone de un subsistema llamado Statistics Collector que se encarga de recopilar y reportar información sobre la actividad del servidor. Para desactivarlo y así evitar esa pérdida de rendimiento debemos modificar los parámetros ‘track_counts’, ‘track_functions’ y ‘track_activities’ en el fichero ‘postgresql.conf’. Sin embargo, antes de desactivarlo debe valorar si le merece la pena ya que esta acción eliminará la posibilidad de conseguir datos muy útiles.

Para acceder a estos datos, será necesario realizar consultas SQL a varias vistas predefinidas del sistema. Algunas de las más importantes son:

*pg_database

Guarda información sobre las bases de datos disponibles. Una fila por base de datos.

Esta tabla resulta interesante porque nos permitirá obtener el tamaño (en bytes) del conjunto de las bases de datos. La función “pg_database_size(oid)” devuelve
el tamaño de la base de datos cuyo identificador es pasado como argumento. Al llamarla sobre esta tabla, mostrará el tamaño de cada una, fila a fila, y solo quedará sumar los resultados para saber si hay espacio de sobra en el disco:

SELECT SUM(pg_database_size(oid)) FROM pg_database;

*pg_locks

Muestra información sobre los bloqueos activos en el servidor. Contiene una fila por cada bloqueo, en cada una de las cuales aparece información sobre el tipo de bloqueo, sobre dónde se ha producido, etc.

Como los SGBD permiten el acceso a varios usuarios al mismo tiempo, puede que alguno de ellos quiera hacer cambios en un elemento que se encuentra en uso por otro. Para evitar esta situación, se le añade un bloqueo a los elementos que se encuentran en uso en alguna transacción. Para conocer el número de bloqueos, solo debe realizar la siguiente consulta:

SELECT COUNT(*) FROM pg_locks;

Un número elevado de bloqueos indica que hay una mayor probabilidad de que dos procesos o más intenten acceder al mismo recurso, lo que causa que tengan que quedar a la espera de que el recurso sea liberado y el rendimiento del servidor disminuya.

*pg_stat_activity: Muestra información sobre procesos del servidor. Contiene una fila por proceso.

De esta vista podemos obtener el número de usuarios conectados con la consulta:

SELECT COUNT(usename) FROM pg_stat_activity;

La cantidad de usuarios concurrentes que puede gestionar el sistema dependerá sobre todo del hardware y de la conexión de la máquina en la que se encuentre el servidor. Es conveniente realizar pruebas de estrés para saber cuál es ese límite, y estar alertado si se está alcanzando.

*pg_stat_database

Muestra información sobre el uso de las bases de datos. Contiene una fila por cada base de datos, en las que se muestra el número de procesos conectados, los commits y rollbacks, información sobre los bloques leídos en el disco y la caché, así como el número y tipo de operaciones realizadas con filas de cada una.

Con esta tabla tan interesante podremos conocer, por ejemplo, si la memoria que actúa de caché esta funcionando correctamente. Es posible sacar un ratio de uso de la memoria caché respecto a la común con esta consulta:

SELECT SUM(blks_hit) / SUM(blks_read) FROM pg_stat_database;

A mayor ratio, mayor velocidad a la hora de recoger datos de las tablas.

*pg_stat_bgwriter

Sólo contiene una fila y muestra datos del background writer, relacionados con los checkpoints y los buffers.

Mediante la interpretación de datos, un usuario avanzado puede sacarle partido a esta información al cambiar ciertas propiedades para ganar rendimiento. Por ejemplo, se puede realizar esta consulta:

SELECT maxwritten_clean FROM pg_stat_bgwriter;

Nos devolverá el número de veces que al ocurrir un checkpoint, el bgwriter ha sobrescrito el máximo número de buffers que tenía permitido en esa ronda. Un número bajo no da problemas, pero si los valores son demasiado altos, incrementar el valor ‘bgwriter_lru_maxpages’ (por defecto 100) en ‘postgresql.conf’ o en la línea de comandos, mejorará el rendimiento.

También es posible mejorar el rendimiento si observamos qué procesos externos se ven obligados a pedir más espacio en los buffers una gran cantidad de veces.

Se puede ver con esta consulta:

SELECT buffers_backend FROM pg_stat_bgwriter;

Para hacer que se amplíe el espacio antes de que un proceso necesite ampliarlo se pueden incrementar los valores ‘bgwriter_lru_maxpages’ y ‘bgwriter_lru_multiplier’, y reducir el valor ‘bgwriter_delay’.

El resto del listado de tablas y funciones se pueden ver aquí: http://www.postgresql.org/docs/9.1/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE (varían dependiendo de la versión de PostgreSQL)

Monitorización de PostgreSQL con Pandora FMS

Posiblemente, algunos usuarios prefieran realizar chequeos manualmente para asegurar el correcto funcionamiento de su servidor PostgreSQL. Sin embargo, para aquellos usuarios que necesiten llevar un control estricto del funcionamiento y evolución del sistema, un sistema de monitorización es la solución perfecta para ampliar las funcionalidades y las posibilidades de Postgress.

En el mercado existe una amplia oferta de sistemas de monitorización. Como fabricantes y expertos de Pandora FMS recomendamos monitorizar PostgreSQL con Pandora FMS junto a un plugin para PostgreSQL.

El primer paso para desplegar la monitorización es descargar e instalar Pandora FMS. Puede encontrar una imagen ISO aquí:

https://pandorafms.com/community/get-started/

Una vez instalado Pandora FMS, es necesario instalar un agente en la máquina donde se ejecuta el servidor de cache PostgreSQL. Podrá encontrar agentes para diferentes distribuciones Linux en el siguiente enlace:

http://sourceforge.net/projects/pandora/files/Pandora%20FMS%204.0.3/

Para no alargar el artículo, no describiremos el proceso de instalación de cada componente de forma detallada, ya que se pueden consultar todos los pasos en la documentación oficial de Pandora FMS:

https://pandorafms.com/manual/es/documentation/02_installation/01_installing

A continuación será necesario descargar el plugin para monitorizar PostgreSQL, puede conseguirlo en el siguiente enlace:

http://pandorafms.com/index.php?sec=Library&sec2=repository&lng=es&action=view_PUI&id_PUI=553

Para instalar el plugin, solo debe descomprimir el archivo descargado y mover los archivos Pandora_PostgreSQL.pl y Pandora_PostgreSQL.conf a la carpeta de plugins del agente instalado en el servidor de PostgreSQL a monitorizar. Puede usar los siguientes comandos:

# unzip Pandora_PostgreSQL.zip

# cp Pandora_PostgreSQL.* /etc/pandora/plugins

Una vez que el plugin se encuentre en la ubicación correcta, tenemos que configurarlo. Es necesario añadir el nombre de usuario y la contraseña de un usuario con permisos totales de lectura y autorización para el logueo con contraseña desde la terminal. También habrá que añadir la dirección del host e indicar un directorio para los datos temporales. Una vez hecho esto, el plugin será completamente funcional, ya que incluye una gran cantidad de verificaciones por defecto.

Es posible añadir o eliminar chequeos de la configuración, o incluso personalizar los ya existentes. Existen tres tipos de chequeos: del sistema, consultas generales a la información de PostgreSQL y consultas libres. Para eliminar alguno en concreto, basta con borrarlo o comentarlo añadiendo un # al inicio de cada línea del chequeo.
Para añadir uno nuevo o personalizarlo es necesario comprender como funcionan los datos que aparecen allí:

*Chequeos del sistema:

check_begin

check_pgsql_service

check_end

Estos chequeos están predefinidos y no se pueden añadir otros nuevos, pero es posible eliminarlos o personalizarlos de una forma que se explicará más adelante.

*Consultas generales:

check_begin

check_name (Nombre del chequeo)

pgsql_column (Columna de la que se quiera extraer información)

pgsql_table (Tabla de la que se desea extraer información)

check_end

Estos chequeos funcionarán cuando la información a obtener sea de tipo numérico, en ese caso devolverán la suma de todos los datos encontrados en la columna de la tabla indicada.

*Consultas libres:

check_begin

check_name (Nombre del chequeo)

pgsql_sql (Consulta SQL a realizar)

check_end

Estos chequeos funcionarán igual que las consultas generales en el caso de que se busquen datos numéricos. En el caso de que se indique que el chequeo va a recoger información de tipo texto, devolverá el primer dato de texto encontrado.

Cada chequeo debe encontrarse entre una línea “check_begin” y otra “check_end”, y está formado por los datos necesarios para su ejecución indicados anteriormente y otros opcionales de personalización:

-post_condition: Compara el resultado obtenido con el aquí indicado y si se cumple, causa la ejecución de ‘post_status’, ‘post_execution’ o ambos si se encuentran definidos.
Ej: post_condition > 10

-post_status: Cambia el estado del chequeo al indicado en el caso de que la condición de ‘post_condition’ se haya cumplido.
Ej: post_status WARNING

-post_execution: Ejecuta la orden de terminal indicada antes de ‘_DATA_’ en el caso de que la condición de ‘post_condition’ se haya cumplido.
Ej: post_execution snmptrap -v 1 -c public 192.168.5.2 1.3.6.1.2.1.2 192.168.50.124 6 666 1233433 .1.3.6.1.2.1.2.2.1.1.6 i _DATA_

-data_delta: Indicando esta propiedad, la información obtenida por el chequeo es tratada como “delta”, es decir, recoge el incremento para observar el ritmo del aumento que se va produciendo.

-module_type: Cambia el tipo de dato que devolverá el chequeo al indicado.
Ej: module_type async_string

Una vez la configuración se haya completado de acuerdo a nuestras necesidades, habrá que añadir el plugin al archivo de configuración del agente de Pandora FMS, que se encuentra por defecto en la ruta “/etc/pandora/pandora_agent.conf”, para que este pueda ejecutarlo periódicamente. Si los archivos del plugin se han añadido a la carpeta de plugins, bastará con esta línea:

module_plugin /etc/pandora/plugins/Pandora_PostgreSQL.pl /etc/pandora/plugins/Pandora_PostgreSQL.conf

Tras terminar este proceso, Pandora FMS habrá sido instalado en sus sistemas y podrá tener la seguridad de que será informado de cualquier fallo que se produzca en sus sistemas antes de que se convierta en un problema mayor.

Antes de despedirnos, ¿quieres conocer mejor qué es lo que Pandora FMS puede ofrecerte? Descúbrelo entrando aquí: https://pandorafms.com/es

O si tienes que monitorizar más de 100 dispositivos también puedes disfrutar de una DEMO GRATUITA de 30 días de Pandora FMS Enterprise. Consíguela aquí.

Además, recuerda que si tus necesidades de monitorización son más limitadas tienes a tu disposición la versión OpenSource de Pandora FMS. Encuentra más información aquí: https://pandorafms.org/es/

No dudes en enviar tus consultas. ¡El equipazo de Pandora FMS estará encantado de atenderte!