Cómo acelerar el rendimiento de inserción en PostgreSQL

Consulte poblar una base de datos en el manual de PostgreSQL, el excelente artículo de Depesz sobre el tema, como siempre, y esta pregunta SO .

(Tenga en cuenta que esta respuesta trata sobre la carga masiva de datos en una base de datos existente o para crear una nueva. Si está interesado en restaurar el rendimiento de la base de datos pg_restoreo en psqlla ejecución de pg_dumpla salida, gran parte de esto no se aplica desde entonces pg_dumpy pg_restoreya hace cosas como crear desencadenadores e índices después de finalizar una restauración de esquema+datos) .

Hay mucho por hacer. La solución ideal sería importar a una UNLOGGEDtabla sin índices, luego cambiarla a registrada y agregar los índices. Desafortunadamente, en PostgreSQL 9.4 no hay soporte para cambiar tablas UNLOGGEDa registradas. 9.5 agrega ALTER TABLE ... SET LOGGEDpara permitirle hacer esto.

Si puede desconectar su base de datos para la importación masiva, utilice pg_bulkload.

De lo contrario:

• Deshabilite cualquier desencadenante en la mesa

• Elimine los índices antes de iniciar la importación y vuelva a crearlos después. (Se necesita mucho menos tiempo para crear un índice de una sola vez que para agregarle los mismos datos progresivamente, y el índice resultante es mucho más compacto).

• Si realiza la importación dentro de una sola transacción, es seguro eliminar las restricciones de clave externa, realizar la importación y volver a crear las restricciones antes de confirmar. No hagas esto si la importación se divide en varias transacciones, ya que podrías introducir datos no válidos.

• Si es posible, utilice COPYen lugar de INSERTs

• Si no puede usarlo, COPYconsidere usar s de valores múltiples INSERTsi es práctico. Parece que ya estás haciendo esto. Sin embargo , no intente enumerar demasiados valores en uno solo VALUES; esos valores tienen que caber en la memoria un par de veces, así que manténgalos en unos pocos cientos por declaración.

• Combine sus inserciones en transacciones explícitas, realizando cientos de miles o millones de inserciones por transacción. No existe un límite práctico, AFAIK, pero el procesamiento por lotes le permitirá recuperarse de un error marcando el inicio de cada lote en sus datos de entrada. Nuevamente, parece que ya estás haciendo esto.

• Utilice synchronous_commit=offy enorme commit_delaypara reducir los costos de fsync(). Sin embargo, esto no ayudará mucho si ha agrupado su trabajo en grandes transacciones.

• INSERTo COPYen paralelo desde varias conexiones. La cantidad depende del subsistema de disco de su hardware; Como regla general, desea una conexión por disco duro físico si utiliza almacenamiento conectado directamente.

• Establezca un max_wal_sizevalor alto ( checkpoint_segmentsen versiones anteriores) y habilítelo log_checkpoints. Mire los registros de PostgreSQL y asegúrese de que no se quejen de que los puntos de control ocurren con demasiada frecuencia.

• Si y solo si no le importa perder todo su clúster PostgreSQL (su base de datos y cualquier otra en el mismo clúster) debido a una corrupción catastrófica si el sistema falla durante la importación, puede detener Pg, configurar fsync=off, iniciar Pg, realizar su importación, luego (vitalmente) detenga Pg y fsync=onvuelva a configurar. Ver configuración WAL . No haga esto si ya hay datos que le interesan en alguna base de datos de su instalación de PostgreSQL. Si configura, fsync=offtambién puede configurar full_page_writes=off; Nuevamente, recuerde volver a activarlo después de la importación para evitar la corrupción y la pérdida de datos en la base de datos. Ver configuraciones no duraderas en el manual de la página.

También debería considerar ajustar su sistema:

• Utilice SSD de buena calidad para el almacenamiento tanto como sea posible. Los buenos SSD con cachés de reescritura confiables y con protección energética hacen que las tasas de confirmación sean increíblemente rápidas. Son menos beneficiosos cuando sigues los consejos anteriores, que reducen los vaciados de disco/número de fsync()s, pero aún así pueden ser de gran ayuda. No utilice SSD baratos sin la protección adecuada contra fallas de energía a menos que no le importe conservar sus datos.

• Si está utilizando RAID 5 o RAID 6 para almacenamiento adjunto directo, deténgase ahora. Haga una copia de seguridad de sus datos, reestructure su matriz RAID a RAID 10 y vuelva a intentarlo. Los RAID 5/6 no tienen remedio para el rendimiento de escritura masiva, aunque un buen controlador RAID con una gran caché puede ayudar.

• Si tiene la opción de utilizar un controlador RAID de hardware con una gran caché de reescritura respaldada por batería, esto realmente puede mejorar el rendimiento de escritura para cargas de trabajo con muchas confirmaciones. No ayuda mucho si usas una confirmación asíncrona con commit_delay o si realizas menos transacciones grandes durante la carga masiva.

• Si es posible, almacene WAL ( pg_walo pg_xlogen versiones anteriores) en un disco/matriz de discos independiente. No tiene mucho sentido utilizar un sistema de archivos independiente en el mismo disco. La gente suele optar por utilizar un par RAID1 para WAL. Nuevamente, esto tiene más efecto en sistemas con altas tasas de confirmación y tiene poco efecto si utiliza una tabla no registrada como destino de carga de datos.

También te puede interesar Optimizar PostgreSQL para realizar pruebas rápidas .

Aug 30 '2012 23:08 Craig Ringer