Riesiges Sqlfile über psql einlesen

Hallo zusammen.

Ich hoffe ich bin hier in der richtigen Rubrik gelandet.

Ich habe ein riesiges sqlfile (4,5 GB) das per psql von der Konsole eingelesen werden muss.

psql -h HOST -p PORT -U USER -q -o /dev/null -f file.sql  2>&1

Nach 10 minuten kommt dann

psql:file.sql:11563401: cannot allocate memory for output buffer

Ich habe noch keine große Erfahrung mit postgres aber vielleicht ist es wichtig zu wissen das das ganze file eine Transaction ist. Im file.sql sind auch keine “plain” SQL Befehle wie z.B. INSERT, UPDATE, DELETE sondern alles aufrufe von pl/pgsql funktionen. Z.B. select * from insertNewUa(…)

Kleinere file.sql (20 - 50 MB) funktionieren bestens

Meine Frage lautet nun

  • Was will mir psql mit dieser Fehlermeldung sagen ?
  • Wie löse ich das Problem

Vielen Dank schonmal im voraus

Sigh

Welche Version?

Auf https://projects.commandprompt.com/public/pgsql/changeset/12608 finde ich einen Patch für 7.1 zu diesem Problem, ist Deine Version so alt?


Andreas

Vielleicht wäre es auch praktisch STDERR nicht unbedingt Richtung /dev/null zu schicken.

Wie sieht die Speicherkonfiguration deiner Datenbank aus?
Welche PostgreSQL Version ist das?
Was für Hardware ist das?

Ist das ein Backup, das zurückgespielt werden soll?

der fehler passiert auf dem client -> psql -> pqlib

PostgreSQL Source Code: src/interfaces/libpq/fe-misc.c Source File

bei der zeile 11563401 deines file.sql …

bei 2 gig address-raum für 32 bit prozesse darf man von durchschnittlicher zeilenbreite ca. 172 zeichen ausgehen … was mir realistisch bei deinem fall erscheint …

soweit ich das im quellcode erkennen konnte tritt der fehler in

[url=http://doxygen.postgresql.org/libpq-int_8h.html#0e2b94a3b894918aba366784d2064c1b]00295[/URL] [url=http://doxygen.postgresql.org/fe-misc_8c.html#0e2b94a3b894918aba366784d2064c1b]pqCheckOutBufferSpace[/URL](size_t bytes_needed, [url=http://doxygen.postgresql.org/structpg__conn.html]PGconn[/URL] *conn)

diese funktion vergroessert den ausgabe-puffer fuer die connection - wenn daten/aufrufe ans backend gesendet werden.

wenn psql -f das gesamte script-file als ganzes an das backend sendet wuerde dies u.U. erklaeren,
warum das nen out-of-memory produziert. ich habe zwar gesehen, dass der code der wenigen aufrufer
dieser fkt. zwischendurch ein flush(…) vorsieht (also speicher bereits gesendeter daten freigeben) da stand aber ein “try” dabei …

dann wäre also interessant ob es

  1. ueberhaupt ein limit fuer die groesse von script-files gibt
  2. unter welchen bedingungen es ein limit gibt

aus der doku habe ich leider nichts erfahren koennen

Postgres Version: 8.3.3
System: Debian Etch
Hardware: Neue aber nix Spezielles

Es ist kein Backup das zurück gespielt werden soll. Es werden tägl. Daten erhoben und verarbeitet. Daraus resultiert dann so ein file.sql das dann eingespielt werden soll.

Memory Settings

shared_buffers = 57MB
work_mem = 512MB
maintenance_work_mem = 1024MB
max_stack_depth = 7MB
max_fsm_pages = 204800
effective_cache_size = 2048MB


Antworten auf Symbionts Fragen klingen so als ob Sie auch eine Antwort auf mein Problem wären :slight_smile:

nein - keine direkten antworten - aber vielleicht hinweise um eine antwort zu finden …

ich wuerde mich auf den client konzentrieren … der fehler tritt nicht im backend auf und hat auch nichts mit evtl. limits des backends zu tun …

ich habe mal versucht in die richtung “grosse scripte” zu recherchieren … da find ich aber nix gescheites …

empfehlen wuerde ich einen alternativen weg zu erdenken wie du die “täglichen” daten in die pg db nudelst …

am einfachsten waere es wohl das script zu splitten - ginge das? - oder muss das script zwingend in einer transaktion zum server flutschen … das waere schon eine heftige aktion

deine backend memory einstellungen sehen irgendwie ziemlich zerfrickelt aus … hast daran rumgespielt? (hat aber eher unwahrscheinlich mit dem problem zu tun)

[EDIT] Nachtrag …

selbst wenn psql das script “kommandoweise” an das backend sendet, kann das ersehnte “flush” welches den speicher fuer den ausgabe-puffer freigibt - erst irgendwann “zum schluss” zum tragen kommen. interessant wäre, ob das ganze auf einem anderen OS (windows g) - weil anderes compilat - andere systemfunktionen - etc.) funktioniert. hast du schonmal versucht das auf einem anderen OS ablaufen zu lassen?

Nein kein anderes OS versucht.

client hat btw auch Debian etch.

Ich bin nun auch als workaround zum Thema splitten gekommen. Ab welcher Größe (in byte) es kracht versuche ich mit “try and error” heraus zu finden.

und ja an den memory limits habe ich herumgefrickelt.

das system wird kein riesiges multiusersystem sein sondern man hat wenige user (connections) die dann jeweils schnelle Antwort über große mengen an Daten bekommen sollen. Nachdem ich dutzende von Optimierungstutorials gelesen habe (und ausprobiert) waren diese Settings bisher die besten.

abfragen die mit den standard settings 14 mins dauerten gehen nun in 10 sec. :slight_smile:


falls es da aber grobe Schnitzer geben solle bin ich für anmerkungen immer offen (auch wenn das off topic wäre )

also wie ich schon schrieb - der fehler riecht nach ner kollision an der 2 GB grenze fuer 32 bit Prozesse - ich muss zugeben, das ich nicht hunderprozentig weiss, ob diese auch fuer debian OS gilt.

wenn du da experimentieren willst - versuch dich mal daran zu orientieren …

d.h. aber nicht, dass ein 2 GB script file “genau” 2 GB Speicher fuer den Puffer beansprucht … die funktion allociert ja auch Speicher fuer notwendigen Overhead des PG protokolls zum Backend …

Wenn es nun aber tatsaechlich “daran” liegt, solltest du mit splitten auf 1GB “auf der sicheren Seite sein” …

gotcha :wink:

hast mich erwischt.

habe das file.sql in 10.000.000 Zeilen zerlegen lassen und siehe da ^^

1 file ist ~ 2,2 GB

teste das einfach mal ansonsten splitte ich halt bei 5.000.000 Zeilen

gebe Feedback ob das 2,2GB durchläuft

Warum ich das eigentlich vermeiden wollte ist das ich eine extra tabelle habe, in der jedes file.sql quasi nen eintrag mit einer id (datum etc.) bekommt. diese tabelle wächst dann nichtmehr um 1 pro tag Sondern um ~ 10 pro tag (split halt)

aber das wird mich und meine datenbank sicher nicht umbringen :slight_smile:

ach - grau ist alle theorie …

es koennte durchaus interessant sein, das psql mal auf einem anderen OS mit so grossen dateien laufen zu lassen …

vielleicht gibt es auch eine 64 bit version vom client ? vielleicht ginge das dann auch …

das sind nur so ideen - die man verfolgen koennte - wenn du schonmal beim experimentieren bist g

Mit den Einstellungen wird es keinen Spaß machen die Datenbank zu betreiben. Da ist eine Menge falsch.

Wenn ihr solche großen Datenmengen verarbeitet: wieso nutzt ihr kein 64-Bit System?

hab den wunsch nach einem 64 bit client zum testen an meine admins weiter gegeben. sobald ich da was zum testen habe mache ich das mal und gebe auch davon bescheid

Aber an sich ist da wohl viel Verbesserungspotential.

Wie schaut denn so eine SQL-Datei typischerweise aus?

Und warum hast du zuerst alles in einer großen Transaktion geladen und später ist es egal wenn du das in einzelne Dateien splittest?

Mit den Einstellungen wird es keinen Spaß machen die Datenbank zu betreiben. Da ist eine Menge falsch.

Dann werde bitte konkret :slight_smile:

Btw. der Server hat 4 GB RAM zur Verfügung.

Und warum hast du zuerst alles in einer großen Transaktion geladen und später ist es egal wenn du das in einzelne Dateien splittest?

Um das zu beantworten muss ich weit ausholen.

aller anfang ist ein apache logfile in einem custom format.

dieses wird geparst, gefiltert, umformatiert und schließlich in SQL Statements übersetzt.

In so einem file.sql steht z.B. drinne


BEGIN;
select * from insertnewip(array[‘ip’,‘countrycode’, ‘countryname’, ‘isp’],array[‘66.
249.71.43’,‘US’,‘United States’,‘Google’]);


COMMIT;


Wichtig ist zu wissen das die meisten Datensätze auch Timestamps besitzen die ebenfalls in die DB geschrieben werden. Um die DB vor dem Import doppelter Datensätze zu schützen (“huch das selbe Logfile 2mal eingelesen. so ein Pech”)
wird u.a. der Timestamp der ersten Logfilezeile und der Timestamp der letzten Logfilezeile in eine “Intervalltabelle” geschrieben.

Das ermitteln passiert während der Verarbeitung des Logfiles zu einem file.sql automatisch.

wird das file.sql nun importiert wird der gesammte Inhalt nicht in die DB geschrieben wenn es das intervall schon in der DB gibt. Daher eine Transaction pro file.sql

splitte ich nun das riesen logfile und lasse die splits durchlaufen, wird pro split auch ein intervall erzeugt. daher ist das file.sql eines split logfiles wieder als saubere transaction ok.
habe halt nur mehr files die verarbeitet werden müssen (und mehr intervalle die bei einem import geprüft werden müssen).

Die Frage damit beantwortet ?

So einfach ist das nicht, man passt seine Speicheranstellungen an die Arbeitsaufgabe an und sehr wichtig: man muss den Erfolg kontrollieren und ggf. nachbessern.

Über so etwas halte ich mehrstündige Workshops, das kann man nicht in zwei Sätzen abhandeln.

Als Anhaltspunkte: 57 MB shared_buffers sind um Welten zu wenig. Hier würde ich eher im Bereich 1 GB arbeiten. work_mem ist dafür wieder zu hoch und kann dir die Maschine killen, ein einstelliger MB Wert ist hier passend und mehr als ausreichend. max_stack_depth von 7 MB brauchst du auch nur wenn du sehr umfangreiche und vor allem rekursive Operationen am laufen hast. max_fsm_pages hast du angepasst, wie sieht es mit max_fsm_relations aus? effective_cache_size ist dem Anschein nach in Ordnung aber vielleicht etwas zu hoch gewählt. Müsste man am Betriebssystem überprüfen und ggf. noch anpassen.

Alles in allem: wie bist du auf diese Wertte gekommen? Und die von dir genannten Werte anpassen ist nicht alles was man tun kann (und sollte) sondern da ist schon noch mehr Arbeit zu erledigen.

du musst beachten - du brauchst natuerlich auch eine 64 bit version des psql … ob es da eins gibt - oder ob man sich selbst eins mal eben so zusammen-compillieren kann weiss ich nicht

ads weiss da vielleicht mehr …

zu deinen speichereinstellungen …

shared_buffers und effective_cache_size

Alles in allem: wie bist du auf diese Wertte gekommen?

tja. erstmal (kein quatsch)mehr als 30 Mio. Testdatensätze erstellt und dann z.B. diese Tuts hier gelesen

Performance Tuning PostgreSQL
Tuning PostgreSQL for performance

und schließlich über try and error ausprobiert welche settings mir die schnellsten antworten auf meine selects geben.

Mehr kann man nicht tun wenn man (wie ich) keine Erfahrung mit PG hat(te) und sie nutzen will.

Und man darf den zeitlichen Aspekt nicht verachten. Die Datenbank hat ja keinen Selbstzweck sondern Sie ist (wichtiges) MIttel zum eigentlichen Zweck. Bedeutet das man nicht ohne ende Zeit hat für das optimieren auch der letzten 10% der DB. Mit den Tonnen an Daten muss ich ja auch noch was machen :slight_smile:

Daher bin ich über jede konkrete Hilfe dankbar.

Wie gesagt

4 GB Ram hat das 32 Bit System

Max Connections 10

Nach wochenlangen analyse select … läuft alles auf joins, hashs und sorts großer Datenmengen (Auf ein Select ca. 60 Mio Antwortzeilen) hinaus. dafür ist work_mem erhöhen einfach die beste Wahl (nach meinem Kenntnisstand).

Nein.

Aber das hättet ihr auch schneller - und ev. billiger - beantwortet bekommen können.

Aber das hättet ihr auch schneller - und ev. billiger - beantwortet bekommen können.

Wie ?

Wiederhole:
Dann werde bitte konkret :slight_smile: