Hallo Liste!
Ich glaube mich zu erinnern, dass es hier einige gibt, die sich mit Datenbanken und im speziellen sogar mit PostgreSQL ziemlich gut auskennen.
Wir verwenden hier in Chemnitz seit einiger Zeit eine Postgres-Datenbank um unsere Messdaten zu speichern. Eigentlich halte ich die Anforderungen für sehr moderat, dennoch bin ich mit der ,,Gesamtsituation'' bisher etwas unzufrieden.
Ich versuch mal kurz zu skizzieren was wir machen (wollen) und wo es im Moment klemmt.
Die Datenbank besteht im Prinzip aus einer Tabelle die pro Datensatz eine ID, einen Zeitstempel, einen Blob sowie noch drei String Felder enthält. Da wir die PostGIS-Erweiterung benutzten ist auch noch eine Feld mit Koordinaten vorgesehen.
In dem Blob-Feld fügen wir Binärdaten der Größe 100 Byte - 300kb ein, das ist aber ständig variable.
Insgesamt ist perspektivisch mit eine Datenvolumen von ca. 1 Terrabyte zu rechnen, also eigentlich nix aufregendes. Wenn ich einen Satz Messdaten importiere sind das ca. 20GB-Daten, die auf 500.000 Datensätze verteilt sind.
Die Datenbank läuft auf einem Windows-Server in der 32-Bit Version. Client-seitig verwenden wir c# mit dotConnect von devart.
Jetzt zu den Problemen:
1) Der Import der Daten gestaltet sich aus meiner Sicht ziemlich langsam. Der Importer schafft im Moment lediglich eine Datenrate von 8 MB/s wenn er in die Datenbank schreibt. Beschaffen könnte er die Daten theoretisch mit 50M/s.
2) Ganz blauäugig dachte ich mir, dass man um das INSERT der 500.000 Datensätze, die ja alle logisch zu einem Messdatensatz gehören, mit einer Transaktion klammern kann. Wenn ich das mache, bekomme ich nach ca. 5GB importierten Daten eine Exception, die mir mitteilt, dass auf Serverseite irgendwelcher Speicher alle wäre?
Wenn ich weniger Daten importiere gibt es keine Exception. Dafür bekomme ich beim finalen Commit der Transaktion ein Timeout.
3) Das löschen von Messdaten (wider 500.000 Zeilen) mit einer Query ala
DELTE FROM measurements WHERE session_id = 34
dauert ewig und kommt meist Client-seitig mit einem Timeout zurück.
Hat jemand vielleicht einen Ansatzpunkt?
Vielen Dank! Marcus
Zitat von Marcus Obst marcus.obst@etit.tu-chemnitz.de:
Hallo Liste!
Ich glaube mich zu erinnern, dass es hier einige gibt, die sich mit Datenbanken und im speziellen sogar mit PostgreSQL ziemlich gut auskennen.
Wir verwenden hier in Chemnitz seit einiger Zeit eine Postgres-Datenbank um unsere Messdaten zu speichern. Eigentlich halte ich die Anforderungen für sehr moderat, dennoch bin ich mit der ,,Gesamtsituation'' bisher etwas unzufrieden.
Ich versuch mal kurz zu skizzieren was wir machen (wollen) und wo es im Moment klemmt.
Die Datenbank besteht im Prinzip aus einer Tabelle die pro Datensatz eine ID, einen Zeitstempel, einen Blob sowie noch drei String Felder enthält. Da wir die PostGIS-Erweiterung benutzten ist auch noch eine Feld mit Koordinaten vorgesehen.
In dem Blob-Feld fügen wir Binärdaten der Größe 100 Byte - 300kb ein, das ist aber ständig variable.
Insgesamt ist perspektivisch mit eine Datenvolumen von ca. 1 Terrabyte zu rechnen, also eigentlich nix aufregendes. Wenn ich einen Satz Messdaten importiere sind das ca. 20GB-Daten, die auf 500.000 Datensätze verteilt sind.
Die Datenbank läuft auf einem Windows-Server in der 32-Bit Version.
*GNA*. Wäre ein Wechsel zu einem OS denkbar?
Client-seitig verwenden wir c# mit dotConnect von devart.
Jetzt zu den Problemen:
- Der Import der Daten gestaltet sich aus meiner Sicht ziemlich
langsam. Der Importer schafft im Moment lediglich eine Datenrate von 8 MB/s wenn er in die Datenbank schreibt. Beschaffen könnte er die Daten theoretisch mit 50M/s.
Wie sieht die postgresql.conf aus? Habt Ihr die so belassen, oder aber verändert?
- Ganz blauäugig dachte ich mir, dass man um das INSERT der 500.000
Datensätze, die ja alle logisch zu einem Messdatensatz gehören, mit einer Transaktion klammern kann. Wenn ich das mache, bekomme ich nach ca. 5GB importierten Daten eine Exception, die mir mitteilt, dass auf Serverseite irgendwelcher Speicher alle wäre?
Wie lautet die Meldung *genau*? An und für sich ist Deine Idee richtig. Aber wenn Du das in der PG-Default-Congfig machst, würde mich dies als Resultat nicht wirklich wundern.
Wenn ich weniger Daten importiere gibt es keine Exception. Dafür bekomme ich beim finalen Commit der Transaktion ein Timeout.
- Das löschen von Messdaten (wider 500.000 Zeilen) mit einer Query ala
DELTE FROM measurements WHERE session_id = 34
dauert ewig und kommt meist Client-seitig mit einem Timeout zurück.
Was sagt ein "EXPLAIN ANALYSE SELECT * FROM measurements WHER ID = 34;" ?
Hat jemand vielleicht einen Ansatzpunkt?
Jede Menge...
Andreas
On 09.05.2011 09:21, andreas@a-kretschmer.de wrote:
Die Datenbank läuft auf einem Windows-Server in der 32-Bit Version.
*GNA*. Wäre ein Wechsel zu einem OS denkbar?
Erstmal nicht :)
Wie sieht die postgresql.conf aus? Habt Ihr die so belassen, oder aber verändert?
Ist Standard, so wie sie bei Postgres 9.0 ausgeliefert wird.
- Ganz blauäugig dachte ich mir, dass man um das INSERT der 500.000
Datensätze, die ja alle logisch zu einem Messdatensatz gehören, mit einer Transaktion klammern kann. Wenn ich das mache, bekomme ich nach ca. 5GB importierten Daten eine Exception, die mir mitteilt, dass auf Serverseite irgendwelcher Speicher alle wäre?
Wie lautet die Meldung *genau*? An und für sich ist Deine Idee richtig. Aber wenn Du das in der PG-Default-Congfig machst, würde mich dies als Resultat nicht wirklich wundern.
Ich hab hier mal rauskopiert was ich in der Exception finden konnte:
{Error: 53200: Speicher aufgebraucht} ".\src\backend\utils\mmgr\aset.c" "Fehler bei Anfrage mit Größe 8388608." Line Number: 589 ProcedureName "AllocSetAlloc"
Wenn ich weniger Daten importiere gibt es keine Exception. Dafür bekomme ich beim finalen Commit der Transaktion ein Timeout.
- Das löschen von Messdaten (wider 500.000 Zeilen) mit einer Query ala
DELTE FROM measurements WHERE session_id = 34
dauert ewig und kommt meist Client-seitig mit einem Timeout zurück.
Was sagt ein "EXPLAIN ANALYSE SELECT * FROM measurements WHER ID = 34;" ?
explain analyse select * from measurements where session_id = 77; QUERY PLAN ------------------------------------------------------------------- Seq Scan on measurements (cost=0.00..17308.40 rows=153072 width=762) (actual time=0.043..174.420 rows=158699 loops=1) Filter: (session_id = 77) Total runtime: 179.649 ms (3 rows)
Ein anschließendes
delete from measurements where session_id = 77;
dauert wie schon beschrieben ewig.
Danke.
Marcus
Zitat von Marcus Obst marcus.obst@etit.tu-chemnitz.de:
On 09.05.2011 09:21, andreas@a-kretschmer.de wrote:
Die Datenbank läuft auf einem Windows-Server in der 32-Bit Version.
*GNA*. Wäre ein Wechsel zu einem OS denkbar?
Erstmal nicht :)
Wie sieht die postgresql.conf aus? Habt Ihr die so belassen, oder aber verändert?
Ist Standard, so wie sie bei Postgres 9.0 ausgeliefert wird.
Aua. Diese Config ist so spartanisch, daß man dann mit Deinen Anforderungen nicht zurecht kommen KANN.
IIRC ist z.B. als Default shared_buffers auf 24 MB gesetzt, das ist arg wenig. Je nach Hardware des Servers und anderen Bedingungen (dedizierter Server?) ist da einiges anzupassen.
- Ganz blauäugig dachte ich mir, dass man um das INSERT der 500.000
Datensätze, die ja alle logisch zu einem Messdatensatz gehören, mit einer Transaktion klammern kann. Wenn ich das mache, bekomme ich nach ca. 5GB importierten Daten eine Exception, die mir mitteilt, dass auf Serverseite irgendwelcher Speicher alle wäre?
Wie lautet die Meldung *genau*? An und für sich ist Deine Idee richtig. Aber wenn Du das in der PG-Default-Congfig machst, würde mich dies als Resultat nicht wirklich wundern.
Ich hab hier mal rauskopiert was ich in der Exception finden konnte:
{Error: 53200: Speicher aufgebraucht} ".\src\backend\utils\mmgr\aset.c" "Fehler bei Anfrage mit Grö�e 8388608." Line Number: 589 ProcedureName "AllocSetAlloc"
Wenn ich weniger Daten importiere gibt es keine Exception. Dafür bekomme ich beim finalen Commit der Transaktion ein Timeout.
- Das löschen von Messdaten (wider 500.000 Zeilen) mit einer Query ala
DELTE FROM measurements WHERE session_id = 34
dauert ewig und kommt meist Client-seitig mit einem Timeout zurück.
Was sagt ein "EXPLAIN ANALYSE SELECT * FROM measurements WHER ID = 34;" ?
explain analyse select * from measurements where session_id = 77; QUERY PLAN
Seq Scan on measurements (cost=0.00..17308.40 rows=153072 width=762) (actual time=0.043..174.420 rows=158699 loops=1) Filter: (session_id = 77) Total runtime: 179.649 ms (3 rows)
Wieviel Zeilen hat die Tabelle insgesamt? Möglicherweise fehlt ein Index, aber dazu müßte wissen, wieviel Zeilen in der Tabelle sind.
Ein anschließendes
delete from measurements where session_id = 77;
dauert wie schon beschrieben ewig.
Hier sind weitere Parameter zu prüfen/anzupassen. Z.B. Anzahl der WAL-Files.
Anpassen solltest Du:
- shared_buffers auf etwa 25% des vorhandenen RAM (bei dediziertem Server) - effective_cache_size korrekt setzen - work_mem von aktuell 1MB auf vielleicht erst mal 4, aber das hängt von vielen Dingen ab - wal_buffers erhöhen - checkpoint_segments (aktuell IIRC 3 auf vielleicht 30)
Das mal als Anfang.
Ich kann Dir auch das Hosting hier bei uns anbieten ;-)
Andreas
On 09.05.2011 12:22, andreas@a-kretschmer.de wrote:
Aua. Diese Config ist so spartanisch, daß man dann mit Deinen Anforderungen nicht zurecht kommen KANN.
IIRC ist z.B. als Default shared_buffers auf 24 MB gesetzt, das ist arg wenig. Je nach Hardware des Servers und anderen Bedingungen (dedizierter Server?) ist da einiges anzupassen.
Hab ich geändert, hat etwas gebracht (schafft jetzt 10 MB/s). Aber ist jetzt auch nicht so der Knaller.
Wieviel Zeilen hat die Tabelle insgesamt? Möglicherweise fehlt ein Index, aber dazu müßte wissen, wieviel Zeilen in der Tabelle sind.
Im Moment ca. 300.000 (frisch eingefügt). Die will ich unmittelbar danach mittels
DELETE FROM measurements;
alle einfach wieder löschen -> dauert....
- shared_buffers auf etwa 25% des vorhandenen RAM (bei dediziertem Server)
- effective_cache_size korrekt setzen
- work_mem von aktuell 1MB auf vielleicht erst mal 4, aber das hängt von
vielen Dingen ab
- wal_buffers erhöhen
- checkpoint_segments (aktuell IIRC 3 auf vielleicht 30)
Danke. Größter Kritikpunkt ist eigentlich nach wie vor, dass es hier nicht möglich ist die 20GB zw. 300.000 INSERT Statements in einen Transaktionsblock zu stecken ohne das ich eine Out-Of-Memory Exception bekomme.
Ich kann Dir auch das Hosting hier bei uns anbieten ;-)
Hm, mittelfristig soll der Datenbankserver im Messauto mitfahren, ich denke das wird schwierig :)
Danke.
Marcus
Zitat von Marcus Obst marcus.obst@etit.tu-chemnitz.de:
On 09.05.2011 12:22, andreas@a-kretschmer.de wrote:
Aua. Diese Config ist so spartanisch, daß man dann mit Deinen Anforderungen nicht zurecht kommen KANN.
IIRC ist z.B. als Default shared_buffers auf 24 MB gesetzt, das ist arg wenig. Je nach Hardware des Servers und anderen Bedingungen (dedizierter Server?) ist da einiges anzupassen.
Hab ich geändert, hat etwas gebracht (schafft jetzt 10 MB/s). Aber ist jetzt auch nicht so der Knaller.
Wieviel Zeilen hat die Tabelle insgesamt? Möglicherweise fehlt ein Index, aber dazu müßte wissen, wieviel Zeilen in der Tabelle sind.
Im Moment ca. 300.000 (frisch eingefügt). Die will ich unmittelbar danach mittels
DELETE FROM measurements;
alle einfach wieder löschen -> dauert....
Wenn Du ALLES aus einer Tabelle löschen willst: TRUNCATE
- shared_buffers auf etwa 25% des vorhandenen RAM (bei dediziertem Server)
- effective_cache_size korrekt setzen
- work_mem von aktuell 1MB auf vielleicht erst mal 4, aber das hängt von
vielen Dingen ab
- wal_buffers erhöhen
- checkpoint_segments (aktuell IIRC 3 auf vielleicht 30)
Danke. Größter Kritikpunkt ist eigentlich nach wie vor, dass es hier nicht möglich ist die 20GB zw. 300.000 INSERT Statements in einen Transaktionsblock zu stecken ohne das ich eine Out-Of-Memory Exception bekomme.
Mir ist nicht ganz klar, wo die her kommt. Stand diese Meldung im Log von PG An und für sich kann PG sowas.
Ach ja: wenn Du kannst, verwende COPY anstelle INSERT, wenn es Bulk-Inserts sind. Ist um Größenordnungen schneller.
Ich kann Dir auch das Hosting hier bei uns anbieten ;-)
Hm, mittelfristig soll der Datenbankserver im Messauto mitfahren, ich denke das wird schwierig :)
Das ist kein Ausschluß ;-)
Einner unserer Kunden hat z.B. $GANZ_VIELE GPS-Boxen im Einsatz, die ständig ihre Position melden. Da geht schon ganz schön die Post ab ...
On 09.05.2011 14:34, andreas@a-kretschmer.de wrote:
Hallo!
Mir ist nicht ganz klar, wo die her kommt. Stand diese Meldung im Log von PG An und für sich kann PG sowas.
Kam als Exception in meinem Client an.
Ach ja: wenn Du kannst, verwende COPY anstelle INSERT, wenn es Bulk-Inserts sind. Ist um Größenordnungen schneller.
Geht leider nicht.
Ich hab jetzt nochmal ein bisschen weiter nachgeforscht. Das Problem scheint der BLOB zu seien. Wir speichern u.a. unkomprimierte Bilder ab, diese haben dann pro Eintrag eine Größe von ca. 300k. Sobald ich diese Einfügen will bricht die import Performance von 40MB/s auf 5MB/s ein.
Lasse ich die Bilder weg, geht alles wunderbar.
Marcus
Marcus Obst marcus.obst@etit.tu-chemnitz.de (Tue May 10 13:26:29 2011):
On 09.05.2011 14:34, andreas@a-kretschmer.de wrote:
Hallo!
Mir ist nicht ganz klar, wo die her kommt. Stand diese Meldung im Log von PG An und für sich kann PG sowas.
Kam als Exception in meinem Client an.
Ach ja: wenn Du kannst, verwende COPY anstelle INSERT, wenn es Bulk-Inserts sind. Ist um Größenordnungen schneller.
Geht leider nicht.
Ich hab jetzt nochmal ein bisschen weiter nachgeforscht. Das Problem scheint der BLOB zu seien. Wir speichern u.a. unkomprimierte Bilder ab, diese haben dann pro Eintrag eine Größe von ca. 300k. Sobald ich diese Einfügen will bricht die import Performance von 40MB/s auf 5MB/s ein.
Kann man vielleicht dummy-Bilder schon mal vorher einfügen und dann nur updates machen?
Am 9. Mai 2011 12:22 schrieb andreas@a-kretschmer.de:
Ich kann Dir auch das Hosting hier bei uns anbieten ;-)
Ich glaube jetzt dämmerts bei mir. Hast Du neulich dienstlich mal eine MySQL-DB für eine Foto-News-Seite repariert?
Viele Grüße Eric
Eric Schaefer eric@gixgax.de wrote:
Am 9. Mai 2011 12:22 schrieb andreas@a-kretschmer.de:
Ich kann Dir auch das Hosting hier bei uns anbieten ;-)
Ich glaube jetzt dämmerts bei mir. Hast Du neulich dienstlich mal eine MySQL-DB für eine Foto-News-Seite repariert?
maybe ...
Andreas
Am 09.05.2011 12:04, schrieb Marcus Obst: ....
Ein anschließendes
delete from measurements where session_id = 77;
dauert wie schon beschrieben ewig.
Hallo Marcus,
wenn die Tabelle vorher leer war, kann der Query-Planer kräftig daneben liegen. Bei kleinen Tabellen wählt er lieber sequentiellen-Scan als Index-Scan. Wenn nach dem Bulk-Insert die Statistik nicht stimmt, rammelt der Query durch zig-tausend Zeilen, obwohl es einen Index gibt.....
Hier lohnt es sich einmal ANALYZE per Hand aufzurufen.
Grüße aus Chemnitz, nach Chemnitz
Thomas
Thomas Güttler guettli@thomas-guettler.de wrote:
Am 09.05.2011 12:04, schrieb Marcus Obst: ....
Ein anschließendes
delete from measurements where session_id = 77;
dauert wie schon beschrieben ewig.
Hallo Marcus,
wenn die Tabelle vorher leer war, kann der Query-Planer kräftig daneben liegen. Bei kleinen Tabellen wählt er lieber sequentiellen-Scan als Index-Scan. Wenn nach dem Bulk-Insert die Statistik nicht stimmt, rammelt der Query durch zig-tausend Zeilen, obwohl es einen Index gibt.....
Hier lohnt es sich einmal ANALYZE per Hand aufzurufen.
Jein.
Wenn der Anteil an zu löschenden Zeilen groß gegenüber der Gesamtgröße der Tabelle ist, dann ist ein Seq-Scan okay, weil ein Index-Scan nix bringen würde. (höhere Kosten) Kommt natürlich auch auf das Storage an, bei SSD's z.B. würde man die Kosten anders ansetzen und Index-Scan's bevorzugen.
Aber prinzipiell stimmt die Aussage, daß ein (manuelles) ANALYSE nach gröberen Änderungen sinnvoll ist.
Andreas
lug-dd@mailman.schlittermann.de
-
Andreas Kretschmer -
andreas＠a-kretschmer.de -
Eric Schaefer -
Heiko Schlittermann -
Marcus Obst -
Thomas Güttler