PostgreSQL, Fremdschlüssel, Einfügegeschwindigkeit & Django

Question

Vor ein paar Tagen stieß ich auf ein unerwartetes Leistungsproblem mit einer ziemlich standardmäßigen Django-Einrichtung. Für ein bevorstehendes Feature müssen wir stündlich eine Tabelle neu generieren, die etwa 100k Zeilen Daten enthält, 9M auf der Festplatte, 10M Indizes laut pgAdmin.

Das Problem ist, dass das Einfügen der Dateien, egal mit welcher Methode, buchstäblich Ewigkeiten dauert, bis zu 3 Minuten bei 100 % Festplattenauslastung. Das ist nichts, was man an einem Produktionsstandort haben möchte. Dabei spielt es keine Rolle, ob die Einfügevorgänge in einer Transaktion, durch einfaches Einfügen, mehrzeiliges Einfügen, COPY FROM oder sogar INSERT INTO t1 SELECT * FROM t2 ausgeführt wurden.

Nachdem ich festgestellt hatte, dass dies nicht die Schuld von Django ist, folgte ich einer Trial-and-Error-Route, und siehe da, das Problem verschwand, nachdem ich alle Fremdschlüssel gelöscht hatte! Statt 3 Minuten dauerte die Ausführung von INSERT INTO SELECT FROM weniger als eine Sekunde, was bei einer Tabelle <= 20M auf der Festplatte nicht allzu überraschend ist. Was ist Seltsam ist, dass PostgreSQL es schafft, Einfügungen um das 180-fache zu verlangsamen, indem es nur 3 Fremdschlüssel verwendet.

Oh, die Festplattenaktivität war reines Schreiben, da alles im RAM zwischengespeichert wird; nur Schreibvorgänge gehen an die Festplatten. Es sieht so aus, als ob PostgreSQL sehr hart arbeitet, um jede Zeile in den verwiesenen Tabellen zu berühren, da 3MB/sec * 180s viel mehr Daten sind als die 20MB, die diese neue Tabelle auf der Platte braucht. Kein WAL für den 180s Fall, ich testete direkt in psql, in Django, füge ~50% Overhead für WAL Logging hinzu. Versucht @commit_on_success, gleiche Langsamkeit, ich hatte sogar multi row insert und COPY FROM mit psycopg2 implementiert. Das ist eine weitere seltsame Sache, wie können 10M Einfügungen im Wert von > 10x 16M Log-Segmente erzeugen?

Tabellenlayout: id serial primary, eine Reihe von int32, 3 Fremdschlüssel zu

• kleine Tabelle, 198 Zeilen, 16k auf Platte
• große Tabelle, 1.2M Zeilen, 59 Daten + 89 Index MB auf Platte
• große Tabelle, 2.2M Zeilen, 198 + 210MB

Also, bin ich dazu verdammt, entweder Drop die Fremdschlüssel manuell oder verwenden Sie die Tabelle in einer sehr un-Django Weise durch die Definition von Speichern bla_id x3 und überspringen Sie mit models.ForeignKey? Ich würde gerne über einige magische Gegenmittel / pg-Einstellung zu hören, dies zu beheben.

Answer 1

100.000 FK-Prüfungen sollten etwa 2-5 Sekunden dauern, wenn sie nicht auf IO-Lesevorgänge warten müssen. Viel langsamer als das Einfügen in die Tabelle, aber viel schneller als die von Ihnen angegebene Zeit.

Überprüfen Sie, ob alle Ihre Fremdschlüssel INDEXED sind:

(Ich spreche von einem Index für die referenzierte Spalte, nicht für die referenzierende Spalte, verstanden?)

Wenn products.category_id REFERENCES category(id) und es keinen Index auf category.id gibt, muss jedes Mal, wenn ein FK überprüft werden soll, die Tabelle durchsucht werden.

Um herauszufinden, welche nicht, machen Sie Ihren Einsatz mit 1 FK, dann 2 FKs... Sie werden herausfinden, welche verantwortlich ist.

Und ja, wenn Sie die Tabelle abschneiden, ist es schneller, auch alle Beschränkungen und Indizes zu löschen und sie nach der Masseneinfügung neu zu erstellen.

Answer 2

Das scheint mir ein normales Verhalten zu sein. Wenn die Tabelle Indizes, Fremdschlüssel oder Trigger hat, müssen diese beim Masseneinfügen in eine Datenbank Zeile für Zeile überprüft werden. Normalerweise sollte man sie also löschen, die Einfügungen durchführen (wenn möglich mit Kopieren) und dann die Indizes, Fremdschlüssel und Trigger neu erstellen.

Auf dieser Seite in den Dokumenten finden Sie weitere Details zu autocommit, maintenance_work_mem und checkpoint_segments, die Sie einstellen können: http://www.postgresql.org/docs/8.4/interactive/populate.html

Answer 3

Vielleicht haben Sie einen Trigger in Ihrer Tabelle, von dem Sie nichts wissen oder an den Sie sich nicht erinnern, der bei jeder eingefügten/gelöschten Zeile ausgelöst wird. Können Sie mit "psql" eine Verbindung zu einer Datenbank herstellen? Wenn ja, dann analysieren Sie die Ausgabe von " \d + table_name" für alle Ihre Tabellen.

Sie können Ihre Datenbank auch dumpen, importieren und erneut dumpen. Vergleichen Sie die Dumps, um zu prüfen, ob sich der Inhalt einer anderen Tabelle geändert hat.

Answer 4

Ich hatte vergessen, dass EXPLAIN ANALYZE INSERT INTO bleh ... Ihnen das Timing aller Insert-Trigger anzeigt.