Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- mysql_performance_tuning [2022-02-24 12:14:53] – [MySQL Performance Tuning] manfred
+++ mysql_performance_tuning [2022-02-24 12:28:25] (aktuell) – gelöscht manfred
@@ Zeile 1: / Zeile 1: @@
-====== MySQL Performance Tuning ======
-siehe auch: [[::MySQL Tuning]]
-  * [[https://www.doag.org/formes/pubfiles/7522870/2015-K-MySQL-Oliver_Sennhauser-MySQL_Performance_Tuning_fuer_Oracle-DBA_s-Praesentation.pdf]]
-  * [[http://fromdual.com/de/mysql-performance-schema-hints]]
-  * [[https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html]]
-  * [[https://docs.oracle.com/cd/E17952_01/mysql-monitor-3.0-en/mem-qanal-using-performance-schema.html]]
-  * [[https://dev.mysql.com/doc/refman/5.7/en/performance-schema-statement-digests.html]]
-Möglichkeiten:
-  - ''SHOW PROCESSLIST;''
-  - ''Performance Schema''
-  - ''Slow Query Log''
-===== 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
-<file bash mysqltuner.log>
-...
--------- Performance Metrics -----------------------------------------------------------------------
-[--] Up for: 4d 2h 14m 50s (2M q [7.146 qps], 189K conn, TX: 1G, RX: 1G)
-[--] Reads / Writes: 100% / 0%
-[--] Binary logging is disabled
-[--] Physical Memory     : 3.9G
-[--] Max MySQL memory    : 942.9M
-[--] Other process memory: 0B
-[--] Total buffers: 169.0M global + 5.1M per thread (151 max threads)
-[--] P_S Max memory usage: 72B
-[--] Galera GCache Max memory usage: 128M
-[OK] Maximum reached memory usage: 261.3M (6.62% of installed RAM)
-[OK] Maximum possible memory usage: 942.9M (23.91% of installed RAM)
-[OK] Overall possible memory usage with other process is compatible with memory available
-[OK] Slow queries: 0% (0/2M)
-[OK] Highest usage of available connections: 11% (18/151)
-[OK] Aborted connections: 0.00%  (0/189529)
-[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
-[!!] Query cache may be disabled by default due to mutex contention.
-[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
-[OK] Query cache prunes per day: 0
-[OK] No Sort requiring temporary tables
-[OK] No joins without indexes
-[OK] Temporary tables created on disk: 0% (1 on disk / 104K total)
-[OK] Thread cache hit rate: 99% (18 created / 189K connections)
-[OK] Table cache hit rate: 96% (177 open / 184 opened)
-[OK] table_definition_cache(1400) is upper than number of tables(296)
-[OK] Open file limit used: 0% (36/1M)
-[OK] Table locks acquired immediately: 100% (6M immediate / 6M locks)
-...
--------- Galera Metrics ----------------------------------------------------------------------------
-[--] Galera is enabled.
-[--] GCache is using 0B
-[--] CPU core detected  : 4
-[--] wsrep_slave_threads: 2
-[!!] wsrep_slave_threads is not equal to 2, 3 or 4 times number of CPU(s)
-[!!] gcs.fc_limit should be equal to 5 * wsrep_slave_threads
-[--] wsrep parallel slave can cause frequent inconsistency crash.
-[!!] gcs.fc_limit should be equal to 5 * wsrep_slave_threads
-[!!] gcs.fc_factor should be equal to 0.8
-[OK] Flow control fraction seems to be OK (wsrep_flow_control_paused<=0.02)
-[OK] All tables get a primary key
-[OK] All tables are InnoDB tables
-[OK] Binlog format is in ROW mode.
-[OK] InnoDB flush log at each commit is disabled for Galera.
-[--] Read consistency mode :OFF
-[OK] Galera WsREP is enabled.
-[OK] Galera Cluster address is defined: gcomm://
-[--] There are 1 nodes in wsrep_cluster_address
-[OK] There are 3 nodes in wsrep_cluster_size.
-[OK] All cluster nodes detected.
-[OK] Galera Cluster name is defined: galera-15
-[OK] Galera Node name is defined: 61edf12b743b
-[!!] Galera Notify command is not defined.
-[OK] SST Method is based on xtrabackup.
-[OK] TOI is default mode for upgrade.
-[--] Max WsRep message : 2.0G
-[OK] Node is connected
-[OK] Node is ready
-[--] Cluster status :Primary
-[OK] Galera cluster is consistent and ready for operations
-[OK] Node and whole cluster at the same level: e44252e8-a667-11ea-b5d3-522932a4ca0c
-[OK] Node is synced with whole cluster.
-[OK] There is no certification failures detected.
-...
-Variables to adjust:
-    query_cache_size (=0)
-    query_cache_type (=0)
-    query_cache_limit (> 1M, or use smaller result sets)
-    thread_pool_size between 16 and 36 for InnoDB usage
-    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
-    wsrep_slave_threads = 16
-    gcs.fc_limit= wsrep_slave_threads * 5
-    Set wsrep_slave_threads to 1 in case of HA_ERR_FOUND_DUPP_KEY crash on slave
-    gcs.fc_limit= wsrep_slave_threads * 5
-    gcs.fc_factor=0.8
-    set up parameter wsrep_notify_cmd to be notify
-</file>