Jak przyspieszyć wydajność wstawiania w PostgreSQL

Testuję wydajność wstawiania Postgres. Mam tabelę z jedną kolumną z numerem jako typem danych. Jest na nim również indeks. Wypełniłem bazę danych używając tego zapytania: 

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

Wstawiłem 4 miliony wierszy bardzo szybko 10,000 na raz z powyższym zapytaniem. Po osiągnięciu przez bazę danych 6 milionów wierszy wydajność drastycznie spadła do 1 miliona wierszy co 15 minut. Czy istnieje jakaś sztuczka, aby zwiększyć wydajność wstawiania? Potrzebuję optymalnej wydajności wstawiania na tym projekt.

Używanie Windows 7 Pro na komputerze z 5 GB PAMIĘCI RAM.

Author: Erwin Brandstetter, 2012-08-31
Source

6 answers

Zobacz uzupełnij bazę danych w podręczniku PostgreSQL, doskonały jak zwykle artykuł depesz na ten temat, i to pytanie .

(zauważ, że ta odpowiedź dotyczy zbiorczego ładowania danych do istniejącego DB lub tworzenia nowego. Jeśli interesuje Cię wydajność DB restore z pg_restore LUB psql wykonaniem wyjścia pg_dump, wiele z tego nie ma zastosowania, ponieważ pg_dump i pg_restore już robią takie rzeczy, jak tworzenie wyzwalaczy i indeksów po zakończeniu schemat+przywracanie danych) .

Jest wiele do zrobienia. Idealnym rozwiązaniem byłoby zaimportowanie do tabeli UNLOGGED bez indeksów, a następnie zmiana jej na logged i dodanie indeksów. Niestety w PostgreSQL 9.4 nie ma wsparcia dla zmiany tabel z UNLOGGED na logged. 9.5 dodaje ALTER TABLE ... SET LOGGED, aby umożliwić Ci to.

Jeśli możesz wyłączyć bazę danych w celu importu zbiorczego, użyj pg_bulkload.

Inaczej:

  • Wyłącz wszelkie wyzwalacze na tabela

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

  • Jeśli importowanie odbywa się w ramach jednej transakcji, można bezpiecznie zrezygnować z ograniczeń 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 COPY zamiast INSERTs

  • Jeśli nie możesz użyć COPY rozważ użycie wielowartościowych INSERTs, jeś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ą zmieścić się w pamięci kilka razy, więc zachowaj je do kilkuset na każde polecenie.

  • Wsadź swoje wstawki do explicit transakcje, wykonując setki tysięcy lub miliony wstawek na transakcję. Nie ma praktycznego limitu AFAIK, ale wsadowanie pozwoli Ci odzyskać po błędzie, zaznaczając początek każdej partii w danych wejściowych. Znowu, wydaje się, że już to robisz.

  • Użyj synchronous_commit=off i ogromnego commit_delay, aby zmniejszyć koszty fsync (). To niewiele pomoże, jeśli umieścisz swoją pracę w dużych transakcjach.

  • INSERT lub COPY równolegle z kilku połączeń. Ile zależy od podsystemu dyskowego Twojego sprzętu; z reguły potrzebujesz jednego połączenia na fizyczny dysk twardy, jeśli używasz bezpośrednio dołączonej pamięci masowej.

  • Ustaw wysoką wartość checkpoint_segments i włącz log_checkpoints. Spójrz na dzienniki PostgreSQL i upewnij się, że nie skarży się na punkty kontrolne występujące zbyt często.

  • Wtedy i tylko wtedy, gdy nie przeszkadza ci utrata całego klastra PostgreSQL (Twojej bazy danych i innych na tym samym klastrze) na skutek katastrofalnego uszkodzenia jeśli System ulegnie awarii podczas importu, możesz zatrzymać Pg, ustawić fsync=off, uruchomić Pg, wykonać import, następnie (żywotnie) zatrzymać Pg i ponownie ustawić fsync=on. Zobacz konfiguracja WAL . nie rób tego, jeśli są już jakieś dane, na których Ci zależy w dowolnej bazie danych w Twojej instalacji PostgreSQL. jeśli ustawisz fsync=off Możesz również ustawić full_page_writes=off; ponownie pamiętaj, aby włączyć ją ponownie po zaimportowaniu, aby zapobiec uszkodzeniu bazy danych i utracie danych. Zobacz nietrwałe ustawienia W Pg Instrukcja obsługi.

Należy również spojrzeć na strojenie systemu:

  • Użyj dobrej jakości dysków SSD do przechowywania jak najwięcej. Dobre dyski SSD z niezawodnymi, zabezpieczonymi energią pamięciami podręcznymi odpisów sprawiają, że szybkość zatwierdzania jest niewiarygodnie szybsza. Są mniej korzystne, gdy zastosujesz się do powyższych porad-co zmniejsza spłukiwanie 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 masz to gdzieś przechowywanie danych.

  • Jeśli używasz RAID 5 lub RAID 6 do bezpośredniej pamięci masowej, zatrzymaj się teraz. Utwórz kopię zapasową danych, Zmień macierz RAID na RAID 10 i spróbuj ponownie. RAID 5/6 są beznadziejne dla wydajności zapisu zbiorczego - chociaż dobry kontroler RAID z dużą pamięcią podręczną może pomóc.

  • Jeśli masz możliwość korzystania ze sprzętowego kontrolera RAID z dużą bateryjną pamięcią podręczną zapisu, może to naprawdę poprawić wydajność zapisu dla obciążeń z dużą ilością pamięci podręcznej / align = "left" / Nie pomaga to tak bardzo, jeśli używasz commit asynchroniczny z commit_delay lub jeśli robisz mniej dużych transakcji podczas ładowania zbiorczego.

  • Jeśli to możliwe, przechowuj WAL (pg_xlog) na oddzielnym dysku / macierzy dyskowej. Nie ma sensu używać oddzielnego systemu plików na tym samym dysku. Ludzie często decydują się na użycie pary RAID1 dla WAL. Ponownie, ma to większy wpływ na systemy z wysoką szybkością zatwierdzania i ma niewielki wpływ, jeśli używasz niezalogowanej tabeli jako obciążenia danych cel.

Możesz być również zainteresowany Optimise PostgreSQL for fast testing.

 380
Author: Craig Ringer,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:30

Użyj COPY table TO ... WITH BINARY, co według dokumentacji jest " nieco szybsze niż formaty tekstowe i CSV ."Zrób to tylko, jeśli masz miliony wierszy do wstawienia i jeśli czujesz się komfortowo z danymi binarnymi.

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

 10
Author: Mike T,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:55:03

Oprócz doskonałego postu Craiga Ringera i postu depesz na blogu, jeśli chcesz przyspieszyć swoje wstawki poprzez interfejs ODBC (psqlodbc) za pomocą wstawek prepared-statement wewnątrz transakcji, jest kilka dodatkowych rzeczy, które musisz zrobić, aby działać szybko:

  1. Ustaw poziom-of-rollback-on-errors na "Transaction", określając Protocol=-1 w łańcuchu połączeń. Domyślnie psqlodbc używa poziomu "Statement", który tworzy punkt SAVEPOINT dla każdej instrukcji zamiast całej transakcji, dzięki czemu wkładki są wolniejsze.
  2. użyj instrukcji przygotowanych po stronie serwera, określając UseServerSidePrepare=1 w łańcuchu połączenia. Bez tej opcji Klient wysyła całą instrukcję insert wraz z każdym wstawianym wierszem.
  3. Wyłącz automatyczne zatwierdzanie każdej instrukcji za pomocą SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
  4. po wstawieniu wszystkich wierszy Zatwierdź transakcję za pomocą SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);. Nie ma potrzeby jawnego otwierania transakcji.

Niestety psqlodbc "implementuje" SQLBulkOperations poprzez wydanie serii nieprzygotowanych poleceń insert, tak aby osiągnąć najszybszy insert trzeba ręcznie wykonać powyższe kroki.

 8
Author: Maxim Egorushkin,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-22 11:43:58

Spędziłem dziś około 6 godzin nad tą samą sprawą. Wkładki idą z "zwykłą" prędkością (mniej niż 3 sekundy na 100k) do 5mi (z całkowitej 30mi) rzędów, a następnie wydajność spada drastycznie (aż do 1min na 100k).

Nie wymienię wszystkich rzeczy, które nie działały i nie trafiły prosto na spotkanie.

I upuściłem klucz podstawowy na stół docelowy (który był GUID) i moje 30mi lub wiersze szczęśliwie płynęły do miejsca docelowego ze stałą prędkością mniejszą niż 3 sekundy na 100K.

 2
Author: Dennis,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-10 23:19:26

Dla optymalnej wydajności wstawiania wyłącz indeks, jeśli jest to opcja dla Ciebie. Poza tym pomocny jest również lepszy sprzęt (Dysk, Pamięć)

 0
Author: Icarus,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-30 22:43:06

Napotkałem również ten problem z wydajnością wstawiania. Moim rozwiązaniem jest spawn niektórych procedur go, aby zakończyć pracę wstawiania. W międzyczasie, {[1] } Należy podać odpowiedni numer, w przeciwnym razie zostanie zaalarmowane zbyt wiele otwartych błędów 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()

Prędkość ładowania jest znacznie szybsza dla mojego projektu. Ten fragment kodu dał pomysł, jak to działa. Czytelnicy powinni być w stanie łatwo go modyfikować.

 0
Author: Patrick,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-13 21:29:04