ProHoster > Log > administrasjon > Når VAKUUM svikter, rengjør vi bordet manuelt

Når VAKUUM svikter, rengjør vi bordet manuelt

VAKUUM kan "rydde opp" fra en tabell i PostgreSQL bare hva ingen kan se - det vil si at det ikke er en eneste aktiv forespørsel som startet før disse postene ble endret.

Men hva om en så ubehagelig type (langvarig OLAP-belastning på en OLTP-database) fortsatt eksisterer? Hvordan rent aktivt stellebord omgitt av lange spørringer og ikke tråkke på en rake?

Når VAKUUM svikter, rengjør vi bordet manuelt

Legger ut riven

La oss først finne ut hva problemet vi ønsker å løse er og hvordan det kan oppstå.

Vanligvis skjer denne situasjonen på et relativt lite bord, men hvor det forekommer mange endringer. Vanligvis dette eller annerledes målere/aggregater/klassifiseringer, som UPDATE ofte utføres på, eller buffer-kø å behandle en konstant pågående strøm av hendelser, registreringer av disse er konstant INSERT/DELETE.

La oss prøve å reprodusere alternativet med vurderinger:

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;

Og parallelt, i en annen forbindelse, starter en lang, lang forespørsel, som samler inn litt kompleks statistikk, men påvirker ikke bordet vårt:

SELECT pg_sleep(10000);

Nå oppdaterer vi verdien på en av tellerne mange, mange ganger. For renheten til eksperimentet, la oss gjøre dette i separate transaksjoner ved hjelp av dblinkhvordan det vil skje i virkeligheten:

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

Hva skjedde? Hvorfor selv for den enkleste OPPDATERING av en enkelt post utførelsestid redusert med 7 ganger - fra 0.524ms til 3.808ms? Og vurderingen vår bygges mer og saktere.

Det hele er MVCC sin feil.

Alt handler om MVCC-mekanisme, som får spørringen til å se gjennom alle tidligere versjoner av oppføringen. Så la oss rense bordet vårt fra "døde" versjoner:

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

Å, det er ingenting å rense! Parallell Den kjørende forespørselen forstyrrer oss - han kan tross alt en dag ønske å vende seg til disse versjonene (hva om?), og de burde være tilgjengelige for ham. Og derfor vil heller ikke VAKUUM FULL hjelpe oss.

"Skjuler sammen" bordet

Men vi vet med sikkerhet at den spørringen ikke trenger tabellen vår. Derfor vil vi fortsatt prøve å returnere systemytelsen til tilstrekkelige grenser ved å eliminere alt unødvendig fra tabellen - i det minste "manuelt", siden VACUUM gir opp.

For å gjøre det mer tydelig, la oss se på eksemplet med tilfellet med en buffertabell. Det vil si at det er stor flyt av INSERT/DELETE, og noen ganger er tabellen helt tom. Men hvis det ikke er tomt, må vi lagre det nåværende innholdet.

#0: Vurderer situasjonen

Det er klart at du kan prøve å gjøre noe med tabellen selv etter hver operasjon, men dette gir ikke mye mening - vedlikeholdskostnadene vil helt klart være større enn gjennomstrømningen til målspørringene.

La oss formulere kriteriene - "det er på tide å handle" hvis:

  • VACUUM ble lansert for ganske lenge siden
    Vi forventer stor belastning, så la det være 60 sekunder siden siste [auto]VAKUUM.
  • fysisk bordstørrelse er større enn målet
    La oss definere det som dobbelt så mange sider (8KB blokker) i forhold til minimumsstørrelsen - 1 blk for haug + 1 blk for hver indeks - for et potensielt tomt bord. Hvis vi forventer at en viss mengde data alltid vil forbli i bufferen "normalt", er det rimelig å justere denne formelen.

Bekreftelsesforespørsel

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: Fortsatt VAKUUM

Vi kan ikke på forhånd vite om en parallell spørring forstyrrer oss vesentlig - nøyaktig hvor mange poster som har blitt "utdatert" siden den startet. Derfor, når vi bestemmer oss for å behandle tabellen på en eller annen måte, bør vi i alle fall først kjøre på den VAKUUM - i motsetning til VACUUM FULL, forstyrrer den ikke parallelle prosesser som arbeider med lese-skrive-data.

Samtidig kan den umiddelbart rense ut det meste av det vi ønsker å fjerne. Ja, og påfølgende forespørsler på denne tabellen vil gå til oss av "hot cache", som vil redusere varigheten deres - og dermed den totale tiden for blokkering av andre av vår servicetransaksjon.

#2: Er noen hjemme?

La oss sjekke om det i det hele tatt er noe i tabellen:

TABLE tbl LIMIT 1;

Hvis det ikke er en eneste post igjen, kan vi spare mye på behandlingen ved å kjøre TRUNKER:

Den fungerer på samme måte som en ubetinget DELETE-kommando for hver tabell, men er mye raskere siden den faktisk ikke skanner tabellene. Dessuten frigjør det diskplass umiddelbart, så det er ikke nødvendig å utføre en VACUUM-operasjon etterpå.

Om du trenger å tilbakestille tabellsekvenstelleren (RESTART IDENTITY) er opp til deg å avgjøre.

#3: Alle sammen – bytt på!

Siden vi jobber i et svært konkurranseutsatt miljø, mens vi er her og sjekker at det ikke er noen oppføringer i tabellen, kan noen allerede ha skrevet noe der. Vi bør ikke miste denne informasjonen, så hva? Det er riktig, vi må sørge for at ingen kan skrive det ned sikkert.

For å gjøre dette må vi aktivere SERIALISERBAR-isolasjon for transaksjonen vår (ja, her starter vi en transaksjon) og lås bordet "tett":

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

Dette blokkeringsnivået bestemmes av operasjonene vi ønsker å utføre på det.

#4: Interessekonflikt

Vi kommer hit og vil "låse" skiltet - hva om noen var aktive på det i det øyeblikket, for eksempel og leste fra det? Vi vil "henge" og vente på at denne blokken skal slippes, og andre som vil lese vil støte på oss...

For å forhindre at dette skjer, vil vi "ofre oss selv" - hvis vi ikke var i stand til å få en lås innen en viss (akseptabelt kort) tid, vil vi motta et unntak fra basen, men vi vil i det minste ikke forstyrre for mye med andre.

For å gjøre dette, angi øktvariabelen lock_timeout (for versjoner 9.3+) eller/og statement_timeout. Det viktigste å huske er at statement_timeout-verdien kun gjelder fra neste setning. Det vil si slik i liming - vil ikke fungere: 

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

For ikke å måtte håndtere å gjenopprette den "gamle" verdien til variabelen senere, bruker vi skjemaet SET LOKALT, som begrenser omfanget av innstillingen til gjeldende transaksjon.

Vi husker at statement_timeout gjelder for alle påfølgende forespørsler slik at transaksjonen ikke kan strekke seg til uakseptable verdier hvis det er mye data i tabellen.

#5: Kopier data

Hvis tabellen ikke er helt tom, må dataene lagres på nytt ved hjelp av en midlertidig hjelpetabell:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatur ON COMMIT DROP betyr at i det øyeblikket transaksjonen avsluttes, vil den midlertidige tabellen slutte å eksistere, og det er ikke nødvendig å slette den manuelt i tilkoblingskonteksten.

Siden vi antar at det ikke er mye "live" data, bør denne operasjonen skje ganske raskt.

Vel, det er alt! Ikke glem etter å ha fullført transaksjonen kjør ANALYSE for å normalisere tabellstatistikk om nødvendig.

Setter sammen det endelige manuset

Vi bruker denne "pseudo-pyton":

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

Er det mulig å ikke kopiere dataene en gang til?I prinsippet er det mulig hvis selve bordets oid ikke er knyttet til andre aktiviteter fra BL-siden eller FK fra DB-siden:

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

La oss kjøre skriptet på kildetabellen og sjekke beregningene:

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

Alt ordnet seg! Tabellen har krympet med 50 ganger og alle OPPDATERINGER kjører raskt igjen.

Kilde: www.habr.com

Legg til en kommentar