funciones - herramientas para monitorear postgresql

Optimizar PostgreSQL para pruebas rápidas (2)

Usa diferentes diseños de disco:

disco diferente para $ PGDATA
disco diferente para $ PGDATA / pg_xlog
disco diferente para archivos tem (por base de datos $ PGDATA / base // pgsql_tmp) (ver nota sobre work_mem)

Ajustes de postgresql.conf:

shared_memory: 30% de RAM disponible pero no más de 6 a 8GB. Parece que es mejor tener menos memoria compartida (2GB - 4GB) para cargas de trabajo intensivas de escritura
work_mem: principalmente para consultas de selección con ordenaciones / agregaciones. Esto es por configuración de conexión y la consulta puede asignar ese valor varias veces. Si los datos no pueden caber, se usa el disco (pgsql_tmp). Marque "explicar analizar" para ver cuánta memoria necesita
fsync y synchronous_commit: los valores predeterminados son seguros, pero si puede tolerar la pérdida de datos, puede desactivarlos
random_page_cost: si tiene SSD o matriz RAID rápida, puede reducir esto a 2.0 (RAID) o incluso a menor (1.1) para SSD
checkpoint_segments: puede ir más alto 32 o 64 y cambiar checkpoint_completion_target a 0.9. Un valor más bajo permite una recuperación posterior al accidente más rápida

Estoy cambiando a PostgreSQL desde SQLite para una aplicación típica de Rails.

El problema es que las especificaciones de ejecución se volvieron lentas con PG.
En SQLite tomó ~ 34 segundos, en PG es ~ 76 segundos, que es más de 2 veces más lento .

Así que ahora quiero aplicar algunas técnicas para llevar el rendimiento de las especificaciones a la par con SQLite sin modificaciones de código (lo ideal sería simplemente establecer las opciones de conexión, lo que probablemente no sea posible).

Un par de cosas obvias desde lo alto de mi cabeza son:

Disco RAM (buena configuración con RSpec en OSX sería bueno verlo)
Tablas no registradas (¿se puede aplicar en toda la base de datos, así que no tengo que cambiar todas las secuencias de comandos?)

Como habrás comprendido, no me preocupan la fiabilidad y el resto (el DB es simplemente una cosa descabellada aquí).
Necesito aprovechar al máximo el PG y hacerlo lo más rápido posible .

La mejor respuesta idealmente describiría los trucos para hacer exactamente eso, la configuración y los inconvenientes de esos trucos.

ACTUALIZACIÓN: fsync = off + full_page_writes = off solo disminuyó el tiempo a ~ 65 segundos (~ -16 segundos). Buen comienzo, pero lejos del objetivo de 34.

ACTUALIZACIÓN 2: Intenté usar el disco RAM, pero la ganancia de rendimiento estaba dentro de un margen de error. Entonces no parece valer la pena.

ACTUALIZACIÓN 3: * Encontré el mayor cuello de botella y ahora mis especificaciones se ejecutan tan rápido como las de SQLite.

El problema fue la limpieza de la base de datos que hizo el truncamiento . Aparentemente, SQLite es demasiado rápido allí.

Para "arreglarlo" abro una transacción antes de cada prueba y la vuelvo a enrollar al final.

Algunos números para ~ 700 pruebas.

Truncamiento: SQLite - 34s, PG - 76s.
Transacción: SQLite - 17s, PG - 18s.

Aumento de velocidad de 2x para SQLite. Aumento de velocidad 4x para PG.

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

Qué no hacer

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

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

Si realmente quiere un sistema basado en ramdisk, initdb un clúster completamente nuevo en el ramdisk initdb una nueva instancia de PostgreSQL en el ramdisk, para que tenga una instancia de PostgreSQL completamente desechable.

Configuración del servidor PostgreSQL

Al probar, puede configurar su servidor para una operación no duradera pero más rápida .

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

Huelga decir que nunca debe habilitar fsync=off en producción a menos que esté usando 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 desactivar full_page_writes , ya que no sirve para nada. Tenga en cuenta que fsync=off y full_page_writes aplican en el nivel del clúster , por lo que afectan a todas las bases de datos en su instancia de PostgreSQL.

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

Si tiene la opción de alterar levemente el DDL, también puede usar UNLOGGED tablas UNLOGGED en Pg 9.1+ para evitar por completo el registro WAL y obtener un impulso de velocidad real a costa de que las tablas se borren si el servidor falla. No hay una opción de configuración para desactivar todas las tablas, debe establecerse durante CREATE TABLE . Además de ser bueno para las pruebas, es útil si tiene tablas llenas de datos generados o no importantes en una base de datos que de otro modo contiene elementos que necesita para estar seguro.

Verifique sus registros y vea si recibe advertencias sobre demasiados puntos de control. Si es así, debería aumentar sus checkpoint_segments . Es posible que también desee sintonizar su checkpoint_completion_target para suavizar las escrituras.

Sintonice shared_buffers para que se ajuste a su carga de trabajo. Esto depende del sistema operativo, depende de qué más esté sucediendo con su equipo y requiere un poco de prueba y error. Los valores predeterminados 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 siguientes; 9.3 y superior cambiaron la forma en que usan la memoria compartida para evitar eso.

Si usa solo un par de conexiones que hacen mucho trabajo, aumente work_mem para darles más RAM para jugar, etc. Tenga cuidado de que una configuración demasiado alta de work_mem puede causar problemas de falta de memoria porque es ordenar no por conexión, por lo que una consulta puede tener muchos géneros anidados. En realidad, solo tiene que aumentar work_mem si puede ver work_mem que se log_temp_files en el disco en EXPLAIN o se registran con la configuración log_temp_files (recomendado), pero un valor mayor también puede permitir que Pg elija planes más inteligentes.

Como dijo otro afiche aquí, es aconsejable colocar el xlog y las tablas / índices principales en discos duros separados si es posible. Las particiones separadas son bastante inútiles, realmente quieres discos separados. Esta separación tiene un beneficio mucho menor si se ejecuta con fsync=off y casi ninguna si está utilizando tablas UNLOGGED .

Finalmente, sintoniza tus consultas. Asegúrese de que su random_page_cost y seq_page_cost reflejen el rendimiento de su sistema, asegúrese de que effective_cache_size sea correcto, etc. Utilice EXPLAIN (BUFFERS, ANALYZE) para examinar planes de consultas individuales, y active el módulo auto_explain para informar todas las consultas lentas. A menudo puede mejorar el rendimiento de las consultas de forma espectacular con solo crear un índice adecuado o modificar los parámetros de costos.

AFAIK no hay forma de configurar una base de datos o un clúster como UNLOGGED . Sería interesante poder hacerlo. Considera preguntar en la lista de correo de PostgreSQL.

Afinación del sistema principal de host

También se puede hacer algo a nivel del sistema operativo. Lo más importante que puede hacer es convencer al sistema operativo de que no vacíe las escrituras en el disco de manera agresiva, ya que realmente no le importa cuándo / si llegan al disco.

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

El único problema con la configuración de reescritura de escritura demasiado floja es que una descarga de otro programa puede causar que todos los búferes acumulados de PostgreSQL también se vacíen, causando grandes bloqueos mientras todo bloquea las escrituras. Es posible que pueda aliviar esto ejecutando PostgreSQL en un sistema de archivos diferente, pero algunas descargas pueden ser a nivel de dispositivo o de nivel de host completo, no de nivel de sistema de archivos, por lo que no puede confiar en eso.

Esta sintonización realmente requiere jugar con la configuración para ver qué funciona mejor para su carga de trabajo.

En los núcleos más nuevos, es posible que desee asegurarse de que vm.zone_reclaim_mode se establezca en cero, ya que puede causar graves problemas de rendimiento con los sistemas NUMA (la mayoría de los sistemas actualmente) debido a las interacciones con la forma en que PostgreSQL administra los shared_buffers .

Consulta y ajuste de la carga de trabajo

Estas son cosas que REALMENTE requieren cambios de código; es posible que no te convengan. Algunas son cosas que podrías aplicar.

Si no estás trabajando por lotes en transacciones más grandes, comienza. Muchas transacciones pequeñas son costosas, por lo que debe lotear cosas cada vez que sea posible y práctico hacerlo. Si usa la confirmación asincrónica, esto es menos importante, pero aún así es muy recomendable.

Siempre que sea posible, use 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, manipularlos como necesites, luego hacer un INSERT INTO ... SELECT ... para copiarlo en la mesa final. Tenga en cuenta que las tablas temporales son por sesión; si su sesión finaliza o pierde su conexión, la tabla temporal se va y ninguna otra conexión puede ver el contenido de la (s) tabla (s) temporal (es) de la sesión.

Si está utilizando PostgreSQL 9.1 o una UNLOGGED más nueva, puede usar tablas UNLOGGED para datos que puede permitirse perder, como el estado de la sesión. Estos son visibles en diferentes sesiones y se conservan entre conexiones. Se truncan si el servidor se cierra de manera imprudente, por lo que no se pueden usar para nada que no se pueda volver a crear, pero son geniales para cachés, vistas materializadas, tablas de estado, etc.

En general, no DELETE FROM blah; . Utilice TRUNCATE TABLE blah; en lugar; es mucho más rápido cuando estás volcando todas las filas en una tabla. Trunque muchas tablas en una llamada TRUNCATE si puede. Sin embargo, hay una advertencia si estás haciendo muchos TRUNCATES de mesas pequeñas una y otra vez; ver: velocidad de truncamiento Postgresql

Si no tiene índices en claves externas, los DELETE s que implican las claves primarias a las que hacen referencia esas claves foráneas serán terriblemente lentos. Asegúrese de crear dichos índices si alguna vez espera DELETE de 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 utilizar un conjunto mínimo de índices y permita que los escaneos de índice de mapas de bits los combinen en lugar de mantener demasiados índices enormes y costosos de varias columnas. Cuando se requieran índices, intente rellenar primero la tabla y luego cree índices al final.

Hardware

Tener suficiente memoria RAM para mantener toda la base de datos es una gran ganancia si puede administrarla.

Si no tienes suficiente memoria RAM, el almacenamiento más rápido se puede obtener mejor. Incluso un SSD barato hace una gran diferencia con respecto a la oxidación en rotación. Sin embargo, no confíe en SSD baratos para producción, a menudo no son seguros y pueden consumir sus 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. Debe ser una referencia útil.

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