Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- mysql_tuning [2022-02-24 12:14:29] – [MySQL Tuning] manfred
+++ mysql_tuning [2026-05-22 10:14:22] (aktuell) – [Beispiel MySQL 8.0 mit 16GB RAM] manfred
@@ Zeile 1: / Zeile 1: @@
-===== MySQL Tuning =====
+====== MySQL Tuning ======
-siehe auch: [[::MySQL Performance Tuning]]
+siehe auch:
+  * [[https://github.com/major/MySQLTuner-perl]]
+    * ''> git clone https://github.com/major/MySQLTuner-perl.git''
+    * ''> ls -1 MySQLTuner-perl/mysqltuner.pl''
+    * ''MySQLTuner-perl/mysqltuner.pl''
+  * [[::MySQL - Benchmark]]
+  * [[::MySQL-Lasttest]]
+  * [[::MySQL Tuning]]
-==== Ab MySQL 8.0 ====
+===== Ab MySQL 8.0 =====
   * [[https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html|8.5.8 Optimizing InnoDB Disk I/O]]
@@ Zeile 31: / Zeile 38: @@
 Es sollten von der MySQL-DB nur max. 84% des RAM belegt werden.
-  innodb_buffer_pool_size + ( max_allowed_packet * max_connections ) <= 84% RAM
+  innodb_buffer_pool_size + ( max_allowed_packet * max_connections ) <= 70-84% RAM
 beide Konfigurationen belegen so ziehmlich die gleiche Menge RAM:
@@ Zeile 52: / Zeile 59: @@
-==== Bis MySQL 5.7 ====
+==== I/O-Optimierung ====
+<code bash>
+iostat -x 1 /dev/nvme0n1
+IOPS = r/s + w/s
+-4900 wkB/s → Write throughput
+-297  rkB/s → Read throughput
+-5200 IOPS
+innodb_io_capacity -> durchschnittliche IOPS des Datenträgers
+innodb_io_capacity_max -> maximale IOPS des Datenträgers
+innodb_redo_log_capacity = Schreibrate * 10 Minuten (abhängig von innodb_flush_method) = innodb_redo_log_capacity -> ca.~ 5–15% vom innodb_buffer_pool_size
+bei O_DSYNC und O_DIRECT sollte innodb_redo_log_capacity etwas größer sein (z.B.: 32G)
+aber bei innodb_fast_shutdown=0 kann innodb_redo_log_capacity wieder deutlich kleiner sein (z.B.: 8G)
+</code>
+<code bash>
+### bei O_DSYNC und O_DIRECT sollte innodb_redo_log_capacity etwas größer sein => innodb_redo_log_capacity=32G
+### aber wenn innodb_fast_shutdown = 0, dann kann innodb_redo_log_capacity deutlich kleiner => innodb_redo_log_capacity=8G
+### SET GLOBAL innodb_io_capacity=3000; SET GLOBAL innodb_io_capacity_max=8000; SET GLOBAL innodb_redo_log_capacity = 8 * 1024 * 1024 * 1024;
+innodb_io_capacity              = 3000  # wird von innodb_redo_log_capacity benötigt -> durchschnittliche IOPS des Datenträgers
+innodb_io_capacity_max          = 8000  # wird von innodb_redo_log_capacity benötigt -> maximale IOPS des Datenträgers
+innodb_redo_log_capacity        = 8G    # Schreibrate * 10 Minuten (abhängig von innodb_flush_method) = innodb_redo_log_capacity -> ca.~ 5–15% vom innodb_buffer_pool_size
+innodb_buffer_pool_instances    = 8
+join_buffer_size                = 2M
+tmp_table_size                  = 32M
+max_heap_table_size             = 32M
+### LRU Scan Depth bestimmt, wie weit InnoDB vorausschaut, um Platz zu schaffen
+### Zu klein: Threads warten auf freie Pages, „buffer pool free list“ wird knapp, spontane Flush-Spikes
+### Zu groß: hohe CPU-Last im Hintergrund, unnötige IO, keine echte Performanceverbesserung
+### echo "SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_size', 'innodb_lru_scan_depth', 'Innodb_page_size'); SHOW GLOBAL STATUS WHERE variable_name IN ('Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_dirty', 'Innodb_buffer_pool_wait_free', 'Innodb_page_size');" | mysql -t
+### Innodb_buffer_pool_wait_free  > 0 (muß "0" sein)                                 # innodb_lru_scan_depth & innodb_io_capacity erhöhen
+### Innodb_buffer_pool_pages_free ~ 0 (sollte mind. bei 1-5% vom Buffer Pool sein)   # innodb_lru_scan_depth & innodb_io_capacity erhöhen
+### echo "SET GLOBAL innodb_lru_scan_depth=1024;" | mysql
+### Innodb_buffer_pool_pages_total = Innodb_buffer_pool_size / Innodb_page_size
+### max. Innodb_buffer_pool_pages_free = Innodb_buffer_pool_pages_total /  20
+### min. Innodb_buffer_pool_pages_free = Innodb_buffer_pool_pages_total / 100
+### max. Innodb_buffer_pool_pages_free = 4194304 /  20 = ca. 210000 freie Pages
+### min. Innodb_buffer_pool_pages_free = 4194304 / 100 = ca.  42000 freie Pages
+#innodb_lru_scan_depth           = 256
+#innodb_lru_scan_depth           = 512
+innodb_lru_scan_depth           = 1024
+################################################################################
+innodb_page_cleaners            = 8
+innodb_flush_log_at_trx_commit  = 1
+sync_binlog                     = 0
+innodb_doublewrite_batch_size   = 64
+innodb_doublewrite_pages        = 128
+################################################################################
+</code>
+<file bash /etc/mysql/conf.d/max_flushing.cnf>
+[mysqld]
+### https://www.percona.com/blog/2020/05/07/prepare-mysql-for-a-safe-shutdown/
+### 4. Configure InnoDB for Max Flushing.
+### SET GLOBAL innodb_fast_shutdown=0; SET GLOBAL innodb_max_dirty_pages_pct=0; SET GLOBAL innodb_change_buffering='none';
+### SHOW VARIABLES LIKE 'innodb_fast_shutdown'; SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct'; SHOW VARIABLES LIKE 'innodb_change_buffering';
+innodb_fast_shutdown		= 0
+#innodb_max_dirty_pages_pct	= 0	# so ist sie im Dauerstress
+innodb_max_dirty_pages_pct	= 50	# damit sie schneller startet
+#innodb_max_dirty_pages_pct_lwm	= 0
+innodb_max_dirty_pages_pct_lwm	= 20	# damit sie schneller startet
+innodb_change_buffering 	= 'none'
+</file>
+===== Bis MySQL 5.7 =====
   * [[http://dev.mysql.com/doc/refman/5.1/de/innodb-tuning.html]]
@@ Zeile 162: / Zeile 238: @@
   query_cache_size= 512M
   query_cache_type= 1
@@ Zeile 335: / Zeile 412: @@
   [server]
   sql-mode=""
+==== Performance Schema ====
+=== getestet mit Ubuntu 14.04.5 LTS / mysqld 5.7.21 ===
+<file sql 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;
+</file>
+<file sql 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;
+</file>
+===== 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
+  > perl mysqltuner.pl --defaults-file ~/.my.cnf 2>&1 | tee mysqltuner_-_$(date +'%F_%H-%M-%S').log
+==== mysqltuner.pl funktioniert nicht mit PXC ====
+<code c Der Fehler>
+# wget http://mysqltuner.pl/ -O mysqltuner.pl
+# perl mysqltuner.pl --forcemem 8000000000 --forceswap 8000000000 --defaults-file /root/.my.cnf --defaults-extra-file /etc/mysql/conf.d_23357/my.cnf --port 23357
+ >>  MySQLTuner 2.5.3
+	 * Jean-Marie Renouard <jmrenouard@gmail.com>
+	 * Major Hayden <major@mhtx.net>
+ >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
+ >>  Run with '--help' for additional options and output filtering
+[--] Skipped version check for MySQLTuner script
+mysql: [ERROR] unknown option '--print-defaults'
+mysql: [ERROR] unknown option '--print-defaults'
+[--] Assuming 8000000000 MB of physical memory
+[--] Assuming 8000000000 MB of swap space
+[!!] Failed to execute: SELECT VERSION()
+[!!] FAIL Execute SQL / return code: 1792
+[!!] You probably do not have enough privileges to run MySQLTuner ...
+</code>
+<code c Fehler nachstellen>
+# /usr/bin/mysql --port 23357 --print-defaults
+mysql: [ERROR] unknown option '--print-defaults'
+</code>
+<code c Client-Ursprung ermitteln>
+# dpkg -S mysql | grep -F bin | grep -E mysql$
+percona-xtradb-cluster-client-5.7: /usr/bin/mysql
+</code>
+<code c Der Grund steht in der Hilfe>
+# /usr/bin/mysql --help
+...
+The following options may be given as the first argument:
+--print-defaults        Print the program argument list and exit.
+...
+</code>
+<code c Test ist nicht befriedigend>
+# /usr/bin/mysql --print-defaults --port 23357
+/usr/bin/mysql would have been started with the following arguments:
+--port=3306 --socket=/var/run/mysqld/mysqld.sock --host=localhost --user=root --password=***** --port 23357
+</code>
@@ Zeile 359: / Zeile 551: @@
   replicate_wild_ignore_table     = DatenbankName.%
+===== MyISAM-Tuning =====
+[[https://mariadb.com/kb/en/myisam-system-variables/|MyISAM System Variables]]
+prüfen, ob ''key_buffer_size'' richtig dimensioniert ist:
+  > echo "SHOW STATUS LIKE '%key_read_requests%'; SHOW STATUS LIKE '%key_reads%';" | mysql -N | awk '{print $2}' | tr -s '\n' ' ' | awk '{a=$1 / $2; if (a < 100) A="katastrophal" ; if (a >= 100) A="akzeptabel" ; if (a >= 1000) A="gut" ; if (a >= 10000) A="sehr gut"; print "Wert ist",A,"("a")"}'
+  Wert ist sehr gut (12570.9)
+  > echo "SHOW STATUS LIKE '%key_reads%'; SHOW STATUS LIKE '%key_read_requests%';" | mysql -t
+  +---------------+---------+
+  | Variable_name | Value   |
+  +---------------+---------+
+  | Key_reads     | 1583133 |
+  +---------------+---------+
+  +-------------------+-------------+
+  | Variable_name     | Value       |
+  +-------------------+-------------+
+  | Key_read_requests | 19943598300 |
+  +-------------------+-------------+
+//key_buffer_size 25% or more of the available RAM (The ratio of ''key_reads'' to ''key_read_requests'' should be as low as possible, 1:100 is the highest acceptable, 1:1000 is better, and 1:10 is terrible.)//