Ich habe bei einer Datenbank größere Tabellen mit einer halben bis Million Datensätze. Wenn ich mittels ORDER by Datensätze auslese funktioniert das bei Spalten auf denen ein Index ist normal schnell. Sobald ich aber Spalten sortiere die keinen Index haben, dauert es meistens 3 Sekunden Datensätze auszulesen. Die zu sortierenden Spalten sind alle vom Typ Mediumint oder Float, einen Index kann ich darauf meines Wissens nach nicht setzen, da die darin enthaltenen Werte mehrmals vorkommen. Jetzt meine Frage: Wie kann ich meine Tabellen bzw. meine Queries optimieren, damit die Abfragen schneller laufen und mein vServer nicht so belastet wird? Denn im Moment scannt MySQL die ganze Tabelle bei jedem dieser Queries, was außer 3 Sekunden Wartezeit auch zu einer enormen Auslastung führt. Ich habe bereits versucht die MySQL Konfiguration ein wenig anzupassen, brachte aber leider wenig - ich glaube das Problem muss ich direkt bei den Tabellen oder Queries beheben. Was kann ich machen?
Ich habe eigentlich bei meiner PHP-DB-Klasse eine Cache Funktion integriert, aber die ist Zusammengebrochen bei dieser Datenmenge, da waren letztendlich zu viele Festplattenzugriffe, die alles nur langsamer machten. Aber vielleicht gäbe es ja eine wirklich Serverseitige Lösung und nicht nur per Script
Vielleicht kann man wirklich noch was an der MySQL optimieren, aber dazu kenne ich mich leider zu wenig mit MySQL aus
Aber ich bin mir sicher dass wir hier einige MySQL Fachmänner haben
Zusätzlich dazu musste ich allerdings einige andere Dinge noch umsetzen, die ich euch nicht vorenthalten möchte:
Die große Tabelle hatte mehr als 30 Spalten, wodurch ich schon mal gar nicht überall einen Index setzen konnte, wo es nötig ist, da man nur auf 16 Spalten einen Index setzen kann. Da diese Struktur sowie Schwachsinn ist und nicht gut durchdacht war, teilte ich diese Tabelle in eine weitere auf. Die ursprüngliche Tabelle schrumpfte damit gleich einmal, die neue Tabelle hat jetzt zwar 3-mal soviel Einträge wie die andere, aber sie ich viel einfacher zu verwalten, jedenfalls aus der Sicht von MySQL.
Ich habe einmal deinen Rat befolgt und einen Index auf die jeweiligen Spalten erstellt. Dabei habe ich auch gleich noch ein paar andere Indexe angepasst und optimiert.
Nun musste ich meine Queries abändern. Bei allen Queries gibt es jetzt zwar einen JOIN mehr, letztendlich rechnet sich das aber.
Da das komplette Caching mittels PHP zum Zusammenbruch führte, aktivierte ich den Cache nur für Abfragen, die es nicht in unendlich vielen Kombinationen gibt, also nur für einen Teil davon, die fast immer statisch sind. Diese Queries, die vllt 1/3 davon ausmachen, werden nun also mit PHP gecached.
Zusätzlich habe ich an meiner MySQL Konfiguration geschraubt: read_rnd_buffer_size habe ich von 256K auf 512K erhöht. sort_buffer_size von 2M auf 8M. das ist zwar für einen vServer mit 512 MB fixem Ram vielleicht übertrieben, bisher gab es allerdings noch keine Probleme und der Ram ist sowieso wenig ausgelastet. Falls es damit Probleme gibt, kann ich es immer noch runtersetzen.
Und zu guter Letzt habe ich noch die Anzahl der Elemente je Seite verdoppelt, denn so gibt es zum einen weniger verfügbare Seiten und zum anderen weniger DB Abfragen.
So, wenn man nun meine Seiten aufruft könnte man zuerst einen Schock bekommen, denn plötzlich dauern diese Queries, die jetzt ja noch mehr Joins haben gleich 4-5 Sekunden anstatt wie vorher 2-3 Sekunden. Aber: Nach einiger Zeit und einigen Aufrufen scheint sich MySQL darauf einzustellen. MySQL braucht dann nicht einmal mehr 0,2 Sekunden für alle Queries. Es gibt zwar kurzfristig eine höhere Last als vorher, aber im Endeffekt eine niedrigere. Der Mix scheint es also auszumachen, denn die Last sowie Ram sehen derzeit schöner aus als gestern
Und falls ich mich dabei komplett verkalkuliert habe, ich habe ja ein Backup
Falls es noch Vorschläge/Tipps zu meinen Änderungen gibt, lasst sie mich bitte hören, vllt. kann man dabei ja noch mehr verbessern
