Thomas Gericke

Es gibt Datenbanken, die stehen unter grosser Last, weil die Queries sehr komplex sind. Andere Datenbanken sind immens gross. Eine meiner Datenbanken hat sehr viele Zugriffe, sowohl lesend als auch schreibend. Heute wurde der Server an die Lastgrenze getrieben, was mich dazu zwang, Optimierungen vorzunehmen.

1. INSERT DELAYED und UPDATE LOW_PRIORITY

Bisher habe ich mich immer davon fern gehalten, diese Funktionen zu nutzen, einfach weil ich keine Veranlassung dazu sah. Warum schreibende Zugriffe verzögern, wenn sie doch auch unmittelbar vollzogen werden können? Die Tatsache, dass mehrere hunderte gleichzeitiger Zugriffe sich gegenseitig behinderten, brachte mich dazu, meine Denkweise zu überdenken.

Ich habe zwei schreibende SQL-Statements kurzerhand von “INSERT INTO TABLE...” in “INSERT DELAYED INTO TABLE...” bzw. “UPDATE TABLE...” in “UPDATE LOW_PRIORITY TABLE...” umgeschrieben.

Natürlich gibt es Nachteile. Erstens sind die geschriebenen Daten nicht unmittelbar abrufbar, zweitens gehen diese unweigerlich verloren, sofern der MySQL-Server unsauber beendet wird, bevor die Queries auf Platte geschrieben werden konnten.

Die Vorteile überwiegen jedoch, denn ich konnte damit die Serverlast unglaublich reduzieren. Es sollte allerdings wohl überlegt werden, welche Statements man verzögert schreiben lässt.

2. Die sinnvolle Verwendung von Indizes

Eine weitere Optimierung, zu der ich mich gezwungen sah, war der Einsatz von Indizes in MySQL-Tabellen. Dauert die Verarbeitung eines SQL-Queries sehr lang, weil unheimlich viele Daten analysiert werden müssen, so bietet sich der Einsatz eines Index’ durchaus an. Beispiel:

SELECT COUNT(wa_sessions.ID) AS Sessions
FROM wa_sessions, wa_sites
WHERE wa_sites.site_name = 'foo'
AND wa_sites.ID = wa_sessions.site_id
AND wa_sessions.begin
BETWEEN NOW() - INTERVAL 5 MINUTE
AND NOW() - INTERVAL 0 MINUTE;
+----------+
| Sessions |
+----------+
|       20 |
+----------+

1 row in set (5.00 sec)

Im obigen Beispiel dauert die Abfrage exakt 5 Sekunden, was für eine Webanwendung unbrauchbar lang ist. Mit MySQL-Boardmitteln kann man sich anschauen, warum die Abfrage so lange dauert:

EXPLAIN SELECT COUNT(wa_sessions.ID) AS Sessions
FROM wa_sessions, wa_sites
WHERE wa_sites.site_name = 'foo'
AND wa_sites.ID = wa_sessions.site_id
AND wa_sessions.begin
BETWEEN NOW() - INTERVAL 5 MINUTE
AND NOW() - INTERVAL 0 MINUTE\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wa_sites
type: ref
possible_keys: PRIMARY,site_sites
key: site_sites
key_len: 35
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: wa_sessions
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 831752
Extra: Using where
2 rows in set (0.00 sec)

Man erkennt, dass zwei Tabellen durchsucht werden. Für die erste Tabelle wird ein Key verwendet, für die zweite Tabelle jedoch nicht. Aus diesem Grund liefert die Abfrage der ersten Tabelle auch nur genau ein Ergebnis zurück, während in der zweiten Tabelle noch 831752 Ergebnisse auf den WHERE-Teil hin überprüft werden müssen. Das dauert natürlich eine Weile.

Besser geht es mit dem Einsatz eines Indizes im entsprechenden Tabellen-Feld, welches vom WHERE-Teil abgefragt wird:

CREATE INDEX session_begin ON wa_sessions (begin(18));
Query OK, 831761 rows affected (34.52 sec)

Das gleiche EXPLAIN SELECT Statement von gerade eben zeigt, dass nun auch in der zweiten Tabelle ein Key verwendet wird:

EXPLAIN SELECT COUNT(wa_sessions.ID) AS Sessions
FROM wa_sessions, wa_sites
WHERE wa_sites.site_name = 'foo'
AND wa_sites.ID = wa_sessions.site_id
AND wa_sessions.begin
BETWEEN NOW() - INTERVAL 5 MINUTE
AND NOW() - INTERVAL 0 MINUTE \G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wa_sites
type: ref
possible_keys: PRIMARY,site_sites
key: site_sites
key_len: 35
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: wa_sessions
type: range
possible_keys: session_begin
key: session_begin
key_len: 21
ref: NULL
rows: 55
Extra: Using where
2 rows in set (0.00 sec)

Sofort ist erkennbar, dass in der zweiten Tabelle nur noch 55 Ergebnisse mittels der WHERE-Abfrage analysiert werden müssen. Die meisten der weit über 800.000 Ergebnisse in der zweiten Tabelle wurden durch das reine Vorhandensein des Indizes bereits ausgeschlossen. Und das SQL-Statement ist damit volle 5 Sekunden schneller:

SELECT COUNT(wa_sessions.ID) AS Sessions
FROM wa_sessions, wa_sites
WHERE wa_sites.site_name = 'foo'
AND wa_sites.ID = wa_sessions.site_id
AND wa_sessions.begin
BETWEEN NOW() - INTERVAL 5 MINUTE
AND NOW() - INTERVAL 0 MINUTE;

+----------+
| Sessions |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

Auch bei Indizes ist Vorsicht geboten. Zu grosse oder falsche Indizes können die Datenbank unter Umständen nicht nur unnötig gross, sondern auch langsamer machen.

Erläuterungen finden sich in der MySQL-Dokumentation auf http://dev.mysql.com/doc/.

Hat’s gefallen?

Wenn der Artikel gefallen hat oder hilfreich war, bitte folgende Funktionen benutzen: