Sehr große Datanbank machbar? Tipps?

Hallo,


ich habe den Auftrag 1 Mrd. (1.000.000.000) Datensätze (a 25 Felder) in einer Datenbank zur Verfügung zu stellen. Da die Daten alle georeferenziert sind soll das ganze mit PostGis passieren.
Ich habe einen Ubuntu 10.4 Server (i3, 4GB Ram, 1TB HDD) mit einer fertige PostGis Datenbank (Standard Einstellungen).
Einen Importer habe ich auch schon geschrieben…

Jetzt bekomme ich aber doch ein bissel kalte Füße da der Import sicher mehrere Tage dauern wird…

Kann die Datenbank mit 1 Milliarde Einträgen umgehen?
Kann ich diese alle in eine Tabelle packen oder ist es sinnvoll mehrere Tabellen / Datenbanken zu nutzen?
Wie kann ich die Performance optimieren?

Die Tabelle wird danach nicht mehr verändert und es greifen auch maximal 2 bis 3 Nutzer gleichzeitig darauf zu.

Ich bin alle Tipps und Erfahrungen dankbar!

Gruß TK

Hallo!

Das geht schon, allerdings sind einige Vorüberlegungen nötig.

  1. Was sind das denn für Daten? Sind das große Geometrien oder nur Punkte? Mit Groß mein ich >200000 Punkte pro Polygon oder so :wink: . Hier könnte eine Aufbereitung nach Douglas-Poiker o.ä Abhilfe schaffen, wenn möglich.
    2 . Wie sollen sie abgefragt werden? Eventuell ist eine Partitionierung der Tabelle nützlich. wenn z.B. oft nach Postleitzahlen abgefragt wird, ist ggf. eine solche Partitionierung nach Postleitzahlbereichen nützlich.
    3.Eminent wichtig ist hier die Verwendung von Indices, die die Suchanfragen auch unterstützen. Vor allen ein Index auf die Geometrie ist hier sehr wichtig, z.B. wenn eine WMS-Abfrage stattfinden soll.
  2. Hast du viele teure Union/Intersect etc- Abfragen, ist es ggf. sinnvoll große Geometrien in Abschnitte zu zerteilen. Das ist zwar mit Geos3.1 sehr viel schneller geworden, aber halt immer noch recht teuer.
  3. Überprüfen, wie mit den Daten gearbeitet werden soll. Eventuell ist es sinnvoll, den einzelnen Bearbeitern Auszüge in einer eigenen Datenbank bereitzustellen.
    Das kann man alles weitgehend auch automatisieren. Der (einmalige) Import ist halt was, wo man schwer drumrum kommt :wink:
    Wie liegen die Daten denn vor? Tabellen oder Shapes? Schreibst du per Copy oder Insert?

Was ganz kritisch wird, sind die 4GB. Da sollten schon 8 oder mehr draus werden, vor allem, wenn noch was anderes da laufen sollte oder umfangreiche Abfragen stattfinden. Zum Feintuning von shared_mem oder work_mem kommt aber bestimmt auch noch was.
Soviel erst mal…

Gruß Uwe

Hallo Gisman,

Ersteinmal danke für die Antwort, ich bin was Datenbanken angeht leider noch ein unbeschriebens Blatt…
Bei den Daten handelt es sich nur um Punkte. Diese liegen als CVS Dateien vor (20000 Dateien mit so durchschnittlich 50000 Einträgen).
Diese werden alle per INSERT eingetragen.

Später sollen mit einer Gui anhand einer Kartenansicht Datensätze generiert werden wie z.B. alle Messpunkte innerhalb eines Radiuses um eine Koordinate oder
alle Punkte die an einer Strecke von Koordinate A nach B liegen. Diese kleineren Datensätze werden dann separat verarbeitet.

Ich hatte vor den Index nach dem Import anhand der Geometrie zu erstellen!

Die Datenbank wird nach der Erstellung nicht mehr geändert sprich es muß keine neue Indizierung stattfinden…

Die 4GB sind sicher nicht genug da werden wohl noch mal 4 oder gar 8 GB folgen, ich habe einiges über shared Buffer gelesen gibt es dazu Tips?

Viele Grüße
TK

Die Faustregel ist etwa 25% vom RAM als Shared Buffers zu nehmen. Aber je nach workload können da auch andere Werte sinnvoll sein. Und es gibt noch ein paar andere Parameter.

Falls es die Daten und die Abfragen zulassen, solltest Du über Normalisierung und table-partitioning nachdenken.

Ach ja: INSERT bei der Menge an Daten ist absolut suboptimal. Wenn die als CSV vorliegen, dann nehme COPY.


Andreas

Bei der Menge Daten macht 25% nur noch wenig Sinn. In der Regel wirst du bei Scans über diese Datenmengen die Tabellen immer und immer wieder durchlaufen und dein Cache ist regelmäßig leer.
Das ganze ist ein typisches Data Warehouse Problem und keine OLTP-Betrachtung mehr.

Wie akretschmer sagt: probier dich mit COPY, das beschleunigt zumindest das Einlesen.
Für den täglichen Betrieb müsste man aber im Detail schauen, was man wo optimieren kann …

Ich sage jetzt hier nicht, das meine Firma für so etwas fertige Lösungen hat :wink:

Und, was würdest Du empfehlen?

IMHO ist es relativ egal, ob 25% oder auch 50%, die 4GByte reichen so oder so nicht wirklich. Aber: es kommt auch auf die Abfragen an, insbesondere, ob diese passende Indexe nutzen können. Falls ja, wäre das alles noch nicht dramatisch, aber falls Abfragen ohne Indexe da laufen wird es böse. Egal, wieviel % man für Shared nimmt. Und ja: ich bezweifle, daß Euer Produkt zaubern kann …


Andreas

Es zaubert nicht, es parallelisiert.
Es macht in der Größenordnung keinen Sinn mehr, alles im Shared Buffers halten zu wollen.
Statt dessen möchte man die Daten möglichst schnell zu einer oder mehreren CPUs zum Scannen bekommen.

Komm zur FrOSCon, ich zeige dort, wie das geht :wink:

Darf ich mal aus dem Ursprungsposting zitieren?

“Ich habe einen Ubuntu 10.4 Server (i3, 4GB Ram, 1TB HDD)”

Unter dieser Bedingung könnt Ihr wohl kaum “die Daten möglichst schnell zu … mehreren CPUs zum Scannen bekommen”, oder?


Aber wenn wir schon mal dabei sind: könnt ihr (auf einem Eisen) mehrere CPU’s für eine Anfrage nutzen? Das wäre dann allerdings (eventuell) ein echtes Plus…



Andreas

Ja, Greenplum kann das.

Generell - und das ist keine Greenplum Eigenheit - ist bei Data Warehouse typischen Anfragen das Disk I/O das Limit.
Du musst halt durch zu viele Daten möglichst schnell durch … das ist ein beträchtlicher Unterschied zur OLTP-Welt.
Mit dem Gedanken hatte ich Anfangs (sprich: als ich bei Greenplum angefangen habe) auch ganz schön zu kämpfen.

Guten Morgen!

Hier kann auch optimiert werden. Was ist denn das für eine GUI? Desktop-GIS oder Webmapping? Man könnte überlegen, die Massendaten per WMS auch für das DesktopGIS bereitzustellen. Grund: Wenn man Die Tabelle als Tabelle einliest, muß die komplett gelesen werde, das dauert ewig. Der WMS liest nur das, was im Auschnitt liegt. Attribute können dann ggf. über einen WFS bereitgestellt werden, was auch schnell ist, da sehr präzisse in der Tabelle gesucht werden kann.
Nach deiner Beschreibung brauchst du einige Buffer- und Intersect-Abfragen, das ist nicht unbedingt billig zu haben. Es ist also sinnvoll, diese kleineren Datensätze ebenfalls in PostGIS bereitzustellen. Hier könnte sich ggf. noch die Möglichkeit bieten über Views oder materialized Views komplexere Abfragen vorzubereiten, vor allem wenn sich die Daten nicht mehr ändern. Auch könnte sich eine Optmierung der Daten als vorteilhaft erweisen, z.B. um unnötige Punkte in Linien zu entfernen (bremst Buffer aus z.B.).
Allerdings ist hier bei allem schon ein Limit durch die Hardware gesetzt.

Viele Grüße
Uwe

Hallo

Ich habe gestern in meinem jugendlichen Leichtsinn dann auch gleich mal versucht meine Importer zu starten (der mit INSERT arbeitet),
worauf hin ich feststellen musste das ich für die 1,5 Mrd Einträge wohl um die 100 Tage brauchen würden.

Woraufhin ich mich dann panisch in den COPY Befehl eingelesen habe…
Der funktioniert zwar schnell wirft mir aber andere Probleme entgegen…

Die Datenbank ist UTF-8 formatiert und muss es wohl auch bleiben (ich glaube das ist eine Vorgabe von PostGis korrigiert mich bitte wenn ich falsch liege)
die CSV Dateien sind aber LATIN Codiert… was jetzt dazu führt das ich entweder einen Workaround finde oder alle 20000 Dateien konvertieren muss…

Auch will COPY anscheinend nur varchar als column typ das kann ich zwar nachträglich ändern aber ich weiß nicht genau wie lange das dann wieder dauert…

Die GUI ist in diesem Falle Desktop Gis Marke Eigenbau, unnötige Datensätze gibt es leider nicht und es sollen hauptsächlich Statistische Informationen gewonnen…

Schau mal, ob du “client_encoding” passend setzen kannst.

Ok werde ich versuchen, inzwischen habe ich mir eine Batch Datei geschrieben mit der ich gerade alle 2000 Dateien in UTF-8 convertiere :smiley: . sicher ist sicher…

Übernimmt COPY auch das Clientencoding?

COPY selbst nicht. Aber wenn du “client_encoding” passend setzt, macht das die Datenbank für dich.

Das hat so auch alles funktioniert! Habe die client encoding auf Latin1 gesetzt und schon lief es… wenn gewünscht werde ich gerne mal ein paar Erfahrungen über die Performance posten!

Sehr gewünscht.
Alternativ: komm zur FrOSCon, dort treffen sich eine Reihe PostgreSQL Leute und es gibt einen Tag lang Vorträge.
Dürfte auch etwas für sich dabei sein.

Hier mal ein kleines Update…

Ich habe jetzt fürs erste eine große Tabelle mit 450 000 000 Einträgen, für den Rest wird dann wohl ein zweiter Server kommen, upload mit COPY hat so 6 Stunden gedauert,
erzeugen der Geometrie nochmal so 10 Stunden und das erstellen des Indexes etwa 19 Stunden.

Eine Suchanfrage nach Punkten um einen Linestring herrum kommt nach etwa 2 bis 3 Minuten zurück.

Den Shared Buffer konnte ich noch nicht ändern weil Ubuntu da nicht mehr als 16MB zulässt.

Working Memory habe ich auch 1 GB, maintenance memory auf 512 MB…

Ich schaue da jetzt erst mal wieder wie das Schwein ins Uhrwerk und werde jetzt langsam versuchen weiter zu optimieren.

Was denkt ihr kann man da jetzt noch an Performance rausholen??

Du weisst schon, das man das im Betriebssystem einstellen kann?
Steht eigentlich in so ziemlich jeder Dokumentation beschrieben, wie man shared buffers berechnet und im OS anpasst.