Anzeige:
Ergebnis 1 bis 13 von 13

Thema: PostgreSQL: Binärer Cursor für INSERT?

Hybrid-Darstellung

Vorheriger Beitrag Vorheriger Beitrag   Nächster Beitrag Nächster Beitrag
  1. #1
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650

    Question PostgreSQL: Binärer Cursor für INSERT?

    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?

  2. #2
    Registrierter Benutzer
    Registriert seit
    11.03.2001
    Ort
    Delmenhorst
    Beiträge
    118
    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/i.../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.
    Holarse.de - Spielen unter Linux

  3. #3
    Registrierter Benutzer Avatar von elrond
    Registriert seit
    04.10.2001
    Ort
    potsdam
    Beiträge
    881
    wenn man sich ein pg_dump output ansieht, wird da ein copy from benutzt

    das sieht dann in etwa so aus:

    Code:
    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
    "Um die Welt zu ruinieren, genügt es, wenn jeder seine Pflicht tut." (Winston Churchill)

  4. #4
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650
    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.

  5. #5
    Registrierter Benutzer
    Registriert seit
    22.06.1999
    Beiträge
    677
    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

  6. #6
    Registrierter Benutzer Avatar von elrond
    Registriert seit
    04.10.2001
    Ort
    potsdam
    Beiträge
    881
    Zitat Zitat von Christoph

    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

    mach 10 min war's ungefähr 10mal so schnell
    "Um die Welt zu ruinieren, genügt es, wenn jeder seine Pflicht tut." (Winston Churchill)

  7. #7
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650
    Zitat Zitat von Christoph
    1) Nach wieviel Statements machst Du ein COMMIT? Etwa nach jedem??
    Nein, nie. Das COMMIT ist eingespart worden.


    Zitat Zitat von Christoph
    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.


    Zitat Zitat von Christoph
    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.
    Geändert von nobody0 (19-06-2006 um 19:17 Uhr)

  8. #8
    Registrierter Benutzer Avatar von mwanaheri
    Registriert seit
    28.10.2003
    Ort
    Bayreuth
    Beiträge
    569
    Zitat Zitat von nobody0
    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.
    Das Ziel ist das Ziel.

  9. #9
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650
    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 ...

  10. #10
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650
    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?

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

  11. #11
    Registrierter Benutzer
    Registriert seit
    22.06.1999
    Beiträge
    677
    Zitat Zitat von 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?
    Das klingt so, als wenn Du nach jedem Statement ein Commit machst, z.B. weil Du mit psql importierst und versehentlich autocommit eingescahltet hast?

  12. #12
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650
    Zitat Zitat von Christoph
    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.

  13. #13
    Registrierter Benutzer
    Registriert seit
    23.03.2001
    Beiträge
    650
    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.

Lesezeichen

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •