Geschwindigkeitsoptimierung bei sehr großen Tabellen

[Fabi_K]

Moinsen,

in letzter Zeit habe ich beunruhigend regelmäßig mit Tabellen zu tun, die um die 100.000 bis 500.000 und teilweise sogar mehr Datensätze enthalten.

DMBS ist bisher immer MySQL gewesen.

MySQL arbeitet relativ zügig, wenn ich einen einfachen SELECT auf eine der großen Tabellen absetze. Gebrauche ich jedoch JOINS auf zwei oder mehr (Gott bewahre) Tabellen kommt es gut und gerne vor, daß MySQL so zehn bis zwölf Minuten nur am ackern ist.

Ich wollte jetzt mal wissen, wie man vorgehen kann, die Geschwindigkeit zu verbessern.

Ein wenig habe ich mir darüber auch bereits den Kopf zerbrochen. Eine Möglichkeit wäre den JOIN Programmatisch zu lösen. Also beiden Tabellen einzeln (SELECT * FROM x und SELECT * FROM y) einlesen und dann in eigenen Datensätzen ablegen.

Problem hierbei ist, daß der Arbeitsspeicher des Datenbank-Clients dicht geschrieben wird. Und wenn eine Sprache wie PHP zum Einsatz kommt (leider viel zu häufig für meine Geschmack, aber so is es nu ma), wird der solange das PHP-Script läuft auch nicht mehr frei gegeben.

Eine weiter Möglichkeit sehe ich im Cachen der Daten. MySQL macht das zwar, hilft aber auch nicht wirklich. Ich dachte eher an einen weiteren Prozess, der die Tabellen im Arbeitsspeicher hält und bei Änderungen in der Datenbank die Erforderlichen Daten nachlädt. Gibt es da evtl. eine Möglichkeit für MySQL?
Testweise wollte ich einen SOAP-Webservice schreiben, der diesen Zweck erfüllt. Wenn es aber schon eine Lösung gibt, kann ich mir diese Zeit sparen.

Und sonst ein anderes DBMS, welches gut mit großen Datenbeständen umgehen kann. Gibt's da was? Soweit ich informiert bin, soll ja MySQL schon sehr schnell sein. Nicht umsonst setzt Google auf MySQL.

So. Bin für alle Vorschläge offen.


Gruß Fabian

 

[dms]

Das hast Du Dir schon zu Gemüte geführt?

Problem hierbei ist, daß der Arbeitsspeicher des Datenbank-Clients dicht geschrieben wird. Und wenn eine Sprache wie PHP zum Einsatz kommt (leider viel zu häufig für meine Geschmack, aber so is es nu ma), wird der solange das PHP-Script läuft auch nicht mehr frei gegeben.

Dafür gibt es extra die Funktion mysql_free_result().

Eine weiter Möglichkeit sehe ich im Cachen der Daten. MySQL macht das zwar, hilft aber auch nicht wirklich. Ich dachte eher an einen weiteren Prozess, der die Tabellen im Arbeitsspeicher hält und bei Änderungen in der Datenbank die Erforderlichen Daten nachlädt. Gibt es da evtl. eine Möglichkeit für MySQL?
Testweise wollte ich einen SOAP-Webservice schreiben, der diesen Zweck erfüllt. Wenn es aber schon eine Lösung gibt, kann ich mir diese Zeit sparen.

Was vorgefertigtes kenne ich da nicht. Aber etwas ganz ähnliches habe ich mal gemacht. Mit PHP habe ich mir eine kleine Server-Anwendung geschrieben die auf einem bestimmten Port lauscht. Beim Starten wurden alle Werte aus der DB ausgelesen. Der Client fragte diese dann nur über einen Socket ab. Dabei ging es allerdings nicht um so viele Daten. Bei mir war nur das Problem dass die Daten ziemlich oft gebraucht wurden. Somit war es sinnvoller diese gleich im RAM zu behalten als jede Sekunde neu aus der DB zu holen. Kommt natürlich auch darauf an was für Daten das sind, aber wenn man 500.000 Datensätze immer im RAM behält sollte der Hauptspeicher schon ausreichend gross sein.

 

[falkgottschalk]

Dickeren Server mit mehr RAM, RAM und nochmals RAM.
Und mal bei phpadmin schauen, was so in den Statistiken steht, dort sind auch oft hilfreiche Tips drin.

 

[eXcuvator]

via EXPLAIN die Joins analysieren und schauen, ob er auch überall Indizes nutzt. Eigentlich sollten vernünftige Joins (auch in der Größenordnung) nicht einen solchen (zeitlichen) Unterschied zu Selects machen.

 

[Fabi_K]

Das hast Du Dir schon zu Gemüte geführt?

Ne, kannte ich noch nicht. Wenn ich den Text richtig verstanden habe, dann nimmt MySQL bereits selber einige Optimierungen vor und stellt fest, in welcher Reihenfolge die Tabellen zu Joinen sind. Oder habe ich das falsch verstanden?

Etwas unklar ist mir folgender Satz:

All standard join optimizations are performed, with the exception that a table is always read after all tables on which it depends.

Wie stellt MySQL fest, welche Tabelle zuletzt gelesen werden soll (also die Tabelle die von den andern abhängig ist, sprich die Foreign-Keys enthält).
Indem es das SQL-Statement analysiert, oder indem es aus der Tabellendefinition ausließt, wie die Foreign-Keys sind. Ist es letztres könnte das die schlechte Performance erklären, da Foreign Keys AFAIK gar nicht gesetzt sind.

Dafür gibt es extra die Funktion mysql_free_result().

Kannte ich ebenfalls noch nicht. Danke.

via EXPLAIN die Joins analysieren und schauen, ob er auch überall Indizes nutzt. Eigentlich sollten vernünftige Joins (auch in der Größenordnung) nicht einen solchen (zeitlichen) Unterschied zu Selects machen.

EXPLAIN habe ich noch nicht benutzt, aber die Indizes sind auf allen Primary Keys.


Gruß Fabian

 

[falkgottschalk]

Index auf Primärkey nutzt nur dann etwas, wenn auch NUR über den zugegriffen wird beim SELECT .. .where ..
Wenn man andere Felder nimmt, die nicht indiziert sind, ist im Regelfall ein full-table-scan erforderlich und der ist für Performance tödlich.

 

[eXcuvator]

Nicht nur andere Felder, sondern auch Felderkombinationen:

Beispiel: TABLE user fields int primary (userid), int (locationid)
index1 auf userid und index2 auf locationid.
Select * where userid = $whatever -> toll
Select * where locationid = $whatever -> toll
Select * where locationid = $w1 AND userid = $w2 -> FTS, da diese kombi nicht indiziert ist

 

[falkgottschalk]

ja, klar. Und damit wären wir beim Beweis der Existenzberechtigung von EXPLAIN angelangt. Ab und an sollte man mal schauen, was denn die Datenbank da so treibt.

 

[master_p]

Also einen MySQL-Server, der 10-12 Minuten für ein SELECT-Statement braucht, den habe ich noch nicht gesehen. Selbst bei einer unsortierten, nicht indizierten Tabelle mit 500.000 Datensätzen. Da hat aber entweder der Server einen ganz schönen Hau auf der Festplatte (wo er ja ohne Index und ohne Cache die Daten herholt) oder aber er hat nur 256MB RAM und der ist dauerhaft belegt - oder aber es ist ein 486 mit 33MHz...

Ordentliche Indizes sollten schon gesetzt werden. Ein Primary-Key ist IMMER ein Index. Egal ob per Hand gesetzt oder nicht. Wenn man ihn per Hand nochmal setzt, ist das sogar eher schlechter für die Performance.

So grob über'n Daumen gesagt, braucht man für alle Felder einen Index, über die ein WHERE-Statement geht. Sprich "SELECT f1, f2, f3 FROM table WHERE f2=1 AND f3=3". Hier wäre ein Index auf f2 und f3 sinnvoll. Wenn man weiß, dass die Tabelle IMMER oder aber sehr häufig auf f2 UND f3 abgefragt wird, dann sollte man diesen Index sogar zusammenlegen.

Eine solch große Anzahl an Datensätzen ohne Index ist natürlich tödlich. Wundere Dich auch nicht, wenn jetzt beim setzen des Index der MySQL-Server mal 10 Minuten "rödelt". Aber generell solltest Du entweder den Server mal überprüfen oder/und Dich mit Indizes beschäftigen. Denn die bringen quasi kostenlos Performance (gut, sie benötigen RAM).

 

[Fabi_K]

Moin,

daß Primary-Keys automatisch einen Index bekommen hatte ich mir schon fast gedacht. Find's aber gut es ausdrücklich noch mal zu lesen.

Zu den Indizes wollte ich noch was hinzufügen. Ein Index ist ja nichts anderes als ein Binärer Baum. Die Daten werden anhand eines Kriteriums (in der Regel größer oder kleiner als ...) sortiert abgespeichert. Bei Zahlen funktioniert das wunderbar. Bei Text auch, allerdings ist da zu beachten, daß Texte zeichenweise miteinander vergleichen werden. Beginnend beim ersten Zeichen wird ermittelt, ob das Zeichen größer oder kleiner als das Zeichen im zu vergleichenden Text ist.

Lange Rede, kurzer Sinn. Was ich damit sagen will ist, daß Indizes auf Textfelder nur dann Sinn machen, wenn man in der WHERE-Clause Texte vom ersten Zeichen an miteinander vergleicht. Also nur wenn man Statements wie:
"WHERE x LIKE 'needle'" oder "WHERE x LIKE 'needle%'" hat. Bei "WHERE x LIKE '%needle'" bringt ein Index nichts.

Und um meine andere Frage nicht in Vergessenheit geraten zu lassen. Wie sieht es denn mit der JOIN-Optimierung von MySQL bei Foreigen-Keys aus?
Muss dieser gesetzt sein, oder ist es Wurscht.
Bisher bin ich davon ausgegangen, daß Foreign-Keys bei MyISAM-Tabellen rein gar nichts bringen, da ein Foreign-Key ja für die Referenzielle Integrität nötig ist und MyISAM Tabellen sich einen Dreck um die Referenzielle Integrität kümmern. Wenn MySQL jetzt aber anhand des Foreign-Keys Joins optimieren kann, dann ist so ein Foreign-Key ja doch nicht Wurscht.

Hat da jemand ein paar Infos?


Gruß Fabian

 

[dms]

Ich habe gerade ein ganz ähnliches Problem und kann es nicht nachvollziehen.

Zwei Tabellen:

CREATE TABLE `manufacturers` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `zip` varchar(30) NOT NULL default '',
  `city` varchar(150) NOT NULL default '',
  `street` varchar(255) NOT NULL default '',
  `country` int(11) unsigned NOT NULL default '0',
  `contactperson` varchar(150) NOT NULL default '',
  `phone` varchar(100) NOT NULL default '',
  `fax` varchar(100) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `url` mediumtext NOT NULL,
  `signowner` tinyint(1) NOT NULL default '0',
  `signuser` tinyint(1) NOT NULL default '0',
  `login` varchar(50) NOT NULL default '',
  `password` varchar(8) NOT NULL default '',
  `scode` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3209 ;

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `manufacturer` bigint(20) unsigned NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `description_de` mediumtext NOT NULL,
  `description_en` mediumtext NOT NULL,
  `contract` varchar(25) NOT NULL default '',
  `validtill` date NOT NULL default '0000-00-00',
  `picture` mediumblob NOT NULL,
  `pictype` varchar(20) NOT NULL default '',
  `brand` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `manufacturer` (`manufacturer`)
) TYPE=MyISAM AUTO_INCREMENT=7751 ;

manufacturers enthält 1681 Einträge und ein komplettes SELECT darüber dauert 0.0221 Sekunden.

products enthält 3514 Einträge und ein komplettes SELECT darüber dauert 0.1655 Sekunden.

Soweit, so gut. Bei einem ganz simplen LEFT JOIN geht die Datenbank jedoch in die Knie:

SELECT manufacturers.name AS name,
       COUNT(*) AS anzahl_produkte
FROM manufacturers
LEFT JOIN products ON (products.manufacturer=manufacturers.id)
GROUP BY manufacturers.id

Dieser kleine Query dauert mal eben 23 bis 26 Sekunden!

Woran kann das liegen? Ein viel komplexerer Query der (unter anderem) die gleichen beiden Tabellen verwendet rast blitzschnell durch: https://www.macuser.de/forum/showpost.php?p=3865959&postcount=12 (Tabellen- und Spaltennamen hatte ich dort der Übersichtlichkeit halber nur umbenannt)

Ist in dem Query ein grober Fehler der mir einfach nicht auffallen will?

 

[master_p]

Naja was glaubst Du denn, was bei einem COUNT passiert? Und dann über "*". Sowas macht man auch über "COUNT(id)" oder so. Denn hier muss er die komplette Tabelle einlesen und zwar bei JEDEM Datensatz!

Sowas solltest Du Dir mal per Explain in phpMyAdmin (oder wo auch immer) erklären lassen. Versuch mal über die id zu "counten". Das sollte es schon deutlich schneller machen.

 

[dms]

Ursprünglich hatte ich da die id gezählt. Hab dann experimentiert um zu sehen woran es liegt. Letztenendes kann ich das COUNT komplett rausnehmen, an den 23-26 Sekunden ändert sich dadurch nix. Das COUNT hab ich dann kurzerhand wieder reingesetzt damit klar wird warum ich überhaupt einen JOIN brauche.

 

[gishmo]

Schon mal Frontbase angetestet?

 

[AxlF]

Moin,

Zu den Indizes wollte ich noch was hinzufügen. Ein Index ist ja nichts anderes als ein Binärer Baum. Die Daten werden anhand eines Kriteriums (in der Regel größer oder kleiner als ...) sortiert abgespeichert. Bei Zahlen funktioniert das wunderbar. Bei Text auch, allerdings ist da zu beachten, daß Texte zeichenweise miteinander vergleichen werden. Beginnend beim ersten Zeichen wird ermittelt, ob das Zeichen größer oder kleiner als das Zeichen im zu vergleichenden Text ist.

Gruß Fabian

Ich denke eigentlich auch dass gute Indizes deine Schlüssel zum erfolg sein werden.

Selbst hab ich es auch nicht geglaubt und das immer überbewertet. Aber kürzlich hatte ich eine Datenbank mit mehr als 1.000.000 Einträge, und benutze auch JOIN Statements. Das Ergebnis: die Abfragen dauerten um die 5-10 Sekunden. Dann bin ich in meiner Verzweiflung auf die Idee mit den Indizes gekommen, und bei den Abfragen hat sich das komma verschoben. Sicherlich 10 mal schneller als vorher.

Gruß,
Alexander

 

[Fabi_K]

Hi folks,

habe des Problems Lösung gefunden.
Als hier der ein oder andere davon schrieb, daß bei JOINS über mehrere Tabellen die auch so um die 500.000 Einträge, oder mehr, hatten, längst nicht mehr als ein paar Sekunden benötigte wurde ich schon ganz neidisch ;-)

Wieso der Server mit dem ich arbeite, jedoch mehrere Minuten benötigte, konnte ich mir jetzt erst recht nicht erklären. Schließlich hatte ich ja jetzt grobe Vergleichswerte.

An fehlenden Indizes, konnte es nicht liegen (auch wenn das hier so häufig als Grund genannt wurde), da Indizes auf allen Primary-Keys gesetzt waren. Für andere Felder waren keine nötig, da auf anderen Feldern nicht gejoint wurde.

Letzte Woche benötigte ich allerdings einen JOIN auf die gleiche Tabelle über ein normales Datenfeld. Der MySQL-Server war selbst nach 30 Minuten noch nicht fertig. Mit EXPLAIN das Statement Analysiert und der JOIN ging über ALL, also die Performance schlechteste JOIN-Methode. Wollte deswegen einen Index auf das Feld setzten, doch MySQL fing auf einmal an zu meckern.
Die Tabelle war kaputt! Kurzerhand die Tabelle Repariert und volá, der JOIN war in zwei bis drei Sekunden durch.

Was mich jetzt allerdings ärgert ist, daß MySQL mir nicht eine Warnung ausgegeben hat. Logdateien habe ich jetzt nicht angeguckt... wann macht man das auch schon mal. Weis deswegen auch nicht, ob dort überhaupt ein Hinweis auf die kaputte Tabelle vorhanden ist.

Nächte Frage, was kann so eine MySQL-Tabelle beschädigen? Passiert das bei MySQL ganz gerne mal, oder nur im Ausnahmefall? AFAIK wird der MySQL-Server-Prozess ab und zu mal gekillt.
Das muss machmal sein. Über die Gründe lässt sich Streiten.

Da setzte ich mein Hoffnung auf den neuen Falcon-Tabellen-Typ der in MySQL 6 Einzug findet.
http://dev.mysql.com/doc/refman/6.0/en/se-falcon-features.html
Besonders in den letzte Punkt "Implicit savepoints ensure data integrity during transactions."


Gruß Fabian

 

[dms]

Danke für den Tip. Werde das morgen mal testen. Ist möglicherweise ja auch bei mir der Fall. Anders kann ich mir das auch nicht erklären.

 

[QBFinest]

Wenn du soviel JOINS benutzt solltest du vielleicht mal drüber nachdenken auf ein mächtigers DBS umzusteigen. Haste du die DB auch in die BCNF bzw. wenigstens in die 3.NF gebracht?

 

[Fabi_K]

Die DB ist nicht von mir. Und von den Normalformen hat der ursprüngliche Entwickler anscheinend noch nie was gehört. Auch wenn dieser Informatik Student war.

Zur 3. Normalform wollte ich dann auch noch mal was sagen. So wie ich die Erfahrung gemacht habe, mach die 3. Normalform nicht immer Sinn. Häufig ist eine Enumeration besser, da einfacher zu Programmieren und es ist auch schneller, denke ich. Hab's nicht getestet.

Gruß Fabian

PS: Hab eben noch mal die LOG-Files von MySQL durchstöbert. Hab keine einzige Meldung wegen dem Tabellencrash gefunden. Allerdings waren die Logfiles auch leer. Hab vielleicht am falschen Ort geguckt (/var/log/mysql.log und /var/log/mysql/*)

Naja, was soll's.

 

[elybis]

Naja was glaubst Du denn, was bei einem COUNT passiert? Und dann über "*". Sowas macht man auch über "COUNT(id)" oder so. Denn hier muss er die komplette Tabelle einlesen und zwar bei JEDEM Datensatz!

COUNT(*), COUNT(1) und COUNT(id) macht von der Geschwindigkeit her keinen Unterschied, da der Optimizer bei einem COUNT(*) oder COUNT(1) auch den vorhandenen Index auf dem Primärschlüssel benutzen würde, er lest also nicht die ganze Tabelle, sondern holt sich die Information über die Anzahl aus dem Index.

Haste du die DB auch in die BCNF bzw. wenigstens in die 3.NF gebracht?

Die Normalformen sind nicht immer das Allheilmittel, grade bei grossen Datawarehouse-Anwendungen mit hohem Auswertebedarf (OLAP) empfiehlt es sich aus Performancegründen auch mal Daten redundant zu halten, im Gegensatz zu OLTP-Anwendungen, wo die Normalformen möglichst genau beachtet werden sollten.

Die Tabelle war kaputt!

Wenn du Probleme mit korrupten Tabellen hast, empfehle ich dir mysqlcheck als Cronjob laufen zu lassen (http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html).

mfg