Cómo mejorar el rendimiento y consultas de nuestro motor Postgresql

Hola comunidad de Backtrack Academy, en esta ocasión compartiré algunos datos que pude recolectar durante mi última semana, trabajando con el motor de base de datos pg, PostgreSQL es un sistema de gestión de bases de datos relacional orientado a objetos y de código abierto. 

 

En el lugar donde trabajo apareció un requerimiento que constaba en mejorar algunas consultas que tomaban mucho tiempo al momento de generar algún reporte, es común siendo programador, escribir consultas SQL n + 1, forma ineficiente de consultar una base de datos, además de trabajar con volúmenes de datos que no necesitamos, pero cómo podemos realizar este análisis de manera más precisa. Durante este proceso conocí una herramienta bastante sencilla y que generó bastante valor al momento de analizar una consulta, independiente de nuestra tecnología, ya estemos trabajando con un motor mysql o pg. 

 

Antes de continuar, es importante comprender el ciclo de vida de una consulta. El ciclo de vida es lo que sucede desde una solicitud inicial a la base de datos hasta la respuesta que envía PostgreSQL. Cuando tiene una gran cantidad de datos, la obtención cruda de sus datos puede llevar a una caída en el rendimiento. Si está escaneando su base de datos de forma secuencial (a menudo llamada exploración de tabla) para sus datos, su rendimiento se escalará linealmente: más filas, rendimiento más lento. Pero podemos hacerlo mejor que eso.

 

Entonces, ¿cuál es el ciclo de vida de una consulta? Inicialmente, hay una transmisión de la cadena de consulta a PostgreSQL. La cadena de consulta se analiza y se crea un plan, los planes son muy importantes. Estos son los pasos que PostgreSQL tomará para encontrar los datos solicitados. La forma en que configure la configuración de su base de datos, su esquema y sus índices (pronto habrá más información sobre los índices) afectará el rendimiento de estos planes. Por lo tanto, será importante que entendamos los planes y cómo optimizarlos. Finalmente, la base de datos realizará el plan y recuperará los datos.

 

Se puede producir una discrepancia entre el plan de la base de datos que PostgreSQL pretende utilizar para obtener sus datos y la forma en que realmente obtiene sus datos. Esto se debe a que PostgreSQL basa su plan en métricas y estadísticas que se actualizan con poca frecuencia. Algunos datos deben actualizarse periódicamente para que las estadísticas utilizadas para los planes estén actualizadas.

 

Aquí es donde entra en juego ANALYZE. La ejecución del comando ANALYZE actualiza estas estadísticas para que Postgres tenga un nuevo conjunto de datos sobre cómo crear sus planes, por lo tanto, si está actualizando las tablas o el esquema o agregando índices, recuerde ejecutar un comando ANALIZAR después para que los cambios surtan efecto.

 

Lo primero que necesitamos hacer, es exportar nuestros datos, y que métricas queremos analizar, por ejemplo:

- COST

- VERBOSE

- BUFFERS

y lo exportamos en formato json FORMAR JSON, a continuación analizaremos una consulta aleatoria, para ello crearemos un archivo el cual lo llamaremos query.sh y pegamos la siguiente consulta:

 

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) select a.id, a.name, a.nemo, p.price, a.generic_attribute_id, p.price_date, p.generated_price_date, sum(m.quantity) as sum_quantity, sum(m.quantity * price * bonus_factor.value) as sum_total, mu.name as monetary_unit, m.asset_id, a.is_monetary_unit, a.is_um_aux, mu.iso, mu.name as um_name , a.general_asset_type_id ,  a.general_asset_type_id
        from movements as m
        inner join levels as l
          on m.level_id = l.id
        inner join assets as a
          on m.asset_id = a.id
        inner join monetary_units as mu
          on a.monetary_unit_id = mu.id
        inner join (select distinct on (ap.asset_id) price, ap.asset_id, ap.price_date, ap.generated_price_date
            from asset_prices ap, assets a
            where ap.deleted = false and price_date = '2018-02-28'
            and ap.asset_id = a.id
             and a.general_asset_type_id in (1, 447, 459, 461, 481, 483, 484, 495, 499)
             and  a.general_asset_type_id in (1, 447, 459, 461, 481, 483, 484, 495, 499)
            ) as p
        on m.asset_id = p.asset_id
        inner join (select distinct on (asset_id) value, asset_id, bonus_factor_date
            from bonus_factors
            where bonus_factor_date <= '2018-02-28T00:00:00+00:00'
            order by asset_id, bonus_factor_date desc) as bonus_factor
        on m.asset_id = bonus_factor.asset_id
        where (l.lft >= 1446 and l.rgt <= 1489) and
            m.movement_date <= '2018-02-28T00:00:00+00:00' and

            m.deleted = false

             and a.general_asset_type_id in (1, 447, 459, 461, 481, 483, 484, 495, 499)
             and  a.general_asset_type_id in (1, 447, 459, 461, 481, 483, 484, 495, 499)

        group by m.asset_id, a.id, price, a.generic_attribute_id, a.name, a.nemo, mu.name, p.price_date, p.generated_price_date, bonus_factor.value, bonus_factor.bonus_factor_date, mu.iso, a.is_um_aux, um_name, a.is_monetary_unit , a.general_asset_type_id ,  a.general_asset_type_id
        order by a.id

Luego procedemos a procesar los datos y obtener nuestro archivo JSON:

 

psql -d database_development -qAt -f query.sql > analyze.json

 

Un ejemplo de nuestro archivo:

 

 

Entender profundamente la consulta SQL

 

Lo primero que vamos a realizar es copiar nuestra consulta SQL y nuestro JSON, ingresamos a

http://tatiyants.com/pev/#/plans/new

 

Una ves que pegamos nuestros valores; obtendremos un análisis de la siguiente manera el cual nos permitirá detectar de manera más gráfica en dónde se producen los cuellos de botella:

 

 

De esta manera podemos analizar, tanto el volumen de datos que estamos filtrando los cuellos de botella, qué consulta toma más tiempo.

 

Otra alternativa sencilla y rápida para pg, es pgtune, un sitio web que nos permite con un simple input obtener una mejor configuración y sacar mayor provecho a nuestro motor de base de datos.

Para ello solo debemos ingresar al sitio web y agregar la configuración de nuestro servidor:

https://pgtune.leopard.in.ua/#/

 

 

En este caso solo debemos copiar nuestra configuración y editar nuestro archivo postgresql.conf y reemplazar la configuración:  

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 16 GB
# CPUs num: 8
# Connections num: 50
# Data Storage: ssd

max_connections = 50
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 20971kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

 

OTROS Conceptos importantes: 

max_connections

Las conexiones son cómo sus aplicaciones se comunican con su base de datos. Cada conexión requerirá un parloteo de comunicación y configuración para establecer, sin embargo, una vez establecidas, las consultas pueden enviarse por cable a su base de datos. Su base de datos PostgreSQL vendrá con un número predeterminado de conexiones, que puede modificar. Desea asegurarse de que sus aplicaciones no se conecten innecesariamente, ya que esto puede ser costoso y puede afectar el rendimiento. Las asignaciones de memoria se realizan por conexión, por lo que deberá equilibrar la asignación de memoria y la cantidad de conexiones.

 

checkpoint_segments

Un punto de control es una acción periódica que almacena información sobre su sistema. De forma predeterminada, un punto de control se ejecutará después de una serie de segmentos, pero dependiendo de su sistema, es posible que desee aumentar este valor. Hablaremos más adelante en el artículo sobre cómo puede cerrar la sesión de los datos de su punto de control, pero la configuración del punto de control es importante porque puede ser una operación bastante costosa. A menudo se piensa que la configuración predeterminada es demasiado agresiva y realiza puntos de control con demasiada frecuencia, por lo que es posible que desee aumentar este valor para que los puntos de control sean menos frecuentes.

 

work_mem

Como mencioné anteriormente, la asignación y administración de memoria es una parte importante de PostgreSQL de ajuste de rendimiento. Si su sistema está realizando muchas tareas complejas, aumentar la memoria de clasificación puede ayudar a la base de datos a optimizar su configuración para su configuración. Esto permite que PostgreSQL almacene en caché más datos en la memoria mientras realiza su clasificación, en lugar de realizar llamadas costosas al disco.

 

random_page_cost

Esta configuración es esencialmente la cantidad de tiempo que su optimizador debería pasar leyendo la memoria antes de llegar a su disco. Debe modificar esta configuración solo cuando haya realizado otras optimizaciones basadas en el plan que cubriremos pronto, como pasar la aspiradora, indexar o alterar sus consultas y esquema.

Estas son solo algunas de las optimizaciones que puede realizar para las configuraciones de la base de datos, pero hay muchas más . Ahora que sabe cómo ajustar la configuración de su base de datos, veamos otra área para investigar: pasar la aspiradora.

 

log_line_prefix

Este es el formato de sus registros de base de datos. Para herramientas como PGBadger, necesitarán datos enriquecidos y también deberán conocer el formato de los datos. Su prefijo de línea de registro le indica a PostgreSQL en qué formato debe emitir sus datos de registro. Si desea realizar un análisis de registro significativo, deberá configurar este valor para que sea compatible con las herramientas que está utilizando para analizar sus registros.

 

log_checkpoints

Como comentamos en los ajustes de configuración, los puntos de control en PostgreSQL son acciones periódicas que almacenan datos sobre su sistema. Estos puntos de control de registro pueden, si son excesivos, conducir a degradaciones del rendimiento. Si sospecha que este podría ser el caso, al habilitar los puntos de control de registro podrá ver una gran cantidad de datos sobre esos puntos de control, como la frecuencia con la que se ejecutan y lo que podría estar activándolos.

 

logging_connection

También es posible que desee saber información sobre las conexiones. Si solo tiene una aplicación conectada a su base de datos, pero está viendo muchas conexiones concurrentes, algo podría estar mal. Demasiadas conexiones inundando su base de datos también podría significar que las solicitudes no llegan a la base de datos y podrían afectar a los usuarios finales de su aplicación.

Ahora que tenemos muchos datos sobre nuestro sistema, ¿qué herramientas tenemos para mejorar la estructura de nuestro sistema para que sea más eficiente?