Optimizacion de Postgres 9.1 para el DW

Hola a todos,

Queria saber cual podria ser la “mejor” configuracion recomendada para un postgres 9.1 que esta en un servidor de 4gb de RAM que corre Wichi (pentaho).

Por el momento le di 2 gb al pentaho y deje 2 libres, para SO y demas …

Tengo entendido que modificando:

work_mem y SHARED_BUFFERS, se podria aumentar la performance, pero me gustaria saber que se recomienda para una base de DW, cuya funcion primaria es la de consulta.

Haciendo un:
SELECT pg_stat_activity.usename,
round(date_part(‘epoch’::text, now() - pg_stat_activity.query_start)) AS duration,
pg_stat_activity.current_query
FROM pg_stat_activity
ORDER BY round(date_part(‘epoch’::text, now() - pg_stat_activity.query_start)) DESC;

obtengo:

select
“map_dw_lt_imppresupsubdependencia”.“imppresupsubdependencia_desc” as “c0”,
“map_dw_lt_imppresupfuentes”.“imppresupfuente_id” as “c1”,
“map_dw_lt_imppresupfuentes”.“imppresupfuente_desc” as “c2”,
“map_dw_lt_redprogramatica”.“programa_desc” as “c3”,
“map_dw_lt_redprogramatica”.“subprograma_desc” as “c4”,
“map_dw_lt_categoriascargo”.“escalafon_id_map” as “c5”,
“map_dw_lt_categoriascargo”.“escalafon_desc” as “c6”,
“map_dw_lt_persona”.“letra” as “c7”,
“map_dw_lt_persona”.“apellidonombre_desc” as “c8”,
“map_dw_lt_categoriascargo”.“dedicacion_id_map” as “c9”,
“map_dw_lt_categoriascargo”.“dedicacion_desc” as “c10”
from
“mapuche”.“map_dw_lt_imppresupsubdependencia” as “map_dw_lt_imppresupsubdependencia”,
“mapuche”.“map_dw_ft_vw_liquidaciones” as “map_dw_ft_vw_liquidaciones”,
“mapuche”.“map_dw_lt_imppresupfuentes” as “map_dw_lt_imppresupfuentes”,
“mapuche”.“map_dw_lt_redprogramatica” as “map_dw_lt_redprogramatica”,
“mapuche”."map_dw_lt_categoriasc

Aclaro que el qry esta truncado, posiblemente porque sea muy grande, de todos modos, estos qry son automaticos, es decir, los procesa el motor mondrian, de acuerdo a como esten conformados los cubos.

Muchas gracias y saludos,
Fernando

Hola Fernando, te paso una serie de tips a tener en cuenta:

Hardware
CPU (priorizar velocidad ante cantidad de nucleos)
raid
varios discos de menor tamaño suelen dar mejor resultado que uno mas grande.
Se necesita un gran throughput (se obtiene con RAID … de 6 a 48 discos). Ver discos de estado solido / nas
network
gigabit
tiempos de espera por hardware intermedio
ancho de banda
Testear cada componente
CPU: PassMark, sysbench, Spec CPU
RAM: memtest, cachebench, Stream
I/O: bonnie++, dd, iozone
Network: bwping, netperf
DB: pgBench

Una vez que sabemos que todo el hardware anda bien, recien ahi creamos nuestra BD

O.S / FileSystem
Separar el log de transacciones (pg_xlog) (solo si hay transacciones)
Ponerlo en un disco (o raid) dedicado puede mejorar la performance entre un 10-50%
Tablespaces para tablas grandes / o con muchas transacciones
Poner las tablas / indices mas utilizados en un tablespace / disco distinto

Postgresql.conf
max_connections de 10 to 40 --En DW no suelen haber muchas conexiones concurrentes
shared_buffers (1/4 de la memoria libre en servidores dedicados)
work_mem (no es compartida, es por conexion) ojo si tengo muchas conexiones concurrentes.
Si usa swap es que lo incremente mucho
Ver los logs si nos dice algo del estilo “not enough work_mem”
probar de 128MB hasta 1GB
temp_buffers probar de 128MB hasta 1GB
maintenance_work_mem Cuanto antes termine el vacuum, mejor!
En bases grandes se puede pensar en valores de 256MB a 1GB
Tambien se utiliza para la creacion de indices.
Incrementarlo para cargas masivas
wal_buffers debe ser menor al tamaño del segmento wal (no tiene sentima mas grande que 16mb)
Probar con 8MB y 16MB
checkpoint_segments (16, 64, 128)
mayor cantidad mas espacio en disco y mas tiempo de restauracion ante una caida del servidor.
effective_cache_size memoria disponible para los querys
Se recomienda probar con 2/3 de la memoria libre.
default_statistics_target de 200 a 1000 para bases grandes
autovacuum
NO ACTIVARLO PARA DW (siempre y cuando el vacuum sea un paso en la insercion de datos)
Es bueno para aplicaciones generales de lectura / escritura
No es bueno para escrituras masivas (se recomienda hacerlo manual luego de la carga)
Asegurarse de utilizar analyze
autovacuum_max_workers Si se tienen muchas tablas (>100, >1000) se pueden utilizar varios.
aconsejan no utilizar mas que la mitad de la cantidad de nucleos
vacuum_cost_delay Probar con valores entre 50 y 100ms
Hace que el vacuum se duerma cuando execede un costo de ejecucion.

Carga masiva de datos (si el preoceso falla se tiene que poder repetir el mismo) Ojo, leer bien antes de usarlo.
fsync off → Cargar datos masivamente → crear indices → fsync on → reiniciar el motor

Diseño de la base
Tablas
No optimizar prematuramente
Normalizar las tablas y esperar a que ocurra algo para desnormalizarlas.
Postgres esta diseñado para trabajar con tablas normalizadas
Indices
Indexar:
Indices para las PK y FK
Indices para los criterios comunes de los where
Indices para columnas que se le apliquen funciones de agregacion
Indices especiales: expresiones, FTS, parciales
NO Indexar:
Indices costosos de actualizar y borrar
Muchos indices pueden confundir al planner
Tablas pequeñas o de pocas columnas
pg_stat_user_indexes Muestra los indices que no se utilizan
pg_stat_user_tables Muestra las busquedas secuenciales (posibles candidatos a indices)
Muestra tablas que tienen muchos update y delete
Particiones
Particionar tablas grandes (datos historicos: ver de purgarlos. Delete masivos atentan contra el servidor)
Tablas grandes (>1GB o 10millones de registros)
La particion puede ser activa / pasiva
Querys (aprovechar las consultas)
Postgres generalmente se comporta mejor con un querys mas grande que varios mas chiquitos
avitar hacer joins
Inserts masivos realizarls dentro de una transaccion
actualizar todas las columnas necesarias en un solo update (en lugar de varios update)
Los COPY son entre 3 y 5x mas rapidos que los inserts
Usar vistas para simplificar los querys

Optimizar los querys
En testeo (antes de pasar a produccion)
Esto obliga a que los ambientes de testeo asemejen a los de producción.
Simular cantidad de usuarios
minitorear querys lentos (log)
EXPLAIN ANALYZE (mala estimación de numero de registros, busquedas secuenciales, alta cantidad de loops)
Reescribir querys complejos varias veces hasta detectar el problema o notar que cambia
utilizar pg_stat_statement

Espero que les sea de utilidad.
Saludos, Nico.

Excelente, voy a probar algunas de las configuraciones que aplican para el DW

Muchas gracias y saludos,
Fernando