11.2    Tuning

Beim Tuning eines MariaDB-Servers ist es das zentrale Ziel, die Festplattenlast, also die Anzahl der Ein-/Ausgabeoperationen (I/O), zu verringern. Während ein RAM-Zugriff in wenigen Nanosekunden erfolgt, bewegt man sich bei Festplattenzugriffen bereits im Millisekundenbereich, weil das Positionieren der Schreib-/Leseköpfe nun einmal diese Zeit in Anspruch nimmt. Auch die Drehgeschwindigkeit der Festplattenscheiben kann nicht beliebig gesteigert werden. Ferner spielen die Latenz und die Geschwindigkeit der Netzwerkanbindung eine Rolle, denn wenn der Server seine Anfragen zu langsam erhält oder seine Antworten nicht schnell genug loswird, geht wertvolle Zeit verloren.

Sollten Sie mit dem Gedanken spielen, Ihren Datenbankserver mit besserer Hardware zu bestücken, so sollten Sie zunächst in RAM investieren. Schnellere CPUs bringen nur bei komplexen Rechenoperationen einen Vorteil. Der Umstieg von lokalen Festplatten auf ein schnelles, gut angebundenes SAN beschleunigt das I/O-Verhalten. In den folgenden Abschnitten geht es aber nicht um Hardware-Tuning. Stattdessen werden Sie lernen, wie Sie mit den Bordmitteln des Betriebssystems und MariaDB die Speichernutzung und das Laufzeitverhalten Ihres Servers optimieren können.

[+]  Nutzen Sie »mytop«

Das Programm mytop zeigt Ihnen eine ständig aktualisierte Übersicht darüber, womit Ihr MariaDB-Server gerade beschäftigt ist. Es liefert auch eine Reihe grundlegender Messwerte, an denen Sie ablesen können, ob Ihre Tuning-Maßnahmen erfolgreich sind.

11.2.1    Tuning des Speichers

Unter der URL https://launchpad.net/mysql-tuning-primer erhalten Sie das sehr nützliche bash-Skript mysql-tuning-primer.sh. Wenn Sie Debian oder Ubuntu nutzen, ändern Sie am Anfang des Skripts /bin/sh in /bin/bash. Das ist notwendig, weil /bin/sh unter Ubuntu ein Link auf /bin/dash ist. Beachten Sie bitte auch, dass das Skript das Programm bc erfordert. Bitte installieren Sie es, falls es noch nicht vorhanden ist. Führen Sie das Skript auf dem Server aus, den Sie tunen möchten. Beachten Sie dabei die beiden folgenden Punkte:

Auf den folgenden Seiten finden Sie die für Ihre Analysen relevanten Ausgaben des Skripts mysql-tuning-primer.sh und Hinweise darauf, wie Sie die Ausgaben interpretieren und als Grundlage für Tuning-Maßnahmen nutzen können.

Slow Queries

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 1908 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

Listing 11.35    Skriptausgabe: »Slow Queries«

Die Behandlung von Slow Queries, also von langsamen Abfragen, wird in Abschnitt 11.2.2, »Tuning von Indizes«, näher betrachtet.

Binary Update Log

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

Listing 11.36    Skriptausgabe: »Binary Update Log«

Auch das Binary Update Log begegnet uns erst an späterer Stelle wieder, und zwar in Abschnitt 11.3, »Backup und Point-In-Time-Recovery«.

Max Connections

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum. 
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

Listing 11.37    Skriptausgabe: »Max Connections«

Jede Verbindung benötigt 2 MB Speicher. Dazu kommen aber zusätzlich noch die Puffer und Caches, deren Größe in den Variablen sort_buffer_size, read_buffer_size und binlog_cache_size definiert ist.

Das Skript hat festgestellt, dass wesentlich weniger Verbindungen benutzt werden, als im Parameter max_connections konfiguriert sind. Da für jede potenzielle Verbindung Speicher reserviert wird, ist es sinnvoll, den max_connections-Wert abzusenken. In der MariaDB-Shell geht das wie folgt:

MariaDB [(none)]> set global max_connections=10;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10    |
+-----------------+-------+
1 row in set (0.00 sec)

Listing 11.38    Der Wert »max_connections« wird zunächst reduziert, dann kontrolliert.

Die Einstellung des max_connections-Werts wird auf diese Weise nur temporär verändert. Wenn Sie den Wert dauerhaft verändern wollen, gehen Sie wie folgt vor:

InnoDB Status

INNODB STATUS
Current InnoDB index space = 0 bytes
Current InnoDB data space = 0 bytes
Current InnoDB buffer pool free = 96 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

Listing 11.40    Skriptausgabe: »InnoDB Status«

Die verwendete Größe der InnoDB-Buffer-Pools können Sie über den Wert der Variablen innodb_buffer_pool_size anpassen. Das Vorgehen ist das gleiche wie bei der Variablen max_connections. In diesem Beispiel ist keine Änderung notwendig.

Memory Usage

MEMORY USAGE
Max Memory Ever Allocated : 44 M
Configured Max Per-thread Buffers : 405 M
Configured Max Global Buffers : 42 M
Configured Max Memory Limit : 447 M
Physical Memory : 244 M

Max memory limit exceeds 90% of physical memory

Listing 11.41    Skriptausgabe: »Memory Usage«

[ ! ]  Diesen Wert müssen Sie unbedingt im Auge behalten! Sollte das konfigurierte Memory Limit über dem tatsächlich verfügbaren Speicher liegen, kann es zum Swappen (zur Nutzung von Auslagerungsspeicher) kommen. Der Server würde in diesem Fall sehr langsam werden.

Key Buffer

KEY BUFFER
Current MyISAM index space = 301 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 156
Key buffer free ratio = 40 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

Listing 11.42    Skriptausgabe: »Key Buffer«

Die Indexblöcke aller MyISAM-Tabellen werden in einen Pufferspeicher[ 19 ] geschrieben, der von allen Threads gemeinsam genutzt wird. Die Variable key_buffer_size enthält die Größe dieses Pufferspeichers. In dem Beispiel aus Listing 11.42 ist er ein wenig zu groß und könnte gefahrlos reduziert werden.

Query Cache

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 74 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 57.94 %
Current query_cache_min_res_unit = 4 K
MariaDB won't cache query results that are larger than query_cache_limit in size

Listing 11.43    Skriptausgabe: »Query Cache«

Die Variable query_cache_size definiert die Größe des Caches, der zum Zwischenspeichern von Abfrageergebnissen insgesamt zur Verfügung steht. Der Standardwert ist 0 (Null), das bedeutet, dass der Cache deaktiviert ist. Im Beispiel wurden dem Cache 128 MB zur Verfügung gestellt. Es werden nur Ergebnisse zwischengespeichert, die nicht größer sind als der Wert der Variablen query_cache_limit. In diesem Beispiel liegt das Limit bei 4 MB, der Default-Wert ist 1 MB.

Sort Operations

SORT OPERATIONS
Current sort_buffer_size = 8 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

Listing 11.44    Skriptausgabe: »Sort Operations«

Der Sortierungspuffer (Sort Buffer) wird beispielsweise für Abfragen benötigt, die ein ORDER BY-Statement enthalten. Je nach Anzahl der Datensätze, die regelmäßig sortiert werden müssen, kann dieser Wert größer oder kleiner gewählt werden.

Joins

JOINS
Current join_buffer_size = 2.00 M
You have had 1138756 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

Listing 11.45    Skriptausgabe: »Joins«

Dieser Wert wird in Abschnitt 11.2.2, »Tuning von Indizes«, näher beleuchtet.

Open Files Limit

OPEN FILES LIMIT
Current open_files_limit = 4096 files

The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

Listing 11.46    Skriptausgabe: »Open Files Limit«

Dies ist kein MariaDB-Parameter: Für die Limitierung der gleichzeitig geöffneten Dateien ist das Betriebssystem zuständig. Um diesen Wert auf 4096 festzulegen, geben Sie vor dem Start von MariaDB als root das Kommando ulimit -n 4096 auf der Kommandozeile ein.

Table Cache

Current table_cache value = 1993 tables
You have a total of 780 tables

You have 1582 open tables.
The table_cache value seems to be fine

Listing 11.47    Skriptausgabe: »Table Cache«

Dies ist die Anzahl der geöffneten Tabellen für alle Threads. Wenn Sie nun den Wert für table_cache erhöhen, benötigt MariaDB mehr Dateideskriptoren. Durch die Eingabe des Befehls flush tables in der MariaDB-Shell werden alle offenen Tabellen geschlossen und nur diejenigen wieder geöffnet, die tatsächlich benötigt werden.

Temp Tables

TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M

Of 744743 temp tables, 15% were created on disk
Created disk tmp tables ratio seems fine

Listing 11.48    Skriptausgabe: »Temp Tables«

Wenn Sie viele Abfragen mit GROUP BY-Statements haben, ist es vorteilhaft, diese Werte (im Beispiel 32 MB) zu erhöhen, sofern Sie ausreichend RAM zur Verfügung haben.

Wächst die Größe der temporären Tabelle im Arbeitsspeicher über diese Beschränkung hinaus, wird MariaDB sie automatisch in eine MyISAM-Tabelle auf der Festplatte umwandeln, was sich natürlich nachteilig auf die Zugriffsgeschwindigkeit auswirken wird.

Table Scans

TABLE SCANS
Current read_buffer_size = 508 K
Current table scan ratio = 2313 : 1
read_buffer_size seems to be fine

Listing 11.49    Skriptausgabe: »Table Scans«

Ein Table Scan ist unvermeidlich, wenn eine Tabelle keinen Index besitzt. Manchmal führt MariaDB trotz des vorhandenen Index einen Table Scan aus. Das passiert, wenn der eingebaute Optimierer der Ansicht ist, es sei schneller, die Tabelle komplett zu lesen, als nur einzelne Datensätze herauszusuchen.

Table Locking

TABLE LOCKING
Current Lock Wait ratio = 1 : 1198

You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

Listing 11.50    Skriptausgabe: »Table Locking«

Wenn Sie MyISAM-Tabellen benutzen, werden Sie sehr wahrscheinlich davon profitieren, den Parameter concurrent_insert=2 zu aktivieren. MariaDB erlaubt damit die gleichzeitige Ausführung von SELECT- und INSERT-Statements. Nehmen Sie sich die Zeit, die für Sie beste Kombination dieser Parameter herauszufinden.

Da es kein Patentrezept gibt, werden Sie um eigene Tests nicht herumkommen. Die Mühe lohnt sich, denn mit den richtigen Einstellungen holen Sie das Maximum aus Ihrem Datenbankserver heraus.

11.2.2    Tuning von Indizes

Im vorigen Abschnitt wurde das Slow Queries bereits ebenso erwähnt wie Abfragen, die keine Indizes benutzen. MariaDB kann alle Abfragen, die länger als eine bestimmte Zeit benötigen, in eine darauf spezialisierte Log-Datei schreiben, in das Slow Query Log. Aktiviert wird es durch den folgenden Eintrag in der MariaDB-Konfigurationsdatei:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Listing 11.51    Aktivierung des »Slow Query Log«

In diesem Beispiel wandern alle Abfragen, die länger als zwei Sekunden brauchen, in die Log-Datei /var/log/mysql/mysql-slow.log. Abfragen, die keinen Index benutzen, werden dort ebenfalls protokolliert (log-queries-not-using-indexes).

Passen Sie »long_query_time« an

Ob der Schwellenwert von zwei Sekunden für Ihren Datenbankserver vernünftig gewählt ist, ist keineswegs sicher. Wenn Sie ein Data Warehouse betreiben, sind lange Abfragezeiten normal. Wenn Sie dagegen eine Webseite oder einen Online-Shop betreiben, kommt es eher auf schnelle Antwortzeiten an. Passen Sie den Wert der Variablen long_query_time also an Ihre Bedürfnisse an!

Wenn Sie einen hohen Prozentsatz von langsamen Abfragen im Slow Query Log finden, lohnt es sich, diesen mit dem SQL-Kommando EXPLAIN auf den Grund zu gehen. Das Erzeugen zusätzlicher oder besserer Indizes kann die SELECT-Performance drastisch erhöhen, allerdings dauern damit auch INSERT- und UPDATE-Statements deutlich länger.

Seien Sie in jedem Fall nicht allzu pedantisch. Man muss nicht jede einzelne Abfrage optimieren. Solange es nicht überhandnimmt, dürfen einzelne Abfragen auch einmal etwas länger dauern – in einer Datenbank, in der es sehr häufig Aktualisierungen gibt, ist es vielleicht besser, auf Abfragen zu warten, als die Aktualisierungen warten zu lassen. Was generell besser ist, hängt von Ihrer speziellen Anwendung ab.

Ein Beispiel aus der Praxis

Das Slow Query Log können Sie mit dem folgenden Befehl zusammenfassen lassen:

mysqldumpslow /var/log/mysql/mysql-slow.log

Listing 11.52    Eine Zusammenfassung des »Slow Query Log« erzeugen

Wenn Sie als Parameter noch ein -r eingeben, können Sie sich die am längsten laufenden Abfragen zum Schluss ausgeben lassen. [+]  Achtung: Damit finden Sie nur den Typ der Abfrage, Werte werden durch den Platzhalter »N« ersetzt.

Wenn Sie nun im slow-query.log nach einer Abfrage dieses Typs suchen, können Sie diese mit dem Befehl EXPLAIN erklären lassen. So lässt sich beispielsweise in der Datenbank eines Mailservers folgende Abfrage definieren:

MariaDB [mailserver]> select * from virtual_aliases where \
                      destination='mail@example.com';

Listing 11.53    Eine einfache Datenbankabfrage

Mit EXPLAIN können Sie sehen, wie der MariaDB-Server die Abfrage bearbeitet:

MariaDB [mailserver]> explain select * from virtual_aliases where \
                      destination='mail@example.com';

+----+-------------+-----------------+------+---------------+-------+---------
| id | select_type | table           | type | possible_keys | key   | key_len
+----+-------------+-----------------+------+---------------+-------+---------
|  1 | SIMPLE      | virtual_aliases | ALL  | NULL          | NULL  | NULL
+----+-------------+-----------------+------+---------------+-------+---------

-+------+------+-------------+
 | ref  | rows | Extra       |
-+------+------+-------------+
 | NULL |  127 | Using where |
-+------+------+-------------+

1 row in set (0.00 sec)

Listing 11.54    Anwendung des »explain«-Statements

Schauen wir uns nun die einzelnen Elemente der Ausgabe an:

Für unser Beispiel ist die Erkenntnis wichtig, dass es keinen passenden Index gibt. Mit dem folgenden Kommando am MariaDB-Prompt erzeugen wir ihn:

MariaDB [(none)]> create index idx_destination on virtual_aliases(destination);
Query OK, 127 rows affected (0.12 sec)
Records: 127  Duplicates: 0  Warnings: 0

Listing 11.55    Nachträglich einen Index erzeugen

Mit einem weiteren EXPLAIN-Statement kontrollieren wir den Erfolg:

MariaDB [(none)]> explain select * from virtual_aliases where \
                  destination='mail@example.com';

+----+-------------+-----------------+------+-----------------+-----------------
| id | select_type | table           | type | possible_keys   | key
+----+-------------+-----------------+------+-----------------+-----------------
|  1 | SIMPLE      | virtual_aliases | ref  | idx_destination | idx_destination
+----+-------------+-----------------+------+-----------------+-----------------

-+---------+-------+------+-------------+
 | key_len | ref   | rows | Extra       |
-+---------+-------+------+-------------+
 | 82      | const |    1 | Using where |
-+---------+-------+------+-------------+

Listing 11.56    Erfolgskontrolle der Indexerstellung

Dieser Index würde Abfragen auf die Datenbank in Zukunft deutlich beschleunigen.

Zusammenfassung der Indexerstellung

  1. Schalten Sie das Slow Query Log ein.

  2. Finden Sie mit mysqldumpslow die Abfrageklasse, die das System am stärksten beeinflusst.

  3. Suchen Sie aus dem Slow Query Log ein typisches Beispiel dafür.

  4. Untersuchen Sie dieses Beispiel mithilfe des EXPLAIN-Kommandos.

  5. Erzeugen Sie einen (zusätzlichen) Index.