VACUUM ne peut "nettoyer" d'une table dans PostgreSQL que ce que
personne ne peut voir - c'est-à-dire qu'il n'y a pas une seule requête active qui a commencé plus tôt que ces enregistrements n'ont été modifiés.
Mais s'il existe un type aussi désagréable (charge OLAP à long terme sur la base OLTP)? Comment
nettoyer une table changeant activement entourée de longues requêtes et ne pas marcher sur un râteau?
Nous déployons un râteau
Premièrement, nous déterminons ce que c'est et comment le problème que nous voulons résoudre peut survenir.
Habituellement, cette situation se produit
sur une table relativement petite , mais dans laquelle il
y a beaucoup de changements . En général, il s'agit soit de
compteurs / agrégats / classements différents , sur lesquels la MISE À JOUR est souvent exécutée, soit d'une
file d'
attente de mémoire tampon pour le traitement d'une sorte de flux d'événements en cours d'exécution, dont les enregistrements sont toujours INSERT / DELETE.
Essayons de reproduire l'option avec des notes:
CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC);
Et en parallèle, dans une connexion différente, une requête longue-longue démarre, collectant des statistiques complexes, mais
n'affectant 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, nous le ferons
dans des transactions séparées en utilisant dblink , car cela se produira 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
Que s'est-il passé? Pourquoi, même pour la MISE À JOUR la plus simple d'un seul enregistrement
, le temps d'exécution s'est dégradé de 7 fois - de 0,524 ms à 3,808 ms? Et notre cote se construit de plus en plus lentement.
MVCC est à blâmer
Il s'agit
du mécanisme MVCC , qui oblige la demande à consulter toutes les versions précédentes de l'enregistrement. 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! Une
requête parallèle
nous dérange - après tout, un jour, il voudra peut-être se référer à ces versions (et si?), Et elles devraient être disponibles pour lui. Et donc même VACUUM FULL ne nous aidera pas.
«Serrer» la table
Mais nous savons avec certitude que notre table n'a pas besoin de notre requête. Par conséquent, essayons de restaurer les performances du système dans un cadre adéquat, après avoir éliminé tout ce qui est superflu de la table - au moins «manuellement», puisque VACUUM passe.
Pour le rendre plus clair, considérons un exemple de table tampon. Autrement dit, il existe un grand flux INSERT / DELETE, et parfois la table est complètement vide. Mais s'il n'y est pas vide, il faut
sauvegarder 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 demandes ciblées.
Nous formulons les critères - «il est temps d'agir», si:
- VACUUM fonctionne depuis longtemps
Nous nous attendons à une charge importante, alors laissez-la s'écouler à 60 secondes du dernier [auto] VIDE. - taille de la table physique supérieure à la cible
Nous le définissons comme le nombre doublé de pages (blocs de 8 Ko) par rapport à la taille minimale - 1 bloc par segment + 1 bloc pour chacun des indices - pour une table potentiellement vide. Si nous nous attendons à ce qu'une certaine quantité de données reste toujours dans le tampon «normalement», il est raisonnable de resserrer cette formule.
Demande de vérification SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 1105920 | 3392.484835
# 1: Aspirateur de toute façon
Nous ne pouvons pas savoir à l'avance si la requête parallèle nous gêne vraiment - exactement combien d'enregistrements sont «obsolètes» depuis sa création. Par conséquent, lorsque nous décidons néanmoins de traiter la table d'une manière ou d'une autre, dans tous les cas, vous devez d'abord exécuter
VACUUM dessus - contrairement à VACUUM FULL, il n'interfère pas avec les processus parallèles de lecture et d'écriture de données.
Dans le même temps, il peut immédiatement nettoyer la plupart de ce que nous aimerions supprimer. Oui, et les demandes ultérieures pour cette table nous seront envoyées
dans un "cache chaud" , ce qui réduira leur durée - et, par conséquent, le temps total pour bloquer les autres avec notre transaction de diffusion.
# 2: Quelqu'un est-il à la maison?
Vérifions - y a-t-il quelque chose dans le tableau:
TABLE tbl LIMIT 1;
S'il ne reste plus un seul enregistrement, alors nous pouvons économiser beaucoup sur le traitement - juste en faisant
TRUNCATE :
Elle agit de la même manière que la commande DELETE inconditionnelle pour chaque table, mais beaucoup plus rapidement, 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 après.
Si vous devez réinitialiser le compteur de la séquence de tableaux (RESTART IDENTITY) en même temps - décidez par vous-même.
# 3: Tout - à son tour!
Puisque nous travaillons dans des conditions de forte compétitivité, alors que nous vérifions ici l'absence d'inscriptions dans le tableau, quelqu'un pourrait déjà y écrire quelque chose. Nous ne devons pas perdre cette information, alors quoi? C'est vrai, cela doit être fait pour que personne ne puisse enregistrer avec certitude.
Pour ce faire, nous devons activer l'isolement
SERIALISABLE pour notre transaction (oui, nous commençons ici la transaction) et verrouiller la table «étroitement»:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
Ce niveau de blocage est dû aux opérations que nous souhaitons y effectuer.
# 4: Conflit d'intérêts
Nous venons ici et nous voulons «verrouiller» la tablette - et si quelqu'un y était actif à ce moment, par exemple, lire? On va "se bloquer" en prévision de la sortie de ce bloc, tandis que d'autres qui souhaitent lire seront déjà enterrés en nous ...
Pour éviter que cela ne se produise, "sacrifiez-vous" - si nous ne pouvons toujours pas obtenir le verrou pendant un certain temps (petit autorisé), nous obtiendrons une exception de la base de données, mais au moins nous ne dérangerons pas le reste.
Pour ce faire, définissez la variable de session
lock_timeout (pour les versions 9.3+) ou / et
statement_timeout . La principale chose à retenir est que la valeur de statement_timeout s'applique uniquement à partir de l'instruction suivante. Autrement dit, comme dans le collage,
cela ne fonctionnera pas :
SET statement_timeout = ...;LOCK TABLE ...;
Afin de ne pas traiter de la restauration ultérieure de l '«ancienne» valeur de la variable, nous utilisons le formulaire
SET LOCAL , qui limite la portée des paramètres à la transaction en cours.
N'oubliez pas que statement_timeout s'applique à toutes les demandes ultérieures afin que la transaction ne puisse pas s'étirer à des valeurs inacceptables s'il y a beaucoup de données dans la table.
# 5: Copier les données
Si le tableau s'est avéré ne pas être complètement vide, les données devront être réenregistrées via l'étiquette temporaire auxiliaire:
CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
La signature
ON COMMIT DROP signifie qu'au moment où la transaction se termine, la table temporaire cesse d'exister et vous n'avez pas besoin de la supprimer manuellement dans le contexte de la connexion.
Puisque nous supposons qu'il n'y a pas beaucoup de données «en direct», cette opération devrait se dérouler assez rapidement.
Et bien c'est tout! N'oubliez
pas d'exécuter ANALYZE une fois la transaction terminée pour normaliser les statistiques de la table, si nécessaire.
Nous collectons le script final
Nous utilisons un tel "pseudo python":
Et ne pouvez-vous pas copier les données une deuxième fois?En principe, cela 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 a fonctionné! La table a diminué de 50 fois et toutes les MISES À JOUR s'exécutent à nouveau rapidement.