MySQL Performance Tuning

Möglichkeiten:

SHOW PROCESSLIST;
Performance Schema
Slow Query Log

Performance Schema

getestet mit Ubuntu 14.04.5 LTS / mysqld 5.7.21

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

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