Laden...

Performanteste Datenbank für Koordinaten

Erstellt von unconnected vor 8 Jahren Letzter Beitrag vor 8 Jahren 2.067 Views
unconnected Themenstarter:in
849 Beiträge seit 2006
vor 8 Jahren
Performanteste Datenbank für Koordinaten

Hallo,

ich habe hier eine Tabelle auf einer Oracle Db die Koordinaten, Zeitstempel und noch ein varchar zu einer Id (nicht unique) speichert. Diese Tabelle bekommt ca. 500 Datensätze pro Sekunde zu verarbeiten und hält die Daten ca. 20 Tage vor, was zu einem Datenvolumen von mehreren 100 GB führt. Nun liegt auf der Id natürlich ein Index und der zugriff auf diesen geht auch schnell.
Sobald ich nun aber im Select auf die Koordinaten (nicht im Index enthalten) dauert ein Select auf eine Id mit 50000 Datensätzen ca. 40 sek., da ja nun zu dem Index auch Daten ausserhalb des Index benötigt werden. Diese Daten kann sich Oracle natürlich nicht im Ram halten (weil mehrere 100GB) und müssen mühsam auf der Platte zusammengekrazt werden.

Da sich diese Tabelle schon am Rande ihrer Leistungsfähigkeit bewegt ist die Idee, einen zusätzlichen Index anzulegen der Id + die zusätzlichen Daten die ich im Select benötige enthält, eher nicht so gern gesehen.

Nun zu meiner Frage: Gibt es Datenbanken die mit dieser Art und Menge von Daten besser zu recht kommen?

Grüße

H
523 Beiträge seit 2008
vor 8 Jahren

Moin,

wie sieht es denn mit der Fragmentierung der Tabelle aus? Hast Du Dir mal den Execution Plan Deiner SQL-Statements angesehen?

PS: Ich arbeite selber nicht mit Oracle, kann mir aber nicht vorstellen, dass Oracle mit dieser Menge nicht zurecht kommt.

3.511 Beiträge seit 2005
vor 8 Jahren

Moin,

also 100GB sind ja nicht viel für ein Oracle (oder jedes andere "größere" DBMS). Warum kein weiterer Index? Ist ja nichts schlimmes dran. Wichtig - also viel viel wichtiger - ist, wie sieht die IO dahinter aus? Liegen die Indizes in separaten Tablespaces? Auf welchem Storage Type liegen die Tablespaces (HDD, SAS, SSD)? Welche Art von Indizes nutzt ihr? Müssen die vorgehaltenen Daten immer und immer wieder abgefragt werden? Wenn nein, dann nehmt paritionierte Indizes. Wie sehen die Execution Pläne aus? Usw. usw. Da gibt es zich Optimierungsmöglichkeiten. Aber die größe der Tabelle ist definitiv nicht das Problem! Habe mit DBs größer 5TB gearbeitet, zwar unter MSSQL, aber die beiden Systeme nehmen sich nicht viel. IO ist fast immer der Flaschenhals.

Gruß
Khalid

"Jedes Ding hat drei Seiten, eine positive, eine negative und eine komische." (Karl Valentin)

16.806 Beiträge seit 2008
vor 8 Jahren

Klar; NoSQL-Datenbanken sind genau für solche Szenarien gemacht worden.
Und im Falle von MongoDB wird Geospatial direkt unterstützt.

D
985 Beiträge seit 2014
vor 8 Jahren

Würde es nicht Sinn machen die ID (varchar = ?? Bytes) in der Tabelle durch eine ID (integer = 4 Bytes) zu ersetzen?

Über eine weitere Tabelle kann die ID (varchar) der ID (integer) eindeutig zugeordnet werden und die Daten-Tabelle sollte erheblich an Größe verlieren.

unconnected Themenstarter:in
849 Beiträge seit 2006
vor 8 Jahren

Hallo,

und Danke für die vielen Antworten.

@Sir Rufo, Stimmt zumal es sich hier um Konstanten handelt.

Aber genau wie Khalid halte ich die größe der Db nicht für das Problem. und ich denke der zusätzliche Index würde ordentlich Schub geben.

Das Problem ist hier eher die durchschnittlich 500 Datensätze (Es gibt hier auch Peaks in die eine oder andere Richtung) pro sekunde, die eingefügt werden. Diese wollen ja auch mal in den Index einsortiert werden. Und ein zusätzlicher Index kostet beim Insert halt auch Zeit. Zumal der Server der die Datenbank befüllt jetzt schon hin und wieder Backlogs aufbaut, weil er die Datensätze nicht schnell genug in die Db blasen kann.

der select sieht ungefähr so aus:


select id, x,y,varcharSpalte,datum from Tabelle where id = 'A12DFB1784F' order by datum

Den Plan habe ich unten angehängt.

Der Index ist Type "NORMAL"

MongoDb, Geospatial,Fragmentierung und partitionierter Index habe ich schonmal zum Nachforschen aufgenommen. Falls noch jemand eine Idee hat, bin ich für jede Dankbar 😃

unconnected Themenstarter:in
849 Beiträge seit 2006
vor 8 Jahren

@Khalid

Leider fallen partielle Indexe hier aus. Die Datensätze werden von Usern abgerufen und eventuell auch mehrmals.

D
985 Beiträge seit 2014
vor 8 Jahren

Ob Konstante oder nicht, diese Werte sind mehrfach in der Tabelle enthalten und fett. Ein Integer ist dagegen eher schmal.

Und jetzt stell dir den Index vor, der ja im besten Fall komplett im Speicher gehalten werden soll (wegen der Performance). Bei ca. 864.000.000 Zeilen wirkt sich das nicht unerheblich aus.

Als zusätzlichen Index solltest du aber ID und Datum anlegen, denn die werden ja benutzt.

Des Weiteren solltest du dich fragen ob es wirklich Sinn macht, die ID nochmals zu übertragen, denn die ist doch eh eindeutig, weil du genau nach dieser ID fragst. Und nun willst du dir 50.000x diesen fetten ID Wert über die Leitung drücken lassen ...

W
955 Beiträge seit 2010
vor 8 Jahren

Ob Konstante oder nicht, diese Werte sind mehrfach in der Tabelle enthalten und fett. Ein Integer ist dagegen eher schmal. Wie kommst Du darauf? Das ist doch nur die HexString-Version eines 64-Integers. Das DIng ist maximal doppelt zu groß wie der Int. Das ist nicht das Problem, sondern die IO-Last, die er mit einem DB-Admin optimieren sollte.
Wenn ein anderes System verwendet werden könnte sollte dies erst einmal ausgiebig auf dessen Leistungsverhalten getestet werden.

D
985 Beiträge seit 2014
vor 8 Jahren

Ob Konstante oder nicht, diese Werte sind mehrfach in der Tabelle enthalten und fett. Ein Integer ist dagegen eher schmal.
Wie kommst Du darauf? Das ist doch nur die HexString-Version eines 64-Integers. Das DIng ist maximal doppelt zu groß wie der Int. Das ist nicht das Problem, sondern die IO-Last, die er mit einem DB-Admin optimieren sollte.
Wenn ein anderes System verwendet werden könnte sollte dies erst einmal ausgiebig auf dessen Leistungsverhalten getestet werden.

Da wir nicht wissen mit welcher Codierung der VarChar gespeichert wird, kann das zu groß von doppelt so groß (1 Byte pro Char) bis viermal so groß (2 Byte pro Char) sein.

Wenn es ein Int64 ist/darstellt, dann sollte das auch als Int64 gespeichert werden. Mit einer Zahl kommt eine Datenbank (gerade beim Index) immer besser zurecht als mit einer Zeichenfolge (nicht nur wegen der Größe).

Und bevor ich mir Gedanken darüber mache, wie ich die IO-Performance des Systems verbessere, denke ich darüber nach, wie ich das System generell entlaste.

W
955 Beiträge seit 2010
vor 8 Jahren

Möglicherweise gibt es aber Systeme die kein 64b-Int beherrschen, so dass diese Kodierung gewählt wurde. (Eine Änderung der Struktur bricht alle Systeme die darauf zugreifen.) Alles weitere ist Raten.
Vllt sollte man mal wirklich testen was ein ein weiterer Index für Last bringt.

742 Beiträge seit 2005
vor 8 Jahren

Du wirst um Indices nicht herum kommen. Ohne Indices wird es auf ein Table-Scan mit einem relativen teuren Vergleich (geographische Distanz) und viel IO hinauslaufen. Das kann nicht performant sein.

Bei Indices für räumliche Abfragen werdem Datenstrukturen aufgebaut, z.B. Quadtree, R-Tree, oder Geohashing. Ich habe nicht viel Erfahrung damit, was passiert, wenn sich Daten oft ändern. Die Theorie sagt aber, dass es zu relativ aufwändigen Neustrukturierungen des Indices kommen kann. Es kann daher sinnvoll sein, die Daten in mehrere Tabellen aufzuteilen (z.B. eine Tabelle pro Tag). Das muss man aber testen, ich weiß nicht wie clever SQL Server usw. hier ist.

unconnected Themenstarter:in
849 Beiträge seit 2006
vor 8 Jahren

Ja stimmt die übertragung der Id ist nicht nötig, würde bei weiterer Optimierung auch rausfallen. Das fällt aber nicht wirklich ins Gewicht, weil beim 2. Ausführen das ganze in millisekunden (Zumindest ist das so schnell das keine wirkliche verzögerung feststellen konnte) geladen ist.

Und 50000 Datensätze nach nem Zeitstempel zu sortieren ist für oracle nicht wirklich das Thema, auch ohne Index. Wenn ich das sortieren weglasse ist der select genau so langsam/schnell.

Die einzige Beschleunigung die ich wirklich sehe, ist die Spalten die ich brauche mit in einen neuen Index zu nehmen (ID,Zeitstempel,X,Y,VarcharSpalte) um Oracle den schritt zu ersparen vom Index nochmals in die dazugehörige Row per RowId zu greifen und somit ein zusätzlichen Festplatten I/O erzeugt.

Die Id ist sogar 96Bit lang hatte oben nur beispielweise eine hineingeschrieben damit klar wird das hier ein Hex String (Der natürlich länger als 96 Bit ist) verwendet wird. Was ich im übrigen auch nicht für optimal halte.

Grüße und vielen Dank nochmals für die rege Beteiligung.

D
985 Beiträge seit 2014
vor 8 Jahren

Es gibt aber für alles schon fertige Lösungen.

Wenn es sich beim Eintragen in die Datenbank staut, dann schaltet man eine Queue dazwischen (MSMQ, ActiveMQ, etc.) um gerade diese Peaks vernünftig abzufangen.

Ist die Art der Datenspeicherung nicht effizient, dann baut man diese effizient um und gibt den bestehenden Systemen eine View auf diese Daten. Schon ist es nach aussen hin wieder wie vorher.

D
985 Beiträge seit 2014
vor 8 Jahren

Es kann daher sinnvoll sein, die Daten in mehrere Tabellen aufzuteilen (z.B. eine Tabelle pro Tag). Das muss man aber testen, ich weiß nicht wie clever SQL Server usw. hier ist.

Dafür sollte der Server das Partitioning einer Tabelle beherrschen, dann verhält sich diese wie mehrere Tabellen, belibt aber nach aussen hin eine 😉

5.657 Beiträge seit 2006
vor 8 Jahren

Hi unconnected,

Nun zu meiner Frage: Gibt es Datenbanken die mit dieser Art und Menge von Daten besser zu recht kommen?

Viele Datenbanken unterstützen mittlerweile Koordinaten als Datentypen für Tabellenspalten und räumliche Abfragen, u.a. der SQL-Server und PostgreSQL/PostGIS.

Christian

Weeks of programming can save you hours of planning

T
2.219 Beiträge seit 2008
vor 8 Jahren

Klingt sehr nach gespeicherten GPS Daten 😃
Ich arbeite beruflich auch an einer Flottenlösung, die quasi das gleiche Thema hat.
Wir suchen hier aber nach einem anderen Schema.

Die Daten, vereinfacht, speichern wir mit der Imei, dem Zeitstempel und den Geopos in Latitude und Longitude.
Da wir Daten meist zeitlich auswerten, also z.B. für ein oder mehrere Fahrzeuge, haben wir in der DB einen Index auf die Imei und den Zeitstempel.
Wir halten dann in der DB Daten für rund eine Woche vor.
Dabei kommen aktuell einige Mio. Datensätze zusammen.

Alle Daten die älter als eine Woche sind, lagern wir auf eine Storage Lösung mit einem RAID 5 aus.
Dort speichern wir die Daten dann in Ordnern mit dem Namen Schema yyyyMMdd
In jedem Ordner befindet sich dann eine Textdatei mit der Imei als Name.
Darin sind dann die Daten des jeweiligen Tages in einem festen Format gespeichert.

Somit lassen sie auch größere Datenmengen performant auswerten.
Man muss nur doppelte Daten Sicherung betreiben um halt die Daten in der DB als auch die Daten auf der Storage zu speichern.

Wenn möglich, könntest du dieses Konzept übernehmen.
Bisher lässt es sich damit recht gut und performant fahren.
Falls dies aber für dich eher ungünstig ist, da du z.B. für deine ID nach bestimmten Positionen über einen größeren Zeitraum suchst, dann wird dieses Konzept eher wenig hilfreich sein.
Hier müsstest du dann eine extra Auswertung durch einen Task machen, wenn die Auswertungen auch mal länger laufen können.

Nachtrag:
Wir sprechen bei den gespeicherten Dateien auf der Storage aber auch von einigen GB, diese belaufen sich auf über 100 GB.
Diese Lösung mit einer klassischen Herachie könnte ggf. was für dich sein 😃
Als DB kommt bei uns der MS SQL Server 2012 zum Einsatz, der bisher auch sehr performant bei unserer Flottenlösung ist.
Hoffe ich konnte dir ein brauchbares Konzept geben.

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

unconnected Themenstarter:in
849 Beiträge seit 2006
vor 8 Jahren

Falls dies aber für dich eher ungünstig ist, da du z.B. für deine ID nach bestimmten Positionen über einen größeren Zeitraum suchst, dann wird dieses Konzept eher wenig hilfreich sein.

Genau so ist es.

Es geht darum für eine bekannte Id alle bekannten koordinaten zu holen. Und das möglichst Performant, ohne das einfügen der Daten zu sehr zu bremsen.
Im Moment spiele ich gerade mit mongodb ein wenig rum.

Sind übrigens keine GPS sondern RFID positionen 😉 was aber nicht den unterschied macht denke ich.

T
2.219 Beiträge seit 2008
vor 8 Jahren

Macht keinen Unterschied.
Ob die ID eine Imei, eine Guid oder was auch immer ist, spielt ja keine Rolle.

Schade, dass unser Konzept dir hier nicht hilft.
Denke mal, du wirst dann auch kaum mit einer Hierarchie das Problem lösen können.
Also z.B. Ordnernamen im Lat/Long Schema und dann die ID als DAteinamen.
Könnte man als billige Lösung testen.
Aber wenn NoSQL Datenbanken für den Job besser ausgelegt sind, kann man auch direkt zu einer greifen.
Hier aber bei MongoDB dann die Eisntellungen prüfen.
Per Default sind MongoDBs wohl nach außen sehr gesprächig, was letztens erst zu großem Trubel geführt hat.
Per Default wird quasi keine Authentifizierung geprüft.
Muss man extra anschalten 😕

T-Virus

Developer, Developer, Developer, Developer....

99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.

16.806 Beiträge seit 2008
vor 8 Jahren

MongoDBs hängen relativ selten direkt im Netz. Normalerweise sitzt ein Service davor.
Und dass Auth per default inaktiv ist liegt daran, dass die config einfach optional ist.
Ansonsten könnte man nie auf eine initiale Instanz zugreifen.