ProHoster > Blogs > Administrācija > Kad VACUUM neizdodas, mēs tīrām galdu manuāli

Kad VACUUM neizdodas, mēs tīrām galdu manuāli

VAKUUMS var “iztīrīt” no tabulas PostgreSQL tikai ko neviens nevar redzēt - tas ir, nav neviena aktīva pieprasījuma, kas sākts pirms šo ierakstu maiņas.

Bet ko darīt, ja šāds nepatīkams veids (ilgtermiņa OLAP slodze OLTP datu bāzē) joprojām pastāv? Kā tīrīt aktīvi maināmo galdu garu vaicājumu ieskauts un neuzkāpt uz grābekļa?

Kad VACUUM neizdodas, mēs tīrām galdu manuāli

Grābekļa atlocīšana

Vispirms noskaidrosim, kāda ir problēma, kuru vēlamies atrisināt, un kā tā var rasties.

Parasti šāda situācija notiek uz salīdzinoši maza galda, bet kurā tas notiek daudz izmaiņu. Parasti tas vai savādāk metri/agregāti/vērtējumi, kurā bieži tiek izpildīts UPDATE, vai bufera rinda lai apstrādātu kādu pastāvīgi notiekošu notikumu plūsmu, kuras ieraksti tiek pastāvīgi INSERT/DELETE.

Mēģināsim reproducēt opciju ar vērtējumiem:

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;

Un paralēli citā saistībā sākas garš, garš pieprasījums, vācot kaut kādu sarežģītu statistiku, bet neietekmējot mūsu galdu:

SELECT pg_sleep(10000);

Tagad mēs daudzas, daudzas reizes atjauninām viena skaitītāja vērtību. Eksperimenta tīrības labad darīsim tā atsevišķos darījumos, izmantojot dblinkkā tas notiks patiesībā:

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

Kas notika? Kāpēc pat par vienkāršāko viena ieraksta ATJAUNINĀJUMU izpildes laiks samazināts 7 reizes — no 0.524 ms līdz 3.808 ms? Un mūsu reitings veidojas arvien lēnāk.

Tā visa ir MVCC vaina.

Tas viss ir par MVCC mehānisms, kas liek vaicājumam izskatīt visas iepriekšējās ieraksta versijas. Tātad, attīrīsim galdu no “mirušajām” versijām:

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

Ak, nav ko tīrīt! Paralēli Darbojas pieprasījums traucē mums - galu galā viņš var kādreiz vēlēties pievērsties šīm versijām (ja nu?), un tām vajadzētu būt viņam pieejamām. Un tāpēc mums nepalīdzēs pat VACUUM FULL.

Tabulas “sabrukšana”.

Taču mēs noteikti zinām, ka šim vaicājumam mūsu tabula nav nepieciešama. Tāpēc mēs joprojām mēģināsim atgriezt sistēmas veiktspēju līdz atbilstošām robežām, no tabulas noņemot visu nevajadzīgo - vismaz “manuāli”, jo VACUUM atsakās.

Lai padarītu to skaidrāku, apskatīsim bufera tabulas gadījuma piemēru. Tas ir, ir liela INSERT/DELETE plūsma, un dažreiz tabula ir pilnīgi tukša. Bet, ja tas nav tukšs, mums tas ir jādara saglabājiet tā pašreizējo saturu.

#0: situācijas novērtēšana

Ir skaidrs, ka jūs varat mēģināt kaut ko darīt ar tabulu pat pēc katras darbības, taču tam nav lielas jēgas - uzturēšanas izmaksas noteikti būs lielākas nekā mērķa vaicājumu caurlaidspēja.

Formulēsim kritērijus – “ir laiks rīkoties”, ja:

  • VACUUM tika palaists diezgan sen
    Sagaidām lielu slodzi, lai tā būtu 60 sekundes kopš pēdējā [auto]VAKUUMS.
  • fiziskais tabulas izmērs ir lielāks par mērķi
    Definēsim to kā divreiz lielāku lappušu skaitu (8 KB blokus) attiecībā pret minimālo izmēru - 1 blk par kaudzi + 1 blk par katru indeksu - potenciāli tukšam galdam. Ja mēs sagaidām, ka noteikts datu apjoms vienmēr paliks buferī “parasti”, ir saprātīgi pielāgot šo formulu.

Pārbaudes pieprasījums

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: joprojām VAKUUMS

Mēs nevaram iepriekš zināt, vai paralēlais vaicājums būtiski traucē mums — tieši cik ierakstu ir kļuvuši “novecojuši” kopš tā sākuma. Tāpēc, kad mēs nolemjam kaut kā apstrādāt tabulu, mums jebkurā gadījumā vispirms jāizpilda tajā VAKUUMS - atšķirībā no VACUUM FULL, tas netraucē paralēliem procesiem, kas strādā ar lasīšanas un rakstīšanas datiem.

Tajā pašā laikā tas var nekavējoties notīrīt lielāko daļu no tā, ko mēs vēlētos noņemt. Jā, un turpmākie vaicājumi par šo tabulu tiks nosūtīti mums ar "hot cache", kas samazinās to ilgumu un līdz ar to arī kopējo laiku, kad mūsu apkalpošanas darījums bloķē citus.

#2: Vai kāds ir mājās?

Pārbaudīsim, vai tabulā vispār ir kaut kas:

TABLE tbl LIMIT 1;

Ja nav palicis neviens ieraksts, mēs varam daudz ietaupīt uz apstrādi, vienkārši to darot TRUNCĀT:

Tā darbojas tāpat kā beznosacījumu DELETE komanda katrai tabulai, taču ir daudz ātrāka, jo tā faktiski neskenē tabulas. Turklāt tas nekavējoties atbrīvo vietu diskā, tāpēc pēc tam nav nepieciešams veikt VACUUM darbību.

Tas, vai ir jāatiestata tabulas secības skaitītājs (RESTART IDENTITY), ir jūsu ziņā.

#3: Visi - pārmaiņus!

Tā kā mēs strādājam ļoti konkurences apstākļos, kamēr mēs šeit pārbaudām, vai tabulā nav ierakstu, kāds jau varēja tur kaut ko ierakstīt. Mums nevajadzētu pazaudēt šo informāciju, ko tad? Tieši tā, mums ir jāpārliecinās, ka neviens to nevar droši pierakstīt.

Lai to izdarītu, mums ir jāiespējo SERIALIZĒJAMS- mūsu darījuma izolēšana (jā, šeit mēs sākam darījumu) un "cieši" bloķējiet tabulu:

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

Šo bloķēšanas līmeni nosaka darbības, kuras mēs ar to vēlamies veikt.

#4: interešu konflikts

Mēs atnākam šeit un gribam “aizslēgt” zīmi - ja nu kāds tajā brīdī būtu aktīvs uz tās, piemēram, lasītu no tās? Mēs “pakārtēsim” gaidot, kad šis bloks tiks atbrīvots, un citi lasītgribētāji ieskrienas mums...

Lai tas nenotiktu, “ziedosim sevi” - ja nevarējām dabūt slēdzeni noteiktā (pieļaujami īsā) laikā, tad saņemsim izņēmumu no bāzes, bet vismaz pārāk neiejauksim. citi.

Lai to izdarītu, iestatiet sesijas mainīgo lock_timeout (versijai 9.3+) vai/un paziņojums_noildze. Galvenais, kas jāatceras, ir tas, ka Stateout_timeout vērtība tiek piemērota tikai no nākamā priekšraksta. Tas ir, piemēram, līmējot - nedarbosies: 

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

Lai vēlāk nebūtu jānodarbojas ar mainīgā “vecās” vērtības atjaunošanu, mēs izmantojam formu IESTATĪT LOCAL, kas ierobežo iestatījuma darbības jomu ar pašreizējo darījumu.

Mēs atceramies, ka statement_timeout attiecas uz visiem nākamajiem pieprasījumiem, lai darījums nevarētu izstiepties līdz nepieņemamām vērtībām, ja tabulā ir daudz datu.

#5: kopējiet datus

Ja tabula nav pilnībā tukša, dati būs atkārtoti jāsaglabā, izmantojot pagaidu palīgtabulu:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Paraksts PAR PIEMĒROŠANU nozīmē, ka brīdī, kad transakcija beidzas, pagaidu tabula beigs pastāvēt un savienojuma kontekstā tā nav manuāli jādzēš.

Tā kā mēs pieņemam, ka "dzīvu" datu nav daudz, šai darbībai vajadzētu notikt diezgan ātri.

Nu tas arī viss! Neaizmirstiet pēc darījuma pabeigšanas palaist ANALĪZI lai normalizētu tabulas statistiku, ja nepieciešams.

Galīgā scenārija salikšana

Mēs izmantojam šo "pseidopitonu":

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

Vai ir iespējams otrreiz nekopēt datus?Principā tas ir iespējams, ja pats tabulas oid nav piesaistīts citām aktivitātēm no BL puses vai FK no DB puses:

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

Palaidīsim skriptu avota tabulā un pārbaudīsim metriku:

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

Viss izdevās! Tabula ir samazinājusies par 50 reizēm, un visi ATJAUNINĀJUMI atkal darbojas ātri.

Avots: www.habr.com

Pievieno komentāru