🥇Quand VACUUM ne fonctionne pas — nettoyons la table manuellement

VACUUM ne peut « nettoyer » de la table dans PostgreSQL que ce que personne ne peut voir — c'est-à-dire qu'il n'y a pas de requête active ayant commencé avant que ces enregistrements aient été modifiés.

Et si un type désagréable (une charge OLAP prolongée sur une base OLTP) est quand même là ? Comment nettoyer une table en constante évolution dans un environnement de longues requêtes sans se heurter aux mêmes problèmes ?

Démêlons les dilemmes

D'abord, déterminons en quoi consiste le problème que nous voulons résoudre et comment il peut survenir.

Cette situation se produit généralement sur une table relativement petite, mais où il y a beaucoup de modifications. Cela concerne généralement différents compteurs/aggrégats/classements, qui sont mis à jour fréquemment, ou une file d'attente tampon pour traiter un flux constant d'événements, les enregistrements étant constamment insérés/supprimés.

Essayons de reproduire un cas avec des classements :

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- c'est sur cet index que nous construirons le classement

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

Et en parallèle, dans une autre connexion, un long long запрос запускается, который собирает какую-то сложную статистику, но sans toucher notre table:

SELECT pg_sleep(10000);

Maintenant, nous mettons à jour de nombreuses fois la valeur de l'un des compteurs. Pour la clarté de l'expérience, faisons-le dans des transactions séparées avec dblink, comme cela se produira dans la réalité :

DO $$
DECLARE
  i integer;
  tsb timestamp;
  tse timestamp;
  d double precision;
BEGIN
  PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  FOR i IN 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$);
    tse = clock_timestamp();
    IF i % 1000 = 0 THEN
      d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5);
    END IF;
  END LOOP;
  PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

NOTICE:  i =  1000, exectime = 0.524
NOTICE:  i =  2000, exectime = 0.739
NOTICE:  i =  3000, exectime = 1.188
NOTICE:  i =  4000, exectime = 2.508
NOTICE:  i =  5000, exectime = 1.791
NOTICE:  i =  6000, exectime = 2.658
NOTICE:  i =  7000, exectime = 2.318
NOTICE:  i =  8000, exectime = 2.572
NOTICE:  i =  9000, exectime = 2.929
NOTICE:  i = 10000, exectime = 3.808

Que s'est-il passé ? Pourquoi même pour la mise à jour la plus simple d'un seul enregistrement le temps d'exécution a été dégradé par 7 — de 0.524ms à 3.808ms ? Et notre classement se construit de plus en plus lentement.

Tout cela à cause du MVCC

Tout est lié au mécanisme MVCC, qui oblige la requête à parcourir toutes les versions précédentes de l'enregistrement. Nettoyons donc notre table des versions « mortes » :

VACUUM VERBOSE tbl ;

INFO :  nettoyage de "public.tbl"
INFO :  "tbl" : trouvé 0 versions de lignes supprimables, 10026 versions de lignes non supprimables sur 45 pages sur 45
DÉTAIL :  10000 versions de lignes mortes ne peuvent pas encore être supprimées, xmin le plus ancien : 597439602

Oh là là, il n'y a rien à nettoyer ! Parallèlement la requête en cours nous gêne — car elle pourrait à un moment donné avoir besoin de ces versions (et si ?) et elles doivent lui être accessibles. C'est pourquoi même VACUUM FULL ne nous aidera pas.

Nous « compactons » la table

Mais nous savons pertinemment que cette requête n'a pas besoin de notre table. Essayons donc de ramener la performance du système à des limites raisonnables, en éliminant tout ce qui est superflu dans la table — au moins manuellement, puisque VACUUM ne fonctionne pas.

Pour être plus clair, prenons l'exemple d'une table tampon. Il y a donc un grand flux d'INSERT/DELETE, et parfois la table se retrouve complètement vide. Mais si elle n'est pas vide, nous devons préserver son contenu actuel.

#0: Оцениваем ситуацию

Il est évident que l'on peut essayer de faire quelque chose avec la table après chaque opération, mais cela n'a pas beaucoup de sens — les frais généraux de maintenance seront clairement plus élevés que la bande passante des requêtes cibles.

Formulons les critères — « il est déjà temps d'agir », si :

VACUUM a été lancé il y a longtemps
Nous prévoyons une forte charge, donc prenons ça comme 60 secondes depuis le dernier [auto]VACUUM.
la taille physique de la table est supérieure à la cible
Nous la définirons comme le double du nombre de pages (blocs de 8 Ko) par rapport à la taille minimale — 1 blk sur le tas + 1 blk pour chacun des index — pour une table potentiellement vide. Cependant, si nous prévoyons que la mémoire tampon contiendra « normalement » une certaine quantité de données, il est raisonnable d'ajuster cette formule.

Requête de contrôle

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- ici, il serait plus correct de multiplier par current_setting('block_size')::bigint, mais qui change la taille des blocs ?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: Все равно VACUUM

Nous ne pouvons pas savoir à l'avance dans quelle mesure une requête parallèle nous gêne — combien d'enregistrements sont devenus "obsolètes" depuis son lancement. Donc, quand nous déciderons finalement de traiter la table, il faudra d'abord exécuter VACUUM — elle, contrairement à VACUUM FULL, ne gêne pas les processus parallèles travaillant avec les données en lecture-écriture.

Elle peut également nettoyer la majeure partie de ce que nous voulons enlever. De plus, les requêtes suivantes sur cette table seront sur le "cache chaud", ce qui réduira leur durée — et donc le temps total de blocage de notre autre transaction de service.

#2: Есть кто-нибудь дома?

Vérifions s'il y a au moins quelque chose dans la table :

TABLE tbl LIMIT 1;

S'il ne reste aucun enregistrement, nous pouvons économiser beaucoup sur le traitement — simplement en exécutant TRUNCATE:

Elle fonctionne comme une commande DELETE inconditionnelle pour chaque table, mais beaucoup plus rapidement, car elle ne scanne pas réellement les tables. De plus, elle libère immédiatement de l'espace disque, donc il n'est pas nécessaire d'exécuter une opération VACUUM après.

Que vous souhaitiez réinitialiser le compteur de séquence de la table (RESTART IDENTITY) — c'est à vous de décider.

#3: Все — по-очереди!

Étant donné que nous travaillons dans un environnement à forte concurrence, pendant que nous vérifions l'absence d'enregistrements dans la table, quelqu'un a peut-être déjà écrit quelque chose. Nous ne devons pas perdre cette information, donc — quoi ? Correct, il faut s'assurer que personne ne puisse enregistrer.

Pour cela, nous devons activer SERIALIZABLE-l'isolement pour notre transaction (oui, ici nous démarrons une transaction) et bloquer la table "définitivement" :

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Ce niveau de verrouillage est nécessaire en raison des opérations que nous voulons effectuer sur elle.

#4: Конфликт интересов

Nous arrivons ici et voulons "verrouiller" la table — mais si à ce moment quelqu'un était actif dessus, par exemple, en lisant ? Nous "pendrons" en attendant que ce verrou soit libéré, tandis que d'autres qui veulent lire se heurteront à nous…

Pour éviter cela, nous "nous sacrifierons" — si après un certain temps (raisonnablement court) nous ne pouvons pas obtenir ce verrou, nous recevrons une exception de la base, mais au moins nous ne gênerons pas trop les autres.

Pour ce faire, nous définirons la variable de session lock_timeout (pour les versions 9.3+) ou/et statement_timeoutIl est important de se rappeler que la valeur de statement_timeout ne s'applique qu'à la déclaration suivante. Donc, cela ne fonctionne pas ainsi dans la concaténation — ne fonctionnera pas:

SET statement_timeout = ...; LOCK TABLE ...;

Pour éviter de restaurer plus tard la « vieille » valeur de la variable, utilisons la forme SET LOCAL, qui limite le champ d'application de la configuration à la transaction actuelle.

Souvenons-nous que statement_timeout s'applique à toutes les requêtes suivantes, afin que la transaction ne puisse pas s'étendre à des valeurs inacceptables, si les données dans la table s'avèrent nombreuses.

#5: Копируем данные

Si la table n'est pas complètement vide, il faudra réenregistrer les données via une table temporaire auxiliaire :

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signature ON COMMIT DROP signifie qu'à la fin de la transaction, la table temporaire cessera d'exister, et il n'est pas nécessaire de s'occuper de sa suppression manuelle dans le contexte de la connexion.

Puisque nous supposons qu'il n'y a pas beaucoup de données « vivantes », cette opération devrait se dérouler assez rapidement.

Voilà, c'est tout ! N'oubliez pas, après la fin de la transaction de lancer ANALYZE pour normaliser les statistiques de la table, si nécessaire.

Rassemblons le script final

Utilisons un tel « pseudo-python » :

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

Peut-on éviter de copier les données une seconde fois ?En principe, c'est possible, si aucune autre activité côté BL ou FK venant de la base de données n'est liée à l'oid de la table elle-même :

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Exécutons le script sur la table d'origine et vérifions les métriques :

VACUUM tbl;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
  LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
  CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
  TRUNCATE TABLE tbl;
  INSERT INTO tbl TABLE _tmp_swap;
COMMIT;

relpages | size_norm | size   | vaclag
-------------------------------------------
       0 |     24576 |  49152 | 32.705771

Tout a fonctionné ! La table a été réduite de 50 fois, et tous les UPDATE s'exécutent de nouveau rapidement.

Source : habr.com

Quand VACUUM échoue — nettoyons la table manuellement