webentwicklung-frage-antwort-db.com.de

So beschleunigen Sie die Einfügungsleistung in PostgreSQL

Ich teste die Einfügungsleistung von Postgres. Ich habe eine Tabelle mit einer Spalte mit der Nummer als Datentyp. Es gibt auch einen Index. Ich habe die Datenbank mit dieser Abfrage gefüllt:

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

Ich habe mit der obigen Abfrage 4 Millionen Zeilen sehr schnell 10.000 auf einmal eingefügt. Nachdem die Datenbank 6 Millionen Zeilen erreicht hatte, ging die Leistung alle 15 Minuten drastisch auf 1 Million Zeilen zurück. Gibt es einen Trick, um die Einfügeleistung zu erhöhen? Ich benötige für dieses Projekt eine optimale Einfügeleistung.

Verwenden von Windows 7 Pro auf einem Computer mit 5 GB RAM.

176
Luke101

Siehe Füllen einer Datenbank im PostgreSQL-Handbuch, depeszs Artikel in der gewohnten Qualität zum Thema und this SO Frage .

(Beachten Sie, dass es sich bei dieser Antwort um das Massenladen von Daten in eine vorhandene Datenbank oder das Erstellen einer neuen handelt. Wenn Sie daran interessiert sind, stellen Sie die Leistung der Datenbank mit _pg_restore_ oder psql wieder her. Ausführung der Ausgabe von _pg_dump_, ein Großteil davon entfällt, da _pg_dump_ und _pg_restore_ bereits Trigger und Indizes erstellen, nachdem ein Schema + eine Datenwiederherstellung abgeschlossen wurden. .

Es gibt viel zu tun. Die ideale Lösung wäre, in eine Tabelle UNLOGGED ohne Indizes zu importieren, diese dann in "protokolliert" zu ändern und die Indizes hinzuzufügen. Leider gibt es in PostgreSQL 9.4 keine Unterstützung für das Ändern von Tabellen von UNLOGGED zu protokolliert. 9.5 fügt _ALTER TABLE ... SET LOGGED_ hinzu, um dies zu ermöglichen.

Wenn Sie Ihre Datenbank für den Massenimport offline schalten können, verwenden Sie _pg_bulkload_ .

Andernfalls:

  • Deaktivieren Sie alle Trigger auf dem Tisch

  • Löschen Sie die Indizes, bevor Sie den Import starten, und erstellen Sie sie anschließend neu. (Es dauert viel weniger Zeit, um einen Index in einem Durchgang zu erstellen, als dasselbe fortlaufend hinzugefügt wird, und der resultierende Index ist viel kompakter).

  • Wenn Sie den Import innerhalb einer einzelnen Transaktion ausführen, können Sie Fremdschlüsseleinschränkungen löschen, den Import ausführen und die Einschränkungen neu erstellen, bevor Sie einen Commit durchführen. Tun Sie dies nicht, wenn der Import auf mehrere Transaktionen aufgeteilt ist, da Sie möglicherweise ungültige Daten eingeben.

  • Verwenden Sie nach Möglichkeit COPY anstelle von INSERT

  • Wenn Sie COPY nicht verwenden können, sollten Sie ggf. mehrwertige INSERT verwenden. Sie scheinen das schon zu tun. Versuchen Sie jedoch nicht, zu viele Werte in einem einzigen VALUES aufzulisten; Diese Werte müssen ein paar Mal in den Speicher passen. Halten Sie sie also auf einige Hundert pro Anweisung.

  • Batchen Sie Ihre Beilagen in explizite Transaktionen, indem Sie Hunderttausende oder Millionen von Beilagen pro Transaktion ausführen. Es gibt kein praktisches Limit für AFAIK, aber durch das Batching können Sie einen Fehler beheben, indem Sie den Start jeder Charge in Ihren Eingabedaten markieren. Wieder scheinen Sie dies bereits zu tun.

  • Verwenden Sie _synchronous_commit=off_ und einen riesigen _commit_delay_, um die Kosten für fsync () zu senken. Dies hilft jedoch nicht viel, wenn Sie Ihre Arbeit in große Transaktionen aufgeteilt haben.

  • INSERT oder COPY parallel von mehreren Verbindungen. Wie viele, hängt vom Festplattensubsystem Ihrer Hardware ab. Als Faustregel gilt, dass Sie eine Verbindung pro physischer Festplatte benötigen, wenn Sie direkt angeschlossenen Speicher verwenden.

  • Setzen Sie einen hohen Wert für _checkpoint_segments_ und aktivieren Sie _log_checkpoints_. Sehen Sie sich die PostgreSQL-Protokolle an und vergewissern Sie sich, dass Checkpoints nicht zu häufig auftreten.

  • Wenn und nur wenn Sie nichts dagegen haben, Ihren gesamten PostgreSQL-Cluster (Ihre Datenbank und alle anderen im selben Cluster) durch eine katastrophale Beschädigung zu verlieren, wenn das System während des Imports abstürzt, können Sie Pg stoppen, _fsync=off_ setzen, Pg starten Führen Sie den Import aus, stoppen Sie dann (unbedingt) Pg und setzen Sie _fsync=on_ erneut. Siehe WAL-Konfiguration . Tun Sie dies nicht, wenn Ihre PostgreSQL-Installation bereits Daten enthält, die Sie interessieren. Wenn Sie _fsync=off_ setzen, können Sie dies auch tun set _full_page_writes=off_; Denken Sie auch hier daran, es nach dem Import wieder einzuschalten, um Datenbankbeschädigungen und Datenverlust zu vermeiden. Siehe nicht dauerhafte Einstellungen im Pg-Handbuch.

Sie sollten sich auch die Optimierung Ihres Systems ansehen:

  • Verwenden Sie hochwertige SSDs, um so viel wie möglich zu speichern. Gute SSDs mit zuverlässigen, stromgeschützten Write-Back-Caches sorgen für unglaublich schnellere Commit-Raten. Sie sind weniger nützlich, wenn Sie den obigen Hinweisen folgen - wodurch das Löschen der Festplatte/die Anzahl der fsync() s verringert wird - können aber dennoch eine große Hilfe sein. Verwenden Sie keine billigen SSDs ohne ausreichenden Stromausfallschutz, es sei denn, Sie möchten Ihre Daten nicht aufbewahren.

  • Wenn Sie RAID 5 oder RAID 6 für direkt angeschlossenen Speicher verwenden, stoppen Sie jetzt. Sichern Sie Ihre Daten, strukturieren Sie Ihr RAID-Array auf RAID 10 um und versuchen Sie es erneut. RAID 5/6 ist für die Leistung beim Massenschreiben hoffnungslos - obwohl ein guter RAID-Controller mit großem Cache hilfreich sein kann.

  • Wenn Sie die Option haben, einen Hardware-RAID-Controller mit einem großen batteriegepufferten Write-Back-Cache zu verwenden, kann dies die Schreibleistung für Workloads mit vielen Commits erheblich verbessern. Dies ist weniger hilfreich, wenn Sie async commit mit commit_delay verwenden oder beim Massenladen weniger große Transaktionen ausführen.

  • Wenn möglich, speichern Sie WAL (_pg_xlog_) auf einem separaten Datenträger/Datenträgerarray. Es hat wenig Sinn, ein separates Dateisystem auf derselben Festplatte zu verwenden. Menschen entscheiden sich häufig für die Verwendung eines RAID1-Paares für WAL. Dies hat wiederum größere Auswirkungen auf Systeme mit hohen Festschreibungsraten und nur geringe Auswirkungen, wenn Sie eine nicht protokollierte Tabelle als Datenladeziel verwenden.

Sie könnten auch interessiert sein an Optimieren von PostgreSQL für schnelle Tests .

433
Craig Ringer

Verwenden COPY table TO ... WITH BINARY ist laut Dokumentation " etwas schneller als die Text- und CSV-Formate ". Tun Sie dies nur, wenn Sie Millionen von Zeilen einfügen müssen und mit Binärdaten vertraut sind.

Hier ist ein Beispielrezept in Python, das psycopg2 mit Binäreingabe verwendet .

12
Mike T

Zusätzlich zu den hervorragenden Posts von Craig Ringer und dem Blogpost von depesz, wenn Sie Ihre Einfügungen durch die Schnittstelle ODBC ( psqlodbc ) beschleunigen möchten, indem Sie Inserts mit vorbereiteten Anweisungen verwenden Bei einer Transaktion müssen Sie einige zusätzliche Schritte ausführen, damit sie schnell funktioniert:

  1. Setzen Sie die Ebene des Rollbacks bei Fehlern auf "Transaktion", indem Sie Protocol=-1 In der Verbindungszeichenfolge angeben. Standardmäßig verwendet psqlodbc die Ebene "Anweisung", wodurch ein SAVEPOINT für jede Anweisung und nicht für eine gesamte Transaktion erstellt wird, wodurch Einfügungen langsamer werden.
  2. Verwenden Sie serverseitig vorbereitete Anweisungen, indem Sie UseServerSidePrepare=1 In der Verbindungszeichenfolge angeben. Ohne diese Option sendet der Client die gesamte Einfügeanweisung zusammen mit jeder eingefügten Zeile.
  3. Deaktivieren Sie die automatische Festschreibung für jede Anweisung mit SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
  4. Sobald alle Zeilen eingefügt wurden, bestätigen Sie die Transaktion mit SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);. Es ist nicht erforderlich, eine Transaktion explizit zu öffnen.

Leider "implementiert" psqlodbc SQLBulkOperations, indem es eine Reihe von nicht vorbereiteten Einfügeanweisungen ausgibt, so dass man die obigen Schritte manuell codieren muss, um die schnellste Einfügung zu erzielen.

11

Ich habe heute ungefähr 6 Stunden mit dem gleichen Thema verbracht. Einfügungen werden mit einer "normalen" Geschwindigkeit (weniger als 3 Sekunden pro 100 KB) bis zu 5 MB (von insgesamt 30 MB) Zeilen ausgeführt, und dann sinkt die Leistung drastisch (bis auf 1 Minute pro 100 KB).

Ich werde nicht alle Dinge auflisten, die nicht funktioniert haben und direkt zum Fleisch schneiden.

Ich habe einen Primärschlüssel abgelegt in der Zieltabelle (die eine GUID war) und meine 30MI oder Zeilen flossen glücklich mit einer konstanten Geschwindigkeit von weniger als 3 Sekunden pro 100 KB zu ihrem Ziel.

7
Dennis

Deaktivieren Sie den Index, wenn dies für Sie eine Option ist, um eine optimale Einfügeleistung zu erzielen. Ansonsten ist auch eine bessere Hardware (Festplatte, Speicher) hilfreich

1
Icarus