Einsatz von SSD für die Datenbank

In meiner Firma betreiben wir seit mehreren Jahren die Hauptdatenbank mit SSDs. Allerdings ist der zu erwartende Geschwindigkeitsgewinn davon abhängig, wie schlecht Indices im Speicher gehalten werden können. In der Regel hilft eine Erhöhung des Hauptspeichers mehr als eine Umstellung auf SSDs. Sobald die Indices alle in den Hauptspeicher passen, ist die Geschwindigkeit der Platten (außer bei fast ausschließlich Updates und Inserts) nicht mehr stark der Flaschenhals. Deshalb würde ich eher darauf achten, dass die Indices alle in den Hauptspeicher passen als dass sie auf einer SSD liegen. Unsere Indices sind z.B. 47 GB groß, die Datenbank hat 72GB Hauptspeicher. I/O ist für uns kein Thema. Darf ich fragen, wie groß Deine Indices insgesamt auf der Festplatte sind und wie viel Speicher Du hast? Nutzt Du ein 64bit Betriebssystem?

Um den zweiten Teil der Frage zu beantworten: Wir nutzen diese Platten hier: Intel® SSD 710 Series 300GB, 2.5in SATA Ausfälle gab es noch nie, wir haben davon 8 Stück als raid 1+0 im Dauerbetrieb und die Datenbank langweilt sich definitiv nicht ;o)

Nadjita wrote:

In meiner Firma betreiben wir seit mehreren Jahren die Hauptdatenbank mit SSDs. Allerdings ist der zu erwartende Geschwindigkeitsgewinn davon abhängig, wie schlecht Indices im Speicher gehalten werden können. In der Regel hilft eine Erhöhung des Hauptspeichers mehr als eine Umstellung auf SSDs.

Klar, aber bei 32GB ist bei "normalen" Motherboards halt Schluß - und die hat meiner.

Sobald die Indices alle in den Hauptspeicher passen, ist die Geschwindigkeit der Platten (außer bei fast ausschließlich Updates und Inserts) nicht mehr stark der Flaschenhals. Deshalb würde ich eher darauf achten, dass die Indices alle in den Hauptspeicher passen als dass sie auf einer SSD liegen. Unsere Indices sind z.B. 47 GB groß, die Datenbank hat 72GB Hauptspeicher.
I/O ist für uns kein Thema. Darf ich fragen, wie groß Deine Indices insgesamt auf der Festplatte sind und wie viel Speicher Du hast? Nutzt Du ein 64bit Betriebssystem?

Server: 8-Core AMD, 32GB Memory, 12 TB Disk, Ubuntu 13.10, 64 Bit.

Die Planet-DB ist "etwas" größer:

planet2#␣\d+
List␣of␣relations
Schema␣|␣␣␣␣␣␣␣␣Name␣␣␣␣␣␣␣␣|␣Type␣␣|␣␣Owner␣␣␣|␣␣Size␣␣␣|␣Description
--------+--------------------+-------+----------+---------+-------------
...
public␣|␣planet_osm_line␣␣␣␣|␣table␣|␣postgres␣|␣45␣GB␣␣␣|
public␣|␣planet_osm_nodes␣␣␣|␣table␣|␣postgres␣|␣374␣GB␣␣|
public␣|␣planet_osm_point␣␣␣|␣table␣|␣postgres␣|␣13␣GB␣␣␣|
public␣|␣planet_osm_polygon␣|␣table␣|␣postgres␣|␣57␣GB␣␣␣|
public␣|␣planet_osm_rels␣␣␣␣|␣table␣|␣postgres␣|␣1369␣MB␣|
public␣|␣planet_osm_roads␣␣␣|␣table␣|␣postgres␣|␣7188␣MB␣|
public␣|␣planet_osm_ways␣␣␣␣|␣table␣|␣postgres␣|␣111␣GB␣␣|
...

und die Indices sind auch noch da:

public␣|␣planet_osm_line_index␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_line␣␣␣␣|␣5070␣MB␣|
public␣|␣planet_osm_line_pkey␣␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_line␣␣␣␣|␣1842␣MB␣|
public␣|␣planet_osm_line_tags_index␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_line␣␣␣␣|␣13␣GB␣␣␣|
public␣|␣planet_osm_nodes_pkey␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_nodes␣␣␣|␣43␣GB␣␣␣|
public␣|␣planet_osm_point_index␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_point␣␣␣|␣2717␣MB␣|
public␣|␣planet_osm_point_pkey␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_point␣␣␣|␣1047␣MB␣|
public␣|␣planet_osm_point_tags_index␣␣␣|␣index␣|␣postgres␣|␣planet_osm_point␣␣␣|␣8223␣MB␣|
public␣|␣planet_osm_polygon_index␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_polygon␣|␣6937␣MB␣|
public␣|␣planet_osm_polygon_pkey␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_polygon␣|␣2464␣MB␣|
public␣|␣planet_osm_polygon_tags_index␣|␣index␣|␣postgres␣|␣planet_osm_polygon␣|␣13␣GB␣␣␣|
public␣|␣planet_osm_rels_idx␣␣␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_rels␣␣␣␣|␣264␣kB␣␣|
public␣|␣planet_osm_rels_parts␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_rels␣␣␣␣|␣946␣MB␣␣|
public␣|␣planet_osm_rels_pkey␣␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_rels␣␣␣␣|␣47␣MB␣␣␣|
public␣|␣planet_osm_roads_index␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_roads␣␣␣|␣463␣MB␣␣|
public␣|␣planet_osm_roads_pkey␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_roads␣␣␣|␣166␣MB␣␣|
public␣|␣planet_osm_roads_tags_index␣␣␣|␣index␣|␣postgres␣|␣planet_osm_roads␣␣␣|␣1521␣MB␣|
public␣|␣planet_osm_ways_idx␣␣␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_ways␣␣␣␣|␣2641␣MB␣|
public␣|␣planet_osm_ways_nodes␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_ways␣␣␣␣|␣94␣GB␣␣␣|
public␣|␣planet_osm_ways_pkey␣␣␣␣␣␣␣␣␣␣|␣index␣|␣postgres␣|␣planet_osm_ways␣␣␣␣|␣9345␣MB␣|

Um den zweiten Teil der Frage zu beantworten: Wir nutzen diese Platten hier: Intel® SSD 710 Series 300GB, 2.5in SATA Ausfälle gab es noch nie, wir haben davon 8 Stück als raid 1+0 im Dauerbetrieb und die Datenbank langweilt sich definitiv nicht ;o)

Uii, das sprengt wohl meinen Kostenrahmen - aber für ne Firma sind das wohl nur Peanuts.

Ich werde mir wohl nach und nach 1-3 SSD zulegen und die wichtigsten Indices (*) drauflegen aber ich hab immer noch ein ungutes Gefühl bezüglich der Robustheit.

Danke für deine Antwort
Gruss
walter

• ) wie krieg ich die raus? Muß mich wohl mal mit Postgresql-Monitoring beschäftigen.
  

@all: die UK-Toolchain in England macht ja das gleiche: Diff-Files mit osm2pgsql in die Postgresql-DB einspielen. Bei denen flutscht das prima. Kann man sich die Configs bei denen ansehen? Klar, die verwendete Software ist im Download, aber was ist mit den Live-Configs?
Ich taste mich mal ran.

Ich verwende seit zwei Jahren SSDs als Systemplatte. Die Stabilität ist so eine Sache: Es kommt gelegentlich vor, dass alle paar Stunden ein Bluescreen auftaucht. Nach ein paar Tagen ist der Spuk dann wieder vorbei. Bei der ersten SSD trat es dann aber so häufig auf, dass ich sie (auch wegen der Größe) ausgemustert habe. Bei einer dritten (auf einem anderen Rechner) war bisher Ruhe. Auf dieser Grundlage möchte ich aber keine Aussage über heutige SSDs machen. In der IT ist ein Jahr fast eine Ewigkeit.
Bei SSD ist das Schreiben ganz klar der kritischere Vorgang. Trotz der Verteilung der Schreibvorgänge (wear leveling) ist irgendwann Feierabend, wenn das zu häufig passiert. Es kommt aber darauf an, wieviel geschrieben wird. Der Index-File einer DB wird ja nicht jedesmal komplett neu geschrieben, oft werden ja nur ein paar Hash-Werte hinzugefügt. Ich habe mich jedenfalls getraut, den swapfile auch auf die SSD zu legen, da es Untersuchungen gibt, dass auf den gar nicht so viel geschrieben wird, wie man eigentlich annimmt.
Bis die SSD dann den Geist aufgibt, gibt es vielleicht so viel schnellere, günstigere und größere Exemplare, dass ich froh bin, einen unwiderlegbaren Grund zum Wechsel zu haben 😉
Zur Aussage von Nadjita: Natürlich ist RAM schneller (und unempfindlicher) als eine SSD. Jede DB-SW wird daher einen Teil des Index im RAM als Cache zu halten versuchen. Je mehr RAM, desto besser. Wenn der Index zu groß für RAM ist, kommt es auf das Verhältnis Lesen/Schreiben an. Die SSD kann ihre Vorteile (gegenüber HD) vor allem beim Lesen ausspielen.

seichter wrote:

Bei einer dritten (auf einem anderen Rechner) war bisher Ruhe. Auf dieser Grundlage möchte ich aber keine Aussage über heutige SSDs machen. In der IT ist ein Jahr fast eine Ewigkeit.

Danke für die Info.

Kannst du mir sagen, welchen Hersteller du bevorzugst und welchen Typ du einsetzt?

Der Index-File einer DB wird ja nicht jedesmal komplett neu geschrieben, oft werden ja nur ein paar Hash-Werte hinzugefügt.

Derzeit laß ich autovacuum ziemlich oft über die Tabellen und auch Indices huschen. vacuum analyze ist ja nur lesend und vacuum alleine schreibt ja auch die Indices nicht neu - glaub ich zumindest. Nur ein Rebuild oder vacuum full schreibt das Zeug neu.
Ich glaub, ich werde das mal riskieren. Datenfiles liegen auf einem Raid und wenn ein Index abfackelt, kann man den jederzeit neu aufsetzen - wenn man es kann 😉

Gruss
walter

Moinsen,
ich nutze seit ca. 1 Jahr eine 500GB SSD von Samsung in meinem Laptop.
Bisher ohne Probleme.

Grüße
Chris

wambacher wrote:

Kannst du mir sagen, welchen Hersteller du bevorzugst und welchen Typ du einsetzt?

querbeet: OCZ Agility 60GB, Samsung 830 128, die dritte kann ich im Moment nicht nachsehen

wambacher wrote:

Frage: Hat jemand praktische Erfahrungen mit SSD im Datenbankbereich?

Ja, nutze auf meinem Kartenserver seit einigen Jahren nur noch SSDs für die Datenbank.

Geschwindigkeitsgewinn bei Datenbankoperationen ca. Faktor 5, wenn Du flatfile benutzt könnte es auch mehr sein, das gabs bei meiner Vergleichsmessung damals noch nicht.

Probleme gab es bisher keine. DB wird alle 3 Tage komplett importiert und Server ist permanent am Rendern. Wichtig ist daß Dein Kernel eine saubere TRIM-Unterstützung hat, muß evtl. dafür neu gebaut werden.

Evtl. fährst Du günstiger wenn Du 2 kleinere SSDs nimmst und zu einem virtuellen Laufwerk zusammenlegst, so ist mein Server konfiguriert.

bye, Nop

Ich würde bei einer DB (zumindest Oracle) erwarten, daß es performancemäßig am meisten bringt, die Redo-Logs auf SSD zu verlagern, erst nachrangig die Daten oder die Indizes. Keine Ahnung wie das bei pgsql heißt.

Gruß,
Zecke

Ich nutze aktuell eine Samsung 840 für die Generierung der RadReiseKarte. Bei den Größen deiner DB freue ich mich richtig, dass ich mit einem komprimierten planet arbeiten kann...

wambacher wrote:

Kannst du mir sagen, welchen Hersteller du bevorzugst und welchen Typ du einsetzt?

Auf keinen Fall OCZ. Bevor man sich diesen technischen Sondermüll antut, empfiehlt sich ein Blick ins OCZ-Forum

Nachdem die letzte RMA-Platte 10 Tage ausgehalten hat, bevor sie den Dienst quittiert hat, freue ich mich schon auf neue Abenteuer..

Nop wrote:

wenn Du flatfile benutzt könnte es auch mehr sein, das gabs bei meiner Vergleichsmessung damals noch nicht.

Muß ich mir mal ansehen. Dachte, flatfile wäre irgend so ein altes Zeug. Ich importiere im Slim-Modus; damit und nur damit kann man Diffs einspielen. Und das ist wohl sowas ähnliches? Mal sehen.

Wichtig ist daß Dein Kernel eine saubere TRIM-Unterstützung hat, muß evtl. dafür neu gebaut werden.

TRIM im Kernel? Das sagt mir absolut nichts. Ansonsten fahr ich das allerneueste 13.10 Ubuntu mit 3.11.0-Kernel. Da sollte doch wohl alles sauber sein.

Evtl. fährst Du günstiger wenn Du 2 kleinere SSDs nimmst und zu einem virtuellen Laufwerk zusammenlegst, so ist mein Server konfiguriert.

Wenn - und das ist schon fast sicher - fange ich mit einer SSD an und schau mal was das bringt. Striping kommt, wenn ich noch eine 2. SSD von euch geschenkt bekomme kaufe 😉

Gruss
walter

Zecke wrote:

Ich würde bei einer DB (zumindest Oracle) erwarten, daß es performancemäßig am meisten bringt, die Redo-Logs auf SSD zu verlagern, erst nachrangig die Daten oder die Indizes. Keine Ahnung wie das bei pgsql heißt.

Danke für den Tip.
Die Redo-Logs nennen sich bei Postgresql WAL (Write Ahead Logging), liegen in der Directory pg_xlog und das man man natürlich überall hinlegen. (*)
Kommt auf jeden Fall ganz oben auf meine Liste.

Gruss
walter

• ) Am besten in eine RAM-Disk, schneller geht es nimmer 😉 😉 😉
  

wambacher wrote:

[…]
Ich werde mir wohl nach und nach 1-3 SSD zulegen und die wichtigsten Indices (*) drauflegen aber ich hab immer noch ein ungutes Gefühl bezüglich der Robustheit.
[…]

• ) wie krieg ich die raus? Muß mich wohl mal mit Postgresql-Monitoring beschäftigen.
  

[…]

Sieh dir die Dokumentation unter http://www.postgresql.org/docs/9.3/stat … stats.html insbesondere zu pg_stat_user_indexes und pg_statio_user_indexes an. Damit solltest du dir ein Bild von der Wichtigkeit der Indizes für deine Abfragen machen können.

wambacher wrote:

Nop wrote:

wenn Du flatfile benutzt könnte es auch mehr sein, das gabs bei meiner Vergleichsmessung damals noch nicht.

Muß ich mir mal ansehen. Dachte, flatfile wäre irgend so ein altes Zeug. Ich importiere im Slim-Modus; damit und nur damit kann man Diffs einspielen. Und das ist wohl sowas ähnliches? Mal sehen.

Wichtig ist daß Dein Kernel eine saubere TRIM-Unterstützung hat, muß evtl. dafür neu gebaut werden.

TRIM im Kernel? Das sagt mir absolut nichts. Ansonsten fahr ich das allerneueste 13.10 Ubuntu mit 3.11.0-Kernel. Da sollte doch wohl alles sauber sein.

Flatfile ist ein neueres Feature und cached die Koordinaten der Nodes in einer simplen Datei anstatt in der Datenbank. Viel weniger Platzverbrauch, viel schnellerer Zugriff und auf einer SSD rockt das. :-)

Zu Ubuntu kann ich nix sagen, aber ich habe hier ein frisch installiertes aktuelles Debian Wheezy und da mußte man den Kernel für besseres TRIM nochmal bauen.

bye, Nop

Nop wrote:

Flatfile ist ein neueres Feature und cached die Koordinaten der Nodes in einer simplen Datei anstatt in der Datenbank. Viel weniger Platzverbrauch, viel schnellerer Zugriff und auf einer SSD rockt das. :-)

Interessant. wie ich sehe, hat kai krueger die finger drin. werd ich mal checken.

Zu Ubuntu kann ich nix sagen, aber ich habe hier ein frisch installiertes aktuelles Debian Wheezy und da mußte man den Kernel für besseres TRIM nochmal bauen.

So, bin endlich von einer Baustelle zurück, auf der ich mich verlaufen hatte: Trim ist für mich Zeichenkettenverarbeitung gewesen und das passte irgendwie nicht in den Kernel 😉
TRIM für SSD sollte zumindest in meinem Ubuntu (13.10) drin sein. Zumindest weiss ich jetzt, dass ich darauf achten muß.

Gruss
walter

ps: wenn jetzt die letzte Tabelle (planet_osm_nodes mit 370 GB!) in einen anderen Tablespace verschoben wurde, schau ich mal ob es etwas schneller wird. Ab jetzt sind zumindest Indices und Daten physikalisch getrennt. Danach lege ich pg_xlog (Wal) mal auf ein eigenes Laufwerk, das hier ungenutzt in der Ecke liegt.

wambacher wrote:

Die Redo-Logs nennen sich bei Postgresql WAL (Write Ahead Logging), liegen in der Directory pg_xlog und das man man natürlich überall hinlegen. (*)
Kommt auf jeden Fall ganz oben auf meine Liste.

• ) Am besten in eine RAM-Disk, schneller geht es nimmer ;) ;) ;)
  

Neee, RAM-Disk ist nicht die beste Lösung. Datenbanken sind Weltmeister darin, Speicher sinnvoll zu nutzen, alles was Du als RAM-Disk abzweigst fehlt zum Cachen. Abgesehn davon, dass bei vielen Updates und Inserts die WAL-Logs extrem groß werden. Diese auf eine SSD zu packen ist aber durchaus sinnvoll, da dort die meiste Aktivität ist. Wenn nicht auf eine SSD, dann zumindest auf ein separates Laufwerk. Wenn ich allerdings sehe, dass planet_osm_nodes_pkey 43GB groß ist (und planet_osm_ways_nodes noch größer), dann ist das durchaus ein Problem bei "nur" 32GB Arbeitsspeicher. Wenn der Index nicht in den Speicher passt, wird es einfach langsam, da die Datenbank dann swappen muss. Wenn es meine Datenbank wäre, würde ich den Index in Teile zerlegen die in den Speicher passen (z.B. in einen Index mit nodes id < 5000000000, einenen >= 5000000000 und < 10000000000, usw.). Wenn Du Dich allerdings mit Datenbanken im Allgemeinen und PostgreSQL im Speziellen nicht auskennst, fällt das vermutlich flach. Ich vermute mal, dass Du mit einer einzigen SSD von 128GB und einem Swapfile auf der Platte schon einen deutlichen Geschwindigkeitsgewinn bekommst. Hätte ich die Hardware hier, würde ich entsprechend schauen, ob ich deine Konfiguration nachbauen und optimieren kann, aber momentan fliegt das nicht hier herum ;o)

TRIM ist überhaupt kein Problem in neueren Ubuntu-Kerneln und die Anzahl der Schreiboperationen sollte man auch nicht überschätzen. Faktisch ist eine SSD schneller zu klein für ihren Einsatzzweck als dass sie den Geist aufgibt. Natürlich kann es Dir immer wieder passieren, dass sie sich nach einem Jahr verabschiedet wenn Du keine SSD wie die von mir gepostete nimmst, aber der Preisunterschied rechtfertigt es im privaten Umfeld einfach nicht. So lange keine sensiblen Daten darauf liegen, ist doppelt kaufen billiger als extrem teuer kaufen ;o)

wambacher wrote:

TRIM im Kernel? Das sagt mir absolut nichts. Ansonsten fahr ich das allerneueste 13.10 Ubuntu mit 3.11.0-Kernel. Da sollte doch wohl alles sauber sein.

TRIM (Mitteilung, dass Blöcke nicht mehr gebraucht werden) ist in Linux seit 2010 (2.6.33) dabei, bei MS seit W7. Es kann aber sein, dass es neuere Varianten (-> Nop) gibt.

Nadjita wrote:

wambacher wrote:

Die Redo-Logs nennen sich bei Postgresql WAL (Write Ahead Logging), liegen in der Directory pg_xlog und das man man natürlich überall hinlegen. (*)
Kommt auf jeden Fall ganz oben auf meine Liste.

• ) Am besten in eine RAM-Disk, schneller geht es nimmer 😉 😉 😉
  

Neee, RAM-Disk ist nicht die beste Lösung. Datenbanken sind Weltmeister darin, Speicher sinnvoll zu nutzen, alles was Du als RAM-Disk abzweigst fehlt zum Cachen.

Voll drauf reingefallen - und dabei hab ich extra drei Smilies drangehängt. 😉 Wer die WAL-Files (bei mir übrigens 1.1 GB) in den Speicher legt, der gehört erschossen. Die werden doch genau dann gebraucht, wenn der Rechner abgestürzt ist - und dann ist der Memory natürlich auch weg. War wirklich nur ein Scherz.

Wenn der Index nicht in den Speicher passt, wird es einfach langsam, da die Datenbank dann swappen muss. Wenn es meine Datenbank wäre, würde ich den Index in Teile zerlegen die in den Speicher passen (z.B. in einen Index mit nodes id < 5000000000, einenen >= 5000000000 und < 10000000000, usw.). Wenn Du Dich allerdings mit Datenbanken im Allgemeinen und PostgreSQL im Speziellen nicht auskennst, fällt das vermutlich flach.

Nönö, das trau ich mir durchaus zu. Aber mit dem Aspekt hab ich mich noch nicht beschäftigt.

Ich vermute mal, dass Du mit einer einzigen SSD von 128GB und einem Swapfile auf der Platte schon einen deutlichen Geschwindigkeitsgewinn bekommst.

Meinst du wirklich, den Memory per Swap aufzubohren und den Postgresql zum Fressen geben? Also postgresql.conf noch mehr aufzubohren als es der reale Memory erlaubt? Fragt sich dann, was schneller ist: Daten von der Platte lesen oder Index swappen? Mal sehen, Swapfiles sind ja schnell erstellt und Plattenplatzt hab ich eh genug.

Hätte ich die Hardware hier, würde ich entsprechend schauen, ob ich deine Konfiguration nachbauen und optimieren kann, aber momentan fliegt das nicht hier herum ;o)

Ich schau mal im Keller nach, ob ich noch so eine Kiste zusammenbauen kann 😉 Nee, Danke für dein "Angebot" aber soooo kritisch ist die Sache ja auch nicht.

Natürlich kann es Dir immer wieder passieren, dass sie sich nach einem Jahr verabschiedet wenn Du keine SSD wie die von mir gepostete nimmst, aber der Preisunterschied rechtfertigt es im privaten Umfeld einfach nicht. So lange keine sensiblen Daten darauf liegen, ist doppelt kaufen billiger als extrem teuer kaufen ;o)

"Deine" Samsung 830 mit 128 GB gibt es für ca 125€, das sollte im November wohl drin sein. Aber ich mach mich gerade erst so richtig schlau über das Thema. Will halt keinen Schrott vom Discounter-Krabbeltisch kaufen.

Gruss
walter

wambacher wrote:

Voll drauf reingefallen - und dabei hab ich extra drei Smilies drangehängt.

Mift 🤔 Aber wenn Du wüsstest, was ich schon alles gesehen habe... da sind WAL-Files in RAM-Disk noch harmlos...

wambacher wrote:

Die Planet-DB ist "etwas" größer:

planet2# \d+
List of relations
Schema | Name | Type | Owner | Size | Description

---

+--------------------+-------+----------+---------+-------------

...
public | planet_osm_line | table | postgres | 45 GB |
public | planet_osm_nodes | table | postgres | 374 GB |
public | planet_osm_point | table | postgres | 13 GB |
public | planet_osm_polygon | table | postgres | 57 GB |
public | planet_osm_rels | table | postgres | 1369 MB |
public | planet_osm_roads | table | postgres | 7188 MB |
public | planet_osm_ways | table | postgres | 111 GB |
...

Kleine Kollateral-Erkenntnis: Man muss im Moment noch kein schlechtes Gewissen haben, wenn man eine neue Relation erstellt.

Wie Nop bereits geschrieben hatte, ist flate-nodes ein feature up die Node Daten in einer simplen Datei zu speichern. Postgresql ist eine generelle Datenbank, die mehr oder weniger beliebige Daten speichern kann. Das mach Postgres auch eigentlich sehr gut. Allerdings diese flexibilitaet kostet ein wenig overhead. Bei 2 Milliarden Nodes die inzwischen im Planet sind, addieren sich auch ein paar bytes overhead pro Node schnell zu sehr grossen Mengen.

Zum Glueck braucht man die volle Flexibilitaet von Postgres nicht fuer die das Speichern der Nodes fuer den slim mode. Fuer diesen speziellen Zweck weiss man das man lediglich die Koordinaten benoetigt (4 bytes latitude, 4 bytes longitude) und das man sie nur per node_id suchen muss. Weiterhin weis man das die gueltigen node_ids dicht gepackt sind.

Desshalb kann man relativ leicht eine "Spezial Datenbank" erstellen, was das "flat-nodes" feature ist. Es schreibt schlicht 8 byte (lat/lon) linear fuer alle IDs in eine grosse Datei. Da derzeit die hoechste Node_id bei ca 2506700000 liegt, ergibt das eine Datei groesse von ~19 GB. Die postgresql Datenbank dafuer benoetigt hingegen ueber 100GB wenn ich mich nicht taeusche.

Ausserdem benoetigt "flat-nodes" keine Indexe, da die position in der Datei eindeutig durch die node_id gegeben ist. Somit sind lookups O(1). Desweiteren durch die geringere Groesse der Datei, passt mehr davon in den ram cache, wodurch die Geschwindigkeit weiter steigen koennte.

Insofern wuerde ich jedem der einen vollen Planet importiert die Option flatnodes empfehlen!

wambacher wrote:

Frage: Hat jemand praktische Erfahrungen mit SSD im Datenbankbereich?

Der haupt Kachelserver von Openstreetmap verwendet eine SSD um die osm2pgsql Datenbank zu speichern und hat damit sehr gute Erfahrung gemacht.

Seit May 2011 werkelt dort eine einzelne Intel 320 SSD mit 600 GB Groesse und enthaelt die komplette osm2pgsql Datenbank. Der Umstieg von 4 velociraptor in Raid 10 auf eine einzelne SSD hat einen riesigen Unterschied gemacht und die I/O Performance ist nun kaum noch ein Problem. Der Server faellt eigentlich nie mit den Updates zurueck und kann in fast allen Faellen die 16 cores fuers rendering voll auslasten (so fern genuegend Anfragen da sind).

Moderne SSDs wie die Samsung 840 pro, sind noch einmal deutlich schneller als die inzwischen "recht alte" Intel 320, und werden seit kurzem im zweiten rendering Server von osm.org eingesetzt, so wie fuer die Nominatim Datenbank.

"Write endurance" scheint fuer normale SSDs mit einer osm2pgsql Datenbank kein Problem zu sein. Die SSD in Yevaud laeuft nun sein zwei einhalb Jahren im Dauerbetrieb mit minutely diffs und hat bislang erst 5% der Schreibzyklen aufgebraucht. Das heist die SSD kann noch die 20 fache Menge an Schreibzyklen verkraften, bzw bei gleichbleibender Belastung wuerde sie theoretisch noch 40 - 50 Jahre halten!

Natuerlich verhaelt sich jede SSD ein wenig anders, und die relative ueberdimensionierung von 600GB und die Tatsache das es eine aeltere SSD mit 25nm Struktur ist duerfte dabei helfen. Andererseits erwartet wohl auch keiner das eine SSD tatsaechlich 50 Jahre hallten soll.

Insfoern sind mir bislang noch keine Indikatoren bekannt die darauf hinweisen das eine osm2pgsql Datenbank mit diffs SSDs schneller verschleisst als die typische Lebensdauer von herkoemlichen Festplatten von ca 5 Jahren.

Moeglicherweise wuerden sogar verhaeltnissmaessig "Schreibempfindliche" (und billige) SSDs wie die TLC SSD von Samsung (840 / 840 Evo), wenn man nicht gerade nur eine 64GB SSD verwendet, ausreichend durchhalten.

free_as_a_bird wrote:

Auf keinen Fall OCZ. Bevor man sich diesen technischen Sondermüll antut, empfiehlt sich ein Blick ins OCZ-Forum

full ack... OCZ kann ich auch auf keinen Fall empfehlen. Ich hatte anfangs auch OCZ bei uns in der Firma eingesetzt und bei einigen PCs mächtig Probleme. Seit ich aber Intel-SSDs (und vereinzelt Corsair Force Series GS) einsetze, läuft alles absolut rund.
Auch Privat nutze ich nur noch Intels und Corsair-SSDs - auch hier: nie irgendwelche Probleme.

Vielleicht OT: Aber hast du für dich schon die Frage geklärt, ob du wirklich die gesamte Erde benötigst und nicht nur mit Europa auskommst?

Gruß Klaus

amm wrote:

Seit May 2011 werkelt dort eine einzelne Intel 320 SSD mit 600 GB Groesse und enthaelt die komplette osm2pgsql Datenbank. Der Umstieg von 4 velociraptor in Raid 10 auf eine einzelne SSD hat einen riesigen Unterschied gemacht und die I/O Performance ist nun kaum noch ein Problem.

Auf die Ausfallsicherheit des RAID10 ggü. einer Einzelplatte wurde bewußt verzichtet? Gilt eine SSD heute als soviel zuverlässiger als eine klassische Festplatte? Wenn ich hier die Berichte über einzelne Marken lese, beginne ich zu zweifeln.

Gruß,
Zecke

Zecke wrote:

amm wrote:

Seit May 2011 werkelt dort eine einzelne Intel 320 SSD mit 600 GB Groesse und enthaelt die komplette osm2pgsql Datenbank. Der Umstieg von 4 velociraptor in Raid 10 auf eine einzelne SSD hat einen riesigen Unterschied gemacht und die I/O Performance ist nun kaum noch ein Problem.

Auf die Ausfallsicherheit des RAID10 ggü. einer Einzelplatte wurde bewußt verzichtet? Gilt eine SSD heute als soviel zuverlässiger als eine klassische Festplatte? Wenn ich hier die Berichte über einzelne Marken lese, beginne ich zu zweifeln.

Gruß,
Zecke

Die Ausfallsicherheit ist durch den 2. Server gegeben. Da es in diesem Fall ja auch nicht so ist, das die Daten irgendwie wertvoll wären (sprich: geht die SSD kaputt wird sie ersetzt und die Daten neu importiert), spricht nicht wirklich was gegen die Lösung.

Generell sollte man auch nicht ausser acht lassen, dass SSDs sich in den letzten 3 Jahren von exotisch zu völlig mainstream entsickelt haben (und parallel dazu ist die Technik auch gereift), sprich dass Erfahrungen die ein paar Jahre alt sind, kaum mehr wirklich relevant sind.

Simon

Zecke wrote:

amm wrote:

Seit May 2011 werkelt dort eine einzelne Intel 320 SSD mit 600 GB Groesse und enthaelt die komplette osm2pgsql Datenbank. Der Umstieg von 4 velociraptor in Raid 10 auf eine einzelne SSD hat einen riesigen Unterschied gemacht und die I/O Performance ist nun kaum noch ein Problem.

Auf die Ausfallsicherheit des RAID10 ggü. einer Einzelplatte wurde bewußt verzichtet? Gilt eine SSD heute als soviel zuverlässiger als eine klassische Festplatte? Wenn ich hier die Berichte über einzelne Marken lese, beginne ich zu zweifeln.

Gruß,
Zecke

Das ist realativ!

Habe gestern erst wieder eine ausgebaut - Beim runterfahren alle OK beim Hochfahren nur noch 2MB 😬 .

Leider gibt es bei den SSD nicht sowas wie s.w.i.f.t das Problem vorher ankündigen könnte - wenn der interne Controller ausfällt ist halt nur noch der Zwischenspeicher da fertig ....

amm wrote:

Die postgresql Datenbank dafuer benoetigt hingegen ueber 100GB wenn ich mich nicht taeusche.

bei mir derzeit sogar ~370 GB. Ich hab allerdings mit --hstore-all importiert und das scheint mit ein Grund für die Probleme zu sein.

Insofern wuerde ich jedem, der einen vollen Planet importiert, die Option flatnodes empfehlen!

Yes, Sir 😉

Gruss
walter

amm wrote:

"Write endurance" scheint fuer normale SSDs mit einer osm2pgsql Datenbank kein Problem zu sein. Die SSD in Yevaud laeuft nun sein zwei einhalb Jahren im Dauerbetrieb mit minutely diffs und hat bislang erst 5% der Schreibzyklen aufgebraucht. Das heist die SSD kann noch die 20 fache Menge an Schreibzyklen verkraften, bzw bei gleichbleibender Belastung wuerde sie theoretisch noch 40 - 50 Jahre halten!

DAS hört sich ja gut an! Also befinde ich mich auf dem richtigen Weg. Genau die "Write Endurance" machte mir ja am meisten Sorgen.

Moeglicherweise wuerden sogar verhaeltnissmaessig "Schreibempfindliche" (und billige) SSDs wie die TLC SSD von Samsung (840 / 840 Evo), wenn man nicht gerade nur eine 64GB SSD verwendet, ausreichend durchhalten.

Nach dem, was ich mir inzwischen angelesen habe, kommt TLC für mich nicht in Frage. MLC ist akzeptabel und SLC auch nicht mehr so teuer.

Gruss
walter

ps: wäre es eventuell möglich, mir die postgresql.conf von Yevaud zukommen zu lassen (wenn die nicht schon irgendwo online steht)? Ein paar Kniffe sollten da schon zu sehen sein.

toc-rox wrote:

Vielleicht OT: Aber hast du für dich schon die Frage geklärt, ob du wirklich die gesamte Erde benötigst und nicht nur mit Europa auskommst?

ja, ich möchte den ganzen Planeten. Nur damit kann ich verlässliche Auswertungen/Anwendungen machen, die nicht an ihre Grenzen stoßen.

Zudem werden die Daten im Laufe der Zeit immer "diffuser", da die Diffs immer alle neuen und geänderten Daten des gesamten Planeten einbringen. Wenn man z.B. eine Hessen-DB aufbaut und dann mit Diffs loslegt, hat man 5 Minuten später alle Änderungen in Papua-Neuguinea drin, falls man nicht weitere Klimmzüge macht. Damit hab ich mich 2010/2011 rumgeschlagen und das will ich mir nicht mehr antun.

Den Planeten hatte ich ja auch schon seit Monaten aktiv, nur im osmosis/Snapshot-Schema und nicht im osm2pgsql-Schema.
Platz ist absolut kein Problem und es klemmt eigentlich nur beim Update per Diff-Files. "Früher" brauchte er etwa 20 Minuten für 1 Stunde Daten und jetzt braucht er dafür 2 Stunden - d.h. die DB hinkt immer mehr hinterher.

Entweder krieg ich das - mit eurer Hilfe - hin oder ist stelle wieder auf osmosis/snapshot um. Allerdings muß ich mich dann selber wieder um Sachen kümmern, die osm2pgsql "on the Fly" erledigt, wie den Umgang mit Multipoint-Relationen mit "Löchern".

Gruss
walter

Zecke wrote:

amm wrote:

Seit May 2011 werkelt dort eine einzelne Intel 320 SSD mit 600 GB Groesse und enthaelt die komplette osm2pgsql Datenbank. Der Umstieg von 4 velociraptor in Raid 10 auf eine einzelne SSD hat einen riesigen Unterschied gemacht und die I/O Performance ist nun kaum noch ein Problem.

Auf die Ausfallsicherheit des RAID10 ggü. einer Einzelplatte wurde bewußt verzichtet? Gilt eine SSD heute als soviel zuverlässiger als eine klassische Festplatte? Wenn ich hier die Berichte über einzelne Marken lese, beginne ich zu zweifeln.

Ich hätte auch ein ungutes Gefühl, wenn alles auf einer einzigen ungespiegelten Platte liegt. Bei einer eventuellen Umfrage "Was sollen wir mit unserem Geld machen?" würde ich "SSD spiegeln" ankreuzen.

Gruss
walter

wambacher wrote:

Zecke wrote:

amm wrote:

Seit May 2011 werkelt dort eine einzelne Intel 320 SSD mit 600 GB Groesse und enthaelt die komplette osm2pgsql Datenbank. Der Umstieg von 4 velociraptor in Raid 10 auf eine einzelne SSD hat einen riesigen Unterschied gemacht und die I/O Performance ist nun kaum noch ein Problem.

...

Ich hätte auch ein ungutes Gefühl, wenn alles auf einer einzigen ungespiegelten Platte liegt. Bei einer eventuellen Umfrage "Was sollen wir mit unserem Geld machen?" würde ich "SSD spiegeln" ankreuzen.

Hast Du vielleicht den Satz davor überlesen? (Hervorhebung von mir)

amm wrote:

Der haupt Kachelserver von Openstreetmap verwendet eine SSD um die osm2pgsql Datenbank zu speichern und hat damit sehr gute Erfahrung gemacht.

Wenn dessen Datenbank abraucht, ist das kein Drama, dann gibt's halt (zweiter Server vernachlässigt) bis zum Neuaufbau keine neuen Kacheln. Beim primären Datenbankserver wäre das natürlich anders.

quasilotte wrote:

Leider gibt es bei den SSD nicht sowas wie s.w.i.f.t das Problem vorher ankündigen könnte

Wirklich kein Swift (ich spar mir mal die Punkte) bei SSD? Ich meine, ich hätte was anderes gelesen.

- wenn der interne Controller ausfällt ist halt nur noch der Zwischenspeicher da fertig ....

Ja, da bringt Swift auch nichts. Aber das kennen wir ja auch von den "richtigen" Platten.

Gruss
walter

Oli-Wan wrote:

Hast Du vielleicht den Satz davor überlesen? (Hervorhebung von mir)

amm wrote:

Der haupt Kachelserver von Openstreetmap verwendet eine SSD um die osm2pgsql Datenbank zu speichern und hat damit sehr gute Erfahrung gemacht.

Wenn dessen Datenbank abraucht, ist das kein Drama, dann gibt's halt (zweiter Server vernachlässigt) bis zum Neuaufbau keine neuen Kacheln. Beim primären Datenbankserver wäre das natürlich anders.

Ein wenig schon. Aber das ist inzwischen geklärt.

Gruss
walter

ps: Damit das hier nicht untergeht: könnte postgresql.conf von Yevaud ganz gut gebrauchen.

wambacher wrote:

Kannst du mir sagen, welchen Hersteller du bevorzugst und welchen Typ du einsetzt?

Nachtrag: Die SSD, die bisher problemlos läuft, ist eine Kingston SV100S2 128GB.
Auf diesem Gebiet ist aber alles im Fluss und auch die Foren sind mit Vorsicht zu genießen: Da melden sich natürlich bevorzugt diejenigen, bei denen es Probleme gab, bei gängigen Typen eben deutlich mehr.
Es besteht eine gewisse Korrelation von höherem Preis zu Zuverlässigkeit, aber halt leider nicht zwingend.

Nachdem so schlecht über OCZ gesprochen wurde...
Hab ne 30 GByte OCZ Vertex seit Anfang 2009 in meinem Media Center PC im Einsatz und die läuft fast täglich und immer noch ganz problemlos. CrystalDiskInfo Zustand aktuell 93%

Auf meinem Hauptrechner habe ich ne Intel 30 GByte SLC SSD Systemplatte + Vertex Datenplatte drinnen (seit 2010). Die laufen auch noch ganz problemlos.
Wobei die Intel einmal Probleme gemacht hat - Ursache war das SATA Kabel.
Die Intel SSD hat allerdings einiges an Performance eingebüßt. Sie unterstützt kein Trim und hat kein "Garbage collection" wie die Vertex.
Die Performance der Vertex ist noch wie neu.

Nicht alle SSDs von OCZ sind schlecht und die Problemursache kann zT auch wo anders liegen (schlechte SATA Kabel).

Wichtig ist auch die SSD groß genug zu dimensionieren. SSDs sollte man nicht bis "zum Rand" voll machen.
Aktuell würde ich mir wohl entweder eine OCZ Vector oder eine Samsung 840 Pro kaufen.

nesol wrote:

Hab ne 30 GByte OCZ Vertex seit Anfang 2009 in meinem Media Center PC im Einsatz und die läuft fast täglich und immer noch ganz problemlos.

Und damit aus einer sehr frühen Serie, in der die Qualität noch passte.. Liest man die Erfahrungsberichte im Internet, so hat sich die Qualität von OCZ kontinuierlich verschlechtert.

Verschaff Dir selbst ein Bild und wirf einen Blick ins oben verlinkte OCZ-Forum, gerade die aktuellen Vertex- und Agility-Serien sind katastrophal und definitv keine Arbeitsgeräte!

free_as_a_bird wrote:

nesol wrote:

Hab ne 30 GByte OCZ Vertex seit Anfang 2009 in meinem Media Center PC im Einsatz und die läuft fast täglich und immer noch ganz problemlos.

Und damit aus einer sehr frühen Serie, in der die Qualität noch passte.. Liest man die Erfahrungsberichte im Internet, so hat sich die Qualität von OCZ kontinuierlich verschlechtert.

Verschaff Dir selbst ein Bild und wirf einen Blick ins oben verlinkte OCZ-Forum, gerade die aktuellen Vertex- und Agility-Serien sind katastrophal und definitv keine Arbeitsgeräte!

Ich schätze mal, daß sich der Einsatz einer SSD in einem Media-Center wohl etwas von meiner geplanten Nutzung unterscheidet.

Auch bei den Festplatten gibt es ja Unterschiede zwischen den verschiedenen Anwendungen, die sich auch in den Preisen niederschlagen:

z.B. 1000GB Seagate Video 3.5 HDD ST1000VM002 für ca 54€ und dagegen 1000GB Seagate Enterprise Value HDD / Terascale HDD ST1000NC001 für ca 81€ beim gleichen Händler.
einmal Videos schnell abspielen und dagegen 24/7/365 Dauerbetrieb im Server.

Gruss
walter

Augustus Kling wrote:

Sieh dir die Dokumentation unter http://www.postgresql.org/docs/9.3/stat … stats.html insbesondere zu pg_stat_user_indexes und pg_statio_user_indexes an. Damit solltest du dir ein Bild von der Wichtigkeit der Indizes für deine Abfragen machen können.

Hab mir mal die ersten Auswertungen angesehen und hab da schon einen Kandidaten:

planet2=#␣select␣indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
from␣pg_catalog.pg_stat_user_indexes
where␣indexrelname␣like␣'planet%'
order␣by␣idx_scan␣desc;

indexrelname␣␣␣␣␣␣␣␣␣␣|␣idx_scan␣|␣idx_tup_read␣|␣idx_tup_fetch
-------------------------------+----------+--------------+---------------
planet_osm_nodes_pkey␣␣␣␣␣␣␣␣␣|␣41794098␣|␣␣␣␣␣40819507␣|␣␣␣␣␣␣␣␣361623
planet_osm_ways_pkey␣␣␣␣␣␣␣␣␣␣|␣␣3691033␣|␣␣␣␣␣␣4113560␣|␣␣␣␣␣␣␣2145667
planet_osm_rels_parts␣␣␣␣␣␣␣␣␣|␣␣1703546␣|␣␣␣␣␣␣␣982071␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
planet_osm_point_pkey␣␣␣␣␣␣␣␣␣|␣␣1506524␣|␣␣␣␣␣␣␣104570␣|␣␣␣␣␣␣␣␣103328
planet_osm_ways_nodes␣␣␣␣␣␣␣␣␣|␣␣1303080␣|␣␣␣␣␣␣␣402152␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
planet_osm_polygon_pkey␣␣␣␣␣␣␣|␣␣␣442874␣|␣␣␣␣␣␣␣␣64867␣|␣␣␣␣␣␣␣␣␣42190
planet_osm_roads_pkey␣␣␣␣␣␣␣␣␣|␣␣␣442760␣|␣␣␣␣␣␣␣␣91993␣|␣␣␣␣␣␣␣␣␣72423
planet_osm_line_pkey␣␣␣␣␣␣␣␣␣␣|␣␣␣442760␣|␣␣␣␣␣␣␣655940␣|␣␣␣␣␣␣␣␣424225
planet_osm_rels_pkey␣␣␣␣␣␣␣␣␣␣|␣␣␣␣58035␣|␣␣␣␣␣␣␣␣84276␣|␣␣␣␣␣␣␣␣␣54548
planet_osm_polygon_index␣␣␣␣␣␣|␣␣␣␣␣␣560␣|␣␣␣␣␣␣6078783␣|␣␣␣␣␣␣␣␣215223
planet_osm_point_index␣␣␣␣␣␣␣␣|␣␣␣␣␣␣205␣|␣␣␣␣␣␣␣218481␣|␣␣␣␣␣␣␣␣␣70466
planet_osm_roads_index␣␣␣␣␣␣␣␣|␣␣␣␣␣␣103␣|␣␣␣␣␣␣␣␣␣8559␣|␣␣␣␣␣␣␣␣␣␣7386
planet_osm_ways_idx␣␣␣␣␣␣␣␣␣␣␣|␣␣␣␣␣␣␣15␣|␣␣␣␣␣␣␣375353␣|␣␣␣␣␣␣␣␣196279
planet_osm_rels_idx␣␣␣␣␣␣␣␣␣␣␣|␣␣␣␣␣␣␣15␣|␣␣␣␣␣␣␣␣49990␣|␣␣␣␣␣␣␣␣␣25454
planet_osm_line_index␣␣␣␣␣␣␣␣␣|␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
planet_osm_roads_tags_index␣␣␣|␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
planet_osm_point_tags_index␣␣␣|␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
planet_osm_polygon_tags_index␣|␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
planet_osm_line_tags_index␣␣␣␣|␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣0␣|␣␣␣␣␣␣␣␣␣␣␣␣␣0
(19␣rows)

planet2=#␣\di+␣planet_osm_nodes_pkey
List␣of␣relations
Schema␣|␣␣␣␣␣␣␣␣␣Name␣␣␣␣␣␣␣␣␣␣|␣Type␣␣|␣␣Owner␣␣␣|␣␣␣␣␣␣Table␣␣␣␣␣␣␣|␣Size␣␣|␣Description
--------+-----------------------+-------+----------+------------------+-------+-------------
public␣|␣planet_osm_nodes_pkey␣|␣index␣|␣postgres␣|␣planet_osm_nodes␣|␣43␣GB␣|
(1␣row)

sind natürlich erstmal ziemlich grobe Eindrücke, aber planet_osm_nodes_pkey ist mein Spitzenkandidat.
Das deckt sich wohl in etwa mit dem Flatfile, wo ja genau die gleichen Daten der Nodes drin stehen sollen. Muß ich aber noch checken.
Sollte das flatfile wohl aktivieren. planet_osm_nodes ist riesig und der index ist auch net gerade klein. Ich hoffe nicht, dass das einen Re-Import mit osm2pgsql bedeutet :(

Danke & Gruss
walter

ps: jetzt bekommt planet_osm_nodes_pkey erst einmal seine "eigene" Platte (noch nicht ssd), da ich noch eine fast unbenutzte 80 GB-Platte rumliegen habe.

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

Netzwolf wrote:

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.

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,

wambacher wrote:

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.

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

Netzwolf wrote:

Nahmd,

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

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

wambacher wrote:

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.

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,

wambacher wrote:

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.

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

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.

Nop wrote:

Flatfile ist ein neueres Feature und cached die Koordinaten der Nodes in einer simplen Datei anstatt in der Datenbank. Viel weniger Platzverbrauch, viel schnellerer Zugriff und auf einer SSD rockt das. :-)

amm wrote:

Insofern wuerde ich jedem der einen vollen Planet importiert die Option flatnodes empfehlen!

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 … ser=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.