Verteilte Datenbank, Optimierung und MySQL Clusters?

[soundbear]

Hallo zusammen,

bin neu und falle mal gleich mit einer Frage ins Haus.

Ich arbeite seit vielen Jahr als Entwickler an einer Software zur Personalvermittlung. Naturgemäß entstehen hier sehr viele Daten, auch in recht unhandlichen Formaten wie Text und Varchar. Derzeit nutzen wir kanpp 100 Tabellen, die prinzipiell recht aufgeräumt sind (3.Normalform), wenn auch nicht überall. Das bringt halt agile Programmierung ab und an mit sich.

Problem/Ist-Zustand
Wir betreiben die Software derzeit auf einem eigene Rootserver (Quadcore, 4GB). Bis auf den Profilabgleich (Stelle/Arbeitnehmer) läuft der Rest gut. Beim Abgleich kommt es regelmäßig zu Zeitüberschreitungen. Die Abfrage habe ich gefühlte 100 Mal umgearbeitet und optimiert. Derzeit arbeite ich hier in 2 Stufen mit einem temporären Table (Memory-Engine), neugesetzten Indizies, um die Last zu mindern. Nach Recherche nutze ich vermehrt Joins - zumindest hat das ein wenig was gebracht, weil wahrscheinlich die elenden Kreuzprodukte nicht mehr gebildet werden. Hauptlast entsteht hier u.a. weil jedes Profil auf beiden Seiten zwischen 20 und 40 Berufe/Fertigkeiten aufweist, die verglichen/verknüpft werden - dazu kommen dann später noch Regionen, etc.

Verteilen oder nicht?
Soweit zur Vorgeschichte. Wir verknüpfen hier also Unmengen an Daten, beim Abgleich werden ca. 6-7 Tabellen genutzt. Abfragedauer kann zwischen ein paar Sekunden und einer halben Minute schwanken, je nach Menge der Profildaten. Nun geistert seit einiger Zeit der Gedanken durch den Raum das System zu verteilen. Leider kenne ich mich da nicht so aus, aber zB die MySQL Clusters sehen sehr vielversprechend aus, wobei ich aber gelesen habe, dass es weniger für größere Datenmengen geeignet ist. Wie kann ich das verstehen?? Nutzt das System jemand?

Wie optimieren?
Welche Möglichkeiten gibt es noch die Last mittels MySQL zu verteilen??
Wir möchten demnächst einen neuen Server holen mit mehr RAM und 2 x Quadcore-CPUs. Bringt eine gute Maschine mehr, als zB 2 "normale" Server und verteilter Last?? Was können wir noch an unserer MySQL-Abfrage/DB optimieren?

Wäre super, wenn Ihr ein paar Denkanstöße geben könntet!

Danke und Grüße
Soundbear

[thomas_w]

Meiner Meinung nach ist es einfacher und sicherer eine "dickere" Hardware zu nehmen und/oder auf normale Optimierung zu setzen. Nach meiner Erfahrung läßt sich eine Menge über normale Server-Parameter und Index-Optimierung erreichen.
Ab einer gewissen Projektgröße - viel paralleler Traffic / korrekterweise lang laufende Abfragen - ist ein Cluster vermutlich besser (da habe ich aber keine eigene Erfahrung).

Grüße
Thomas

[erc]

Das Problem wirst du nicht mit mehr Hardware lösen. Entweder ist die Abfrage schlecht oder das Datenbankdesign passt nicht zur Aufgabe.

ps: 3.Normalform ist nicht die Aufgabe!

[soundbear]

Die Aufgabe ist ganz klar: eine eierlegende Wollmilchsau.

Es gibt keine Aufgabe. Der Abgleich zwischen Stellenangebot und Arbeitnehmer-Profil ist nur der Bruchteil eines riesigen Systems. Aber genau der macht öfter Ärger bzw. erzeugt Flaschenhälse.

Die Enttäuschung: Weder das Datenbankdesign, noch die Abfrage ist schlecht. Kann das leider aus rechtlichen Gründen nicht posten. Das System ist über Jahre gewachsen und erweitert worden. Zeitgleich kommen täglich tausende Datensätze hinzu.

Fakt ist, dass hier also enorme Datenmengen kombiniert werden (wir haben Tabellen mit 20.000 und mehr Attributen). Wenn davon 6-7 "aufeinanderprallen" lässt, geht die beste CPU in die Knie. Nun steht die große Frage im Raum, wie man sowas optimiert und gehandlet bekommt.

[thomas_w]

Fakt ist, dass hier also enorme Datenmengen kombiniert werden (wir haben Tabellen mit 20.000 und mehr Attributen).

Ich gehe mal davon aus, dass Du Tabellen-Zeilen meinst. Ich glaube nicht, dass eine Tabelle 20.000 Spalten hat.

Wenn das Tabellendesign geheim ist, dann musst Du das Problem eben alleine lösen. Bleibt noch der Hinweis auf EXPLAIN

EXPLAIN SELECT ...

Mal einen "langsamen" Query mit EXPLAIN analysieren und das Result hier "gut formatiert" oder als Bild zeigen.

Grüße
Thomas

[soundbear]

Hallo,

danke zunächst für die Antworten. Wie gesagt, ich möchte nicht das DB-Design im großen Stil ändern, denn das ist recht fixiert. Auch wenn es nicht die 100%ige Optimalität hat. Über die Jahre steht hier Kompatibiltät und Uptime an forderster Stelle. Ich habe leider auch meine Vorgaben - hier hängt der Job von vielen Leuten dran. Genau das machts auch so schwierig.

Anbei mal die Explain-Ausgabe. Ich beschäftige mich seit 10 Jahren mit MySQL - hilft mir an der Stelle nicht weiter. Vielleicht sieht hier jemand mehr. Wäre allerdings nur die 1.Stufe. Laut Log macht aber genau die oft Probleme.


(DB-Prefix habe ich mal rausgeschnitten wegen Projektname)

Im allgemeinen gehts mir in erster Linie, um "allgemeine" Tips und Optimierungenstricks, die für MySQL gelten und die man hier und da noch anwenden könnte. zB das man statt Text oder Varchar Char verwendet, weil statische Tabellen schnell gelesen werden. Das Ersetzen der vielen WHERE-Kreuzabfragen gegen JOINS war auch eine lohnende Sache. Halt sowas ... wie gesagt, sinnvolle und hilfreiche Tips sind immer gern gesehen!!

Das Thema verteilte Systeme hat sich, denke mal, zunächst erledigt. Weder mein Autraggeber noch ich sind davon überzeugt. Von daher wird es eher ein Server mit 2 CPUs a 4 Kerne werden.

[thomas_w]

Laut EXPLAIN findet MySQL eigentlich immer einen Key (siehe Spalte "key"). Auffällig ist der Eintrag "Using temporary; Using filesort..." in der ersten Zeile. Hier lagert MySQL Daten auf die Platte aus. Das kostet Zeit.

Generelles Problem ist, dass die meisten Entwickler einen Index nur mit einer Spalte anlegen (z. B. user_id). Werden aber mehrere Spalten sinnvoll in einem Index zusammengefasst, dann kann MySQL eventuell besser zugreifen ( zeigt sich dann eventuell in Extra "Using index" ).

Weiterhin sind Indices nur so gut wie die Kardinalität bzw. Selektivität ist.
Werden "SELECT * FROM ..." nutzt der beste Index nichts. Also unnötige Spalten vermeiden.

Soweit zum Allgemeinen.

Grüße
Thomas

[soundbear]

Danke fürs Drüberschauen. Ja, das mit dem Temp.Table ist mir auch aufgefallen, zumal es hier nur 1 Datensatz gibt, nämlich die Stelle. Das muss ich nochmal überprüfen. Der Rest ist halt über Indizies verbunden. Schade nur, dass wir viele Spalten als Text und Varchar haben - das lässt sich auch nicht vermeiden. (siehe Type)

Wie habe ich mir das mit dem Index über mehrere Spalten vorzustellen?

[thomas_w]

Falls noch kein identischer Index existiert, dann würde ich folgenden für die Tabelle "s" vorschlagen. Wie die Spalte des PRIMARY KEY der Tabelle "s" genau heißt, sehe ich leider nicht.

CREATE INDEX sx_tabelle_s_01 ON tabelle_s (user_id, PRIMARY);

und dann nochmal den EXPLAIN ausführen und schauen ob der neue Index verwendet wird.

Grüße
Thomas

[soundbear]

Bei der Stelle (s) gibt quasi schon 2 Indizies, einmal user_id (Besitzer) und einmal id (für die Stelle, PRIMARY) selbst.

Verstehe trotzdem noch nicht ganz, wozu wir hier einen 2te Index brauchen - was verwaltet der denn?

[thomas_w]

Bis jetzt gab es vermutlich folgendes:

CREATE TABLE s
   id INT NOT NULL,
   ...
   PRIMARY KEY(id),
   KEY user_id,
   ...

Also zwei Indices mit je einem Feld. MySQL kann aber bei einer Abfrage
zumeist nicht mehr als einen Index einer Tabelle nutzen (ob eine Index-Merge
funktoniert ist schwierig).

Mein Vorschlag also:

Ein neuer Index, der hilft beim Zugriff über die User_id plus Primary Key für den Join.

CREATE INDEX sx_tabelle_s_01 ON tabelle_s (user_id, id);

Der bestehende Index "user_id" kann entfernt werden.

Dann mal ausprobieren und per EXPLAIN prüfen. Wenn es nichts hilft, können die Index ja wieder hergestellt werden.

Genaueres ist mir ohne genaue Kenntnisse der Tabellen nicht möglich.

Probier es einfach mal aus, ob es hilft.

Grüße
Thomas

[rftk]

Vorsicht beim Erstellen von Indizes

Vorsicht beim erstellen von Indizes, das kostet nämlich Zeit. Und sperrt je nach Speicher-Engine die Tabelle. Bei MyISAM ist das nämlich so.

Überdies hat ein zweispaltiger Index keinen Vorteil bei Joins. Aber wenn es sich um eine Abdeckende Abfrage handelt. Was durch "Using index" unter Extras in der Explain-Ausgabe angezeigt wird.

Ein Abdecken liegt z.B. vor, wenn Du nach user_id abfragst (Where Klausel) und nach id (PRIMARY) sortierst.

[akretschmer]

Vorsicht beim erstellen von Indizes, das kostet nämlich Zeit. Und sperrt je nach Speicher-Engine die Tabelle. Bei MyISAM ist das nämlich so.

IIRC auch bei InnoDB. Man möge mich berichtigen.

In PG gibt es CREATE INDEX CONCURRENTLY, was exakt dieses Problem löst.


Btw.: was soll eigentlich die Leichenfledderei hier?

Andreas

[Gast]

Wenn die Profile so ausufernd werden, ist das doch für eine NoSQL-DB prädestiniert..