VACUUM ሲወድቅ, ጠረጴዛውን በእጅ እናጸዳዋለን

VACUUM በ PostgreSQL ውስጥ ካለው ጠረጴዛ ላይ "ማጽዳት" የሚችለው ምን ብቻ ነው። ማንም ማየት አይችልም - ማለትም እነዚህ መዝገቦች ከመቀየሩ በፊት የጀመረ አንድም ንቁ ጥያቄ የለም።

ግን እንደዚህ አይነት ደስ የማይል አይነት (በ OLTP የውሂብ ጎታ ላይ የረዥም ጊዜ የ OLAP ጭነት) አሁንም ካለስ? እንዴት ንጹህ በንቃት የሚቀይር ጠረጴዛ በረጅም መጠይቆች ተከብቦ በሬክ ላይ አይረግጡም?

መሰኪያውን በመዘርጋት ላይ

በመጀመሪያ፣ መፍታት የምንፈልገው ችግር ምን እንደሆነ እና እንዴት ሊፈጠር እንደሚችል እንወስን።

ብዙውን ጊዜ ይህ ሁኔታ ይከሰታል በአንጻራዊ ሁኔታ ትንሽ ጠረጴዛ ላይ, ግን በየትኛው ውስጥ ይከሰታል ብዙ ለውጦች. ብዙውን ጊዜ ይህ ወይም የተለየ ሜትር / ድምር / ደረጃዎች, በዚህ ላይ UPDATE ብዙ ጊዜ የሚፈጸም, ወይም ቋት-ወረፋ አንዳንድ ቀጣይነት ያሉ የክስተቶችን ፍሰት ለማስኬድ፣ መዝገቦቹ ያለማቋረጥ ያስገቡ/ሰርዝ ናቸው።

አማራጩን ከደረጃዎች ጋር ለማባዛት እንሞክር፡-

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

ምን ሆነ? ለምንድነው ለአንድ ነጠላ መዝገብ በጣም ቀላል UPDATE የማስፈጸሚያ ጊዜ በ 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 ሰከንድ ከመጨረሻው [auto] 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 - ከ VACUUM FULL በተለየ ፣ ከተነበበ-ጽሑፍ መረጃ ጋር በሚሰሩ ትይዩ ሂደቶች ላይ ጣልቃ አይገባም።

በተመሳሳይ ጊዜ፣ ልናስወግደው የምንፈልገውን አብዛኞቹን ወዲያውኑ ሊያጸዳ ይችላል። አዎ፣ እና በዚህ ጠረጴዛ ላይ ያሉ ቀጣይ ጥያቄዎች ወደ እኛ ይሄዳሉ በ "ሙቅ መሸጎጫ", ይህም ቆይታቸውን ይቀንሳል - እና ስለዚህ, ሌሎች በእኛ አገልግሎት ግብይት የማገድ ጠቅላላ ጊዜ.

#2፡ ቤት ውስጥ ያለ ሰው አለ?

በሠንጠረዡ ውስጥ ምንም ነገር ካለ እንፈትሽ፡-

TABLE tbl LIMIT 1;

አንድም ሪከርድ ከሌለ በቀላሉ በመስራት ሂደት ላይ ብዙ መቆጠብ እንችላለን መዘርጋት:

ለእያንዳንዱ ሠንጠረዥ እንደ ቅድመ ሁኔታ ከሌለው የሰርዝ ትዕዛዝ ጋር ተመሳሳይ ነው የሚሰራው፣ ነገር ግን በትክክል ሠንጠረዦቹን ስለማይቃኝ በጣም ፈጣን ነው። ከዚህም በላይ ወዲያውኑ የዲስክ ቦታን ያስለቅቃል, ስለዚህ ከዚያ በኋላ የ VACUUM ስራ ማከናወን አያስፈልግም.

የሠንጠረዡን ተከታታይ ቆጣሪ (ዳግም ጀምር መታወቂያ) ዳግም ማስጀመር ያስፈልግዎት እንደሆነ ለመወሰን የእርስዎ ምርጫ ነው።

#3: ሁሉም ሰው - ተራ ይውሰዱ!

እኛ የምንሰራው በጣም ፉክክር ባለበት አካባቢ ስለሆነ፣ እዚህ በሠንጠረዡ ውስጥ ምንም ግቤቶች እንደሌሉ እያጣራን ሳለ አንድ ሰው እዚያ የሆነ ነገር ሊጽፍ ይችል ነበር። ይህንን መረጃ ማጣት የለብንም ፣ ታዲያ ምን? ትክክል ነው ማንም በእርግጠኝነት ሊጽፈው እንደማይችል ማረጋገጥ አለብን።

ይህንን ለማድረግ ማንቃት አለብን SERIALIZABLE- ለግብይታችን ማግለል (አዎ እዚህ ግብይት እንጀምራለን) እና ሰንጠረዡን “በጥብቅ” ቆልፈው፡-

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

ይህ የማገጃ ደረጃ የሚወሰነው በእሱ ላይ ልናከናውናቸው በሚፈልጉት ስራዎች ነው.

# 4: የፍላጎት ግጭት

እዚህ መጥተናል እና ምልክቱን "መቆለፍ" እንፈልጋለን - በዚያን ጊዜ አንድ ሰው በእሱ ላይ ንቁ ሆኖ ቢሠራስ ለምሳሌ ከእሱ ማንበብ? ይህ ብሎክ እስኪፈታ ድረስ "እንጠልጥላለን" እና ሌሎች ማንበብ የሚፈልጉ ወደ እኛ ይሮጣሉ...

ይህ እንዳይሆን “እራሳችንን እንሰዋለን” - በተወሰነ (ተቀባይነት ያለው አጭር) ጊዜ ውስጥ መቆለፊያ ማግኘት ካልቻልን ከመሠረቱ የተለየ ነገር እንቀበላለን ፣ ግን ቢያንስ እኛ ብዙ ጣልቃ አንገባም ሌሎች።

ይህንን ለማድረግ የክፍለ-ጊዜውን ተለዋዋጭ ያዘጋጁ የመቆለፊያ_ጊዜ ማብቂያ (ለ 9.3+ ስሪቶች) ወይም/እና መግለጫ_ጊዜ ማብቂያ. ማስታወስ ያለብን ዋናው ነገር የመግለጫው_ጊዜ ማብቂያ ዋጋ የሚተገበረው ከሚቀጥለው መግለጫ ብቻ ነው። ማለትም ፣ በማጣበቅ ውስጥ እንደዚህ ያለ - አይሰራም:

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

በኋላ ላይ የተለዋዋጭውን "አሮጌ" እሴት ወደነበረበት ለመመለስ ላለመነጋገር, ቅጹን እንጠቀማለን አካባቢያዊ አዘጋጅ, ይህም የቅንጅቱን ወሰን አሁን ባለው ግብይት ላይ ይገድባል.

በሠንጠረዡ ውስጥ ብዙ ውሂብ ካለ ግብይቱ ተቀባይነት ወደሌላቸው እሴቶች መዘርጋት እንዳይችል የመግለጫ_ጊዜ ማብቂያ ሁሉንም ቀጣይ ጥያቄዎችን እንደሚመለከት እናስታውሳለን።

#5፡ ውሂብ ቅዳ

ሠንጠረዡ ሙሉ በሙሉ ባዶ ካልሆነ ውሂቡ ረዳት ጊዜያዊ ሠንጠረዥን በመጠቀም እንደገና መቀመጥ አለበት፡-

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

ፊርማ በ COMMIT DrOP ማለት ግብይቱ በሚያልቅበት ጊዜ, ጊዜያዊ ጠረጴዛው መኖር ያቆማል, እና በግንኙነት አውድ ውስጥ በእጅ መሰረዝ አያስፈልግም.

ብዙ "የቀጥታ" ውሂብ የለም ብለን ስለምናስብ ይህ ክዋኔ በፍጥነት መከናወን አለበት.

ደህና, ያ ብቻ ነው! ግብይቱን ከጨረሱ በኋላ አይርሱ ትንታኔን አሂድ አስፈላጊ ከሆነ የሠንጠረዥ ስታቲስቲክስን መደበኛ ለማድረግ.

የመጨረሻውን ስክሪፕት አንድ ላይ በማጣመር

ይህንን “pseudo-python” እንጠቀማለን፡-

# собираем статистику с таблицы
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 ጎን ወይም ኤፍኬ ከዲቢ በኩል ከሌላ ማንኛውም እንቅስቃሴዎች ጋር ካልተቆራኘ ይቻላል.

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 ጊዜ ቀንሷል እና ሁሉም UPDATEs እንደገና በፍጥነት እየሮጡ ነው።

ምንጭ: hab.com