Comment nous avons résolu le problème de mémoire dans PostgreSQL sans ajouter d'octet

Une courte histoire sur la demande "lourde" et une solution élégante au problème

Récemment, la nuit, des alertes ont commencé à nous réveiller: il n'y avait pas assez d'espace disque. Nous avons rapidement compris que le problème venait des tâches ETL.

La tâche ETL a été effectuée dans une table où les enregistrements binaires et les vidages sont stockés. Chaque nuit, cette tâche consistait à supprimer les vidages en double et à libérer de l'espace.

Pour rechercher des vidages en double, nous avons utilisé cette requête:

id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps 

La requête combine les mêmes vidages par le champ BLOB. En utilisant la fonction de fenêtre, nous obtenons l'identifiant de la première apparition de chaque vidage. Ensuite, avec cette demande, nous supprimons tous les vidages en double.

La demande a été exécutée pendant un certain temps et, comme le montrent les journaux, a consommé beaucoup de mémoire. Le graphique montre comment il a marqué l'espace disque libre chaque nuit:

Au fil du temps, la demande a nécessité plus de mémoire, les échecs se sont aggravés. Et, jetant un coup d'œil au plan d'exécution, nous avons immédiatement vu où tout allait:

  Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms 

Le tri prend beaucoup de mémoire. En termes d'exécution, le tri nécessite environ 30 Mo de mémoire à partir d'un ensemble de données de test.

Pourquoi

PostgreSQL alloue de la mémoire pour le hachage et le tri. La quantité de mémoire est contrôlée par le paramètre work_mem . La taille par défaut de work_mem est de 4 Mo. Si plus de 4 Mo sont nécessaires pour le hachage ou le tri, PostgreSQL consomme temporairement de l'espace disque.

Notre requête consomme clairement plus de 4 Mo, la base de données utilise donc beaucoup de mémoire. Nous avons décidé: nous ne nous précipiterons pas et n'avons pas augmenté le paramètre ni étendu le stockage. Il est préférable de rechercher une autre façon de réduire la mémoire pour le tri .

Tri économique

"La quantité de tri consommée dépend de la taille de l'ensemble de données et de la clé de tri. Vous ne pouvez pas réduire l'ensemble de données, mais la taille de la clé est possible .

Pour le point de référence, nous prenons la taille moyenne de la clé de tri:

  avg ---------- 780 

Chaque clé pèse 780. Pour réduire la clé binaire, elle peut être hachée. Dans PostgreSQL, il y a md5 pour cela (oui, pas de sécurité, mais pour notre objectif, cela fera l'affaire). Voyons combien pèse le BLOB avec md5:

  avg ----------- 36 

La taille de la clé hachée via md5 est de 36 octets. Une clé hachée ne pèse que 4% de l'option d'origine .

Ensuite, nous avons lancé la demande d'origine avec une clé hachée:

  id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps; 

Et le plan de mise en œuvre:

  Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms 

Avec une clé hachée, la demande ne consomme que 4 mégaoctets supplémentaires, soit un peu plus de 10% des 30 Mo précédents. Ainsi, la taille de la clé de tri affecte considérablement la quantité de mémoire consommée par le tri .

Plus plus

Dans cet exemple, nous avons haché le BLOB à l'aide de md5 . Les hachages créés avec MD5 doivent peser 16 octets. Et nous en avons plus:

 md5_size ------------- 32 

Notre hachage était exactement deux fois plus grand, car md5 produit un hachage sous forme de texte hexadécimal.

Dans PostgreSQL, vous pouvez utiliser MD5 pour le hachage avec l'extension pgcrypto . pgcrypto crée MD5 de type bytea (en binaire) :

 select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20 

Le hachage fait toujours 4 octets de plus qu'il ne devrait l'être. C'est juste que le type bytea utilise ces 4 octets pour stocker la longueur de la valeur, mais nous ne la laisserons pas comme ça.

Il s'avère que le type uuid dans PostgreSQL pèse exactement 16 octets et prend en charge toute valeur arbitraire, donc nous nous débarrassons des quatre octets restants:

 uuid_size --------------- 16 

C’est tout. 32 octets avec md5 transforment en 16 avec uuid .

J'ai vérifié les effets du changement en prenant un ensemble de données plus grand. Les données elles-mêmes ne peuvent pas être affichées, mais je partagerai les résultats:

Comme vous pouvez le voir sur le tableau, la demande problématique d'origine pesait 300 Mo (et nous a réveillés au milieu de la nuit). Avec la clé uuid , le tri ne prenait que 7 Mo.

Considérations de suivi

Une requête avec une clé de tri de mémoire hachée consomme moins, mais elle fonctionne beaucoup plus lentement:

Le hachage utilise plus de CPU, donc une demande avec un hachage est plus lente. Mais nous avons essayé de résoudre le problème d'espace disque, de plus, la tâche est effectuée la nuit, donc le temps n'est pas un problème. Nous avons compromis pour économiser de la mémoire.

Ceci est un excellent exemple du fait que vous n'avez pas toujours besoin d'accélérer les requêtes de base de données . Il est préférable d'utiliser ce qui est équilibré et de tirer le maximum d'un minimum de ressources.