Optimierungsfrage

Hallo NG,

ich habe jetzt einen neuen Server mit Dual-Prozessor und 4 GB RAM. Auf
der Maschine läuft MySQL 5.0.22 mit 2 größeren Datenbanken. Ein paar
Eckdaten noch dazu.

Dieser MySQL-Server läuft bereits 2 Tage, 12 Stunden, 36 Minuten und 21
Sekunden. Er wurde am 11. Juni 2006 um 22:15 gestartet.

ø pro Sekunde: 50,09

Die größten Tabellen mit großen Indizes (Fulltext) schauen wie folgt aus:

Tabelle 1:
Daten 590.753 KB
Index 1.017 MB
Insgesamt 1.594 MB

Tabelle 2:
Daten 729.107 KB
Index 1.065 MB
Insgesamt 1.777 MB

Diese beiden Tabellen werden größtenteils abgefragt, die anderen
Tabellen der Datenbanken sind nur Beiwerk. Meine MySQL-Parameter der
Serverkonfiguration schauen wie folgt aus.

# Index
ft_min_word_len = 3

#
# * Fine Tuning
#
# global_buffers
key_buffer = 2048M

# thread_buffers
sort_buffer_size = 256M
myisam_sort_buffer = 256M
read_buffer_size = 256M
read_rnd_buffer_size = 256M
join_buffer_size = 256M

# other parameters
max_allowed_packet = 1M
table_cache = 512
thread_stack = 1M
thread_cache_size = 64M
max_connections = 300
tmp_table_size = 512M

#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 256M
query_cache_type = 1

#
# * Logging
#
log-slow-queries = /var/log/mysql/mysql-slow.log
#log-queries-not-using-indexes


Der Server läuft soweit ganz stabil und die Abfragen sind auch schnell
beantwortet. Ab und zu kommen aber Abfragen, die den Load mal ganz
schnell auf 2-3 katapultieren.. jaja, mysql-slow.log (ich weiß...)

Ein paar Daten aus'm phpMyAdmin hätte ich noch:

Handler_read_rnd 28 M
Handler_read_rnd_next 210 M

Qcache_free_memory 15 M
Qcache_hits 959 k
Qcache_inserts 2 M
Qcache_lowmem_prunes 687 k

Threads_cached 18
Threads_connected 4
Threads_created 22
Threads_running 1
Threads_cache_hitrate_% 99,99 %

Created_tmp_disk_tables 4 k
Created_tmp_files 14
Created_tmp_tables 360 k

Key_blocks_unused 22 k
Key_blocks_used 2 M
Key_read_requests 15 G
Key_reads 2 M
Key_write_requests 23 M
Key_writes 19 M
Key_buffer_fraction_% 98,94 %

Select_full_join 8
Select_full_range_join 19
Select_range 38 k
Select_range_check 0
Select_scan 10 k

Open_tables 214
Opened_tables 301
Table_locks_immediate 10 M
Table_locks_waited 8 k

Die Hauptfrage ist erstmal: Ist die Konfiguration von oben in Ordnung,
insbesondere für ein Dual-Prozessorsystem? Wenn nicht, was sollte ich
anders machen? Bin für jeden konstruktiven Ratschlag dankbar!


Gruß,
Stefan

Re: Optimierungsfrage

Stefan Gläßer wrote:
>
> ich habe jetzt einen neuen Server mit Dual-Prozessor und 4 GB RAM. Auf
> der Maschine läuft MySQL 5.0.22 mit 2 größeren Datenbanken. Ein paar
> Eckdaten noch dazu.

Betriebssystem? Hoffentlich ein 64-bittiges Linux.

> ø pro Sekunde: 50,09

Aha. Dein Server langweilt sich :-)

> Die größten Tabellen mit großen Indizes (Fulltext) schauen wie folgt aus:
>
> Tabelle 1:
> Daten 590.753 KB
> Index 1.017 MB
> Insgesamt 1.594 MB
>
> Tabelle 2:
> Daten 729.107 KB
> Index 1.065 MB
> Insgesamt 1.777 MB

Oha. 2GB Indizes. Paßt noch vom RAM her, aber viel Luft ist da nicht.

> key_buffer = 2048M

Ist sinnvoll. Zumindest wenn deine Tabellen alle MyISAM sind.

> # thread_buffers
> sort_buffer_size = 256M
> myisam_sort_buffer = 256M
> read_buffer_size = 256M
> read_rnd_buffer_size = 256M
> join_buffer_size = 256M

Viel zu groß. Bedenke, das ist pro Thread, vulgo pro Connection.
Bleib bei den Defaults. Höchstens sort_buffer und read_buffer kannst
du *leicht* vergrößern.

> thread_stack = 1M

Zu groß. Hier bewahrt dich nur das Betriebssystem vor Schaden (lazy
allocation / overcommit). Bleib beim Default.

> thread_cache_size = 64M
> max_connections = 300

In dieser Kombination sinnlos. Wenn du maximal 300 Server-Threads haben
kannst, brauchst du auch maximal 300 für Re-use zu cachen. Außerdem ist
clone() auf Linux/x86 vergleichsweise billig. Ich würde thread_cache =
max_connections / 2 ansetzen. Oder weniger. / 4 reicht auch.

> tmp_table_size = 512M

Das kann fett in die Hose gehen. Bedenke, das das pro Thread auch
mehrfach zur Anwendung kommen kann.

> Qcache_free_memory 15 M
> Qcache_hits 959 k
> Qcache_inserts 2 M
> Qcache_lowmem_prunes 687 k

Sieht nicht so aus, als wäre der Query-Cache sehr effektiv. Entweder
ist er zu klein (statistisch wird der Cache bei jedem 3. Insert
reorganisiert) oder deine Applikation setzt nicht genug identische
Queries ab.

> Threads_cached 18

Siehste! Du hast maximal 18 Threads parallel gehabt. Wozu brauchst
du da 64K Thread-Cache?

> Created_tmp_disk_tables 4 k
> Created_tmp_tables 360 k

Das ist auch OK. Aber bei 512M Limit für in memory tmp_tables auch
zu erwarten. Ich würde tmp_table_size systematisch verkleinern und
beobachten, ab wann dieses Verhältnis schlechter wird.

> Key_blocks_unused 22 k
> Key_blocks_used 2 M
> Key_read_requests 15 G
> Key_reads 2 M

Dein key_buffer wird scheints komplett ausgenutzt. Vermutlich bringt
es mehr, die 256MB des Query-Cache hier reinzustecken.


XL

Re: Optimierungsfrage

Hallo Axel,

ja, ist ein Debian x64.

>> ø pro Sekunde: 50,09
> Aha. Dein Server langweilt sich :-)
Nicht wirklich :)

>> key_buffer = 2048M
> Ist sinnvoll. Zumindest wenn deine Tabellen alle MyISAM sind.
Ja sind sie.

> [tolle Tips]

>> tmp_table_size = 512M
> Das kann fett in die Hose gehen. Bedenke, das das pro Thread auch
> mehrfach zur Anwendung kommen kann.

Ich hab gedacht, es handelt sich hierbei um einen globalen Wert und
nicht um einen Thread-abhängigen. Hab es jetzt reduziert auf (in
Verbindung mit http://bugs.mysql.com/bug.php?id=18875)

tmp_table_size = 32M
max_heap_table_size = 32M

> [noch mehr tolle Tips]

Ich hab noch einige Änderungen vorgenommen und bisher sieht es ganz gut
aus. Werd nochmal ein bisschen mit rumexperimentieren.

Auf jeden Fall herzlichen Dank!


Gruß,
Stefan