Postgres beschleunigen

SunCobalt · 2011-02-09T19:46:39.000Z

Hallo,

ich suche nach einer Möglichkeit, Postgres zu beschleunigen. Es geht besonders darum, bei geringen Zoomleveln die Abfrage zu beschleunigen. Bei geringen Zoomleveln wird ja nicht viel gerendert. Also wenn ich bspw ganz Europa anschaue, müssen ja alle Daten für den Bereich durchsucht werden obwohl nur wenige Daten gerendert werden. Ich habe was über einen Materialized View gelesen. Reicht es den einzurichten und das wars weils im Hintergrund läuft? Oder müssen dann auch die SQL Queries in den Rendering Regeln angepasst werden. Und hilft es mehrere Materialized Views für verschiedene Zoom Level einzurichten? Einerseits sollten die Resultate schneller da sein, andererseits bläht es die DB auf, was die Sache wieder langsam macht…

Achja, über “normale” Tipps zur Beschleunigung von Postgres freue ich mich natürlich auch.

wambacher · 2011-02-09T21:32:52.000Z

SunCobalt:

Hallo,

ich suche nach einer Möglichkeit, Postgres zu beschleunigen. Es geht besonders darum, bei geringen Zoomleveln die Abfrage zu beschleunigen. Bei geringen Zoomleveln wird ja nicht viel gerendert. Also wenn ich bspw ganz Europa anschaue, müssen ja alle Daten für den Bereich durchsucht werden obwohl nur wenige Daten gerendert werden. Ich habe was über einen Materialized View gelesen. Reicht es den einzurichten und das wars weils im Hintergrund läuft? Oder müssen dann auch die SQL Queries in den Rendering Regeln angepasst werden. Und hilft es mehrere Materialized Views für verschiedene Zoom Level einzurichten? Einerseits sollten die Resultate schneller da sein, andererseits bläht es die DB auf, was die Sache wieder langsam macht…

Achja, über “normale” Tipps zur Beschleunigung von Postgres freue ich mich natürlich auch.

hi,
ich nehme mal an, dass du von dem osm2pgsql-schema und dann mapnik sprichst - oder?

ohne dass ich “materialized views” kenne (ein link wäre sehr nett gewesen und zum suchen hab ich echt keine ~~lust~~ zeit),
kann ich dir nur sagen, dass views keinerlei performancegewinn bringen können; views machen queries nur einfacher/schöner aber nicht schneller.
was was bringen könnte, sind trigger - das sind psql-prozeduren, die automatisch z.b. bei einem update eines objektes schon alles berechnen, was später mal benötigt werden könnte.
der update wird etwas langsamer aber der zugriff auf die daten später schneller.
in etwa so, wie postgresql automatisch den index updated, wenn es einen geben sollte.
Gruss
Walter

Nop · 2011-02-09T23:50:34.000Z

Leider schreibst Du nicht, was für ein Setup Du genau mit Deiner Postgres hast. Wenn’s um’s Mapnik-Rendern geht gibt es ein paar Methoden, sie deutlich schneller zu kriegen, aber keine davon ist geschenkt.

Pack die DB auf eine SSD (keine Arbeit, bei mir 5x schneller)
Layers optimieren. Objekte mit ähnlichem Zoomlevel zusammen in eine Layer tun. Wenn Objekte mit unterschiedlichem Zoomlevel in derselben Layer stehen, werden immer alle geholt und die meisten wieder weggeworfen. Bringt aber mehr bei mittleren Zoomleveln.
Select-Statements optimieren. In den Standard-Mapnik-Sheets wird oft sehr allgemein abgefragt auf tag != null. Dadurch wird oft von der DB zuviel geholt und wieder weggeschmissen. Mit möglichst genauen SELECT-Statements wird das besser, z.B. highway=track anstatt highway != null.
Partielle Indexes anlegen. Wenn man einen partiellen gist-Index anlegt, der vom Ausschnitt her genau zu einem Select-statement paßt, rödelt die Datenbank nicht mehr über alles drüber, sondern nimmt nur noch diesen Ausschnitt. Dadurch wird die Layer deutlich schneller.

bye
Nop

SunCobalt · 2011-02-10T06:17:22.000Z

http://en.wikipedia.org/wiki/Materialized_view
Kurz gesagt, wenn man in einem bestimmten Zoomlevel nur Autobahnen, Hauptstädte und Flüsse rendern will, legt man so einen Materialized View an. Im Gegensatz zur einem normalen View, werden da dann auch Hauptstädte, Flüsse und Autobahnen extra nochmal gespeichert. Hat den Vorteil, dass bei großen Gebieten nicht alle Daten durchsucht werden müssen. Okay, bei sehr geringen Vergrößerungen arbeite ich eh mit dem Tilecache (und tilecache_seed) aber speziell Zoom Levels 10-12 sind aufgrund der Menge an Daten teils etwas langsam und alle Tiles vorab zu berechenen und Cachen ist etwas zuviel des gutem.

Mein Setup ist Postgres 9.0, PostGIS 1.5.4, gefüllt mit osm2pgsql (etwas andere Config als Standard), Maperserver 5.6.5 und Tilecache, Debian (headless)

SSD geht leider nicht, wäre bei einem Desktop PC sicher eine gute Möglichkeit. Oft sehe ich im Prozessmonitor 100% Auslastung, es müsste also auch ohne SSD was zu optimieren sein.
Die select Statements habe ich auch schon im Blick. Da werden teilweise Daten abgegriffen, die nicht benutzt werden. Das mit den partiellen gist-Indizes werde ich mir mal anschauen. Danke für den Tipp

EvanE · 2011-02-10T10:41:23.000Z

SunCobalt:

SSD geht leider nicht, wäre bei einem Desktop PC sicher eine gute Möglichkeit. …

Hallo Thomas

SSD-Platten haben meist den Formfaktor 2,5 Zoll (6,35 cm).
Die lassen sich wunderbar extern anschliessen.

Hauptvorteil für eine Datenbank ist halt, dass es keine mechanischen Positionierzeiten gibt. Das macht über die große Anzahl an Zugriffen in Summe eine Menge aus.

Falls Postgres das zulässt, reicht es eventuell aus nur die Index-Teile auf eine SSD auszulagern, um eine spürbare Beschleunigung zu erreichen. Damit bliebe der finanzielle Aufwand überschaubar.

Edbert (EvanE)

viw · 2011-02-10T10:54:23.000Z

EvanE:

SunCobalt:

SSD geht leider nicht, wäre bei einem Desktop PC sicher eine gute Möglichkeit. …

Hallo Thomas

SSD-Platten haben meist den Formfaktor 2,5 Zoll (6,35 cm).
Die lassen sich wunderbar extern anschliessen.

Hauptvorteil für eine Datenbank ist halt, dass es keine mechanischen Positionierzeiten gibt. Das macht über die große Anzahl an Zugriffen in Summe eine Menge aus.

Falls Postgres das zulässt, reicht es eventuell aus nur die Index-Teile auf eine SSD auszulagern, um eine spürbare Beschleunigung zu erreichen. Damit bliebe der finanzielle Aufwand überschaubar.

Edbert (EvanE)

Wenn du die Sache schon extern anschließen möchtest und dann nur den Index auslagern liegt doch die Nutzung eines USB Sticks nahe, oder?

EvanE · 2011-02-10T13:20:13.000Z

viw:

EvanE:

SunCobalt:

SSD geht leider nicht, wäre bei einem Desktop PC sicher eine gute Möglichkeit. …

SSD-Platten haben meist den Formfaktor 2,5 Zoll (6,35 cm). Die lassen sich wunderbar extern anschliessen.

Hauptvorteil für eine Datenbank ist halt, dass es keine mechanischen Positionierzeiten gibt. …
Falls Postgres das zulässt, reicht es eventuell aus nur die Index-Teile auf eine SSD auszulagern, um eine spürbare Beschleunigung zu erreichen. Damit bliebe der finanzielle Aufwand überschaubar.

Wenn du die Sache schon extern anschließen möchtest und dann nur den Index auslagern liegt doch die Nutzung eines USB Sticks nahe, oder?

Zuerst dachte ich, was für ein Blödsinn. Das war aber ein Irrtum.

Ich habe bei Conrad mal USB-Sticks (USB 3.0) mit SSD-Platten und jeweils 64 GB verglichen.

Die USB-Sticks haben eine vergleichbare Geschwindigkeit
Die USB-Sticks kosten 200/260/310 Euro
(mit 80/240/320 MB/s Lesegeschwindigkeit)
Die SSD-Platten mit 64 GB kosten von 100 bis 170 Euro
Ab 130 Euro gibt es Lesegeschwindigkeiten über 200 MB/s
Dazu käme noch ein Gehäuse mit USB 3.0 Schnittstelle
für 20 - 25 Euro.

Uuups, SSD-Platten sind bei 64 GB Kapazität günstiger als USB-Sticks.
Ich habe 32 GB und 128 GB nicht verglichen. Da könnte die Waage anders ausschlagen.

Mein Fazit: Beide Wege sind gangbar.
Es ist dabei auf USB 3.0 und die Transfer-Geschwindigkeit zu achten.

Edbert (EvanE)

viw · 2011-02-10T13:38:34.000Z

EvanE:

Ich habe bei Conrad mal USB-Sticks (USB 3.0) mit SSD-Platten und jeweils 64 GB verglichen.

Die USB-Sticks haben eine vergleichbare Geschwindigkeit

Die USB-Sticks kosten 200/260/310 Euro
(mit 80/240/320 MB/s Lesegeschwindigkeit)

Die SSD-Platten mit 64 GB kosten von 100 bis 170 Euro
Ab 130 Euro gibt es Lesegeschwindigkeiten über 200 MB/s
Dazu käme noch ein Gehäuse mit USB 3.0 Schnittstelle
für 20 - 25 Euro.

Uuups, SSD-Platten sind bei 64 GB Kapazität günstiger als USB-Sticks.
Ich habe 32 GB und 128 GB nicht verglichen. Da könnte die Waage anders ausschlagen.

Mein Fazit: Beide Wege sind gangbar.
Es ist dabei auf USB 3.0 und die Transfer-Geschwindigkeit zu achten.

Edbert (EvanE)

Also die Einsparungsidee lag ja gerade darin dass nicht die volle Kapazität von 64 GB gebraucht wird. Zumal wenn nur der Index ausgelagert werden soll.
Aber gerade bei der Klasse 125 MB/s lesen geht es auch schon mit 64 GB schon für 130 Euro

Außerdem wäre für älter Computer denkbar auf eSATA zusetzen, statt auf den ganz neuen USB3.0 Anschluss. Die Preise sind dann ganz ähnlich.

Dennoch halte ich es für einen Test einfach für zu teuer.

EvanE · 2011-02-10T14:45:35.000Z

viw:

EvanE:

Ich habe bei Conrad mal USB-Sticks (USB 3.0) mit SSD-Platten und jeweils 64 GB verglichen.

…
Uuups, SSD-Platten sind bei 64 GB Kapazität günstiger als USB-Sticks. …
Mein Fazit: Beide Wege sind gangbar. …

Also die Einsparungsidee lag ja gerade darin dass nicht die volle Kapazität von 64 GB gebraucht wird. Zumal wenn nur der Index ausgelagert werden soll.
Aber gerade bei der Klasse 125 MB/s lesen geht es auch schon mit 64 GB schon für 130 Euro

Außerdem wäre für älter Computer denkbar auf eSATA zusetzen, statt auf den ganz neuen USB3.0 Anschluss. Die Preise sind dann ganz ähnlich.

Dennoch halte ich es für einen Test einfach für zu teuer.

Nun ja, für die volle Kapazität bin ich von 256 GB ausgegangen.
Wenn man mit 32 oder gar 16 GB für die Index-Teile auskäme, dürften die USB-Sticks irgendwann gewinnen. Allerdings sind bei USB-Sticks hohe Geschwindigkeiten immer teuer im Vergleich zu langsameren Sticks.

Ich habe Conrad genommen, weil der eine breite Auswahl hat. Dass man woanders die Sachen zum Teil nochmal deutlich günstiger bekommen kann ist bekannt. Allerdings dürften die Verhältnisse auch bei SSD-Platten ähnlich sein.

Ein USB-Stick mit eSATA ist mir nicht bekannt. Für eine SSD-Platte wäre es wohl kein Problem ein passendes Gehäuse mit eSATA zu finden.

Ob es für einen Test zu teuer ist, kann nur SunCobalt selbst entscheiden.

Schnell → teuer
Langsam → geringe Aussagekraft.

Es gibt noch die Variante mit einer Hybrid-Festplatte.
Z.B. Seagate Momentus XT mit 500 GB, 7.200 U/min, 32 MB Cache und 4 GB Flash-Speicher, der Leseoperationen stark beschleunigen kann (ca. 120 Euro). Ist halt die Frage ob SunCobalt mit 500 GB insgesamt auskommt.

Edbert (EvanE)

SunCobalt · 2011-02-10T19:07:29.000Z

eigentlich dachte ich mehr an daran, an der Software was zu beschleunigen. Eine SSD Platte wäre wahrscheinlich Perlen für die Säue. Zum einen ist der Rechner nicht mehr der neuste und wird zusätzlich ja noch teilweise von den ODBL Statistiken in Beschlag genommen, zum anderen ist mein Upstream mit 1MBit nicht der Beste.
In absehbarer Zeit wird aber eh ein neuer Server angeschaft. Da werde ich die Tipps (danke dafür) berücksichtigen. Wegen den noch recht hohen Preisen dachte ich für eine Kombination aus kleiner SSD für die Indizes und RAID 0 mit konventionellen Platten. Ich will ja keine öffentliche Karte betreiben.
Momentan rendere ich sprachlich angepasste Karten (die ich nichtmmal lesen kann ), die als Test für eine Realisierbarkeit, speziell im Hinblick auf Mapserver statt Mapnik, dienen.

wambacher · 2011-02-10T21:47:57.000Z

hi thomas,

an die regelmässigen Pflichtübungen eines DB-Admins denkst du wohl, oder?
analyze und vacuum? oder besser autovacuum - die bringen auch was.
Gruss
Walter

EvanE · 2011-02-11T01:30:45.000Z

SunCobalt:

eigentlich dachte ich mehr daran, an der Software was zu beschleunigen. Eine SSD Platte wäre wahrscheinlich Perlen für die Säue. Zum einen ist der Rechner nicht mehr der neuste … zum anderen ist mein Upstream mit 1MBit nicht der Beste.

In absehbarer Zeit wird aber eh ein neuer Server angeschaft. Da werde ich die Tipps (danke dafür) berücksichtigen. Wegen den noch recht hohen Preisen dachte ich für eine Kombination aus kleiner SSD für die Indizes und RAID 0 mit konventionellen Platten. …

Eine SSD-Platte geht dir ja nicht verloren. Die kannst du in den neuen Rechner übernehmen.
Als kostengünstigere Alternative gibt es noch die Hybrid-Platte Seagate Momentus XT.
Daraus ein RAID und die Post geht ab. Nachteil ist neben dem Preis die Begrenzung auf zur Zeit 500 GB. Keine Ahnung ob du damit für die DB auskommen kannst.

Edbert (EvanE)

mongolean · 2011-02-11T06:13:44.000Z

Hi,

ich bin vor einigen Tagen über OSM gestolpert und finde das Projekt wirklich spannend. Bezüglich der Performanceprobleme an der API würd mich interessieren, wie der Systemaufbau wirklich ausssieht. Laut dem Komponentendiagramm im WIKI sind ja die API und das Osmosis Tool die einzigen direkten Kontaktstellen zur Pg Datenbank.

Wie sieht denn das Datenbankschema genau aus? Sind das wirklich nur ein paar große Table, so wie die CREATE Statements im Wiki das zeigen? Habt ihr die Tables schon partitioniert? Wenn man das nach Längen (und Breitengraden) partitioniert, kann man sicher einen ordentlichen Performancegewinn erreichen. Und so wie ich das sehe (ich weiß allerdings nicht, wie umfangreich die API ist) hält sich der Portierungsaufwand auch in Grenzen, da es nur zwei Schnittstellen zur Datenbank gibt.

Nur mal so aus interesse…

lga

TobWen · 2011-02-12T01:31:42.000Z

Langsames Datenbankschema durch schnellere Festplatten beschleunigen?

Irgendwie sehe ich eine Parallele in die Realität (Kein Konstrukt, sondern alle 4-5 Wochen der Fall.):
Bei uns im Kreis Soest wurde vor kurzem der Ärzte-Notdienst neu organisiert: Wenn ich (Werl) nun krank bin, muss ich 20 km bis zu einem Notdienst-Zentrum (Soest) fahren. Dort wird mir gesagt, was ich habe und welche Medikamente ich mir holen soll. Da der Ärzte-Notdienst aber nichts mit dem Apotheken-Notdienst zu tun hat, kann die notddiensthabende Apotheke im Nachbarort (Wickede (Ruhr)) sein: 25 km von Soest und 8 km von Werl weg. Das Krankenhaus in Werl hat strikte Anweisung, keine akuten Notfälle anzunehmen und nach Soest zu verweisen.

Das heißt: Werl - Soest - Wickede - Werl = 20 + 25 + 8 = 53 [km]
Wenn ich mir für diese Strecke einen Lamborghini ausleihen würde (ich will ja schnell gesund werden), hätte man die gleiche Situation, wie ihr mit den SSDs

derstefan · 2012-02-22T14:22:19.000Z

Evtl. beschleunigen kann man das Rendern durch Vereinfachungen:
ST_Simplify(geom, tolerance) oder
ST_SimplifyPreserveTopology(geom, tolerance)

tolerance könnte man sich aus der aktuellen Zoomstufe berechnen: !scale_denominator!

Beispiel:

(SELECT ST_Simplify(way,!scale_denominator!/5000) AS way,boundary,admin_level FROM &prefix;_line WHERE boundary IN ('administrative')) AS borders

edit: Auf kleinen Zoomstufen kann man durchaus etwas Geschwindigkeit gewinnen, bei größeren jedoch bremst die Berechnung der Vereinfachung enorm.

rurseekatze · 2012-02-23T16:40:06.000Z

Bei der OpenLinkMap habe ich das Problem ein wenig anders gelöst. Dort filtere ich beim Import/Update möglichst viel heraus und berechne schon soviel wie möglich. Dadurch ist zwar der Import und das Updaten extrem langsam und rechenintensiv, aber der Abruf ist rasend schnell, da ich nicht mehr filtern muss, die Datenmenge stark reduziert wurde und auch schon Centroids berechnet wurden.

Diese Technik ist zwar ziemlich speziell, aber je nach Anwendungszweck kann diese Strategie von schnellem Abruf und langsamen Update aber auch die beste sein.

derstefan · 2012-02-23T22:01:58.000Z

Das Thema Geschwindigkeit ist leider auch für mich eine der großen Entwicklungsbremsen. Gut, unser “Server” ist auch nur ein ausgemusterter CIP-Rechner. Momentan braucht das Rendern von Zoomstufe 8 bis 14 von Deutschland mindestens zwei Wochen.

viw · 2012-02-24T06:00:24.000Z

derstefan:

Das Thema Geschwindigkeit ist leider auch für mich eine der großen Entwicklungsbremsen. Gut, unser “Server” ist auch nur ein ausgemusterter CIP-Rechner. Momentan braucht das Rendern von Zoomstufe 8 bis 14 von Deutschland mindestens zwei Wochen.

Und wo ist der Flaschenhals? Bist du sicher das es die Datenbank ist? Laufen alle CPU Kerne unter voller Leistung? oder ist wengistens die Festplatte an der Schreibgrenze? Andernfalls mal darüber nachdenken mehrere Renderingprozesse laufen zu lassen.

derstefan · 2012-02-24T12:36:50.000Z

Mein momentaner Flaschenhals sind die Höhendaten. Derzeit liegen diese in der gleichen Datenbank wie die importierten OSM-Daten. Ich werde in der Zukunft mal paar Dinge wie eigene Datenbank, vorberechnete Werte ausprobieren. Auf jeden Fall müssen die Höhenlinien und Beschriftungen immer neu mitgerendert werden, da ich sie nicht einfach über die fertigen Kacheln drüberklatsche, sondern wie gelernt zwischen verschiedene Ebenen hineinschiebe.

Die CPUs sind alle am Anschlag - es ist nur eine.
Eim gschenkdn Gaul schaut ma net nein Maul…