Cómo analizar las estadísticas de una base de datos PostgreSQL administrada mediante Elastic Stack en Ubuntu 18.04

El autor seleccionó el Fondo de Código Libre y Abierto para recibir una donación como parte del programa Write for DOnations .

Introducción

La monitorización de bases de datos es el proceso continuo de seguimiento sistemático de diversas métricas que muestran el rendimiento de la base de datos. Al observar los datos de rendimiento, puede obtener información valiosa e identificar posibles cuellos de botella, así como encontrar formas adicionales de mejorar el rendimiento de la base de datos. Estos sistemas suelen implementar alertas, que notifican a los administradores cuando algo sale mal. Las estadísticas recopiladas se pueden utilizar no solo para mejorar la configuración y el flujo de trabajo de la base de datos, sino también los de las aplicaciones cliente.

La ventaja de utilizar Elastic Stack (pila ELK) para supervisar su base de datos administrada es su excelente compatibilidad con búsquedas y la capacidad de ingerir nuevos datos muy rápidamente. No se destaca por actualizar los datos, pero esta compensación es aceptable para fines de supervisión y registro, donde los datos anteriores casi nunca se modifican. Elasticsearch ofrece un medio poderoso para consultar los datos, que puede utilizar a través de Kibana para comprender mejor cómo se comporta la base de datos en diferentes períodos de tiempo. Esto le permitirá correlacionar la carga de la base de datos con eventos de la vida real para obtener información sobre cómo se utiliza la base de datos.

En este tutorial, importará métricas de base de datos, generadas por el recopilador de estadísticas de PostgreSQL , en Elasticsearch a través de Logstash . Esto implica configurar Logstash para extraer datos de la base de datos mediante el conector JDBC de PostgreSQL para enviarlos a Elasticsearch para indexarlos inmediatamente después. Los datos importados se pueden analizar y visualizar más tarde en Kibana. Luego, si su base de datos es nueva, utilizará pgbench , una herramienta de evaluación comparativa de PostgreSQL, para crear visualizaciones más interesantes. Al final, tendrá un sistema automatizado que extrae estadísticas de PostgreSQL para su posterior análisis.

Prerrequisitos

• Un servidor Ubuntu 18.04 con al menos 8 GB de RAM, privilegios de root y una cuenta secundaria que no sea root. Puedes configurarlo siguiendo esta guía de configuración inicial del servidor . Para este tutorial, el usuario que no es root es sammy.

• Java 8 instalado en su servidor. Para obtener instrucciones de instalación, visite Cómo instalar Java apten Ubuntu 18.04 .

• Nginx instalado en su servidor. Para obtener una guía sobre cómo hacerlo, consulte Cómo instalar Nginx en Ubuntu 18.04 .

• Elasticsearch y Kibana instalados en su servidor. Complete los dos primeros pasos del tutorial Cómo instalar Elasticsearch, Logstash y Kibana (Elastic Stack) en Ubuntu 18.04 .

• Una base de datos administrada PostgreSQL 13 proporcionada desde DigitalOcean con información de conexión disponible. Asegúrese de que la dirección IP de su servidor esté en la lista blanca. Para obtener una guía sobre cómo crear una base de datos administrada PostgreSQL mediante el Panel de control de DigitalOcean, visite la Guía de inicio rápido de PostgreSQL .

Paso 1: Configuración de Logstash y el controlador JDBC de PostgreSQL

En esta sección, instalará Logstash y descargará el controlador JDBC de PostgreSQL para que Logstash pueda conectarse a su base de datos administrada.

Comience instalando Logstash con el siguiente comando:

sudo apt install logstash -y

Una vez instalado Logstash, habilite el servicio para que se inicie automáticamente al arrancar:

sudo systemctl enable logstash

Logstash está escrito en Java, por lo que para conectarse a PostgreSQL es necesario que la biblioteca PostgreSQL JDBC (Java Database Connectivity) esté disponible en el sistema en el que se ejecuta. Debido a una limitación interna, Logstash cargará correctamente la biblioteca solo si se encuentra en el /usr/share/logstash/logstash-core/lib/jarsdirectorio donde almacena las bibliotecas de terceros que utiliza.

Dirígete a la página de descarga de la biblioteca JDBC y copia el enlace a la última versión. Luego, descárgala curlejecutando el siguiente comando:

sudo curl https://jdbc.postgresql.org/download/postgresql-42.3.3.jar -o /usr/share/logstash/logstash-core/lib/jars/postgresql-jdbc.jar

Al momento de escribir este artículo, la última versión de la biblioteca era 42.3.3, con Java 8 como la versión de entorno de ejecución compatible. Asegúrese de descargar la última versión; emparejándola con la versión de Java correcta que tanto JDBC como Logstash admitan. Logstash almacena sus archivos de configuración en /etc/logstash/conf.d, y se almacena a su vez en /usr/share/logstash/bin.

Ha instalado Logstash apty ha descargado la biblioteca JDBC de PostgreSQL para que Logstash pueda usarla para conectarse a su base de datos administrada. En el siguiente paso, configurará Logstash para extraer datos estadísticos de ella.

Paso 2: configuración de Logstash para extraer estadísticas

En esta sección, configurará Logstash para extraer métricas de su base de datos PostgreSQL administrada.

Configurará Logstash para supervisar tres bases de datos del sistema en PostgreSQL, a saber:

• pg_stat_database: proporciona estadísticas sobre cada base de datos, incluido su nombre, número de conexiones, transacciones, reversiones, filas devueltas al consultar la base de datos, bloqueos, etc. Tiene un stats_resetcampo que especifica cuándo se restablecieron las estadísticas por última vez.
• pg_stat_user_tables: proporciona estadísticas sobre cada tabla creada por el usuario, como el número de filas insertadas, eliminadas y actualizadas.
• pg_stat_user_indexes: recopila datos sobre todos los índices en las tablas creadas por el usuario, como la cantidad de veces que se ha escaneado un índice en particular.

Almacenará la configuración para indexar las estadísticas de PostgreSQL en Elasticsearch en un archivo con el nombre postgresql.confen el /etc/logstash/conf.ddirectorio donde Logstash almacena los archivos de configuración. Cuando se inicia como servicio, los ejecutará automáticamente en segundo plano.

Crea postgresql.confusando tu editor favorito (por ejemplo, nano):

sudo nano /etc/logstash/conf.d/postgresql.conf

Añade las siguientes líneas:

/etc/logstash/conf.d/postgresql.conf

input {        # pg_stat_database        jdbc {                jdbc_driver_library = ""                jdbc_driver_class = "org.postgresql.Driver"                jdbc_connection_string = "jdbc:postgresql://host:port/defaultdb"                jdbc_user = "username"                jdbc_password = "password"                statement = "SELECT * FROM pg_stat_database"                schedule = "* * * * *"                type = "pg_stat_database"        }        # pg_stat_user_tables        jdbc {                jdbc_driver_library = ""                jdbc_driver_class = "org.postgresql.Driver"                jdbc_connection_string = "jdbc:postgresql://host:port/defaultdb"                jdbc_user = "username"                jdbc_password = "password"                statement = "SELECT * FROM pg_stat_user_tables"                schedule = "* * * * *"                type = "pg_stat_user_tables"        }        # pg_stat_user_indexes        jdbc {                jdbc_driver_library = ""                jdbc_driver_class = "org.postgresql.Driver"                jdbc_connection_string = "jdbc:postgresql://host:port/defaultdb"                jdbc_user = "username"                jdbc_password = "password"                statement = "SELECT * FROM pg_stat_user_indexes"                schedule = "* * * * *"                type = "pg_stat_user_indexes"        }}output {        elasticsearch {                hosts = "http://localhost:9200"                index = "%{type}"        }}

Recuerde reemplazar hostcon su dirección de host, portcon el puerto al que puede conectarse a su base de datos, usernamecon el nombre de usuario de la base de datos y passwordcon su contraseña. Todos estos valores se pueden encontrar en el Panel de Control de su base de datos administrada.

En esta configuración, se definen tres entradas JDBC y una salida Elasticsearch. Las tres entradas extraen datos de las bases de datos pg_stat_database, pg_stat_user_tablesy pg_stat_user_indexes, respectivamente. Todas establecen el jdbc_driver_libraryparámetro en una cadena vacía, porque la biblioteca JDBC de PostgreSQL está en una carpeta que Logstash carga automáticamente.

Luego, establecen el jdbc_driver_class, cuyo valor es específico de la biblioteca JDBC, y proporcionan un jdbc_connection_string, que detalla cómo conectarse a la base de datos. La jdbc:parte significa que es una conexión JDBC, mientras que postgres://indica que la base de datos de destino es PostgreSQL. A continuación, vienen el host y el puerto de la base de datos, y después de la barra diagonal también especifica una base de datos a la que conectarse; esto se debe a que PostgreSQL requiere que esté conectado a una base de datos para poder emitir consultas. Aquí, se establece en la base de datos predeterminada que siempre existe y no se puede eliminar, acertadamente llamada defaultdb.

A continuación, se establece un nombre de usuario y una contraseña para el usuario a través del cual se accederá a la base de datos. El statementparámetro contiene una consulta SQL que debe devolver los datos que se desean procesar; en esta configuración, se seleccionan todas las filas de la base de datos correspondiente.

El scheduleparámetro acepta una cadena en sintaxis cron que define cuándo Logstash debe ejecutar esta entrada; si la omite por completo, Logstash la ejecutará solo una vez. Si especifica * * * * *, como lo ha hecho aquí, le indicará a Logstash que la ejecute cada minuto. Puede especificar su propia cadena cron si desea recopilar datos en diferentes intervalos.

Solo hay una salida, que acepta datos de tres entradas. Todas envían datos a Elasticsearch, que se ejecuta localmente y es accesible en http://localhost:9200. El indexparámetro define a qué índice de Elasticsearch enviará los datos y su valor se pasa desde el typecampo de la entrada.

Cuando haya terminado de editar, guarde y cierre el archivo.

Ha configurado Logstash para recopilar datos de varias tablas estadísticas de PostgreSQL y enviarlos a Elasticsearch para su almacenamiento e indexación. A continuación, ejecutará Logstash para probar la configuración.

Paso 3: Prueba de la configuración de Logstash

En esta sección, probará la configuración ejecutando Logstash para verificar que extraiga los datos correctamente. Luego, hará que esta configuración se ejecute en segundo plano configurándola como una canalización de Logstash.

Logstash permite ejecutar una configuración específica al pasar su ruta de archivo al -fparámetro. Ejecute el siguiente comando para probar su nueva configuración del último paso:

sudo /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/postgresql.conf

Puede que transcurra algún tiempo hasta que se muestre algún resultado, que será similar a esto:

OutputUsing bundled JDK: /usr/share/logstash/jdkOpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.WARNING: Could not find logstash.yml which is typically located in $LS_HOME/config or /etc/logstash. You can specify the path using --path.settings. Continuing using the defaultsCould not find log4j2 configuration at path /usr/share/logstash/config/log4j2.properties. Using default config which logs errors to the console[INFO ] 2022-02-24 08:49:36.664 [main] runner - Starting Logstash {"logstash.version"="7.17.0", "jruby.version"="jruby 9.2.20.1 (2.5.8) 2021-11-30 2a2962fbd1 OpenJDK 64-Bit Server VM 11.0.13+8 on 11.0.13+8 +indy +jit [linux-x86_64]"}[INFO ] 2022-02-24 08:49:36.671 [main] runner - JVM bootstrap flags: [-Xms1g, -Xmx1g, -XX:+UseConcMarkSweepGC, -XX:CMSInitiatingOccupancyFraction=75, -XX:+UseCMSInitiatingOccupancyOnly, -Djava.awt.headless=true, -Dfile.encoding=UTF-8, -Djruby.compile.invokedynamic=true, -Djruby.jit.threshold=0, -Djruby.regexp.interruptible=true, -XX:+HeapDumpOnOutOfMemoryError, -Djava.security.egd=file:/dev/urandom, -Dlog4j2.isThreadContextMapInheritable=true][INFO ] 2022-02-24 08:49:36.700 [main] settings - Creating directory {:setting="path.queue", :path="/usr/share/logstash/data/queue"}[INFO ] 2022-02-24 08:49:36.710 [main] settings - Creating directory {:setting="path.dead_letter_queue", :path="/usr/share/logstash/data/dead_letter_queue"}[WARN ] 2022-02-24 08:49:36.992 [LogStash::Runner] multilocal - Ignoring the 'pipelines.yml' file because modules or command line options are specified[INFO ] 2022-02-24 08:49:37.018 [LogStash::Runner] agent - No persistent UUID file found. Generating new UUID {:uuid="bfd27cc5-f2d0-4b19-8870-a125586135ed", :path="/usr/share/logstash/data/uuid"}[INFO ] 2022-02-24 08:49:38.085 [Api Webserver] agent - Successfully started Logstash API endpoint {:port=9600, :ssl_enabled=false}[INFO ] 2022-02-24 08:49:39.284 [Converge PipelineAction::Createmain] Reflections - Reflections took 68 ms to scan 1 urls, producing 119 keys and 417 values...[INFO ] 2022-02-24 08:50:03.102 [Ruby-0-Thread-34@[d39f109727b9e1a2b881639e708f21ce1d65378257869071cbed233a3946468d]jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/scheduler.rb:77] jdbc - (0.194969s) SELECT * FROM pg_stat_user_tables[INFO ] 2022-02-24 08:50:03.104 [Ruby-0-Thread-32@[bd7d166b46e4ae8c53b4d498eaec7d53de881ea0f8a9bdfb08f574f9cbd3a4f6]jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.192893s) SELECT * FROM pg_stat_database[INFO ] 2022-02-24 08:50:03.104 [Ruby-0-Thread-33@[fc2c0b0065c00ee9f942e75f35edf001a9e285c77ba7cf4ae127886e43c140fc]jdbc__scheduler_worker-00: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:284] jdbc - (0.197744s) SELECT * FROM pg_stat_user_indexes...

Si Logstash no muestra ningún error y registra que ha SELECTejecutado correctamente filas de las tres bases de datos, las métricas de la base de datos se enviarán a Elasticsearch. Si recibe un error, vuelva a verificar todos los valores en el archivo de configuración para asegurarse de que la máquina en la que está ejecutando Logstash pueda conectarse a la base de datos administrada.

Logstash seguirá importando los datos en momentos específicos. Puedes detenerlo de forma segura presionando CTRL+C.

Si creó una nueva base de datos solo para este tutorial, solo doadmindebe estar presente un usuario ( ). Esto significa que las tablas pg_stat_user_tablesy pg_stat_user_indexesestarán vacías (porque no hay otros usuarios presentes que hayan creado tablas) y no aparecerán en Elasticsearch.

Como se mencionó anteriormente, cuando se inicia como servicio, Logstash ejecuta automáticamente todos los archivos de configuración que encuentra /etc/logstash/conf.den segundo plano. Ejecute el siguiente comando para iniciarlo como servicio:

sudo systemctl start logstash

En este paso, ejecutaste Logstash para comprobar si puede conectarse a tu base de datos y recopilar datos. A continuación, visualizarás y explorarás algunos de los datos estadísticos en Kibana.

Paso 4: Exploración de datos importados en Kibana

En esta sección, explorará y visualizará los datos estadísticos que describen el rendimiento de su base de datos en Kibana.

En su navegador web, acceda al dominio en el que expuso Kibana como parte del requisito previo. Verá la página de bienvenida predeterminada:

Antes de explorar los datos que Logstash envía a Elasticsearch, primero deberá agregar el pg_stat_databaseíndice a Kibana. Para ello, primero presione Explorar por mi cuenta y luego abra el menú de hamburguesas en la esquina superior izquierda. En Análisis , haga clic en Descubrir .

Luego, Kibana le solicitará que cree un nuevo patrón de índice:

Haga clic en Crear patrón de índice . Verá un formulario para crear un nuevo Patrón de índice . Los patrones de índice en Kibana brindan una manera de extraer datos de varios índices de Elasticsearch a la vez y se pueden usar para explorar solo un índice.

A la derecha se muestran los tres índices a los que Logstash ha enviado estadísticas. Escriba en pg_stat_databaseel campo de texto Nombre@timestamp y seleccione en el menú desplegable el campo Marca de tiempo . Cuando haya terminado, presione el botón Crear patrón de índice que se encuentra debajo.

Para crear y ver visualizaciones existentes, abra el menú de hamburguesas. En Analytics , seleccione Dashboard . Cuando se cargue, haga clic en Create new dashboard y, a continuación, presione Create visualization para comenzar a crear uno nuevo:

El panel del lado izquierdo proporciona una lista de valores que Kibana puede utilizar para dibujar la visualización, que se mostrará en la parte central de la pantalla. En el lado superior derecho de la pantalla se encuentra el selector de rango de fechas. Si el @timestampcampo se utiliza en la visualización, Kibana solo mostrará los datos que pertenecen al intervalo de tiempo especificado en el selector de rango.

Ahora visualizará la cantidad promedio de tuplas de datos INSERTejecutadas en minutos en el intervalo dado. En el menú desplegable de la parte principal de la página, seleccione Línea en la sección Línea y áreatup_inserted . Luego, busque el campo en la lista de la izquierda y arrástrelo a la parte central. Pronto verá una visualización lineal de la cantidad media de INSERTconsultas a lo largo del tiempo. Si su base de datos es nueva y no se utiliza, no verá nada todavía. Sin embargo, en todos los casos, verá una representación precisa del uso de la base de datos:

En el lado derecho, puedes configurar cómo se procesan los ejes horizontal y vertical. Allí, puedes configurar el eje vertical para que muestre el valor Promedio en lugar de la Mediana , presionando en el eje mostrado:

Puede seleccionar una función diferente o proporcionar la suya propia:

El gráfico se actualizará inmediatamente con los valores actualizados.

En este paso, aprendió a visualizar algunos de los datos estadísticos de PostgreSQL usando Kibana.

Paso 5 (opcional): evaluación comparativa con pgbench

Si aún no ha trabajado en su base de datos fuera de este tutorial, puede completar este paso para crear visualizaciones más interesantes usando pgbench para comparar su base de datos. pgbench ejecutará los mismos comandos SQL una y otra vez, simulando el uso de una base de datos del mundo real por parte de un cliente real.

Primero deberá instalar pgbench ejecutando el siguiente comando:

sudo apt install postgresql-contrib -y

Debido a que pgbench insertará y actualizará datos de prueba, deberá crear una base de datos independiente para ello. Para ello, diríjase a la pestaña Usuarios y bases de datos en el Panel de control de su base de datos administrada y desplácese hacia abajo hasta la sección Bases de datos . Escriba pgbenchel nombre de la nueva base de datos y luego presione Guardar . Pasará este nombre, así como la información del host, el puerto y el nombre de usuario a pgbench.

Antes de ejecutarlo realmente pgbench, deberá ejecutarlo con el -iindicador para inicializar su base de datos:

pgbench -h host -p port -U username -i pgbench

Deberás reemplazarlo hostpor la dirección de tu host, portel puerto al que puedes conectarte a tu base de datos y usernameel nombre de usuario de la base de datos. Puedes encontrar todos estos valores en el Panel de control de tu base de datos administrada.

Tenga en cuenta que pgbenchno tiene un argumento de contraseña; en su lugar, se le solicitará cada vez que lo ejecute.

El resultado será similar al siguiente:

OutputNOTICE:  table "pgbench_history" does not exist, skippingNOTICE:  table "pgbench_tellers" does not exist, skippingNOTICE:  table "pgbench_accounts" does not exist, skippingNOTICE:  table "pgbench_branches" does not exist, skippingcreating tables...100000 of 100000 tuples (100%) done (elapsed 0.16 s, remaining 0.00 s)vacuum...set primary keys...done.

pgbenchSe crearon cuatro tablas que se usarán para realizar evaluaciones comparativas y se completaron con algunas filas de ejemplo. Ahora podrá ejecutar evaluaciones comparativas.

Los dos argumentos más importantes que limitan el tiempo de ejecución del benchmark son -t, que especifica la cantidad de transacciones que se completarán, y -T, que define durante cuántos segundos debe ejecutarse el benchmark. Estas dos opciones son mutuamente excluyentes. Al final de cada benchmark, recibirá estadísticas, como la cantidad de transacciones por segundo ( tps).

Ahora, inicie una prueba comparativa que durará 30 segundos ejecutando el siguiente comando:

pgbench -h host -p port -U username pgbench -T 30

El resultado será similar a esto:

Outputstarting vacuum...end.transaction type: builtin: TPC-B (sort of)scaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 30 snumber of transactions actually processed: 11991latency average = 2.502 mstps = 399.664353 (including connections establishing)tps = 399.987202 (excluding connections establishing)

En este resultado, verá la información general sobre el punto de referencia, como la cantidad total de transacciones ejecutadas. El efecto de estos puntos de referencia es que las estadísticas que Logstash envía a Elasticsearch reflejarán esa cantidad, lo que a su vez hará que las visualizaciones en Kibana sean más interesantes y más cercanas a los gráficos del mundo real. Puede ejecutar el comando anterior unas cuantas veces más y posiblemente modificar la duración.

Cuando hayas terminado, dirígete a Kibana y pulsa Actualizar en la esquina superior derecha. Ahora verás una línea diferente a la anterior, que muestra la cantidad de INSERTsegundos. Puedes cambiar el intervalo de tiempo de los datos que se muestran modificando los valores en el selector ubicado sobre el botón Actualizar. Así es como puede verse el gráfico después de varios puntos de referencia de duración variable:

Ha utilizado pgbench para evaluar su base de datos y ha evaluado los gráficos resultantes en Kibana.

Conclusión

Ahora tiene la pila Elastic instalada en su servidor y configurada para extraer datos estadísticos de su base de datos PostgreSQL administrada de forma regular. Puede analizar y visualizar los datos utilizando Kibana o algún otro software adecuado, que lo ayudará a recopilar información valiosa y correlaciones reales sobre el rendimiento de su base de datos.

Para obtener más información sobre lo que puede hacer con su base de datos administrada PostgreSQL, visite la documentación del producto .