Post196 nn

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


12063705 10207273024561428 59106377850244238 n
Felipe Barrios

Hola comunidad de Backtrack Academy, en esta ocasión compartiré algunos datos que pude recolectar durante mí ú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/#/