Jak przyspieszyć wydajność wstawiania w PostgreSQL

Testuję skuteczność wstawiania Postgres. Mam tabelę z jedną kolumną z numerem jako typem danych. Jest tam również indeks. Zapełniłem bazę danych za pomocą tego zapytania:

insert into aNumber (id) values (564),(43536),(34560) ...

W powyższym zapytaniu wstawiłem 4 miliony wierszy jednocześnie bardzo szybko 10 000. Po osiągnięciu przez bazę danych 6 milionów wierszy wydajność drastycznie spadła do 1 miliona wierszy co 15 minut. Czy jest jakiś sposób na zwiększenie wydajności wstawiania? Potrzebuję optymalnej wydajności wstawiania w tym projekcie.

Korzystanie z systemu Windows 7 Pro na komputerze z 5 GB pamięci RAM.

Zobacz wypełnianie bazy danych w podręczniku PostgreSQL, jak zwykle artykuł Depesz na ten temat oraz to SO pytanie .

(Zauważ, że ta odpowiedź dotyczy masowego ładowania danych do istniejącej bazy danych lub tworzenia nowej. Jeśli jesteś zainteresowany, DB przywraca wydajność pg_restorelub psqlwykonanie danych pg_dumpwyjściowych, większość z nich nie ma zastosowania odtąd pg_dumpi pg_restorejuż robi rzeczy takie jak tworzenie wyzwala i indeksuje po zakończeniu schematu + przywracanie danych) .

Jest wiele do zrobienia. Idealnym rozwiązaniem byłoby zaimportowanie do UNLOGGEDtabeli bez indeksów, a następnie zmiana jej na zalogowaną i dodanie indeksów. Niestety w PostgreSQL 9.4 nie ma możliwości zmiany tabel z UNLOGGEDzalogowanych. 9.5 dodaje, ALTER TABLE ... SET LOGGEDaby ci to umożliwić.

Jeśli możesz przełączyć bazę danych w tryb offline do importu zbiorczego, użyj pg_bulkload.

Inaczej:

• Wyłącz wszystkie wyzwalacze na stole

• Usuń indeksy przed rozpoczęciem importu, a następnie utwórz je ponownie. ( Zbudowanie indeksu w jednym przebiegu zajmuje znacznie mniej czasu niż stopniowe dodawanie do niego tych samych danych, a wynikowy indeks jest znacznie bardziej zwarty).

• W przypadku importu w ramach jednej transakcji można bezpiecznie usunąć ograniczenia klucza obcego, wykonać import i ponownie utworzyć ograniczenia przed zatwierdzeniem. Nie rób tego, jeśli import jest podzielony na wiele transakcji, ponieważ możesz wprowadzić nieprawidłowe dane.

• Jeśli to możliwe, użyj COPYzamiast INSERTs

• Jeśli nie możesz użyć, COPYrozważ użycie wielu wartości, INSERTjeśli jest to praktyczne. Wygląda na to, że już to robisz. Nie próbuj jednak wymieniać zbyt wielu wartości w jednym VALUES; wartości te muszą się kilka razy zmieścić w pamięci, więc utrzymuj je na poziomie kilkuset na instrukcję.

• Partie wkładek w jawne transakcje, wykonując setki tysięcy lub milionów wstawek na transakcję. AFAIK nie ma praktycznego limitu, ale grupowanie pozwala na odzyskanie po błędzie poprzez oznaczenie początku każdej partii w danych wejściowych. Znów wydaje się, że już to robisz.

• Użyj synchronous_commit=offi ogromną, commit_delayaby zmniejszyć koszty fsync (). To jednak niewiele pomoże, jeśli podzielisz swoją pracę na duże transakcje.

• INSERTlub COPYrównolegle z kilku połączeń. Ile zależy od podsystemu dyskowego twojego sprzętu; ogólna zasada to jedno połączenie na fizyczny dysk twardy, jeśli używana jest pamięć podłączona bezpośrednio.

• Ustaw wysoką checkpoint_segmentswartość i włącz log_checkpoints. Przejrzyj dzienniki PostgreSQL i upewnij się, że nie narzeka na zbyt częste punkty kontrolne.

• Jeśli tylko nie masz nic przeciwko utracie całego klastra PostgreSQL (bazy danych i wszystkich innych w tym samym klastrze) z powodu katastrofalnego uszkodzenia w przypadku awarii systemu podczas importu, możesz zatrzymać Pg, ustawić fsync=off, uruchomić Pg, wykonać import, następnie (witalnie) zatrzymaj Pg i ustaw fsync=onponownie. Zobacz konfigurację WAL . Nie rób tego, jeśli w dowolnej bazie danych instalacji PostgreSQL znajdują się już dane, na których Ci zależy. Jeśli ustawisz fsync=off, możesz także ustawić full_page_writes=off; ponownie, pamiętaj tylko o ponownym włączeniu po imporcie, aby zapobiec uszkodzeniu bazy danych i utracie danych. Zobacz nietrwałe ustawienia w instrukcji Pg.

Powinieneś także przyjrzeć się strojeniu systemu:

• Używaj dysków SSD dobrej jakości do przechowywania jak najwięcej. Dobre dyski SSD z niezawodnymi, chronionymi zasilaniem buforami zapisu zwrotnego sprawiają, że szybkość zatwierdzania jest niewiarygodnie szybsza. Są mniej korzystne, gdy zastosujesz się do powyższej porady - co zmniejsza opróżnianie dysku / liczbę fsync()s - ale nadal może być dużą pomocą. Nie używaj tanich dysków SSD bez odpowiedniej ochrony przed awarią zasilania, chyba że nie zależy ci na przechowywaniu danych.

• Jeśli używasz RAID 5 lub RAID 6 do bezpośredniego podłączania pamięci, przestań teraz. Utwórz kopię zapasową danych, zrestrukturyzuj macierz RAID do RAID 10 i spróbuj ponownie. RAID 5/6 nie ma nadziei na wydajność zapisu zbiorczego - chociaż dobry kontroler RAID z dużą pamięcią podręczną może pomóc.

• Jeśli masz opcję użycia sprzętowego kontrolera RAID z dużą, buforowaną pamięcią podręczną pamięcią podręczną zapisu, może to naprawdę poprawić wydajność zapisu dla obciążeń z dużą ilością zatwierdzeń. Nie pomaga to tak bardzo, jeśli używasz zatwierdzania asynchronicznego z opóźnieniem zatwierdzenia lub wykonujesz mniej dużych transakcji podczas ładowania zbiorczego.

• Jeśli to możliwe, przechowuj WAL ( pg_xlog) na osobnym dysku / macierzy dyskowej. Używanie osobnego systemu plików na tym samym dysku nie ma sensu. Ludzie często wybierają parę RAID1 dla WAL. Znowu ma to większy wpływ na systemy o wysokich współczynnikach zatwierdzania i ma niewielki wpływ, jeśli używasz niezalogowanej tabeli jako celu ładowania danych.

Możesz być także zainteresowany Zoptymalizuj PostgreSQL do szybkiego testowania .

Zastosowanie COPY table TO ... WITH BINARYzgodne z dokumentacją jest „ nieco szybsze niż format tekstowy i CSV ”. Zrób to tylko wtedy, gdy masz miliony wierszy do wstawienia i nie masz problemów z danymi binarnymi.

Oto przykładowy przepis w Pythonie, wykorzystujący psycopg2 z wejściem binarnym .

Oprócz doskonałego postu Craiga Ringera i posta na blogu depesz, jeśli chcesz przyspieszyć swoje wstawki za pomocą interfejsu ODBC ( psqlodbc ), używając wstawionych gotowych instrukcji w transakcji, jest kilka dodatkowych rzeczy, które musisz zrobić, aby to zrobić Pracuj szybko:

1. Ustaw poziom wycofywania błędów na „Transakcja”, określając Protocol=-1parametry połączenia. Domyślnie psqlodbc używa poziomu „Wyciąg”, który tworzy SAVEPOINT dla każdego wyciągu, a nie dla całej transakcji, powodując wolniejsze wstawianie.
2. Użyj instrukcji przygotowanych po stronie serwera, określając UseServerSidePrepare=1parametry połączenia. Bez tej opcji klient wysyła całą instrukcję insert wraz z każdym wstawianym wierszem.
3. Wyłącz automatyczne zatwierdzanie dla każdej instrukcji za pomocą SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
4. Po wstawieniu wszystkich wierszy zatwierdzić transakcję za pomocą SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);. Nie ma potrzeby jawnego otwierania transakcji.

Niestety psqlodbc „implementuje” SQLBulkOperations , wydając serię nieprzygotowanych instrukcji insert, więc aby uzyskać najszybsze wstawianie, należy ręcznie kodować powyższe kroki.

Dziś spędziłem na tej samej kwestii około 6 godzin. Wkładki idą z „regularną” prędkością (mniej niż 3 sekundy na 100 K) aż do 5MI wierszy (z całkowitej liczby 30 MMI), a następnie wydajność drastycznie spada (aż do 1 minuty na 100 K).

Nie wymienię wszystkich rzeczy, które nie zadziałały i pokroiłem prosto na mięso.

I spadł klucza podstawowego w tabeli docelowej (które było GUID) i mój 30MI lub wiersze szczęśliwie płynęły do miejsca przeznaczenia przy prędkości stałej mniejszej niż 3s za 100K.

Jeśli zdarzyło Ci się wstawić kolumny z UUID (co nie jest dokładnie twoim przypadkiem) i dodać do odpowiedzi @Dennis (nie mogę jeszcze komentować), radzę niż używać gen_random_uuid () (wymaga PG 9.4 i modułu pgcrypto) jest (a dużo) szybciej niż uuid_generate_v4 ()

=# explain analyze select uuid_generate_v4(),* from generate_series(1,10000);
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=11.674..10304.959 rows=10000 loops=1)
 Planning time: 0.157 ms
 Execution time: 13353.098 ms
(3 filas)

vs

=# explain analyze select gen_random_uuid(),* from generate_series(1,10000);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.274..418.137 rows=10000 loops=1)
 Planning time: 0.064 ms
 Execution time: 503.818 ms
(3 filas)

Jest to również sugerowany oficjalny sposób na zrobienie tego

Uwaga

Jeśli potrzebujesz tylko losowo wygenerowanych UUID (wersja 4), rozważ użycie funkcji gen_random_uuid () z modułu pgcrypto.

Ten czas wstawiania spadł z ~ 2 godzin do ~ 10 minut dla 3,7 mln wierszy.

Aby uzyskać optymalną wydajność wstawiania, wyłącz indeks, jeśli jest to dla Ciebie opcja. Poza tym pomocny jest także lepszy sprzęt (dysk, pamięć)

Wystąpił również problem z wydajnością wstawiania. Moim rozwiązaniem jest odrodzenie niektórych procedur go, aby zakończyć pracę wstawiania. W międzyczasie SetMaxOpenConnsnależy podać prawidłowy numer, w przeciwnym razie zostanie zaalarmowany zbyt duży błąd połączenia.

db, _ := sql.open() 
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
var wg sync.WaitGroup
for _, query := range queries {
    wg.Add(1)
    go func(msg string) {
        defer wg.Done()
        _, err := db.Exec(msg)
        if err != nil {
            fmt.Println(err)
        }
    }(query)
}
wg.Wait()

Szybkość ładowania jest znacznie większa dla mojego projektu. Ten fragment kodu po prostu dał wyobrażenie o tym, jak to działa. Czytelnicy powinni mieć możliwość łatwej modyfikacji.