ProHoster > Блог > administratio > Cum deficit VACUUM, mensam manually purgamus

Cum deficit VACUUM, mensam manually purgamus

VACUUM potest "mundare" ex mensa in PostgreSQL solum quid " nemo potest videre — hoc est, non una rogatio activa, quae ante hosce monumenta mutata sunt.

Sed quid si adhuc exstat tam ingratum genus (longum tempus OLAP onere datorum OLTP)? Quam clean actively mutantur in mensa cincta diu queries, non rastro calcari?

Cum deficit VACUUM, mensam manually purgamus

Ponens in sarculo

Primum, quid sit problema, quod solvere volumus, et quomodo oriri possit, determinemus.

Plerumque hoc situ accidit in comparatione parva mensased in quo fit multum mutationes. Plerumque hoc vel diversum metris / aggregat / ratings, in quo UPDATE saepe supplicium est , vel buffer-queue ad aliquid processum constanter perennem rerum cursum, quarum monumenta constanter INDO/DELETE.

Optionem cum ratings effingere experimentum:

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;

Et in parallelis, in alio connexione, petitio longa et longa incipit, statistica quaedam complexa colligens, sed non pertinet ad mensam nostram:

SELECT pg_sleep(10000);

Nunc renovamus valorem unius calculi multi, pluries. Pro puritate experimenti hoc faciamus per singulas res per dblinkquomodo fiet in re;

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

Quid accidit? Cur vel simplicissimae UPDATE unius record? supplicium tempus abjecta VII temporibus - ab 0.524ms ad 3.808ms? Nostra aestimatio plus ac tardius aedificat.

MVCC Tota culpa est.

Actum est de MVCC mechanismqui interrogationem facit ut per omnes priores versiones introitus inspiciat. Mundamus ergo mensam nostram a versionibus "mortuis":

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

O mundare nihil est! Parallel Currens petitio impedit nos — tamen, aliquando ad has versiones converti velit (quid si?), eique praesto sint. Quam ob rem etiam VACUUM PLENA nos adiuvabit.

"Collapsa" mensa

Sed certo scimus hanc interrogationem nostram tabulam non egere. Ideo adhuc conabimur reddere rationem observantiam ad sufficientes limites tollendo omnia quae superflua sunt e tabula, saltem "manualiter", cum VACUUM desinat.

Quo ut clarius pateat, exemplum causae tabulae quiddam inspiciamus. Hoc est, magna fluxus INDO/DELETE est, et interdum mensa omnino vacua est. At si vacua non est, debemus nisi ad current contenta.

# 0, perpensis situ

Patet te etiam post quamque operationem aliquid facere conari posse, sed hoc non multum sensum - sustentationem capitis evidenter maior erit quam per scopo quaestionis.

Criteria proponere - "tempus est agere" si:

  • VACUUM satis diu ante
    Expectamus onus grave, sic fiat 60 seconds cum ultima [auto]VACUUM.
  • corporis mensa mole est maior quam scopum
    Illud definiamus ut bis numerus paginarum (8KB caudices) respectu minimae magnitudinis - 1 blk ad acervum + 1 blk pro indice sulum — ad mensam in potentia vacua. Si speramus aliquas notitiarum quantitates semper in quiddam "normaliter" manere, rationabile est hanc formulam duplicare.

Comprobatio petitionem

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: Still VACUUM

Praenoscere non possumus utrum quaestio parallela signanter nos impediat - quot prorsus monumenta "ex tempore" facta sunt cum incepit. Cum igitur aliquo casu aliquid decernimus ad mensam processus, primum in ea exequamur VACUUM - DISSIMILITAS VACUUM PLENA, non impedit processibus parallelis laborantibus cum notitia legere-scribe.

Simul, statim maxime emundare potest quod removere velimus. Ita, et interrogationes subsequentes de hac tabula ad nos pergent per "calidi cache"quae eorum durationem reducet - atque adeo totum tempus impediendi aliis negotiis nostris gerendis.

#2: Ecquis est domi?

Compesce si quid in mensa omnino est;

TABLE tbl LIMIT 1;

Si relictum non est ullum, tunc multum in processu simpliciter currit servare possumus truncata:

Idem agit ac sine exceptione mandatum singulis tabulis delere, sed multo velocius cum tabulas actu non lustrat. Praeterea spatium orbis statim liberat, ut opus vacuum postea operationi praestare non possit.

Num opus est ut sequentia tabulae reset (RESUMI IDENTITY) ad vos statuendum est.

#3: Unusquisque - vices!

Cum in ambitu maxime competitive laboramus, dum hic inhibemus quod nulla viscus in tabula est, iam aliquis aliquid ibi scripsit. Haec indicia non perdamus, quid ergo? Ius est, opus fac ut nemo eam pro certo scribere possit.

Hoc facere oportet ut Serializable'-isolatio pro transactione nostra (sic, hic incipimus transactionem) et mensam "arte" claudite;

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

Hic gradus interclusionis determinatur per operationes quas in eo praestare volumus.

# IV: conflictus interest

Venimus huc et signum "cincinre" volumus - quid si quis tunc temporis in eo ageret, exempli gratia, ex eo legere? Nos "suspendere" exspectantes hunc scandalum dimittendum, et alii qui legere volunt in nos incurrent.

Quod ut ne fiat, "sibi sacrificabimus" - si crinem intra aliquod (acceptibiliter breve) tempus obtinere nequivimus, exceptionem a basi recipiemus, at certe nimium non impediemus. alii.

Ad hoc faciendum, pone sessionem variabilem lock_timeout (pro versionibus 9.3+) vel/and statement_timeout. Summa meminisse est valorem propositionis_timeout solum ex altera enuntiatione valere. Hoc est, sic in gluten - non opus: 

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

Ut non sit agendum de restauratione valoris "vetus" variabilis postea, forma utimur SET LOCORUMqui ambitum hodiernam transactionem limitat.

Meminimus enuntiationem_timere omnibus postulationibus subsequentibus applicari ita ut res ad valores ingratas extendi non possit, si in mensa multum est notitiae.

#5: Exemplar data

Si mensa vacua non sit, notitia reficienda erit utens mensa auxiliaria temporaria;

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Subscriptio DE COMMENDO STILLO significat quod, momento negotii terminandi, mensa temporalis exstare desinet, neque opus est manuale delere in contextu sermonis.

Cum supponatur non multum data "vivere", haec operatio cito fieri debet.

Bene omnia sunt! Noli oblivisci peracta re currere ENODO ad mensam mutant si normalize necesse est.

Collatis finalis scriptor

Hoc "pseudo-python" utimur.

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

Licetne non iterum datas exscribere?Principio, fieri potest, si oid ipsius mensae nullis aliis actionibus ex BL latere vel FK a latere DB ligatur;

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

Curramus scriptum in mensa fonte et metricos deprime;

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

Omnia elaborata! Mensa resiluit 50 vicibus et omnes UPDATEs iterum celeriter currunt.

Source: www.habr.com

Add a comment