Einsatz von SSD für die Datenbank

Moins,

ganz allgemein zu Datenbank, Index, Plattengeschwindigkeiten und SSD:

Über den Index finde ich schnell heraus, wo die gewünschten Datensätze liegen. Wenn die aber zufällig über eine große Tabelle verteilt sind, wird die Geschwindigkeit des Einsammelns nicht über die Transferrate der Disk, sondern über die Seek-Geschwindigkeit begrenzt. Und da ist die SSD absolut überlegen bis zu einem Faktor 100. Die Tabelle auf einer langsamen SSD kann deshalb schneller sein als auf einer schnellen Drehdisk.

Ich kenne mich mit der Einspielroutine nicht aus, daher weiß ich nicht, ob anwendbar: wenn die meisten Abfragen auf die Datenbank kleine Gebiete betreffen, kann es sinnvoll sein, die Node-Datensätze vor dem ersten Einspielen “geographisch” zu sortieren, z.B. nach Quadtree-Werten. Das erhöht die Lokalität, oft gemeinsam genutzte Nodes liegen meist nah beieinander, damit braucht es weniger Seeks und die Geschwindigkeit geht hoch. Ohne zusätzliche Hardware.

Gruß Wolf

Bei Projekten mit dem osm2pgsql-Schema sind zwei verschiedene Aspekte zu beachten:

  • Aufbau und Update der globalen Datenbank
  • Benutzung der Datenbank für GIS-Anwendungen

Punkt 1 kann keinerlei geographische Indices verwenden, da die Updates in aufsteigender Reihenfolge nach OSM-IDs geortnet sind und diese Updates völlig zufällig über den ganzen Planeten verteilt sind. 1x Paris,Texas und gleich darauf Papua-Neuguinea und dann Glabotki-Mitte.

Für Punkt 2 werden selbstverständlich geographische Indices aufgebaut und auch verwendet. So hat z.B. die Tabelle planet_osm_point, die alle wichtigen POI enthält, einen spatialen Index. Andere Tabellen ebenso.


planet2=# \d planet_osm_point

            Table "public.planet_osm_point"
       Column       |         Type         | Modifiers 
--------------------+----------------------+-----------
 osm_id             | bigint               | 
 access             | text                 | 
 addr:housename     | text                 | 
 addr:housenumber   | text                 | 
---snip---
 tags               | hstore               | 
 way                | geometry(Point,4326) |                                <-----------
 traffic_sign       | text                 | 
Indexes:
    "idx_point_admin_level" btree (admin_level) WHERE admin_level <> ''::text, tablespace "tablespace2"
    "idx_point_place" btree (place) WHERE place <> ''::text, tablespace "tablespace3"
    "idx_point_postcode" btree ("addr:postcode") WHERE "addr:postcode" <> ''::text, tablespace "tablespace2"
    "idx_point_traffic_sign" btree (traffic_sign) WHERE traffic_sign <> ''::text, tablespace "tablespace3"
    "planet_osm_point_index" gist (way), tablespace "tablespace2"           <----------
    "planet_osm_point_pkey" btree (osm_id), tablespace "tablespace3"
    "planet_osm_point_tags_index" gin (tags), tablespace "tablespace2"
Tablespace: "tablespace4"

hier “planet_osm_point_index” gist (way) , wobei way die Koordinaten enthält. Dadurch wird bei der Auswertung (Karte) viel an Durchsatz gewonnen.

tl;dr: Mit Bordmitteln ist schon fast alles ausgereizt, die Hardware muß einfach schneller werden.

Gruss
Walter

Nahmd,

Natürlich gibt es diesen Index.

Ich sprach aber von der Tabelle, dem Speicher für die Datensätze. Da liegen die Nodes (vor dem ersten Update) in der Reihenfolge des Einspielens. Wenn in OSM-Id-Reihenfolge eingespielt, geographisch weitgehend zufällig. Führt dazu, dass der Index 1000 Fundstellen meldet, die über 20Gb verteilt sind. Heißt: 1000 Seeks. Heißt: warten.

Sortiert man die Nodes vor dem Einspielen um, liegen geographisch nahe Punkte mit hoher Wahrscheinlichkeit auch auf der Disk nahe beieinander, das erspart Seeks und der Zugriff kann signifikant schneller werden.

Natürlich geht diese Ordnung mit den Updates langsam verloren; das aber kann man mit einem Reorganisationslauf beheben.

Ich hab mit diesem Vorgehen in der Vor-SSD-Zeit eine lahme Anwendung in eine “jede Anfrage mit 2 Diskzugriffen beantwortet” umgebaut. Weil es da aber um eine “N×M” Zuordnung ging, also über jeweils eine von zwei Spalten gesucht wurde, die Tabelle verdoppelt, eine physisch nach N, eine phsisch nach M sortiert. Geht natürlich nur, wenn man ein Zeitfenster für die Reorganisationsläufe hat.

Just my 2.38¢.

Gruß Wolf

alle klar. Das Kind nennt sich bei PostgreSQL Cluster - nicht zu verwechseln mit einem Database Cluster.

Das Clustern von Tabellen ordnet die Daten passend zum Index an. Nimmt man dazu den Spatialen Index, so liegen räumlich nahe Daten auch auf der Platte eng beieinander. Das Clustern wurde automatisch beim Import der Rohdaten gemacht und sollte ab und zu mal neu angestoßen werden.Das Blöde ist nur: so ein Clustern lockt die Tabelle exklusiv, braucht ca den 3-fachen Plattenplatz der Tabelle und dauert ewig (Tage!) Während dieser Zeit ist die Anwendung down, wenn man nicht mit Kopien arbeitet - also noch mehr Platz braucht. Updates müssen dann auch warten.

Gruss
walter

Hm, ich hab das kürzlich eingeführt, läuft bei mir ca. 90 Minuten. Die Datenmenge ist hier geringer, habe einen Großteil von Europa drin, aber auch hochgerechnet auf einen Planet komme ich nicht auf Tage. Liegt aber auch auf einer SSD und Hauptspeicher ist reichlich vorhanden.

bye, Nop

Nahmd,

Ich nehme alle Vorschläge mit dem Ausdruck größten Bedauerns zurück und stelle hiermit fest: Du machst alles perfekt richtig und solltest keinesfalls irgendetwas ändern.

Gruß Wof

So, ich habe auf flatfile umgestellt ohne neu zu importieren (*).

Dadurch wurde aus einer 370GB großen planet_osm_nodes + 49 GB Index ein 20 GB großes Flatfile. Das liegt jetzt auf der gleichen Platte wie die WAL-Files und die ist damit zu bis zum Anschlag ausgelastet. SSD wird heute gekauft. 64 GB sollten dafür reichen. done.

vielen Dank an die Kollegen für die wertvollen Tips.

Gruss
walter

*) ich habe dazu einen osm2pgsql-Patch reaktiviert, der ein Tool dafür zur Verfügung stellt. siehe http://gis.19327.n5.nabble.com/template/NamlServlet.jtp?macro=user_nodes&user=339234 und ff.
Kurzfassung: Der Tool liest planet_osm_nodes und macht da ohne Umwege ein Flatfile raus. Laufzeit für über 2 Milliarden Datensätze ca 2 Stunden!
Flatfile aktivieren, planet_osm_nodes truncaten, vacuum full planet_osm_nodes, feddich.