ProHoster > Blog > башкаруу > ВАКУУМ иштебей калганда үстөлдү кол менен тазалайбыз

ВАКУУМ иштебей калганда үстөлдү кол менен тазалайбыз

боштук PostgreSQLдеги таблицадан эмнени гана "тазалай" алат эч ким көрө албайт - башкача айтканда, бул жазуулар өзгөртүлгөнгө чейин башталган бир дагы активдүү өтүнүч жок.

Бирок мындай жагымсыз түрү (OLTP маалымат базасына узак мөөнөттүү OLAP жүктөө) дагы деле бар болсочы? Кантип активдүү алмаштыруучу үстөлдү тазалоо узак суроолор менен курчалган жана тырмоо басып эмес?

ВАКУУМ иштебей калганда үстөлдү кол менен тазалайбыз

Тырмоону ачуу

Биринчиден, биз чечкибиз келген көйгөй эмне экенин жана ал кандайча пайда болушу мүмкүн экенин аныктап алалы.

Адатта мындай абал болот салыштырмалуу кичинекей столдун үстүндө, бирок кайсы жерде пайда болот көп өзгөрүүлөр. Көбүнчө бул же башкача метр/агрегаттар/рейтинг, UPDATE көбүнчө аткарылат, же буфер-кезеги кээ бир тынымсыз уланып жаткан окуялардын агымын иштетүү, алардын жазуулары дайыма INSERT/DELETE.

Келгиле, опцияны рейтинг менен кайра чыгарууга аракет кылалы:

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.524 мсден 3.808 мсге чейин? Ал эми биздин рейтинг барган сайын жай түзүлүп жатат.

Мунун баарына 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 берет.

Аны айкыныраак кылуу үчүн, келгиле, буфердик столдун мисалын карап көрөлү. Башкача айтканда, INSERT/DELETE чоң агымы бар, кээде таблица толугу менен бош болот. Бирок бош болбосо керек анын учурдагы мазмунун сактоо.

№0: кырдаалды баалоо

Ар бир операциядан кийин да үстөл менен бир нерсе жасоого аракет кылсаңыз болору түшүнүктүү, бирок бул анча деле мааниге ээ эмес - тейлөө боюнча кошумча чыгымдар максаттуу суроо-талаптардын өткөрүү жөндөмдүүлүгүнөн көбүрөөк болот.

Келгиле, критерийлерди түзөлү - "иш-аракет кылууга убакыт келди", эгерде:

  • ВАКУУМ көп убакыт мурун ишке киргизилген
    Биз оор жүк күтөбүз, ошондой болсун 60 секунд акыркы [авто]ВАКУУМдан бери.
  • физикалык столдун өлчөмү максаттуу караганда чоңураак
    Аны минималдуу өлчөмгө салыштырмалуу эки эселенген барактардын саны (8KB блоктору) катары аныктайлы - Үймөк үчүн 1 блк + ар бир индекс үчүн 1 блк - потенциалдуу бош стол үчүн. Эгерде биз белгилүү бир көлөмдөгү маалыматтардын ар дайым буферде “нормалдуу” бойдон кала тургандыгын күтсөк, бул формуланы чыңдоо акылга сыярлык.

Текшерүү өтүнүчү

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ден айырмаланып, ал окуу-жазуу маалыматтары менен иштеген параллелдүү процесстерге тоскоолдук кылбайт.

Ошол эле учурда, ал биз алып салгыбыз келген нерселердин көбүн дароо тазалай алат. Ооба, бул таблицадагы кийинки суроолор бизге келет "ысык кэш" менен, бул алардын узактыгын кыскартат - демек, биздин тейлөө транзакциябыз аркылуу башкаларга бөгөт коюунун жалпы убактысы.

№2: Үйдө бирөө барбы?

Келгиле, таблицада бир нерсе бар-жогун текшерип көрөлү:

TABLE tbl LIMIT 1;

Эгерде бир дагы жазуу калбаса, анда биз жөн гана иштетүү менен иштетүүдө көп нерсени үнөмдөй алабыз ТРУНКАТ:

Ал ар бир таблица үчүн шартсыз DELETE буйругу менен бирдей иштейт, бирок таблицаларды сканерлебегендиктен бир топ ылдамыраак. Мындан тышкары, ал дароо диск мейкиндигин бошотот, андыктан кийин ВАКУМ операциясын жасоонун кереги жок.

Таблица ырааттуулугун эсептегичти баштапкы абалга келтирүү керекпи (RESTART IDENTITY) сиз чечесиз.

# 3: Ар бир адам - ​​кезек менен!

Биз катуу атаандаштык чөйрөсүндө иштегендиктен, биз бул жерде таблицада эч кандай жазуу жок экенин текшерип жатканда, кимдир бирөө ал жерге бир нерсе жазып коюшу мүмкүн. Биз бул маалыматты жоготпошубуз керек, анда эмне болот? Туура, аны эч ким так жаза албасын текшеришибиз керек.

Бул үчүн биз иштетишибиз керек СЕРИЯЛАШТЫРУУЧУ- биздин транзакциябыз үчүн изоляция (ооба, бул жерде биз транзакцияны баштайбыз) жана үстөлдү "бекем" бекитүү:

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

Бөгөттөөнүн бул деңгээли биз ага аткаргыбыз келген операциялар менен аныкталат.

№4: Кызыкчылыктардын кагылышы

Биз бул жерге келип, белгини "кулпулоону" каалайбыз - эгерде ошол учурда кимдир бирөө анда активдүү болсо, мисалы, андан окуп калсачы? Биз бул блоктун чыгышын күтүп "илип" калабыз, ал эми окугусу келгендер бизге чуркашат...

Мунун алдын алуу үчүн, биз "өзүбүздү курмандыкка чалабыз" - эгерде биз белгилүү (кабыл алынарлык кыска) убакыттын ичинде кулпу ала албасак, анда биз базадан өзгөчөлүктү алабыз, бирок, жок эле дегенде, биз көп кийлигишпейбиз. башкалар.

Бул үчүн, сеанс өзгөрмөсүн орнотуңуз lock_timeout (9.3+ версиялары үчүн) же/жана билдирүү_тайм. Эсте турган негизги нерсе - statement_timeout мааниси кийинки билдирүүдөн гана колдонулат. Башкача айтканда, чаптоодо ушундай - иштебейт: 

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

Кийинчерээк өзгөрмөнүн "эски" маанисин калыбына келтирбөө үчүн форманы колдонобуз ЖЕРГИЛИКТҮҮ КОЮ, бул орнотуунун көлөмүн учурдагы транзакцияга чектейт.

Эгерде таблицада көп маалыматтар бар болсо, транзакция кабыл алынгыс маанилерге чейин созулушу үчүн, statement_timeout бардык кийинки суроо-талаптарга тиешелүү экенин унутпайбыз.

#5: Маалыматтарды көчүрүү

Эгерде таблица толугу менен бош болбосо, анда маалыматтар көмөкчү убактылуу таблица аркылуу кайра сакталышы керек болот:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Кол коюу ON COMMIT Drop транзакциянын аягында убактылуу таблица өз ишин токтотот жана туташуунун контекстинде аны кол менен жок кылуунун кереги жок дегенди билдирет.

Биз "тирүү" маалыматтар көп эмес деп ойлогондуктан, бул операция абдан тез болушу керек.

Ооба, ушунча! Транзакцияны аяктагандан кийин унутпаңыз 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;

Маалыматты экинчи жолу көчүрбөө мүмкүнбү?Негизи, эгерде үстөлдүн өзү BL тараптан башка иш-аракеттерге же МБ тараптан FK менен байланышпаса болот:

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 эсе кичирейди жана бардык ЖАҢЫРТУУлар кайра тез иштеп жатат.

Source: www.habr.com

Комментарий кошуу