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(…)
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
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
ueberhaupt ein limit fuer die groesse von script-files gibt
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.
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?
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.
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” …
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
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
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).
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
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
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).