Ein Paar Wiener bitte, oder: zwei tuning Tipps für MySQL

Über MySQL Tuning gibt es Beiträge wie Sand am Meer. Viele behandeln die gleichen Tipps und sind oft auch für verschiedene Software gültig. Ich habe hier zwei Tipps die für mich recht viel Sinn machen und auch – relativ – einfach umzusetzen sind. Dazu kommt, dass der Effekt bei beiden Tipps recht groß ist ;-)

“tmpfs” für die Datenbank

Ich hatte mir schon öfter mal Gedanken darum gemacht, ob es denn nicht möglich ist eine Datenbank in den RAM zu legen. Das Problem – was auch mit meinem Tipp nicht gelöst ist – ist dabei, dass die Daten bei einem Neustart des Systems oder auch des Dienstes verloren gehen. Dafür hat man jedoch eine weit höhere Performance … gerade bei Lesezugriffen. Eine Idee die ich dabei schon öfters mal ausprobieren wollte war das ganze mit tmpfs zu realisieren. Also irgendwie Tabellen im tmpfs erstellen, mit Kopien auf der HD syncen … Für Produktionsdaten ist das allerdings nix, also hab ich das auch nie wirklich versucht.

Nun bin ich auf eine MySQL Funktion gestoßen die mir das ganze wieder ins Hirn gerufen hat. Es gibt eine Database Engine “MEMORY”. Diese Engine handelt die damit definierte Tabelle komplett im Speicher. Wird MySQL neu gestartet, so gehen die Daten verloren. Wird der Dienst wieder gestartet, wird allerdings die Tabelle / Datenbank wieder erstellt (was ich bei einer eigenen Lösung hätte zu Fuß machen müssen).


CREATE TABLE `queue_summary` (
 `queue_name` varchar(45) NOT NULL,
 `date` date NOT NULL,
 `sum_connect` int(4) DEFAULT '0',
 `sum_abandon` int(4) DEFAULT '0',
 `sum_timeout` int(4) DEFAULT '0',
 PRIMARY KEY (`queue_name`,`date`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8

Das oben ist ein Beispiel. Man sieht dass die Tabelle ganz normal definiert wird, allerdings als Storage Engine “MEMORY” definiert wird. Damit ist das Ding fertig. Es gibt ein paar Einschränkungen für die Engine MEMORY – die findet ihr hier. Wichtig ist natürlich, dass es genügend RAM Speicher gibt :-P

Trigger zum summieren

Der zweite Tipp geht in Richtung Trigger. Wer weiß nicht was Trigger sind? Hans? Ok, ich sag’s Dir. Mit Triggern kann man grob gesagt Aktionen ausführen lassen wenn etwas auf einer Datenbank oder Tabelle passiert. Hier ein Beispiel:


CREATE
DEFINER=`root`@`localhost`
TRIGGER `asterisk_db`.`TR_queue_waiting`
BEFORE INSERT ON `asterisk_db`.`queue_log`
FOR EACH ROW
BEGIN
 IF NEW.event = 'ENTERQUEUE' THEN
 insert into queue_waiting(callid,queue,number) values(NEW.callid,NEW.queuename,NEW.data1);
 ElSEIF NEW.event = 'ABANDON' THEN
 delete from queue_waiting where callid=NEW.callid;
 insert into queue_summary(queue_name,date,sum_abandon) values(NEW.queuename,CURDATE(),'1')
 ON DUPLICATE KEY UPDATE sum_abandon=sum_abandon + 1;
 ElSEIF NEW.event = 'EXITWITHTIMEOUT' THEN
 delete from queue_waiting where callid=NEW.callid;
 insert into queue_summary(queue_name,date,sum_timeout) values(NEW.queuename,CURDATE(),'1')
 ON DUPLICATE KEY UPDATE sum_timeout=sum_timeout + 1;
 ElSEIF NEW.event = 'COMPLETEAGENT' OR NEW.event = 'COMPLETECALLER' OR NEW.event = 'TRANSFER' THEN
 delete from queue_waiting where callid=NEW.callid;
 ELSEIF NEW.event = 'CONNECT' THEN
 update queue_waiting set connected_to=NEW.agent where callid=NEW.callid;
 insert into queue_summary(queue_name,date,sum_connect) values(NEW.queuename,CURDATE(),'1')
 ON DUPLICATE KEY UPDATE sum_connect=sum_connect + 1;
 END IF;
END

Mit diesem Statement wird ein Trigger definiert der vor dem Update der Tabelle asterisk_db.queue_log für jede Zeile einen oder mehrere Befehle ausführt. Dabei wird auf Feld des INSERTS geschaut (NEW.event), je nach dem welchen Wert dieses Feld hat, wird eine Aktion ausgeführt.

Hat NEW.event den Wert “ENTERQUEUE”, so wird ein INSERT in die queue_waiting durchgeführt.

Hat NEW.event den Wert “ABANDON”, so wird ein DELETE und ein INSERT durchgeführt.

….

Doch was hat das ganze mit Performance tuning zu tun?

Ganz einfach. Folgendes Beispiel (aus dem wahren Leben):

Ich habe eine Tabelle – queue_log. In dieser wird alles geloggt was in der Telefon Queue passiert. Ein Anrufer betritt die Warteschlange (ENTERQUEUE), ein Anrufer wird verbunden mit einem Agenten (CONNECT), ein Anrufer legt auf bevor er verbunden wurde (ABANDON), das Gespräch wird beendet (COMPLETECALLER oder COMPLETEAGENT) … Um ein Monitoring für die Queue zu erstellen, möchte ich neben diversen anderen Informationen auch sehen, wie viele Anrufer heute bereits angerufen haben, wie viele haben aufgelegt usw. . Anfangs habe ich einfach ein SQL Statement über die queue_log laufen lassen. Das ging, brauchte allerdings seine Zeit und verursachte auch gewisse Last auf dem System. Da in der queue_log recht viele Informationen zu jedem Anruf geloggt werden, wächst diese recht schnell. Eine Summe zu bilden für die verschiedenen Stati und das nur für einen Tag, gepaart mit einer Aktualisierung alle 3 Sekunden und zur Zeit ca. 20 Usern … naja, das macht sich schon bemerkbar. Das Programm fragt alle 3 Sekunden nach dem Status der Queue – daher die Zahl. Also wie das ganze beschleunigen?

Mit einem Trigger!

Wird – und das seht ihr in dem Beispiel oben – ein “CONNECT” in die queue_log geschrieben, so wird vorher


insert into queue_summary(queue_name,date,sum_connect) values(NEW.queuename,CURDATE(),'1')
ON DUPLICATE KEY UPDATE sum_connect=sum_connect + 1;

ausgeführt. Was macht das Statement? In die kleine Tabelle (die aus dem MEMORY Beispiel oben ;-) ) wird ein INSERT gemacht. Dabei setzt sich der Key aus queue_name und date zusammen. Das ganze ist ein “INSERT … ON DUPLICATE KEY UPDATE” Script. Ist der Key schon vorhanden, wird lediglich ein Update auf den Datensatz durchgeführt. In unserem Fall wird also zuerst versucht den Datensatz für die aktuelle Queue und das heutige Datum anzulegen. Ist das schon vorhanden, so wird lediglich das Feld “sum_connect” um eins hochgezählt. Und das wird gemacht für jedes INSERT was auf die queue_log gemacht wird. Damit haben wir eine Tabelle mit den Summen die automatisch immer aktualisiert wird. Unser Monitoring greift also nur noch auf diese kleine Tabelle zu – die noch dazu im Speicher liegt. Es müssen keine Summen mehr gebildet werden oder ähnliches.

Diese Daten sind sehr gut geeignet für eine Tabelle im RAM – die Summen kommen aus der queue_log und können jederzeit wiederhergestellt werden.

Durch diese beiden Änderungen konnte ich auf unserem System die Auslastung um 15% reduzieren.

Ich hoffe dass ich das Konstrukt halbwegs nachvollziehbar erklärt habe?! Ansonsten einfach fragen :-)

3 Responses to Ein Paar Wiener bitte, oder: zwei tuning Tipps für MySQL

  1. Diez

    Ich hätte da zwei Einwände, zum einen hast du keine Transaktionssicherheit/kein MVCC für MEMROY Tables. Bei Konkurrierenden Zugriffen bekommst du also irgendwann Probleme. Und InnoDB mit ausreichendem buffer_pool ist nicht wesentlich langsamer als Memory Tables in MySQL, erst recht wenn die Lese-Schreiblast sehr hoch ist (ist AFAIK auch so in der MySQL Doku zu lesen).

    • Ronny

      Ich weiß grad nicht wie das ist, aber wenn MySQL selbst über einen Trigger in die Tabelle schreibt, kann das dann auch mehrfach gleichzeitig passieren?
      Für andere Anwendungsfälle hast Du bestimmt Recht. In meinem Beispiel habe ich nur den MySQL Prozess der ein Update macht und der Rest liest nur. Ich denke das sollte passen.

  2. Sebastian

    Mit der memory storage engine hab ich auch schon mal geliebäugelt. Eine drupal installation ( mein daily buisiness ) ist damit allerdings nicht möglich.

    aber ich habe eine dev-vm in der sehr erfolgreich mysql mit tmpfs läuft.
    mit diesem angepassten upstart script
    http://wolfgangziegler.net/ubuntu-11.04-simpletest-performance-upstart-mysql-ramdisk

    zusätzlich habe ich dann noch einen cron der ab und an das sichern via rsync auf die platte vornimmt.

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>