MySQL - erste Volltextsuche über 3GB Tabelle langsam?

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

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:

Code: Select all

port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 16M
table_cache = 512
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 128M
tmp_table_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by Joe User »

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.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

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.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by Joe User »

Es wird Dir nicht viel nutzen...
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

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?
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by Joe User »

Ja, auch mediumtext verhindert RAMbasierte tmp_tables.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

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?
Top

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

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.
Top

braindead
RSAC
Posts: 257
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by braindead »

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.
Top

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

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.
Top

braindead
RSAC
Posts: 257
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by braindead »

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/show-processlist.html
Top

monotek
Posts: 64
Joined: 2003-08-26 23:23
Location: Dresden

Re: MySQL - erste Volltextsuche über 3GB Tabelle langsam?

Post by monotek »

Wahrscheinlich liegt es auch noch daran...

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


Danke an alle, für Eure Hilfe.
Top