Permettez-moi de vous rappeler que nous avons commencé par des problèmes liés à l' isolement , fait une digression sur l' organisation des données à un bas niveau , parlé en détail des versions de ligne et comment les instantanés sont obtenus à partir des versions.

Ensuite, nous avons examiné le nettoyage sur la page (et les mises à jour HOT), le nettoyage régulier , mais aujourd'hui, nous examinons le nettoyage automatique.

Nettoyage automatique (autovacuum)

Nous avons déjà dit que le nettoyage ordinaire dans des conditions normales (lorsque personne ne tient l'horizon de transaction pendant longtemps) doit faire face à son travail. La question est de savoir à quelle fréquence l'appeler.

Si vous nettoyez trop rarement une table à langer, elle grossira plus que vous ne le souhaiteriez. De plus, pour le prochain nettoyage, il peut falloir plusieurs passages dans les indices si trop de changements se sont accumulés.

Si vous nettoyez la table trop souvent, au lieu d'un travail utile, le serveur effectuera constamment des opérations de maintenance, ce qui n'est pas bon non plus.

Notez que le démarrage d'un nettoyage planifié régulier ne résout pas le problème, car la charge peut changer avec le temps. Si la table a commencé à être mise à jour plus activement, elle doit être nettoyée plus souvent.

Le nettoyage automatique n'est que le mécanisme qui vous permet de commencer le nettoyage, en fonction de l'activité des changements dans les tables.

Lorsque l'autonettoyage est activé (paramètre de configuration autovacuum ), le processus de lancement autovacuum est toujours présent dans le système, qui prévoit de fonctionner, et les flux de travail des travailleurs autovacuum sont impliqués dans le nettoyage réel, dont plusieurs instances peuvent fonctionner en parallèle.

Le processus de lancement autovacuum compile une liste de bases de données dans lesquelles il existe une activité. L'activité est déterminée par des statistiques et, pour être collectée, le paramètre track_counts doit être défini. Ne désactivez jamais le vide automatique et les track_counts , sinon le nettoyage automatique ne fonctionnera pas.

Une fois dans autovacuum_naptime, le processus du lanceur autovacuum démarre (à l'aide du processus postmaster) un flux de travail pour chaque base de données de la liste. En d'autres termes, s'il existe une activité dans la base de données, les workflows y entreront avec l'intervalle autovacuum_naptime . Pour ce faire, s'il existe plusieurs bases de données actives (N pièces), les processus de travail sont lancés N fois plus souvent que autovacuum_naptime . Mais en même temps, le nombre total de workflows fonctionnant simultanément est limité par le paramètre autovacuum_max_workers .

Après avoir démarré, le workflow se connecte à la base de données spécifiée par lui et commence par construire la liste:

• toutes les tables, vues matérialisées et toasts qui doivent être effacées,
• toutes les tables et représentations matérialisées qui nécessitent une analyse (les tables de toasts ne sont pas analysées, car elles sont toujours accessibles par index).

En outre, le flux de travail nettoie et / ou analyse à son tour les objets sélectionnés et se termine lorsque le nettoyage est terminé.

Si le processus n'a pas terminé tout le travail prévu pour autovacuum_naptime , le processus de lancement autovacuum enverra un autre flux de travail à la même base de données et ils travailleront ensemble. «Ensemble» signifie simplement que le deuxième processus construira sa liste de tableaux et la suivra. Ainsi, différentes tables seront traitées en parallèle, mais au niveau d'une table il n'y a pas de parallélisme - si l'un des processus de travail travaille déjà sur la table, l'autre la sautera et continuera.

Il y a eu une discussion sur la nécessité d'un traitement parallèle depuis longtemps, mais le patch n'a pas encore été adopté.

Examinons maintenant de plus près ce qui "nécessite un nettoyage" et "nécessite une analyse".

Quelles tables doivent être nettoyées

On pense qu'un nettoyage est nécessaire si le nombre de versions "mortes", c'est-à-dire non pertinentes, de chaînes dépasse une valeur de seuil définie. Le nombre de versions mortes est constamment collecté par le collecteur de statistiques et stocké dans la table pg_stat_all_tables. Et le seuil est fixé par deux paramètres:

• autovacuum_vacuum_threshold définit la valeur absolue (en morceaux),
• autovacuum_vacuum_scale_factor détermine la proportion de lignes dans une table.

La formule finale est: le nettoyage est requis si pg_stat_all_tables.n_dead_tup> = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes.

Les paramètres par défaut définissent autovacuum_vacuum_threshold = 50 et
autovacuum_vacuum_scale_factor = 0.2. Le paramètre principal ici, bien sûr, est autovacuum_vacuum_scale_factor - c'est lui qui est important pour les grandes tables (à savoir, les problèmes possibles leur sont associés). La valeur de 20% semble être largement surestimée, elle devra probablement être considérablement réduite.

Les valeurs optimales des paramètres peuvent varier pour différentes tables en fonction de leur taille et de la nature des modifications. Il est logique d'établir, dans l'ensemble, des valeurs adéquates et - si nécessaire - de configurer spécialement les paramètres au niveau de certaines tables en utilisant les paramètres de stockage:

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

Afin de ne pas être confus, cela ne devrait être fait que pour un petit nombre de tableaux qui se distinguent entre autres par le volume ou l'intensité des changements, et uniquement si les valeurs définies globalement ne conviennent pas.

De plus, l'autonettoyage peut être désactivé au niveau de la table (bien qu'il soit difficile de penser à une raison pour laquelle cela serait nécessaire):

• autovacuum_enabled et toast.autovacuum_enabled .

Par exemple, la dernière fois que nous avons créé une table d'aspirateur avec le nettoyage automatique désactivé, pour - à des fins de démonstration - gérer le nettoyage manuel. 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, nous allons créer une vue montrant quelles tables doivent actuellement être nettoyées. Il utilisera une fonction qui renvoie la valeur actuelle du paramètre, étant donné qu'elle peut être remplacée au niveau de la table:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( --    ,    (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE --  toast-    WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), --      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 doivent être analysés

Avec l'auto-analyse, la situation est à peu près la même. On pense qu'une analyse est requise pour les tables pour lesquelles le nombre de versions modifiées (depuis la dernière analyse) des lignes dépasse la valeur seuil spécifiée 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 d'auto-analyse par défaut sont légèrement 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 pour les tables individuelles:

• autovacuum_analyze_threshold ,
• autovacuum_analyze_scale_factor

Étant donné que les tableaux de toasts ne sont pas analysés, il n'y a pas de paramètres correspondants pour eux.

Créons 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

Pour les expériences, nous définissons les valeurs de paramètres suivantes:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; --     => 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éez maintenant un tableau similaire à celui que nous avons utilisé la dernière fois et insérez-y mille lignes. Le nettoyage automatique est désactivé au niveau de la table et nous allons l'activer nous-mêmes. Si cela n'est pas fait, les exemples ne seront pas reproductibles, car l'autonettoyage peut fonctionner au 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 vue de nettoyage va montrer:

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

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

Il y a deux points auxquels vous devez faire attention. Premièrement, max_dead_tup = 0, bien que 3% de 1000 lignes soit 30 lignes. Le fait est que nous n'avons pas encore de statistiques sur la table, car INSERT lui-même ne le met pas à jour. Jusqu'à ce que notre table soit analysée, les zéros resteront, puisque pg_class.reltuples = 0. Cependant, jetons un œil à 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 le tableau a modifié (ajouté) 1 000 lignes, et c'est plus que zéro, l'auto-analyse devrait fonctionner. Vérifiez ceci:

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

Après une courte pause, nous voyons que la table est analysée et au lieu de zéros dans max_mod_tup, nous voyons les 20 lignes correctes:

 => 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 au nettoyage automatique:

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

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

Max_dead_tup, comme nous le voyons, a déjà été corrigé. Le deuxième point à prendre en compte est dead_tup = 0. Les statistiques montrent qu'il n'y a pas de versions mortes de lignes dans le tableau ... et c'est vrai. Il n'y a encore rien à nettoyer dans notre table. Ainsi, toute table utilisée uniquement en mode ajout uniquement ne sera pas effacée et, par conséquent, la carte de visibilité ne sera pas mise à jour pour elle. Et cela rend impossible l'utilisation exclusive de l'analyse d'index (analyse d'index uniquement).

(La prochaine fois, nous verrons que le nettoyage arrivera tôt ou tard à la table en annexe uniquement, mais cela se produira très rarement.)

Conclusion pratique: s'il est important d'utiliser uniquement la numérisation d'index, vous devrez peut-être appeler pour un nettoyage manuel.

Désactivez maintenant le nettoyage automatique et mettez à jour la ligne 31 - une de plus que la valeur 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 de l'auto-nettoyage est maintenant remplie. Activez le nettoyage automatique et après une courte pause, nous verrons que le tableau a été traité:

 => 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) 

Régulation de charge

Le nettoyage ne bloque pas les autres processus, car il fonctionne page par page, mais crée néanmoins une charge sur le système et peut avoir un effet notable sur les performances.

Règlement pour le nettoyage régulier

Afin de pouvoir contrôler l'intensité du nettoyage et, par conséquent, son effet sur le système, le processus alterne entre travail et attente. Le nettoyage effectue approximativement les unités de travail conventionnelles de vacuum_cost_limit , puis s'endort sur vacuum_cost_delay ms.

Les paramètres par défaut définissent vacuum_cost_limit = 200, vacuum_cost_delay = 0. Le dernier zéro signifie en fait que le nettoyage (normal) ne s'endort pas, de sorte que la valeur spécifique de vacuum_cost_limit ne joue aucun rôle. Cela est dû au fait que si l'administrateur doit démarrer VACUUM manuellement, il souhaite probablement effectuer le nettoyage le plus rapidement possible.

Néanmoins, si vous définissez toujours le temps de veille, la quantité de travail spécifiée dans vacuum_cost_limit correspondra au coût de travail avec les pages dans le cache de tampon. Chaque accès à une page est évalué comme suit:

• si la page a été trouvée dans le cache de tampon, alors vacuum_cost_page_hit = 1;
• s'il n'est pas trouvé, vacuum_cost_page_miss = 10;
• si vous ne le trouviez pas et que vous deviez pousser la page sale hors du tampon, alors vacuum_cost_page_dirty = 20.

Autrement dit, avec les paramètres vacuum_cost_limit par défaut, 200 pages du cache, ou 20 pages du disque, ou 10 pages avec extrusion peuvent être traitées en une seule séance. Il est clair que ce sont des nombres plutôt arbitraires, mais cela n'a aucun sens de les sélectionner plus précisément.

Règlement pour le nettoyage automatique

Le contrôle de la charge pendant le nettoyage automatique fonctionne de la même manière que pour le nettoyage régulier. Mais pour que le nettoyage manuel et l' auto- nettoyage puissent fonctionner avec des intensités différentes, les processus automatiques ont leurs propres paramètres: autovacuum_vacuum_cost_limit et autovacuum_vacuum_cost_delay . Si ces paramètres prennent la valeur -1, alors 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 vacuum_cost_limit = 200 est utilisée) et autovacuum_vacuum_cost_delay = 20ms. Sur les équipements modernes avec ces numéros, l'auto-nettoyage fonctionnera très, très lentement.

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

De plus, il convient de noter que la limite fixée par ces paramètres est commune à tous les processus de travail. En d'autres termes, à mesure que le nombre de processus de travail simultanés change, la charge totale restera constante. Par conséquent, si la tâche consiste à augmenter les performances du nettoyage automatique , alors lors de l'ajout de workflows, il vaut la peine d'augmenter autovacuum_vacuum_cost_limit .

Utilisation et surveillance de la mémoire

La dernière fois, nous avons examiné comment le nettoyage utilise la mémoire RAM de la taille maintenance_work_mem pour stocker les identificateurs de version des lignes à nettoyer.

Le nettoyage automatique fait exactement la même chose. Mais il peut y avoir de nombreux processus simultanés si vous définissez autovacuum_max_workers sur une grande valeur. De plus, toute la mémoire est allouée immédiatement et complètement, et non par nécessité. Par conséquent, pour le workflow de nettoyage automatique , vous pouvez définir votre propre restriction à l'aide du paramètre autovacuum_work_mem . Par défaut, ce paramètre est -1, c'est-à-dire qu'il n'est pas utilisé.

Comme déjà mentionné, le nettoyage peut fonctionner avec un minimum de mémoire. Mais si des index sont créés sur la table, une petite valeur maintenance_work_mem peut conduire à des analyses d'index répétées. Il en va de même pour l'auto-nettoyage. Idéalement, vous devez sélectionner une valeur minimale de autovacuum_work_mem à laquelle les analyses répétées ne se produisent pas.

Nous avons vu que pour surveiller le nettoyage, vous pouvez utiliser le paramètre VERBOSE (mais il ne peut pas être spécifié pour le nettoyage automatique) ou la vue pg_stat_progress_vacuum (mais il n'affiche que les informations actuelles). Par conséquent, le principal moyen de surveiller le nettoyage automatique est le paramètre log_autovacuum_min_duration , qui affiche des informations dans le journal des messages du serveur. Par défaut, il est désactivé (défini sur -1). Il y a une raison pour activer ce paramètre (à une valeur de 0, des informations sur tous les démarrages d'autonettoyage seront affichées) et observer les chiffres.

Voici à quoi ressemble la 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 présentes ici.

Rappelez-vous que, souvent, vous ne devez pas augmenter la taille de la mémoire, mais diminuer le seuil de nettoyage afin que moins de données soient traitées à la fois.

Il peut également être judicieux de surveiller la longueur de la liste des tables qui doivent être nettoyées à l'aide des vues ci-dessus. Une augmentation de la longueur de la liste indiquera que l'autonettoyage n'a pas le temps de faire son travail et que les paramètres doivent être modifiés.

À suivre .