Lorsque VACUUM échoue, nous nettoyons la table manuellement

VIDE peut « nettoyer » une table dans PostgreSQL uniquement ce qui personne ne peut voir - c'est-à-dire qu'il n'y a pas une seule demande active qui ait démarré avant la modification de ces enregistrements.

Mais que se passerait-il si un type aussi désagréable (charge OLAP à long terme sur une base de données OLTP) existait toujours ? Comment table à langer propre et active entouré de longues requêtes et ne pas marcher sur un râteau ?

Déplier le râteau

Tout d’abord, déterminons quel est le problème que nous voulons résoudre et comment il peut survenir.

Habituellement, cette situation se produit sur une table relativement petite, mais dans lequel cela se produit beaucoup de changements. Habituellement ceci ou différent compteurs/agrégats/calibres, sur lequel UPDATE est souvent exécuté, ou file d'attente tampon pour traiter un flux constant d'événements, dont les enregistrements sont constamment INSÉRÉS/SUPPRIMÉS.

Essayons de reproduire l'option avec les notes :

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- по этому индексу будем строить рейтинг

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

Et en parallèle, dans un autre contexte, une longue, longue requête démarre, collectant des statistiques complexes, mais n'affecte pas notre table:

SELECT pg_sleep(10000);

Maintenant, nous mettons à jour la valeur de l’un des compteurs plusieurs fois. Pour la pureté de l'expérience, faisons ceci dans des transactions distinctes utilisant dblinkcomment cela se passera-t-il en 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

Ce qui s'est passé? Pourquoi même pour la MISE À JOUR la plus simple d'un seul enregistrement temps d'exécution dégradé de 7 fois — de 0.524 ms à 3.808 ms ? Et notre note augmente de plus en plus lentement.

Tout est de la faute de MVCC.

C'est a propos de Mécanisme MVCC, ce qui amène la requête à parcourir toutes les versions précédentes de l'entrée. Nettoyons donc notre table des versions « mortes » :

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages
DETAIL:  10000 dead row versions cannot be removed yet, oldest xmin: 597439602

Oh, il n'y a rien à nettoyer ! Parallèle La requête en cours nous interfère - après tout, il voudra peut-être un jour se tourner vers ces versions (et si ?), et elles devraient être à sa disposition. Et donc même le VIDE PLEIN ne nous aidera pas.

« Réduire » la table

Mais nous savons avec certitude que cette requête n’a pas besoin de notre table. Par conséquent, nous essaierons toujours de ramener les performances du système à des limites adéquates en éliminant tout ce qui est inutile du tableau - au moins "manuellement", puisque VACUUM cède.

Pour que ce soit plus clair, regardons l’exemple du cas d’une table tampon. Autrement dit, il y a un flux important d'INSERT/DELETE et parfois la table est complètement vide. Mais si ce n'est pas vide, il faut enregistrer son contenu actuel.

#0 : Évaluer la situation

Il est clair que vous pouvez essayer de faire quelque chose avec la table même après chaque opération, mais cela n'a pas beaucoup de sens - la surcharge de maintenance sera clairement supérieure au débit des requêtes cibles.

Formulons les critères - « il est temps d'agir » si :

• VACUUM a été lancé il y a assez longtemps
  Nous nous attendons à une lourde charge, alors qu'il en soit ainsi 60 secondes depuis le dernier [auto]VIDE.
• la taille de la table physique est supérieure à la cible
  Définissons-le comme deux fois le nombre de pages (blocs de 8 Ko) par rapport à la taille minimale - 1 bloc pour le tas + 1 bloc pour chaque index - pour une table potentiellement vide. Si l’on s’attend à ce qu’une certaine quantité de données reste toujours « normalement » dans le tampon, il est raisonnable de modifier cette formule.

Demande de vérification

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, 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 : Toujours sous vide

Nous ne pouvons pas savoir à l'avance si une requête parallèle nous gêne de manière significative - combien d'enregistrements sont devenus « obsolètes » depuis son lancement. Par conséquent, lorsque nous décidons de traiter la table d’une manière ou d’une autre, nous devons dans tous les cas l’exécuter d’abord. VIDE - contrairement à VACUUM FULL, il n'interfère pas avec les processus parallèles travaillant avec des données en lecture-écriture.

En même temps, il peut immédiatement nettoyer la plupart de ce que nous aimerions supprimer. Oui, et les requêtes ultérieures sur cette table nous seront adressées par "cache chaud", ce qui réduira leur durée - et, par conséquent, la durée totale de blocage des autres par notre transaction de service.

#2 : Y a-t-il quelqu'un à la maison ?

Vérifions s'il y a quelque chose dans le tableau :

TABLE tbl LIMIT 1;

S'il ne reste plus un seul enregistrement, nous pouvons économiser beaucoup sur le traitement en faisant simplement TRONQUER:

Elle agit de la même manière qu'une commande DELETE inconditionnelle pour chaque table, mais est beaucoup plus rapide car elle n'analyse pas réellement les tables. De plus, il libère immédiatement de l'espace disque, il n'est donc pas nécessaire d'effectuer une opération VACUUM par la suite.

C'est à vous de décider si vous devez réinitialiser le compteur de séquence de table (RESTART IDENTITY).

#3 : Tout le monde, à tour de rôle !

Puisque nous travaillons dans un environnement hautement compétitif, pendant que nous vérifions ici qu’il n’y a aucune entrée dans le tableau, quelqu’un aurait pu déjà y écrire quelque chose. Nous ne devrions pas perdre ces informations, et alors ? C'est vrai, nous devons nous assurer que personne ne peut l'écrire avec certitude.

Pour ce faire, nous devons activer SÉRIALISABLE-isolation pour notre transaction (oui, ici nous démarrons une transaction) et verrouillons « étroitement » la table :

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

Ce niveau de blocage est déterminé par les opérations que l'on souhaite y effectuer.

#4 : Conflit d’intérêts

Nous venons ici et voulons « verrouiller » le panneau - et si quelqu'un était actif dessus à ce moment-là, par exemple en le lisant ? Nous allons « accrocher » en attendant la sortie de ce bloc, et d'autres qui veulent lire se heurteront à nous...

Pour éviter que cela ne se produise, nous nous "sacrifierons" - si nous ne parvenons pas à obtenir un verrou dans un certain délai (acceptablement court), nous recevrons alors une exception de la base, mais au moins nous n'interférerons pas trop avec autres.

Pour ce faire, définissez la variable de session lock_timeout (pour les versions 9.3+) ou/et instruction_timeout. La principale chose à retenir est que la valeur Statement_timeout ne s'applique qu'à partir de l'instruction suivante. C'est-à-dire comme ça dans le collage - ne fonctionnera pas:

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

Afin de ne pas avoir à restaurer «l'ancienne» valeur de la variable ultérieurement, nous utilisons la forme DÉFINIR LOCAL, ce qui limite la portée du paramètre à la transaction en cours.

Nous nous souvenons que Statement_timeout s'applique à toutes les requêtes ultérieures afin que la transaction ne puisse pas s'étendre à des valeurs inacceptables s'il y a beaucoup de données dans la table.

#5 : Copier les données

Si la table n'est pas complètement vide, les données devront être réenregistrées à l'aide d'une table temporaire auxiliaire :

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signature SUR COMMIT DROP signifie qu'au moment où la transaction se termine, la table temporaire cessera d'exister et il n'est pas nécessaire de la supprimer manuellement dans le contexte de connexion.

Puisque nous supposons qu’il n’y a pas beaucoup de données « live », cette opération devrait avoir lieu assez rapidement.

Eh bien voilà tout! N'oubliez pas après avoir terminé la transaction exécuter l'ANALYSE pour normaliser les statistiques du tableau si nécessaire.

Mettre en place le scénario final

Nous utilisons ce « 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;

Est-il possible de ne pas copier les données une seconde fois ?En principe, c'est possible si l'oid de la table elle-même n'est lié à aucune autre activité du côté BL ou FK du côté DB :

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 source 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 s'est bien passé ! La table a été réduite de 50 fois et toutes les MISE À JOUR fonctionnent à nouveau rapidement.

Source: habr.com