Ich habe viele Indices per Tablespace auf SSD gelegt. Das kostet relativ wenig und bringt schon sehr viel. Mag aber sein, dass du alles auf SSD hast, dann bringt das natürlich nix.
Das lässt sich so nicht wirklich beantworten. Wie sieht deine DB/Schema aus? Was ist da alles an Daten drin (kompletter Planet mit 2 Jahren History,…?). Was hast du an Overpass Query probiert? Wie sieht deine Query aus? Wie sieht der Laufzeitunterschied aus?
Ich habe probiert, die Adressen eines ganzen Bundeslandes (BaWü) zu zählen und als CSV pro Gemeinde auszugeben. Das ging aber nicht (Ein anderes Beispiel für nur einen Kreis ging recht flott).
Nein, ich habe keine History und nutze ein eigenes Schema in PostgreSQL. Die erwähnte Auswertung dauert auf meinem Rechner knapp 5 Sekunden. Ich meine ja auch nicht, dass man das wirklich vergleichen kann.
Nicht falsch verstehen. Overpass ist eine tolle Sache. Ich will das nicht herabsetzen.
Interessehalber frage ich mich nur, was für eine Datenhaltung Overpass nutzt.
Roland
Verzichtet komplett auf Transaktionen. Das Ding ist read only ausgelegt, und wenn es auf die Nase fällt muss er die DB komplett neu aufbauen.
D.h. Mehr Performanz, weniger transaktionale Absicherung.
Ok “komplett” war evtl. ein bisschen extremistisch. Aber Aussage von Roland in Bonn war genau, das seine DB durch Verzicht auf den ganzen Sicherheitskram deutlich schlanker im Source ist und deutlich schneller. (Die geringen lines of Code - die ich nicht gemerkt habe - fande ich schon interessant).
Mit einem passenden Index findest Du blitzschnell heraus, wo auf der Disk Deine 10.000 Treffer liegen. Bei so 300 Seeks/s braucht das Ansteuern der Treffer alleine 30 Sekunden. Der eigentliche Datentransfer spielt praktisch keine Rolle.
Ich halte deshalb alle Daten gleich drei mal: einmal nach Koordinaten sortiert, einmal zuerst nach Key und dann nach Koordinaten, und einmal zuerst nach Value, dann nach Key und dann nach Koordinaten. Einfache Anfragen mit ein paar zigtausend Treffern werden damit instantan beantwortet, die knapp 500k Geschichtskarten-POIs werden in etwas über zwei Minuten aus ~200 Einzelanfragen zusammengesetzt. Je Anfrage (vom Index abgesehen) nur 1 Seek, und 200× Seek+(im Schnitt) 0.4Mb lesen ist schneller als 500.000× Seek+200Bytes lesen. Leider ist nichts umsonst: Die Monsterdateien zu sortieren ist so aufwendig, dass ich nur einmal am Tag aktualisieren kann. Für QS-Aktionen völlig ungeeignet. Ich definiere diesen Bug aber zu einem Feature: die DB hat immer einen vollkommen konsistenten Bearbeitungsstand
In Postgres kann man das nachbilden, indem man Untertabellen zu einer Tabelle anlegt und das Konstrukt mit Constraints/Rules so ausstattet, dass Objekte in die gleiche Untertabelle gepackt werden, die bei häufigen Anfragen auch zusammen im Ergebnis erscheinen. Z.B. die Welt mit 200 Rechtecken überdecken, wenn man oft regional sucht. Leider vertragen sich die Untertabellen nicht mit Foreign-Keys, sie können nur auf Einzeltabellen zeigen, aber nicht auf die Hierarchie als ganzes. Das soll geändert werden; das Feature ist angekündigt, war aber bei meinem letzten Blick in die Doku noch nicht implementiert.
hier mal die wichtigsten. Hab natürlich noch mehr, da man die ja nach Bedarf zusätzlich anlegen kann.
public | planet_osm_line_index | index | postgres | planet_osm_line | 23 GB |
public | planet_osm_line_pkey | index | postgres | planet_osm_line | 2904 MB |
public | planet_osm_line_tags_index | index | postgres | planet_osm_line | 23 GB |
public | planet_osm_nodes_pkey | index | postgres | planet_osm_nodes | 8192 bytes |
public | planet_osm_point_index | index | postgres | planet_osm_point | 3515 MB |
public | planet_osm_point_pkey | index | postgres | planet_osm_point | 2003 MB |
public | planet_osm_point_tags_index | index | postgres | planet_osm_point | 10 GB |
public | planet_osm_polygon_index | index | postgres | planet_osm_polygon | 14 GB |
public | planet_osm_polygon_pkey | index | postgres | planet_osm_polygon | 3565 MB |
public | planet_osm_polygon_tags_index | index | postgres | planet_osm_polygon | 24 GB |
public | planet_osm_rels_idx | index | postgres | planet_osm_rels | 14 MB |
public | planet_osm_rels_parts | index | postgres | planet_osm_rels | 3887 MB |
public | planet_osm_rels_pkey | index | postgres | planet_osm_rels | 150 MB |
public | planet_osm_roads_index | index | postgres | planet_osm_roads | 3641 MB |
public | planet_osm_roads_pkey | index | postgres | planet_osm_roads | 386 MB |
public | planet_osm_roads_tags_index | index | postgres | planet_osm_roads | 3989 MB |
public | planet_osm_ways_idx | index | postgres | planet_osm_ways | 2641 MB |
public | planet_osm_ways_nodes | index | postgres | planet_osm_ways | 136 GB |
public | planet_osm_ways_pkey | index | postgres | planet_osm_ways | 13 GB |
und selbst erzeugte:
public | idx_boundaries_type | index | postgres | boundaries | 6160 kB |
public | idx_boundaries_value | index | postgres | boundaries | 9432 kB |
public | idx_changesets_name | index | postgres | changesets | 353 MB |
public | idx_changesets_uid | index | postgres | changesets | 280 MB |
public | idx_countries2_id | index | postgres | countries2 | 261 MB |
public | idx_countries2_level | index | postgres | countries2 | 256 MB |
public | idx_countries2_ts | index | postgres | countries2 | 255 MB |
public | idx_destatis_ags | index | postgres | destatis | 360 kB |
public | idx_exifs_geom | index | postgres | exifs | 304 kB |
public | idx_line_highway | index | postgres | planet_osm_line | 6683 MB |
public | idx_line_natural | index | postgres | planet_osm_line | 3803 MB |
public | idx_line_postal_code | index | postgres | planet_osm_line | 26 MB |
public | idx_line_postcode | index | postgres | planet_osm_line | 11 MB |
public | idx_line_ref | index | postgres | planet_osm_line | 6818 MB |
public | idx_line_route | index | postgres | planet_osm_line | 7598 MB |
public | idx_point_hkts | index | postgres | planet_osm_point | 368 kB |
public | idx_point_place | index | postgres | planet_osm_point | 120 MB |
public | idx_point_postcode | index | postgres | planet_osm_point | 723 MB |
public | idx_point_traffic_sign | index | postgres | planet_osm_point | 8608 kB |
public | idx_polygon_admin_level | index | postgres | planet_osm_polygon | 3519 MB |
public | idx_polygon_boundary | index | postgres | planet_osm_polygon | 3528 MB |
public | idx_polygon_pcl | index | postgres | planet_osm_polygon | 5478 MB |
public | idx_polygon_pos | index | postgres | planet_osm_polygon | 11 GB |
public | idx_polygon_postal_code | index | postgres | planet_osm_polygon | 1952 kB |
public | idx_polygon_postcode | index | postgres | planet_osm_polygon | 374 MB |
public | idx_roads_ref | index | postgres | planet_osm_roads | 370 MB |
eine 250 GB SSD sollte erst mal ausreichen, aber ne 500-er ist ja auch nicht mehr so teuer. Real hab ich eine 120-er und eine 250-er, aber da sind noch einige andere Daten drauf. z.B. das Flat-File für osm2pgsql mit ca 25 GB.
Stimmt. Da macht es dann der Index alleine nicht. Deshalb habe ich für in Abfragen wiederkehrende Datensätze teils gespiegelte Subset-Tabellen. Nicht sonderlich schön, weil redundant, kann aber viel helfen.
Die Magie besteht darin, die POIs in allen Areas gleichzeitig zu suchen, die Geschwindigkeit wird also mit Speicherverbrauch (alle Grenzen gleichzeitig geladen) bezahlt. Dies in den OP einzubauen sollte möglich sein, und der Query-Optimizer vom PostGis sollte es auch hinbekommen.
Ich hätte noch die Admin-Boundaries der Welt im Angebot (als angereicherte POIs und als Übersicht), die brauchen etwa 20 Sekunden. Sucht aber nur den Mittelpunkt der jeweiligen Region im Land, kann also bei ungehörigen Grenzverläufen Fehler enthalten. Zu einer Version für Area in Area hab ich im Moment nicht die Zeit.
Alles jedoch nur einmal täglich aktualisiert, nachmittags mit Datenstand Mitternacht, damit für QS völlig ungeeignet. Aber vielleicht brauchbar für Fortschrittsstatistiken, weil der Datenstand genau definiert ist.
Mein Raid-0 ist zur Zeit “degraded”, läuft also nur mit 2 anstelle von 3 Drives. Deswegen ist die Performance auch grottenschlecht.
Fühl mich garnicht wohl dabei, aber eine Dasi von 670 GB hab ich noch nicht hinbekommen und vorher will ich da nicht rumschrauben.
Bin gerade dabei, die DB mit Slony auf einem anderen Server zu spiegeln, damit ich den aktuellen Server neu aufsetzen kann und der Laden dennoch weiterläuft. Und wenn das nicht funzt, mach ich halt ne Woche dicht.