Tuning Your PostgreSQL Server/es

by Greg Smith, Robert Treat, and Christopher Browne

PostgreSQL viene con una configuración básica mejorada para una gran compatibilidad en vez de una gran performance. Muy probablemente los parámetros por defecto sean muy inferiores a los que su sistema pueda soportar. En lugar de entrar en muchos detalles (que ud. puede encontrar en GUC Three Hour Tour), aquí daremos un pantallazo rápido de lo básico, con las cosas más comunes que se puede encontrar alguien nuevo en PostgreSQL. Ud. puede clickear en el nombre de cada parametro en la sección correspondiente para ir directamente a la documentación relevante en el manual de PostgreSQL (en inglés) para obtener más detalles después de leer esta introducción rápida.

Información de base en la configuración

Los seteos en PostgreSQL pueden ser manipulados por diferentes vías, pero generalmente ud. puede preferir actualizarlos en el archivo postgresql.conf. Las opciones de que dispone cambian de versión en versión; la lista definitiva esta en el código fuente src/backend/utils/misc/guc.c de su versión de PostgreSQL (pero la vista pg_settings debería ser suficiente para la mayoría de casos).

Los tipos de configuración

Existen diferentes tipos de variables de configuración, divididas en base a los posibles valores de entrada

Boolean true, false, on, off
Integer Números (2112)
Float Valores decimales (21.12)
Memory / Disk Enteros (2112) o "Unidades computacionales" (512MB, 2112GB). Evite usar números enteros sin unidad; necesitará saber la unidad subyacente para entender lo que significa.
Time "Unidades de tiempo" aka d,m,s (30s). A veces se omite la unidad; no haga esto.
Strings Texto simple con comilla simple('pg_log')
ENUMs Cadenas de caracteres, pero de una lista específica ('WARNING', 'ERROR')
Lists Una lista de cadenas separada por comas ('"$user",public,tsearch2)

Cuando surten efecto

Los seteos en PostgreSQL tienen diferentes niveles de flexibilidad para cuando pueden ser cambiados, usualmente relacionados a las restricciones del código interno. La lista de estos niveles es:

Postmaster: requiere reiniciar el servidor
Sighup: requiere para el servidor, o hacer un 'kill -HUP' (usualmente -1), pg_ctl reload o 'select pg_reload_config();'
Usuario: peude setearse en cada sesión individual, teneiendo efecto en esa misma
Interno: puesto a la hora de compilar, no puede ser cambiado, principalmente por referencia
Backend: se pondrán antes de iniciar una sesión
Superuser: puede aplicarse mientras corre el servidor solo por superusuarios

La mayor parte del tiempo utilizará el primero, pero el segundo puede ser útil si tiene un servidor que no quiere detener. El tercer nivel es útil en situaciones especiales. Puede saber de qué nivel es cada opción mirando la columna "context" de la vista pg_settings.

Notas importantes acerca de postgresql.conf

Debería poder encontrarlo en $PGDATA/postgresql.conf, mire enlaces simbólicos y mediante otros posibles trucos.
SHOW config_file le dará la ubicación del archivo de configuración
Las lineas con # son comentarios que no tienen efecto. Para una base de datos nueva, esto significará que se esta usando el valor por defecto, pero en sistemas en funcionamiento no se deberían descomentar! En versiones anteriores a 8.3, al comentar una línea no se devuelve al valor por omisión. Incluso en versiones posteriores, los cambios en postgresql.conf no surten efecto sin un restart/reload; por tanto, es posible que el sistema esté corriendo algo diferente a lo configurado en este archivo.
Si el mismo valor es declarado varias veces, el ultimo es el que importa.

Viendo la configuración actual

Mire el archivo postgresql.conf. Esto funciona si ud. sigue buenas prácticas, pero no es definitivo!
show all, show <setting> le mostrarán el valor actual de una variable. Tenga cuidado con los cambios específicos de valores de sesión
select * from pg_settings le mostrará los cambios de sesión específicos como los ha modificado localmente.

listen_addresses

Por defecto, PostgreSQL solo responde a conexiones provenientes desde localhost. Si desea que su servidor fuera accesible por otros sistemas via red de TCP/IP, debe cambiar el valor por defecto de listen_addresses. Lo más usual es configurarlo de esta manera:

listen_addresses = '*'

Luego, controlar quienes no pueden conectarse a través del archivo pg_hba.conf .

max_connections

max_connections aplica la cantidad máxima de conexiones de clientes. esto es muy importante para algunos de los parametros siguientes (particularmente work_mem) porque hay recursos de memoria que pueden ser ubicados por cliente, entonces el numero máximo de clientes puede sugerir el máximo de memoria utilizada posible. Generalmente, postgresql en un beun hardware puede soportar unos pocos cientos de conexiones. Si desea tener por miles, debe considerar utilizar connection pooling software para reducir la sobrecarga de conexión.

shared_buffers

La configuración del parametro shared_buffers determina cuanta memoria está dedicada a PostgreSQL para datos en caché. Por defecto es bajo porque en algunas plataformas (como versiones viejas de solaris y SGI) teniendo valores altos requieren acciones invasivas como recompilar el kernel. si tiene un sistema con 1GB o más de RAM, un valor inicial razonable es un 1/4 de dicha memoria. Si tiene menos deberá calcular cuidadosamente este valor de acuerdo al sistema operativo, cercano al 15% en los casos más comunes.

Note que en Windows y versiones viejas de Postgresql (anteriores a 8.1), altos valores de shared_buffers no son efectivos, teniendo buenos resultados manteniendolo relativamente bajo (alrededos de 50.000, quizás menos) y utilizando mejor el chaché del sistema operativo.

Es parecido a que elevando la cantidad de memoria de su sistema operativo le permitirá establecer el valor de shared_buffers alto. Si ud. lo establece más allá de lo soportado, obtendra un mensaje parecido a este:

IpcMemoryCreate: shmget(key=5432001, size=415776768, 03600) failed: Invalid argument 

This error usually means that PostgreSQL's request for a shared memory 
segment exceeded your kernel's SHMMAX parameter. You can either 
reduce the request size or reconfigure the kernel with larger SHMMAX. 
To reduce the request size (currently 415776768 bytes), reduce 
PostgreSQL's shared_buffers parameter (currently 50000) and/or 
its max_connections parameter (currently 12).

Vea Managing Kernel Resources para detalles de la corrección.

Cambiar este valor requerirá reiniciar la base de datos. Además, se trata de una difícil asignación de memoria; quedará todo fuera de la memoria virtual cuando se inicie la base de datos.

effective_cache_size

Este debe ser establecido en un monto estimado de cuanta memoria está disponible para memorai intermedia en el disco para el sistema operativo, luego de entrar a una cuenta usada por el Sistema operativo , dedicado a la memoria de postgresql y otras aplicaciones. Esta es una guía para como se espera que esté disponible la memoria en el búfer de caché de sistema operativo, no una asignación! Este valor es solo utilizado por el planeador de consultas para tener en cuenta los planes que puedan o no caber en memoria. si es establecido demasiado bajo, los indices no se utilizarían del modo que ud. esperaría.

Estableciendo effective_cache_size a la mitad del total de la memoria, debería ser la opción más conservadora y 3/4 para una opción más agresiva pero que sigue siendo razonable. Sería conveniente que elija este valor de acuerdo a las estadísitcas del sistema operativo. En sistemas Unix/linux, sume free + cached arrojados por los comandos free o top para tener una estimación. En Windows vea el tamaño del "System Cache" de la pestaña del Administrador de Tareas. Cambiar este valor no requiere reiniciar el servidor (un HUP sería suficiente o un RELOAD).

checkpoint_segments checkpoint_completion_target

PostgreSQL escribe las nuevas transacciones a la Base de Datos en un archivo llamado segmentos del WAL que son de 16MB de tamaño. Todo el tiempo el valor de checkpoint_segments escrito, por defecto 3, ocurre un 'checkpoint' o punto de chequeo. Estos pueden ser un recurso intensivo, y en un sistema moderno hacer uno cada 48 MB puede ocacionar cuellos de botella. Estableciendo este valor un poco más alto mejoraría este inconveniente. Si esta corriendo en una configuración pequeña, debería establecer esta al menos en 10, permitiendo alcanzar los objetivos.

Para sistemas de escritura masiva, valores desde 32 (punto de chequeo cada 512MB) a 256 (cada 128GB) son vias populares. Sistemas muy grandes utilizan muchísimo más disco lo que la recuperación llevaría más tiempo, por lo que debería elegir en que rango se encuentra comfortable. Normalmente los valores altos (>64/1GB) son utilizadas para cargas de gran aumento de volumen. de cualquier manera que elija los segmentos, necesitará un punto de chequeo por lo menos cada 5 minutosa menos que aumente el checkpoint_timeout (lo que no es necesario en muchos sistemas).

Comenzando con PostgreSQL 8.3, las escrituras del punto de chequeo se extiende un poco mientras comienza a trabajar en el próximo punto. Puedes difundir las nuevas escrituras, la reducción de escribir encima de la media, incrementando el parametro checkpoint_completion_target a su máximo de 0.9 (con el objetivo de terminar en el 90 % del tiempo antes del proximo checkpoint) en vez del valor por defecto de 0.5 (terminando cuando el próximo está en un 50%). Establecerlo a 0 daría algo similar a lo que era en las veriones más tempranas. La razón principal de que 0.9 no es el valor por defecto es que se necesita un valor alto de checkpoint_segments para la difusión funcione bien. Para mayor información de mejoras en el checkpoint vea Checkpoints and the Background Writer (aprenderá a mejorar de fondo los parámetros de escritura particularmente de 8.2 o menores, siendo un reto hacerlo bien).

autovacuum max_fsm_pages,max_fsm_relations

NOTA TRADUCCIÓN: Vaccum significa 'vaciar' literalmente hablando. El proceso de VACUUM lo que realiza es una limpieza de tuplas muertas que han sido marcadas como borradas o modificadas, ya que el motor de base de datos no las borra inmediatamente de la parte física para no sobrecargar las operaciones normales.

El proceso automático de Vacuum (autovacuum) realiza una serie de operaciones de mantenimiento en la base que ud. necesita. Como en 8.3, está activo por defecto y debería dejarlo así. En 8.1 y 8.2 ud. debe activarlo. Tenga en cuenta que en esas versiones antiguas, debe configurar sus variables para uqe tenga un efecto más rotundo; puede que no haga el trabajo suficiente por defecto si tiene una base de datos muy grande o realice muchas actualizaciones en los datos.

Generalmente, si ud. piensa que necesita desactivarlo si está tomando muchos recursos, significa que este haciendolo trabajar incorrectamente. Las respuestas a todos los problemas del vacuum es realizarlo más seguido, no menos, por lo que cada operación de vacuum individual tendrá menos que mantener.

Debería entonces incrementar el valor de max_fsm_pages y max_fsm_relations hasta donde lo necesite. El mapa de espacio libre (Free Space Map) es usado para seguir donde se encuentran lsa tuplas muertas (rows o filas) . Solo obtendrá un efectivo desbloqueo de las consultas de VACUUM si las tuplas muertas pueden ser listadas en el FSM. Como resultado, si no planea correr VACUUM frecuentemente, y espera muchas actualizaciones, debería asegurarse de que estos valores sean altos (y recuerde, estos valores son del largo del cluster, no de base de datos). debería ser bastante fácil establecer max_fsm_relations lo suficientemente alto; el problema más común es cuando max_fsm_pages no está establecido en un valor suficientemente alto. Una vez uqe FSM está lleno, VACUUM esta´ra deshabilitado para seguir y anotar las páginas muertas. En una base de datos con mucha actividad, esto necesita estar en un valor 1000... entonces, recuerde uqe cambiando estos valores requerirá reiniciar la base de datos, por lo que se debería establacer en márgenes cómodos.

Si ejecuta VACUUM VERBOSE en su base de datos, le dirá cuantas páginas y relaciones están en uso (y, superiores a la 8.3, cuales son los límites). por ejemplo:

INFO:  free space map contains 5293 pages in 214 relations
DETAIL:  A total of 8528 page slots are in use (including overhead).
8528 page slots are required to track all free space.
Current limits are:  204800 page slots, 1000 relations, using 1265 kB.

Si Ud. encuentra que sus valores son realmente bajos, ud. necesitará un VACUUM más intenso de su sistema, y posiblemente reindizando y reallizando vacuum full puedan ser necesarios también. Si está cerca de los límites para las franjas de las páginas, lo común en la práctica es establecer el doble de sus valores actuales, y quizás con un procentaje menor de incremento una vez que obtenga valores muy altos (en un rango de millones). Para los valores de 'max relations', tenga en cuenta que este valor incluye a todas als bases de datos de su cluster.

Otra situación es tener cuidado es al autovacuum_max_freeze_age. cuando una base de daots se acerca a este valor, realizará vacuum a cada tabla en la base de datos que no haya sido pasada por este proceso antes. En algunos sistemas no resultará con mucha actividad, pero en otros que tengan muchas tablas sin limpiar, puede ocacionar mayores ocurrencias (inclusive si el sistema está en estado de dump/restore). El significado de todo esto, incluo en un sitema con el valor de fsm's bien establecido, una vez que su sistema comience el proceso de vacuum de las tablas adicionales. su viejo fsm no será apropiado.

logging

Existen muchas cosas que se pueden registrar que pueden o no ser importantes para Ud. Debería investigar la documentación para todas las opciones, pero existen algunos trucos para empezar:

log_destination & log_directory (& log_filename): Lo que establece con estas opciones no es tan importante como saber que pueden dar información para determinar donde esta registrando el servidor. Una mejor práctica sería intentar y hacerlo similar a través de todos sus servidores. En algunos casos, el init script comnezará su base de datos utilizará el destino detallado en la linea de comandos, sobreescribiendo lo que esta en el postgresql.conf (por lo que puede tener un comportamiento disinto al utilizar pg_ctl en vez de el script de inicio).

log_min_error_statement: Quizás debería estar seguro que esté en al menos a nivel de error, para ver que sentencias generan error. Dejeló por defecto en las versiones más recientes.

log_min_duration_statement: No es necesario para el uso diario, pero puede generar registros de "consultas lentas" en su sistema.

log_line_prefix: Adhiere información al principio de cada linea. Una recomendación generica es '%t:%r:%u@%d:[%p]: ' : %t=timestamp, %u=db user name (usuario), %r=host desde donde se conecta, %d=base de datos conectada, %p=PID de la conexión. Puede ser que no sea obvio que el PID sea útil, pero puede ayudar a solucionar inconvenientes en el futuro, por lo que se recomienda en los registros.

log_statement: Puede elegir entre NONE(ninguna), DDL, MOD, ALL (todas). Usar "all" puede causar problemas de performance. "ddl" puede ser útil para ayudar a descubrir cambios hechos desde afuera de sus procesos recomendados, por otros "cowboy DBAs" por ejemplo.

default_statistics_target

El software de bases de datos recolecta estadísticas de cada una de las tables en su base de datos para decidir como se ejecutarán las consultas sobre ellas. Por defecto, no recolecta demasiada información, y si no esta obteniendo buenos planes de ejecución particularmente en las más largas (o variadas) tablas debería incrementar default_statistics_target y luego correr ANALYZE la base de datos nuevamente (o esperar al autovacuum que lo haga por ud.). Mucha gente cree que el valor por defecto para default_statistics_target en hardware más moderno debe ser llevado a 100 (de su valor de 10), ya que hace menos probable que se ejecute un mal plan sólo en el coste de la actividad de algunos antecedentes.

work_mem maintainance_work_mem

Si hace muchas ordenaciones complejas, y tiene bastante memoria, incrementando esta variable le permitirá a PostgreSQL a realizar ordenamientos más distendidos en memoria, obviamente incrementando la performance en comparación a las basadas en disco.

Este tamaño está aplicada a cada uno de los ordenamientos para cada usuario, y consultas complejas pueden utilizar múltiples buffers de memoria dedicados a estos. Establecerlo en 50MB y teniendo 30 usuarios ejecutando consultas y estaría utilizando 1.5GB de memoria real. Mas allá, si una consulta implica hacer ordenamientos con juntas de 8 tablas, requeriria 8 veces work_mem. Debería considerar lo que tiene establecido en max_connections para establecer el work_mem apropiadamente. Este es un valor donde los almacenes de datos, donde los usuarios ejecutan consultas extensas, podrían llegar a utilizar gigas en memoria.

maintenance_work_mem es utilizada para operaciones de vacuum (limpieza). Usar valores muy altos no ayudaría mucho, y porque deberpia reservar esa memoria cuando vacuum entre en escenario, para cuando realmente estaría con mejores propósitos. En esos casos 256mb es anecdóticamente razonable para los valores altos.

En 8.3 puede utilizar log_temp_files para verificar si los ordenamientos están utilizando disco en vez de caber en memoria. En versiones más antiguas, tenía que monitorear el tamaño de ellas mirando cuando espacio estaba siendo utilizado en los archivos de $PGDATA/base/<db oid>/pgsql_tmp. Puede ver como suceden los ordenamientos en disco en el EXPLAIN ANALYZE. Por ejemplo, si ve una linea que reza "Sort Method: external merge Disk: 7526kB", ud. sabría que el work_mem en al menos 8mb podría mejorar la velocidad en la que se ejecutaría la consulta (siendo ordenada en RAM en vez de realizar escrituras en disco).

wal_sync_method wal_buffers

Luego de cada transacción, Postgresql fuerza a comprometer para aliviar la WAL. Esto puede hacerse en varias formas, y en algunas plataformas las otras opciones pueden ser consideradas más rápidas que la opción conservadora por defecto. open_sync es la más común de las otras opciones que no están por defecto, en las plataformas que lo soportan pero por defecto es una de los métodos de fsync. Puede ver Tuning PostgreSQL WAL Synchronization para ver más detalles de esto. Tenga en cuenta que open_sync tiene algunos errores en algunas plataformas, y debería (como siempre) realizar varios testeos de inserciones masivas para estar seguro de que no hará inestable a su sistema.

Incrementando los wal_buffers de su pequeño valor por defecto con un par de kilobytes, debería ayudar para sistemas con grandes inserciones. Las estadísticas indican que incrementando a 1MB es suficiente para sistemas grandes. Cambiar este valor requiere reiniciar la base de datos.

constraint_exclusion

Si planea utilizar particionado de tablas, necesitará establecer en 'on' este valor. Hasta que esto genere una sobrecarga del planeador de consultas, es recomendable que lo deje en 'off' fuera de este escenario.

max_prepared_transactions

Este valor es usado para manejar 2 phase commit (transacción a dos fases). Si Ud. no lo utiliza (y no sabe lo que es, ni lo utiliza), puede llevar este valor a 0. Esto salvará un poco de memoria compartida. Para los sistemas de bases de datos con un alto numero (minimo 100) de conexiones concurrentes, tenga precaucion ya que este valor además afecta el número disponible de lugares para bloqueos en pg_locks, por lo que querrá dejar este en el valor por defecto. Existe una fórmula para saber cuanta memoria ubica in the docsy en el valor por defecto en postgresql.conf.

El cambio de max_prepared_transactions requiere reiniciar el servidor.

synchronous_commit

PostgreSQL solo puede utilizar la escritura intermedia segura si tiene una bateria respaldo. Vea WAL reliability para ver una introducción al tema . De en serio! Lealo ahora para comprender como tiene que funcionar correctamente su base de datos.

Puede que este limitado aproximadamente a 100 transaction commits por segundo y por cliente en situaciones donde no tenga una escritura intermedia durable (y posiblemente solo 500 segundos incluso con muchos clientes). Para situaciones donde perdidas pequeñas de datos son aceptables en retorno de una gran respuesta en como muchas actualizaciones puede hacer por segundo, considere pasar a cometer sincrónicamente. Es particularmente útil en la situación donde no tiene una batería de escritura intermedia en su disco controlador, porque ud. podría tener potencialmente miles de cometiciones por segundo en vez de unas cientos.

Fue introducida en PostgreSQL 8.3. Para versiones más tempranas de PostgreSQL, podría encontrar gente que recomienda que establezca fsync=off para aumentar las escrituras en sistemas con mucha carga. Es peligroso! una pérdida de energía podría resultar en una base de datos corrupta e incapaz de reiniciar. Este sistema no introduce estos riesgos en su base de datos, solo pérdida de algunos datos.

random_page_cost

Este valor sugiere al optimizador cuanto tiempo le llevará a tus discos encontrar una página aleatoria de disco, como cuanto lleva una lectura múltiple secuencial (con un costo de 1.0). Si tiene discos rápidos, como los comúnmente encontrables con RAID de SCSI, sería apropiado dejarlo bajo, con cual animaría al optimizador utilizar rastreos de acceso aleatorios. Algunos creen que 4.0 es muy alto para el hardware actual, sin embargo no es inusual en los administradores estandarizar el valor entre 2.0 y 3.0. En algunos casos este comportamiento es un vestigio de versiones tempranas de PostgreSQL que tenían el valor random_page_cost muy alto lo que hoy representa a entorpecer el plan de optimización (y establecerlo en 2.0 o mayor era regularmente necesario). Dado que estas estimaciones de gastos que son sólo eso (estimaciones) no debería afectar tratar con valores menores.

Pero no es aquí donde debería empezar a buscar problemas en el plan. Tenga en cuenta que random_page_cost esta muy abajo en esta lista (en el final, de hecho). Si esta teniendo planes malos, no debería ser el primero en el que debería pensar, incluso bajar este valor puede ser efectivo. sin embargo, debería confirmar que este trabajando correctamente el autovacuum, que esté recolectando estadísticas asiduamente y que tiene establecidas correctamente los parámetros de cantidad de memoria en el servidor (todas las cosas anteriores). una vez que haya revisado todas estas cosas importantes, y ud. sigue teniendo malos planes solo luego de eso debería revisar si tener bajo este valor le sigue siendo útil.