ProHoster > blog > Gweinyddiaeth > Pan fydd VACUUM yn methu, rydyn ni'n glanhau'r bwrdd â llaw

Pan fydd VACUUM yn methu, rydyn ni'n glanhau'r bwrdd â llaw

VACUUM yn gallu “glanhau” o fwrdd yn PostgreSQL dim ond beth ni all neb weld - hynny yw, nid oes un cais gweithredol a ddechreuodd cyn i'r cofnodion hyn gael eu newid.

Ond beth os yw math mor annymunol (llwyth OLAP hirdymor ar gronfa ddata OLTP) yn dal i fodoli? Sut glanhau bwrdd sy'n newid yn weithredol wedi'i amgylchynu gan ymholiadau hir ac nid cam ar gribin?

Pan fydd VACUUM yn methu, rydyn ni'n glanhau'r bwrdd â llaw

Yn agor y rhaca

Yn gyntaf, gadewch i ni benderfynu beth yw'r broblem yr ydym am ei datrys a sut y gall godi.

Fel arfer mae'r sefyllfa hon yn digwydd ar fwrdd cymharol fach, ond y mae yn digwydd llawer o newidiadau. Fel arfer hyn neu'n wahanol metrau/agregau/graddau, ar ba un y gweithredir DIWEDDARIAD yn aml, neu byffer-ciw prosesu rhai llif parhaus o ddigwyddiadau, y mae cofnodion ohonynt yn cael eu MEWNOSOD/DILEU yn gyson.

Gadewch i ni geisio atgynhyrchu'r opsiwn gyda graddfeydd:

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;

Ac yn gyfochrog, mewn cysylltiad arall, mae cais hir, hir yn dechrau, gan gasglu rhai ystadegau cymhleth, ond ddim yn effeithio ar ein bwrdd:

SELECT pg_sleep(10000);

Nawr rydym yn diweddaru gwerth un o'r cownteri lawer, lawer gwaith. Ar gyfer purdeb yr arbrawf, gadewch i ni wneud hyn mewn trafodion ar wahân gan ddefnyddio dblinksut y bydd yn digwydd mewn gwirionedd:

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

Beth ddigwyddodd? Pam hyd yn oed am y DIWEDDARAF symlaf o un cofnod amser gweithredu wedi'i ddiraddio 7 gwaith — o 0.524ms i 3.808ms? Ac mae ein sgôr yn cynyddu'n fwyfwy araf.

Mae'r cyfan ar fai MVCC.

Mae'n ymwneud Mecanwaith MVCC, sy'n achosi i'r ymholiad edrych trwy holl fersiynau blaenorol y cofnod. Felly gadewch i ni lanhau ein bwrdd o fersiynau “marw”:

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

O, does dim byd i'w lanhau! Cyfochrog Mae'r cais rhedeg yn ymyrryd â ni - wedi'r cyfan, efallai y bydd am droi at y fersiynau hyn (beth os?), a dylent fod ar gael iddo. Ac felly ni fydd hyd yn oed WACUUM LLAWN yn ein helpu.

“Cwympo” y bwrdd

Ond gwyddom yn sicr nad oes angen ein bwrdd ar yr ymholiad hwnnw. Felly, byddwn yn dal i geisio dychwelyd perfformiad y system i derfynau digonol trwy ddileu popeth diangen o'r bwrdd - o leiaf "â llaw", gan fod VACUUM yn rhoi'r gorau iddi.

I'w wneud yn gliriach, gadewch i ni edrych ar yr enghraifft o achos bwrdd clustogi. Hynny yw, mae llif mawr o INSERT/DLETE, ac weithiau mae'r bwrdd yn hollol wag. Ond os nad yw'n wag, rhaid inni arbed ei gynnwys presennol.

#0: Asesu'r sefyllfa

Mae'n amlwg y gallwch chi geisio gwneud rhywbeth gyda'r bwrdd hyd yn oed ar ôl pob llawdriniaeth, ond nid yw hyn yn gwneud llawer o synnwyr - bydd y gorbenion cynnal a chadw yn amlwg yn fwy na thrwygyrch yr ymholiadau targed.

Gadewch i ni lunio’r meini prawf - “mae’n bryd gweithredu” os:

  • Lansiwyd VACUUM amser maith yn ôl
    Rydyn ni'n disgwyl llwyth trwm, felly gadewch iddo fod Eiliadau 60 ers y [auto] VACUUM diwethaf.
  • maint bwrdd corfforol yn fwy na'r targed
    Gadewch i ni ei ddiffinio fel dwywaith nifer y tudalennau (blociau 8KB) o'i gymharu â'r maint lleiaf - 1 blk am domen + 1 blk ar gyfer pob mynegai - ar gyfer bwrdd a allai fod yn wag. Os ydym yn disgwyl y bydd rhywfaint o ddata bob amser yn aros yn y glustog “fel arfer”, mae’n rhesymol tweakio’r fformiwla hon.

Cais dilysu

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: Yn dal yn WAG

Ni allwn wybod ymlaen llaw a yw ymholiad cyfochrog yn ymyrryd yn sylweddol â ni - yn union faint o gofnodion sydd wedi mynd yn “hen ffasiwn” ers iddo ddechrau. Felly, pan fyddwn yn penderfynu prosesu'r tabl rywsut, beth bynnag, dylem ei weithredu arno yn gyntaf VACUUM - yn wahanol i WACUUM LLAWN, nid yw'n ymyrryd â phrosesau cyfochrog sy'n gweithio gyda data darllen-ysgrifennu.

Ar yr un pryd, gall lanhau'r rhan fwyaf o'r hyn yr hoffem ei ddileu ar unwaith. Bydd, a bydd ymholiadau dilynol ar y tabl hwn yn mynd atom ni gan "storfa boeth", a fydd yn lleihau eu hyd - ac, felly, cyfanswm yr amser o rwystro eraill gan ein trafodiad gwasanaethu.

#2: A oes unrhyw un adref?

Gadewch i ni wirio a oes unrhyw beth yn y tabl o gwbl:

TABLE tbl LIMIT 1;

Os nad oes un cofnod ar ôl, yna gallwn arbed llawer ar brosesu trwy wneud yn syml TRYSORFA:

Mae'n gweithredu yr un peth â gorchymyn DELETE diamod ar gyfer pob tabl, ond mae'n llawer cyflymach gan nad yw'n sganio'r tablau mewn gwirionedd. Ar ben hynny, mae'n rhyddhau lle disg ar unwaith, felly nid oes angen cyflawni gweithrediad VACUUM wedyn.

Chi sydd i benderfynu a oes angen ailosod y rhifydd dilyniant tabl (AIL-DDECHRAU HUNANIAETH).

#3: Pawb - cymerwch eich tro!

Gan ein bod yn gweithio mewn amgylchedd hynod gystadleuol, tra ein bod yma yn gwirio nad oes unrhyw gofnodion yn y tabl, gallai rhywun fod wedi ysgrifennu rhywbeth yno eisoes. Ni ddylem golli'r wybodaeth hon, felly beth? Mae hynny'n iawn, mae angen inni wneud yn siŵr na all neb ei ysgrifennu i lawr yn sicr.

I wneud hyn mae angen i ni alluogi CYFRESOGOL-ynysu ar gyfer ein trafodiad (ie, dyma ni'n cychwyn trafodiad) a chloi'r bwrdd yn “dynn”:

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

Mae'r lefel hon o rwystro yn cael ei phennu gan y gweithrediadau yr ydym am eu perfformio arno.

#4: Gwrthdaro buddiannau

Rydyn ni'n dod yma ac eisiau “cloi” yr arwydd - beth os oedd rhywun yn weithgar arno bryd hynny, er enghraifft, yn darllen ohono? Byddwn yn “hongian” yn aros i'r bloc hwn gael ei ryddhau, a bydd eraill sydd eisiau darllen yn rhedeg i mewn i ni ...

Er mwyn atal hyn rhag digwydd, byddwn yn “aberthu ein hunain” - pe na baem yn gallu cael clo o fewn amser penodol (derbyniol o fyr), yna byddwn yn derbyn eithriad o'r sylfaen, ond o leiaf ni fyddwn yn ymyrryd yn ormodol ag ef. eraill.

I wneud hyn, gosodwch y newidyn sesiwn cloi_amser allan (ar gyfer fersiynau 9.3+) neu/a datganiad_amser terfyn. Y prif beth i'w gofio yw mai dim ond o'r datganiad nesaf y mae'r gwerth datganiad_amser terfyn yn berthnasol. Hynny yw, fel hyn mewn gludo - ni fydd yn gweithio: 

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

Er mwyn peidio â gorfod delio ag adfer “hen” werth y newidyn yn ddiweddarach, rydyn ni'n defnyddio'r ffurflen GOSOD LLEOL, sy'n cyfyngu cwmpas y gosodiad i'r trafodiad cyfredol.

Rydym yn cofio bod statement_timeout yn berthnasol i bob cais dilynol fel na all y trafodiad ymestyn i werthoedd annerbyniol os oes llawer o ddata yn y tabl.

#5: Copïo data

Os nad yw'r tabl yn gwbl wag, bydd yn rhaid ail-gadw'r data gan ddefnyddio tabl dros dro ategol:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Llofnod AR YMRWYMIAD GALWAD yn golygu ar hyn o bryd y bydd y trafodiad yn dod i ben, bydd y tabl dros dro yn peidio â bodoli, ac nid oes angen ei ddileu â llaw yn y cyd-destun cysylltiad.

Gan ein bod yn tybio nad oes llawer o ddata “byw”, dylai'r llawdriniaeth hon ddigwydd yn eithaf cyflym.

Wel, dyna i gyd! Peidiwch ag anghofio ar ôl cwblhau'r trafodiad rhedeg DADANSODDIAD i normaleiddio ystadegau tabl os oes angen.

Rhoi'r sgript derfynol at ei gilydd

Rydyn ni'n defnyddio'r “ffug-python” hwn:

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

A yw'n bosibl peidio â chopïo'r data yr eildro?Mewn egwyddor, mae'n bosibl os nad yw oid y bwrdd ei hun yn gysylltiedig ag unrhyw weithgareddau eraill o'r ochr BL neu FK o'r ochr 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;

Gadewch i ni redeg y sgript ar y tabl ffynhonnell a gwirio'r metrigau:

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

Gweithiodd popeth allan! Mae'r tabl wedi crebachu 50 gwaith ac mae'r holl DDIWEDDARAF yn rhedeg yn gyflym eto.

Ffynhonnell: hab.com

Ychwanegu sylw