Hola, tengo un reporte en Mapuche que funciona muuuuy lento. Fui al curso de Postgresql Nivel II en Abril de este año y vimos como analizar y optimizar consultas con el Statistics Collector de Postgres.
La cuestión es que al revisar como activar el Statistics Collector descubro que el parametro track_activities esta seteado en “on” en la base de datos aunque esté comentado en postgresql.conf.
Ahora quiero probar si esto era lo que ralentizaba el reporte, debería poner este parametro en off y reiniciar el servicio postgres. Necesito ver algún otro parámetro? Borro la tabla pg_statistics (que tiene mas de 19.000 registros)?
Saludos!!
PD: Cuando me llegue el servidor nuevo voy a consultar por mas parámetros de configuración
Para desactivar el tracking tenes que setear el parametro track_activities con valor off. Si esta comentado, descomentalo y luego hace un restart de postgres.
Es verdad que postgres deberia funcionar mas rapido con el tracking apagado, sin embargo resulta raro que sea la causa de que un reporte este lento.
Deduje que el escaneo secuencial de la tabla logs_dh03 era un punto critico y le cree un index. Otra vez explain analyze y me tiro esto:
siuanterior=# EXPLAIN SELECT dh03.nro_legaj, dh05.nro_licencia, dh05.nro_cargo, dh05.auditoria_fecha, dh05.auditoria_usuario, dh05.auditoria_operacion FROM mapuche_auditoria.logs_dh05 dh05 JOIN mapuche_auditoria.logs_dh03 dh03 ON(dh05.nro_cargo = dh03.nro_cargo) JOIN mapuche_auditoria.logs_dh01 dh01 ON(dh03.nro_legaj = dh01.nro_legaj) WHERE dh03.auditoria_fecha = (SELECT MAX(dh03_2.auditoria_fecha) FROM mapuche_auditoria.logs_dh03 dh03_2 WHERE dh03_2.auditoria_fecha <= dh05.auditoria_fecha AND dh03_2.nro_cargo = dh03.nro_cargo) AND dh05.nro_legaj IS NULL;
QUERY PLAN
Nested Loop (cost=0.00…37378390907312.55 rows=21571870 width=58)
Join Filter: (dh03.nro_legaj = dh01.nro_legaj)
→ Nested Loop (cost=0.00…37378326191056.49 rows=266731 width=58)
Join Filter: ((SubPlan 1) = dh03.auditoria_fecha)
→ Seq Scan on logs_dh05 dh05 (cost=0.00…1306.66 rows=229 width=54)
Filter: (nro_legaj IS NULL)
→ Index Scan using ix_logs_dh03_cargo on logs_dh03 dh03 (cost=0.00…892474.28 rows=232953 width=16)
Index Cond: (nro_cargo = dh05.nro_cargo)
SubPlan 1
→ Aggregate (cost=700670.24…700670.25 rows=1 width=8)
→ Bitmap Heap Scan on logs_dh03 dh03_2 (cost=4327.92…700476.11 rows=77651 width=8)
Recheck Cond: (nro_cargo = dh03.nro_cargo)
Filter: (auditoria_fecha <= dh05.auditoria_fecha)
→ Bitmap Index Scan on ix_logs_dh03_cargo (cost=0.00…4308.51 rows=232953 width=0)
Index Cond: (nro_cargo = dh03.nro_cargo)
→ Materialize (cost=0.00…687.62 rows=16175 width=4)
→ Seq Scan on logs_dh01 dh01 (cost=0.00…606.75 rows=16175 width=4)
(17 filas)
Todavia se hace un scan secuencial de la tabla logs_dh05 pero tire el reporte desde el sistema y tardo 2 minutos y dio resultado.
Algo mas que deba mirar?
Sigo esperando un servidor nuevo al que tengo que hacerlo desde 0 y ahi les voy a pedir consejos para cada uno de los parametros.
Agrego una consulta por índices. Resulta que el reporte de novedades del SIU Mapuche toma datos de las tablas de log. Especialmente hay una logs_dh03 que tiene 46millones de filas y por eso los reportes no salian.
Como ademas esta tabla tiene muchas columnas y el reporte es muy personalizable creo que voy a tener que crear varios indices.
Configurando el reporte para que consulte registros por las columnas “nro_cargo” y “auditoria_fecha” tardaba mucho hasta que cree el indice:
CREATE INDEX ix_logs_dh03_cargo
ON mapuche_auditoria.logs_dh03
USING btree
(nro_cargo);
Luego configuré el reporte para que consulte registros por usuario y fecha. Tardaba entre 2 y 4 minutos hasta que cree el siguiente indice y salio en 10 segundos.
CREATE INDEX ix_logs_dh03_auditoria_fecha_usuario
ON mapuche_auditoria.logs_dh03
USING btree
(auditoria_usuario,auditoria_fecha);
Configurando el reporte para que consulte por fechas solamente tarda mucho y no logro dar con un indice.
La consulta que realiza este reporte es:
SELECT DISTINCT nro_legaj, nro_cargo FROM mapuche_auditoria.logs_dh03 WHERE (TRUE) AND auditoria_fecha::date BETWEEN ‘2015-05-01’ AND ‘2015-05-11’;
El explain analyze da lo siguiente:
siuanterior=# EXPLAIN SELECT DISTINCT nro_legaj, nro_cargo FROM mapuche_auditoria.logs_dh03 WHERE (TRUE) AND auditoria_fecha::date BETWEEN ‘2015-05-01’ AND ‘2015-05-11’;
QUERY PLAN
Hola Marco, como estas? Te cuento que el “Reporte de Novedades” del sistema SIU-Mapuche consume información del log de auditoria. Si bien son muy pocos las tablas que se usan, es verdad que los tiempos pueden incrementarse en función del volumen de datos.
Una solución es crear indices en el esquema de auditoria, pero algo también muy recomendable es realizar limpiezas sobre el esquema de auditoria dejando en linea información relativamente nueva y en backups fuera de linea el resto de la información.
Siguiendo con la linea que iniciaste de crear indices: te recomiendo que, ademas, hagas un vacuum analyze ya que seguramente las estadísticas no estén actualizadas y esto de como resultada realizar un barrido secuencial. Tene en cuenta que muchas veces dependiendo de la cantidad de registros postgres puede optar por un recorrido secuencias antes que usar el indexado.
Saludos, Nico.
Muchas gracias Nicolas. Sabia que esta tabla es muy grande pero no habia considerado recortar los datos. Hace poco que estoy con Mapuche y recien estoy metiendome en esto.
Voy a realizar las pruebas con los datos actuales unicamente pero seguramente dará resultados.
Vuelvo al tema de indices y optimización de consultas.
Siguiendo los consejos del tema reduje la cantidad de datos de 46millones a 12millones dejando solo los del año en curso. Hice un vaccum analyze y cree indices por auditoria_fecha en la tabla logs_dh03. El inconveniente es que siguen haciendo un escaneo secuencial para la consulta que mostré antes.
siuanterior=# EXPLAIN SELECT DISTINCT nro_legaj, nro_cargo FROM mapuche_auditoria.logs_dh03 WHERE (TRUE) AND auditoria_fecha::date BETWEEN ‘2015-05-01’ AND ‘2015-05-11’;
QUERY PLAN
HashAggregate (cost=2816013.33…2816084.40 rows=7107 width=8)
→ Seq Scan on logs_dh03 (cost=0.00…2815658.02 rows=71062 width=8)
Filter: (((auditoria_fecha)::date >= ‘2015-05-01’::date) AND ((auditoria_fecha)::date <= ‘2015-05-11’::date))
Si a esta consulta le agrego el campo usuario, el reporte sale en 2 segundos usando un indice creado para (auditoria_usuario, auditoria_fecha)
siuanterior=# EXPLAIN SELECT DISTINCT nro_legaj, nro_cargo FROM mapuche_auditoria.logs_dh03 WHERE (TRUE AND auditoria_usuario IN (‘faas’)) AND auditoria_fecha::date BETWEEN ‘2015-05-01’ AND ‘2015-05-11’;
QUERY PLAN
HashAggregate (cost=2025.31…2025.32 rows=1 width=8)
→ Index Scan using ix_logs_dh03_auditoria_fecha_usuario on logs_dh03 (cost=0.00…2025.29 rows=5 width=8)
Index Cond: ((auditoria_usuario)::text = ‘faas’::text)
Filter: (((auditoria_fecha)::date >= ‘2015-05-01’::date) AND ((auditoria_fecha)::date <= ‘2015-05-11’::date))
(4 filas)
El problema estará con las fechas? Todavía tengo muchos registros y debería volver a recortar las tablas?
Registros desde que fecha tenes? otra opción puede ser probar con un indice mas especifico y chico (por ejemplo indexando solo los del año 2015, esto se logra con un where en la creación del indice)
Saludos, Nico.
Era exactamente eso Ignacio. El indice lo estaba creando sobre auditoria_fecha sin usar la función date. Ahora cree el indice con la función y mejoró la consulta.