Im letzten halben Jahrzehnt (puh, das klingt furchtbar) habe ich mehrere MySQL Datenbanken und Server betreut und dabei eine Vielzahl von Sachen gelernt, die ich mal niederschreiben muss. Das wird ein umfassender Artikel, also schon mal anschnallen…

MySQL, MariaDB, Percona, etc.

MySQL hat eine sehr lange Geschichte, die bis in die frühen 90er Jahre zurückreicht. Seit es die HPfSC Domain gibt, kam immer MySQL zum Einsatz, auch wenn die ersten Versuche noch keine größere Datenbankabfragen enthielten.

Leider gab es Ende der 2000er Jahre einen Fork im zugehörigen OpenSource Projekt und MariaDB entstand als damals noch kompatible Version. Die Kompatibilität zwischen MySQL und MariaDB ist spätestens seit MySQL 5.7 nicht mehr gegeben. Beide Datenbanksysteme sind nicht austauschbar und verhalten sich teils auch bei Konfiguration und Abfragen unterschiedlich. Leider hat sich das noch nicht rum gesprochen und es wird noch immer behauptet, man könne ein für MySQL freigegebenes Produkt problemlos mit MariaDB betreiben. Das stimmt definitiv nicht!

MySQL gehört zum Oracle Imperium. Neben der Community Edition gibt es eine kommerzielle Version. Diese bietet viele interessante Features wie Verschlüsselung der Datenbankdateien oder Maskierung von Feldern. Die Preise sind aber gepfeffert und es wird, wie bei Oracle üblich, nach verwendeten CPU-Cores berechnet. Da kommen schnell mehrere 10k€ pro Jahr pro Server zusammen.

Neben der professionellen Version von Oracle gibt es noch einige weitere Anbieter, die die Community Edition um weitere Features angereichert haben. Hier möchte ich insbesondere Percona nennen. Einen Teil der Percona Tools, wie die Backuplösung XtraBackup, kann man kostenlos nutzen. Würde ich eine kommerzielle MySQL Version mit professionellem Support benötigen, würde ich zuerst bei Percona schauen!

Tatsächlich habe ich aber in den Jahren nur die Community Edition verwendet, denn diese ist für mittelgroße Produktivdatenbanken vollkommen ausreichend.

MySQL 5.7 Installation unter RHEL 7

Normalerweise nutzt man Linux, um MySQL zu betreiben. Ich kenne keine produktiven MySQL Umgebungen, die nicht entweder RedHat Enterprise Linux (RHEL), Suse Linux oder Ubuntu nutzen.

Tatsächlich ist aber selbst unter RedHat die MySQL Installation nicht trivial, da die Installationspakete in RHEL zugunsten von MariDB nicht enthalten sind. Deshalb muss man zunächst die Community Paketquellen einbinden und dann die Installation starten. Die MySQL 5.7 Installation unter RHEL 7 geht ungefähr so:

yum -y install yum-utils
yum-config-manager --enable rhel-7-server-optional-rpms
yum update -y

export http_proxy=http://ein-corporate-proxy-falls-noetig:8080
export https_proxy=http://ein-corporate-proxy-falls-noetig:8080

yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum-config-manager --disable mysql80-community
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community
yum repolist all | grep mysql
yum -y install mysql-community-server
umount /var/lib/mysql
chmod 755 /var/lib/mysql
chown mysql:mysql /var/lib/mysql
mount /var/lib/mysql
ls -la /var/lib/
restorecon -R /var/lib/mysql

Jetzt starte ich den frisch installierten MySQL Server kurz, damit er die Datenbankdateien erstellt.

systemctl daemon-reload
systemctl start mysqld
systemctl status mysqld -l
systemctl stop mysqld
ls -la /var/lib/mysql

Wichtig: MySQL legt die Datenbanken in das Verzeichnis /var/lib/mysql. Dieses Verzeichnis sollte auf einer Partition mit extrem hoher Zugriffsgeschwindigkeit (hohe IOPs) liegen, etwa einer SSD oder eine sehr schnellen Netzlaufwerk.

Falls ich einen Proxy für den Zugriff auf das Internet verwenden musste (was ja typisch in Serverumgebungen ist), dann sollte ich diesen Proxy noch für die MySQL Yum Repos eintragen. Die normalen RHEL Repos werden sicher von einem entsprechenden Mirror im Netzwerk zur Verfügung gestellt, der ohne Proxy erreichbar ist. Den Proxy setze ich in den Yum Repo Dateien:

vim /etc/yum.repos.d/mysql-community.repo
vim /etc/yum.repos.d/mysql-community-source.repo

..für jedes Repo diesen Eintrag hinzufügen...

proxy=http://ein-corporate-proxy-server:8080

Nach dieser Änderung teste ich, ob der Zugriff auf die MySQL Repos klappt:

unset http_proxy
unset https_proxy
yum clean metadata
yum update

Jetzt wäre der richtige Zeitpunkt, den Server zu konfigurieren. Konkrete Vorschläge zur Konfiguration gibt es weiter unten. Wichtig zu wissen ist:

• /etc/my.cnf – zentrale Konfigurationsdatei mit globalen Einstellungen
• /etc/my.cnf.d/*.cnf – hier eigene Konfigurationsdatei erstellen

Steht die Konfiguration, kann der Server gestartet und „abgesichert“ werden. Dazu bietet MySQL ein kleines Skript. Dieses Skript loggt sich als Root ein und nimmt ein paar Änderungen vor. Das initiale Root Passwort muss man in den MySQL Logdateien suchen, denn es wird automatisch beim ersten Start generiert.

mkdir -p /var/lib/mysql/binlogs
chmod 700 /var/lib/mysql/binlogs
chown mysql:mysql /var/lib/mysql/binlogs
systemctl start mysqld

cat /var/log/mysqld.log | grep "A temporary password is generated for root@localhost:"

mysql_secure_installation
systemctl stop mysqld
systemctl disable mysqld

Über das mysql_secure_installation Skript setzte ich zum Beispiel ein neues Passwort für den Root Nutzer und lege fest, dass dieser sich nur lokal, also nicht über das Netzwerk, einloggen kann.

Jetzt bleibt noch den „Autostart“ des MySQL Servers zu aktivieren und das per Reboot zu testen:

systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
reboot

InnoDB und MyISAM

MySQL bietet verschiedene Engines, mit denen Tabellen gespeichert werden. Historisch wurde MyISAM verwendet und man findet auch heute noch bei vielen Hostern diese Engine als Standardeinstellung. Tatsächlich ist MyISAM keine gute Wahl. Das Ändern einer Tabellenzeile sperrt z.B. die gesamte Tabelle für andere Schreibzugriffe. Praktisch bedeutet dies, dass man nur sequentiell Daten in einer Tabelle schreiben kann.

Falls es also keinen speziellen anderen Grund gibt, verwende ich immer InnoDB als Tabellen-Engine. InnoDB bietet Transaktionen und ausgeklügeltes Locking und hat viele Stellschrauben, um es an das tatsächliche Abfrageverhalten anzupassen.

In einer Datenbank, deren Betreuung ich vor 5 Jahren übernommen habe, war die Situation besonders schlimm: Es gab eine Mischung aus MyISAM und InnoDB Tabellen. Das waren Tabellen, die zu einer Applikation gehören und es fanden auch Abfragen über eine Mischung aus MyISAM und InnoDB Tabellen statt. Das führte immer wieder zu Deadlocks.

Die Bereinigung hat sehr viel Zeit in Anspruch genommen, da in MySQL 5.6 die Breite von Indizes bei InnoDB geringer war als bei MyISAM. Das wurde erst mit MySQL 5.7 angepasst. Doch um auf MySQL 5.7 zu wechseln, musste zunächst die Applikation aktualisiert werden. Diese ganze Kette an Abhängigkeiten hat sich dann knapp 2 Jahre für die Produktionsdatenbank nebst Applikation gezogen. Es war nicht schön.

Weiterhin wurde bei den Tabellen eine Mischung aus verschiedenen Character und Collation Sets verwendet. Auch das ist ein absolutes No Go! Die Character Sets definieren, wie Zeichenketten gespeichert werden. Collations definieren, wie Vergleiche zwischen Zeichenketten durchgeführt werden, etwa ob ein Ü gleich einem U ist. Glücklicherweise war dieses Problem über die entsprechenden ALTER TABLE Statements relativ einfach zu bereinigen und hatte auch keine bekannten Auswirkungen auf die Applikation.

init_connect='SET collation_connection=utf8mb4_unicode_ci' 
init_connect='SET NAMES utf8mb4' 
character-set-server=utf8mb4 
collation-server=utf8mb4_unicode_ci 
skip-character-set-client-handshake

Sollte Situationen sollte man tunlichst vermeiden. Engine Typ, Character und Collation Set definiert man einmalig in der MySQL Config und beim Erstellen einer Tabelle lässt man diese Angaben unbedingt weg. Dann kann auch kein Wildwuchs entstehen.

MySQL Nutzer

Es ist hoffentlich selbstverständlich, dass man den MySQL Root Nutzer nicht für die normale Datenbankarbeit nutzt. Tatsächlich sollte man für jeden Zweck einen eigenen Nutzer anlegen. Beispiele:

• Nutzer für Backups
• Nutzer für Replikation
• Nutzer für Applikation
• Nutzer für Administration

MySQL bietet unendlich viele Möglichkeiten, um den Zugriff einzelner Nutzer auf den angedachten Zweck einzuschränken. Beispiele für mögliche Einschränkungen:

• Von wo darf der Nutzer sich verbinden, etwa IP-Range?
• Auf welche Datenbanken (oder sogar nur einzelne Tabellen oder Views) darf der Nutzer zugreifen?
• Welche Rechte hat der Nutzer auf den zugreifbaren Objekten?
• Welche Befehle darf der Nutzer ausführen?

Läuft der Backup-Prozess zum Beispiel lokal auf dem MySQL Server, dann könnte man den Backup Nutzer folgendermaßen einschränken:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Wird die Datenbank von einem weiteren Datenbankserver repliziert, dann könnte man diesen Nutzer folgendermaßen einschränken:

CREATE USER 'repl'@'IP-of-other-mysql-host' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON . TO 'repl'@'IP-of-other-mysql-host';
FLUSH PRIVILEGES;

Views nutzen

Ein häufig unterschätztes Feature von SQL Servern sind Views. Views sind virtuelle Tabellen, deren Inhalt über SQL Queries definiert wird. Views kann man zum Beispiel nutzen, um bestimmten Nutzern nur auf einen Teil der Originaltabelle zu gewähren. Man würde in der View Definition zum Beispiel Spalten mit sensitiven Daten weglassen und einem Nutzer nur Zugriff auf den View, nicht aber die Originaltabelle gewähren.

Views lassen sich auch mittels JOIN über mehrere Tabellen definieren. Auch andere SQL Bestandteile wie WHERE oder GROUP BY lassen sich in Views verwenden. Auf einem Views selbst können alle normalen SQL SELECT Statements anwenden. Intern führt MySQL dann diese Query plus die Query in der View Definition zusammen aus.

Das ist dann aber auch die Schwäche eines Views. Da der View nur virtuell ist, muss dessen Inhalt bei jeder Abfrage erneut gebildet werden. Deshalb ist es wichtig, dass die Query in der View Definition durch Indizes abgedeckt ist und keine Full Table Scans notwendig sind.

MySQL Backup mittels Percona XtraBackup

Zunächst: MySQL ist extrem stabil. Ich kann mich in den letzten 5 Jahren an keinen Fall erinnern, dass MySQL als Software auf den von mir betreuten Servern (teils bis zu 50 Server) abgestürzt wäre. Was aber in den Jahren passiert ist:

• komplettes Rechenzentraum mit Stromausfall
• korrupte Replikation wegen plötzlichem Netzwerkausfall
• unerwartete Abstürze oder Reboots der Server / VMs
• kompletter Ausfall des Network Storage mitten im Schreibzugriff
• jemand tippt manuell DROP DATABASE ein und hat die Replikation zuvor nicht aufgehoben

In all diesen Situationen können Backups eine Hilfe sein, neben vielen anderen Mitteln. Der erste Ansatz für ein Backup ist mysqldump:

mysqldump -u backup -p database > database-dump.sql

Das ist für kleine Spielzeugdatenbanken eine valide Option, aber nichts für ein Produktivsystem. Während der Dump durchgeführt wird, werden Tabellen oder sogar die gesamte Datenbank gesperrt, je nach verwendeter Engine. Es findet auch kein Backup von Änderungen während des Backups statt und der Zustand der Tabellen muss dadurch nicht konsistent sein.

Eine wesentlich bessere Lösung ist Percona’s XtraBackup. XtraBackup arbeitet direkt auf den Dateien der Datenbank. Es kopiert diese auf Betriebssystemebene und wendet anschließend die Binlogs (die noch nicht persistierten Änderungen) auf die Kopie an. Das klappt ohne Datenbank-Lock, solange man InnoDB verwendet.

cd backup-verzeichnis/
xtrabackup --backup --password=PASSWORT-BACKUP-NUTZER
xtrabackup --prepare
tar -czf "backup-`date +%Y_%m_%d__%Hh%Mm`.tgz" xtrabackup_backupfiles

Das erzeugte Backup muss natürlich auf einem anderen Server gesichert werden. Dazu könnte man rsync oder ein anderes Tool verwenden.

Das erzeugte Archiv enthält den Inhalt des /var/lib/mysql Verzeichnis. Ein Restore des Backups ist damit denkbar einfach.

• Backup auf Server übertragen und entpacken
• Server stoppen
• /var/lib/mysql ersetzen durch Backup
• Server starten

In der Realität ist es natürlich etwas komplizierter. Wenn man ein Backup zurückspielt, muss vorher die Replikation gestoppt und am Ende wieder neu aufgesetzt werden.

stop slave;
reset slave;

Es kann auch sein, dass SELinux es gar nicht gut findet, wenn der Inhalt des /var/lib/mysql Ordnerns ersetzt wird. Und vielleicht möchte man doch eine Kopie des alten /var/lib/mysql Ordners behalten. Mit folgenden Kommandos kommt man grob zum Ziel:

systemctl stop mysql
systemctl status mysql

mv /var/lib/mysql /var/lib/mysql-backup
mkdir -p /var/lib/mysql
chown mysql:mysql /var/lib/mysql
chmod 755 /var/lib/mysql

xtrabackup --copy-back --target-dir=/opt/mysql-restore/xtrabackup_backupfiles

mkdir -p /var/lib/mysql/binlogs
chown -R mysql:mysql /var/lib/mysql
chmod 755 /var/lib/mysql
find /var/lib/mysql -type d -exec chmod 700 {} \;
find /var/lib/mysql -type f -exec chmod 660 {} \;
restorecon -R /var/lib/mysql

systemctl start mysql
systemctl status mysql
cat /var/log/mysqld.log

Wichtig: Der gesamte Backup-Prozess muss getestet werden. Nichts ist schlimmer als zu glauben, man hätte ein funktionierendes Backup. Auch das Restore eines Backups sollte man 1-2 im Jahr üben und die dazu notwendigen Schritte sind zu dokumentieren. Der Punkt, an dem man ein Backup wiederherstellen muss, wird immer ungünstig sein (lies: nachts 4:20 Uhr) und dann ist man froh, wenn man eine Copy & Paste Anleitung hat. Been there, done that!

MySQL Replikation

Um einen unterbruchsfreien Betrieb zu gewährleisten, muss es mehrere MySQL Server geben, die sich gegenseitig replizieren. Diese Server müssen sich in unterschiedlichen Rechenzentren befinden und über eine schnelle latenzarme Direktverbindung verfügen. Ziel ist, dass beide Server immer auf dem gleichen Stand sind und die jeweiligen Änderungen der Gegenseite stets innerhalb weniger Millisekunden replizieren. Man spricht hier von einer Master-Master Replikation. Jeder Server bekommt per MySQL Config eine ID zugewiesen:

server-id=1 bzw. 2 auf dem anderen Server

Weiterhin muss man sicherstellen, dass Schreibzugriffe nur an den aktuell primären Server geschickt werden. Dadurch werden mögliche Konflikte stark vermindert (Beispiel: Server 1 will eine Zeile ändern, die Server 2 gerade gelöscht hat). In der MySQL Community Edition gibt es dafür keine Lösung. Man kann einen einfachen TCP Proxy verwenden oder kommerzielle Produkte wie ProxySQL.

Sollte es doch mal passieren, dass beide Server eine neue Zeile in einer Tabelle erzeugen, sollten diese unterschiedliche Primärschlüssel verwenden. Typischerweise löst man dies dadurch, dass ein Server gerade und der andere ungerade Zahlen als Primärschlüssel erzeugt:

auto_increment_offset=2 
auto_increment_increment=1 bzw. 2 auf dem anderen Server

Die MySQL Replikation nutzt Binlog Dateien, um die Änderungen zwischen den Servern zu replizieren. Damit diese Binlogs erstellt werden (und auch nach 7 Tagen wieder gelöscht werden), gibt es folgende Einstellungen:

transaction-isolation=READ-COMMITTED 
binlog-format=MIXED
general_log=0 
log-bin=/var/lib/mysql/binlogs/binlogs 
relay-log=mysqld-relay-bin 
expire_logs_days=7

Bei einer Replikation gibt es immer einen Master und einen Slave. Der Slave repliziert die Änderungen des Masters. Im Fall einer Master-Master Replikation setzt man tatsächlich 2 Replikationen auf, eine für Replikation von Server 1 zu Server 2 und eine zweite für Replikation von Server 2 zu Server 1. Damit ist jeder der beiden Server sowohl Master als auch Slave.

Jede dieser beiden Replikationen muss man einzeln aufsetzen und zwar immer auf dem Slave. Das Vorgehen im laufenden Betrieb (Server 1 ist produktiv, Server 2 soll hinzugefügt werden) ist grob:

1. beidseitige Konnektivität prüfen
2. Auf Server 1 setzt man alle Binlog Statistiken über „reset master;“ zurück.
3. Backup auf Server 1 mittels XtraBackup erstellen
4. Backup auf Server 2 übertragen und dort einspielen
5. Server 2 starten
6. Inhalt der xtrabackup_binlog_info ausgeben und aufschreiben.
7. Server 2 als Slave von Server 1 konfigurieren und dabei die Infos aus der xtrabackup_binlog_info verwenden.
8. Server 2 Slave starten.
9. Aktuelle binlog Position auf Server 2 mittels „show master status“ ausgeben.
10. Server 1 als Slave von Server 2 konfigurieren und dabei die Infos aus „show master status“ von Server 2 verwenden.
11. Server 1 Slave starten.
12. beidseitige Replikation testen

Zugegeben, das klingt irre kompliziert, aber es wird besser mit der Zeit :-) Schauen wir uns die Punkte einzeln an.

Zunächst prüfe ich von jedem Server aus, ob ich mich auf dem anderen Server einloggen kann. Dabei verwende ich natürlich den Nutzer, den ich auch später für die Replikation verwenden will. Geht hier was schief, sind mögliche Fehlerquellen:

• falscher Nutzername oder Passwort
• falsch angelegter Nutzer (oder Nutzer nur auf einem Server angelegt)
• Firewall verhindert Verbindungsaufbau

mysql -u repl -p --host IP-ANDERER-SERVER

Jetzt kann ich auf Server 1 die Binlogs zurücksetzen.

reset master;

Jetzt erstelle ich ein Backup (siehe Abschnitt zu Percona XtraBackup), übertrage dieses auf Server 2 und spiele es auf Server 2 ein und starte den Server. Im Backup befindet sich eine Datei, deren Inhalt ich benötige:

$: cat xtrabackup_binlog_info
binlogs.000096  16199746

Mit diesen Infos kann ich nun auf Server 2 die Replikation konfigurieren:

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_HOST='IP-SERVER-1', MASTER_PORT=3306, MASTER_LOG_FILE='binlogs.000096', MASTER_LOG_POS=16199746;
start salve;
show slave status\G

Falls ich keinen Fehler gemacht habe, beginnt Server 2 nun alle seit dem Backup aufgelaufenen Änderungen zu replizieren. Sobald Server 2 alle Änderungen aufgeholt hat, sollte im Slave Status diese Zeile erscheinen:

show slave status\G
...
Seconds_Behind_Master: 0

Jetzt kann ich weitermachen und die Replikation von Server 2 zu Server 1 aufsetzen. Dazu setze ich zunächst die Binlogs auf Server 2 zurück und gebe die Binlog Koordinaten auf Server 2 aus:

reset master;
show master status;
+----------------+-----------+                                         
| File           | Position  |                                         
+----------------+-----------+                                         
| binlogs.000217 | 656360650 |                                         
+----------------+-----------+

Und genau diese Daten verwende ich jetzt auf Server 1, um auch hier die Replikation zu konfigurieren:

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_HOST='IP-SERVER-2', MASTER_PORT=3306, MASTER_LOG_FILE='binlogs.000217', MASTER_LOG_POS=656360650;
start salve;
show slave status\G

Da auf Server 2 zu diesem Zeitpunkt keine schreibenden Änderungen sein dürfen, sollte es nur wenige Augenblicke dauern, bis Server 1 alle Änderungen von Server 2 übernommen hat. Ich füge nun testweise auf einem Server eine neue Zeile oder Tabelle hinzu und kontrolliere auf dem anderen Server, ob die Änderung sichtbar ist. Sollte es zu Fehlern kommen, zeigt „show slave status\G“ diese an. Meiner Erfahrung nach läuft die Replikation aber problemlos, solange es nicht zu irgendwelchen Netzwerkausfällen, etc. kommt.

MySQL Performance und Sys Schema

MySQL verfügt über 2 enorm hilfreiche Werkzeuge, um langsame Queries, fehlende Indizes, etc. zu identifizieren. Das so genannte Performance Schema wird per Konfiguration aktiviert:

performance_schema=on

Ab diesem Zeitpunkt erhebt MySQL verschiedene Statistiken und Metriken und speichert diese in den Tabellen der „performance_schema“ Datenbank.

Die Abfrage der Metriken im Performance Schema ist recht mühsam. Deshalb sollte man unbedingt noch das so genannte MySQL Sys Schema installieren, falls es nicht vorhanden ist. Nachdem der Server ein paar Tage im Betrieb war, kann man die ersten Auswertungen generieren.

In den folgenden Beispielen habe ich eine Datenbank „live“, für die ich die Auswertung ermitteln möchte. Mit dem folgenden Statement kann ich ungenutzte Indizes auflisten. Diese Indizes sollte man entfernen, da sie beim Ändern des Tabelleninhalts aktualisiert werden müssen, was entsprechende Queries verlangsamt.

select * from sys.schema_unused_indexes where object_schema='live';

Das folgende Statement listet redundante Indizes. Dies sind auch Löschkandidaten.

select * from sys.schema_redundant_indexes where table_schema = 'live'\G;

Schlimmer als ungenutzte oder redundante Indizes sind fehlende Indizes. Die folgenden Statements helfen beim Finden möglicher fehlender Indizes.

Das folgende Statement listet Queries, für deren Ermittlung MySQL temporäre Dateien anlegen muss. D

select s.*, digest.digest_text from sys.statements_with_temp_tables s JOIN performance_schema.events_statements_summary_by_digest digest ON s.digest = digest.digest where s.db = 'live' AND s.tmp_tables_to_disk_pct > 0 \G;

Das nächste Statement enthält alle Queries, die langsamer als die meisten Queries sind.

select s.*, digest.digest_text from statements_with_runtimes_in_95th_percentile s JOIN performance_schema.events_statements_summary_by_digest digest ON s.digest = digest.digest where s.db = 'live'\G;

Das folgende Statement listet Queries, die einen Full Table Scan verursachen.

select ps.DIGEST_TEXT, sy.exec_count, sy.total_latency, sy.no_index_used_pct, sy.rows_examined_avg, sy.last_seen from sys.statements_with_full_table_scans as sy join performance_schema.events_statements_summary_by_digest as ps on ps.DIGEST = sy.digest where db = 'live' AND sy.no_index_used_pct = 100 order by exec_count desc\G

Das folgende Statement listet Queries, bei denen die Tabelle sortiert werden muss.

select  s.*, digest.digest_text from statements_with_sorting s JOIN performance_schema.events_statements_summary_by_digest digest ON s.digest = digest.digest where s.db = 'live'\G;

Mit folgendem Statement kann man ermitteln, welche Tabellen und Datenbankdateien am häufigsten geschrieben werden. Das könnte zum Beispiel eine Tabelle mit Logeinträgen sein.

SELECT file_name, count_read+count_write FROM performance_schema.file_summary_by_instance ORDER BY (count_read+count_write) DESC LIMIT 10;

Mit folgendem Statement kann man aktuell laufende Queries ermitteln. In der Regel sind das Langläufer.

SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, PROCESSLIST_DB AS db, PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, PROCESSLIST_STATE AS state, LEFT(PROCESSLIST_INFO, 80) AS info FROM performance_schema.threads WHERE PROCESSLIST_ID IS NOT NULL AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump') ORDER BY PROCESSLIST_TIME ASC;

Wichtig ist auch, ob der Wertebereich für eine automatisch generierte Spalte demnächst erschöpft ist. Das kann passieren, wenn man den Wertebereich für einen Primärschlüssel zu klein gewählt hat. Allerdings kann es auch nur bedeuten, dass in der Tabelle häufig Einträge hinzugefügt und wieder entfernt werden. Dann würde MySQL beim Erreichen der Obergrenze einfach wieder bei der Untergrenze anfangen.

select * from sys.schema_auto_increment_columns;

Möchte man die Anzahl der Zeilen für alle Tabellen ermitteln, muss man kein „select count“ auf jeder Tabelle ausführen. Diese Information ist auch im „Information Schema“ verfügbar. Das Information Schema beschreibt die Struktur aller Datenbanken, Tabellen und Spalten. Das Statement liefert aber nur für Tabellen sinnvolle Aussagen. Bei Views kann man dies nicht ermitteln, da diese ja erst bei einer Abfrage generiert werden.

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'live';

Eine Liste mit allen aktuellen Verbindungen inklusive der aktuell ausgeführten Query (falls eine ausgeführt wird) erhält man über folgendes Statement.

show full processlist;

Es ist auch möglich, alle ausgeführten Queries zu loggen. Aber Vorsicht: Auf einem produktiven System können die Logdateien schnell sehr groß werden. Deshalb nutze ich dieses Logging nur in Ausnahmefällen und auch dann nur für einige wenige Sekunden. Auf alle Fälle darf man nicht vergessen, es wieder zu deaktivieren.

Zunächst gebe ich aus, ob das Logging in eine Datei erfolgen würde, wo diese hingeschrieben wird und wie der aktuelle Status ist.

select @@log_output;
select @@general_log_file;
select @@general_log;

Mit folgenden Statements aktiviere und deaktiviere ich das Query Logging für kurze Zeit und prüfe am Ende, ob ich es wirklich wieder deaktiviert habe.

SET GLOBAL general_log = 1;
 
SET GLOBAL general_log = 0;
select @@general_log;

All diese Abfragen haben den Zweck, mögliche Performance Probleme zu ermitteln, aufwendige Queries umzubauen, Indizes zu definieren, etc. Nachdem eine Optimierung stattgefunden hat, kann man die Statistiken im Performance Schema über folgendes Statement zurücksetzen.

use sys
CALL sys.ps_truncate_all_tables(FALSE);

InnoDB Tuning

Neben den eigentlichen Queries kann man auch InnoDB tunen. Idealerweise passen die am häufigsten genutzten Tabellen komplett in den Hauptspeicher, um teure Zugriffe auf die Disk zu vermeiden. Generell gilt, dass auf einem MySQL Server keine weiteren Anwendungen laufen sollten (außer vielleicht ein periodisches Backupskript). Dann kann man mehr oder weniger den gesamten Hauptspeicher MySQL zur Verfügung stellen.

Im Netz gibt es unendlich viele Tuning Tipps. Wichtig dabei ist genau zu schauen, ob es wirklich um InnoDB Tuning geht oder ob sich die Angaben auf MyISAM beziehen. Der Query Cache zum Beispiel ist einzig für MyISAM gedacht und sollte dementsprechend vollständig per Konfiguration deaktiviert werden.

query_cache_type=0
query_cache_limit=1M
query_cache_size=0

Das Tuning der InnoDB Parameter ist eine Kunst und die erste Anlaufstelle sollte das MySQLTuner Skript sein. Dieses Skript nutzt die Metriken im Performance Schema sowie Informationen über den verfügbaren Hauptspeicher, um sinnvolle Größen für InnoDB Parameter vorzuschlagen. Natürlich ist es in der eigenen Verantwortung, diese Parameter genau zu recherchieren und zu verstehen. Diese Arbeit kann das Skript nicht abnehmen.

Die wichtigsten Parameter für das Tuning sind:

max_allowed_packet=16M
thread_stack=192K
thread_cache_size=16

innodb_buffer_pool_size=16G
innodb_log_file_size=256M
sort_buffer_size=2M
tmp_table_size=64M
max_heap_table_size=64M
table_open_cache_instances=8
table_open_cache=5000

Die folgenden Parameter steuern, wie viele Clients gleichzeitig eine Verbindung aufbauen können.

max_connections=800
max_user_connections=780

Fazit

Über die letzten Jahre hat sich also viel MySQL Wissen bei mir angesammelt und ich hoffe, dass vielleicht die eine oder andere Sache hilfreich ist. MySQL ist ein stabiler SQL Server. Natürlich kann auch MySQL keine Wunder vollbringen und unendlich skalieren. Dazu braucht es dann anderer Konzepte wie Sharding oder redundante Datenhaltung für verschiedene Einsatzzwecke (inkl. NoSQL). Mit ein wenig Hintergrundwissen kommt man aber schon recht weit.

Dieser Beitrag wurde am 26.03.2021 um 21:56in der Kategorie Software Engineering veröffentlicht. Kommentare können per RSS 2.0 Feed abonniert werden. Sie können ein Kommentar schreiben oder einen Trackback von ihrer Seite hinterlassen.

4 Kommentare to “MySQL Lektionen der letzten 5 Jahre”

1. Matin sagt:

   28. März 2021 um 11:09 Uhr

   Und ist der Zugriff auf die Datenbank in der jeweiligen Applikation auch immer schön gekappselt, damit die Applikationsentwickler sich nicht darum kümmern müssen, wo die Daten herkommen und hingehen? (Und die Datenbank auch ohne großen Aufwand ausgetauscht werden könnte)

2. Sebastian sagt:

   27. Mai 2021 um 22:01 Uhr

   @Matin: Nein, natürlich nicht. Ich habe es nie erlebt, dass das DBMS in einem Projekt ausgetauscht wurde.

   Ansonsten gilt, dass alle gängigen Programmiersprachen ihre DB Frameworks mitbringen. Im Bereich Java / JVM ist dies JPA (früher Hibernate) und im Bereich Python SQLAlchemy. Da noch groß was drumrum zu erfinden, ist eigentlich nicht nötig.

3. Matin sagt:

   30. Mai 2021 um 13:43 Uhr

   Diese Kapselung hat zwei Gründe:

   Der primäre ist die Austauschbarkeit, die vielleicht für den Produktivbetrieb nicht sonderlich relevant ist, sehr wohl aber für die Tests. Tests gegen eine Produktivdatenbank zu fahren, kann im besten Fall nervig, im schlimmsten Falle katastrophal sein.

   Der sekundäre Grund ist mir erst vor einigen Tagen klar geworden: Die Datenbank sollte gekapselt sein, damit die Entwickler nicht auf die Idee kommen, sie zu nutzen. Jedenfalls nicht anders als von MIR geplant.

4. Sebastian sagt:

   31. Mai 2021 um 20:14 Uhr

   Wegen Austauschbarkeit bei Tests hätte ich dir vor einigen Jahren noch Recht gegeben, aber wenn man nicht gerade ein sehr komplexes DBMS wie Oracle oder MS SQL Server verwendet, geht heute der Trend ganz klar in Richtung Test Container. Man verwendet für den Test dann das gleiche DBMS, wie in Produktion. Spannend sind in diesem Zusammenhang auch spezielle Testbibliotheken wie PyMongo, die 95% der DB Funktionalität nachbilden. Dann kannst du einen Unit Test sogar gegen die DB laufen lassen und musst die DB Schicht nicht aufwendig wegmocken und musst auch nicht mühseelig Testcontainer starten.

   Den zweiten Grund kann ich nicht nachvollziehen, da ich bisher nur mit Code Basen zu tun hatte, bei denen alle Entwickler an allen Stellen ändern können, sollen und müssen. Temporäre Entwickler, die nur mal für kurze Zeit im Code rumfuschen, hatte ich so nicht.

   Ein weiteres Problem mit DB Abstraktionsebenen war immer, dass die gut für geringes Datenvolumen sind, aber an den Stellen, an denen es wirklich drauf ankommt, müssen dann sowieso optimierte Abfragen her. Dadurch entsteht dann entweder ein Wildwuchs im DB Layer oder alle benutzen gleich ihre eigenen Abfragepläne.