Optimierung über Konfiguration

Torsten Witt
MySQL
www.wissen4free.org
MySQL-Optimierung-Konfiguration
Die Optimierung eines MySQL-Servers ist keine banale Sache. Es gibt sehr viele Schrauben an
denen man drehen, kann um die Leistung seiner Anwendung die mit der Datenbank einhergeht zu
optimieren. Hier sollen einige Optimierungen vorgestellt werden, die mit der Konfiguration von
MySQL zusammenhängen.
Die Konfiguration wird in der my.conf (Linux/Unix) bzw. my.ini (Windows) vorgenommen.
Nachdem dort Änderungen vorgenommen wurden, muss die Datenbank neu gestartet werden.
Globale Konfiguration
temporäre Tabellen
Hintergrund:
Temporäre Tabellen sind Daten, die aufgrund ihrer Größe nicht mehr in den schnellen
Arbeitsspeicher abgelegt werden können. Daher werden diese auf die Festplatte ausgelagert. Diese
ist aber wesentlich langsamer. Daher muss das Ziel die Verminderung solcher Vorgänge zu sein.
mysql> SHOW GLOBAL STATUS LIKE 'Created\_tmp\_%';
+-------------------------+----------+
| Variable_name
| Value
|
+-------------------------+----------+
| Created_tmp_disk_tables | 9500784 |
| Created_tmp_files
| 240077
|
| Created_tmp_tables
| 18824835 |
+-------------------------+----------+
3 rows in set (0.00 sec)
Created tmp_tables
Ist die Summe aller temporäre angelegten Tabellen.
Created tmp_disk_tables
Ist die Anzahl der temporären Tabellen, die auf der Festplatte ausgelagert werden mussten.
Das ganze ist anschaulicher, wenn man sich das Verhältnis betrachtet:
Created_tmp_disk_tables
∗100
Created_tmp_disk_tablesCreated_tmp_table
9500784
∗100=33,541... %
950078418824835
Die Schräubchen an denen man hier drehen muss heißen:
max_heap_table_size
Begrenzt die Größe der im RAM ladbaren temporären Tabellen.
Seite 1/7
Torsten Witt
MySQL
www.wissen4free.org
tmp_table_size
Begrenzt die Größe der temporären Tabellen.
Um ihre aktuellen Größen zu ermitteln, ist folgender SQL-Befehl notwendig:
mysql> SHOW GLOBAL VARIABLES LIKE '%\_table_size';
+---------------------+----------+
| Variable_name
| Value
|
+---------------------+----------+
| max_heap_table_size | 67108864 |
| tmp_table_size
| 67108864 |
+---------------------+----------+
2 rows in set (0.00 sec)
Um das Verhältnis der in der auf der Festplatte ausgelagerten Tabellen und der in dem RAM zu
verbessern, sollten beide Werte herauf gesetzt werden. Zu beachten ist aber, dass
max_heap_table_size nicht kleiner als sein sollte als tmp_table_size.
Der Query-Cache
Der Query-Cache speichert alle Abfragen und deren Ergebnisse. Dieser Cache bringt also dann
besonders viel Geschwindigkeit, wenn häufig die gleichen Abfragen abgesetzt werden. Dies hilft
vor allem bei CMS-Systemen wie TYPO3 oder Drupal.
Zunächst muss geprüft werden, ob der vielleicht schon Cache aktiviert ist.
mysql> show variables like '%cache%';
+------------------------------+------------+
| Variable_name
| Value
|
+------------------------------+------------+
| binlog_cache_size
| 32768
|
| have_query_cache
| YES
|
| key_cache_age_threshold
| 300
|
| key_cache_block_size
| 1024
|
| key_cache_division_limit
| 100
|
| max_binlog_cache_size
| 4294963200 |
| query_cache_limit
| 1048576
|
| query_cache_min_res_unit
| 4096
|
| query_cache_size
| 0
|
| query_cache_type
| ON
|
| query_cache_wlock_invalidate | OFF
|
| table_definition_cache
| 256
|
| table_open_cache
| 64
|
| thread_cache_size
| 0
|
+------------------------------+------------+
14 rows in set (0.00 sec)
Seite 2/7
Torsten Witt
MySQL
www.wissen4free.org
Wenn query_cache_type = OFF ist, dann ist der Query-Cache deaktiviert. Hier kann man aber
sehen, dass seine Größe mittels query_cache_size auf 0 gesetzt ist.
Wenn wir jetzt query_cache_size = 100M setzen, dann ist der Cache aktiv.
Um den aktuellen Stand unsere Konfiguration zu beobachten, denn hier müsste gelegentlich erhöht
werden, bis man gute Werte hat.
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name
| Value
|
+-------------------------+----------+
| Qcache_free_blocks
| 6118
|
| Qcache_free_memory
| 14389112 |
| Qcache_hits
| 6166202 |
| Qcache_inserts
| 1597849 |
| Qcache_lowmem_prunes
| 741468
|
| Qcache_not_cached
| 235612
|
| Qcache_queries_in_cache | 13607
|
| Qcache_total_blocks
| 35042
|
+-------------------------+----------+
8 rows in set (0.00 sec)
Anhand des Wertes Qcache_not_cached kann man sehen, dass hier der Wert vielleicht noch etwas
erhöht werden könnte.
Zuammengefasst:
query_cache_limit = max. Größe für einen einzelnen Query Aufruf, der in den Cache
aufgenommen wird.
query_cache_size = Wie viel Speicher der gesamte Query-Cache belegen darf
query_cache_type = Query-Cache ein (1) der (0) ausschalten (2 per SQL aktivieren
(SQL_CACHE) /deaktivieren (SQL_NO_CACHE) )
Anzahl offener Tabellen
Mit der Variable table_open_cache wird die Anzahl der geöffneten Tabellen festgelegt. Der
maximale Wert ist von dem Hostbetriebssystem abhängig. Zusätzlich wird die Zahl der offenen
Dateien durch die Zahl der offenen Verbindungen (max_connections), sowie temporären Tabellen
und Dateien erhöht.
Um die aktuelle Zahl der geöffneten Tabellen durch MySQL zu ermitteln:
SHOW GLOBAL STATUS LIKE 'Opened_tables';
Standardwert für table_open_cache = 400
Maximaler Wert: 524288
Maximale Anzahl offener Dateien durch MySQL = table_cache*N+max_connections+temp Tables
+ temp Files
Seite 3/7
Torsten Witt
MySQL
www.wissen4free.org
N ist dabei die maximale Anzahl von Joins in einer Abfrage.
Wie viele Dateien kann ich mit meinem Betriebssystem gleichzeitig
offen halten?
Windows:
c:\net config server
Ergebnis für Windows 7 Professional 64 Bit: 16384
Linux:
cat /proc/sys/fs/file-max
Ergebnis: 75000
Diese Grenzen sind jedoch erweiterbar. Denken Sie beim ändern von table_open_cache daran,
dass nicht nur MySQL Dateien auf Ihrem System offen hält.
Indizes
max_seeks_for_key
Der Wert der Variable gibt dem MySQL-Optimierer vor, wie viele Suchvorgänge für die Suche von
passenden Schlüsseln maximal gemacht werden müssen, um ein passenden Schlüssel zu finden. Das
bedeutet, dass man den MySQL-Optimierer durch einen niedrigen Wert (z.Bsp. 100) dazu zwingt,
die Indizes zu beachten und keinee Tablescans durchführt. So wird die Anzahl der
Festplattenzugriffe verringert.
Optimierungen für Tabellen mit Speicher-Engine MyISAM
myisam_sort_buffer_size
Der dieser Cache sollte so groß gesetzt werden, wie der größte Index der Tabellen. Dies ist ein
Cache, der zum Sortieren des jeweiligen Indexes verwendet wird.
Key Buffer
Der Key-Buffer speichert den Index der Tabellen. Der Key Buffer sollte so groß sei um alle Indexe
aufnehmen zu können. Das kann dann schnell mehrere 100 MB sein. Um eine gute Größe zu finden
sollte man sich die Werte der Variablen key_read_requests und key_reads anschauen.
Der key_reads_requests ist die Gesamtmenge von Zugriffen auf gecachte Schlüsseldaten.
Key_reads ist die Summe aller Zugriffe auf nicht gecachte Schlüsseldaten also die auf das
Dateisystem.
Seite 4/7
Torsten Witt
MySQL
www.wissen4free.org
Das Ziel sollte daher sein, dass so oft wie möglich die Schlüssel aus dem Cache gelesen werden,
also
–
key_reads_requests so groß wie möglich
–
key_reads so klein wie möglich
Um das Verhältnis der beiden Werte zueinander zu sehen genügt folgender Befehl:
show status like 'Key_%'
mysql> show status like 'Key_%';
+------------------------+-------+
| Variable_name
| Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0
|
| Key_blocks_unused
| 14347 |
| Key_blocks_used
| 13
|
| Key_read_requests
| 26
|
| Key_reads
| 13
|
| Key_write_requests
| 0
|
| Key_writes
| 0
|
+---------------------+----------+
7 rows in set (0.00 sec)
Der Name zu konfigurierenden Variable: key_buffer.
Optimierungen für Tabellen mit Speicher-Engine InnoDB
–
Status abfragen:
– show innodb status; (in MySQL 6 nicht mehr verfügbar)
– oder: show engine innodb status;
–
Was guckt man sich an?
– File IO
– Buffer Pool
– Log-Aktivität
– Zeile-Aktivitä
innodb_buffer_pool_size
–
–
Zwischenspeicherung von Daten und Inidizes
80% des Speichers hierfür verwenden, wenn die gesamte Datenbank mit InnoDB läuft
Seite 5/7
Torsten Witt
MySQL
www.wissen4free.org
Optimierung durch richtigen Umgang mit Logging
innodb_log_file_size
–
–
–
typische Größen: 128 – 512 MB
Check innodb_log_waits → bei vielen Änderungen verschlechtert sich die Performance
durch Checkpointing
Achtung: Recoveryzeit nach Crash direkt abhängig von der Größe des ungeflushten
Logs
innodb_flush_log_at_trx_commit
Dieser Wert gibt an, wie oft die Daten aus dem Logpuffer ( aus dem Arbeitsspeicher) in die
Logdatei auf der Festplatte geschrieben werden.
–
–
–
1 (langsam) schreibt (fsync) Log bei jedem Commit die Platte → Echtes ACID
– ist Standardwert
2 (schnell) schreibt Log bei Commit nur in den OS-Zwischenspeicher, Synchronisation
auf der Platte einmal pro Sekunde
0 (sehr schnell) schreibt (fsync) Log ca. jede Sekunde
Seite 6/7
Torsten Witt
MySQL
www.wissen4free.org
Quellen
–
https://auth.ez.no/layout/set/printarticle/developer/articles/tuning_mysql_for_ez_publish
–
http://blog.koehntopp.de/uploads/PT_isotopp_deutsch.pdf
–
http://mysql.com/
–
http://www.administrator.de/Der_MySQL_Query-Cache.html
–
http://dev.mysql.com/doc/refman/5.6/en/table-cache.html (10.04.2012)
–
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html (10.03.2013)
–
http://blog.koehntopp.de/uploads/PT_isotopp_deutsch.pdf (27.10.2011)
Seite 7/7