sentencia - truncate postgresql example

Velocidad de truncamiento de Postgresql (4)

Estamos utilizando Postgresql 9.1.4 como nuestro servidor db. He estado tratando de acelerar mi suite de pruebas, así que he analizado el perfil del DB un poco para ver exactamente qué está pasando. Estamos utilizando database_cleaner para truncar tablas al final de las pruebas. SÍ, sé que las transacciones son más rápidas, no puedo usarlas en ciertas circunstancias, así que no estoy preocupado por eso.

Lo que me preocupa, es por qué TRUNCATION lleva tanto tiempo (más tiempo que el uso de DELETE) y por qué se necesita MUCHO MÁS en mi servidor CI.

En este momento, localmente (en un Macbook Air), un conjunto de prueba completo demora 28 minutos. Hacer un seguimiento de los registros, cada vez que truncamos las tablas ... es decir:

TRUNCATE TABLE table1, table2 -- ... etc

lleva más de 1 segundo realizar el truncamiento. Al seguir los registros en nuestro servidor de CI (Ubuntu 10.04 LTS), la toma tarda 8 segundos completos en truncar las tablas y una compilación demora 84 minutos.

Cuando cambié a la estrategia de :deletion , mi compilación local tomó 20 minutos y el servidor de CI bajó a 44 minutos. Esta es una diferencia significativa y estoy realmente impresionado de por qué esto podría ser. He tuned the datos en el servidor de CI, tiene 16 GB de ram de sistema, 4gb shared_buffers ... y una SSD. Todas las cosas buenas Como es posible:

a. que es mucho más lento que mi Macbook Air con 2 GB de RAM
segundo. ese TRUNCATION es mucho más lento que DELETE cuando el postgresql documenta explícitamente que debería ser mucho más rápido.

¿Alguna idea?

Brad, solo para avisarte. He examinado bastante profundamente una pregunta muy similar.

Pregunta relacionada: 30 tablas con pocas filas: ¿TRUNCATE la forma más rápida de vaciarlas y restablecer las secuencias adjuntas?

Consulte también este problema y esta solicitud de extracción:

https://github.com/bmabey/database_cleaner/issues/126

https://github.com/bmabey/database_cleaner/pull/127

También este hilo: http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php

Lamento haber escrito esto como respuesta, pero no encontré ningún enlace de comentarios, tal vez porque ya hay demasiados comentarios allí.

Esto ha aparecido un par de veces recientemente, tanto en SO como en las listas de correo de PostgreSQL.

El TL; DR para sus dos últimos puntos:

(a) Los shared_buffers más grandes pueden ser la razón por la cual TRUNCATE es más lento en el servidor de CI. Diferentes configuraciones fsync o el uso de medios rotacionales en lugar de SSD también podrían ser la culpa.

(b) TRUNCATE tiene un costo fijo, pero no necesariamente más lento que DELETE , además de que hace más trabajo. Vea la explicación detallada que sigue.

ACTUALIZACIÓN: una discusión significativa sobre el rendimiento de pgsql surgió de esta publicación. Ver este hilo

ACTUALIZACIÓN 2: Se han agregado mejoras a 9.2beta3 que deberían ayudar con esto, consulte esta publicación .

Explicación detallada de TRUNCATE vs DELETE FROM :

Aunque no soy un experto en el tema, tengo entendido que TRUNCATE tiene un costo casi fijo por tabla, mientras que DELETE es al menos O (n) para n filas; peor si hay claves externas que hacen referencia a la tabla que se elimina.

Siempre asumí que el costo fijo de un TRUNCATE era menor que el costo de un DELETE en una tabla casi vacía, pero esto no es cierto en absoluto.

TRUNCATE table; hace más que DELETE FROM table;

El estado de la base de datos después de una TRUNCATE table es muy similar a como si en su lugar se ejecutara:

DELETE FROM table;
VACCUUM (FULL, ANALYZE) table; (9.0+ solamente, ver nota al pie)

... aunque, por supuesto, TRUNCATE realidad no logra sus efectos con un DELETE y un VACUUM .

El punto es que DELETE y TRUNCATE hacen cosas diferentes, por lo que no solo estás comparando dos comandos con resultados idénticos.

Una lista DELETE FROM table; permite que las filas muertas y la saturación permanezcan, permite que los índices lleven entradas muertas, no actualiza las estadísticas de la tabla utilizadas por el planificador de consultas, etc.

Un TRUNCATE le brinda una tabla e índices completamente nuevos como si solo fueran CREATE ed. Es como si eliminara todos los registros, reindexara la tabla e hiciera un VACUUM FULL .

Si no le importa si queda algo en la tabla porque está a punto de ir y volver a llenarlo, puede ser mejor que use la DELETE FROM table; .

Debido a que no está ejecutando VACUUM , encontrará que las filas muertas y las entradas de índice se acumulan como bloat que deben escanearse y luego ignorarse; esto ralentiza todas tus consultas. Si sus pruebas en realidad no crean y eliminan toda esa cantidad de datos, es posible que usted no se dé cuenta o no se preocupe, y si lo hace, siempre puede hacer un VACUUM o dos en medio de la prueba. Mejor, deje que la configuración agresiva del autovacío garantice que autovacuum lo haga en segundo plano.

Todavía puede TRUNCATE todas sus tablas después de que todo el conjunto de pruebas se ejecute para asegurarse de que no se acumulen efectos en muchas ejecuciones. En 9.0 y posteriores, VACUUM (FULL, ANALYZE); globalmente sobre la mesa es al menos tan bueno o mejor, y es mucho más fácil.

IIRC Pg tiene algunas optimizaciones que significan que puede notar cuando su transacción es la única que puede ver la tabla e inmediatamente marcar los bloques como libres de todos modos. En las pruebas, cuando quería crear bloat, tuve que tener más de una conexión simultánea para hacerlo. Sin embargo, no confiaría en esto.

DELETE FROM table; es muy barato para mesas pequeñas sin refs f / k

Para DELETE todos los registros de una tabla sin referencias de clave externa, todo Pg tiene que hacer un escaneo de tabla secuencial y establecer el xmax de las tuplas encontradas. Esta es una operación muy económica, básicamente una lectura lineal y una escritura semilineal. AFAIK no tiene que tocar los índices; continúan apuntando a las tuplas muertas hasta que son limpiadas por un VACUUM posterior que también marca bloques en la tabla que contienen solo tuplas muertas como libres.

DELETE solo se vuelve costoso si hay muchos registros, si hay muchas referencias de claves externas que deben verificarse, o si cuenta la VACUUM (FULL, ANALYZE) table; subsiguiente VACUUM (FULL, ANALYZE) table; necesario para hacer coincidir los efectos de TRUNCATE con el costo de DELETE .

En mis pruebas aquí, una DELETE FROM table; fue típicamente 4 veces más rápido que TRUNCATE a 0.5ms vs 2ms. Es un DB de prueba en una SSD, ejecutándose con fsync=off porque no me importa si pierdo todos estos datos. Por supuesto, DELETE FROM table; no está haciendo el mismo trabajo, y si sigo con una VACUUM (FULL, ANALYZE) table; es un 21ms mucho más caro, por lo que el DELETE es solo una ganancia si realmente no necesito la mesa prístina.

TRUNCATE table; hace mucho más trabajo y limpieza de costo fijo que DELETE

Por el contrario, un TRUNCATE tiene que hacer un montón de trabajo. Debe asignar nuevos archivos para la tabla, su tabla TOAST si lo hay y todos los índices que tiene la tabla. Los encabezados deben escribirse en esos archivos y es posible que los catálogos del sistema también necesiten actualización (no estoy seguro sobre ese punto, no lo he marcado). Luego tiene que reemplazar los archivos antiguos por los nuevos o eliminar los antiguos, y debe asegurarse de que el sistema de archivos haya alcanzado los cambios con una operación de sincronización, fsync () o similar, que generalmente vacía todos los búferes en el disco. . No estoy seguro de si la sincronización se omite si está ejecutando la opción (consumo de datos) fsync=off .

Aprendí recientemente que TRUNCATE también debe eliminar todos los búferes de PostgreSQL relacionados con la tabla anterior. Esto puede tomar una cantidad de tiempo no trivial con enormes shared_buffers . Sospecho que es por eso que es más lento en su servidor de CI.

El balance

De todos modos, puede ver que un TRUNCATE de una tabla que tiene asociada una tabla TOAST (la mayoría lo hace) y varios índices pueden tardar unos minutos. No es largo, pero es más largo que un DELETE de una tabla casi vacía.

En consecuencia, es mejor que hagas una DELETE FROM table; .

Nota: en DB antes de 9.0, CLUSTER table_id_seq ON table; ANALYZE table; CLUSTER table_id_seq ON table; ANALYZE table; o VACUUM FULL ANALYZE table; REINDEX table; VACUUM FULL ANALYZE table; REINDEX table; sería un equivalente más cercano a TRUNCATE . La VACUUM FULL cambió a una mejor en 9.0.

Me he encontrado con un problema similar últimamente, es decir:

El tiempo para ejecutar el conjunto de pruebas que utiliza DatabaseCleaner varió ampliamente entre diferentes sistemas con hardware comparable,
Cambiar la estrategia de DatabaseCleaner a :deletion proporcionada ~ 10x de mejora.

La causa principal de la lentitud fue un sistema de archivos con diario (ext4) utilizado para el almacenamiento de la base de datos. Durante la operación TRUNCATE, el daemon de diario (jbd2) estaba usando ~ 90% de la capacidad de IO del disco. No estoy seguro de si esto es un error, un caso extremo o un comportamiento realmente normal en estas circunstancias. Sin embargo, esto explica por qué TRUNCATE fue mucho más lento que DELETE: generó muchas más grabaciones de disco. Como no quería usar DELETE en realidad, recurrí a configurar fsync=off y fue suficiente para mitigar este problema (la seguridad de los datos no era importante en este caso).

Un par de enfoques alternativos a considerar:

Cree una base de datos vacía con datos de "fixture" estáticos y ejecute las pruebas de esa manera. Cuando haya terminado, simplemente suelte la base de datos, que debe ser rápido.
Cree una nueva tabla llamada "test_ids_to_delete" que contenga columnas para nombres de tabla e identificadores de clave principal. Actualice su lógica de eliminación para insertar los ids / nombres de tabla en esta tabla, lo que será mucho más rápido que ejecutar eliminaciones. Luego, escriba una secuencia de comandos para ejecutar "fuera de línea" para eliminar realmente los datos, ya sea después de que haya finalizado una ejecución de prueba completa o de la noche a la mañana.

El primero es un enfoque de "sala limpia", mientras que el último significa que habrá algunos datos de prueba que permanecerán en la base de datos por más tiempo. El enfoque "sucio" con eliminaciones fuera de línea es lo que estoy usando para un banco de pruebas con aproximadamente 20,000 pruebas. Sí, a veces hay problemas debido a tener datos de prueba "extra" en la base de datos de desarrollo, pero a veces. Pero a veces esta "suciedad" nos ha ayudado a encontrar y corregir errores porque el "desorden" simulaba mejor una situación del mundo real, de una manera que el enfoque de salas limpias nunca lo hará.