Dlaczego aktualizacja Postgres zajęła 39 godzin?

Mam tabelę Postgres z ~ 2,1 miliona wierszy. Uruchomiłem na nim poniższą aktualizację:

WITH stops AS (
    SELECT id,
           rank() OVER (ORDER BY offense_timestamp,
                     defendant_dl,
                     offense_street_number,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1
)

UPDATE consistent.master
SET arrest_id=stops.stop
FROM stops
WHERE master.id = stops.id;

Uruchomienie tego zapytania zajęło 39 godzin. Używam tego na 4 (fizycznym) rdzeniu laptopa i7 Q720, dużo pamięci RAM, nic więcej nie działa przez większość czasu. Brak ograniczeń miejsca na dysku twardym. Tabela została niedawno odkurzona, przeanalizowana i ponownie zindeksowana.

Przez cały czas działania zapytania, przynajmniej po zakończeniu początkowego WITH, zużycie procesora było zwykle niskie, a dysk twardy był w użyciu w 100%. Dysk twardy był używany tak intensywnie, że każda inna aplikacja działała znacznie wolniej niż normalnie.

Ustawienia zasilania laptopa były na wysokiej wydajności (Windows 7 x64).

Oto WYJAŚNIENIE:

Update on master  (cost=822243.22..1021456.89 rows=2060910 width=312)
  CTE stops
    ->  WindowAgg  (cost=529826.95..581349.70 rows=2060910 width=33)
          ->  Sort  (cost=529826.95..534979.23 rows=2060910 width=33)
                Sort Key: consistent.master.offense_timestamp, consistent.master.defendant_dl, consistent.master.offense_street_number, consistent.master.offense_street_name
                ->  Seq Scan on master  (cost=0.00..144630.06 rows=2060910 width=33)
                      Filter: (citing_jurisdiction = 1)
  ->  Hash Join  (cost=240893.51..440107.19 rows=2060910 width=312)
        Hash Cond: (stops.id = consistent.master.id)
        ->  CTE Scan on stops  (cost=0.00..41218.20 rows=2060910 width=48)
        ->  Hash  (cost=139413.45..139413.45 rows=2086645 width=268)
              ->  Seq Scan on master  (cost=0.00..139413.45 rows=2086645 width=268)

citing_jurisdiction=1wyklucza tylko kilkadziesiąt tysięcy wierszy. Nawet z tą WHEREklauzulą ​​nadal działam w ponad 2 milionach wierszy.

Dysk twardy jest w całości zaszyfrowany za pomocą TrueCrypt 7.1a. Która spowalnia rzeczy nieco w dół, ale nie na tyle, aby spowodować zapytanie do podjęcia że wiele godzin.

Uruchomienie tej WITHczęści zajmuje około 3 minut.

arrest_idPola nie miał indeks dla klucza obcego. W tej tabeli znajduje się 8 indeksów i 2 klucze obce. Wszystkie pozostałe pola w zapytaniu są indeksowane.

arrest_idPola nie miał ograniczeń, z wyjątkiem NOT NULL.

Tabela zawiera łącznie 32 kolumny.

arrest_idma różny typ znaków (20) . Zdaję sobie sprawę, że rank()tworzy wartość liczbową, ale muszę używać znaków różniących się (20), ponieważ mam inne wiersze, w których citing_jurisdiction<>1w tym polu są używane dane nienumeryczne.

arrest_idPole było puste dla wszystkich rzędach citing_jurisdiction=1.

Jest to osobisty laptop z wyższej półki (od 1 roku). Jestem jedynym użytkownikiem. Żadne inne zapytania ani operacje nie były uruchomione. Blokowanie wydaje się mało prawdopodobne.

Nigdzie w tej tabeli ani nigdzie indziej w bazie danych nie ma żadnych wyzwalaczy.

Inne operacje na tej bazie danych nigdy nie zajmują zbyt dużo czasu. Przy właściwym indeksowaniu SELECTzapytania są zwykle dość szybkie.

Ostatnio stało się coś podobnego z tabelą 3,5 miliona wierszy. Moja aktualizacja nigdy się nie skończy. Po wielu eksperymentach i frustracji w końcu znalazłem winowajcę. Okazało się, że indeksy tabeli są aktualizowane.

Rozwiązaniem było usunięcie wszystkich indeksów z aktualizowanej tabeli przed uruchomieniem instrukcji aktualizacji. Gdy to zrobiłem, aktualizacja zakończyła się za kilka minut. Po zakończeniu aktualizacji ponownie utworzyłem indeksy i wróciłem do pracy. To prawdopodobnie nie pomoże ci w tym momencie, ale może ktoś inny szuka odpowiedzi.

Trzymałbym indeksy w tabeli, z której pobierasz dane. Ten nie będzie musiał aktualizować żadnych indeksów i powinien pomóc w znalezieniu danych, które chcesz zaktualizować. Działa dobrze na wolnym laptopie.

Twoim największym problemem jest wykonywanie dużej ilości pracy związanej z zapisem i wyszukiwaniem na dysku twardym laptopa. To nigdy nie będzie szybkie, bez względu na to, co robisz, zwłaszcza jeśli jest to rodzaj wolniejszego napędu 5400 RPM dostarczanego w wielu laptopach.

TrueCrypt spowalnia rzeczy bardziej niż „trochę” w przypadku zapisów. Odczyty będą dość szybkie, ale zapisy sprawiają, że RAID 5 wygląda szybko. Uruchamianie bazy danych na woluminie TrueCrypt będzie torturami dla zapisów, szczególnie przypadkowych.

W takim przypadku myślę, że marnowałbyś czas na optymalizację zapytania. W każdym razie przepisujesz większość wierszy i będzie to powolne w twojej przerażającej sytuacji pisania. Polecam:

BEGIN;
SELECT ... INTO TEMPORARY TABLE master_tmp ;
TRUNCATE TABLE consistent.master;
-- Now DROP all constraints on consistent.master, then:
INSERT INTO consistent.master SELECT * FROM master_tmp;
-- ... and re-create any constraints.

Podejrzewam, że będzie to szybsze niż samo usuwanie i ponowne tworzenie samych ograniczeń, ponieważ UPDATE będzie miało dość losowe wzorce zapisu, które zabiją twoją pamięć. Dwie wstawki zbiorcze, jedna do niezalogowanej tabeli i jedna do tabeli zalogowanej WAL bez ograniczeń, prawdopodobnie będą szybsze.

Jeśli masz absolutnie aktualne kopie zapasowe i nie masz nic przeciwko konieczności przywracania bazy danych z kopii zapasowych , możesz również ponownie uruchomić PostgreSQL z fsync=offparametrem i full_page_writes=off tymczasowo dla tej operacji zbiorczej. Wszelkie nieoczekiwane problemy, takie jak utrata zasilania lub awaria systemu operacyjnego, sprawią, że baza danych będzie nie do odzyskania fsync=off.

Odpowiednikiem POSTGreSQL dla „bez rejestrowania” jest użycie niezalogowanych tabel. Te niezalogowane tabele są obcinane, jeśli DB wyłącza się nieczysto, gdy są brudne. Używanie niezalogowanych tabel co najmniej zmniejszy obciążenie zapisu o połowę i zmniejszy liczbę wyszukiwań, dzięki czemu mogą być DUŻO szybsze.

Podobnie jak w Oracle, dobrym pomysłem może być usunięcie indeksu, a następnie ponowne utworzenie go po dużej aktualizacji wsadowej. Planista PostgreSQL nie może zorientować się, że ma miejsce duża aktualizacja, wstrzymać aktualizacje indeksu, a następnie odbudować indeks na końcu; nawet gdyby mógł, bardzo trudno byłoby zorientować się, w którym momencie warto to zrobić, szczególnie z góry.

Ktoś da lepszą odpowiedź dla Postgresa, ale oto kilka spostrzeżeń z perspektywy Oracle, które mogą mieć zastosowanie (a komentarze są za długie na pole komentarza).

Moją pierwszą troską będzie próba aktualizacji 2 milionów wierszy w jednej transakcji. W Oracle pisałeś obraz przed aktualizacją każdego bloku, aby inna sesja nadal miała spójny odczyt bez czytania zmodyfikowanych bloków i masz możliwość wycofania. To jest długie wycofywanie. Zwykle lepiej jest robić transakcje w małych porcjach. Powiedz 1000 rekordów na raz.

Jeśli masz indeksy na stole, a tabela będzie uważana za nieczynną podczas konserwacji, często lepiej jest usunąć indeksy przed dużą operacją, a następnie ponownie ją odtworzyć. Tańsze niż stałe próby utrzymania indeksów przy każdym zaktualizowanym rekordzie.

Oracle pozwala „nie rejestrować” podpowiedzi na wyciągach, aby zatrzymać rejestrowanie. Przyspiesza to wiele instrukcji, ale pozostawia twoją bazę danych w sytuacji „niemożliwej do odzyskania”. Więc chcesz wykonać kopię zapasową wcześniej, a kopię zapasową natychmiast później. Nie wiem, czy Postgres ma podobne opcje.