Warum ist diese Mysql-Volltextsuche eigentlich so schnell?

Ich fragte mich heute warum eine frisch installierte Mysql-Datenbank einen ekelhaften Query der Sorte:

select
*
from HotlineDB
where
volltext like '%Drucker%' or
loesung like '%Drucker%' or
originalmail like '%Drucker%' or
informationstext like '%Drucker%' or
int_comment like '%Drucker%';

Innerhalb von 0.0090 Sekunden durchführen kann. Und das obwohl die Tabelle im MyISAM Format ist (also gibt es keinen Puffer der Daten im Mysql RAM so wie z.B. bei InnoDB) und es immerhin 100 MB Daten sind.

Kurz nachgedacht und drauf gekommen: MyISAM verlässt sich beim Puffern der Daten auf das Betriebssystem (hier natürlich Linux, genaugenommen ein Ubuntu 14.04 LTS) und dieses ist ziemlich „aggressiv“ beim Puffern/Cachen von Dateien im Speicher.
Wann immer Linux eine Datei liest (und es genug freien RAM hat) läd es alle Teile (Pages) dieser Datei in seinen Speicher. (alle Pages ist hier nicht ganz korrekt, es werden nur die Pages in den Puffer geladen die gerade gelesen werden und außerdem noch ein paar Pages mehr in der Hoffnung dass diese bald gelesen werden sollen (Read Ahead)).
Das nächste Lesen der Datei muss also nicht mehr die langsame Festplatte bemühen.
Wieviel in diesem Cache liegt, sieht man in der Ausgabe von ‚free -m‘:

              total        used        free      shared  buff/cache   available
Mem:           3951         488        2735          19         727        3206
Swap:          2047           0        2047

Hier hat Linux also 727 MB an Dateien in seinem Cache.
Keine Angst, der Speicher steht trotzdem jederzeit zur Verfügung.
Details dazu gibt es bei Help! Linux ate my RAM!

Nun bleibt die Frage, hat Linux echt unsere Tabellendatei in seinem Buffer? Und wie schnell ist der obige Query wenn die Datei nicht im Buffer ist?
Um diese drängenden Fragen zu beantworten hat ein gewisser Doug Hoyte das praktische Tool vmtouch geschrieben.
Wir installieren es uns wie folgt:

git clone https://github.com/hoytech/vmtouch.git
cd vmtouch 
make
sudo make install

Es kann sein dass vorher noch ein

apt-get install build-essential git

nötig ist…

Und jetzt wo wir dieses praktische Tool haben, können wir endlich schauen ob unsere Tabelle (bei MyISAM eine *.MYD-Datei mit dem Tabellennamen) von Linux gepuffert ist:

vmtouch -v /var/lib/mysql/HotlineDB/HotlineDB.MYD

Und siehe da:

[OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO] 23650/23650

           Files: 1
     Directories: 0
  Resident Pages: 23650/23650  92M/92M  100%
         Elapsed: 0.003418 seconds

Tatsächlich, sie ist zu 100% im Filesystemcache von Linux. Braver Pinguin!

Natürlich kommt jetzt der Gegentest. Das folgende Kommando wirft die Datei aus dem Linux Filesystemcache raus:

vmtouch -ev /var/lib/mysql/HotlineDB/HotlineDB.MYD

Und wenn wir jetzt wieder den Query durchführen braucht er plötzlich 0.0600 Sekunden. Also ca. um den Faktor 7 langsamer.
Ob jetzt natürlich die Daten tatsächlich von der Platte kommen, oder in Wirklichkeit noch im Linux-Filesystembuffer der Hostmaschine (der Server ist eine virtuelle Maschine) gepuffert waren, oder vielleicht im Cache des Storage Systems, kann man nicht so leicht herausfinden.
Es ist wahrscheinlich dass hier also doch keine Platte ihren faulen Lesearm bewegen musste…

Diese vielen Caches machen heutzutage alle Programme schön schnell. Leider machen sie auch das Messen der Performance manchmal recht schwierig 🙁

PS: Und wer das Ergebnis nicht nachvollziehen kann und mit oder ohne Linux Filesystemcache Zeiten von ca. 0.0005 Sekunden hat, der kriegt die Antwort wahrscheinlich auf dem Mysql Query Cache. Dort werden die Antworten auf jede Abfrage direkt im Speicher von MySQL gespeichert.
Kommt exakt die gleiche Abfrage wieder, so kommt die Antwort direkt aus diesem Speicher. Da muss sich also weder die Festplatte, noch der Pinguin bemühen. Wenn man vor dem Query

reset query cache;

ausführt, ist der Query Cache geleert.
Oder man schreibt den Query so:

SELECT SQL_NO_CACHE
*
from HotlineDB
where
volltext like '%Drucker%' or
loesung like '%Drucker%' or
originalmail like '%Drucker%' or
informationstext like '%Drucker%' or
int_comment like '%Drucker%';

Kommentare

2 Antworten zu „Warum ist diese Mysql-Volltextsuche eigentlich so schnell?“

  1. Du kannst ja mal testen, wie lange die Anfrage dauert, nachdem du `sync; echo 1 > /proc/sys/vm/drop_caches` ausgeführt hast. Damit wird der Cache gelehrt (und füllt sich anschließend wieder).

    1. Thomas

      „vmtouch -ev /var/lib/mysql/HotlineDB/HotlineDB.MYD“ sollte ja eigentlich auch den Cache putzen. Müsste also aufs gleiche rauskommen.

Schreibe einen Kommentar

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

Time limit is exhausted. Please reload CAPTCHA.