ProHoster > Blog > yönetim > VAKUM başarısız olduğunda masayı manuel olarak temizliyoruz

VAKUM başarısız olduğunda masayı manuel olarak temizliyoruz

VAKUM PostgreSQL'de bir tablodan yalnızca kimse görmez - yani bu kayıtlar değiştirilmeden önce başlamış tek bir aktif istek yoktur.

Peki ya bu kadar hoş olmayan bir tür (OLTP veritabanında uzun vadeli OLAP yükü) hala mevcutsa? Nasıl aktif olarak değişen masayı temizleyin Etrafınız uzun sorularla çevrili ve tırmığa basmıyor musunuz?

VAKUM başarısız olduğunda masayı manuel olarak temizliyoruz

Tırmığın döşenmesi

Öncelikle çözmek istediğimiz sorunun ne olduğunu ve nasıl ortaya çıkabileceğini belirleyelim.

Genelde bu durum oluyor nispeten küçük bir masada, ancak meydana geldiği yer birçok değişiklik. Genellikle bu veya farklı metre/toplamlar/derecelendirmelerUPDATE'in sıklıkla yürütüldüğü veya arabellek kuyruğu kayıtları sürekli olarak INSERT/DELETE olan bazı sürekli devam eden olay akışını işlemek için.

Seçeneği derecelendirmelerle yeniden oluşturmaya çalışalım:

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;

Buna paralel olarak, başka bir bağlantıda, bazı karmaşık istatistikleri toplayan uzun, çok uzun bir istek başlar, ancak masamızı etkilemez:

SELECT pg_sleep(10000);

Artık sayaçlardan birinin değerini defalarca güncelliyoruz. Deneyin saflığı için şunu yapalım dblink kullanarak ayrı işlemlerdegerçekte nasıl olacak:

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

Ne oldu? Neden tek bir kaydın en basit GÜNCELLEMESİ için bile yürütme süresi 7 kat azaldı — 0.524 ms'den 3.808 ms'ye mi? Ve derecelendirmemiz giderek daha yavaş artıyor.

Hepsi MVCC'nin hatası.

her şey hakkında MVCC mekanizmasıBu, sorgunun girdinin önceki tüm sürümlerine bakmasına neden olur. O halde tablomuzu “ölü” versiyonlardan temizleyelim:

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

Ah, temizlenecek hiçbir şey yok! Paralel Çalışan istek bizi engelliyor - sonuçta, bir gün bu versiyonlara dönmek isteyebilir (ya şöyle olursa?) ve bunların onun kullanımına sunulması gerekir. Ve bu nedenle VAKUM DOLU bile bize yardımcı olmayacaktır.

Masayı “çöktürmek”

Ancak bu sorgunun tablomuza ihtiyacı olmadığından eminiz. Bu nedenle, VACUUM pes ettiği için, gereksiz olan her şeyi tablodan - en azından "manuel olarak" ortadan kaldırarak sistem performansını yeterli sınırlara döndürmeye çalışacağız.

Daha açık hale getirmek için tampon tablo örneğine bakalım. Yani, büyük bir INSERT/DELETE akışı vardır ve bazen tablo tamamen boştur. Ama eğer boş değilse, yapmalıyız mevcut içeriğini kaydet.

#0: Durumun değerlendirilmesi

Her işlemden sonra bile tabloyla bir şeyler yapmayı deneyebileceğiniz açıktır, ancak bu pek mantıklı değildir - bakım yükü, hedef sorguların veriminden açıkça daha fazla olacaktır.

Kriterleri formüle edelim: "harekete geçme zamanı" eğer:

  • VACUUM oldukça uzun zaman önce piyasaya sürüldü
    Ağır bir yük bekliyoruz, öyle olsun 60 saniye son [otomatik]VAKUM'dan bu yana.
  • fiziksel tablo boyutu hedeften daha büyük
    Bunu minimum boyuta göre sayfa sayısının iki katı (8KB blok) olarak tanımlayalım - Yığın için 1 blok + her dizin için 1 blok - potansiyel olarak boş bir masa için. Belirli bir miktar verinin her zaman arabellekte "normal" olarak kalacağını beklersek, bu formülde değişiklik yapmak mantıklı olacaktır.

Doğrulama isteği

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: Hâlâ VAKUM

Paralel bir sorgunun bize önemli ölçüde müdahale edip etmediğini önceden bilemeyiz - başladığından bu yana tam olarak kaç kaydın "güncelliğini yitirdiğini" bilemeyiz. Bu nedenle, tabloyu bir şekilde işlemeye karar verdiğimizde, her durumda, önce onu çalıştırmalıyız. VAKUM - VACUUM FULL'dan farklı olarak okuma-yazma verileriyle çalışan paralel işlemlere müdahale etmez.

Aynı zamanda kaldırmak istediklerimizin çoğunu anında temizleyebilir. Evet ve bu tablodaki sonraki sorgular bize iletilecek "sıcak önbellek" ilebu da sürelerini ve dolayısıyla hizmet işlemimiz nedeniyle başkalarının engellenmesinin toplam süresini azaltacaktır.

#2: Evde kimse var mı?

Tabloda herhangi bir şey olup olmadığını kontrol edelim:

TABLE tbl LIMIT 1;

Tek bir kayıt kalmadıysa, yalnızca çalıştırarak işlemden çok tasarruf edebiliriz. KIRMIZI:

Her tablo için koşulsuz DELETE komutuyla aynı işlevi görür, ancak aslında tabloları taramadığından çok daha hızlıdır. Üstelik disk alanını anında boşaltır, dolayısıyla daha sonra VAKUM işlemi yapmanıza gerek kalmaz.

Tablo sıra sayacını (YENİDEN BAŞLATMA KİMLİK) sıfırlamanız gerekip gerekmediğine karar vermek size kalmıştır.

#3: Herkes - sırayla!

Rekabetin yoğun olduğu bir ortamda çalıştığımız için tablodaki kayıtların yokluğunu kontrol ederken birileri oraya bir şeyler yazmış olabilir. Bu bilgiyi kaybetmemeliyiz, ne olmuş yani? Doğru, bunu kimsenin kesin olarak yazamayacağından emin olmamız gerekiyor.

Bunu yapmak için etkinleştirmemiz gerekiyor SERİLEŞTİRİLEBİLİR- işlemimiz için izolasyon (evet, burada bir işleme başlıyoruz) ve masayı "sıkıca" kilitliyoruz:

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

Bu engelleme düzeyi, üzerinde gerçekleştirmek istediğimiz işlemlere göre belirlenir.

#4: Çıkar çatışması

Buraya geliyoruz ve tabelayı "kilitlemek" istiyoruz - ya birisi o anda tabela üzerinde aktifse, örneğin tabeladan okuyorsa? Bu bloğun çıkmasını bekleyerek "takılacağız", okumak isteyenler de bizimle karşılaşacak...

Bunun olmasını önlemek için "kendimizi feda edeceğiz" - eğer belirli (kabul edilebilir derecede kısa) bir süre içinde kilit alamamışsak, o zaman tabandan bir istisna alacağız, ancak en azından çok fazla müdahale etmeyeceğiz. diğerleri.

Bunu yapmak için oturum değişkenini ayarlayın lock_timeout (9.3+ sürümleri için) veya/ve deyim_zaman aşımı. Hatırlanması gereken en önemli şey,statement_timeout değerinin yalnızca bir sonraki ifadeden geçerli olduğudur. Yani, yapıştırmada böyle - işe yaramayacak: 

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

Daha sonra değişkenin "eski" değerini geri yüklemeyle uğraşmamak için formu kullanıyoruz YEREL AYARLAayarın kapsamını geçerli işlemle sınırlandırır.

Tabloda çok fazla veri varsa işlemin kabul edilemez değerlere uzanmaması içinstatement_timeout'un sonraki tüm istekler için geçerli olduğunu hatırlıyoruz.

#5: Verileri kopyalayın

Tablo tamamen boş değilse, verilerin yardımcı bir geçici tablo kullanılarak yeniden kaydedilmesi gerekecektir:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

İmza COMMIT BIRAKMADA işlem sona erdiği anda geçici tablonun varlığının sona ereceği ve bağlantı bağlamında onu manuel olarak silmeye gerek olmayacağı anlamına gelir.

Çok fazla "canlı" verinin olmadığını varsaydığımız için bu işlemin oldukça hızlı bir şekilde gerçekleşmesi gerekiyor.

İşte bu kadar! İşlemi tamamladıktan sonra unutmayın ANALİZİ çalıştır Gerekirse tablo istatistiklerini normalleştirmek için.

Son senaryoyu bir araya getirmek

Bu “sözde python”u kullanıyoruz:

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

Verilerin ikinci kez kopyalanmaması mümkün müdür?Prensip olarak, tablonun oid'inin BL tarafından başka herhangi bir aktiviteye veya DB tarafından FK'ye bağlı olmaması mümkündür:

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

Komut dosyasını kaynak tabloda çalıştıralım ve metrikleri kontrol edelim:

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

Her şey yolunda gitti! Tablo 50 kat küçüldü ve tüm UPDATE'ler yeniden hızlı çalışıyor.

Kaynak: habr.com

Yorum ekle