MySQL Performance Tuning: Features / Learings
MySQL 5.1 Stable, Wann?
6. Dezember 2008, dieses Blog läuft jetzt aber schon auf 5.1
Kickfire, Magie, technische Raffinesse, oder Manpower?
Der "SQL-Chip" macht hier und da etwas Caching, hinter der Maschine steckt aber eigentlich nur brachiale Gewalt. Ein System mit ähnlicher Hardware (2 Quad Xeons, 16 GB Ram, SAS Platten, ...) wird ähnliche Benchmarks erreichen
Prepared Statements vs. Query Cache
Seit 5.1.17 wandern auch Prepared Statements in den Query-Cache. Diese Limitierung war besonders ärgerlich, da Prepared-Statements einem eine Menge Ärger ersparen können (Stichwort SQL-Injection), aber dadurch, dass die Ergebnisse nicht im Query-Cache landen, machen diese die Applikation langsamer im Vergleich zu "normalen" Queries.
"The query cache is not used for server-side prepared statements before MySQL 5.1.17. If you are using server-side prepared statements, consider that these statements will not be satisfied by the query cache." (Quelle)
Transaktion pro Sekunde
Jede abgeschlossene Transaktion (COMMIT) muss auf die Platte raus schreiben, dass bedeutet, pro COMMIT einmal write auf die Platte. Die mittlere Zugriffszeit ist eine Plattenumdrehung also lautet die Formel rein von der Spindel in der Platte: 1/(RPM/60) = Zugriffszeit. Bsp. 15.000 rpm: 1/(15000/60) = 0,004 s = 4ms
Das braucht schon alleine die Spindel / der Schreibkopf in der Platte um an der richtigen Position zu sein (im Schnitt).
Das bedeutet, dass pro Sekunde 250 Operationen auf die Platte durchgehen, mehr schafft die nicht (1/0,004 = 250). Alles mit mehr als 250 Transaktionen pro Sekunde wird also ein großer Spaß, vor allem: Das ist ein theoretischer Wert. Das Betriebssystem muss ja auch noch was machen.
Umkreissuche
Du bist an Punkt X und suchst alle Objekte im Umkreis von Y Kilometer. Ob ein Punkt innerhalb deines Umkreises ist ließe sich ganz einfach mit < a href="http://de.wikipedia.org/wiki/Satz_des_Pythagoras">Satz des Pythagoras berechnen. Problem: Du musst für jeden Punkt in der Datenbank eine komplexe Berechnung ausführen. Beispiel: X = (100/200) y = 30. Für jeden Punkt, der innerhalb des Umkreises ist gilt:
(100-x)^2 + (200-y)^2 <= 30^2Das Problem hierbei: Ein Full-Table Scan. Was tun? Vorselektieren. Wie? Ein Viereck aufspannen:
x BETWEEN 100-30 AND 100+30Das lässt sich auch noch mit einem Index beschleunigen und ist zumindest ein Näherungswert. Dann per AND einfach noch die Bedinung von oben dran flanschen:
AND y BETWEEN 200-30 AND 200+30
SELECT foo
FROM bar
WHERE x BETWEEN 100-30 AND 100+30
AND y BETWEEN 200-30 AND 200+30
AND POW(100-x,2)+POW(200-y,2) <= 30^2
MySQL Clipping-Feature Abschlaten
Seit MySQL 5.1 gibt es die Möglichkeit, das Clipping-Feature abzuschalten. Clipping ist dafür verantwortlich, dass man in ein TinyInt Feld zwar 300 reinschreibt, dann aber nur 255 drin steht, oder dass man einen String mit der Länge von z.B. 12 in ein CHAR(10) Feld speichern kann, dann aber halt einfach nur 10 Zeichen drin stehen. Kurz gesagt: Clipping ist Scheiße. Und das lässt sich abschalten:
SET GLOBAL SQL_MODE=STRICT_ALL_TABLES;Dann macht MySQL kein Clipping mehr, sondern quitiert die Operation mit einer Fehlermeldung.
MySQL Doku
InnoDB Threads
InnoDB arbeitet intern mit mehrere Threads. Allerdings ist die Standardeinstellung so gewählt, dass nur maximal acht Threads gleichzeitig arbeiten können, alle anderen warten. Um das zu ändern muss man die Variable "innodb_thread_concurrency" einstellen. Faustformel für einen guten Wert: Anzahl_Cores * Anzahl_Platten * 2
Wobei Anzahl Platten die Platten sind, auf welchen das InnoDB-Datafile liegt (Stripping).