Όταν το VACUUM αποτυγχάνει, καθαρίζουμε το τραπέζι χειροκίνητα

ΚΕΝΟ μπορεί να "καθαρίσει" από έναν πίνακα στην PostgreSQL μόνο τι κανείς δεν μπορεί να δει - δηλαδή, δεν υπάρχει ούτε ένα ενεργό αίτημα που ξεκίνησε πριν από την αλλαγή αυτών των εγγραφών.

Αλλά τι γίνεται αν ένας τέτοιος δυσάρεστος τύπος (μακροπρόθεσμη φόρτωση OLAP σε μια βάση δεδομένων OLTP) εξακολουθεί να υπάρχει; Πως καθαρό ενεργά αλλαξιέρα περιτριγυρίζεσαι από μεγάλες απορίες και δεν πατάς τσουγκράνα;

Ξεδιπλώνοντας τη γκανιότα

Αρχικά, ας προσδιορίσουμε ποιο είναι το πρόβλημα που θέλουμε να λύσουμε και πώς μπορεί να προκύψει.

Συνήθως συμβαίνει αυτή η κατάσταση σε ένα σχετικά μικρό τραπέζι, αλλά στο οποίο εμφανίζεται πολλές αλλαγές. Συνήθως αυτό ή διαφορετικό μέτρα/αδρανή/διαβαθμίσεις, στο οποίο εκτελείται συχνά UPDATE, ή buffer-queue για την επεξεργασία ορισμένων συνεχώς εν εξελίξει ροής γεγονότων, τα αρχεία των οποίων είναι συνεχώς ΕΙΣΑΓΩΓΗ/ΔΙΑΓΡΑΦΗ.

Ας προσπαθήσουμε να αναπαράγουμε την επιλογή με αξιολογήσεις:

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;

Και παράλληλα, σε μια άλλη σύνδεση, ξεκινά ένα μακρύ, μακρύ αίτημα, συλλέγοντας μερικά πολύπλοκα στατιστικά στοιχεία, αλλά δεν επηρεάζει το τραπέζι μας:

SELECT pg_sleep(10000);

Τώρα ενημερώνουμε την τιμή ενός από τους μετρητές πολλές, πολλές φορές. Για την καθαρότητα του πειράματος, ας το κάνουμε αυτό σε ξεχωριστές συναλλαγές χρησιμοποιώντας dblinkπώς θα συμβεί στην πραγματικότητα:

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

Τι συνέβη? Γιατί ακόμα και για την πιο απλή ΕΝΗΜΕΡΩΣΗ ενός δίσκου ο χρόνος εκτέλεσης μειώθηκε κατά 7 φορές - από 0.524ms έως 3.808ms; Και η βαθμολογία μας χτίζεται όλο και πιο αργά.

Για όλα φταίει η MVCC.

Πρόκειται για Μηχανισμός MVCC, το οποίο κάνει το ερώτημα να εξετάσει όλες τις προηγούμενες εκδόσεις της καταχώρισης. Ας καθαρίσουμε λοιπόν το τραπέζι μας από «νεκρές» εκδόσεις:

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

Ω, δεν υπάρχει τίποτα για καθαρισμό! Παράλληλο Το τρέχον αίτημα παρεμβαίνει σε εμάς - τελικά, μπορεί κάποια μέρα να θέλει να στραφεί σε αυτές τις εκδόσεις (τι θα γινόταν αν;), και θα πρέπει να είναι διαθέσιμες σε αυτόν. Και επομένως ούτε το VACUUM FULL δεν θα μας βοηθήσει.

«Καταρρέει» το τραπέζι

Αλλά γνωρίζουμε με βεβαιότητα ότι αυτό το ερώτημα δεν χρειάζεται τον πίνακά μας. Επομένως, θα προσπαθήσουμε και πάλι να επαναφέρουμε την απόδοση του συστήματος σε επαρκή όρια εξαλείφοντας οτιδήποτε περιττό από τον πίνακα - τουλάχιστον «χειροκίνητα», αφού το VACUUM υποχωρεί.

Για να γίνει πιο σαφές, ας δούμε το παράδειγμα της περίπτωσης ενός buffer table. Δηλαδή, υπάρχει μεγάλη ροή INSERT/DELETE και μερικές φορές ο πίνακας είναι εντελώς άδειος. Αλλά αν δεν είναι άδειο, πρέπει αποθηκεύστε το τρέχον περιεχόμενό του.

#0: Αξιολόγηση της κατάστασης

Είναι σαφές ότι μπορείτε να προσπαθήσετε να κάνετε κάτι με τον πίνακα ακόμα και μετά από κάθε λειτουργία, αλλά αυτό δεν έχει πολύ νόημα - τα γενικά έξοδα συντήρησης θα είναι σαφώς μεγαλύτερα από την απόδοση των ερωτημάτων στόχου.

Ας διαμορφώσουμε τα κριτήρια - "ήρθε η ώρα να δράσουμε" εάν:

• Το VACUUM κυκλοφόρησε εδώ και πολύ καιρό
  Περιμένουμε βαρύ φορτίο, ας είναι λοιπόν 60 δευτερόλεπτα από το τελευταίο [αυτόματο]ΚΕΝΟ.
• Το μέγεθος του φυσικού πίνακα είναι μεγαλύτερο από τον στόχο
  Ας το ορίσουμε ως διπλάσιο αριθμό σελίδων (μπλοκ 8 KB) σε σχέση με το ελάχιστο μέγεθος - 1 blk για το σωρό + 1 blk για κάθε δείκτη - για ένα δυνητικά άδειο τραπέζι. Εάν περιμένουμε ότι ένας συγκεκριμένος όγκος δεδομένων θα παραμείνει πάντα στο buffer «κανονικά», είναι λογικό να τροποποιήσουμε αυτόν τον τύπο.

Αίτημα επαλήθευσης

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: Ακόμα ΚΕΝΟ

Δεν μπορούμε να γνωρίζουμε εκ των προτέρων εάν ένα παράλληλο ερώτημα παρεμβαίνει σημαντικά σε εμάς - πόσες ακριβώς εγγραφές έχουν γίνει "απαρχαιωμένες" από τότε που ξεκίνησε. Επομένως, όταν αποφασίσουμε να επεξεργαστούμε με κάποιο τρόπο τον πίνακα, σε κάθε περίπτωση, θα πρέπει πρώτα να εκτελέσουμε σε αυτόν ΚΕΝΟ - Σε αντίθεση με το VACUUM FULL, δεν παρεμβαίνει σε παράλληλες διαδικασίες που λειτουργούν με δεδομένα ανάγνωσης-εγγραφής.

Ταυτόχρονα, μπορεί να καθαρίσει αμέσως τα περισσότερα από αυτά που θα θέλαμε να αφαιρέσουμε. Ναι, και οι επόμενες ερωτήσεις σε αυτόν τον πίνακα θα πάνε σε εμάς από "hot cache", που θα μειώσει τη διάρκειά τους - και, ως εκ τούτου, τον συνολικό χρόνο αποκλεισμού άλλων από τη συναλλαγή εξυπηρέτησης μας.

#2: Είναι κανείς σπίτι;

Ας ελέγξουμε αν υπάρχει κάτι στον πίνακα:

TABLE tbl LIMIT 1;

Εάν δεν έχει μείνει ούτε ένα αρχείο, τότε μπορούμε να εξοικονομήσουμε πολλά στην επεξεργασία κάνοντας απλά ΚΟΥΤΣΟΥΡΕΥΩ:

Λειτουργεί το ίδιο με μια εντολή άνευ όρων ΔΙΑΓΡΑΦΗ για κάθε πίνακα, αλλά είναι πολύ πιο γρήγορη αφού στην πραγματικότητα δεν σαρώνει τους πίνακες. Επιπλέον, ελευθερώνει αμέσως χώρο στο δίσκο, επομένως δεν χρειάζεται να εκτελέσετε μια λειτουργία VACUUM μετά.

Το εάν πρέπει να επαναφέρετε τον μετρητή ακολουθίας πινάκων (ΕΠΑΝΕΚΚΙΝΗΣΗ ΤΑΥΤΟΤΗΤΑΣ) εξαρτάται από εσάς να αποφασίσετε.

#3: Όλοι - πάρτε τη σειρά!

Δεδομένου ότι εργαζόμαστε σε ένα άκρως ανταγωνιστικό περιβάλλον, ενώ είμαστε εδώ για να ελέγξουμε ότι δεν υπάρχουν καταχωρήσεις στον πίνακα, κάποιος θα μπορούσε να έχει ήδη γράψει κάτι εκεί. Δεν πρέπει να χάσουμε αυτές τις πληροφορίες, οπότε τι; Αυτό είναι σωστό, πρέπει να βεβαιωθούμε ότι κανείς δεν μπορεί να το γράψει σίγουρα.

Για να γίνει αυτό πρέπει να ενεργοποιήσουμε ΣΕΙΡΑΙΩΣΙΜΟ-απομόνωση για τη συναλλαγή μας (ναι, εδώ ξεκινάμε μια συναλλαγή) και κλειδώνουμε το τραπέζι «σφιχτά»:

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

Αυτό το επίπεδο αποκλεισμού καθορίζεται από τις λειτουργίες που θέλουμε να εκτελέσουμε σε αυτό.

#4: Σύγκρουση συμφερόντων

Ερχόμαστε εδώ και θέλουμε να "κλειδώσουμε" την πινακίδα - τι θα γινόταν αν κάποιος ήταν ενεργός σε αυτήν εκείνη τη στιγμή, για παράδειγμα, διάβαζε από αυτήν; Θα «κολλήσουμε» περιμένοντας να κυκλοφορήσει αυτό το μπλοκ και άλλοι που θέλουν να διαβάσουν θα μας βρουν...

Για να μην συμβεί αυτό, θα «θυσιασθούμε» - εάν δεν μπορέσουμε να αποκτήσουμε κλειδαριά μέσα σε ένα ορισμένο (αποδεκτά σύντομο) χρονικό διάστημα, τότε θα λάβουμε εξαίρεση από τη βάση, αλλά τουλάχιστον δεν θα επέμβουμε πολύ. οι υπολοιποι.

Για να το κάνετε αυτό, ορίστε τη μεταβλητή συνεδρίας lock_timeout (για εκδόσεις 9.3+) ή/και statement_timeout. Το κύριο πράγμα που πρέπει να θυμάστε είναι ότι η τιμή statement_timeout ισχύει μόνο από την επόμενη δήλωση. Δηλαδή, έτσι στο κόλλημα - δεν θα λειτουργήσει:

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

Για να μην χρειαστεί να ασχοληθούμε αργότερα με την επαναφορά της «παλιάς» τιμής της μεταβλητής, χρησιμοποιούμε τη φόρμα ΡΥΘΜΙΣΗ ΤΟΠΙΚΟΥ, το οποίο περιορίζει το εύρος της ρύθμισης στην τρέχουσα συναλλαγή.

Θυμόμαστε ότι το statement_timeout ισχύει για όλα τα επόμενα αιτήματα, έτσι ώστε η συναλλαγή να μην μπορεί να επεκταθεί σε μη αποδεκτές τιμές εάν υπάρχουν πολλά δεδομένα στον πίνακα.

#5: Αντιγραφή δεδομένων

Εάν ο πίνακας δεν είναι εντελώς άδειος, τα δεδομένα θα πρέπει να αποθηκευτούν ξανά χρησιμοποιώντας έναν βοηθητικό προσωρινό πίνακα:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Υπογραφή ON COMMI DOP σημαίνει ότι τη στιγμή που τελειώνει η συναλλαγή, ο προσωρινός πίνακας θα πάψει να υπάρχει και δεν χρειάζεται να τον διαγράψετε με μη αυτόματο τρόπο στο πλαίσιο σύνδεσης.

Εφόσον υποθέτουμε ότι δεν υπάρχουν πολλά «ζωντανά» δεδομένα, αυτή η λειτουργία θα πρέπει να πραγματοποιηθεί αρκετά γρήγορα.

Λοιπόν, αυτό είναι όλο! Μην ξεχάσετε μετά την ολοκλήρωση της συναλλαγής εκτελέστε ANALYZE για να ομαλοποιήσετε τα στατιστικά στοιχεία του πίνακα εάν είναι απαραίτητο.

Συνδυάζοντας το τελικό σενάριο

Χρησιμοποιούμε αυτόν τον "ψευδοπύθωνα":

# собираем статистику с таблицы
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;

Είναι δυνατόν να μην αντιγραφούν τα δεδομένα για δεύτερη φορά;Κατ 'αρχήν, είναι δυνατό εάν το ίδιο το oid του πίνακα δεν συνδέεται με άλλες δραστηριότητες από την πλευρά BL ή FK από την πλευρά 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;

Ας εκτελέσουμε το σενάριο στον πίνακα προέλευσης και ας ελέγξουμε τις μετρήσεις:

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

Όλα λειτούργησαν! Ο πίνακας έχει συρρικνωθεί κατά 50 φορές και όλες οι ΕΝΗΜΕΡΩΣΕΙΣ τρέχουν ξανά γρήγορα.

Πηγή: www.habr.com