Sebastian Kraus — 21.04.2012
In den letzten Wochen und Monaten sind uns einige Projekte untergekommen, die unter abenteuerlichen Performance-Problemen aufgrund "zu großer Datenmengen" in der MySQL-Datenbank litten. Wenn man die Tabellen richtig plant und konfiguriert, dann hat man mit MySQL aber wirklich seltenst Probleme mit der Geschwindigkeit. In einer mehrteiligen Serie von Blog-Artikeln möchten wir einige Schritte aufzeigen, wie man solche Probleme vermeiden bzw. beheben kann.
Der MySQL Query Cache speichert die SQL-Abfragen und die ermittelten Ergebnisse im Arbeitsspeicher. Kommt ein identisches SQL-Statement, wird die Abfrage nicht erneut berechnet, sondern aus dem Query-Cache beantwortet. Erst wenn sich die Inhalte der Tabelle ändern (UPDATE, INSERT, DELETE) wird dieses gecachte Element gelöscht, oder wenn der zugewiesene Query-Cache vollgelaufen ist und die Abfrage am längsten nicht mehr abgerufen wurde.
Obwohl der Arbeitsspeicher die teuerste Ressource des Server ist, teilen wir dem Query-Cache im Normalfall 50-75% des Gesamtarbeitsspeichers zu. Der Query-Cache hat allerdings einen gewissen Overhead, der die Performance dann negativ beeinflusst, wenn auf eine Tabelle überwiegend Write-Operationen durchgeführt werden. Leider kann man den Query-Cache nicht auf Datenbank-Ebene konfigurieren, so dass eine Datenbank mit einer Write-Mostly-Tabelle bereits die Performance-Gewinne aller anderen Datenbanken vernichten kann.
Der Overhead lässt sich allerdings vermeiden, wenn alle abfragenden Queries auf die Write-Mostly-Tabelle das SQL-Schlüsselwort "SQL_NO_CACHE" verwenden, also z. B.:
SELECT SQL_NO_CACHE a, b, c FROM x WHERE ...
In Situationen, in denen große Mengen an Daten zu einem frei wählbaren Zeitpunkt verarbeitet werden müssen (Cron-Jobs), lohnt es sich, die Daten aus der Haupt-Tabelle in eine MEMORY-Tabelle zu kopieren und mit dieser zu arbeiten. Auch das kostet extrem viel RAM, beschleunigt den Vorgang aber um den Faktor 100-1000 nach dem Kopieren.
Darauf zu achten ist, dass in der globalen MySQL-Konfiguration die maximale Größe für MEMORY-Tabellen sinnvollerweise limitiert ist. Standardmäßig ist das 1GB. Will man mehr Daten in die Tabelle kopieren, kommt die Fehlermeldung
THE TABLE tablename IS FULL
... was man unbedingt abfangen sollte, da man sonst mit unvollständigen Daten weiterarbeiten würde. Die maximale Größe für MEMORY-Tabellen lässt sich aber nach oben korrigieren, wobei darauf zu achten ist, dass sich die Größe aller (voll ausgereizten) MEMORY-Tabellen den verfügbaren Arbeitsspeicher mit dem QUERY-Cache teilen und der Server somit schnell ins SWAPpen gerät, was natürlich die Performance erbärmlich verlangsamt.