mysql性能调优工具之MySQLTuner-perl
MySQLTuner-perl介绍
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
MySQLTuner supports ~300 indicators for MySQL/MariaDB/Percona Server in this last version.
MySQLTuner is maintained and indicator collect is increasing week after week supporting a lot of configuration such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, …
兼容性支持列表
-
MySQL 8 (full support, password checks don’t work)
-
MySQL 5.7 (full support)
-
MySQL 5.6 (full support)
-
MySQL 5.5 (full support)
-
MariaDB 10.4 (full support)
-
MariaDB 10.3 (full support)
-
MariaDB 10.2 (full support)
-
MariaDB 10.1 (full support)
-
MariaDB 10.0 (full support, 6 last month support)
-
MariaDB 5.5 (full support, no more MariaDB support)
-
Percona Server 8.0 (full support, password checks don’t work)
-
Percona Server 5.7 (full support)
-
Percona Server 5.6 (full support)
-
Percona XtraDB cluster (partial support, no test environment)
-
Mysql Replications (partial support, no test environment)
-
Galera replication (partial support, no test environment)
-
MySQL 3.23, 4.0, 4.1, 5.0, 5.1, 5.5 (partial support - deprecated version)
*** UNSUPPORTED ENVIRONMENTS - NEED HELP FOR THAT ***
-
Windows is not supported at this time (Help wanted !!!)
-
Cloud based is not supported at this time (Help wanted !!!)
-
CVE vulnerabilities detection support from https://cve.mitre.org
*** MINIMAL REQUIREMENTS ***
- Perl 5.6 or later (with perl-doc package)
- Unix/Linux based operating system (tested on Linux, BSD variants, and Solaris variants)
- Unrestricted read access to the MySQL server (OS root access recommended for MySQL < 5.1)
安装
[root@lineqi opt]# unzip MySQLTuner-perl-master.zip
Archive: MySQLTuner-perl-master.zip
b6f8f9f9a339bc2db3f16a61c6e1bdbf7687a912
使用方式
[root@lineqi MySQLTuner-perl-master]# perl mysqltuner.pl --help
Name:
MySQLTuner 1.7.19 - MySQL High Performance Tuning Script
Important Usage Guidelines:
To run the script with the default options, run the script without
arguments Allow MySQL server to run for at least 24-48 hours before
trusting suggestions Some routines may require root level privileges
(script will provide warnings) You must provide the remote server’s
total memory when connecting to other servers
Connection and Authentication:
–host Connect to a remote host to perform tests (default: localhost)
–socket Use a different socket for a local connection
–port Port to use for connection (default: 3306)
–user Username to use for authentication
–userenv Name of env variable which contains username to use for authentication
–pass Password to use for authentication
–passenv Name of env variable which contains password to use for authentication
–ssl-ca
–mysqladmin
–mysqlcmd
–defaults-file
Performance and Reporting Options:
–skipsize Don’t enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
–skippassword Don’t perform checks on user passwords(default: off)
–checkversion Check for updates to MySQLTuner (default: don’t check)
–updateversion Check for updates to MySQLTuner and update when newer version is available (default: don’t check)
–forcemem Amount of RAM installed in megabytes
–forceswap Amount of swap memory configured in megabytes
–passwordfile
Output Options:
–silent Don’t output anything on screen
–nogood Remove OK responses
–nobad Remove negative/suggestion responses
–noinfo Remove informational responses
–debug Print debug information
–noprocess Consider no other process is running
–dbstat Print database information
–nodbstat Don’t Print database information
–tbstat Print table information
–notbstat Don’t Print table information
–idxstat Print index information
–noidxstat Don’t Print index information
–sysstat Print system information
–nosysstat Don’t Print system information
–pfstat Print Performance schema
–nopfstat Don’t Print Performance schema
–verbose Prints out all options (default: no verbose, dbstat, idxstat, sysstat, tbstat, pfstat)
–bannedports Ports banned separated by comma(,)
–maxportallowed Number of ports opened allowed on this hosts
–cvefile
–nocolor Don’t print output in color
–json Print result as JSON string
–buffers Print global and per-thread buffer values
–outputfile
–reportfile
–template
使用案例
[root@lineqi MySQLTuner-perl-master]# perl mysqltuner.pl --user root --pass W1234
MySQLTuner 1.7.19 - Major Hayden major@mhtx.net
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with ‘–help’ for additional options and output filtering
[–] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.29-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /data/mysql_13306/mysql_error.log exists
[–] Log file: /data/mysql_13306/mysql_error.log(83K)
[OK] Log file /data/mysql_13306/mysql_error.log is readable.
[OK] Log file /data/mysql_13306/mysql_error.log is not empty
[OK] Log file /data/mysql_13306/mysql_error.log is smaller than 32 Mb
[!!] /data/mysql_13306/mysql_error.log contains 152 warning(s).
[!!] /data/mysql_13306/mysql_error.log contains 69 error(s).
[–] 10 start(s) detected in /data/mysql_13306/mysql_error.log
[–] 1) 2020-06-29T14:15:03.500685Z 0 [Note] mysqld: ready for connections.
[–] 2) 2020-06-14T14:36:33.771937Z 0 [Note] mysqld: ready for connections.
[–] 3) 2020-06-14T14:28:04.439244Z 0 [Note] mysqld: ready for connections.
[–] 4) 2020-06-14 20:58:38 8088 [Note] mysqld: ready for connections.
[–] 5) 2020-06-09 22:19:25 2275 [Note] mysqld: ready for connections.
[–] 6) 2020-06-07 22:07:08 2474 [Note] mysqld: ready for connections.
[–] 7) 2020-06-06 20:51:32 2406 [Note] mysqld: ready for connections.
[–] 8) 2020-06-02 23:13:35 2875 [Note] mysqld: ready for connections.
[–] 9) 2020-05-29 10:48:32 2768 [Note] mysqld: ready for connections.
[–] 10) 2020-05-29 10:40:52 2489 [Note] mysqld: ready for connections.
[–] 8 shutdown(s) detected in /data/mysql_13306/mysql_error.log
[–] 1) 2020-06-14T22:52:35.931104Z 0 [Note] mysqld: Shutdown complete
[–] 2) 2020-06-14T14:36:20.733692Z 0 [Note] mysqld: Shutdown complete
[–] 3) 2020-06-14 21:01:19 8088 [Note] mysqld: Shutdown complete
[–] 4) 2020-06-09 23:16:38 2275 [Note] mysqld: Shutdown complete
[–] 5) 2020-06-07 23:20:16 2474 [Note] mysqld: Shutdown complete
[–] 6) 2020-06-06 20:59:41 2406 [Note] mysqld: Shutdown complete
[–] 7) 2020-05-29 16:25:27 2768 [Note] mysqld: Shutdown complete
[–] 8) 2020-05-29 10:48:26 2489 [Note] mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[–] Data in InnoDB tables: 144.0K (Tables: 8)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[–] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[!!] User ‘@lineqi’ is an anonymous account.
[!!] User ‘@localhost’ is an anonymous account.
[!!] User ‘@lineqi’ has no password set.
[!!] User ‘@localhost’ has no password set.
[!!] User ‘lineqi@%’ does not specify hostname restrictions.
[–] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[–] Up for: 48m 13s (425 q [0.147 qps], 150 conn, TX: 91K, RX: 40K)
[–] Reads / Writes: 100% / 0%
[–] Binary logging is enabled (GTID MODE: OFF)
[–] Physical Memory : 1.8G
[–] Max MySQL memory : 19.4G
[–] Other process memory: 0B
[–] Total buffers: 2.0G global + 17.8M per thread (1000 max threads)
[–] P_S Max memory usage: 72B
[–] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2.1G (116.23% of installed RAM)
[!!] Maximum possible memory usage: 19.4G (1090.83% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/425)
[OK] Highest usage of available connections: 0% (1/1000)
[OK] Aborted connections: 0.00% (0/150)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 171 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: 8% (37 on disk / 459 total)
[OK] Thread cache hit rate: 99% (1 created / 150 connections)
[!!] Table cache hit rate: 15% (56 open / 354 opened)
[OK] table_definition_cache(400) is upper than number of tables(287)
[OK] Open file limit used: 0% (2/5K)
[OK] Table locks acquired immediately: 100% (213 immediate / 213 locks)
[OK] Binlog cache memory access: 0% (0 Memory / 0 Total)
-------- Performance schema ------------------------------------------------------------------------
[–] Memory used by P_S: 72B
[–] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[–] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 68.2% (22 cached / 7 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[–] InnoDB is enabled.
[–] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/144.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (14.6484375 %): 100.0M * 3/2.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[–] Number of InnoDB Buffer Pool Chunk : 16 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 83.20% (2367 hits/ 2845 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[–] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[–] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[–] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[–] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[–] Galera Synchronous replication: NO
[–] No replication slave(s) for this server.
[–] Binlog format: ROW
[–] XA support enabled: ON
[–] Semi synchronous replication Master: Not Activated
[–] Semi synchronous replication Slave: Not Activated
[–] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /data/mysql_13306/mysql_error.log file
Control error line(s) into /data/mysql_13306/mysql_error.log file
Remove Anonymous User accounts - there are 2 anonymous accounts.
DELETE FROM mysql.user WHERE user =’’;
Set up a Secure Password for @lineqi user: SET PASSWORD FOR ‘’@‘SpecificDNSorIp’ = PASSWORD(‘secure_password’);
Set up a Secure Password for @localhost user: SET PASSWORD FOR ‘’@‘SpecificDNSorIp’ = PASSWORD(‘secure_password’);
Restrict Host for ‘lineqi’@% to lineqi@SpecificDNSorIp
UPDATE mysql.user SET host =‘SpecificDNSorIp’ WHERE user=‘lineqi’ AND host =’%’; FLUSH PRIVILEGES;
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (5000) variable
should be greater than table_open_cache (400)
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
table_open_cache (> 400)
innodb_log_file_size should be (=170M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=2)
下载地址及相关资料
https://github.com/major/MySQLTuner-perl