Besitzt Postgres eine ausreichende Performance?

[Dellerium]

Ich will demnächst mit einem kleinen Projekt anfangen - bin aber zur Zeit noch am grübeln, wie ich meine Daten am besten speichern soll... Die Daten bestehen hauptsächlich aus Logdaten.

Die Frage ist nun, mit welche Datenbank ( ich würde am liebsten Postgres nehmen ) ich in der Lage bin >50.000 Datensätze am Tag zu speichern? ( kann auch mehr sein ) Die Datensätz selber wären nicht gross. Vielleicht 100-200 Byte aufgeteilt auf mehere Felder innerhalb der DB.
Allein die Menge an Einträgen macht mir dann doch etwas Sorgen... Ich bin zur Zeit am suchen, ob ich Performance Vergleiche solcher Art finde, aber bisher schweigen sie die Suchmaschinen noch aus - zumindest was sinnvolle Treffer angeht :(

[elrond]

<klugsch...>
die performace ist nicht in erster linie vom rdbms abhängig sondern den db-design
</klugsch>

ich bin derzeit dabei ein relativ komplexes projekt (crm) mit postgres umzusetzen. Dabei kommt ein sehr flexibles design zur Anwendung. diese flexibilität 'kauft' man idr. mit vielen Daten. bei jetzt mehr als 250.000 Datenobjekten (in einer zentralen Tabelle) kann ich noch keine performace-probleme feststellen.
Meine im augenblick größte tabelle (projekt ist noch im aufbau) beinhaltet über 460.000 ds und ist -natürlich über die indizes- kein performanceproblem...

Fazit: ich bin mit der postgres (v. 8.0.3) sehr zufrieden.

[mwanaheri]

Das sollte eigentlich kein Problem für Postgres sein. Probier es doch einfach mal aus! Ein miniprogrämmchen mit loop reicht ja.

[Lin728]

Hab zwar noch Postgres-7.4 im Produktiveinsatz aber bin damit eigentlich über alles zufrieden.
Das ding ist schnell, kann ne Menge und läuft superstabil - einfach super.

[mwanaheri]

übrigens, wenn du auf so eine Tabelle jede Menge Indizes packst, verlangsamt das die Einfügeoperationen. Wenn die DB vor allem viel aufnehmen muss und weniger viel abgefragt ist, lohnt es sich mitunter, sich auf die Indizes zu konzentrieren, die wirklich was bringen.

[Christoph]

lohnt es sich mitunter, sich auf die Indizes zu konzentrieren, die wirklich was bringen.

Das lohnt sich nicht nur "mitunter", sondern immer. Wichtig sind Indizes vor allem auf Foreign Key Feldern, da diese für JOIN-Abfragen wichtig sind.

Auf einen Punkt muss man bei Postgres aber achten: Indizes werden nur benutzt, wenn regelmäßig ANALYZE (z.B. per cron-Job) ausgeführt wird; zumindest war das bei den 7.x-Versionen noch so.

[mwanaheri]

Schon richtig, was ich meinte ist folgendes:
Nehmen wir mal an, er macht eine DB, die die Seitenaufrufe des Webservers speichert. Es soll eine Statistik über die Seitenaufrufe gemacht werden.
Wenn man nun die Statistik erstellt, könnten etliche Abfragen durch Indizes erheblich beschleunigt werden. Erstellt man aber die Statistik z.B. ein Mal im Monat, kann es durchaus sein, dass der Gesamtaufwand für die Indexpflege beim laufenden Einfügen in die Datenbank höher ist als der Perfomancegewinn bei der Statistikerstellung. Wenn man also die monatliche Spitzenlast aushält, kann man die Dauerlast senken, indem man auf einige Indizes verzichtet.

edit: Nun eine Frage von mir: gibt es eigentlich Fälle, in denen ein Index auf einen Foreign Key nichts bringt oder gar die Sache verlangsamt? Ich meine besonders Einfügeoperationen.

[Dellerium]

Schön, das so viele geantwortet haben :)

Also, es geht mir darum Log Files auszuwerten. Das "Problem" dabei ist, das die 50.000 Einträge die ich angegeben habe schon das absolute Minimum sind. Im Worst Case kann es auch passieen, das die zehnfache Menge verarbeitet werden - sprich gespeichert werden muss. Dabei muss aber auch sicher gestellt werden, das weiterhin min 1-2 Clients Daten abfragen können, ohne das die Performance merklich in den Keller geht...
Es soll auf den Daten gearbeitet werden - Änderungen der Daten werden allerdings nicht vorgenommen. Zwar wird die Datenbank die meiste Zeit damit zu tun haben Daten zu speichern, aber es sollen keine regelmässigen Statistiken erstellt werden, sondern aktiv auf den Daten gearbeitet werden.
Der Grund weshalb ich frage ist unteranderem, das ich zur Zeit Ärger mit Postgres habe - der aber offenbar eher auf die Hardware zurückzuführen ist (Dual P4 Xeon mit HP SmartArray 6i Raid( 1+0), 6GB Ram). Dort ist die Leistung beim Einspielen eines 1,1GB Dumps ( das ja einen Haufen Inserts macht wenn ich das richtig sehe ) so gering, das es knapp 2 Stunden dauert. Auf dem Testsystem ( Celeron 2GB Ram, IDE ) dauert es weniger als eine halbe Stunde. Da ich auf dem Server viel wait io habe, liegt da wohl eher ein Problem mit der Hardware vor...
Die Tabellen selber werden sehr einfach gestrickt sein. Auf komplexe Strukturen werde ich zugunsten der Leistung verzichten. Normalisierung lasse ich wohl auch sein wo es zuviel Leistung kosten würde. Dann muss ich halt selber schauen, das ich da keine Inkonsistenzen Daten bekomme.
Wenn ich davon ausgehe, das ich 50.000 Einträge am Tag bekomme ( im Normalfall - Spitzen bis sagen wir 500.000 Einträge um Reserven zu haben) und die schön in die DB schreibe, dann macht das mal 365 eine ziemliche Menge. Ich habe mich daher gefragt, ob es nicht sinnvoller wäre, für jeden Tag einfach eine Tabelle anzulegen ( bsp.: tbllog-2005-09-xx ) um dann die Menge an zu bearbeitenden Daten gering zu halten. Auch das Anlegen von Indexen etc würde dann ja schneller gehen, denke ich, oder? Zumindest müsste die DB weniger Daten im Speicher halten und hätte weniger Daten auf denne sie Selects durchführen müsste. In der Regel sind nur die Logs des jeweiligen Tages interessant wenn jemand darauf suchen will.
Was würdet ihr an Tricks im Desing benutzen um die Leistung zu optimieren?

Gruss Andre

[michael.sprick]

Also dann solltest Du schleunigst den Gedanken verwerfen, auf normalisierung zu verzichten ;)

Hier mal ein Beispiel (allerdings mysql):

Ich habe eine Tabelle "events" angelegt



+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | bigint(20) | | PRI | NULL | auto_increment |
| event | text | YES | | NULL | |
| origin | varchar(30) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+



Per Skript habe ich 2.000.000 Einträge eingefügt und als origin einen Hostnamen aus einer Liste von insgesamt 4 Hostnamen einfügen lassen... also, 500.000 mal Host 1, Host 2 , Host 3 und Host 4

Das Einfügen hat 2m gedauert.
Folgender Select hat immerhin 4,97 sec gedauert:



select count(id) from events where origin = 'Host 1';



Dann das Ganze mal normalisiert im relationen Modell:




+-----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+----------------+
| id | bigint(20) | | PRI | NULL | auto_increment |
| origin_id | bigint(20) | YES | MUL | NULL | |
| event | text | YES | | NULL | |
+-----------+------------+------+-----+---------+----------------+

+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | bigint(20) | | PRI | NULL | auto_increment |
| origin | varchar(30) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+




Das entsprechende Select hat diesmal nur 2.81 sec gedauert



select count(a.id) from events a, origins b where b.origin = 'Host 1' and b.id = a.origin_id;




Abhängig von der Art deiner Logs kann es sogar sein, dass Du jede Menge speicherplatz sparen kannst, wenn Du ein geschicktes Design nimmst. Man muss Logs ja z.B. nicht immer als Text speichern - oft ist der Text ähnlich - nur Portangaben weichen ab - oder Adressen... das wäre z.B. ein Punkt, wo man ansetzen könnte.

Ansonsten interessiert Dich vielleicht auch noch folgendes:
http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php

[Dellerium]

Warscheinlich werde ich mit diversen Designs herumtesten müssen - und mich dann für die Möglichkeit entscheiden, die die meisten Anwendungsfälle abdeckt und dabei genug Leistung bietet...

Der Hintergrund weshalb ich überlegt habe auf Normalisierung zumindest teilweise zu verzichten, war der, das dann zwar viele Daten doppelt vorliegen ( Ports, IP Adressen, etc ), aber die Datenbank dann weniger Arbeit hat, wenn die Daten ausgeben werden müssen. Dann hätte sie eine grosse Tabelle anstatt viele kleine über die sie joins ausführen müsste... Mag sein das ich da noch Denkfehler habe - ich traue mich ja das erste Mal an ein so grosse Projekt heran :)

Den Link den du gepostet hast werde ich mir morgen mal anschauen danke dafür!

[elrond]

wir stehen in unseren db vor ähnlichen problemen. wir haben gute erfahrungen damit gemacht, die daten, die am ende tatsächlich ausgewertet werden zu verdichten.

Damit werden für den server xy nicht mehr 30.000 datensätze sondern nur ein bruchteil davon zur auswertung aufgehoben. Da eine verdichtung natürlich auch einen verlust an datentiefe bedeutet, muss man vorher genau recherchieren, was zur auswertung benötigt wird...

Wenn das bekannt ist, kannst du einfach mit einem

select count(*) as anzahl, server_name... from tbl group by server_name...
deine daten weit verdichten.

Auf Normalisierung im db-design würde ich aus performanzgründen nur im äußerstem notfall verzichten.

[Christoph]

Ich habe mich daher gefragt, ob es nicht sinnvoller wäre, für jeden Tag einfach eine Tabelle anzulegen ( bsp.: tbllog-2005-09-xx ) um dann die Menge an zu bearbeitenden Daten gering zu halten.


Das Grundprinzip relationaler Datenbanken lautet: die Tabellenstrukturen sind konstant, nur der Inhalt ändert sich. Davon solltest Du niemals abweichen!

EIn beliebter Trick um das Einfügen bei vielen Indizes zu beschleunigen ist, vor dem Masseninsert die Indizes zu droppen und sie nach dem Massenimport wieder anzulegen. Ebenfalls hilfreich kann ein gelegentliches Commit sein (z.B. alle 2000 Sätze, aber natürlich auf keinen Fall nach jedem INSERT); bei Oracle ist das wesentlicher (ROLLBACK-Segment-Überlauf) als bei Postgres, aber einen Versuch ist es allemal wert.

[Dellerium]

Hallo nochmal,

bin gerade am testen und hab nochmal in den Thread geschaut :)

@Christoph:

Der Grund weshalb ich mir überlegt habe testweise mal für jeden Tag eine eigene Tabelle anzulegen ist folgender:

Es kann ( muss nicht unbedingt, ist aber nicht unwarscheinlich ) passieren, das ich an einem Tag 100.000 ( oder mehr ) Einträge in die DB schieben will... ( Auf die Gefahr hin, das ich jetzt ausgelacht werde - nein, ich hab bisher noch keine so grossen Erfahrungen mit Postgres gesammelt :D Daher weiss ich auch nicht, ob das überhaupt realistisch ist, mit solchen Datenmengen zu arbeiten )

Das mag ja ein paar Tage gut gehen, aber wenn man das jetzt ein ganze Jahr lang macht, dann dürften die Datenmengen irgendwann hinderlich werden möchte ich einmal annehmen. Die Daten müssen allerdings in dem Zustand bleiben wie sie sind. Soll heissen das ich die Daten wirklich so brauche wie sie sind.

Deshalb die Idee, die Daten auf eine Anzahl von Tabellen zu verteilen. In der Regel sind immer nur die Daten eines Tages interessant.

[mwanaheri]

Also rein vom Volumen her dürfte es dauern, bis du ans Limit kommst:

(aus den FAQs der postgres-Webseite)
4.4) What is the maximum size for a row, a table, and a database?

These are the limits:

Maximum size for a database? unlimited (32 TB databases exist)
Maximum size for a table? 32 TB
Maximum size for a row? 1.6TB
Maximum size for a field? 1 GB
Maximum number of rows in a table? unlimited
Maximum number of columns in a table? 250-1600 depending on column types
Maximum number of indexes on a table? unlimited

Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer when these values get unusually large.

The maximum table size of 32 TB does not require large file support from the operating system. Large tables are stored as multiple 1 GB files so file system size limits are not important.

The maximum table size and maximum number of columns can be quadrupled by increasing the default block size to 32k.

One limitation is that indexes can not be created on columns longer than about 2,000 characters. Fortunately, such indexes are rarely needed. Uniqueness is best guaranteed by a funtion index of an MD5 hash of the long column, and full text indexing allows for searching of words within the column.

Wenn eine Tabelle 32 Terabyte haben darf, dürfte der Speicherplatz das größere Problem sein.

[Dellerium]

Also rein vom Volumen her dürfte es dauern, bis du ans Limit kommst:

(aus den FAQs der postgres-Webseite)
4.4) What is the maximum size for a row, a table, and a database?

These are the limits:

Maximum size for a database? unlimited (32 TB databases exist)
Maximum size for a table? 32 TB
Maximum size for a row? 1.6TB
Maximum size for a field? 1 GB
Maximum number of rows in a table? unlimited
Maximum number of columns in a table? 250-1600 depending on column types
Maximum number of indexes on a table? unlimited

Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer when these values get unusually large.



Die FAQ kenne ich - und deshalb zerbreche ich mir ja auch den Kopf. Ich mache mir weniger deshalb Sorgen, weil Postgres die Mengen nicht verarbeiten kann, sonder weil ich nicht genau weiss, wie schnell das Ganze dann noch ist.

-> "Performance may suffer when these values get unusually large."

Die Frage ist nun, ab wann eine Menge als unnormal gross bezeichnet wird... gehe ich mal von 100.000 Datensätzen aus, dann macht das innerhalb von einem Jahr 36.500.000 Datensätze.

[Christoph]

Die Frage ist nun, ab wann eine Menge als unnormal gross bezeichnet wird... gehe ich mal von 100.000 Datensätzen aus, dann macht das innerhalb von einem Jahr 36.500.000 Datensätze.

Wie lange willst Du deine Daten denn aufheben? Wenn Du Sie z.B. ein Jahr aufhebst, dann Kann z.B. ein Bitmap Index (hat die neueste PG Version 8.1) auf den Monat die Abfrage nach Monaten unglaublich beschleunigen.

Andererseits ändern sich alte Daten ja nicht. Also würde ich empfehlen, vorverdichtete Daten (z.B. pro Tag) zu berechnen und in Tabellen zu speichern. Wiel die Daten sich nicht ändern ist Redundanz kein Problem.

[elrond]

die antwort darauf könnte eine regelmäßige verdichtung der Daten sein. so.

btw denke ich nicht, dass die 36 mio Datensätze die sache fürchterlich langsam machen. Allerdings wird eine solche db sehr "unhandlich" bzgl. Datensicherung / Kopie auf Testsystem usw.

[Dellerium]

die antwort darauf könnte eine regelmäßige verdichtung der Daten sein. so.

btw denke ich nicht, dass die 36 mio Datensätze die sache fürchterlich langsam machen. Allerdings wird eine solche db sehr "unhandlich" bzgl. Datensicherung / Kopie auf Testsystem usw.

Ich kann die Daten nicht verdichten, bzw. darf es nicht. Die Daten - auch wenn es sehr viele sind - müssen exakt so vorliegen wie sie gesammelt wurden. :(

Die 36 mio Datensätze ware nur ein Beispiel... es kann auch wesentlich mehr, oder wesentlich weniger sein. Was das angeht bin ich aber Pessimist - deshalb soll es lieber mit noch wesentlch grösseren Datenmengen umgehen können. Sagen wir einfach Faktor 10 grösser.

Das das Handling der Daten dann nicht einfach wird ist klar. Das merke ich schon bei einer anderne DB die wir hier im Einsatz haben. Da dauert das Einspielen eines Dumps im günstigsten Fall 1,5 Stunden wenn fsync aus ist... sonst die doppelte bis dreifache Zeit.


@Christoph: Das mit dem Bitmap Index werde ich mir einmal anschauen, danke.

[Turbohummel]

Im Umgang mit derart großen (um nicht zu sagen "gigantischen") Datenbeständen geben sich MySQL und PostGres nicht viel. So ist es jedenfalls bei 85 Mio Datensätzen (ebenfalls Logs, täglich kommen bis zu 75000 dazu) mit 5 Spalten mit einem FK-Index.

Schneller dagegen waren der MS SQLserver und MaxDB (ca. 1,5 Sekunden (ca. 40%) schneller bei ner Select mit 2 Where-Bedingungen)
MaxDB ist auf Auswertungen ausgerichtet, da es speziell als Backend für SAP-Systeme ausgelegt ist, welches ja in größeren Firmen auch sehr viele Daten analysieren muss. Bei den Inserts war der SQLserver geringfügig schneller.

Die Benchmarks wurden von meiner Firma vor etwa einem Jahr durchgeführt, dabei sind wir von Oracle (wegen Unzufriedenheit mit dem Support) auf ein anders System umgestiegen.

Letztendlich haben wir uns für den SQL-Server entschieden, da die Logs zwar erhalten bleiben mussten, aber für Auswertungen ein Cron die Daten verdichten konnte (jewails auf Tage und auf Monate).

Ist das bei dir nicht der Fall, würde ich mir MaxDB mal ansehen.

Backups machten übrigens mit keinem der Systeme Schwierigkeiten, nur bei MySQL lies sich ein Backup erst beim 2. Versuch einspielen. War aber ein Einzelfall.

EDIT:
Noch n Tipp: Immer so Arbeiten, dass man die Datenbank austauschen kann, ohne am Code was ändern zu müssen. Sprich: Nur nach SQL 99 oder 03 arbeiten.

[Dellerium]

Im Umgang mit derart großen (um nicht zu sagen "gigantischen") Datenbeständen geben sich MySQL und PostGres nicht viel. So ist es jedenfalls bei 85 Mio Datensätzen (ebenfalls Logs, täglich kommen bis zu 75000 dazu) mit 5 Spalten mit einem FK-Index.

Schneller dagegen waren der MS SQLserver und MaxDB (ca. 1,5 Sekunden (ca. 40%) schneller bei ner Select mit 2 Where-Bedingungen)
MaxDB ist auf Auswertungen ausgerichtet, da es speziell als Backend für SAP-Systeme ausgelegt ist, welches ja in größeren Firmen auch sehr viele Daten analysieren muss. Bei den Inserts war der SQLserver geringfügig schneller.

Die Benchmarks wurden von meiner Firma vor etwa einem Jahr durchgeführt, dabei sind wir von Oracle (wegen Unzufriedenheit mit dem Support) auf ein anders System umgestiegen.

Letztendlich haben wir uns für den SQL-Server entschieden, da die Logs zwar erhalten bleiben mussten, aber für Auswertungen ein Cron die Daten verdichten konnte (jewails auf Tage und auf Monate).

Ist das bei dir nicht der Fall, würde ich mir MaxDB mal ansehen.

Backups machten übrigens mit keinem der Systeme Schwierigkeiten, nur bei MySQL lies sich ein Backup erst beim 2. Versuch einspielen. War aber ein Einzelfall.

EDIT:
Noch n Tipp: Immer so Arbeiten, dass man die Datenbank austauschen kann, ohne am Code was ändern zu müssen. Sprich: Nur nach SQL 99 oder 03 arbeiten.

Hallo und danke für die Infos :)

Im Moment mache ich das so, das ich mich soweit es irgendwie geht nach SQL99 richte und die Datenbankzugriffe über eine extra Klasse kapsele. Ich bereite das System schon darauf vor, durch weitere Klassen auch andere Datenbanken anbinden zu können.