DBMS für gr. Datenmengen gesucht

[Maxle]

Hallo,

da ich feststellen muss, dass MySQL in diesem Fall nicht sehr gut geeignet ist, da das Cachen der tmp Tabelle einem Flaschenhals gleicht, bin ich nun auf der Suche nach einem DBMS, dass mit einer riesen Tabelle besser zu Recht kommt.

Beispiel, die meinem Anwendungsfall entspricht:
vereinfachte Struktur:
Tab1: PK INT i1, INT i2, INT i3
Tab2: INT j1, INT j2, INT j3; PK(j1,j2)
Daten:
Tab1 mit etwa 100000 Datensätzen.
Tab2 soll nun erstellt werden aus dem Kreuzprodukt von Tab1.i1 mit Tab1.i1, wobei doppelte Eintragungen (Reihenfolge gleichgültig) unerwünscht sind:
INSERT INTO Tab2 (j1,j2)
 SELECT T1_1.i1,T1_2.i2 FROM Tab1 AS T1_1,Tab1 AS T1_2
  WHERE T1_1.i1<T1_2.i2;
Ich komme damit auf ca 5000000000 Datensätze in Tab2.
Nun wird in einem letzten Schritt durch algorithmische Umformungen der Inhalt von j3 für die vielen Einräge erzeugt. Leider handelt es sich um eine COS, PWR & SQRT Berechnung, aber irgendwann sollte auch diese für alle Datensätze abgeschlossen sein; stark vereinfacht:
UPDATE Tab2 SET j3=(SELECT (T1_1.i2*T1_1.i3+T1_2.i2*T1_2.i3) FROM Tab1 AS T1_1,Tab1 AS T1_2 WHERE T1_1.i1=Tab2.j1 AND T1_2.i1=Tab2.j2);

Insbesondere die Datenmenge von Tab2 lässt mich zum Entschluss kommen, dass ich eine Alternative zu MySQL benötige und nun wüsste ich gerne, wozu Ihr mir raten würdet.

Danke für Tipps!

[Vinzenz Mai]

Hallo,

da ich feststellen muss, dass MySQL in diesem Fall nicht sehr gut geeignet ist, da das Cachen der tmp Tabelle einem Flaschenhals gleicht, bin ich nun auf der Suche nach einem DBMS, dass mit einer riesen Tabelle besser zu Recht kommt.

MySQL kommt mit riesigen Tabellen sehr wohl zurecht, andere DBMS wie DB2, Informix, Oracle, PostgreSQL, MS SQL-Server auch.

Beispiel, die meinem Anwendungsfall entspricht:
Tab1 mit etwa 100000 Datensätzen.
Tab2 soll nun erstellt werden aus dem Kreuzprodukt von Tab1.i1 mit Tab1.i1, wobei doppelte Eintragungen (Reihenfolge gleichgültig) unerwünscht sind:
INSERT INTO Tab2 (j1,j2)
SELECT T1_1.i1,T1_2.i2 FROM Tab1 AS T1_1,Tab1 AS T1_2
  WHERE T1_1.i1<T1_2.i2;
Ich komme damit auf ca 5000000000 Datensätze in Tab2.

Nun wird in einem letzten Schritt durch algorithmische Umformungen der Inhalt von j3 für die vielen Einräge erzeugt. Leider handelt es sich um eine COS, PWR & SQRT Berechnung, aber irgendwann sollte auch diese für alle Datensätze abgeschlossen sein; stark vereinfacht:

Ach ja, und wie oft wird das durchgeführt?

Entfernungsberechnung mittels Geo-Daten?

Insbesondere die Datenmenge von Tab2 lässt mich zum Entschluss kommen, dass ich eine Alternative zu MySQL benötige und nun wüsste ich gerne, wozu Ihr mir raten würdet.

Statt n²/2 Werte möglichst schnell zu berechnen - und die meisten davon vermutlich wieder wegzuwerfen, solltest Du eher an ein cleveres Herangehen an die eigentliche Aufgabe denken.

Freundliche Grüße

Vinzenz

[Maxle]

Entfernungsberechnung mittels Geo-Daten?

Exakt!
Und zwar eine Berechnung der Distanzen aller Orte untereinander.
Eine Selektion von wenigen Orten soll hierbei nicht stattfinden. Stattdessen soll nach Fertigstellung wirklich die Entfernung aller Orte untereinander in DE auf Anhieb vorliegen, bzw. abgefragt werden können.
Z.B. eine Umkreissuche...

Statt n²/2 Werte möglichst schnell zu berechnen - und die meisten davon vermutlich wieder wegzuwerfen, solltest Du eher an ein cleveres Herangehen an die eigentliche Aufgabe denken.

Es sollte nichts umsonst berechnet werden hierbei, oder wo?

Verbesserungsvorschläge - ohne Änderung der "Aufgabenstellung", dass alle Ortsdistanzen bekannt sind am Ende?

[Peter Pan]

Hi!

Entfernungsberechnung mittels Geo-Daten?
Exakt!
Und zwar eine Berechnung der Distanzen aller Orte untereinander.
Eine Selektion von wenigen Orten soll hierbei nicht stattfinden. Stattdessen soll nach Fertigstellung wirklich die Entfernung aller Orte untereinander in DE auf Anhieb vorliegen, bzw. abgefragt werden können.
Z.B. eine Umkreissuche...

Aber so etwas führst Du nicht ständig durch, sondern einmalig bzw. bei Bedarf wieder, oder?

off:PP

[Maxle]

Hi,

ja klar. Einmalige Berechnung als Vorbereitung der Daten, damit dann schnell und einfach per SELECT ... WHERE OrtID=... AND Distanz=...; abgefragt werden kann.
Würde mich ja nicht stören, wenn die (einmalige vorab) Berechnung 1 Woche dauern würde, aber gerade hängt der MySQL Server schon wegen der Kreuzproduktsache.
Anschließend kam mir die Idee, den PK wegzulassen, damit die Tabelle schneller geschrieben werden kann. Nun hatte ich die Tabelle mit 40GB+ (3 INT Felder) und wollte mit ALTER TABLE ... ADD PRIMARY KEY(f1,f2); den Key wieder erstellen und das läuft nun schon 5 Tage, blockiert das DBMS, welches mit show processlist nur sagt: "copy to tmp table".

Tipps?

Hi!

Entfernungsberechnung mittels Geo-Daten?
Exakt!
Und zwar eine Berechnung der Distanzen aller Orte untereinander.
Eine Selektion von wenigen Orten soll hierbei nicht stattfinden. Stattdessen soll nach Fertigstellung wirklich die Entfernung aller Orte untereinander in DE auf Anhieb vorliegen, bzw. abgefragt werden können.
Z.B. eine Umkreissuche...

Aber so etwas führst Du nicht ständig durch, sondern einmalig bzw. bei Bedarf wieder, oder?

off:PP

[Peter Pan]

Hi!

bitte kein TOFU!

ja klar. Einmalige Berechnung als Vorbereitung der Daten, damit dann schnell und einfach per SELECT ... WHERE OrtID=... AND Distanz=...; abgefragt werden kann.

Das verstehe ich noch.

Würde mich ja nicht stören, wenn die (einmalige vorab) Berechnung 1 Woche dauern würde, aber gerade hängt der MySQL Server schon wegen der Kreuzproduktsache.

Auch das.

Anschließend kam mir die Idee, den PK wegzulassen, damit die Tabelle schneller geschrieben werden kann.

Du hast nicht den Pk oder anderes Indices gelöscht, oder?

Nun hatte ich die Tabelle mit 40GB+ (3 INT Felder) und wollte mit ALTER TABLE ... ADD PRIMARY KEY(f1,f2);

..es liest sich fast so.

off:PP

[Ilja]

yo,

Anschließend kam mir die Idee, den PK wegzulassen, damit die Tabelle schneller geschrieben werden kann.

Du hast nicht den Pk oder anderes Indices gelöscht, oder?

zu pauschal, ohne den ausführungsplan zu kennen kann man nicht sagen, ob er indexe braucht oder ob sie alles nur verlangsamen.

Ilja

[Peter Pan]

Hi!

Anschließend kam mir die Idee, den PK wegzulassen, damit die Tabelle schneller geschrieben werden kann.

Du hast nicht den Pk oder anderes Indices gelöscht, oder?

zu pauschal, ohne den ausführungsplan zu kennen kann man nicht sagen, ob er indexe braucht oder ob sie alles nur verlangsamen.

Ich habe deshalb nachgefragt, vielleicht hat er ja eine Begründung dafür, diese fehlt aber noch.

off:PP

[Ilja]

yo,

Ich habe deshalb nachgefragt, vielleicht hat er ja eine Begründung dafür, diese fehlt aber noch.

hmm, mit: "Du hast nicht den Pk oder anderes Indices gelöscht, oder?" meinst du, dass es durchaus sinnvoll sein kann, diese zu löschen, aber er sicherheitshalber vorher noch mal den ausführungsplan durchgehen sollte ?

Ilja

[Peter Pan]

Hi!

Ich habe deshalb nachgefragt, vielleicht hat er ja eine Begründung dafür, diese fehlt aber noch.

hmm, mit: "Du hast nicht den Pk oder anderes Indices gelöscht, oder?" meinst du, dass es durchaus sinnvoll sein kann, diese zu löschen, aber er sicherheitshalber vorher noch mal den ausführungsplan durchgehen sollte ?

Du bist g'rad a bissl "auf Krawall gebürstet", nicht?

Oder wolltest Du etwas zum Thema sagen? Der OP offensichtlich nicht mehr.

salam:PP

[Ilja]

yo,

Du bist g'rad a bissl "auf Krawall gebürstet", nicht?

nah, gerade genug stress hier auf arbeit. ich will sagen, dass indexe oder constraints bei grosseren DML anweisungen durchaus stören können. ob man sie wirklich braucht, sollte der ausfühunrgsplan hergeben. indexe sind kein allheilmittel, im gegenteil, eigentlich sollte man sie vermeiden, wenn sie nicht auch wirklich genutzt werden.

Oder wolltest Du etwas zum Thema sagen? Der OP offensichtlich nicht mehr.

nun, ganz offensichtlich doch noch....

Ilja

[Peter Pan]

Hi!

Du bist g'rad a bissl "auf Krawall gebürstet", nicht?

nah, gerade genug stress hier auf arbeit. ich will sagen, dass indexe oder constraints bei grosseren DML anweisungen durchaus stören können. ob man sie wirklich braucht, sollte der ausfühunrgsplan hergeben. indexe sind kein allheilmittel, im gegenteil, eigentlich sollte man sie vermeiden, wenn sie nicht auch wirklich genutzt werden.

Dem widersprehe ich doch nicht.

Oder wolltest Du etwas zum Thema sagen? Der OP offensichtlich nicht mehr.

nun, ganz offensichtlich doch noch....

habe ich soeben gesehen, ja.

off:PP

[Maxle]

zu pauschal, ohne den ausführungsplan zu kennen kann man nicht sagen, ob er indexe braucht oder ob sie alles nur verlangsamen.

wie gesagt: der versuch war, dass ohne pk/index die daten eingetragen wurden und anschließend die 45gb tabelle mit einem pk als kombination von 2 unique int-Feldern auszustatten.
Die Erstellung der Daten dauerte <24h, aber der PK war nach 6 Tagen immer noch nicht drangehängt.
Jetzt hab ichs wieder abgebrochen, weil es nicht fertig werden wollte, obwohl auf dem Rechner nichts außer dem Mysql Server am Laufen ist. D.h. alle Ressourcen verwendet der und trotzdem geht es so langsam...

[Ilja]

yo,

Jetzt hab ichs wieder abgebrochen, weil es nicht fertig werden wollte, obwohl auf dem Rechner nichts außer dem Mysql Server am Laufen ist. D.h. alle Ressourcen verwendet der und trotzdem geht es so langsam...

wie bereits weiter oben beschrieben, es ist auch eine frage der hardware. was dir zum beispiel passieren kann ist, dass nicht genug platz auf der festplatte mehr vorhanden ist. indexe können bei deiner datenmenge sehr gross werden oder aber zu wenig arbeitsspeicher für das DBMS, eine langsame festplatte....

zum anderen bin ich kein "fan" von primary keys über mehrere spalten, würde ich grundzäzlich vermeiden. aber das löst dein problem nicht wirklich, wenn du mal ein unique key über zwei spalten anlegen willst.

Ilja

[Maxle]

Hi Ilja,

zum anderen bin ich kein "fan" von primary keys über mehrere spalten, würde ich grundzäzlich vermeiden. aber das löst dein problem nicht wirklich, wenn du mal ein unique key über zwei spalten anlegen willst.

Nein, es muss kein unique key sein, da ich semantisch sicherstelle, dass es unique ist. Aber eine DB ohne PK sollte es ja auch nicht sein, nehme ich an, sodass die Alternative wäre, dass ein weiteres Feld mit einem auto increment INT hinzugefügt wird, was wiederum noch mehr Speicher und Aufwand kostet oder liege ich da falsch?

[Ilja]

yo,

Aber eine DB ohne PK sollte es ja auch nicht sein, nehme ich an, sodass die Alternative wäre, dass ein weiteres Feld mit einem auto increment INT hinzugefügt wird, was wiederum noch mehr Speicher und Aufwand kostet oder liege ich da falsch?

ganz genau, ich bevorzuge immer eine "künstliche spalte" für den primary key. ob das letztlich wirklich mehr speicherplatz braucht, da bin ich mir unsicher. den das dbms wird für den pk automatisch einen unique index anlegen. und der wird über zwei spalten größer als über eine. wäre bei deiner datengröße mal interessant herauszufinden, was größer ist, das mehr an tabelle oder der größere index...

Ilja

[Maxle]

Hi Ilja,

werde das gleich rausfinden...

Danke schonmal für den Tipp

[Vinzenz Mai]

Hallo,

zu pauschal, ohne den ausführungsplan zu kennen kann man nicht sagen, ob er indexe braucht oder ob sie alles nur verlangsamen.
wie gesagt: der versuch war, dass ohne pk/index die daten eingetragen wurden und anschließend die 45gb tabelle mit einem pk als kombination von 2 unique int-Feldern auszustatten.

grundsätzlich solltest Du Dir das Handbuchkapitel Optimization vornehmen, falls Du dies noch nicht getan haben solltest. Darin könnten Dich die vorkonfigurierten Option-Files interessieren. Vielleicht hilft Dir my-huge.cnf bzw. my-huge.ini bereits weiter. Out-of-the-box ist MySQL normalerweise nicht für riesige Datenmengen konfiguriert, weil diese in den meisten Fällen nicht vorhanden sind.

Weiterhin sind folgende Abschnitte aus dem Kapitel Optimization besonders interessant:

- Multiple-Column Indexes
 - How MySQL Uses Indexes

Wenn Du den Mehrspaltenindex nicht nutzt, dann laß ihn weg. Willst Du nur einen PK um des PKs willen, nutze eine künstliche Spalte. Einziges Interesse bei Deiner Anwendung sind ja Leseoperationen. Du kennst Deine Anwendung, ermittle, ob Dir diese vom Mehrspaltenindex profitieren können. Wenn nein, weg damit. Die typische Frage wird doch eher so sein:

Gib mir die Datensätze, für die die Entfernung zu einem gegebenen Ort geringer ist als eine bestimmte Grenze. Da Du auf die doppelte Speicherung verzichten willst, kann dieser gegebene Ort sowohl in der ersten Spalte als auch in der zweiten Spalte stehen und Deine Abfrage wird somit eine UNION zweier Teilabfragen werden:

Gib mir die Datensätze,
    für die die Entfernung zu Ort X
    in Spalte A geringer ist als eine bestimmte Grenze.
Vereinigt mit
den Datensätzen,
    für die die Entfernung zu Ort X
    in Spalte B geringer ist als eine bestimmte Grenze.

Ich sehe daher keine Notwendigkeit für einen kombinierten Index über die beiden Ortsspalten, eher welche für kombinierte Indexe über (Ortsspalte A, Entfernung) und (Ortsspalte B, Entfernung).

Freundliche Grüße

Vinzenz

[Timo]

Hi,

Entfernungsberechnung mittels Geo-Daten?
Exakt!

An sowas wollte ich die nächsten Tage auch ran....

Und zwar eine Berechnung der Distanzen aller Orte untereinander.
Eine Selektion von wenigen Orten soll hierbei nicht stattfinden. Stattdessen soll nach Fertigstellung wirklich die Entfernung aller Orte untereinander in DE auf Anhieb vorliegen, bzw. abgefragt werden können.
Z.B. eine Umkreissuche...

und deshalb wundere ich mich über deine Frage hier. Normalerweise würde ich das mit GeoClassPHP machen und die Daten von OpenGeoDB einsetzen.

Dabei erziele ich ja eigentlich das was du willst:
Beispiel

Nun bin ich verunsichert, was bewegt dich also dazu die DB so vollzustopfen? Oder erwartet mich etwa das gleiche Schiksal?

Timo

[Ilja]

yo,

es gibt noch einen andren punkt nebem dem DBMS, den du beachten solltes und zwar die hardware, die eingesetzt wird. worauf soll den das DBMS laufen ?

Ilja

[Multi]

Mahlzeit,

es gibt noch einen andren punkt nebem dem DBMS, den du beachten solltes und zwar die hardware, die eingesetzt wird.

Sie muss ausreichend dimensioniert sein und deren Netzwerkanbindung muss ausreichend sein um die Anfragen zu bewältigen.
Also abhängig von der Anzahl der Anfragen und der Komplexität derer.

worauf soll den das DBMS laufen ?

Auf einer Hardware, die es ermöglicht, das DBMS lauffähig aufzusetzen.

[Multi]

Mahlzeit,

ich wollte damit dem OP die Auswahl erleichtern, falls das nicht richtig rübergekommen ist ;)

[Ilja]

yo,

Also abhängig von der Anzahl der Anfragen und der Komplexität derer.

hmm, die anzahl der abfragen und die komplexität hat in aller regel nicht wirklich was mit der bandbreite der netzwerkverbindung zu tun. eher ist es umgekehrt, dass die ergebnismenge im zusammenhang mit der netzwerkverbindnug steht.

Ilja

[Multi]

Mahlzeit,

hmm, die anzahl der abfragen und die komplexität hat in aller regel nicht wirklich was mit der bandbreite der netzwerkverbindung zu tun.

Dann solltest du auch bitte alles zitieren, da der erste Teilsatz ganz klar von der Hardware handelt:

es gibt noch einen andren punkt nebem dem DBMS, den du beachten solltes und zwar die hardware, die eingesetzt wird.

Sie muss ausreichend dimensioniert sein und deren Netzwerkanbindung muss ausreichend sein ...

Also bitte nicht meine Aussagen aus dem Zusammenhang reissen um sie dann zu widerlegen zu versuchen.

[Ilja]

yo,

Also bitte nicht meine Aussagen aus dem Zusammenhang reissen um sie dann zu widerlegen zu versuchen.

Zitat: "Sie muss ausreichend dimensioniert sein und deren Netzwerkanbindung muss ausreichend sein um die Anfragen zu bewältigen.
Also abhängig von der Anzahl der Anfragen und der Komplexität derer."

nun, versuchen tue ich gar nichts, für mich sind die beiden sätze missverständich. was den ersten satz angeht, so verstehe ich das so, als wenn es die gesamte abarbeitung von anweisungen beinhaltet und da stimme ich mit dir überein, das kann stark von der Netzwerkanbindung abhängen.

dem zweiten satz sehe ich aber im widerspruch dazu, weil damit eigentlich die vermutlich größte komponente, die einfluss auf die Netzwerkanbindung hat unter dem tisch fällt, nämlich die rückmeldung der ergebnisse an die clients. die anzahl der anfrage spielt sicherlich auch eine rolle, die komplexität der anfragen so gut wie gar nicht, aber wo erwähnst du die ergebnissmengen, die zurück zum auftraggeber gehen sollen ?

vielleicht meinst du ja auch mit komplexität die menge der ergebnisse, die zurück gehen sollen, aber das ist für mich missverständlich ausgedrückt.

Ilja

[Multi]

Mahlzeit,

Du hast es schon wieder aus dem Zusammenhang gerissen, obwohl ich es zum zweitem mal deutlich gemacht habe.
Ich antwortete direkt auf deine Aussage, die Hardware ist entscheidend. Du weisst noch, was du geschrieben hast?

Zitat: "Sie  [die Hardware] muss ausreichend dimensioniert sein und deren Netzwerkanbindung [Netzwerkanbindung der Hardware] muss ausreichend sein um die Anfragen zu bewältigen.

Wenn du es immer noch nicht verstanden hast (andere haben es bereits geistig erfassen können, so unlogisch kann meine Aussage nicht sein), geb ich es auf.
Zweimal ein Zitat aus dem Zusammenhang reissen und dann noch behaupten, du tust es nicht, ist schon grenzwertig. Dann in Kombination mit mangelnder Auffassungsgabe ist das ein echtes Problem.

[Ilja]

yo,

ich schreibe einfach mal, wie meiner nach der satz und zwar gerade auch im zusammenhang mit den anderen kontext hätte lauten sollen:

"Sie muss ausreichend dimensioniert sein und deren Netzwerkanbindung muss ausreichend sein um die Anfragen zu bewältigen. Also vorallem abhängig von der Anzahl der Anfragen und der größe der Ergebnismengen."

und damit sollte man es auch beruhen lassen, bevor es hier weiter zu beleidungen kommt. oder aber ein admin kann den zweig ja auch ganz löschen.

Ilja

[Andres Freund]

Moin Max,

Für mich hört es sich so an, als solltest du dich nach einem _grundlegend_ anderen Ansatz umsehen.

Der Grund warum du alle Entfernungen schon im vornherein ausrechnen möchtest ist welcher?

Ich nehme einmal an, dass dir sonst Abfragen über alles in der Umgebung von Punkt liegende zu langsam sind?

Meiner Meinung nach solltest du dich eher nach einer Lösung suchen die das (z.B. durch Indizes) beschleunigen kann. Deine Lösung wird nie im Leben sauber skalieren.

Z.B. könnten dir Lösungen wie Postgis (Postgres), DB2 Spatial, Oracle SDE helfen.

Ich _weiß_ nicht ob es kommerzielle Erweiterungen für Mysql in diesem Bereich gibt - aber ich bin auch absolut kein Freund von Mysql...

Meiner Vermutung nach dürfte das sogar weitaus schneller sein als deine komplett ausgerechnete Variante (Allein dein Index wird gezwungenermassen mehrere Gigabyte belegen, was heißt, dass für einenen einzelnen Zugriff relativ viele weitgehend nicht korrelierende Festplattenzugriffe notwendig sein werden. Und ohne einene Index wirst du bei diesen Datenmengen nicht zurechtkommen.)

Grüße,
Andres Freund

[Ilja]

yo,

Z.B. könnten dir Lösungen wie Postgis (Postgres), DB2 Spatial, Oracle SDE helfen.

leider hat er uns noch nicht die hardware verraten, auf dem das ganze laufen soll. aber dies bezüglich noch der hinweis von mir, dass die editions von oracle neben der funktionalität auch abhängig von den Prozesorsockets, bzw. annzahl an usern ist. insofern kann man nicht so einfach die Standard Edtion wählen, zumal auch die frage ist, ob er es kommerziell nutzen will oder nicht.

kurz gesagt die Lizensierung von oracle gleicht schon einen dschungel....

Ilja

[Andres Freund]

Hi,

yo,

Z.B. könnten dir Lösungen wie Postgis (Postgres), DB2 Spatial, Oracle SDE helfen.
leider hat er uns noch nicht die hardware verraten, auf dem das ganze laufen soll.

Alle diese Lösungen sollten auf ziemlich simpler Hardware annähernd schnell genug laufen.

(Postgis/Postgres sind unter einer freien Lizenz, um das nocheinmal zu betonen)

Grüße,
Andres Freund

[Ilja]

yo,

Alle diese Lösungen sollten auf ziemlich simpler Hardware annähernd schnell genug laufen.

nun, ich würde nicht spekulieren, sondern vieleicht äußert er sich ja noch dazu. sage nur bei Oracle lizenzen sollte man aufpassen.

Ilja