1 Temat przez ptx

  • ptx
  • Nowy
  • Nieaktywny
  • Zarejestrowany: 2011-10-20
  • Posty: 3

Temat: Usuwanie dużej ilość rekordów

Witam
Mam taki problem
Podczas kasowania dużej ilości (ok 30ys rekordów)w jednej transakcji i mam ogromne opóźnienia działania serwera CPU obciążone do 100%
nie pomaga żadne vacuum ani vacuum full ani włączone autovacuum w postgersql.conf
Gdy baza zawierała mniej rekordów problem z obciążeniem CPU występował ale kasowanie nie trwało bardzo długo (ok 3 min), teraz gdy baza się rozrasta kasowanie trwa nawet 60 min i więcej
Baza ma indeksy na tabelach na klucze własne i obce no i niestety na tabelach pozakładane są trigery kontrolujące różne zdarzenia.
postgresql jakiego używam aktualnie to 8.3 , 8.4
proszę o jakies wskazówki

2 Odpowiedź przez rski

  • rski
  • Moderator
  • Nieaktywny
  • Skąd: Gdańsk
  • Zarejestrowany: 2008-03-02
  • Posty: 924

Odp: Usuwanie dużej ilość rekordów

Spróbuj zamiast usuwania
1) wykonać kopię tabeli bez rekordów które mają byc usunięte (CREATE TABLE AS SELECT)
2) usunąć tabele oryginalną
3) zmienić nazwę kopii na nazwę która miała oryginala tabela

czyli zamiast

DELETE FROM test WHERE id=100

zrób

1) CREATE TABLE test_new AS SELECT * FROM test WHERE id<>100
2) DROP TABLE test; (tu zamiast DROP możesz zrobić rename ALTER TABLE test RENAME TO test_old)
3) ALTER TABLE test_new RENAME TO test

Jedyna wada tego rozwiązania to dodatkowa przestrzeń na dysku jak będzie wymagana na nową tabelę.

3 Odpowiedź przez ptx

  • ptx
  • Nowy
  • Nieaktywny
  • Zarejestrowany: 2011-10-20
  • Posty: 3

Odp: Usuwanie dużej ilość rekordów

dziękuje za odpowiedź
spróbuje tego rozwiązania
chociaż obawiam się że podczas DROP TABLE usunięte zostaną także trigery pracujące na tej tabeli więc sprawa się nieco komplikuje, ale za odpowiedź dziękuję.

4 Odpowiedź przez rski

  • rski
  • Moderator
  • Nieaktywny
  • Skąd: Gdańsk
  • Zarejestrowany: 2008-03-02
  • Posty: 924

Odp: Usuwanie dużej ilość rekordów

Dobrze się obawiasz, trzeba będzie je odtworzyć jak i indeksy, które masz utworzone. Co nie zmienia faktu że jest to szybsze rozwiązanie i oryginalna tabela nie jest blokowana przez dlugi delete.
Jak duza jest tabela z której usuwasz dane?

5 Odpowiedź przez ptx

  • ptx
  • Nowy
  • Nieaktywny
  • Zarejestrowany: 2011-10-20
  • Posty: 3

Odp: Usuwanie dużej ilość rekordów

struktura jest taka
tabele
TNaglowki (id_naglowka)
TPozycje (id_pozycji,id_naglowka,id_naglowka_zw)

pole id_naglowka w tabeli TPozycje jest jako klucz podrzedny i założony na niego index (to jasne)
pole id_naglowka_zw jest wypełniane opcjonalnie na nie też jest załozony index

tabele mają tez inne kolumny ale one nie sa powiązane ze sobą

przyklad

TNaglowki
id_naglowka
----------------
1
2
3
4
5

TPozycje
-----------------------------------------
id_pozycj| id_naglowka| id_naglowka_zw|
1                                      1             2
2                                      1             3
3                                      1             4
4                                      1             5
5                                      2             NULL
6                                      2             NULL
7                                      2             NULL
8                                      3             NULL
9                                      3             NULL
10                                   3             NULL       
idt

kasowanie wykonuje tak
DELETE FROM TPOZYCJE WHERE id_naglowka=1 
czyli odnosząc sie do przykladu
kasuja sie sie
id_pozycji 1,2,3,4
id_naglowka 2,3,4,5
i jednocześnie id_pozycji 5,6,7,8,9,10......itd


tabela TNaglowki zawiera w tej chwili 90 tys rekordów
tabela Tpozycjie zawiera w tej chwili  350 tys rekrodów
ilość kasowanych  rekordów z Tnaglowki jest ok 2,5 tys
ilość kasowanych rekordów z Tpozycje jest okolo 25 tys
kasowanie trwa ponad godzine i CPU cały czas 100%
na poczatku gdy tabele miały mniej rekordow kasowanie trwało 3 min (CPU też 100% ale 3 min to nie godzina)