PostgreSQL: Binärer Cursor für INSERT?

[nobody0]

Ich habe das Problem, dass ich in eine Datenbank rund 100 Mio. Zeilen einfügen muß und dass es mit INSERT sehr lange dauert (einige Tage). Dabei ist nicht das importierende C-Programm der Flaschenhals, sondern PostgreSQL, wie top zeigt.
Deshalb brauche ich sowas wie einen binären Cursor, aber anscheinend gibt's das nicht für INSERT. Wie kann man trotzdem schnell Zeilen in PostgreSQL einfügen? :confused:

[comrad]

Hi,

es gibt noch eine Art "COPY" als Alternative zum Insert. Für 8.1 gibts hier Infos:

http://www.postgresql.org/docs/8.1/interactive/sql-copy.html

Weiss aber nicht 100% ob es das ist, was du suchst.

Gruss,
comrad

PS: Aber evtl müsst ihr einfach mehrere Tage investieren, 100 Mio Datensätze sind ja nun auch kein Zuckerschlecken.

[elrond]

wenn man sich ein pg_dump output ansieht, wird da ein copy from benutzt

das sieht dann in etwa so aus:



COPY bm (bmid, bmtypid, mdate, statusid) FROM stdin;
11596 12 2006-01-19 10:47:49.997858 12
11597 0 2006-01-19 11:00:47.274599 12
11603 152 2006-01-19 15:26:57.34557 12
11605 18 2006-01-19 18:47:08.311474 12
11611 224 2006-01-19 19:16:00.86094 18
11612 225 2006-01-19 19:16:02.439097 18
11617 164 2006-01-19 20:01:36.469095 18


sollte deutlich schneller sein als einzelne inserts

[nobody0]

Hm, da bleibt wohl nur zwei Wochen mit INSERT einzuplanen, denn COPY reicht nicht, da die Daten-Dateien mit zip (zlib) komprimiert sind, nicht alles drinn steht, was in PostgreSQL rein soll und zudem die Werte angepasst werden müssen, damit mit Integers entsprechend 1/10 Cent gerechnet werden kann.

Notfalls könnte ich auch 100 INSERTS parallel laufen lassen mittels Posix-Threads. Damit sollte es auch deutlich schneller gehen.

[Christoph]

Aslo top ist veilleicht nicht das geeignete Mittel um den Flaschenhals zu ermitteln. Unter der Annahme, dass Du über Kenntnisse von Datenbanken verfügst, seien hier mal ein paar Anregungen für Gründe der schlechten Performance gegeben (zwei Wochen für 100 Millionen Datensätze kann nicht sein, da musst Du dich schon ziemlich ungeschickt anstellen):

1) Nach wieviel Statements machst Du ein COMMIT? Etwa nach jedem??

2) Hast Du Indizes definiert? Drop die mal vor dem Insert und ergänze sie hinterher wieder.

3) Was für Indizes gilt, gilt auch für Integrity Constraints.

4) Warum geht COPY nicht?? Lässt Du die Datenbank beim Insert wilde Berechnungen machen?

Nur mal als Anregung,

Christoph

[elrond]

1) Nach wieviel Statements machst Du ein COMMIT? Etwa nach jedem??



das ist auch meine nächste idee gewesen. wenn schon nicht copy, dann aber unbedingt ordentlich mit transaktionen arbeiten und nach 10 oder auch 100k ein commit absetzten...

ich hab vor einer weile mal ein fremdes importrscript vor mir gehabt über das alle gemeckert haben dass es viel zu langsam sei... das war genau das problem :o

mach 10 min war's ungefähr 10mal so schnell :D

[nobody0]

1) Nach wieviel Statements machst Du ein COMMIT? Etwa nach jedem??


Nein, nie. Das COMMIT ist eingespart worden.




2) Hast Du Indizes definiert? Drop die mal vor dem Insert und ergänze sie hinterher wieder.

3) Was für Indizes gilt, gilt auch für Integrity Constraints.


Ok, Indizes weg geht noch, aber die Daten sollen schon beim Import überprüft werden.




4) Warum geht COPY nicht?? Lässt Du die Datenbank beim Insert wilde Berechnungen machen?


Nein, nur sowas wie mit 100 Multiplizieren und nach Int casten.

[mwanaheri]

Nein, nie. Das COMMIT ist eingespart worden.


Ich gebe zu, ich habe nicht wirklich viel Ahnung davon, aber ich könnte mir vorstellen, dass das das dbms ganz schön fordert, denn es muss ja alles vorrätig halten, um ein rollback durchführen zu können.



Nein, nur sowas wie mit 100 Multiplizieren und nach Int casten.

bei so vielen Zeilen macht das natürlich ganz schön viel Arbeit. die könnte man evtl verschieben. entweder, indem man sie mit einem script erst mal in der Datei machen lässt, bevor man sie importiert oder, indem man die betreffenden Zahlen in eine temporäre spalte importiert und die dann später multipliziert und gecasted in die eigentliche Zielspalte bringt.
ersteres ist sicher schöner, zweiteres spart nicht wirklich Arbeit für das dbms, man kann aber den zweiten Schritt später machen und die Arbeit etwas verteilen.

[nobody0]

Ok, dann mache ich mal COMMIT nur nach dem Einlesen von jeder der rund 50.000 Dateien.
Nach ein paar Millionen Zeilen ohne Commit streikt PostgreSQL auch ...

[nobody0]

Mir ist auch aufgefallen, das der Import heftige Festplatten-Aktivität verursacht, obwohl nur relativ wenig geschrieben wird. Anscheinend ist das der Flaschenhals.
Wie kann man PostgreSQL dazu bekommen, weniger oft auf Platte zu schreiben? :confused:

Eine RAMdisk als Workaround scheidet aus mangels RAM (nur 1,5 GB).

[Christoph]

Mir ist auch aufgefallen, das der Import heftige Festplatten-Aktivität verursacht, obwohl nur relativ wenig geschrieben wird. Anscheinend ist das der Flaschenhals.
Wie kann man PostgreSQL dazu bekommen, weniger oft auf Platte zu schreiben?

Das klingt so, als wenn Du nach jedem Statement ein Commit machst, z.B. weil Du mit psql importierst und versehentlich autocommit eingescahltet hast?

[nobody0]

Das klingt so, als wenn Du nach jedem Statement ein Commit machst, z.B. weil Du mit psql importierst und versehentlich autocommit eingescahltet hast?

Der Import läuft mit einem C-Programm und über

result = PQexec (dbhandle, abfrage);

Ich werde demnächst commit_delay und bgwriter_delay auf Maximum setzen; vielleicht liegt es daran.

[nobody0]

So, mit den Änderungen, also

Eintragen in /var/lib/pgsql/data/postgresql.conf:
bgwriter_delay = 10000
commit_delay = 100000
wal_files = 0 # range 0-64
wal_buffers = 128 # range 4-Max
commit_delay = 100000 # range 0-100000
commit_siblings = 1000 # range 1-1000
checkpoint_segments = 128 # in logfile segments (16MB each), min 1
checkpoint_timeout = 300 # in seconds, range 30-3600
fsync = false # turns forced synchronization on or off

und ohne Speicherleck (d. h. mit PQclear, das in "Datenbank-Anwendungen mit PostgreSQL" und dessen Beispielen fehlt) funktioniert es endlich: Mit rund 1000 INSERTs/s bei 5 Debugging-Ausgaben je INSERT und relativ geringer Platten-Belastung ist es endlich so, wie es sein sollte.

Wie ich an der Platz-Belegung sehe, wird aber der Platz-Bedarf für die Daten um 200 % erhöht; aus 3 GB CVS-Daten werden durch den Import in PostgreSQL ca. 9 GB. Derzeit habe ich aber noch keinen Rechner, auf dem ich eine so große RAMDISK zur Verfügung habe.