Pour vous rappeler, nous avons commencé par des problèmes liés à l' isolement , fait une digression sur la structure des données de bas niveau , discuté en détail des versions de ligne et observé comment les instantanés de données sont obtenus à partir des versions de ligne.

Ensuite, nous avons exploré le vide sur la page (et les mises à jour HOT) et le vide . Nous allons maintenant examiner l'autovacuum.

Autovacuum

Nous avons déjà mentionné que normalement (c.-à-d., Lorsque rien ne tient l'horizon des transactions pendant longtemps), VACUUM fait généralement son travail. Le problème est de savoir à quelle fréquence l'appeler.

Si nous aspirons trop rarement une table à langer, sa taille augmentera plus que souhaité. En outre, une prochaine opération de vide peut nécessiter plusieurs passages dans les index si trop de changements ont été effectués.

Si nous aspirons la table trop souvent, le serveur effectuera constamment de la maintenance plutôt qu'un travail utile - et ce n'est pas bon non plus.

Notez que le lancement de VACUUM dans les délais prévus ne résout en aucun cas le problème car la charge de travail peut changer avec le temps. Si la table commence à changer plus intensément, elle doit être aspirée plus souvent.

Autovacuum est exactement la technique qui nous permet de lancer l'aspirateur en fonction de l'intensité des changements de table.

Lorsque autovacuum est activé (l'ensemble de paramètres de configuration autovacuum ), le processus du démon lanceur autovacuum est démarré, ce qui planifie le travail. L'aspiration est effectuée par des processus de travail à vide automatique , dont plusieurs instances peuvent s'exécuter en parallèle.

Le processus du lanceur à vide automatique compose une liste de bases de données où n'importe quelle activité a lieu. L'activité est déterminée à partir de statistiques et pour la collecter, le paramètre track_counts doit être défini. Ne désactivez jamais autovacuum et track_counts , sinon la fonction autovacuum ne fonctionnera pas.

Une fois toutes les secondes autovacuum_naptime, le lanceur autovacuum démarre (à l'aide du processus postmaster ) un processus de travail pour chaque base de données de la liste. En d'autres termes, s'il y a une activité dans une base de données, les processus de travail lui seront envoyés à un intervalle de secondes autovacuum_naptime . À cette fin, si quelques (N) bases de données actives sont disponibles, les processus de travail sont lancés N fois aussi souvent que toutes les secondes autovacuum_naptime . Mais le nombre total de processus de travail exécutés simultanément est limité par le paramètre autovacuum_max_workers .

Au démarrage, un processus de travail se connecte à la base de données qui lui est affectée et commence par composer une liste de:

• Toutes les tables, vues matérialisées et tables TOAST qui nécessitent une aspiration.
• Toutes les tables et vues matérialisées qui nécessitent une analyse (les tables TOAST ne sont pas analysées car elles sont toujours atteintes avec l'accès à l'index).

Ensuite, le processus de traitement vide et / ou analyse les objets de la liste un par un et se termine lorsque l'aspiration est terminée.

Si le processus n'a pas pu effectuer tout le travail prévu en secondes autovacuum_naptime , le processus de lancement autovacuum enverra un processus de travail supplémentaire à cette base de données et ils travailleront ensemble. «Ensemble» signifie simplement que le deuxième processus établira sa propre liste et y travaillera. Ainsi, seules différentes tables seront traitées en parallèle, mais il n'y a pas de parallélisme au niveau d'une table - si l'un des processus de travail gère déjà une table, un autre processus la sautera et poursuivra.

Maintenant, clarifions plus en détail ce que l'on entend par «nécessite un aspirateur» et «nécessite une analyse».

Récemment, le correctif a été validé pour permettre au vide de traiter les index en parallèle avec les travailleurs en arrière-plan.

Quelles tables nécessitent un aspirateur?

L'aspiration est considérée comme nécessaire si le nombre de tuples morts (c'est-à-dire obsolètes) dépasse le seuil spécifié. Le collecteur de statistiques garde en permanence le nombre de tuples morts, qui est stocké dans la table pg_stat_all_tables . Et deux paramètres spécifient le seuil:

• autovacuum_vacuum_threshold définit une valeur absolue (le nombre de tuples).
• autovacuum_vacuum_scale_factor définit la part des lignes dans la table.

En résumé: l'aspiration est requise si pg_stat_all_tables.n_dead_tup > = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes .

Avec les paramètres par défaut, autovacuum_vacuum_threshold = 50 et autovacuum_vacuum_scale_factor = 0,2. autovacuum_vacuum_scale_factor est certainement le plus important ici - c'est ce paramètre qui est critique pour les grandes tables (et c'est à eux que les problèmes possibles sont associés). La valeur de 20% semble indûment élevée et, très probablement, elle devra être considérablement réduite.

Les valeurs optimales des paramètres peuvent varier pour différentes tables et dépendent de la taille des tables et des spécificités des modifications. Il est judicieux de définir des valeurs généralement appropriées et, si besoin est, d'effectuer des réglages spéciaux des paramètres au niveau de certaines tables au moyen de paramètres de stockage:

• autovacuum_vacuum_threshold et toast.autovacuum_vacuum_threshold .
• autovacuum_vacuum_scale_factor et toast.autovacuum_vacuum_scale_factor .

Pour éviter toute confusion, il est raisonnable de le faire uniquement pour quelques tableaux qui se distinguent parmi les autres par la quantité et l'intensité des modifications et uniquement lorsque les valeurs définies globalement ne fonctionnent pas correctement.

De plus, vous pouvez désactiver l'autovacuum au niveau de la table (bien que nous ne puissions guère penser à une raison pour laquelle cela pourrait être nécessaire):

• autovacuum_enabled et toast.autovacuum_enabled .

Par exemple, la dernière fois que nous avons créé la table vac avec le vide automatique désactivé afin de contrôler manuellement le vide à des fins de démonstration. Le paramètre de stockage peut être modifié comme suit:

 => ALTER TABLE vac SET (autovacuum_enabled = off); 

Pour formaliser tout ce qui précède, créons une vue qui montre quelles tables doivent être nettoyées à ce stade. Il utilisera la fonction qui renvoie la valeur actuelle du paramètre et tient compte du fait que la valeur peut être redéfinie au niveau de la table:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( -- if the storage parameter is set, we take its value (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE -- for TOAST tables, the parameter name differs WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), -- otherwise, we take the value of the configuration parameter current_setting(param) )::float; $$ LANGUAGE sql; 

Et voici la vue:

 => CREATE VIEW need_vacuum AS SELECT st.schemaname || '.' || st.relname tablename, st.n_dead_tup dead_tup, get_value('autovacuum_vacuum_threshold', c.reloptions, c.relkind) + get_value('autovacuum_vacuum_scale_factor', c.reloptions, c.relkind) * c.reltuples max_dead_tup, st.last_autovacuum FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m','t'); 

Quels tableaux nécessitent une analyse?

La situation avec l'analyse automatique est similaire. Ces tables sont considérées comme nécessitant une analyse dont le nombre de tuples mis à jour (depuis la dernière analyse) dépasse le seuil spécifié par deux paramètres similaires: pg_stat_all_tables.n_mod_since_analyze > = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltupes .

Les paramètres par défaut de l'analyse automatique sont quelque peu différents: autovacuum_analyze_threshold = 50 et autovacuum_analyze_scale_factor = 0,1. Ils peuvent également être définis au niveau des paramètres de stockage de tables distinctes:

• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor

Comme les tables TOAST ne sont pas analysées, elles n'ont pas de tels paramètres.

Créons également une vue pour l'analyse:

 => CREATE VIEW need_analyze AS SELECT st.schemaname || '.' || st.relname tablename, st.n_mod_since_analyze mod_tup, get_value('autovacuum_analyze_threshold', c.reloptions, c.relkind) + get_value('autovacuum_analyze_scale_factor', c.reloptions, c.relkind) * c.reltuples max_mod_tup, st.last_autoanalyze FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m'); 

Exemple

Définissons les valeurs de paramètres suivantes pour les expériences:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; -- to aviod waiting long => ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03; -- 3% => ALTER SYSTEM SET autovacuum_vacuum_threshold = 0; => ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02; -- 2% => ALTER SYSTEM SET autovacuum_analyze_threshold = 0; 

 => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

Créons maintenant un tableau similaire à celui utilisé la dernière fois et y insérons mille lignes. Autovacuum est désactivé au niveau de la table, et nous allons l'activer par nous-mêmes. Sans cela, les exemples ne seront pas reproductibles car la purge automatique peut être déclenchée à un mauvais moment.

 => CREATE TABLE autovac( id serial, s char(100) ) WITH (autovacuum_enabled = off); => INSERT INTO autovac SELECT g.id,'A' FROM generate_series(1,1000) g(id); 

Voici ce que notre vision de l'aspirateur montrera:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 0 | (1 row) 

Il convient ici de prêter attention à deux choses. Tout d'abord, max_dead_tup = 0 bien que 3% des 1000 lignes font 30 lignes. Le fait est que nous n'avons pas encore de statistiques sur la table car INSERT ne la met pas à jour d'elle-même. Jusqu'à ce que la table soit analysée, les zéros resteront puisque pg_class.reltuples = 0. Mais regardons la deuxième vue pour l'analyse:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------ public.autovac | 1000 | 0 | (1 row) 

Étant donné que 1000 lignes ont été modifiées (ajoutées) dans le tableau, ce qui est supérieur à zéro, une analyse automatique doit être déclenchée. Vérifions ceci:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); 

Après une courte pause, nous pouvons voir que le tableau a été analysé et que 20 lignes correctes sont affichées dans max_dead_tup au lieu de zéros:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------------------- public.autovac | 0 | 20 | 2019-05-21 11:59:48.465987+03 (1 row) 

 => SELECT reltuples, relpages FROM pg_class WHERE relname = 'autovac'; 

  reltuples | relpages -----------+---------- 1000 | 17 (1 row) 

Revenons à la purge automatique:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 30 | (1 row) 

Comme nous pouvons le voir, max_dead_tup a déjà été corrigé. Une autre chose à laquelle faire attention est que dead_tup = 0. Les statistiques montrent que la table n'a pas de tuples morts ..., et c'est vrai. Il n'y a encore rien à aspirer dans le tableau. Toute table utilisée exclusivement en mode ajout uniquement ne sera pas mise à vide et, par conséquent, la carte de visibilité ne sera pas mise à jour pour elle. Mais cela rend impossible l'utilisation de l'analyse uniquement par index.

(La prochaine fois, nous verrons que passer l'aspirateur atteindra tôt ou tard une table avec ajout uniquement, mais cela se produira trop rarement.)

Une leçon apprise: si l'analyse par index uniquement est critique, il peut être nécessaire d'appeler manuellement un processus de vide.

Désactivons maintenant le vide automatique et mettons à jour 31 lignes, soit une ligne de plus que le seuil.

 => ALTER TABLE autovac SET (autovacuum_enabled = off); => UPDATE autovac SET s = 'B' WHERE id <= 31; => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 31 | 30 | (1 row) 

La condition de déclenchement sous vide est maintenant remplie. Allumons le vide automatique et après une courte pause, nous verrons que la table a été traitée:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+------------------------------- public.autovac | 0 | 30 | 2019-05-21 11:59:52.554571+03 (1 row) 

Limitation de charge

VACUUM ne bloque pas les autres processus car il fonctionne page par page, mais il produit une charge supplémentaire sur le système et peut affecter de manière significative les performances.

Limitation du vide

Pour pouvoir contrôler l'intensité du vide et donc son effet sur le système, le processus alterne travail et attente. Le processus fera sur les unités de travail conventionnelles de vacuum_cost_limit et ensuite il dormira pendant ms de vacuum_cost_delay .

Les paramètres par défaut sont vacuum_cost_limit = 200 et vacuum_cost_delay = 0. Le dernier zéro signifie en fait que VACUUM ne dort pas, donc une valeur spécifique de vacuum_cost_limit n'a pas d'importance du tout. Le raisonnement derrière cela est que si un administrateur devait lancer manuellement VACUUM, il souhaiterait probablement que l'aspirateur soit effectué le plus rapidement possible.

Néanmoins, si nous définissons le temps de sommeil, la quantité de travail spécifiée dans vacuum_cost_limit sera composée des coûts de travail avec les pages dans le cache de tampon. L'accès à chaque page est estimé comme suit:

• Si la page se trouve dans le cache de tampon, vacuum_cost_page_hit = 1.
• S'il n'a pas été trouvé, vacuum_cost_page_miss = 10.
• S'il n'a pas été trouvé et qu'une page sale a dû en outre être supprimée du cache de tampon, vacuum_cost_page_dirty = 20.

Autrement dit, avec les paramètres par défaut de vacuum_cost_limit , 200 pages de cache ou 20 pages de disque ou 10 pages avec expulsion peuvent être traitées en une seule fois. Il est clair que ces chiffres sont assez provisoires, mais il n'est pas logique de sélectionner des chiffres plus précis.

Limitation de la purge automatique

Pour les processus sous vide, l'étranglement de charge fonctionne de la même manière que pour le VIDE. Mais pour les processus autovacuum et VACUUM lancé manuellement pour fonctionner avec une intensité différente, autovacuum a ses propres paramètres: autovacuum_vacuum_cost_limit et autovacuum_vacuum_cost_delay . Si ces paramètres ont la valeur -1, la valeur de vacuum_cost_limit et / ou vacuum_cost_delay est utilisée.

Par défaut, autovacuum_vacuum_cost_limit = -1 ( c'est-à - dire que la valeur de vacuum_cost_limit = 200 est utilisée) et autovacuum_vacuum_cost_delay = 20 ms. Sur le matériel moderne, le vide automatique sera vraiment lent.

Dans la version 12, la valeur de autovacuum_vacuum_cost_delay est réduite à 2 ms, ce qui peut être pris pour une première approximation plus appropriée.

En outre, nous devons noter que la limite spécifiée par ces paramètres est commune à tous les processus de travail. En d'autres termes, lorsque le nombre de processus de travail simultanés est modifié, la charge globale reste inchangée. Ainsi, pour augmenter les performances de l'autovacuum, lors de l'ajout de processus de travail, il est logique d'augmenter également autovacuum_vacuum_cost_limit .

Utilisation de la mémoire et surveillance

La dernière fois, nous avons observé comment VACUUM utilisait de la mémoire RAM de taille maintenance_work_mem pour stocker les données à nettoyer.

Autovacuum fait absolument la même chose. Mais il peut y avoir de nombreux processus de travail simultanés si autovacuum_max_workers est défini sur une grande valeur. De plus, toute la mémoire est allouée à la fois plutôt que lorsque le besoin s'en fait sentir. Par conséquent, pour un processus de travail, sa propre limitation peut être définie au moyen du paramètre autovacuum_work_mem . La valeur par défaut de ce paramètre est -1, c'est-à-dire qu'il n'est pas utilisé.

Comme déjà mentionné, VACUUM peut également fonctionner avec une taille de mémoire minimale. Mais si des index sont créés sur la table, une petite valeur de maintenance_work_mem peut entraîner des analyses d'index répétées. Il en va de même pour l'autovacuum. Idéalement, autovacuum_work_mem devrait avoir une valeur minimale telle qu'aucune analyse répétée ne se produise.

Nous avons vu que pour surveiller VACUUM, l'option VERBOSE peut être utilisée (qui ne peut pas être spécifiée pour autovacuum) ou la vue pg_stat_progress_vacuum (qui, cependant, n'affiche que les informations actuelles). Par conséquent, le principal moyen de surveiller la mise sous vide automatique consiste à utiliser le paramètre log_autovacuum_min_duration , qui génère les informations dans le journal des messages du serveur. Il est désactivé par défaut (défini sur -1). Il est raisonnable d'activer ce paramètre (avec la valeur 0, des informations sur tous les cycles de vide automatique seront produites) et de regarder les chiffres.

Voici à quoi ressemblent les informations de sortie:

 => ALTER SYSTEM SET log_autovacuum_min_duration = 0; => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

 => UPDATE autovac SET s = 'C' WHERE id <= 31; 

 student$ tail -n 7 /var/log/postgresql/postgresql-11-main.log 

 2019-05-21 11:59:55.675 MSK [9737] LOG: automatic vacuum of table "test.public.autovac": index scans: 0 pages: 0 removed, 18 remain, 0 skipped due to pins, 0 skipped frozen tuples: 31 removed, 1000 remain, 0 are dead but not yet removable, oldest xmin: 4040 buffer usage: 78 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2019-05-21 11:59:55.676 MSK [9737] LOG: automatic analyze of table "test.public.autovac" system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 

Toutes les informations nécessaires sont disponibles ici.

Pour vous rappeler, il est souvent judicieux d'abaisser le seuil de déclenchement sous vide afin de traiter moins de données à la fois plutôt que d'augmenter la taille de la mémoire.

Il peut également être raisonnable d'utiliser les vues ci-dessus pour surveiller la longueur de la liste des tables qui nécessitent une aspiration. L'augmentation de la longueur de la liste indiquera que les processus de vide automatique manquent de temps pour faire leur travail et que les paramètres doivent être modifiés.

À suivre.