Optymalizacja wydajności aktualizacji zbiorczych w PostgreSQL

Korzystanie z PG 9.1 na Ubuntu 12.04.

Obecnie uruchomienie dużego zestawu instrukcji UPDATE w bazie danych, które mają postać:

UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid

(Po prostu nadpisujemy pola obiektów identyfikowanych przez identyfikator.) Wartości pochodzą z zewnętrznego źródła danych (jeszcze nie w DB w tabeli).

Tabele zawierają garść indeksów i nie ma ograniczeń klucza obcego. Do końca nie jest wykonywany COMMIT.

Zaimportowanie pg_dumpcałego DB zajmuje 2 godziny. To wydaje się punktem odniesienia, do którego powinniśmy rozsądnie dążyć.

Czy poza stworzeniem niestandardowego programu, który w jakiś sposób rekonstruuje zestaw danych, aby PostgreSQL mógł ponownie zaimportować, czy jest coś, co możemy zrobić, aby zbliżyć masową wydajność UPDATE do wydajności importu? (Jest to obszar, który naszym zdaniem obsługuje drzewa scalające o strukturze dziennika, ale zastanawiamy się, czy jest coś, co możemy zrobić w PostgreSQL.)

Jakieś pomysły:

• porzucasz wszystkie wskaźniki inne niż ID, a potem odbudowujesz?
• zwiększenie checkpoint_segments, ale czy to faktycznie pomaga w utrzymaniu długoterminowej przepustowości?
• używając technik wymienionych tutaj ? (Załaduj nowe dane jako tabelę, a następnie „scal” stare dane, w których nie znaleziono identyfikatora w nowych danych)

Zasadniczo jest wiele rzeczy do wypróbowania i nie jesteśmy pewni, jakie są najbardziej skuteczne lub czy pomijamy inne rzeczy. Kolejne dni spędzimy na eksperymentach, ale pomyśleliśmy, że też tutaj zapytamy.

Mam równoległe obciążenie tabeli, ale jest to tylko do odczytu.

Założenia

Ponieważ w Q brakuje informacji, założę:

• Twoje dane pochodzą z pliku na serwerze bazy danych.
• Dane są sformatowane podobnie jak COPYdane wyjściowe, z unikalnym id wierszem pasującym do tabeli docelowej.
  Jeśli nie, najpierw sformatuj go poprawnie lub użyj COPYopcji, aby poradzić sobie z tym formatem.
• Aktualizujesz każdy wiersz w tabeli docelowej lub większość z nich.
• Możesz sobie pozwolić na upuszczenie i odtworzenie tabeli docelowej.
  Oznacza to brak równoczesnego dostępu. W przeciwnym razie rozważ tę powiązaną odpowiedź:
  • Najlepszy sposób na wypełnienie nowej kolumny w dużej tabeli?
• Nie ma żadnych zależnych obiektów, z wyjątkiem indeksów.

Rozwiązanie

Sugeruję, aby stosować podobne podejście, jak opisano w linku z trzeciej kuli . Z dużymi optymalizacjami.

Aby utworzyć tabelę tymczasową, istnieje prostszy i szybszy sposób:

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

Jeden duży UPDATEz tabeli tymczasowej wewnątrz bazy danych będzie szybsza niż poszczególnych aktualizacjach spoza bazy danych o kilka rzędów wielkości.

W modelu PostgreSQL MVCC , an UPDATEśrodki, aby utworzyć nową wersję wiersza i oznaczyć starą jako usunięte. To jest tak drogie jak INSERTi DELETEłącznie. Plus, pozostawia ci wiele martwych krotek. Ponieważ i tak aktualizujesz cały stół, ogólnie byłoby szybsze utworzenie nowego stołu i upuszczenie starego.

Jeśli masz wystarczającą ilość pamięci RAM, ustaw temp_buffers(tylko dla tej sesji!) Wystarczająco wysoką, aby utrzymać tabelę temp w pamięci RAM - zanim zrobisz cokolwiek innego.

Aby oszacować, ile pamięci RAM jest potrzebne, uruchom test z małą próbką i użyj funkcji rozmiaru obiektu db :

SELECT pg_size_pretty(pg_relation_size('tmp_tbl'));  -- complete size of table
SELECT pg_column_size(t) FROM tmp_tbl t LIMIT 10;  -- size of sample rows

Kompletny skrypt

SET temp_buffers = '1GB';        -- example value

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

COPY tmp_tbl FROM '/absolute/path/to/file';

CREATE TABLE tbl_new AS
SELECT t.col1, t.col2, u.field1, u.field2
FROM   tbl     t
JOIN   tmp_tbl u USING (id);

-- Create indexes like in original table
ALTER TABLE tbl_new ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl_new (...);
CREATE INDEX ... ON tbl_new (...);

-- exclusive lock on tbl for a very brief time window!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

DROP TABLE tmp_tbl; -- will also be dropped at end of session automatically

Równoczesne obciążenie

Współbieżne operacje na tabeli (które wykluczyłem w założeniach na początku) będą czekać, gdy tabela zostanie zablokowana pod koniec i zakończy się niepowodzeniem, gdy transakcja zostanie zatwierdzona, ponieważ nazwa tabeli jest natychmiast rozpoznawana jako OID, ale nowa tabela ma inny identyfikator OID. Tabela pozostaje spójna, ale równoczesne operacje mogą uzyskać wyjątek i muszą zostać powtórzone. Szczegóły w tej powiązanej odpowiedzi:

• Najlepszy sposób na wypełnienie nowej kolumny w dużej tabeli?

AKTUALIZUJ trasę

Jeśli (musisz) przejść UPDATEtrasę, upuść indeks, który nie jest potrzebny podczas aktualizacji, a następnie utwórz go ponownie. Znacznie taniej jest utworzyć indeks w jednym kawałku niż zaktualizować go dla każdego wiersza. Może to również pozwolić na GORĄCE aktualizacje .

Przedstawiłem podobną procedurę UPDATEw tej ściśle powiązanej odpowiedzi dotyczącej SO .

Jeśli dane można udostępnić w pliku strukturalnym, można je odczytać za pomocą zewnętrznego opakowania danych i wykonać scalenie w tabeli docelowej.