Hinweise zu MySQL: Unterschied zwischen den Versionen

Aus SiteparkWiki
Zur Navigation springen Zur Suche springen
 
(19 dazwischenliegende Versionen von 4 Benutzern werden nicht angezeigt)
Zeile 1: Zeile 1:
 +
=Hinweise zu MySQL und MariaDB=
 +
==Allgemeines==
 +
Für die Konfiguration der Datenbank (unabhängig davon, welcher Hersteller verwendet wird), sind teilweise Standardsetzungen nicht hilfreich, da das von Sitepark für den IES entwickelte ER-Modell einen generischen Ansatz hat und teilweise über die dritte Normalform hinausgeht (siehe https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)). Ebenso sind Standardoptimierungen in den Ausprägungen der von den unterschiedlichen Linux-Distributionen verwendeten Default-Einstellungen teilweise kontraproduktiv.
 +
 
==Initialisierung==
 
==Initialisierung==
−
Es ist wichtig, dass ein Root-Passwort für den MySQL-Server gesetzt wird
+
Der IES verwaltet "seine" Datenbanken (grundsätzlich eine DB je Mandant) selbständig. D.h. er legt sie an, kümmert sich bei Updates um Änderungen des Schemas und führt bei Bedarf Optimierungen durch. Dazu kann grundsätzlich der Datenbank-root-User verwendet werden.
−
mysqladmin password geheim
+
 
 +
Es wird empfohlen für die Verwaltung der Datenbanken vom IES einen eigenen Nutzer mit "DB-root-Rechten" anzulegen.
 +
CREATE USER 'siteparkroot' IDENTIFIED BY 'PASSWORD';
 +
GRANT ALL ON *.* TO 'siteparkroot'@localhost IDENTIFIED BY 'PASSWORD' with grant option;
 +
GRANT ALL ON *.* TO 'siteparkroot'@127.0.0.1 IDENTIFIED BY 'PASSWORD' with grant option;
 +
FLUSH PRIVILEGES;
 +
 
 +
Bei Nutzung des "normalen" Datenbank-root-User ist es - insbesondere bei MariaDB, ggf. auch zukünftig bei MySQL und natürlich immer abhängig von der Implementierung der Datenbank im jeweiligen Linux-Derivat - zwingend notwendig, dass der root-User sich nicht nur über das unix_socket anmelden kann, sondern auch für den root-Nutzer ein "echtes" Passwort vergeben wird.
  
−
Unter Debian müssen ggf. für den MySQL root-Nutzer noch weitere Rechte gesetzt werden
+
Beispielhaft hier die Vergabe eines Passwortes für "root" bei einer Standard-MariaDB-10.6-Installation aus den Repositories von mariadb.org (vgl. https://mariadb.com/kb/en/authentication-from-mariadb-104/):
−
  mysql -u root -pgeheim mysql> GRANT ALL ON *.* TO 'root'@'localhost.localdomain' IDENTIFIED BY 'geheim' WITH GRANT OPTION;
+
 
 +
<div class="note">
 +
  mysql
 +
MariaDB> set password = password("MEINGEHEIMESPASSWORT");
 +
</div>
 +
 
 +
Nun kann sich der root-User auf der Konsole über das Unix-Socket und auch über die TCP-Verbindung verbinden. Die TCP-Verbindung kann auch auf der Konsole mittels <pre>mysql --protocol=TCP -u root -p"MEINGEHEIMESPASSWORT"</pre> erzwungen und getestet werden.
  
 
==Konfiguration==
 
==Konfiguration==
 +
===Grundsätzliches===
 +
Die Verbindung zu MySQL über den TCP-Port muss freigegeben werden. Lokale Sockets funktionieren '''nicht''' (siehe oben).
 +
 
<div class="note">
 
<div class="note">
 
Wie alle Dienste eines Systems, muss auch bei MySQL die Konfiguration regelmäßig geprüft werden.
 
Wie alle Dienste eines Systems, muss auch bei MySQL die Konfiguration regelmäßig geprüft werden.
Zeile 12: Zeile 32:
 
</div>
 
</div>
  
−
Die Verbindung zu MySQL über den TCP-Port muss freigegeben werden. Lokale Sockets funktionieren '''nicht'''.
+
Die Netzwerkbindungen müssen wie folgt (vielfach Standard bei Debian/Ubuntu/MariaDB/MySQL) konfiguriert sein:
−
 
 
−
Hierzu müssen folgende Setzungen mind. erfüllt sein:
 
 
  #skip-networking
 
  #skip-networking
  
Zeile 21: Zeile 39:
 
  bind-address = 127.0.0.1
 
  bind-address = 127.0.0.1
  
−
'''SuSE Linux Enterprise Server 11 - Service Pack 1'''
+
'''Bekannte Besonderheit: SuSE Linux Enterprise Server 11 - Service Pack 1'''
  
 
Mit dem Service Pack 1 kann der IES keine Verbindung zur Datenbank mehr herstellen, wenn ''bind-address'' mit einer IP-Adresse angegeben wurde. Das Problem kann gelöst werden, indem die ''bind-address'' Konfiguration auskommentiert wird.
 
Mit dem Service Pack 1 kann der IES keine Verbindung zur Datenbank mehr herstellen, wenn ''bind-address'' mit einer IP-Adresse angegeben wurde. Das Problem kann gelöst werden, indem die ''bind-address'' Konfiguration auskommentiert wird.
Zeile 27: Zeile 45:
 
Ab SLES11 ist es möglich, dass in der /etc/hosts-Datei standardmäßig localhost nur auf ::1 gebunden wird. Dann wird die MySQL-Administration mit dem IES nicht funktionieren. Es muss localhost (auch) auf eine IPv4-Adresse (127.0.0.1) gebunden werden.
 
Ab SLES11 ist es möglich, dass in der /etc/hosts-Datei standardmäßig localhost nur auf ::1 gebunden wird. Dann wird die MySQL-Administration mit dem IES nicht funktionieren. Es muss localhost (auch) auf eine IPv4-Adresse (127.0.0.1) gebunden werden.
  
 +
===Besonderheiten für den IES===
 +
Da der IES den transaktionssicheren Datenbanktyp ''InnoDB'' verwendet empfehlen wir Anpassungen an einigen Optionen. In der Standard-MariaDB-Installation empfehlen wir eine Konfigurations-Datei anzulegen, die alle Paramenter bündelt. Unter Debian/Ubuntu bietet sich z.B. <code>/etc/mysql/mariadb.conf.d/50-sitepark.cnf</code>an.
  
 +
Bitte wählen Sie hier Größenordnungen, die auf Ihre Hardware abgestimmt sind. Wenn Sie unsicher sind welche Setzungen zu verwenden sind, sprechen Sie uns bitte an. Wir werden Ihnen dann entsprechende Erfahrungswerte nennen können.
  
−
Da der IES den transaktionssicheren Datenbanktyp ''InnoDB'' verwendet empfehlen sich Anpassungen an folgenden Opionen (<code>my.cnf</code>):
+
<pre>
 +
[mysqld]
 +
disable-log-bin                          # sollten Sie Replikation oder Clustering nutzen, geht das natürlich nicht
 +
slow_query_log          = ON            # diese Setzung kann zu Debugging-Zwecken hilfreich sein
  
−
Bitte wählen Sie hier Größenordnungen, die auf Ihre Hardware abgestimmt sind. Wenn Sie unsicher sind welche Setzungen zu verwenden sind, sprechen Sie uns bitte an. Wir werden Ihnen dann entsprechende Erfahrungswerte nennen können.
+
character-set-server    = utf8mb4
−
# Größe des MySQL-Keys (Optimiert die Suche in MyISAM Daten)
+
collation-server        = utf8mb4_general_ci
−
key_buffer              = 256M
 
−
bzw.
 
−
key_buffer_size        = 256M
 
  
−
# MySQL-Paket-Größen gegen an, wie groß ein Statement werden darf (z.B. bei optimierten Dumps)
+
max_allowed_packet      = 256M
−
max_allowed_packet      = 16M
+
max_connections        = 150
 +
max_heap_table_size    = 512M
 +
tmp_table_size          = 512M
  
−
# Für die vom System erzeugten temp. Tabellen muss ausreichend Speicherkapazität definiert werden. Hierzu ist folgende Setzung notwendig
+
query_cache_limit      = 64M
−
max_heap_table_size    = 256M
+
query_cache_size        = 0
 +
query_cache_type        = 0
  
−
# Alle Daten aller InnoDB-Datenbanken in einer Datei (<code>ibdata</code>) gespeichert werden,
+
join_buffer_size        = 32M
−
# kann diese initial sofort entsprechend eingestellt werden. Die stete Vergrößerung kostet unnötige Resourcen
 
−
# Diese Option kann nachdem bereits Datenbanken des Typs InnoDB angelegt wurden nicht mehr verändert werden!
 
−
innodb_data_file_path = ibdata1:3G:autoextend
 
−
innodb_autoextend_increment = 512
 
  
−
  # Cache-Setzungen für InnoDB. Wählen Sie hier eine Poolgröße, die nicht größer als 50-80% des Speichers ist.
+
innodb_flush_log_at_trx_commit = 1      # https://mariadb.com/docs/reference/es/system-variables/innodb_flush_log_at_trx_commit/
−
# Ein Richtwert kann als Root-User über die gesamte Datengröße aller Datenbanken (+10-20%) ermittelt werden:
+
</pre>
−
# SELECT concat( round( sum( data_length  + index_length) / ( 1024 * 1024 ) , 2 ) , ' MB' ) TotalSize FROM information_schema.TABLES WHERE ENGINE='InnoDB';
+
Damit auch bei großen Datenmengen im System der IES maximal performant mit den Daten arbeiten kann, ist es notwendig, möglichst viel RAM im Parameter ''innodb_buffer_pool_size'' für die Nutzung der Datenbank zur Verfügung zu stellen. So können unnötige Zugriffe auf den Festspeicher vermieden werden. Ein Richtwert kann als root-User über die gesamte Datengröße aller Datenbanken ermittelt werden:
−
innodb_buffer_pool_size=2G
+
  SELECT concat( round( sum( data_length  + index_length) / ( 1024 * 1024 ) , 2 ) , ' MB' ) TotalSize FROM information_schema.TABLES WHERE ENGINE='InnoDB';
−
innodb_additional_mem_pool_size=20M
+
Beachten Sie hierbei, dass die Daten- und Indexmenge im Laufe des IES-Betriebs kontinuiertlich steigen wird. Im Rahmen des Montiorings und der sich daraus ergebenden Skalierungsnotwendigkeit ist dieser Wert immer an die aktuellen Gegebenheiten anzupassen.
 +
<pre>
 +
innodb_buffer_pool_size         = 8G    # https://mariadb.com/docs/reference/es/system-variables/innodb_buffer_pool_size/
  
−
# Open-Files und Open-Tables Setzungen. Hierzu ermitteln Sie am besten die Anzahl aller Tabellen im System und wählen entsprechend
+
innodb_log_buffer_size          = 32M
−
# etwas höhere Werte:
+
innodb_log_file_size            = 512M
−
# SELECT count(TABLE_NAME) FROM information_schema.TABLES;
 
−
# bzw.
 
−
# SHOW OPEN TABLES;
 
−
open_files_limit              = 6000
 
−
table_definition_cache        = 1000
 
−
table_open_cache              = 1000
 
  
−
# Weiterhin macht es unter Linux-Systemen durchaus Sinn den ''OS-Level-Cache'' zu umgehen um nicht "doppelt" zu cachen:
+
innodb_open_files              = 1000
−
# Liegen die InnoDB-Daten-Dateien in einem SAN, sollte laut MySQL auf diese Setzung verzichtet werden
 
−
innodb_flush_method=O_DIRECT
 
  
−
# Um im Zeichensatzbereich für jede Anwendung Klarheit darüber zu schaffen, dass sie UTF-8 als Zeichensatz verwendet, sind folgende Setzungen empfohlen
+
innodb_buffer_pool_dump_at_shutdown    = OFF
−
# im [mysqld]-Bereich
+
innodb_buffer_pool_load_at_startup      = OFF
−
character-set-server = utf8
 
−
# im [client]-Bereich
 
−
default-character-set = utf8
 
  
 +
innodb_temp_data_file_path      = ibtmp1:32M:autoextend
  
 +
table_open_cache        = 1000
 +
open_files_limit        = 6000
 +
table_definition_cache  = 1000
 +
</pre>
 +
Gerade der folgende Parameter ''optimizer_use_condition_selectivity'' MUSS auf 1 gesetzt werden. Der Standardwert 4 (Optimierung mittels Histogrammen) führt bei der Menge an Joins und der temporär erzeugten Tabellen, die der IES ausführt bzw. erzeugt, dazu, dass das DB-System innerhalb kürzester Zeit Unmengen an RAM benötigt und unbedienbar wird.
 +
<pre>
 +
optimizer_use_condition_selectivity    = 1 # https://mariadb.com/docs/reference/mdb/system-variables/optimizer_use_condition_selectivity/
 +
</pre>
 +
Weiterhin macht es unter Linux-Systemen durchaus Sinn den ''OS-Level-Cache'' zu umgehen um nicht "doppelt" zu cachen. Liegen die InnoDB-Daten-Dateien in einem SAN, sollte laut MySQL auf diese Setzung verzichtet werden. Seit MariaDB 10.6 ist diese Setzung von ''innodb_flush_method'' eine Standardsetzung.
 +
<pre>
 +
innodb_flush_method=O_DIRECT
 +
</pre>
 +
Um im Zeichensatzbereich für jede Anwendung Klarheit darüber zu schaffen, dass sie UTF-8 als Zeichensatz verwendet, sind folgende Setzungen zu tätigen (für den Server ist die Setzung bereits oben definiert):
 +
<pre>
 +
[client]
 +
default-character-set = utf8mb4
 +
</pre>
 +
Damit eine konsistente Sicherung der Datenbank z.B. mittels eines File-Backups möglich ist, sind nicht die Datenbankdateien (meist in <code>/var/lib/mysql/*</code>) zu sichern, sondern regelmäßige Datenbankdumps mittels <code>mysqldump -uUSER -pPASSWORD --opt --skip-lock-tables --single-transaction DATABASE</code> durchzuführen. Die dump-Dateien können dann gzip-pt und gesichert werden. Damit die Dumps performant laufen können, ist folgende Setzung durchzuführen.
 +
<pre>
 +
[mysqldump]
 +
max_allowed_packet      = 256M
 +
</pre>
  
−
Weitere Informationen zur Optimierung finden Sie u.a. hier:
 
−
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
 
 
<noinclude>
 
<noinclude>
 
[[Kategorie:Installation| 400]]
 
[[Kategorie:Installation| 400]]
 
[[Kategorie:Administration und Konfiguration]]
 
[[Kategorie:Administration und Konfiguration]]
 
</noinclude>
 
</noinclude>

Aktuelle Version vom 10. Juli 2024, 08:46 Uhr

Hinweise zu MySQL und MariaDB

Allgemeines

Für die Konfiguration der Datenbank (unabhängig davon, welcher Hersteller verwendet wird), sind teilweise Standardsetzungen nicht hilfreich, da das von Sitepark für den IES entwickelte ER-Modell einen generischen Ansatz hat und teilweise über die dritte Normalform hinausgeht (siehe https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)). Ebenso sind Standardoptimierungen in den Ausprägungen der von den unterschiedlichen Linux-Distributionen verwendeten Default-Einstellungen teilweise kontraproduktiv.

Initialisierung

Der IES verwaltet "seine" Datenbanken (grundsätzlich eine DB je Mandant) selbständig. D.h. er legt sie an, kümmert sich bei Updates um Änderungen des Schemas und führt bei Bedarf Optimierungen durch. Dazu kann grundsätzlich der Datenbank-root-User verwendet werden.

Es wird empfohlen für die Verwaltung der Datenbanken vom IES einen eigenen Nutzer mit "DB-root-Rechten" anzulegen. 

CREATE USER 'siteparkroot' IDENTIFIED BY 'PASSWORD';
GRANT ALL ON *.* TO 'siteparkroot'@localhost IDENTIFIED BY 'PASSWORD' with grant option;
GRANT ALL ON *.* TO 'siteparkroot'@127.0.0.1 IDENTIFIED BY 'PASSWORD' with grant option;
FLUSH PRIVILEGES;

Bei Nutzung des "normalen" Datenbank-root-User ist es - insbesondere bei MariaDB, ggf. auch zukünftig bei MySQL und natürlich immer abhängig von der Implementierung der Datenbank im jeweiligen Linux-Derivat - zwingend notwendig, dass der root-User sich nicht nur über das unix_socket anmelden kann, sondern auch für den root-Nutzer ein "echtes" Passwort vergeben wird.

Beispielhaft hier die Vergabe eines Passwortes für "root" bei einer Standard-MariaDB-10.6-Installation aus den Repositories von mariadb.org (vgl. https://mariadb.com/kb/en/authentication-from-mariadb-104/): 

mysql
MariaDB> set password = password("MEINGEHEIMESPASSWORT");

Nun kann sich der root-User auf der Konsole über das Unix-Socket und auch über die TCP-Verbindung verbinden. Die TCP-Verbindung kann auch auf der Konsole mittels 

mysql --protocol=TCP -u root -p"MEINGEHEIMESPASSWORT"

erzwungen und getestet werden.

Konfiguration

Grundsätzliches

Die Verbindung zu MySQL über den TCP-Port muss freigegeben werden. Lokale Sockets funktionieren nicht (siehe oben).

Wie alle Dienste eines Systems, muss auch bei MySQL die Konfiguration regelmäßig geprüft werden. Die aktuellen Parameter müssen ermittelt werden und Einstellungen entsprechend angepasst werden. Gerade die Setzungen innodb_buffer_pool_size und table_open_cache u.ä. müssen mit wachsenden Datenbestand überprüft werden!

Die Netzwerkbindungen müssen wie folgt (vielfach Standard bei Debian/Ubuntu/MariaDB/MySQL) konfiguriert sein: 

#skip-networking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

Bekannte Besonderheit: SuSE Linux Enterprise Server 11 - Service Pack 1

Mit dem Service Pack 1 kann der IES keine Verbindung zur Datenbank mehr herstellen, wenn bind-address mit einer IP-Adresse angegeben wurde. Das Problem kann gelöst werden, indem die bind-address Konfiguration auskommentiert wird.

Ab SLES11 ist es möglich, dass in der /etc/hosts-Datei standardmäßig localhost nur auf ::1 gebunden wird. Dann wird die MySQL-Administration mit dem IES nicht funktionieren. Es muss localhost (auch) auf eine IPv4-Adresse (127.0.0.1) gebunden werden.

Besonderheiten für den IES

Da der IES den transaktionssicheren Datenbanktyp InnoDB verwendet empfehlen wir Anpassungen an einigen Optionen. In der Standard-MariaDB-Installation empfehlen wir eine Konfigurations-Datei anzulegen, die alle Paramenter bündelt. Unter Debian/Ubuntu bietet sich z.B. /etc/mysql/mariadb.conf.d/50-sitepark.cnfan.

Bitte wählen Sie hier Größenordnungen, die auf Ihre Hardware abgestimmt sind. Wenn Sie unsicher sind welche Setzungen zu verwenden sind, sprechen Sie uns bitte an. Wir werden Ihnen dann entsprechende Erfahrungswerte nennen können. 

[mysqld]
disable-log-bin                          # sollten Sie Replikation oder Clustering nutzen, geht das natürlich nicht
slow_query_log          = ON             # diese Setzung kann zu Debugging-Zwecken hilfreich sein

character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

max_allowed_packet      = 256M
max_connections         = 150
max_heap_table_size     = 512M
tmp_table_size          = 512M

query_cache_limit       = 64M
query_cache_size        = 0
query_cache_type        = 0

join_buffer_size        = 32M

innodb_flush_log_at_trx_commit  = 1      # https://mariadb.com/docs/reference/es/system-variables/innodb_flush_log_at_trx_commit/

Damit auch bei großen Datenmengen im System der IES maximal performant mit den Daten arbeiten kann, ist es notwendig, möglichst viel RAM im Parameter innodb_buffer_pool_size für die Nutzung der Datenbank zur Verfügung zu stellen. So können unnötige Zugriffe auf den Festspeicher vermieden werden. Ein Richtwert kann als root-User über die gesamte Datengröße aller Datenbanken ermittelt werden: 

 SELECT concat( round( sum( data_length  + index_length) / ( 1024 * 1024 ) , 2 ) , ' MB' ) TotalSize FROM information_schema.TABLES WHERE ENGINE='InnoDB';

Beachten Sie hierbei, dass die Daten- und Indexmenge im Laufe des IES-Betriebs kontinuiertlich steigen wird. Im Rahmen des Montiorings und der sich daraus ergebenden Skalierungsnotwendigkeit ist dieser Wert immer an die aktuellen Gegebenheiten anzupassen. 

innodb_buffer_pool_size         = 8G     # https://mariadb.com/docs/reference/es/system-variables/innodb_buffer_pool_size/

innodb_log_buffer_size          = 32M
innodb_log_file_size            = 512M

innodb_open_files               = 1000

innodb_buffer_pool_dump_at_shutdown     = OFF
innodb_buffer_pool_load_at_startup      = OFF

innodb_temp_data_file_path      = ibtmp1:32M:autoextend

table_open_cache        = 1000
open_files_limit        = 6000
table_definition_cache  = 1000

Gerade der folgende Parameter optimizer_use_condition_selectivity MUSS auf 1 gesetzt werden. Der Standardwert 4 (Optimierung mittels Histogrammen) führt bei der Menge an Joins und der temporär erzeugten Tabellen, die der IES ausführt bzw. erzeugt, dazu, dass das DB-System innerhalb kürzester Zeit Unmengen an RAM benötigt und unbedienbar wird. 

optimizer_use_condition_selectivity     = 1 # https://mariadb.com/docs/reference/mdb/system-variables/optimizer_use_condition_selectivity/

Weiterhin macht es unter Linux-Systemen durchaus Sinn den OS-Level-Cache zu umgehen um nicht "doppelt" zu cachen. Liegen die InnoDB-Daten-Dateien in einem SAN, sollte laut MySQL auf diese Setzung verzichtet werden. Seit MariaDB 10.6 ist diese Setzung von innodb_flush_method eine Standardsetzung. 

innodb_flush_method=O_DIRECT

Um im Zeichensatzbereich für jede Anwendung Klarheit darüber zu schaffen, dass sie UTF-8 als Zeichensatz verwendet, sind folgende Setzungen zu tätigen (für den Server ist die Setzung bereits oben definiert): 

[client]
default-character-set = utf8mb4

Damit eine konsistente Sicherung der Datenbank z.B. mittels eines File-Backups möglich ist, sind nicht die Datenbankdateien (meist in /var/lib/mysql/*) zu sichern, sondern regelmäßige Datenbankdumps mittels mysqldump -uUSER -pPASSWORD --opt --skip-lock-tables --single-transaction DATABASE durchzuführen. Die dump-Dateien können dann gzip-pt und gesichert werden. Damit die Dumps performant laufen können, ist folgende Setzung durchzuführen. 

[mysqldump]
max_allowed_packet      = 256M
Abgerufen von „https://wiki.sitepark.com/index.php?title=Hinweise_zu_MySQL&oldid=10705“