Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
J'ai un problème avec le autovacuum dans ma base de données. J'ai quelque tables qui modifient 2 o 3 rows chaque minute, mais le auovacuum avec les paramètres standard ne marchait pas, et j'ai essayé de diminuer les threshold, mais j'ai eu aucun résultat. Je suis forcé a faire un Vacuum Full tous les nuits pour nettoyer la base de données, parce que 20 MB se transforment en 3 GB dans un jour. J'utilise PhpPgAdmin sur l'interface web, je vous laisse ici les paramètres des autovacuums des tables problematiques (c'est tout standard sauf le threshold):
Schema Table Enabled Vacuum Base Threshold Vacuum Scale Factor Analyze Base Threshold Analyze Scale Factor Vacuum Cost Delay Vacuum Cost Limit
public listaTFT_datoscompetitivo on 1 0.2 50 0.1 2ms -1
public listaTFT_jugadortft on 1 0.2 50 0.1 2ms -1
public listaTFT_studydatajugadorestft on 1 0.2 50 0.1 2ms -1
Si quelqu'un sait quel peut être le problème, il m'aiderai beaucoup. La version de PostgreSQL est 12.16
Hors ligne
Bonjour,
Les 3GB - 20 MB viennent-ils vraiment des modifications de quelques lignes chaque minute sur 3 tables ? Si oui cela voudrait dire une taille de plusieurs KB par enregistrement.
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour,
L'autovacuum et le vacuum full sont 2 opérations différentes.
La vacuum full va réorganiser tous les blocs des tables, rendant l'espace disque, un peu comme si vous faisiez un export/import. Ce qui explique que la table passe de 3GB à 20MB.
L'autovacuum ne fait pas ça, à la place il va "flaguer" les lignes mortes pour qu'elles puissent être réutilisées par de nouvelles lignes. Donc dans ce cas la table ne devient pas plus petite, l'espace n'est pas rendu. Au mieux la table ne grossit pas ou moins vite que sans l'autovacuum.
Vous pouvez checker les actions autovacuum et autoanalyze dans cette vue : pg_stat_all_tables.
Cordialement,
Sébastien.
Hors ligne
Dans le cas où (auto)vacuum n'est pas effectué assez régulièrement la meilleure solution pour récupérer de l'espace est effectivement bien souvent vacuum full.
Un vacuum effectué de manière régulière permet bien de résoudre le problème, dans le sens où le bloat reste fixe plutôt que croitre continuellement, et la table à donc une volumétrie fixe (sauf évidemment s'il y a plus d'insertion que de suppression) ce qui semble être le problème ici.
Julien.
https://rjuju.github.io/
Hors ligne
Dans le cas où (auto)vacuum n'est pas effectué assez régulièrement la meilleure solution pour récupérer de l'espace est effectivement bien souvent vacuum full.
Un vacuum effectué de manière régulière permet bien de résoudre le problème, dans le sens où le bloat reste fixe plutôt que croitre continuellement, et la table à donc une volumétrie fixe (sauf évidemment s'il y a plus d'insertion que de suppression) ce qui semble être le problème ici.
Bonjour Rjuju, merci pour ta réponse.
Oui, en fait la majorité des operations sont des updates des lignes qui existent déjà (c'est en fait des joueurs sur une liste, et sa position change tout le temps).
Mai bon, je n'ai pas spécifie que pour les tables "listaTFT_datoscompetitivo" et "listaTFT_studydatajugadorestft" les données qui changent sont sauvegardés en format JSON. Je sais que ce n'est pas très bien de faire ça, mais je reçois des longues listes en format JSON et je ne savais pas une autre manière de les sauvegarder. Peut-être c'est la raison que j'ai plusieurs KB de enregistrement.
parce
De toute façon, la majorité d'operations c'est des updates, donc je en comprend pas pourquoi les tables grandissent comme ça. J'ai peur de faire plus vacuum full, parce que j'ai lu que ça "bloque" les opérations, donc je le fait seulement une fois chaque nuit, et aussi je pense que ce n'est pas la bonne solution.
Hors ligne
Un VACUUM FULL nécessite un verrou exclusif sur la table, et bloque bien toute opération concurrente dessus.
Concernant autovacuum, en supposant qu'il n'est pas désactivé de manière globale, il devrait se déclancher après la modification d'un nombre de ligne égal à 20% du nombre de ligne total dans la table (le autovacuum_vacuum_scale_factor à 0.2) + 1 (le autovacuum_vacuum_threshold). Vous êtes le seul à avoir accès à vos données et à la charge en modification, à vous de modifier ces paramètres pour faire en sorte qu'autovacuum se déclenche à une fréquence suffisante pour éviter que la fragmentation n'explose (sans faire en sorte qu'il se déclenche après chaque modification, avoir un peu de bloat est normal et peut même être utile car cela permet de bénéficier des mise à jour HOT).
Julien.
https://rjuju.github.io/
Hors ligne
Un VACUUM FULL nécessite un verrou exclusif sur la table, et bloque bien toute opération concurrente dessus.
Concernant autovacuum, en supposant qu'il n'est pas désactivé de manière globale, il devrait se déclancher après la modification d'un nombre de ligne égal à 20% du nombre de ligne total dans la table (le autovacuum_vacuum_scale_factor à 0.2) + 1 (le autovacuum_vacuum_threshold). Vous êtes le seul à avoir accès à vos données et à la charge en modification, à vous de modifier ces paramètres pour faire en sorte qu'autovacuum se déclenche à une fréquence suffisante pour éviter que la fragmentation n'explose (sans faire en sorte qu'il se déclenche après chaque modification, avoir un peu de bloat est normal et peut même être utile car cela permet de bénéficier des mise à jour HOT).
Merci beaucoup, je pense que je n'ai pas bien modifié la valeur du scale factor (j'ai jamais mis moins de 0.1 et je crois que c'est trop grand pour mes tables) et ça peux m'aider. Comme dernière question, dans la table "listaTFT_datoscompetitivo" il y a seulement 3 de 100 lignes qui se modifient chaque minute, mais le reste ne change pas. Pourtant si je fait un Vacuum Full de cette table, je récupere 200-400 MB d'espace. C'est un comportement normal? Peut_être je dois faire une autovacuum toutes les minutes là parce que c'est problématique?
Hors ligne
Au bout de combien de temps la table grossit-elle de 200 or 400MB, j'imagine que ce n'est pas en 1 minute ? La volumétrie est-elle dans la table elle même ou la table TOAST associée ?
Julien.
https://rjuju.github.io/
Hors ligne
Au bout de combien de temps la table grossit-elle de 200 or 400MB, j'imagine que ce n'est pas en 1 minute ? La volumétrie est-elle dans la table elle même ou la table TOAST associée ?
C'est pas immédiat, elle grossit en 2-3 heures comme ça. Et la majorité de la volumétrie est dans la table TOAST.
Hors ligne
Faites le calcul de combien d'espace vous pouvez vous permettre pour la fragmentation, et configurez l'autovacuum sur la table pour se déclencher après le nombre moyen et/ou ratio moyen de mise à jour faites pour atteindre cette volumétrie.
Vous ne pourrez jamais avoir une absence totale de fragmentation, et essayer d'avoir l'autovacuum qui se déclenche en permanence sur ces tables gacherait vos ressources pour un gain assez faible en espace disque.
Julien.
https://rjuju.github.io/
Hors ligne
Faites le calcul de combien d'espace vous pouvez vous permettre pour la fragmentation, et configurez l'autovacuum sur la table pour se déclencher après le nombre moyen et/ou ratio moyen de mise à jour faites pour atteindre cette volumétrie.
Vous ne pourrez jamais avoir une absence totale de fragmentation, et essayer d'avoir l'autovacuum qui se déclenche en permanence sur ces tables gacherait vos ressources pour un gain assez faible en espace disque.
Bonsoir Julien,
Merci pour toute les messages. J'ai été capable de reduire les update inutiles et configurer les autovacuum correctement. Après tout, la table "listaTFT_datoscompetitivo" et ses trois lignes si que mettent a jour chaque minute c'est mon seul problème. Je ne sait pas pourquoi mais elles grandissent 1.2 MB chaque update, et je ne sais pas si c'est un problème avec la table TOAST ou quoi. De toute façon c'est plus géable, donc je peut essayer de le résoudre tranquilement. Encore une fois, merci beaucoup!
Hors ligne
Une ligne peut être de grande taille, sachant que la limite théorique d'une colonne de type text/json/jsonb/bytea etc.. est de 1 Go.
L'explication la plus simple au fait que la table croisse d'1,2 Mo par update est simplement que la ligne changée pèse 1,2 Mo.
On peut utiliser la fonction
SELECT pg_column_size(nom_colonne) FROM table WHERE ...pour voir les tailles réellement stockées par colonne (ça prend en compte la compression)
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Une ligne peut être de grande taille, sachant que la limite théorique d'une colonne de type text/json/jsonb/bytea etc.. est de 1 Go.
L'explication la plus simple au fait que la table croisse d'1,2 Mo par update est simplement que la ligne changée pèse 1,2 Mo.
On peut utiliser la fonction
SELECT pg_column_size(nom_colonne) FROM table WHERE ...pour voir les tailles réellement stockées par colonne (ça prend en compte la compression)
Bonjour Dverite,
En faisant ça j'ai vu qu'il y a une ligne problemátique, qui pese plus 1,1 MB, les autre deux lignes sont petites. Il existe une manière gèrer l'espace d'une seule ligne? Sachant que je ne peux pas modifier le JSON (peut-être je peux le comprimer)?
Hors ligne
Le contenu de la colonne JSON de grande taille est déjà compressé. La taille décompressée s'obtiendrait avec cette requête:
SELECT octet_length(nom_colonne::text) FROM table WHERE ...Vous pouvez exporter juste cette ligne/colonne dans un fichier texte pour la regarder en détail dans un éditeur, via ce genre de commande, sous psql:
\pset format unaligned
\pset tuples_only on
select nomdelacolonne FROM nomdelatable WHERE ... \g fichier-de-sortie.json(ne pas terminer la requête par un point-virgule, c'est le \g qui termine la requête)
Dernière modification par dverite (16/11/2023 13:26:59)
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Pages : 1