Me cambio a PostgreSQL desde SQLite para una típica aplicación Rails.

El problema es que la ejecución de las especificaciones se convirtió lento con el PG.

En SQLite tomó ~34 segundos, en la PG es ~76 segundos que es más de 2x más lento.

Así que ahora quiero aplicar algunas técnicas para llevar el desempeño de las especificaciones a la par con SQLite sin modificaciones del código (idealmente sólo por la configuración de las opciones de conexión, que probablemente no es posible).

Par de cosas obvias de la parte superior de mi cabeza son:

  • Disco RAM (configuración buena con RSpec en OSX, sería bueno ver)
  • Talados tablas (puede ser aplicado en toda la base de datos así que no tengo cambio de todos los scripts?)

Como hayan comprendido no me importa acerca de la fiabilidad y el resto (el DB es algo descartable cosita aquí).

Necesito conseguir el la mayoría de los PG y hacer tan rápido como sea posible.

Mejor respuesta ideal sería describir la trucos para hacer precisamente eso, el programa de instalación y los inconvenientes de los trucos.

ACTUALIZACIÓN: fsync = off + full_page_writes = off sólo reduce el tiempo de a ~65 segundos (~-16 segundos). Buen comienzo, pero lejos de la meta de 34.

ACTUALIZACIÓN 2: me trató de uso de la memoria RAM disco pero la ganancia de rendimiento se encontraba dentro de un margen de error. Así que no parece valer la pena.

ACTUALIZACIÓN 3:*
He encontrado el mayor cuello de botella y ahora mi especificaciones de correr tan rápido como el SQLite queridos.

La cuestión de la limpieza de base de datos que hizo el truncamiento. Al parecer SQLite es muy rápido allí.

Para «arreglar» se me abre una transacción antes de cada prueba y rodar de nuevo en la final.

De algunos números de ~700 pruebas.

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

2x de aumento de velocidad para SQLite.
4x aumento de la velocidad de los PG.

  • Yo realmente dudo de que tendrás que ir tan rápido como SQLite. SQLite con un único usuario es increíblemente rápido. SQLite diseño es muy rápido con baja en cuentas de usuario y escalas mal; Pg diseño de escalas así, pero no es tan rápido para granel simple trabajo con un solo usuario.
  • Me doy cuenta de que, pero hay un caso particular que espero para optimizar la PG para (corridas de prueba) por lo que es tan rápido como sea posible. No me importa que sea un poco más lento que hay, pero 2.2 x es un poco demasiado lento. A ver a qué me refiero?
  • +1 yo estaría muy interesado en las actualizaciones en el disco RAM enfoque si tienes ningún resultado al respecto.
  • Definitivamente, voy a publicar aquí. Pero necesito algo de tiempo ya que estoy trabajando en otras cosas y «investigando» el PG cosas en el «fondo».
  • es la inserción de los datos de su problema o consulta? No está claro a partir de su pregunta.
  • Es tanto. Algunas de las especificaciones de crear registros a continuación, elimine los. La mayoría de insertar, consultar, eliminar.
  • Si el espacio en un disco RAM no proporciona ninguna ganancia, probablemente significa que los archivos WAL (pg_xlog directorio) que debe ser trasladado a un disco ram. También desactivar autovacuum si no lo ha hecho ya.
  • me pregunto por qué el truncamiento es tan lento en postgresql.
  • Dmytrii, ¿cómo ir sobre la configuración de las transacciones exactamente? Pensé que es lo que esta línea fue en spec_helper: config.use_transactional_fixtures = true. Estoy teniendo el mismo problema que tú con postgres ser más de dos veces tan lento como sqlite y poco confundido acerca de cómo solucionarlo.
  • sí, el transaccional características están habilitadas de forma predeterminada en los Rieles. Pero yo estaba usando DatabaseCleaner y tuvo que volver.
  • Impresionante. Tuve la misma experiencia cuando se cambia a Postgres de SQLite. La velocidad relativa fue de 1,9 x más lento sin spork, 2.1 más lento con spork. Voy a intentar su corrección.
  • Estoy en la misma situación. Una cosa que me ayudó mucho es el uso de las enumeraciones en lugar de texto para los casos en que puedo – tenemos alrededor de 40 tablas para cada juego del tipo de activo, y el tipo de material tiene 700 columnas, muchos de ellos son la textura de la configuración de calidad (miplevels, filtrado, etc). La he convertido a todos en pg enumeraciones, y esto redujo el tamaño de la base de datos significativamente (4 bytes para enum oid, en lugar de variables char allí). En general estoy feliz 🙂 pero sqlite es un poder en sí mismo demasiado – dos muy impresionante proyectos
  • En mi caso fsync = off corte de prueba de integración vez en menos de la mitad (incluyendo la compilación). full_page_writes = off no tenía medibles efecto adicional. Un disco ram (yo sólo lo utiliza tmpfs) trajo resultados variables (a veces peor que sin que por alguna razón). El ramdisk también obtener el completo ganancia de velocidad sin establecer fsync = off.

2 Comentarios

  1. 268

    Primero, utilice siempre la última versión de PostgreSQL. Las mejoras de rendimiento son siempre viene, así que probablemente usted está perdiendo su tiempo si usted se está sintonizando una versión antigua. Por ejemplo, PostgreSQL 9.2 mejora considerablemente la velocidad de TRUNCAR y, por supuesto, añade índice-sólo analiza. Incluso las versiones menores siempre deben seguirse; véase la la versión de la política de.

    No hacer

    Do NO, poner un espacio en un disco ram o no de almacenamiento duradero.

    Si usted pierde un espacio de tablas de la base de datos completa puede ser dañado y difícil de usar sin un trabajo importante. Hay muy poco de ventaja a esto, en comparación con sólo el uso de UNLOGGED tablas y tener mucha memoria RAM para la caché de todos modos.

    Si usted realmente quiere un ramdisk sistema basado en initdb un nuevo clúster en el ramdisk por initdbing una nueva instancia de PostgreSQL en el ramdisk, por lo que es totalmente desechable PostgreSQL instancia.

    Servidor PostgreSQL configuración

    Cuando se prueba, usted puede configurar su servidor para no duradero, pero un funcionamiento más rápido.

    Este es uno de los únicos usos aceptables de la fsync=off configuración de PostgreSQL. Esta configuración bastante dice PostgreSQL no molestar con ordenó escribe o cualquiera de las otras desagradable de integridad de los datos-protección y bloqueo de seguridad de las cosas, dándole permiso para totalmente la basura de los datos si se pierde el poder o tener una caída del sistema operativo.

    Hace falta decir que usted debe nunca permitir fsync=off en la producción, a menos que usted está usando Pg como una base de datos temporal para los datos puede volver a generar desde otro lugar. Si y sólo si está haciendo a su vez fsync off también puede activar la full_page_writes fuera, como que ya no hace ningún bien a continuación. Ten en cuenta que fsync=off y full_page_writes aplicar en la clúster nivel, por lo que afecta a todos bases de datos en la instancia de PostgreSQL.

    Para el uso de producción se pueden utilizar synchronous_commit=off y establecer un commit_delay, como vas a conseguir que muchos de los mismos beneficios de fsync=off sin el gigante de datos de riesgos de corrupción. Usted tiene una pequeña ventana de la pérdida de los últimos datos, si desea habilitar async commit – pero eso es todo.

    Si usted tiene la opción de alterar ligeramente el DDL, también puede utilizar UNLOGGED tablas en Pg 9.1+ evitar completamente WAL registro y obtener un real aumento de la velocidad en el costo de las tablas llegar borra si se bloquea el servidor. No hay ninguna opción de configuración para hacer que todas las tablas talados, se debe establecer durante CREATE TABLE. Además de ser bueno para las pruebas de esto es útil si usted tiene mesas llenas de generados o importancia de los datos en una base de datos que de otra forma contiene cosas que usted necesita para estar seguro.

    Compruebe los registros y ver si usted está recibiendo las advertencias sobre demasiados puntos de control. Si usted es, usted debe aumentar su checkpoint_segments. Usted también puede querer ajustar su checkpoint_completion_target para suavizar escribe.

    Melodía shared_buffers para adaptarse a su carga de trabajo. Este es OS-dependiente, lo que depende de otra cosa que está pasando con su máquina, y requiere un poco de ensayo y error. Los valores predeterminados son extremadamente conservador. Puede que sea necesario aumentar el OS es el máximo de memoria compartida límite si aumenta shared_buffers en PostgreSQL 9.2, y el de abajo; 9.3 y por encima cambiado la forma en que el uso de memoria compartida para evitar que.

    Si usted está usando un sólo un par de conexiones que hacer un montón de trabajo, aumentar work_mem para darles más memoria RAM para jugar con tipo etc. Ten en cuenta que un exceso de work_mem configuración puede ser a causa de problemas de memoria, porque es por ordenar no por conexión de modo que una consulta puede tener muchos anidada tipo. Sólo realmente a tener que aumentar la work_mem si puedes ver que tipo de derramar en el disco en EXPLAIN o conectado con el log_temp_files configuración (recomendado), pero un alto valor también puede dejar Pg de selección más inteligente planes.

    Como dijo el otro cartel aquí es aconsejable poner el xlog y las principales tablas y los índices en diferentes discos Duros, si es posible. Independiente de las particiones es bastante inútil, usted realmente desea unidades separadas. Esta separación tiene mucho menos beneficio, si usted está funcionando con fsync=off y casi ninguno si usted está usando UNLOGGED tablas.

    Finalmente, ajustar 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 correcta, etc. Uso EXPLAIN (BUFFERS, ANALYZE) para examinar los distintos planes de consulta, y gire el auto_explain módulo para informar de todas las consultas lentas. Usted puede mejorar el rendimiento de las consultas drásticamente mediante la creación de un índice adecuado o con el ajuste del costo de los parámetros.

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

    Sistema operativo del Host de optimización

    Hay algunos ajustes que usted puede hacer en el nivel de sistema operativo, también. La principal cosa que usted puede hacer es convencer a los que el sistema operativo no a ras de escritura en disco de forma agresiva, ya que realmente no me importa si lo hacen en el disco.

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

    El único problema con la optimización de configuración de reescritura a ser demasiado flojo es que un color por algún otro programa podría causar que todos PostgreSQL acumulado de los búferes se vacían demasiado, causando grandes puestos, mientras que todo los bloques de escrituras. Usted puede ser capaz de aliviar mediante la ejecución de PostgreSQL en un sistema de archivos diferente, pero algunos rubores puede ser a nivel del dispositivo o la totalidad-de nivel de host no sistema de ficheros de nivel, por lo que no se puede confiar en eso.

    Este ajuste realmente requiere jugando con la configuración para ver lo que funciona mejor para su carga de trabajo.

    En los núcleos nuevos, puede que desee asegurarse de que vm.zone_reclaim_mode se establece en cero, ya que puede causar graves problemas de rendimiento con sistemas NUMA (la mayoría de los sistemas en estos días) debido a las interacciones con la forma en PostgreSQL gestiona shared_buffers.

    De consulta y carga de trabajo de optimización

    Estas son cosas que no requieren cambios en el código; puede que no se adapte a ti. Algunas son cosas que usted podría ser capaz de aplicar.

    Si usted no está de lotes de trabajo en las transacciones más grandes, de inicio. Un montón de pequeñas transacciones son caros, por lo que debe lote de cosas de siempre que es posible y práctico hacerlo. Si usted está usando async cometer este es menos importante, pero aún así muy recomendable.

    Siempre que sea posible el uso de tablas temporales. No generan WAL tráfico, por lo que son mucho más rápida para las inserciones y actualizaciones. A veces vale la pena de sorber un montón de datos en una tabla temporal, manipularlo sin embargo, usted necesita para hacer un INSERT INTO ... SELECT ... para copiarlo a la mesa final. Tenga en cuenta que las tablas temporales son por sesión; si la sesión termina o se pierde la conexión, a continuación, la tabla temporal se va, y no de otro tipo de conexión puede ver el contenido de una sesión de temp de tabla(s).

    Si usted está usando PostgreSQL 9.1 o posterior, puede utilizar TALADOS tablas de datos que usted puede permitirse el lujo de perder, como el estado de la sesión. Estos son visibles a través de diferentes sesiones y conservado entre las conexiones. Consiguen truncado si el servidor se apaga uncleanly por lo que no puede ser utilizado para cualquier cosa que usted no puede volver a crear, pero son grandes para las cachés, vistas materializadas, tablas de estado, etc.

    En general, no DELETE FROM blah;. Uso TRUNCATE TABLE blah; lugar; es mucho más rápido cuando estás dumping todas las filas en una tabla. Truncar muchas tablas en una TRUNCATE llamada si usted puede. Hay una salvedad, si usted está haciendo un montón de TRUNCATES de pequeñas tablas una y otra vez, aunque; ver: Postgresql Truncamiento de velocidad

    Si usted no tiene los índices claves foráneas, DELETEs que implican las claves principales que hace referencia a aquellos extranjeros claves será horriblemente lento. Asegúrese de crear tales índices si alguna vez esperar a DELETE de la tabla de referencia(s). Los índices no son necesarios para TRUNCATE.

    No crear índices que no necesita. Cada índice tiene un coste de mantenimiento. Pruebe a utilizar un conjunto mínimo de índices y dejar que el análisis de índice de mapa de bits se combinan ellos, en lugar de mantener demasiados grandes y caras multi-índices de las columnas. Donde los índices son necesarios, intentar rellenar la tabla de primera, a continuación, crear índices en la final.

    Hardware

    Tener suficiente memoria RAM para contener toda la base de datos es una gran victoria si se puede gestionar.

    Si usted no tiene suficiente memoria RAM, el más rápido de almacenamiento se puede obtener el mejor. Baratas SSD hace una enorme diferencia con el hilado de la roya. No confíe en hoteles de unidades de estado sólido para la producción, sin embargo, a menudo no crashsafe y podría comer sus datos.

    Aprendizaje

    Greg Smith, el libro, PostgreSQL 9.0 De Alto Rendimiento sigue siendo pertinente, a pesar de referirse a un poco de la versión anterior. Debe ser una referencia útil.

    Unirse a la PostgreSQL general de la lista de correo y siga.

    Lectura:

    • También puedo recomendar PostgreSQL 9.0 Alto Rendimiento por @GregSmith, es realmente una gran lectura. El libro cubre todos los aspectos de la optimización del rendimiento de diseño de disco para la optimización de consultas y le da una muy buena comprensión de la PG interna.
    • Gracias chicos, pondrá definitivamente en mi lectura de la cola. Supongo que el hecho de que el libro es de 2 años de edad, no es un problema ya que probablemente no mucho tiene cambios en la PG sí mismo. A la derecha?
    • Me encanta ese libro también. No, no es un problema. El libro cubre a través de 9.0 y PG es de tan sólo 9.1.
    • Yo no lanzar una actualización para el libro de PostgreSQL 9.1, la versión sólo desde su publicación, porque no había suficientes de rendimiento relacionados con los cambios en 9.1 que la justifique.
    • Mientras estoy totalmente de acuerdo en la versión más reciente siempre la mejor, también pueden contener los peores(algún nuevo tema\bug). Es realmente sano para intentar mantenerme paso de bloqueo con el Postgres ciclo de lanzamiento? Para asegurarse de que no en la producción. Honestamente estoy solo por curiosidad, de su entrada en esta tratando de no meter un palo en el león.
    • No, no lo es, pero me parece prudente que se mantenga dentro de una versión mayor o dos. Estoy realmente apuntando a esas personas todavía en 8.3 o mayores, que es simplemente absurdo. 8.4 es marginal, me gustaría estar en 9.1, en el momento y el uso de 9.2 en mis pruebas en preparación para rodar hacia fuera en los próximos seis meses o así. Usted ahorra mucho tiempo utilizando las versiones más recientes debido a problemas ya se han solucionado en la nueva versión, el testimonio de la vismap y fsm, autovac mejoras, etc etc.
    • También, después de un par de meses después de la liberación de los temas en general son en gran parte resuelto en la nueva versión principal. Hay algunas señales de que podría ser una pareja de guarros planificador de regresiones en 9.2, pero el problema es que si nadie lo prueba en serio con sus aplicaciones, a continuación, los problemas no se encuentran. No voy a ir directamente a una nueva especialización en la producción, pero voy a empezar a probar muy pronto.
    • Gran valoración crítica. Sólo como una pequeña actualización, «puede Que sea necesario aumentar el OS es el máximo de memoria compartida límite si aumenta shared_buffers» no es cierto (para la mayoría de los usuarios) bajo PostgreSQL 9.3: postgresql.org/docs/9.3/static/release-9-3.html#AEN114343
    • unfortunetely commit_delay sólo permiten un máximo de 100000 (100ms), por lo que no es útil…
    • Por qué no es útil? Es suficiente para grupo de hasta concurrente conjuntos de cometa, y eso es lo que es.
    • es útil en la producción, pero no para las pruebas
    • Mis pruebas, a menudo, hacer muchas tx en alta TPS … porque me tratan de simular la producción, incluyendo la simultaneidad de cargas de trabajo. Si te refieres a «una sola conexión, lineal de prueba», entonces yo estaría de acuerdo con usted.
    • sí, es la realización de pruebas automatizadas en las tareas de rake (rieles)
    • stackoverflow.com/questions/11419536/… BORRAR puede ser más rápido que TRUNCAR las tablas con pocas filas, que es probable que sea el caso en las pruebas.
    • De hecho, ELIMINAR fue por tanto más rápido que TRUNCAR para nuestro caso
    • Es fsync=off seguro contra inmundo postgres se reinicia (como opuesto a OS se reinicia)?
    • En debe ser, pero me gustaría evitar tener que depender de él para cualquier cosa realmente importante, sólo para estar seguro.
    • Ver también esto: tekadempiere.blogspot.ae/2014/09/…
    • Tal vez puede que me ayude con esta pregunta así: stackoverflow.com/questions/44318816/…
    • «Para el uso de producción se pueden utilizar synchronous_commit=off (apagado) y un commit_delay» <– commit_delay no hacer nada cuando la sincronización cometer está apagado. En caso de que «y» ser «o»? 😉
    • Er… sí. Que.

  2. 9

    Uso diferentes de diseño de disco:

    • disco diferente para $PGDATA
    • disco diferente para $PGDATA/pg_xlog
    • disco diferente para tem archivos (por la base de datos $PGDATA/base//pgsql_tmp) (véase la nota sobre work_mem)

    postgresql.conf ajustes:

    • shared_memory: 30% de la memoria RAM disponible, pero no más de 6 a 8 GB. Parece ser mejor tener menos memoria compartida (2GB – 4GB) para escribir cargas de trabajo intensivas
    • work_mem: la mayoría de las consultas de selección con ordenaciones o agregaciones. Esto es por la configuración de la conexión y la consulta puede asignar ese valor varias veces. Si los datos no caben luego de disco se utiliza (pgsql_tmp). Marque «explicar analizar» a ver ¿cuánta memoria se necesita
    • fsync y synchronous_commit: valores por Defecto son seguras, pero Si usted puede tolerar la pérdida de datos, entonces usted puede convertirse luego se apaga
    • tener bajo este valor le: si usted tiene o SSD rápido matriz RAID puede reducir esto a 2.0 (RAID) o incluso inferior (1.1) para SSD
    • checkpoint_segments: usted puede ir a más de 32 o de 64 y cambio checkpoint_completion_target a 0.9. Valor inferior permite mayor rapidez después de la recuperación de un fallo
    • Tenga en cuenta que si ya se está ejecutando con fsync=off, poniendo pg_xlog en un disco independiente no mejora mucho más.
    • El valor de 1.1 para SSD parece muy calificados. Reconozco que es lo que algunos profesionales han ciegamente recomendado. Incluso los Ssd son significativamente más rápido para lecturas secuenciales de lecturas aleatorias.
    • Sí, pero también tienes OS búfer de caché de efectos en el trabajo. Todos los parámetros son un poco la mano-ondulados de todos modos…

Dejar respuesta

Please enter your comment!
Please enter your name here