利用mysqltuner做健康检查
[root@db1 ~]# wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
--2024-04-23 09:09:48-- https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.111.133, 185.199.1192.133, 185.199.109.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.111.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 264173 (258K) [text/plain]
Saving to: ‘mysqltuner.pl’
100%[==========================================================================================================================================================================>] 264,173 335KB/s in 0.8s
2024-04-23 09:09:49 (335 KB/s) - ‘mysqltuner.pl’ saved [264173/264173]
[root@db1 ~]# ps -ef|grep sock
mysql 3768 2712 1 Apr19 ? 01:20:35 /db/mysql/mysql-8.0.36/bin/mysqld --basedir=/db/mysql/mysql-8.0.36 --datadir=/db/mysql/mysql-8.0.36/data --plugin-dir=/db/mysql/mysql-8.0.36/lib/plugin --user=mysql --log-error=db1.err --pid-file=/db/mysql/mysql-8.0.36/data/db1.pid --socket=/db/mysql/mysql-8.0.36/mysql.sock --port=3307
root 110241 110090 0 09:09 pts/1 00:00:00 grep --color=auto sock
[root@db1 ~]# ll
total 9970676
-rw-------. 1 root root 2161 Feb 29 12:29 anaconda-ks.cfg
-rw-r--r--. 1 root root 1595 Mar 7 16:48 automgr.sh
-rw-r--r--. 1 root root 10200547328 Aug 20 2021 CentOS-7-x86_64-Everything-2009.iso
-rw-r--r-- 1 root root 1458 Apr 1 11:12 chk.txt
-rw-r--r-- 1 root root 264173 Apr 23 09:09 mysqltuner.pl
drwxr-xr-x. 4 root root 196 Sep 8 2020 tgbe-1.0.7
-rw-r--r--. 1 root root 8210063 Mar 1 09:20 tgbe-1.0.7.zip
drwxr-xr-x. 4 root root 182 Sep 8 2023 txgbe-1.3.4.1
-rw-r--r--. 1 root root 450201 Sep 8 2023 txgbe-1.3.4.1.zip
drwxr-xr-x. 4 root root 200 Dec 22 18:12 txgbe-1.3.5.1
-rw-r--r--. 1 root root 481140 Jan 2 15:04 txgbe-1.3.5.1.zip
[root@db1 ~]# chmod +x mysqltuner.pl
[root@db1 ~]# ll mysqltuner.pl
-rwxr-xr-x 1 root root 264173 Apr 23 09:09 mysqltuner.pl
[root@db1 ~]# ./mysqltuner.pl --socket /db/mysql/mysql-8.0.36/mysql.sock --port 3307
>> 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
[--] Using mysql to check login
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM -ndbcluster -ndbinfo +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 2.0M (Tables: 51)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 8.0.36
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /db/mysql/mysql-8.0.36/data/db1.err exists
[--] Log file: /db/mysql/mysql-8.0.36/data/db1.err (333K)
[OK] Log file /db/mysql/mysql-8.0.36/data/db1.err is not empty
[OK] Log file /db/mysql/mysql-8.0.36/data/db1.err is smaller than 32 MB
[OK] Log file /db/mysql/mysql-8.0.36/data/db1.err is readable.
[!!] /db/mysql/mysql-8.0.36/data/db1.err contains 1817 warning(s).
[!!] /db/mysql/mysql-8.0.36/data/db1.err contains 200 error(s).
[--] 27 start(s) detected in /db/mysql/mysql-8.0.36/data/db1.err
[--] 1) 2024-04-19T09:38:26.703786+08:00 0 [System] [MY-013292] [Server] Admin interface ready for connections, address: '192.1.1.51' port: 33072
[--] 2) 2024-04-19T09:38:26.703727+08:00 0 [System] [MY-010931] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: ready for connections. Version: '8.0.36' socket: '/db/mysql/mysql-8.0.36/mysql.sock' port: 3307 MySQL Community Server - GPL.
[--] 3) 2024-04-19T09:38:26.703503+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 4) 2024-03-29T20:55:08.160253+08:00 0 [System] [MY-013292] [Server] Admin interface ready for connections, address: '192.1.1.51' port: 33072
[--] 5) 2024-03-29T20:55:08.160111+08:00 0 [System] [MY-010931] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: ready for connections. Version: '8.0.36' socket: '/db/mysql/mysql-8.0.36/mysql.sock' port: 3307 MySQL Community Server - GPL.
[--] 6) 2024-03-29T20:55:08.160091+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 7) 2024-03-29T20:47:27.380800+08:00 0 [System] [MY-013292] [Server] Admin interface ready for connections, address: '192.1.1.51' port: 33072
[--] 8) 2024-03-29T20:47:27.380631+08:00 0 [System] [MY-010931] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: ready for connections. Version: '8.0.36' socket: '/db/mysql/mysql-8.0.36/mysql.sock' port: 3307 MySQL Community Server - GPL.
[--] 9) 2024-03-29T20:47:27.380592+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
[--] 10) 2024-03-29T16:58:36.589785+08:00 0 [System] [MY-013292] [Server] Admin interface ready for connections, address: '192.1.1.51' port: 33072
[--] 8 shutdown(s) detected in /db/mysql/mysql-8.0.36/data/db1.err
[--] 1) 2024-04-19T09:34:31.589012+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 2) 2024-03-29T20:51:16.010922+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 3) 2024-03-29T20:49:06.186935+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 4) 2024-03-29T20:43:08.339853+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 5) 2024-03-29T16:54:42.340267+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 6) 2024-03-29T15:19:58.184359+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 7) 2024-03-29T14:35:36.073887+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[--] 8) 2024-03-11T15:50:22.889106+08:00 0 [System] [MY-010910] [Server] /db/mysql/mysql-8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 23h 34m 41s (346K q [1.006 qps], 114K conn, TX: 53M, RX: 37M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 755.7G
[--] Max MySQL memory : 199.6G
[--] Other process memory: 0B
[--] Total buffers: 190.0G global + 1.9M per thread (5000 max threads)
[--] Performance_schema Max memory usage: 230M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 190.3G (25.18% of installed RAM)
[OK] Maximum possible memory usage: 199.6G (26.41% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (1/346K)
[OK] Highest usage of available connections: 0% (4/5000)
[OK] Aborted connections: 0.00% (1/114999)
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 219 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 193 total)
[OK] Thread cache hit rate: 99% (4 created / 114K connections)
[OK] Table cache hit rate: 99% (121K hits / 121K requests)
[OK] table_definition_cache (2000) is greater than number of tables (384)
[OK] Open file limit used: 0% (9/25K)
[OK] Table locks acquired immediately: 100% (114K immediate / 114K locks)
[OK] Binlog cache memory access: 96.15% (25 Memory / 26 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 230.8M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--] +-- Total MyISAM Tables : 0
[--] +-- Total MyISAM indexes : 0B
[--] +-- KB Size :8.0M
[--] +-- KB Used Size :1.5M
[--] +-- KB used :18.2%
[--] +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--] +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 190.0G ) under limit for 64 bits architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 190.0G / 2.0M
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (0.0493421052631579%): 1.0G / 190.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk: 1520 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 98.60% (83454 hits / 84641 total)
[!!] InnoDB Write Log efficiency: 86.54% (3940 hits / 4553 total)
[OK] InnoDB log waits: 0.00% (0 waits / 613 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- 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:
Check warning line(s) in /db/mysql/mysql-8.0.36/data/db1.err file
Check error line(s) in /db/mysql/mysql-8.0.36/data/db1.err file
Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
innodb_redo_log_capacity should be (=47G) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.
innodb_buffer_pool_instances(=64)
innodb_log_buffer_size (> 16M)
[root@db1 ~]# mysql -uroot -pxxxxxx -Dtest -h 192.1.1.50
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 115253
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@192.1.1.50 [test] >select @@innodb_redo_log_capacity;
+----------------------------+
| @@innodb_redo_log_capacity |
+----------------------------+
| 1073741824 |
+----------------------------+
1 row in set (0.00 sec)
root@192.1.1.50 [test] >select @@innodb_log_buffer_size;
+--------------------------+
| @@innodb_log_buffer_size |
+--------------------------+
| 16777216 |
+--------------------------+
1 row in set (0.00 sec)
root@192.1.1.50 [test] >select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
| 8 |
+--------------------------------+
1 row in set (0.00 sec)
root@192.1.1.50 [test] >
相关参考
https://zhuanlan.zhihu.com/p/114664646
https://mp.weixin.qq.com/s/UMDIQDg3rusE6IwfxkFtVQ
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
