RootForum Community

Ich habe hier eine Rechner (Core 2 Duo 3, GHz, 8GB Ram, Raid 10) mit Ubuntu Server 7.10 (64 Bit) auf dem eine replizierte MySQL Datenbank 5.0 läuft, auf der eine Volltextsuchen über eine 3 GB große Tabelle gemacht werden.

Die erste Suchabfrage am Tag dauert c.a. 30 Minuten. Alle späteren Abfragen sind in ca 5 Sekunden erledigt.

Mir erklärt sich das so, dass der Kernel (2.6.22) hier die temporäre Tabelle in den Cache (RAM) schreibt, der dann Nachts beim Backup des Systems aber wieder verloren geht?

Nun meine Fragen:

1.) Warum dauert die erste Suche so lange?
Das reine Kopieren der Tabelle als File geht um ein vielfaches schneller.
Wieso wird der Cache also nicht so schnell befüllt?

2.) Kann ich den Cache irgendwie "sichern", so dass er nicht wieder freigegeben wird?
Das experimentieren mit "tmp_table_size" brachte hier leider keinen Erfolg. Auch ein Wert von "4G" hielt die Tabelle nicht im RAM.

3.) Wo könnte noch optimiert werden (Die Abfrage selbst lässt sich leider nicht optimieren)?

Anbei noch ein Auszug aus meiner my.cnf:

monotek wrote:Das experimentieren mit "tmp_table_size" brachte hier leider keinen Erfolg. Auch ein Wert von "4G" hielt die Tabelle nicht im RAM.

tmp_table_size bezieht sich auf temporäre Tabellen, diese werden aber nur im RAM erstellt, wenn keine Rows vom Typ TEXT oder BLOB in der Tabelle existieren, andernfalls werden sie on Disc im tmpdir erstellt. Weitere Beschränkungen findest Du im MySQL-Manual.
Du solltest key_buffer_size erheblich erhöhen, ~4GB sollten es bei Deiner DB-Grösse schon sein. thread_cache_size und thread_concurrency würde ich ebenfalls jeweils um 4 erhöhen. Zudem würde ich (join|sort|read|read_rnd)_buffer_size zunächst undefinieren und nur bei Bedarf mittels tuning-primer.sh anpassen.

Ich habe inzwischen die "max_heap_table_size" und "tmp_table_size" auf 4G gesetzt. Mir war nicht bewusst, dass die "tmp_table_size" von "max_heap_table_size" abhängt. Ich berichte morgen, ob es was gebracht hat.

Es wird Dir nicht viel nutzen...

Ich habe inzwischen die "max_heap_table_size" und "tmp_table_size" auf 4G gesetzt. Mir war nicht bewusst, dass die "tmp_table_size" von "max_heap_table_size" abhängt. Hat mir tuning-primer.sh verraten. Ich berichte morgen, ob es was gebracht hat.

edit:

Danke. Ich werde das mal ausprobieren.

Key Buffer passe ich ebenso mal an.

Die Tabelle nutzt MyISAM und verwendet weder Blob noch Text aber teilweise Mediumtext. Ist das ein Problem?

Ja, auch mediumtext verhindert RAMbasierte tmp_tables.

OK. Dann kann ich die Werte für "max_heap_table_size" und "tmp_table_size" wohl auch wieder auf Standard schrauben.

"key_buffer_size" habe ich auf 4G erhöht, habe dann von tuning-primer.sh die Warnung bekommen, dass dies in Meiner Mysql Version (5.0.45) zu Problemen führen kann. Habe jetzt erstmal 3900M verwendet.

Kannst Du mir noch sagen, warum das Befüllen des Kernel Cache so lange dauert?
Gibts da noch Optimierungsmöglichkeiten?

Ich habe den Server nun mal ein paar Tage laufen lassen und dann tuning-primer.sh ausgeführt.
Mir wird vom Script gesagt, dass key_buffer = 3G zu hoch wäre?
Kannst du mir daher deinen Tipp mit dem hohen Wert erklären?
Die anderen Werte sind nun OK.

So richtig weiter gekommen bin ich aber auch so trotzdem noch nicht.
Ich hätte bei der von mir verwendeten Hardware schon einen größeren Geschwindigkeitszuwachs erwartet.
Gibt es noch andere Möglichkeiten an der Performance zu schrauben?
Festplatte, Prozessor, Ram und my.cnf sind ja nun inzwischen scheinbar ausgereizt?

Gibts ne Möglichkeit den Kernel Cache zu sichern oder die Tabelle irgendwie anders zu cachen?
Komplett in den RAM schreiben möchte ich sie eigentlich nicht, obwohl das mir die einzige verbliebene Möglichkeit scheint?
Wenn mir der Cache nicht ständig verloren gehen würde wäre mein Problem schon gelöst.

Gibts hier noch andere Leute die mal eine ähnlich Problemstellung (große Tabelle -> Volltextsuche) hatte und Tipps geben können?

Danke schon mal im Voraus.

Mehr Infos wäre hilfreich, was sagt ein top, vmstat und show processlist wenn die Abfrage läuft. Damit kann man näher beleuchten wo genau das Problem liegt.

Sorry für die verspätete Antwort.

Konnte das Problem inzwischen lösen, indem ich verhindere, dass der Kernel Cache geleert wird.
Dazu war nur die Verlegung einiger Cronjobs auf andere Zeiten nötig.
Am Ende des Backupscriptes wird nun einfach noch eine Suche ausgeführt, so dass der Cache wieder befüllt wird.

Warum MySQL selbst die Tabelle, trotz hoher tmp_table_size/heap_table_size, nicht cached, würde mich trotzdem noch interessieren.

monotek wrote:Warum MySQL selbst die Tabelle, trotz hoher tmp_table_size/heap_table_size, nicht cached, würde mich trotzdem noch interessieren.

Die Werte kommen dann zur Anwendung wenn ein SELECT Statement abgesetzt werden um die ausgelesenen Werte danach nochmal zu bearbeiten (sort oder filtern). Siehe hier: http://dev.mysql.com/doc/refman/5.1/de/ ... slist.html

Wahrscheinlich liegt es auch noch daran...

Joe User wrote:Ja, auch mediumtext verhindert RAMbasierte tmp_tables.

Danke an alle, für Eure Hilfe.