Dodanie kolumny zerowalnej do tabeli kosztuje ponad 10 minut

Mam problemy z dodaniem nowej kolumny do tabeli.
Próbowałem uruchomić go kilka razy, ale po ponad 10 minutach postanowiłem anulować zapytanie z powodu czasu blokady.

ALTER TABLE mytable ADD mycolumn VARCHAR(50);

Przydatna informacja:

• Wersja PostgreSQL: 9.1
• Liczba rzędów: ~ 250 K.
• Liczba kolumn: 38
• Liczba zerowalnych kolumn: 32
• Liczba ograniczeń: 5 (1 PK, 3 FK, 1 UNIQUE)
• Liczba indeksów: 1
• Typ systemu operacyjnego: Debian Squeeze 64

Znalazłem ciekawe informacje na temat sposobu, w jaki PostgreSQL zarządza zerowalnymi kolumnami (przez HeapTupleHeader).

Moje pierwsze przypuszczenie jest takie, że ponieważ ta tabela ma już 32 zerowalne kolumny z 8-bitami MAXALIGN, HeapTupleHeader ma długość 4 Bajtów (niezweryfikowane i nie wiem, jak to zrobić).

Tak więc dodanie nowej zerowalnej kolumny może wymagać aktualizacji HeapTupleHeader w każdym wierszu, aby dodać nowe 8 bitów MAXALIGN, co może powodować problemy z wydajnością.

Próbowałem więc zmienić jedną z kolumn zerowalnych (która w rzeczywistości nie jest zerowalna), aby zmniejszyć do 31 liczbę kolumn zerowalnych, aby sprawdzić, czy moje przypuszczenia mogą być prawdziwe.

ALTER TABLE mytable ALTER myothercolumn SET NOT NULL;

Niestety, ta zmiana również zajmuje bardzo dużo czasu, ponad 5 minut, więc też ją przerwałam.

Czy masz pojęcie, co może spowodować ten koszt wydajności?

Jest tu kilka nieporozumień:

Zerowy bitmapy jest nie część nagłówka sterty krotki. Według dokumentacji:

Istnieje nagłówek o stałym rozmiarze (zajmujący 23 bajty na większości komputerów), a następnie opcjonalna pusta mapa bitowa ...

Twoje 32 zerowalne kolumny są niepomyślne z dwóch powodów:

• Bitmapa o wartości zerowej jest dodawana do wiersza i tylko wtedy, gdy w wierszu znajduje się co najmniej jedna rzeczywista NULLwartość . Kolumny dopuszczające wartości zerowe nie mają bezpośredniego wpływu, a jedynie rzeczywiste NULLwartości. Jeśli przydzielona jest pusta mapa bitowa, zawsze jest ona przydzielana całkowicie (wszystko lub nic). Rzeczywisty rozmiar pustej mapy bitowej wynosi 1 bit na kolumnę, w zaokrągleniu do następnego bajtu . Według aktualnego kodu sosu:

  #define BITMAPLEN(NATTS) (((int)(NATTS) + 7) / 8)

• Pusta mapa bitowa jest przydzielana za nagłówkiem krotki sterty, po której następuje opcjonalny identyfikator OID, a następnie dane wiersza. Początek OID lub danych wiersza jest oznaczony t_hoffw nagłówku. Kod źródłowy komentarza :

  Zauważ, że t_hoff musi być wielokrotnością MAXALIGN.

• Po nagłówku krotki kupy znajduje się jeden wolny bajt, który zajmuje 23 bajty. Tak więc zerowa bitmapa dla wierszy do 8 kolumn faktycznie nie wiąże się z żadnymi dodatkowymi kosztami. Z dziewiątą kolumną w tabeli t_hoffprzesuwa się kolejny MAXALIGN(zwykle 8) bajtów, aby zapewnić kolejne 64 kolumny. Tak więc następna granica będzie mieć 72 kolumny.

Aby wyświetlić informacje kontrolne klastra bazy danych PostgreSQL (włącznie MAXALIGN), przykład typowej instalacji Postgres 9.3 na maszynie Debiana:

    sudo /usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/main

Zaktualizowałem instrukcje w powiązanej cytowanej odpowiedzi .

To wszystko, nawet jeśli twoja ALTER TABLEinstrukcja uruchamia przepisywanie całej tabeli (co prawdopodobnie robi, zmieniając typ danych), 250K to naprawdę niewiele, i byłoby to kwestią sekund na każdej przyzwoitej maszynie (chyba że rzędy są niezwykle duże) . 10 minut lub więcej wskazuje zupełnie inny problem. Najprawdopodobniej twoje oświadczenie czeka na blokadę stołu.

Rosnąca liczba wpisów pg_stat_activityoznacza więcej otwartych transakcji - oznacza równoczesny dostęp do tabeli (najprawdopodobniej), który musi czekać na zakończenie operacji.

Kilka ujęć w ciemności

Sprawdź ewentualne wzdęcia przy stole, spróbuj łagodnego VACUUM mytablelub bardziej agresywnego VACUUM FULL mytable- który może napotkać te same problemy z współbieżnością, ponieważ ta forma zyskuje również wyłączną blokadę. Zamiast tego możesz spróbować pg_repack ...

Zacznę od sprawdzenia możliwych problemów z indeksami, wyzwalaczami, kluczem obcym lub innymi ograniczeniami, szczególnie tymi dotyczącymi kolumny. Może to dotyczyć szczególnie uszkodzonego indeksu? Wypróbuj je wszystkie REINDEX TABLE mytable;lub DROPdodaj je ponownie ALTER TABLE w tej samej transakcji .

Spróbuj uruchomić polecenie w nocy lub zawsze, gdy nie ma dużego obciążenia.

Metoda brute-force polega na zatrzymaniu dostępu do serwera, a następnie spróbuj ponownie:

• Force drop db, podczas gdy inni mogą być podłączeni w postgresql

Bez możliwości przypisania go, pomocne może być uaktualnienie do bieżącej wersji lub nadchodzącej wersji 9.4 . Wprowadzono kilka ulepszeń dla dużych tabel i szczegółów blokowania. Ale jeśli coś jest zepsute w twoim DB, prawdopodobnie powinieneś to najpierw zrozumieć.