ProHoster > Блог > Isakoso > Nigbati VACUUM ba kuna, a nu tabili pẹlu ọwọ

Nigbati VACUUM ba kuna, a nu tabili pẹlu ọwọ

ỌRỌ le "sọ di mimọ" lati tabili kan ni PostgreSQL nikan kini ko si eniti o le ri - iyẹn ni, ko si ibeere ti nṣiṣe lọwọ kan ti o bẹrẹ ṣaaju iyipada awọn igbasilẹ wọnyi.

Ṣugbọn kini ti iru iru alaiwu ba wa (ẹru OLAP igba pipẹ lori ibi ipamọ data OLTP) tun wa? Bawo mọ actively iyipada tabili ti yika nipasẹ gun yoowu ti ati ki o ko Akobaratan lori a àwárí?

Nigbati VACUUM ba kuna, a nu tabili pẹlu ọwọ

Laying jade àwárí

Ni akọkọ, jẹ ki a pinnu kini iṣoro ti a fẹ yanju jẹ ati bii o ṣe le dide.

Nigbagbogbo ipo yii n ṣẹlẹ lori kan jo mo kekere tabili, ṣugbọn ninu eyiti o waye ọpọlọpọ awọn ayipada. Nigbagbogbo eyi tabi yatọ mita / aggregates /-wonsi, lori eyiti a ṣe imudojuiwọn nigbagbogbo, tabi saarin-isinyi lati ṣe ilana diẹ ninu awọn ṣiṣan ti nlọ lọwọ nigbagbogbo ti awọn iṣẹlẹ, awọn igbasilẹ eyiti o jẹ FI sii / Paarẹ nigbagbogbo.

Jẹ ki a gbiyanju lati tun ṣe aṣayan pẹlu awọn idiyele:

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;

Ati ni afiwe, ni asopọ miiran, gigun kan, ibeere gigun bẹrẹ, gbigba diẹ ninu awọn iṣiro eka, ṣugbọn ko ni ipa lori tabili wa:

SELECT pg_sleep(10000);

Bayi a ṣe imudojuiwọn iye ti ọkan ninu awọn onka ọpọlọpọ, ọpọlọpọ igba. Fun iwa mimọ ti idanwo, jẹ ki a ṣe eyi ni lọtọ lẹkọ lilo dblinkbawo ni yoo ṣe ṣẹlẹ ni otitọ:

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

Kini o ti ṣẹlẹ? Kini idi paapaa fun imudojuiwọn ti o rọrun julọ ti igbasilẹ kan akoko ipaniyan degraded nipa 7 igba - lati 0.524ms si 3.808ms? Ati pe idiyele wa n kọ diẹ sii ati siwaju sii laiyara.

Gbogbo rẹ jẹ ẹbi MVCC.

O jẹ gbogbo nipa MVCC siseto, eyi ti o mu ki ibeere naa wo nipasẹ gbogbo awọn ẹya ti tẹlẹ ti titẹsi. Nitorinaa jẹ ki a nu tabili wa mọ lati awọn ẹya “okú”:

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

Oh, ko si nkankan lati nu! Ni afiwe Ibere ​​ti nṣiṣẹ ti wa ni kikọlu pẹlu wa - lẹhinna, o le ni ọjọ kan fẹ lati yipada si awọn ẹya wọnyi (kini bi?), Ati pe wọn yẹ ki o wa fun u. Ati nitorinaa paapaa VACUUM FULL kii yoo ran wa lọwọ.

"Ti n ṣubu" tabili

Ṣugbọn a mọ daju pe ibeere yẹn ko nilo tabili wa. Nitorinaa, a yoo tun gbiyanju lati da iṣẹ ṣiṣe eto pada si awọn opin to pe nipa imukuro ohun gbogbo ti ko wulo lati tabili - o kere ju “pẹlu ọwọ”, nitori VACUUM funni ni.

Lati ṣe alaye diẹ sii, jẹ ki a wo apẹẹrẹ ti ọran ti tabili ifipamọ. Iyẹn ni, ṣiṣan nla ti fi sii / PA, ati nigba miiran tabili jẹ ofo patapata. Ṣugbọn ti ko ba ṣofo, a gbọdọ fi awọn oniwe-lọwọlọwọ awọn akoonu ti.

# 0: Iṣiro ipo naa

O han gbangba pe o le gbiyanju lati ṣe ohun kan pẹlu tabili paapaa lẹhin iṣiṣẹ kọọkan, ṣugbọn eyi ko ni oye pupọ - iṣaju itọju yoo han gbangba tobi ju igbejade ti awọn ibeere ibi-afẹde.

Jẹ ki a ṣe agbekalẹ awọn ibeere - “o to akoko lati ṣe” ti o ba jẹ:

  • VACUUM ti ṣe ifilọlẹ ni igba pipẹ sẹhin
    A n reti ẹru wuwo, nitorina jẹ ki o jẹ 60 aaya lati kẹhin [laifọwọyi] VACUUM.
  • iwọn tabili ti ara tobi ju ibi-afẹde lọ
    Jẹ ki a ṣalaye rẹ bi ilọpo meji nọmba awọn oju-iwe (awọn bulọọki 8KB) ni ibatan si iwọn to kere julọ - 1 blk fun okiti + 1 blk fun atọka kọọkan - fun a oyi ṣofo tabili. Ti a ba nireti pe iye data kan yoo wa nigbagbogbo ninu ifipamọ “deede”, o jẹ oye lati tweak agbekalẹ yii.

Ijerisi ìbéèrè

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: Ṣi VACUUM

A ko le mọ tẹlẹ boya ibeere ti o jọra kan n ṣe idalọwọduro pẹlu wa ni pataki - ni deede iye awọn igbasilẹ ti “ti ti ọjọ” lati igba ti o ti bẹrẹ. Nitorinaa, nigba ti a pinnu lati ṣe ilana tabili bakan, ni eyikeyi ọran, o yẹ ki a kọkọ ṣiṣẹ lori rẹ ỌRỌ - ko dabi VACUUM FULL, ko dabaru pẹlu awọn ilana ti o jọra ti n ṣiṣẹ pẹlu data kika-kikọ.

Ni akoko kanna, o le sọ di mimọ pupọ julọ ohun ti a yoo fẹ lati yọ kuro. Bẹẹni, ati awọn ibeere ti o tẹle lori tabili yii yoo lọ si wa nipasẹ "cache gbona", eyiti yoo dinku iye akoko wọn - ati, nitorinaa, akoko lapapọ ti idinamọ awọn miiran nipasẹ iṣowo iṣẹ wa.

#2: Ṣe ẹnikẹni wa ni ile?

Jẹ ki a ṣayẹwo ti ohunkohun ba wa ninu tabili rara:

TABLE tbl LIMIT 1;

Ti ko ba si igbasilẹ kan ti o ku, lẹhinna a le ṣafipamọ pupọ lori sisẹ nipasẹ ṣiṣe nìkan TRANCATE:

O ìgbésẹ kanna bi ohun unconditional Parẹ tabili fun kọọkan tabili, sugbon jẹ Elo yiyara niwon o ko ni kosi ọlọjẹ awọn tabili. Pẹlupẹlu, o yoo gba aaye disk laaye lẹsẹkẹsẹ, nitorinaa ko si iwulo lati ṣe iṣẹ VACUUM kan lẹhinna.

Boya o nilo lati tun counter tabili ṣeto (TTUN IDANỌ) jẹ fun ọ lati pinnu.

# 3: Gbogbo eniyan - ya awọn akoko!

Niwọn bi a ti n ṣiṣẹ ni agbegbe ifigagbaga pupọ, lakoko ti a wa nibi n ṣayẹwo pe ko si awọn titẹ sii ninu tabili, ẹnikan le ti kọ nkan tẹlẹ nibẹ. A ko yẹ ki o padanu alaye yii, nitorina kini? Iyẹn tọ, a nilo lati rii daju pe ko si ẹnikan ti o le kọ silẹ ni idaniloju.

Lati ṣe eyi a nilo lati mu ṣiṣẹ SERIALIZABLE-ipinya fun iṣowo wa (bẹẹni, nibi a bẹrẹ idunadura kan) ati tii tabili “ni wiwọ”:

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

Ipele ìdènà yii jẹ ipinnu nipasẹ awọn iṣẹ ṣiṣe ti a fẹ ṣe lori rẹ.

# 4: Rogbodiyan ti awọn anfani

A wa nibi ati fẹ lati “titiipa” ami naa - kini ti ẹnikan ba ṣiṣẹ lori rẹ ni akoko yẹn, fun apẹẹrẹ, kika lati inu rẹ? A yoo "duro" nduro fun bulọki yii lati tu silẹ, ati pe awọn miiran ti o fẹ ka yoo sare sinu wa…

Lati yago fun eyi lati ṣẹlẹ, a yoo “rubọ ara wa” - ti a ko ba le gba titiipa laarin akoko kan (iwọn itẹwọgba kukuru), lẹhinna a yoo gba imukuro lati ipilẹ, ṣugbọn o kere ju a kii yoo dabaru pupọ pẹlu awon miran.

Lati ṣe eyi, ṣeto oniyipada igba lock_timeout (fun awọn ẹya 9.3+) tabi/ati gbólóhùn_akoko. Ohun akọkọ lati ranti ni pe iye gbólóhùn_timeout nikan kan lati alaye atẹle. Iyẹn ni, bii eyi ni gluing - kii yoo ṣiṣẹ: 

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

Ni ibere ki o má ba ṣe pẹlu mimu-pada sipo iye "atijọ" ti oniyipada nigbamii, a lo fọọmu naa ṢETO IBILE, eyi ti o fi opin si ipari ti eto naa si iṣowo lọwọlọwọ.

A ranti pe gbólóhùn_timeout kan si gbogbo awọn ibeere ti o tẹle ki idunadura naa ko le na isan si awọn iye itẹwẹgba ti data pupọ ba wa ninu tabili.

#5: da data

Ti tabili ko ba ṣofo patapata, data naa yoo ni lati tun-fipamọ ni lilo tabili igba diẹ oluranlowo:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Ibuwọlu LORI IFỌRỌWỌRỌ tumọ si pe ni akoko idunadura naa pari, tabili igba diẹ yoo dẹkun lati wa, ati pe ko si iwulo lati paarẹ pẹlu ọwọ ni ipo asopọ.

Niwọn igba ti a ro pe ko si pupọ data “ifiwe”, iṣẹ yii yẹ ki o waye ni iyara.

O dara, iyẹn ni gbogbo! Maṣe gbagbe lẹhin ipari idunadura naa ṣiṣe ANALYZE lati normalize tabili statistiki ti o ba wulo.

Nfi papo ik akosile

A lo “pseudo-python” yii:

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

Ṣe o ṣee ṣe lati ma daakọ data naa ni akoko keji?Ni opo, o ṣee ṣe ti epo tabili funrararẹ ko ba ni asopọ si awọn iṣẹ miiran lati ẹgbẹ BL tabi FK lati ẹgbẹ 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;

Jẹ ki a ṣiṣẹ iwe afọwọkọ lori tabili orisun ati ṣayẹwo awọn metiriki:

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

Ohun gbogbo ti ṣiṣẹ jade! Tabili naa ti dinku nipasẹ awọn akoko 50 ati pe gbogbo awọn imudojuiwọn nṣiṣẹ ni iyara lẹẹkansi.

orisun: www.habr.com

Fi ọrọìwòye kun