ProHoster > Blog > Administrazioa > HUTSAK huts egiten duenean, mahaia eskuz garbitzen dugu

HUTSAK huts egiten duenean, mahaia eskuz garbitzen dugu

hutsean PostgreSQL-ko taula batetik "garbitu" daiteke zer bakarrik inork ezin du ikusi - hau da, ez dago erregistro horiek aldatu aurretik hasitako eskaera aktibo bakar bat ere.

Baina zer gertatzen da horrelako mota desatsegina (epe luzerako OLAP karga OLTP datu-base batean) oraindik existitzen bada? Nola garbitu aktiboki aldatzeko mahaia Kontsulta luzez inguratuta eta ez arrastela zapaldu?

HUTSAK huts egiten duenean, mahaia eskuz garbitzen dugu

Arrastelua jartzen

Lehenik eta behin, zehaztu dezagun zein den konpondu nahi dugun arazoa eta nola sor daitekeen.

Normalean egoera hau gertatzen da mahai txiki samarrean, baina bertan gertatzen den aldaketa asko. Normalean hau edo ezberdina metro/agregatuak/kalifikazioak, UPDATE askotan exekutatzen den, edo buffer-ilara etengabe abian diren gertaeren jario batzuk prozesatzeko, horien erregistroak etengabe txertatu/ezabatu.

Saia gaitezen aukera balorazioekin erreproduzitzen:

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;

Eta paraleloan, beste lotura batean, eskaera luze-luze bat hasten da, estatistika konplexu batzuk biltzen, baina gure mahaian eraginik ez:

SELECT pg_sleep(10000);

Orain kontagailuetako baten balioa askotan eta askotan eguneratzen dugu. Esperimentuaren garbitasunagatik, egin dezagun hau transakzio bereizietan dblink erabiliznola gertatuko den errealitatean:

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

Zer gertatu da? Zergatik baita erregistro bakar baten EGUNERATZEA errazenerako ere exekuzio denbora 7 aldiz murriztu da — 0.524 ms-tik 3.808 ms-ra? Eta gure balorazioa gero eta polikiago ari da eraikitzen.

MVCCren errua da guztia.

Guztia da MVCC mekanismoa, eta horrek sarreraren aurreko bertsio guztiak aztertzea eragiten du. Beraz, garbi dezagun gure taula bertsio "hildakoetatik":

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

Ai, ez dago ezer garbitzeko! Paraleloa Korrika eskaera oztopatzen ari zaigu - azken finean, baliteke noizbait bertsio horietara jo nahi izatea (zer gertatuko balitz?), eta eskura egon beharko lukete. Eta, hortaz, VACUUM FULL-ek ere ez digu lagunduko.

Mahaia "kolapsatzen".

Baina ziur badakigu kontsulta horrek ez duela gure taularen beharrik. Hori dela eta, sistemaren errendimendua muga egokietara itzultzen saiatuko gara mahaitik beharrezkoa ez den guztia kenduz, gutxienez "eskuz", VACUUMek amore ematen baitu.

Argiago geratzeko, ikus dezagun buffer taula baten kasuaren adibidea. Hau da, INSERT/DELETE fluxu handia dago, eta batzuetan taula guztiz hutsik dago. Baina hutsik ez badago, behar dugu gorde bere uneko edukia.

#0: Egoera ebaluatzea

Argi dago eragiketa bakoitzaren ondoren ere taularekin zerbait egiten saia zaitezkeela, baina horrek ez du zentzu handirik - mantentze-kostuak helburuko kontsulten errendimendua baino handiagoa izango da argi eta garbi.

Formulatu ditzagun irizpideak - "jarduteko garaia da" baldin:

  • VACUUM duela dezente jarri zen martxan
    Zama astuna espero dugu, beraz, utzi 60 segundo azken [auto]HURTSALA geroztik.
  • taula fisikoaren tamaina helburua baino handiagoa da
    Definitu dezagun gutxieneko tamainaren aldean orrialde kopuruaren bikoitza (8KB bloke) - 1 blk pilarako + 1 blk indize bakoitzeko - hutsik egon daitekeen mahai baterako. Datu kopuru bat beti bufferean "normalean" geratuko dela espero badugu, zentzuzkoa da formula hau doitzea.

Egiaztapen eskaera

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: Oraindik HUTSEAN

Ezin dugu aldez aurretik jakin kontsulta paralelo batek nabarmen oztopatzen gaituen ala ez; zehazki zenbat erregistro "zaharkitu" diren hasi zenetik. Hori dela eta, taula nolabait prozesatzea erabakitzen dugunean, edonola ere, lehenik eta behin exekutatu beharko genuke hutsean - VACUUM FULL ez bezala, ez du oztopatzen irakurketa-idazketa datuekin lan egiten duten prozesu paraleloetan.

Aldi berean, berehala garbitu dezake kendu nahi genukeen gehiena. Bai, eta taula honetako ondorengo kontsultak gurera joango dira "hot cache" arabera, eta horrek iraupena murriztuko du - eta, beraz, gure zerbitzu-transakzioaren bidez beste batzuk blokeatzeko denbora osoa.

#2: Inor al dago etxean?

Ikus dezagun taulan ezer dagoen:

TABLE tbl LIMIT 1;

Erregistro bakar bat ere geratzen ez bada, prozesatzen asko aurreztu dezakegu, besterik gabe, exekutatuta TRONKATUA:

Taula bakoitzeko baldintzarik gabeko DELETE komando baten berdina jokatzen du, baina askoz azkarragoa da, ez baititu taulak eskaneatzen. Gainera, berehala askatzen du diskoko lekua, eta, beraz, ez da ondoren HUTSEAN eragiketarik egin beharrik.

Taularen sekuentzia-kontagailua (BERRASI IDENTITATEA) berrezarri behar duzun ala ez zure esku dago.

#3: Denok - txandaka!

Lehiakortasun handiko ingurune batean lan egiten dugunez, hemen taulan sarrerarik ez dagoela egiaztatzen gauden bitartean, norbaitek dagoeneko idatzi izan zuen zerbait han. Ez dugu informazio hori galdu behar, zer? Hori bai, ziurtatu behar dugu inork ezin duela idatzi seguru.

Horretarako gaitu behar dugu SERIATZEA-Gure transakziorako isolatzea (bai, hemen transakzio bat hasten dugu) eta blokeatu mahaia "ondo":

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

Blokeo maila hori bertan egin nahi ditugun eragiketen arabera zehazten da.

#4: Interes-gatazka

Hona etorri eta seinalea "blokeatu" nahi dugu - eta une horretan norbait aktibo egongo balitz, adibidez, irakurtzen? Bloke hau noiz aterako zain “zintzilik” egongo gara, eta irakurri nahi duten beste batzuk gurekin topo egingo dute...

Hori gerta ez dadin, "geure burua sakrifikatuko dugu" - denbora jakin batean (onargarri laburrean) ezin izan bagenu blokeoa lortu, orduan oinarritik salbuespen bat jasoko dugu, baina gutxienez ez dugu gehiegi oztopatuko. beste batzuk.

Horretarako, ezarri saioaren aldagaia lock_timeout (9.3+ bertsioetarako) edo/eta adierazpena_denbora-muga. Gogoratu beharreko gauza nagusia da statement_timeout balioa hurrengo adierazpenetik soilik aplikatzen dela. Hau da, horrela itsasten - ez du funtzionatuko: 

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

Geroago aldagaiaren balio “zaharra” berreskuratzeari aurre egin beharrik ez izateko, formularioa erabiltzen dugu TOKIKOA EZARTU, ezarpenaren esparrua uneko transakziora mugatzen duena.

Gogoratzen dugu statement_timeout ondorengo eskaera guztiei aplikatzen zaiela, transakzioa ezin dadin balio onartezinetara luzatu taulan datu asko baldin badaude.

#5: Kopiatu datuak

Taula guztiz hutsik ez badago, datuak berriro gorde beharko dira aldi baterako taula laguntzaile baten bidez:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Sinadura KONPROMISO JAITSITZEAN esan nahi du transakzioa amaitzen den unean, behin-behineko taulak existitzeari utziko diola, eta ez dagoela konexio testuinguruan eskuz ezabatu beharrik.

Datu "zuzeneko" asko ez dagoela suposatzen dugunez, eragiketa hau nahiko azkar egin beharko litzateke.

Tira, hori da dena! Ez ahaztu transakzioa amaitu ondoren exekutatu AZTERTU behar izanez gero, taula estatistikak normalizatzeko.

Azken gidoia batzen

"pseudo-python" hau erabiltzen dugu:

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

Posible al da datuak bigarren aldiz ez kopiatzea?Printzipioz, posible da taularen beraren oida BL aldetik edo DB aldetik FK beste jarduerekin lotuta ez badago:

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

Exekutatu dezagun script-a iturburu-taulan eta egiaztatu neurketak:

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

Dena ondo atera zen! Taula 50 aldiz murriztu da eta UPDATE guztiak azkar ari dira martxan berriro.

Iturria: www.habr.com

Gehitu iruzkin berria