MySQL Tuning

siehe auch:

Ab MySQL 8.0

8.5.8 Optimizing InnoDB Disk I/O
- Adjust the flush method:
  - In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.
  - fsync or 0: InnoDB verwendet den Systemaufruf fsync(), um sowohl die Daten- als auch die Protokolldateien zu leeren. fsync ist die Standardeinstellung.
  - O_DSYNC or 1: InnoDB verwendet O_SYNC, um die Protokolldateien zu öffnen und zu leeren, und fsync(), um die Datendateien zu leeren. InnoDB verwendet O_DSYNC nicht direkt, da es bei vielen Unix-Varianten Probleme damit gegeben hat.
  - littlesync or 2: Diese Option wird für interne Leistungstests verwendet und wird derzeit nicht unterstützt. Benutzung auf eigene Gefahr.
  - nosync or 3: Diese Option wird für interne Leistungstests verwendet und wird derzeit nicht unterstützt. Benutzung auf eigene Gefahr.
  - O_DIRECT or 4: InnoDB verwendet O_DIRECT (oder directio() auf Solaris), um die Datendateien zu öffnen, und verwendet fsync(), um sowohl die Daten- als auch die Protokolldateien zu leeren. Diese Option ist in einigen GNU/Linux-Versionen, FreeBSD und Solaris verfügbar.
  - O_DIRECT_NO_FSYNC: InnoDB verwendet O_DIRECT beim Flush-I/O, überspringt jedoch den Systemaufruf fsync() nach jedem Schreibvorgang.

Vor MySQL 8.0.14 ist diese Einstellung nicht für Dateisysteme wie XFS und EXT4 geeignet, die einen fsync()-Systemaufruf erfordern, um Änderungen an Dateisystem-Metadaten zu synchronisieren. Wenn Sie sich nicht sicher sind, ob Ihr Dateisystem einen fsync()-Systemaufruf erfordert, um Änderungen an den Metadaten des Dateisystems zu synchronisieren, verwenden Sie stattdessen O_DIRECT.

Ab MySQL 8.0.14 wird fsync() aufgerufen, nachdem eine neue Datei erstellt, die Dateigröße erhöht und eine Datei geschlossen wurde, um sicherzustellen, dass Metadatenänderungen des Dateisystems synchronisiert werden. Der Systemaufruf fsync() wird weiterhin nach jedem Schreibvorgang übersprungen.

Datenverlust ist möglich, wenn sich Redo-Log-Dateien und Datendateien auf unterschiedlichen Speichergeräten befinden und ein unerwartetes Beenden auftritt, bevor Datendatei-Schreibvorgänge aus einem nicht batteriegepufferten Geräte-Cache geleert werden. Wenn Sie unterschiedliche Speichergeräte für Redo-Log-Dateien und Datendateien verwenden oder verwenden möchten und sich Ihre Datendateien auf einem Gerät mit einem nicht batteriegepufferten Cache befinden, verwenden Sie stattdessen O_DIRECT.

siehe auch:
- sysvar_innodb_use_fdatasync
  - Auf Plattformen, die fdatasync()-Systemaufrufe unterstützen, ermöglicht die Aktivierung der Variable innodb_use_fdatasync die Verwendung von fdatasync() anstelle von fsync()-Systemaufrufen für Betriebssystem-Flushes. Ein fdatasync()-Aufruf leert keine Änderungen an Dateimetadaten, es sei denn, sie werden für den nachfolgenden Datenabruf benötigt, was einen potenziellen Leistungsvorteil bietet.
  - Eine Teilmenge von innodb_flush_method-Einstellungen wie fsync, O_DSYNC und O_DIRECT verwendet fsync()-Systemaufrufe. Die Variable innodb_use_fdatasync ist anwendbar, wenn diese Einstellungen verwendet werden.

Beispiel MySQL 8.0 mit 16GB RAM

Es sollten von der MySQL-DB nur max. 84% des RAM belegt werden.

innodb_buffer_pool_size + ( max_allowed_packet * max_connections ) <= 84% RAM

beide Konfigurationen belegen so ziehmlich die gleiche Menge RAM:

max_allowed_packet              = 16M
max_connections                 = 70
innodb_buffer_pool_size         = 12G

max_allowed_packet              = 64M
max_connections                 = 145
innodb_buffer_pool_size         = 4G

Als erstes sollte man ermitteln, wie groß das benötigte "max_allowed_packet" sein muß.
- Hier gilt die Regel, nur so groß wie nötig aber so klein wie möglich.
Als zweites sollte man ermitteln, wieviele Verbindungen maximal zur Datenbank gleichzeitig gehalten werden sollen (max_connections).
- Hier gilt die Regel, nur soviele wie nötig aber sowenig wie möglich.
Als letztes kann man dann damit errechnen, wieviel Speicher noch für den "innodb_buffer_pool_size" zur verfügung steht:
- "RAM" * 0,84 - ( max_allowed_packet * max_connections ) = innodb_buffer_pool_size
- 16384MB * 0,84 - ( 16M * 70 ) = 12642,56 ~ 12GB
- 16384MB * 0,84 - ( 64M * 145 ) = 4482,56 ~ 4GB

Bis MySQL 5.7

Bufferpool auf ca. 50% des RAMs setzen, mindestens jedoch auf 8MB. Auf garkeinen Fall auf mehr als 80% des RAMs erhöhen!
Logdateien auf die Größe von Bufferpool setzen.
Logpuffer auf mind. 8MB setzen.
Beim Datenimport in InnoDB müssen Sie darauf achten, dass MySQL nicht im Autocommit-Modus läuft, da dieser bei jedem Insert die Logs auf die Festplatte zurückschreibt. Oder beim dumpen dem mysqldump die Option –opt mitgeben, das beschleunigt das importieren in eine INNODB drastisch, auch ohne Autocommit abzuschalten.
Verwenden Sie zum Leeren einer Tabelle DROP TABLE und CREATE TABLE, aber nicht DELETE FROM tbl_name.
Mit RollBack-Operationen sehr sparsam und vorsichtig umgehen, da sie ohne Puffer direkt auf der Platte arbeiten. Dadurch sind sie ca. 30 mal langsamer als die dazugehörigen INSERT-Operationen.
Wenn Sie oft wiederkehrende Anfragen auf Tabellen haben, die sich nur selten ändern, nutzen Sie den Query-Cache:

[mysqld]
query_cache_type = ON
query_cache_size = 10M

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

# echo "SHOW VARIABLES" | mysql -u$USER -p

/etc/mysql/conf.d/tuning.cnf

neu

#
# tuning
#
[mysqld]
#
# key_buffer=128M
# table_cache=512
# sort_buffer=15M
#
key_buffer = 256M
# key buffer bei Bedarf ehoehen
table_cache = 512
#sort_buffer = 32M
#sort_buffer_size = 5M
#read_buffer_size = 128K
# key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
#
#innodb_buffer_pool_size = 3092M
#innodb_log_file_size = 512M
#innodb_flush_log_at_trx_commit = 2
#
#skip-networking
#max_allowed_packet= 64M
#thread_stack= 128K
#thread_cache_size = 64K
#thread_concurrency= 4
#concurrent_insert = 2
#low_priority_updates= 1
max_connect_errors = 1000
max_connections = 1000
connect_timeout = 10
#read_rnd_buffer_size= 4M
#read_buffer_size= 4M
tmp_table_size= 256M
max_heap_table_size = 256M
query_cache_limit = 256M
query_cache_size= 256M
query_cache_type= 1

alt

#
# tuning
#
[mysqld]
#
# Empfehlung für 2GB RAM - systeme
# key_buffer=128M
# table_cache=512
# sort_buffer=15M
#
key_buffer = 1024M
table_cache = 7000
sort_buffer = 128M
#
innodb_buffer_pool_size = 16000MB
innodb_log_file_size = 256MB
innodb_flush_log_at_trx_commit = 2
#
#skip-networking
max_allowed_packet= 64M
thread_stack= 128K
thread_cache_size = 64K
thread_concurrency= 4
concurrent_insert = 2
low_priority_updates= 1
max_connect_errors = 2500
max_connections = 2500
connect_timeout = 10
read_rnd_buffer_size= 4M
read_buffer_size= 4M
tmp_table_size= 512M
max_heap_table_size = 512M
query_cache_limit = 512M
query_cache_size= 512M
query_cache_type= 1

Tuning

Beispiel mit:

Maschine mit 4G RAM;
einer 40GB-Partition auf der die DB liegt;

[mysqld]
server_id                      = 9999
report_host                    = hostname99

bind-address                   = 0.0.0.0
report-port                    = 3306
report-user                    = otto
basedir                        = /opt/mysql/server-5.6
datadir                        = /opt/mysql/server-5.6/data
pid_file                       = mysql.pid
log_bin                        = mysql-bin
relay_log                      = mysql-relay-bin
#master_info_repository         = TABLE
#relay_log_info_repository      = TABLE
relay_log_space_limit          = 4G

#gtid_mode                      = ON
#disable_gtid_unsafe_statements = 1
expire_logs_days               = 7
sync_binlog                    = 1
log_slave_updates              = 1
auto_increment_offset          = 1
auto_increment_increment       = 10
default-storage-engine         = innodb
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_fast_shutdown           = 0
#innodb_read_only               = 1
#slave-skip-errors              = 1062

# http://dev.mysql.com/doc/refman/5.1/de/blocked-host.html
max_connect_errors              = 1000
max_connections                 = 1000
connect_timeout                 = 10
interactive_timeout             = 60
wait_timeout                    = 60

#
# http://dev.mysql.com/doc/refman/5.1/de/replication-row-based.html
# http://dev.mysql.com/doc/refman/5.1/de/server-options.html
# http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html
# http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
#
# statement: anweisungsbasierte Replikation (Statement-Based Replication, SBR)
# row: datensatzbasierte Replikation (Row-Based Replication, RBR, gibt es seit Version 5.1.5)
# Standard vor MySQL 5.1.12 ist statement
# Standard ab MySQL 5.1.12 ist mixed
#
# http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
# Bei nichtdeterministischer Datenmodifikation ist nur "raw" zu verwenden!
binlog_format                   = row
#
# Gibt die maximale Größe eines datensatzbasierten Binärlogereignisses in Byte
# an. Datensätze werden zu Ereignissen zusammengefasst, die - sofern möglich -
# kleiner sind als die hier angegebene Größe.
# Der Wert sollte ein Vielfaches von 256 sein
# Standard vor MySQL 5.6 ist 1024
# Standard ab MySQL 5.6 ist 8192
binlog_row_event_max_size       = 8192
#
# ab MySQL 5.6.6 werden die Replikationsdaten per CRC32 überprüft

#==============================================================================#
#
# Tuning
#
#------------------------------------------------------------------------------#
### MyISAM
# http://dev.mysql.com/doc/refman/5.1/de/structured-system-variables.html
# key_buffer bei Bedarf ehoehen
# Memory in kB = key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections
# max_connections = Memory in kB - key_buffer_size - read_buffer_size - sort_buffer_size
# key_buffer_size = Memory in kB - (read_buffer_size + sort_buffer_size) * max_connections
# Abfrage der Variable:
# echo "SELECT @@key_buffer_size;" | mysql -t
key_buffer = 256M
table_cache = 512
max_connect_errors = 1000
max_connections = 1000
connect_timeout = 10
tmp_table_size= 256M
max_heap_table_size = 256M
query_cache_limit = 256M
query_cache_size= 256M
query_cache_type= 1
#
#------------------------------------------------------------------------------#
### INNODB
## MySQL-Optimierungen - Die wunderbare Welt von Isotopp
## http://blog.koehntopp.de/archives/1997-Die-InnoDB-Storage-Engine-Konfiguration.html
#
# Eine Größe von 1 MB bis 8 MB ist normal.
innodb_log_buffer = 8M
#
# Größe des InnoDB Buffer Pools
#   etwa 70-80% des Hauptspeichers,
#   auf einer Maschine mit 4G RAM
#   also etwa 3G
#
# (/etc/sysctl.conf: vm.swappiness = 0!)
innodb_buffer_pool_size = 3072M
#
# Anzahl der InnoDB Bufferpool Seiten
# die Dirty sein dürfen bevor ein
# Checkpoint erzwungen wird
#
# Default = 90, ok
innodb_max_dirty_pages_pct = 90
#
# Ändert man die Variablen innodb_log_files_in_group oder innodb_log_file_size
# wenn ib_logfile0 und ib_logfile1 schon existieren, wird InnoDB sich weigern
# zu starten und im Error-Log des Servers eine Meldung hinterlassen, die im
# wesentlichen sagt, daß die Größe von vorgefundenen Logfiles nicht mit der
# Größe der konfigurierten Logfiles übereinstimmt.
#
# Anzahl der ib_logfile0/ib_logfile1
innodb_log_files_in_group = 2
#
# Größe eines ib_logfile0/ib_logfile1
# Idealerweise zwischen 64 und 256 MB,
# maximal jedoch 4096 MB
innodb_log_file_size = 256M
#
# Soll InnoDB mit einer ibd-Datei pro Tabelle betrieben werden
innodb_file_per_table = 0
#
# Wie soll die Datei angelegt werden?
#   Tablespace wird hier als 1G große Datei angelegt.
innodb_data_file_path = "ibdata1:1024M:autoextend"
#
# etwa 1% bis 5% der gesamten Dateisystemgröße (in MB)
# bei einer 40GB-Partition sind es also ca. 512 - 2048
innodb_autoextend_increment = 1024
#
# Die Idee bei O_DSYNC und O_DIRECT ist, die Dateien des Redo-Log auf eine Weise
# zu öffnen,
# die den Puffermechanismus des Betriebssystems komplett ausschaltet - die
# Datenbank puffert Daten im innodb_buffer_pool und im Redo-Log selbst und es
# besteht gar keine Notwendigkeit für den File System Buffer Cache des
# Betriebssystems. Setzt man innodb_flush_method zum Beispiel auf O_DIRECT in
# Linux, wird InnoDB nur noch WRITE-Aufrufe durchführen, aber die Daten nicht
# mehr mit FLUSH auf die Platte zwingen. Dies ist auch nicht mehr notwendig,
# da ja jeder WRITE bei O_DIRECT ungepuffert direkt auf die Platte geht.
#
# Die Werte für innodb_flush_log_at_trx_commit haben folgende Bedeutungen:
# 1 -> ohne MySQL-Puffer (sehr langsam aber ACID-gerecht)
# 2 -> mit MySQL-Puffer (schnell)
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
#
# Zum berechnen des Wertes für innodb_thread_concurrency gibt es verschiedene
# Formeln, die einfachste lautet: "doppelte Anzahl der (echten) CPU-Kerne"
innodb_thread_concurrency = 8
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
#
# bei 40.000 InnoDB-Tabellen
# benötigt man ca. 20MB;
# im Normalfall niemals über 8MB
innodb_additional_mem_pool_size = 8M
#
#==============================================================================#

[server]
sql-mode=""

Performance Schema

getestet mit Ubuntu 14.04.5 LTS / mysqld 5.7.21

events_statements_history.sql

SELECT * FROM performance_schema.setup_consumers;
 
SET GLOBAL show_compatibility_56 = ON;
 
SELECT LEFT(digest_text, 64) AS query
, ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms
, ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms
, ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms
, MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) SECOND), 19)) AS first_seen
, MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) SECOND), 19)) AS last_seen
, COUNT(*) AS cnt
FROM performance_schema.events_statements_history
JOIN information_schema.global_status AS isgs
WHERE isgs.variable_name = 'UPTIME'
GROUP BY LEFT(digest_text,64)
ORDER BY tot_exec_ms DESC
LIMIT 10;

events_statements_history_long.sql

SELECT * FROM performance_schema.setup_consumers;
 
UPDATE performance_schema.setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';
SET GLOBAL show_compatibility_56 = ON;
 
SELECT LEFT(digest_text, 64) AS query
, ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms
, ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms
, ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms
, MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) SECOND), 19)) AS first_seen
, MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) SECOND), 19)) AS last_seen
, COUNT(*) AS cnt
FROM performance_schema.events_statements_history_long
JOIN information_schema.global_status AS isgs
WHERE isgs.variable_name = 'UPTIME'
GROUP BY LEFT(digest_text,64)
ORDER BY tot_exec_ms DESC
LIMIT 10;

mysqltuner.pl

http://dev.mysql.com/doc/refman/5.1/de/innodb-tuning.html
wget -O mysqltuner.pl http://mysqltuner.pl
- https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

> perl mysqltuner.pl --user root --pass geheim --host 192.168.1.1 --port 3306

oder

> touch ~/.my.cnf
> chmod 0600 ~/.my.cnf
> vi ~/.my.cnf
> perl mysqltuner.pl --defaults-file ~/.my.cnf

für Zugriffe über das Netz, hier muß der Option --forcemem die RAM-Größe (in MB) des DB-Systems mitgegeben werden, in diesem Fall sind es 4 GB:

> perl mysqltuner.pl --forcemem 4096 --forceswap 4096 --user root --pass geheim --host 192.168.1.1 --port 3306

Memory-Tabellen / Heap-Tables

Memory-Tabellen verschwinden beim Neustart der DBMS. Das bedeutet, das die Replikation nach dem Neustart eines Slaves kaputt ist, weil SQL-Operationen auf die Memory-Tabelle (die ja im Master noch existiert) auf dem Slave nicht ausgeführt werden können.

Und das bedeutet, es darf in dem Fall kein Slave-DBMS restartet werden.

Hier ist ein Artikel, in dem das Problem beschrieben wird: http://www.mysqlperformanceblog.com/2010/10/15/replication-of-memory-heap-tables/

Dort steht, das Memory-Tables in Verbindung mit Replikation nicht richtig funktionieren und das eine gute Lösung der Austausch der Memory-Tabellen gegen InnoDB-Tabellen ist.

Als einzigen Workaround könnte man den Tabellennamen in der Konfigurationsdatei als zu ignorieren deklarieren:

replicate-ignore-table          = DatenbankName.MemoryTabellenName

oder

replicate_wild_ignore_table     = DatenbankName.%