Optimice PostgreSQL para pruebas rápidas

Primero, siempre use la última versión de PostgreSQL. Las mejoras de rendimiento siempre están llegando, por lo que probablemente estés perdiendo el tiempo si estás afinando una versión antigua. Por ejemplo, PostgreSQL 9.2 mejora significativamente la velocidad de TRUNCATE y, por supuesto, agrega escaneos de solo índice. Incluso las versiones menores siempre deben seguirse; consulte la política de versiones .

No lo hagas

Hacer NO poner un espacio de tabla en un disco RAM u otro no duradero almacenamiento.

Si pierde un espacio de tabla, toda la base de datos puede estar dañada y ser difícil de usar sin un trabajo significativo. Hay muy poca ventaja en comparación con el uso de tablas UNLOGGED y tener mucha RAM para la caché de todos modos.

Si realmente desea un sistema basado en ramdisk, initdb un clúster completamente nuevo en el ramdisk mediante initdbuna nueva instancia PostgreSQL en el ramdisk, por lo que tiene una instancia PostgreSQL completamente desechable.

Servidor PostgreSQL configuración

Al realizar pruebas, puede configurar su servidor para un funcionamiento no duradero pero más rápido.

Este es uno de los únicos usos aceptables para el fsync=off configuración en PostgreSQL. Esta configuración prácticamente le dice a PostgreSQL que no se moleste con las escrituras ordenadas o cualquiera de esas otras cosas desagradables de protección de integridad de datos y seguridad de bloqueo, dándole permiso para eliminar totalmente sus datos si pierde energía o tiene un bloqueo del sistema operativo.

No hace falta decir, que nunca debe habilitar fsync=off en producción a menos que esté utilizando Pg como una base de datos temporal para datos que puede volver a generar desde otro lugar. Si y solo si está haciendo para desactivar fsync también puede activar full_page_writes apagado, ya que ya no hace ningún bien entonces. Tenga en cuenta que fsync=off y full_page_writes se aplican en el nivel cluster, por lo que afectan a todas las bases de datos en su instancia de PostgreSQL.

Para el uso de producción, posiblemente puede usar synchronous_commit=off y establecer un commit_delay, ya que obtendrá muchos de los mismos beneficios como fsync=off sin el gigante riesgo de corrupción de datos. Tiene una pequeña ventana de pérdida de datos recientes si habilita la confirmación asincrónica , pero eso es todo.

Si tiene la opción de alterar ligeramente el DDL, también puede usar UNLOGGED tablas en Pg 9.1+ para evitar completamente el registro WAL y obtener un aumento de velocidad real a costa de que las tablas se borren si el servidor falla. No hay ninguna opción de configuración para que todas las tablas se desbloqueen, debe configurarse durante CREATE TABLE. Además de ser bueno para probar esto es útil si tiene tablas llenas de datos generados o sin importancia en una base de datos que de otra manera contiene cosas que necesita para estar seguro.

Revise sus registros y vea si recibe advertencias sobre demasiados puntos de control. Si es así, debe aumentar su checkpoint_segments. También es posible que desee ajustar su checkpoint_completion_target para suavizar las escrituras.

Ajuste shared_buffers para adaptarse a su carga de trabajo. Esto depende del sistema operativo, depende de lo que esté pasando con su máquina, y requiere un poco de ensayo y error. Los valores por defecto son extremadamente conservadores. Es posible que necesite aumentar el límite máximo de memoria compartida del sistema operativo si aumenta shared_buffers en PostgreSQL 9.2 y versiones posteriores; 9.3 y versiones posteriores cambiaron la forma en que usan la memoria compartida para evitar eso.

Si estás usando solo un par de conexiones que hacen mucho trabajo, aumenta work_mem para darles más RAM con la que jugar para ordenar, etc. Tenga en cuenta que un ajuste work_mem demasiado alto puede causar problemas de falta de memoria porque es por orden, no por conexión, por lo que una consulta puede tener muchas órdenes anidadas. Solo realmente tiene que aumentar work_mem si puede ver las clases derramándose al disco en EXPLAIN o conectado con el log_temp_files establecer (recomendado), pero un valor más alto también puede permitir que Pg elija planes más inteligentes.

Como dijo otro poster aquí, es aconsejable poner el xlog y las tablas/índices principales en discos DUROS separados si es posible. Particiones separadas es bastante inútil, usted realmente quiere separado unidad. Esta separación tiene mucho menos beneficio si se está ejecutando con fsync=off y casi ninguno si se está utilizando UNLOGGED tablas.

Finalmente, afine sus consultas. Asegúrese de que su random_page_cost y seq_page_cost reflejan el rendimiento de su sistema, asegúrese de que su effective_cache_size es correcto, etc. Utilice EXPLAIN (BUFFERS, ANALYZE) para examinar planes de consulta individuales, y active el módulo auto_explain para informar de todas las consultas lentas. A menudo, puede mejorar drásticamente el rendimiento de las consultas simplemente creando un índice apropiado o ajustando el costo parámetros.

AFAIK no hay manera de establecer una base de datos completa o clúster como UNLOGGED. Sería interesante poder hacerlo. Considere preguntar en la lista de correo PostgreSQL.

Host OS tuning

También hay algunos ajustes que puedes hacer a nivel del sistema operativo. Lo principal que podría querer hacer es convencer al sistema operativo de no vaciar las escrituras en el disco agresivamente, ya que realmente no le importa cuándo/si llegan al disco.

En Linux se puede controlar esto con la configuración de del subsistema de memoria virtual dirty_*, como dirty_writeback_centisecs.

El único problema con ajustar la configuración de writeback para ser demasiado flojo es que un flush por algún otro programa puede causar que todos los búferes acumulados de PostgreSQL sean flushed también, causando grandes paradas mientras todo se bloquea en las escrituras. Es posible que pueda aliviar esto ejecutando PostgreSQL en un sistema de archivos diferente, pero algunos flushes pueden ser a nivel de dispositivo o a nivel de host completo, no a nivel de sistema de archivos, por lo que no puede confiar en eso.

Este ajuste realmente requiere jugar con los ajustes para ver qué funciona mejor para su carga de trabajo.

En kernels más nuevos, es posible que desee asegurarse de que vm.zone_reclaim_mode esté establecido en cero, ya que puede causar graves problemas de rendimiento con los sistemas NUMA (la mayoría de los sistemas en estos días) debido a las interacciones con la forma en que PostgreSQL administra shared_buffers.

Consulta y ajuste de carga de trabajo

Estas son cosas que requieren cambios de código; puede que no te convengan. Algunas son cosas que podrías ser capaz de aplicar.

Si no está agrupando el trabajo en transacciones más grandes, comience. Muchas transacciones pequeñas son caras, por lo que debe realizar lotes siempre que sea posible y práctico hacerlo. Si está utilizando async commit esto es menos importante, pero aún así es altamente recomendable.

Siempre que sea posible, utilice tablas temporales. No generan tráfico WAL, por lo que son mucho más rápidos para inserciones y actualizaciones. A veces vale la pena sorber un montón de datos en una tabla temporal, manipulándolo como sea necesario, luego haciendo un INSERT INTO ... SELECT ... para copiarlo a la mesa final. Tenga en cuenta que las tablas temporales son por sesión; si su sesión termina o pierde su conexión, la tabla temporal desaparece y ninguna otra conexión puede ver el contenido de la(s) tabla (s) temporal (es) de una sesión.

Si está usando PostgreSQL 9.1 o posterior, puede usar UNLOGGED tablas de datos que puede permitirse perder, como el estado de la sesión. Estos son visibles a través de diferentes sesiones y se conservan entre relación. Se truncan si el servidor se apaga sin limpiar, por lo que no se pueden usar para nada que no pueda recrear, pero son excelentes para cachés, vistas materializadas, tablas de estados, etc.

En general, no DELETE FROM blah;. Use TRUNCATE TABLE blah; en su lugar; es mucho más rápido cuando está volcando todas las filas en una tabla. Trunca muchas tablas en una llamada TRUNCATE si puedes. Sin embargo, hay una advertencia si está haciendo muchas TRUNCATES tablas pequeñas una y otra vez; vea: Truncamiento de Postgresql velocidad

Si no tiene índices en claves foráneas, DELETElas claves primarias referenciadas por esas claves foráneas serán terriblemente lentas. Asegúrese de crear tales índices si alguna vez espera DELETE desde la(s) tabla (s) referenciada (s). Los índices no son necesarios para TRUNCATE.

No cree índices que no necesite. Cada índice tiene un costo de mantenimiento. Intente usar un conjunto mínimo de índices y deje que los escaneos de índices de mapa de bits los combinen en lugar de mantener demasiados enormes y costosos índices de varias columnas. Si se requieren índices, intente rellenar la tabla primero y, a continuación, cree índices al final.

Hardware

Tener suficiente RAM para mantener toda la base de datos es una gran victoria si puedes gestionarla.

Si no tiene suficiente RAM, cuanto más rápido pueda obtener el almacenamiento, mejor. Incluso un SSD barato hace una gran diferencia sobre el óxido giratorio. Sin embargo, no confíe en SSD baratos para la producción, a menudo no son crashsafe y podrían comerse su datos.

Aprendizaje

El libro de Greg Smith, PostgreSQL 9.0 High Performance sigue siendo relevante a pesar de referirse a una versión algo más antigua. Debería ser una referencia útil.

Únase a la lista de correo general de PostgreSQL y sígala.

Leyendo:{[49]]}

• Afinar su servidor PostgreSQL-PostgreSQL wiki
• Número de conexiones de base de datos-PostgreSQL wiki