Insert mit vielen Spalten sehr langsam

Hallo Postgres-Gemeinde,

ich habe hier Tabellen zu befüllen, die nicht sehr lang sind (zwischen 3000 - 9000 Zeilen), aber dafür relativ “breit”: zwischen 700 und 1560 Spalten. Jetzt verhält es sich so, dass inserts mit 700 Spalten in wenigen Sekunden abgewickelt werden, die Tabelle mit den über 1500 Spalten jedoch für das einfügen von zwei Zeilen über 2:30 Minuten (!) :confused: braucht.

Kann mir jemand bitte erklären, warum “breite” Tabellen so langsam sein müssen?

Wie kann ich das optimieren?

Vielen Dank schon mal für Eure Mühen!

Viele Grüsse,
donhaaare

/etc/postgresql/8.1/main/postgresql.conf:

listen_addresses = ‘*’
port = 5432
max_connections = 100
unix_socket_directory = ‘/var/run/postgresql’
ssl = false
shared_buffers = 1000
autovacuum = on
stats_start_collector = true
stats_row_level = true
autovacuum_naptime = 3600 # in secs - one hour
#lc_messages = ‘en_GB.UTF-8’
#lc_monetary = ‘en_GB.UTF-8’
#lc_numeric = ‘en_GB.UTF-8’
#lc_time = ‘en_GB.UTF-8’

lc_messages = ‘en_GB’
lc_monetary = ‘en_GB’
lc_numeric = ‘en_GB’
lc_time = ‘en_GB’

max_fsm_pages = 64000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1250 # min 100, ~70 bytes each
effective_cache_size = 10000 # typically 8KB each

Kannst Du uns bitte erklären, wie man auf solch ein kapottes Design kommt?

Andreas

8.3 ist aktuell, 8.1 ist zwei Jahre veraltet.

Bei deinem Auszug der Konfiguration fehlen alle wichtigen Werte für Speicherangaben.

Und bevor wir hier weiter über Geschwindigkeit diskutieren hätte ich gern eine sehr sehr gute erklärung, wieso man 700 oder 1500 Spalten in einer Tabelle haben möchte.

Davon abgesehen:

kretschmer@tux:~/muell$ cat insert.sql
MAX=1500

echo "create table breite_tabelle ("
for i in `seq 1 $MAX` ; do
  echo "col$i int, "
done;
echo "foo int);"

echo "insert into breite_tabelle values ("
for i in `seq 1 $MAX` ; do
  echo "$i, "
done;
echo "1);"

Das damit erzeugte SQL ausgeführt:

test=# \i dummes.sql
CREATE TABLE
Time: 79.611 ms
INSERT 0 1
Time: 4.539 ms
test=#

Halte ich nun nicht wirklich für problematisch.

Andreas

Hi,

ja, das erkläre ich Euch gerne. Die Tabellen, die hier befüllt werden, werden automatisch erzeugt. Grundlage ist dabei eine Anwendung, die Fragebögen erfasst, und zwar in einer Form, die in etwa so aussieht: Event -> Form -> Itemgroup -> Item. Ein Event besteht aus mehreren Forms, ein Formular aus mehreren Itemgroups usw.
Da die Daten meist nur immer an der nächsthöheren Hierarchieebene festgemacht sind, aber alle in der Hierarchie darunterliegenden Einträge der Person zugeordnet werden können müssen (?), ist die original db-Struktur hypernormalisiert und es existieren immer mehrere (Fremd-)Schlüssel. Ausserdem muss nicht jede Person die gleichen Formulare usw. zugeordnet haben. D.h., der Gesamtdatenbestend pro Person kann variieren.

Die Abfrage von einfachen Werten aus dieser DB gestaltet sich mitunter sehr schwierig, da man immer lange Join-Ketten bilden muss, um den richtigen Kontext herstellen und Wertepaare finden zu können.
Auf dieses Design hatte und habe ich keinen Einfluss.

Um nun die Datenextraktion und -verwertung v.a. mit Software wie SPSS einfacher zu machen, wird die bestehende DB-Struktur in einem XML abgebildet (automatisch) und auf dieser Basis per xslt jeweils eine Tabelle pro Event generiert. D.h. die Personen-ID wird zum primary key, und alle Werte, die innerhalb dieses Events gespeichert sind, werden in Spalten aufgezogen.

Ich weiß, es klingt alles ein wenig hirnverbrannt, aber unsere DataCapture-Software arbeitet nun mal so. Datensammeln kann sie sehr gut und komfortabel, bloß hergeben will sie sie nicht mehr.

Ich hoffe, mit dieser aufwändigen Erklärung habe ich mir ein wenig Unterstützung verdient. Bitte kommentiert nicht das DB-Design, das kann ich wirklich nicht beeinflussen.

Danke!

Traumhaft. Aber leider nicht meine Realität. Warum es bei mir so lange dauert, will ich ja grad herausfinden. Und wie gesagt: Alles unter 800 Spalten geht in akzeptabler Geschwindigkeit.

dahaaare

Da bin ich leider an bestimmte Repositories und Vorgaben gebunden.

Mehr hab ich nicht in der postgres.conf. Die Maschine selbst hat 8GB RAM und eine Athlon X2 6000+ mit 750GB Platten im RAID 1. Vom System her begrenzt nur der Prozessor die Geschwindigkeit, der Rest langweilt sich. Der Prozess an sich braucht nur 15MB Speicher und steht die meiste Zeit auf “PARSE”.

Wenn Ihr noch was anderes braucht / wissen wollt, einfach Bescheid sagen.

donhaaare

Nur zur Info:

psql:dummes.sql:15002: ERROR:  tables can have at most 1600 columns



Ich weiß, es klingt alles ein wenig hirnverbrannt, aber unsere DataCapture-Software arbeitet nun mal so. Datensammeln kann sie sehr gut und komfortabel, bloß hergeben will sie sie nicht mehr.

Daher versucht ihr nun, die Macke des einen Programmes auf diese Weise durch eine andere Macke auszugleichen. Sowas geht nur selten gut.

Ich hoffe, mit dieser aufwändigen Erklärung habe ich mir ein wenig Unterstützung verdient. Bitte kommentiert nicht das DB-Design, das kann ich wirklich nicht beeinflussen.

Danke!

Ja, mag sein. Nur helfen können wir da auch kaum.

Es bleibt vielleicht zu sagen, daß ihr offensichtlich eine nicht auf eure 8GByte RAM angepaßte Konfig habt und sehr wahrscheinlich von dem RAM nur ein Bruchteil genutzt wird. Ich würde da mal in Richtung work_mem da probieren. Möglicherweise würde auch COPY statt INSERT was bringen. Aber Du wirst wahrscheinlich eher wenige Anwender finden, die Erfahrung mit einem deratigen Design haben.

Andreas

Ihr macht da eine Menge böser, ganz böser Fehler.

Der erste ist wohl schon mal, dass ihr nicht wisst, wie man eine relationale Datenbank nutzt. Das muss ich einfach mal so sagen. Ein Design mit einer Spalte pro möglichem Wert ist schlicht kaputt und gehört abgeschafft. Das ist krank, das ist unflexibel, das lässt sich weder sauber erweitern noch performant abfragen. Und wie langsam das wird siehst du gerade selber, geschweige denn die Probleme beim Zusammenstellen der Anfrage. Geschweige denn die wahrscheinlich nicht vorhandene oder komplette Dokumentation. Wenn du schon von 800 oder über 1000 Spalten sprichst … woher weißt du auf einen Schlag, welche Spalten enthalten sind?

Der zweite Fehler: ihr habt eine richtig performante Maschine hingestellt, wisst aber nicht, wie ihr die konfigurieren sollt bzw. müsstet. Wie akretschmer sagt nutzt ihr wahrscheinlich nur einen Bruchteil der Ressourcen und der Rest liegt brach. Im Zusammenhang mit Fehler 1 ergibt das dann die Probleme, die du gerade hast.

Der dritte Fehler: die Datenbank wurde entwickelt als das Interface (woran du gerade arbeitest) noch nicht feststand. Wenn es - wie jetzt - zu Problemen kommt, kann nichts mehr geändert werden. So ein Entwicklungsprozess ist - gelinde gesagt - suboptimal.

Das vierte Problem (das ist kein Fehler mehr, noch nicht): die Datenbank ist veraltet, es gibt neuere und bessere Versionen und ihr könnt die nicht nutzen.


Ich persönlich wüsste nicht, wo ich bei dem Stapel Probleme zuerst ansetzen sollte. Die Probleme und Schmerzen werden allerdings nur noch größer, nicht weniger werden, glaub mir.


Oh, und XML ins Spiel zu bringen verkompliziert die Sache nur, oder wie ein guter Bekannter gern sagt: “Sie haben XML genommen um ein Problem zu lösen. Jetzt haben sie zwei Probleme.”


Wir nicht. Du jedoch könntest mit ein paar besseren Vorgaben wesentlich schneller vorankommen. Welche Möglichkeiten hast du, in den Entscheidungsprozess einzugreifen?

habt ihr denn auch ein 64-bit OS im Einsatz?

Da muss ich Dir widersprechen. Der Fehler liegt in der Konzeption der DataCapture-Software, die als Unterbau nun mal keine relationale DB im herkömmlichen Sinne nutzt. Die Anforderungen an das System sind teils durch klinische Standards bedingt. Für diese Aufgabe, die ich jetzt zu optimieren versuche, war das System nie ausgelegt. Sowas war ürsprünglich so geplant, dass über xml die Daten exportiert werden und dann eben von SPSS oder SAS weiterbearbeitet werden oder mit ähnlichen Systemen Daten ausgetauscht werden. Das kann unsere Software nämlich hervorragend.

Da hast Du wohl recht. Hinzukommt, dass wir mit den Problemen lernen. Wir konnten uns nicht vorher etwas passendes überlegen, weil wir den Umfang und Aufwand nicht kannten. Der Server war ausserdem nicht für dieses Aufgabe exklusiv gedacht.

Das Interface habe ich auch nicht entwickelt. Ich bin nur der Dumme, der jetzt schauen muss, dass halbwegs gut funktioniert. Und wie gesagt, für diese Aufgabe war das System nie bestimmt.


Das muss ich Gott sei Dank auch bei uns in der Firma keinem mehr erklären…

Keine. Ich verwalte nur die Havarie.

Danke trotzdem,

donhaaare

Danke, ich werds mir merken.

Danke, ich probier das aus.

donhaaare

Nein, Debian Etch 32bit. Kernel:

Linux 2.6.18-6-686-bigmem #1 SMP

donhaaare

Das kann IIRC nur max. 4 GByte nutzen.

Andreas

Richtig, bei Bigmem ist wohl bei 4 GB Schluß … der Rest liegt brach und wird umsonst bezahlt - na, nicht ganz, der Stromverbrauch ist immerhin vorhanden :wink:

donhaaare: nun, welchen Sinn macht es, diese Daten in eine relationale Datenbank zu pressen, wenn das ganze doch nicht dafür gedacht war? Und würde es nicht mehr Sinn machen, die Struktur aufzuteilen? Import und Export in beliebige Formate kannst du doch selbst handhaben, deshalb muss man die Struktur nicht 1:1 übernehmen. Noch ein paar Spalten und ihr seid sowieso am Limit und müsst euch etwas überlegen.

Hi,

so, mein free -m sagt

# free -m
             total       used       free     shared    buffers     cached
Mem:          8088       7756        331          0        160       6163
-/+ buffers/cache:       1432       6655
Swap:         2047          0       2047

das würde für mich heißen, dass der Speicher genutzt wird. Unser Hoster bietet die Kisten mit dem Betriebssystem und diesem Kernel an, also würde ich voraussetzen, dass der komplette Speicher auch unterstützt wird.

Was Sinn und Unsinn dieses Verfahrens angeht, kann ich wenig dazu sagen. Es hat sich bei anderen Projekten (mit viel schmäleren Tabellen) als sehr gute Möglichkeit erwiesen, den Projektleuten und Statistikern für Ihre BIRT-Report-Designer und SPSSe die Daten leicht verwertbar zur Verfügung zu stellen.
Auf all das habe ich keinen Einfluss. Ich kann lediglich versuchen, die Probleme mit den extremsten Projekten zu begrenzen und an unsere Entwickler weiterzugeben.
Das sich zwischen den RDBMS-Leuten und den XMLern Welten auftun, habe ich wieder mal erfahren … :wink:

Du vermittelst da zwischen zwei Parteien und wenigstens die Datenbankleute sind dir aufgrund dieses Tabellendesigns nicht an die Gurgel gespriungen?
Eigentlich unterständlich.

hi,

sorry wenn ich den alten Thread noch mal aufnehme aber ich stehe vor einem ähnlichen Problem.
auch ich möchte Daten, aus einer anderen “Daten Welt”, nach PostgreSQL bringen.

ein Grund für ein solches Design mit vielen Feldern lag am Betriebssystem M$ DOS und seinen FILES und BUFFERS …
man konnte nicht “viele kleine” Dateien “verwalten” und hat deshalb “in die Breite” gebaut.

aber zurück zu PostgreSQL v9.2x ( x64 / x32 ) und der Anzahl von Fields
wenige Text Felder und viele logische “Schalter” … alles “müsste” < 8 KB pro ROW (?) ergeben.

kein Index beim Import ( INSERT ) nur als 1st Field “__record” Type “serial”

CONSTRAINT nodes_pkey PRIMARY KEY (__record) ) ;

bei < 300 Fields ist alles “normal” … dann fängt es an und bei > 500 geht gar nichts mehr … es dauert “ewig”

Frage : was “passiert” da wenn der Effekt auftritt ?

über

work_mem

hab ich “gelesen” und ausprobiert aber das scheint mit bei dem ( Design ) Problem nicht zu helfen.

p.s. um das Puzzle noch ein wenig schwieriger zu machen : der Bereich 300 - 500 ist PC abhängig wobei ein alter PC mit P4 CPU 3Ghz “mehr” schafft als ein i7-2630QM ???

Version 8.1 ist falsch, das sollte 9.1 oder 9.2 heißen.

shared_buffers und work_mem sollten erhöht werden.

Um eine Antwort auf die konkrete Frage zu finden, wäre es wichtig zu wissen, wo die Zeit liegen bleibt.
Eine einfache Sache ist, psql für den INSERT zu verwenden und vorher \timing einzuschalten.
Dann sieht man, wieviel Zeit tatsächlich am Server liegenbleibt.
Wie ist denn die Auslastung von CPU und Memory auf Client und Server während der Zeit, die das INSERT dauert?
Welche Prozesse sind am meisten beschäftigt?

Wenn das Problem am Server ist, hilft oft oprofile beim Herausfinden, was passiert:
Profiling with OProfile - PostgreSQL wiki

Liebe Grüße,
Laurenz Albe

du meinst in der PostgreSQL.CONF
ich werde die nochmal kontrollieren.

aha … ja gute Idee !!!

beim i7-2630QM so bei 0% - 15% … genau das wundert mich ja daran !!!
der alter P4 hängt bei 98% aber er “macht” es in 30Sec statt 70Sec. ( > 500000 Row )

ok ich hab bei den test nicht “14.4.1. Disable Autocommit” aktiviert … aber für “viele INSERT” sollte man doch lieber "COPY … FROM … " verwenden.

nun sagte ich ja das ich den Effekt auch habe wenn ich eine grosse Table ( > 500000 ) öffne mit pgAdmin3.EXE ohne ein LIMIT.


Frage : wenn ich “nur” eine Table öffne werden dann schon irgendwelche Trigger oder Sequenz “aktive” ?