cuellos botella en postgresql

EQ Soft Consultoría y Soporte E.I.R.L.Http://[email protected]éfonos: (51) 01–5645744 / 01-5645424 / 997244926 / 997003957

PgDay 2012 1/18

Determinando cuellos deDeterminando cuellos deBotella en PostgreSQLBotella en PostgreSQL

Ernesto Quiñones AzcárateErnesto Quiñones Azcá[email protected]@eqsoft.net

http://www.eqsoft.net/

mailto:[email protected]


PgDay 2012 2/18

Hace un añoHace un año

Hablamos de Análisis de QuerysHablamos de Análisis de Querys




Recordemos..........

Análisis de Querys – Explain

Explain permite visualizar el plan de ejecución de un query, el plan de ejecución son los pasos que sigue la dmbs para procesar la consulta, tabla por tabla con la que trabaja, las uniones, tipos de índices que utiliza, tuplas movidas, etc. prueba4=# explain select * from regla; QUERY PLAN Seq Scan on regla (cost=0.00..18.00 rows=800 width=72)(1 row)

PgDay 2012 3/18





Para ver los comandos usados y los tiempos de respuesta

prueba4=# explain analyze select * from regla; QUERY PLAN Seq Scan on regla (cost=0.00..18.00 rows=800 width=72) (actual time=0.012..0.016 rows=2 loops=1) Total runtime: 0.070 ms(2 rows)

Informa los campos requeridos en la consulta

prueba4=# explain verbose select * from regla; QUERY PLAN Seq Scan on public.regla (cost=0.00..18.00 rows=800 width=72) Output: id, nombre(2 rows)

prueba4=# explain analyze verbose select * from regla; QUERY PLAN Seq Scan on public.regla (cost=0.00..18.00 rows=800 width=72) (actual time=0.010..0.013 rows=2 loops=1) Output: id, nombre Total runtime: 0.050 ms(3 rows)


PgDay 2012 4/18





Los Explain siempre se deben leer de “adentro hacia afuera”.

prueba4=# explain select * from factura_cab a join factura_det b on a.id =b.fac_id; QUERY PLAN Merge Join (cost=260.93..525.73 rows=17120 width=29) Merge Cond: (b.fac_id = a.id) > Sort (cost=111.15..115.15 rows=1600 width=21) Sort Key: b.fac_id > Seq Scan on factura_det b (cost=0.00..26.00 rows=1600 width=21) > Sort (cost=149.78..155.13 rows=2140 width=8) Sort Key: a.id > Seq Scan on factura_cab a (cost=0.00..31.40 rows=2140 width=8)(8 rows)

El primer explain fue hecho sin hacer un VACUMM a las tablas, en el segundo se procedió a ello, nótese las diferencia en las cifras, esto se debe a las estadísticas.

prueba4=# explain select * from factura_cab a join factura_det b on a.id =b.fac_id; QUERY PLAN Hash Join (cost=1.07..2.16 rows=4 width=25) Hash Cond: (b.fac_id = a.id) > Seq Scan on factura_det b (cost=0.00..1.04 rows=4 width=17) > Hash (cost=1.03..1.03 rows=3 width=8) > Seq Scan on factura_cab a (cost=0.00..1.03 rows=3 width=8)(5 rows)


PgDay 2012 5/18





Para determinar si un índice debe ser utilizado, PostgreSQL debe tener estadísticas sobre la tabla. Estas estadísticas se recolectan mediante VACUUM ANALYZE, o simplemente ANALYZE. Usando las estadísticas, el optimizador sabe cuántas son las filas en la tabla, y puede determinar mejor si los índices deben utilizarse. Las estadísticas son también valiosas en la determinación de un orden óptimo y métodos de unión. La recolección de Estadísticas debe ser realiza periódicamente como el cambio de contenido de la tabla.

El costo:

(cost=149.78..155.13 rows=2140 width=8)149.78 < costo inicial de traer la primera tupla155.13 < costo total estimadoRows=2140 < filas escaneadasWidth=8 < filas de salida

El costo total estimado se calcula sobre la siguiente formula (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost)

seq_page_cost < costo de acceso a las páginas de la data (1.00 default)cpu_tuple_cost < costo del proceso de cada fila durante la consultas (0.01 default)


PgDay 2012 6/18




Espiando lo que hacen los Querys.

Para lograr esto debemos modificar el postgresql.conf y activar algunas opciones de debug que no están activas por defecto.

log_error_verbosity = VERBOSE #nivel de detalle si loggea algo

log_min_error_statement = FATAL #solo log de querys que son totalmente perjudiciales para la dbms

log_min_duration_statement = 1000 #log de aquellos querys que duran más de 1 segundo, si ya se sabe que hay muchos así, entonces subir a 2000 o 5000 lo que sea más adecuado o se considere demasiado tiempo

debug_print_plan = On #para que imprima el plan de ejecución de los querys Largos

debug_pretty_print = On #para que imprima el log de los querys en un forma adecuado, decente de leer

log_lock_waits = On #para ver si tenemos problemas de bloqueos


PgDay 2012 7/18




Los bloqueos

El tema de los Vacuums....

PgDay 2012 8/18

Lock Usado en Tiene problemas

ACCESS SHARE Se ejecuta un Select sobre una tabla No

ROW SHARE Se ejecuta un Select sobre una tabla Solo si hay un bloqueo exclusivo sobre la tabla

ROW EXCLUSIVE Se ejecuta UPDATE / DELETE / INSERT en una tabla

Solo si hay un bloqueo exclusivo sobre la tabla o registro. + SHARE

SHARE UPDATE EXCLUSIVE por Vacuum, Analyze y creación de índices

Con cualquier bloqueo exclusivo con el que concurra + SHARE

SHARE creación/actualización de indices Con cualquier bloqueo exclusivo con el que concurra

SHARE ROW EXCLUSIVE Se lanza manualmente Con cualquier bloqueo exclusivo con el que concurra + SHARE

EXCLUSIVE Se lanza manualmente, solo permite ACCESS SHARE

Con todos los bloqueos excepto ACCESS SHARE

ACCESS EXCLUSIVE Cambios de estructura de la tabla, Vacuum Full y procesos de regeneración de índices

Con todos los bloqueos (debe esperar a que terminen)




Administración – Procesos

a)TOP y HTOP

TOP es un comando convencional de Linux / Unix que nos permite ver los procesos que se ejecutan en el servidor.

Los procesos ejecutados por PostgreSQL son normalmente ejecutados por los usuarios “postgres”, “pgsql” ó “postmaster”.

ernesto@depeche:~/aaa$ ps aux | egrep postgrespostgres 961 0.0 0.1 101580 2228 ? S Oct08 0:06 /usr/lib/postgresql/8.4/bin/postgres D /var/lib/postgresql/8.4/main c config_file=/etc/postgresql/8.4/main/postgresql.confpostgres 989 0.0 0.3 101704 6208 ? Ss Oct08 0:11 postgres: writer process postgres 990 0.0 0.0 101580 568 ? Ss Oct08 0:08 postgres: wal writer process postgres 991 0.0 0.0 102380 1320 ? Ss Oct08 0:09 postgres: autovacuum launcher process postgres 992 0.0 0.0 73704 848 ? Ss Oct08 0:17 postgres: stats collector process

Recuerde que PostgreSQL maneja un procesador por conexión, si tiene varios procesadores un proceso lanzado no usará varios procesadores, solo uno, deja los otros disponibles para otras conexiones que lanzan sus propios procesos, la ventaja del uso de esta funcionalidad se complementa con la capacidad de administración de usuarios concurrentes que tenga el sistema operativo que se está usando (que soporte SMP).

Nunca mate un proceso que demora mucho

PgDay 2012 9/18

Pero hay más.....





b)PG_TOPhttp://ptop.projects.postgresql.org/El paquete instalador en Ubuntu es PTOP e instala la aplicación pg_top.

ernesto@depeche:~/aaa$ pg_top helppg_top: invalid option ''pg_top version 3.6.2Usage: pg_top [ITWbcinqu] [x x] [s x] [o field] [z username] [p PORT] [U USER] [d DBNAME] [h HOSTNAME] [number]

ernesto@depeche:~/aaa$ pg_top U dbadmin d prueba5

En una consola:prueba5=# begin;prueba5=# lock alumnos in exclusive mode;

En otra Consola:prueba5=# insert into alumnos values(5,'chicho',12,'nada');

PgTop nos muestra:ast pid: 29499; load avg: 1.59, 1.59, 1.69; up 1+03:48:24 3 processes: 3 sleepingCPU states: 47.0% user, 2.6% nice, 8.8% system, 40.5% idle, 1.1% iowaitMemory: 1920M used, 81M free, 26M buffers, 659M cachedSwap: 375M used, 1532M free, 45M cached PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND29500 postgres 20 0 102M 4232K sleep 0:00 0.02% 0.20% postgres: dbadmin prueba5 [local] idle 29215 postgres 20 0 102M 5048K sleep 0:00 0.00% 0.00% postgres: dbadmin prueba5 [local] idle in transaction 29396 postgres 20 0 102M 3628K sleep 0:00 0.00% 0.00% postgres: dbadmin prueba5 [local] INSERT waiting

PgDay 2012 10/18

Pero hay más.....



http://ptop.projects.postgresql.org/



b)PG_TOP

A < permite ver el plan de ejecución de un query, especifique el PID del procesoC < activa/desactiva colorc < permite ver la linea de comandos completaD < cambia página de listadoh ó ? < helpE < ver versión actualizada del plan de ejecucióne < permite ver errores del sistemaI < ver/no ver procesos en espera o durmiendoK < mata un proceso, indicar el pidL < permite ver los locs del proceso, especificar el pidM < ordena por uso de memoriaN < ordena por número de proceson ó # <indica cuantos procesos se pueden ver, máximo 65O < cambia el orden de muestra dependiendo un parámetro especial ("cpu", "res", "size", "time", "seq_scan", "seq_tup_read", "idx_scan", "idx_tup_fetch", "n_tup_ins","n_tup_upd", "n_tup_del")P < ordena por utilización del procesoQ < muestra el query, especificar el pidq < QuitR < Display user table statistics.r < ??s < cambia la cantidad de segundos de refresco de la pantallaT < ordena por tiempo de ejecuciónT < ??U < muestra los procesos de un usuario, ingresar el usuario X < muestra las estadísticas de los índices

PgDay 2012 11/18

Pero hay más.....





Vacuum

El Vacuum es una de las más importantes tareas de administración, lo que hace es limpiar las “páginas” no usadas por el sistema y actualiza las estadísticas de las tablas e índices para una mejor resolución de querys.

La dbms ejecuta periodicamente (definido en postgresql.conf) un Lazy Vacuum, esto es libera páginas no usadas por data, más no por índice, este vacuum no genera demasiado tiempo de bloqueo en la tabla (depende el tamaño).

PgDay 2012 12/18




Vacuum

El full vacuum requiere acceso exclusivo a la tabla durante el tiempo que demoré la operación, esta limpiara totalmente las páginas no usadas.

Mejora notablemente el tiempo de acceso a los datos.


PgDay 2012 13/18




Vacuum

Se pueden aplicar Vaccums a nivel de tabla o base de datos.

●Vacuum full TABLA < limpia todo el espacio no utilizado, reescribe totalmente la tabla por lo cual consume más espacio.

●Vacuum analyze TABLA < actualiza las estadísticas para e generador de plan de ejecución.

●Vacuum TABLA < solo limpia data y reorganiza las páginas.

Se puede añadir de precisión un Vacuum sobre un campo.

“Vacuumdb” se utiliza externamente desde la linea de comandos del sistema operativo para ejecutar la operación sobre la base de datos seleccionada.

ernesto@depeche:~$ vacuumdb full prueba5 U dbadmin


PgDay 2012 14/18




Vacuum

El Vacuum es bueno, es importante ..... pero ...... imaginen un entorno de alta concurrencia y que el vacuum se lance en un momento de alta carga.


PgDay 2012 15/18




Vacuum

Tenemos que modificar el postgresql.conf nuevamente.

autovacuum_naptime indica el periodo de tiempo en los cuales verificará si las tablas de una db necesitan ejecutar un autovacuum, no indica o fuerza un vacuum.

autovacuum_vacuum_threshold y autovacuum_analyze_threshold indica la cantidad de TUPLAS que deben ser modificadas para justificar que se realice un VACUUM y un ANALYZE, una TUPLA no es igual a una página ni a un registro es una unidad que puede tener información de data, estructuras o índices.

autovacuum_vacuum_scale_factor y autovacuum_analyze_scale_factor indica el porcentaje del total de registros en una tabla que debe ser modificado antes de que se lance un VACUUM o un ANALYZE, por defecto el valor de VACUUM es 0.2 eso significa 20% y en ANALYZE es 0.1 que es 10%, en otras palabras, si tenemos una tabla de 100,000 registros y cambiamos 12,000 entonces se ejecutará un ANALYZE pero no un VACUUM, cuando se modifiquen 20,001 registros entonces se lanzará el VACUUM.

autovacuum_freeze_max_age es solo una medida de seguridad, el AUTOVACUUM el PostgreSQL lo lanza automáticamente por seguridad, por defecto sucederá cada 200millones de transacciones.


PgDay 2012 16/18




Vacuum

Tenemos que modificar el postgresql.conf nuevamente.

autovacuum_vacuum_cost_limit, el COST_LIMIT es el “COSTO” de operaciones máximo que ejecutará la base de datos durante un AUTOVACUUM antes de pararse y esperar un determinado tiempo, especificado por autovacuum_vacuum_cost_delay y continuar.

Como el proceso de VACUUM genera mucho estrés en la DB esto bloquea eventualmente las tablas, como los VACUUM y AUTOVACUUM se lanzan en caliente (osea sin impedir que los usuarios puedan seguir trabajando con la base de datos) es necesario indicarle a la DB cuanto tiempo puede trabajar en estás tareas.

El COSTO no es fácil de calcular, por ejemplo:

buscar y acceder a una página vale 1leer la página vale 10manipular y limpiar una página vacía 20COSTO TOTAL 31


PgDay 2012 17/18




GRACIAS POR SU ATENCION


PgDay 2012 18/18



cuellos botella en postgresql

Education