Vitesse de troncature postgresql

Nous utilisons Postgresql 9.1.4 comme serveur de firebase database. J’essayais d’accélérer ma suite de tests, alors j’ai regardé un peu le profil de la firebase database pour voir exactement ce qui se passait. Nous utilisons database_cleaner pour tronquer les tables à la fin des tests. OUI Je sais que les transactions sont plus rapides, je ne peux pas les utiliser dans certaines circonstances, alors je ne suis pas concerné.

Ce qui m’inquiète, c’est pourquoi TRUNCATION prend autant de temps (plus longtemps qu’avec DELETE) et pourquoi cela prend PLUS LONGEMENT sur mon serveur CI.

En ce moment, localement (sur un Macbook Air), une suite de tests complète prend 28 minutes. Accrocher les journaux, chaque fois que nous tronquons des tableaux …

 TRUNCATE TABLE table1, table2 -- ... etc 

il faut plus d’une seconde pour effectuer la troncature. Mettre les journaux sur notre serveur CI (Ubuntu 10.04 LTS), prend 8 secondes pour tronquer les tables et une construction prend 84 minutes.

Lorsque je suis passé à la stratégie de :deletion , ma version locale a pris 20 minutes et le serveur CI est passé à 44 minutes. C’est une différence significative et je suis vraiment épaté de savoir pourquoi cela pourrait être. J’ai mis au point la firebase database sur le serveur CI, il a un système de 16 Go, 4 Go partagés, et un SSD. Toutes les bonnes choses. Comment est-ce possible:

une. que c’est tellement plus lent que mon Macbook Air avec 2 Go de RAM
b. que TRUNCATION est beaucoup plus lent que DELETE lorsque les documents postgresql indiquent explicitement qu’il devrait être beaucoup plus rapide.

Des pensées?

    Cela a été évoqué à quelques resockets récemment, à la fois sur SO et sur les listes de diffusion PostgreSQL.

    Le TL; DR pour vos deux derniers points:

    (a) Le plus grand shared_buffers peut expliquer pourquoi TRUNCATE est plus lent sur le serveur CI. Une configuration différente de fsync ou l’utilisation de médias rotatifs au lieu de SSD pourraient également être en cause.

    (b) TRUNCATE a un coût fixe, mais pas nécessairement plus lent que DELETE , en plus il fait plus de travail. Voir l’explication détaillée qui suit.

    MISE À JOUR: Une discussion importante sur les performances de pgsql est née de cet article. Voir ce fil

    MISE À JOUR 2: Des améliorations ont été apscopes à la version 9.2beta3, ce qui devrait vous aider. Voir cet article .

    Explication détaillée de TRUNCATE vs DELETE FROM :

    Bien que n’étant pas un expert sur le sujet, je TRUNCATE que TRUNCATE a un coût presque fixe par table, alors que DELETE est au moins O (n) pour n lignes; pire si des clés étrangères référençant la table sont supprimées.

    J’ai toujours supposé que le coût fixe d’un TRUNCATE était inférieur au coût d’un DELETE sur une table presque vide, mais ce n’est pas du tout le cas.

    TRUNCATE table; fait plus que DELETE FROM table;

    L’état de la firebase database après une TRUNCATE table est sensiblement le même que si vous exécutiez à la place:

    • DELETE FROM table;
    • VACCUUM (FULL, ANALYZE) table; (9.0+ seulement, voir note de bas de page)

    … bien que TRUNCATE n’atteigne pas ses effets avec DELETE et VACUUM .

    Le fait est que DELETE et TRUNCATE font des choses différentes, donc vous ne comparez pas seulement deux commandes avec des résultats identiques.

    Une DELETE FROM table; permet aux lignes mortes et aux balles de restr, permet aux index de transporter des entrées mortes, ne met pas à jour les statistiques de table utilisées par le planificateur de requêtes, etc.

    Un TRUNCATE vous donne une table complètement nouvelle et des index comme s’ils étaient simplement CREATE ed. C’est comme si vous supprimiez tous les enregistrements, réindexiez la table et faites un VACUUM FULL .

    Si vous ne vous souciez pas de savoir s’il rest du crud dans la table parce que vous êtes sur le sharepoint le remplir à nouveau, vous pouvez mieux utiliser la DELETE FROM table; .

    Étant donné que vous VACUUM pas VACUUM vous constaterez que les lignes mortes et les entrées d’index s’accumulent sous forme de bourrage qui doit être analysé puis ignoré. cela ralentit toutes vos requêtes. Si vos tests ne créent pas et ne suppriment pas vraiment toutes ces données, vous ne les remarquerez peut-être pas et vous pouvez toujours faire un VACUUM ou deux pendant votre test si vous le faites. Mieux, laissez les parameters d’autovacuum agressifs vous assurer que autovacuum le fait pour vous en arrière-plan.

    Vous pouvez toujours TRUNCATE toutes vos tables après l’exécution de toute la suite de tests pour vous assurer qu’aucun effet n’est créé sur plusieurs exécutions. Sur 9.0 et versions VACUUM (FULL, ANALYZE); , VACUUM (FULL, ANALYZE); globalement sur la table est au moins aussi bien sinon mieux, et c’est beaucoup plus facile.

    IIRC Pg a quelques optimisations qui signifient qu’il pourrait remarquer que votre transaction est la seule à voir la table et marquer immédiatement les blocs comme étant libres. Lors des tests, lorsque j’ai voulu créer un bloat, j’ai dû avoir plusieurs connexions simultanées pour le faire. Je ne compte pas sur ça, cependant.

    DELETE FROM table; est très bon marché pour les petites tables sans ref / f

    Pour DELETE tous les enregistrements d’une table sans références de clé étrangère, tout Pg doit effectuer un balayage de table séquentiel et définir le xmax des tuples rencontrés. C’est une opération très peu coûteuse – essentiellement une lecture linéaire et une écriture semi-linéaire. AFAIK il ne doit pas toucher les index; ils continuent à pointer vers les tuples morts jusqu’à ce qu’ils soient nettoyés par un VACUUM ultérieur qui marque également les blocs de la table ne contenant que des tuples morts comme étant libres.

    DELETE ne coûte que s’il y a beaucoup d’enregistrements, s’il y a beaucoup de références de clés étrangères à vérifier ou si vous comptez la VACUUM (FULL, ANALYZE) table; nécessaire pour faire correspondre les effets de TRUNCATE avec le coût de votre DELETE .

    Dans mes tests ici, une DELETE FROM table; était typiquement 4x plus rapide que TRUNCATE à 0.5ms vs 2ms. C’est un test de firebase database sur un SSD, exécuté avec fsync=off car je ne me soucie pas de perdre toutes ces données. Bien sûr, DELETE FROM table; ne fait pas tout le même travail, et si je suis suivi par une VACUUM (FULL, ANALYZE) table; c’est un 21ms beaucoup plus cher, donc le DELETE n’est qu’une victoire si je n’ai pas besoin de la table vierge.

    TRUNCATE table; fait beaucoup plus de travail à forfait et d’entretien que DELETE

    En revanche, un TRUNCATE doit faire beaucoup de travail. Il doit allouer de nouveaux fichiers pour la table, sa table TOAST, le cas échéant, et tous les index de la table. Les en-têtes doivent être écrits dans ces fichiers et les catalogues système peuvent également nécessiter une mise à jour (pas sûr à ce stade, pas coché). Il doit ensuite remplacer les anciens fichiers par les nouveaux ou supprimer les anciens, et veiller à ce que le système de fichiers ait rattrapé les modifications avec une opération de synchronisation – fsync () ou similaire – qui vide généralement tous les tampons sur le disque. . Je ne sais pas si la synchronisation est ignorée si vous utilisez l’option (mangeant des données) fsync=off .

    J’ai appris récemment que TRUNCATE doit également vider tous les tampons de PostgreSQL liés à l’ancienne table. Cela peut prendre une quantité de temps non sortingviale avec d’énormes shared_buffers . Je soupçonne que c’est pourquoi il est plus lent sur votre serveur CI.

    L’équilibre

    Quoi qu’il en soit, vous pouvez voir qu’un TRUNCATE d’une table qui a une table TOAST associée (la plupart d’entre eux) et plusieurs index peut prendre quelques instants. Pas longtemps, mais plus longtemps qu’un DELETE partir d’une table presque vide.

    Par conséquent, vous feriez peut-être mieux de faire une DELETE FROM table; .

    Remarque: sur les bases de données antérieures à 9.0, la CLUSTER table_id_seq ON table; ANALYZE table; CLUSTER table_id_seq ON table; ANALYZE table; ou VACUUM FULL ANALYZE table; REINDEX table; VACUUM FULL ANALYZE table; REINDEX table; serait un équivalent plus proche de TRUNCATE . Le sous-système VACUUM FULL changé pour devenir beaucoup plus efficace dans 9.0.

    Brad, juste pour te le faire savoir. J’ai examiné assez profondément une question très similaire.

    Question connexe: 30 tableaux avec quelques lignes – TRUNCATE le moyen le plus rapide de les vider et de réinitialiser les séquences attachées?

    S’il vous plaît regardez également ce problème et cette requête pull:

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

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

    Aussi ce fil: http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php

    Je suis désolé d’avoir écrit ceci en tant que réponse, mais je n’ai trouvé aucun commentaire, peut-être parce qu’il ya déjà trop de commentaires.

    Quelques approches alternatives à considérer:

    • Créez une firebase database vide avec des données “fixture” statiques, et exécutez les tests avec cela. Lorsque vous avez terminé, déposez simplement la firebase database, qui devrait être rapide.
    • Créez une nouvelle table appelée “test_ids_to_delete” qui contient des colonnes pour les noms de table et les identifiants de clé primaire. Mettez à jour votre logique de suppression pour insérer les noms d’id / table dans cette table, ce qui sera beaucoup plus rapide que d’exécuter des suppressions. Ensuite, écrivez un script pour exécuter «hors ligne» afin de supprimer les données, soit à la fin d’un test complet, soit du jour au lendemain.

    La première est une approche de type «salle blanche», tandis que la dernière signifie que certaines données de test persisteront plus longtemps dans la firebase database. L’approche “sale” avec les suppressions hors ligne est ce que j’utilise pour une suite de tests avec environ 20 000 tests. Oui, il y a parfois des problèmes dus à des données de test “supplémentaires” dans la firebase database de développement, mais parfois. Mais parfois, cette “saleté” nous a aidés à trouver et à corriger un bug car le “désordre” simulait mieux une situation réelle, de telle manière que l’approche de la salle blanche ne le fera jamais.

    J’ai rencontré un problème similaire récemment, à savoir:

    1. Le temps nécessaire pour exécuter une suite de tests utilisant DatabaseCleaner variait considérablement entre les différents systèmes avec un matériel comparable,
    2. Changer la stratégie de DatabaseCleaner pour :deletion fournie ~ 10x améliorations.

    La cause principale de la lenteur était un système de fichiers avec journalisation (ext4) utilisé pour le stockage de la firebase database. Pendant l’opération TRUNCATE, le démon de journalisation (jbd2) utilisait environ 90% de la capacité d’E / S du disque. Je ne suis pas sûr s’il s’agit d’un bug, d’un cas limite ou d’un comportement réellement normal dans ces circonstances. Cela explique cependant pourquoi TRUNCATE était beaucoup plus lent que DELETE – il générait beaucoup plus d’écritures sur disque. Comme je ne voulais pas utiliser DELETE, j’ai fsync=off et cela suffisait pour atténuer ce problème (la sécurité des données n’était pas importante dans ce cas).