VACUUM අසමත් වූ විට, අපි මේසය අතින් පිරිසිදු කරන්නෙමු

වකුටු PostgreSQL හි වගුවකින් "පිරිසිදු" කළ හැක්කේ කුමක් ද යන්න පමණි කිසිවෙකුට නොපෙනේ - එනම්, මෙම වාර්තා වෙනස් කිරීමට පෙර ආරම්භ වූ එක සක්රිය ඉල්ලීමක් නොමැත.

නමුත් එවැනි අප්රසන්න වර්ගයක් (OLTP දත්ත ගබඩාවක දිගුකාලීන OLAP පැටවීම) තවමත් පවතී නම් කුමක් කළ යුතුද? කෙසේද ක්රියාකාරීව වෙනස් වන වගුව පිරිසිදු කරන්න දිගු විමසුම් වලින් වට වී ඇති අතර පෝරුවක් මත නොයන්නේද?

පෝරකය තැබීම

පළමුව, අපට විසඳා ගැනීමට අවශ්‍ය ගැටලුව කුමක්ද සහ එය පැන නගින්නේ කෙසේද යන්න තීරණය කරමු.

සාමාන්යයෙන් මෙම තත්ත්වය සිදු වේ සාපේක්ෂව කුඩා මේසයක් මත, නමුත් එය සිදු වන්නේ වෙනස්කම් ගොඩක්. සාමාන්යයෙන් මෙය හෝ වෙනස් මීටර් / සමස්ථ / ශ්රේණිගත කිරීම්, යාවත්කාලීන කිරීම බොහෝ විට ක්‍රියාත්මක වන, හෝ බෆර් පෝලිම නිරන්තරයෙන් සිදුවෙමින් පවතින සිදුවීම් ප්‍රවාහයක් සැකසීමට, ඒවායේ වාර්තා නිරන්තරයෙන් ඇතුළත් කිරීම/මැකීම සිදු කෙරේ.

ශ්‍රේණිගත කිරීම් සමඟ විකල්පය ප්‍රතිනිෂ්පාදනය කිරීමට උත්සාහ කරමු:

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.524ms සිට 3.808ms දක්වා? තවද අපගේ ශ්‍රේණිගත කිරීම වඩ වඩාත් සෙමින් වර්ධනය වේ.

ඒ සියල්ල 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 FULL පවා අපට උදව් නොකරනු ඇත.

මේසය "කඩා වැටීම"

නමුත් එම විමසුමට අපගේ වගුව අවශ්‍ය නොවන බව අපි නිසැකවම දනිමු. එමනිසා, අපි තවමත් මේසයෙන් අනවශ්‍ය සියල්ල ඉවත් කිරීමෙන් පද්ධතියේ ක්‍රියාකාරිත්වය ප්‍රමාණවත් සීමාවන්ට ගෙන ඒමට උත්සාහ කරන්නෙමු - අවම වශයෙන් “අතින්”, VACUUM ලබා දෙන බැවින්.

එය වඩාත් පැහැදිලි කිරීම සඳහා, බෆර් වගුවක නඩුවේ උදාහරණය දෙස බලමු. එනම්, INSERT / DELETE විශාල ප්රවාහයක් ඇති අතර, සමහර විට මේසය සම්පූර්ණයෙන්ම හිස් වේ. නමුත් එය හිස් නොවේ නම්, අපි කළ යුතුයි එහි වත්මන් අන්තර්ගතය සුරකින්න.

#0: තත්ත්වය තක්සේරු කිරීම

එක් එක් මෙහෙයුමෙන් පසුව පවා ඔබට මේසය සමඟ යමක් කිරීමට උත්සාහ කළ හැකි බව පැහැදිලිය, නමුත් මෙය එතරම් තේරුමක් නැත - නඩත්තු පොදු කාර්ය පැහැදිලිවම ඉලක්ක විමසුම්වල ප්‍රතිදානයට වඩා වැඩි වනු ඇත.

අපි නිර්ණායක සකස් කරමු - "ක්‍රියා කිරීමට කාලයයි" නම්:

• VACUUM දියත් කළේ බොහෝ කලකට පෙරය
  අපි අධික බරක් බලාපොරොත්තු වෙමු, එබැවින් එය එසේ වීමට ඉඩ දෙන්න 60 තත්පර අවසන් [ස්වයං]VACUUM සිට.
• භෞතික වගු ප්‍රමාණය ඉලක්කයට වඩා විශාලය
  අපි එය අවම ප්‍රමාණයට සාපේක්ෂව පිටු සංඛ්‍යාව මෙන් දෙගුණයක් (8KB කුට්ටි) ලෙස අර්ථ දක්වමු - ගොඩවල් සඳහා 1 blk + එක් එක් දර්ශකය සඳහා 1 blk - හිස් මේසයක් සඳහා. යම් දත්ත ප්‍රමාණයක් සැමවිටම බෆරයේ “සාමාන්‍යයෙන්” පවතිනු ඇතැයි අපි අපේක්ෂා කරන්නේ නම්, මෙම සූත්‍රය වෙනස් කිරීම සාධාරණ ය.

තහවුරු කිරීමේ ඉල්ලීම

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 විධානයක් ලෙස ක්‍රියා කරයි, නමුත් එය ඇත්ත වශයෙන්ම වගු පරිලෝකනය නොකරන බැවින් වඩා වේගවත් වේ. එපමණක් නොව, එය වහාම තැටි ඉඩ නිදහස් කරයි, එබැවින් පසුව VACUUM මෙහෙයුමක් සිදු කිරීමට අවශ්ය නොවේ.

ඔබට වගු අනුක්‍රමික කවුන්ටරය (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 ...;

පසුව විචල්යයේ "පැරණි" අගය ප්රතිෂ්ඨාපනය කිරීමට කටයුතු නොකිරීමට, අපි පෝරමය භාවිතා කරමු දේශීයව සකසන්න, වත්මන් ගනුදෙනුවට සැකසීමේ විෂය පථය සීමා කරයි.

වගුවේ දත්ත විශාල ප්‍රමාණයක් තිබේ නම්, ගනුදෙනුව පිළිගත නොහැකි අගයන් දක්වා දිගු කිරීමට නොහැකි වන පරිදි ප්‍රකාශ_කාලසීමාව පසුකාලීන සියලුම ඉල්ලීම් සඳහා අදාළ වන බව අපට මතකයි.

#5: දත්ත පිටපත් කරන්න

වගුව සම්පූර්ණයෙන්ම හිස් නොවේ නම්, සහායක තාවකාලික වගුවක් භාවිතයෙන් දත්ත නැවත සුරැකීමට සිදුවේ:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

අත්සන කැපවීම මත එයින් අදහස් වන්නේ ගනුදෙනුව අවසන් වන මොහොතේ තාවකාලික වගුව නොපවතිනු ඇති බවත්, සම්බන්ධතා සන්දර්භය තුළ එය අතින් මකා දැමීමට අවශ්ය නොවන බවත්ය.

"සජීවී" දත්ත ගොඩක් නොමැති බව අපි උපකල්පනය කරන බැවින්, මෙම මෙහෙයුම ඉතා ඉක්මනින් සිදු විය යුතුය.

හොඳයි, එපමණයි! ගනුදෙනුව අවසන් වූ පසු අමතක කරන්න එපා 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 පැත්තෙන් හෝ DB පැත්තෙන් 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 ගුණයකින් හැකිලී ඇති අතර සියලුම යාවත්කාලීන කිරීම් නැවත වේගයෙන් ධාවනය වේ.

මූලාශ්රය: www.habr.com