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 (!)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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
Davon abgesehen:
Das damit erzeugte SQL ausgeführt:Code: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);"
Halte ich nun nicht wirklich für problematisch.Code:test=# \i dummes.sql CREATE TABLE Time: 79.611 ms INSERT 0 1 Time: 4.539 ms test=#
Andreas
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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!
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:
Code:psql:dummes.sql:15002: ERROR: tables can have at most 1600 columnsDaher versucht ihr nun, die Macke des einen Programmes auf diese Weise durch eine andere Macke auszugleichen. Sowas geht nur selten gut.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.
Ja, mag sein. Nur helfen können wir da auch kaum.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!
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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?Zitat von donhaaare
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
habt ihr denn auch ein 64-bit OS im Einsatz?
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen