我目前正在致力于优化 MariaDB 数据库的性能,并且遇到了一个名为 mysqltuner 的工具。我已经在我的服务器上安装并运行了 mysqltuner,它为我提供了一些建议和有关 MariaDB 配置当前状态的见解。
但是,我正在寻求有关如何有效解释和实施 mysqltuner 提供的建议的建议和额外指导。
我仅将服务器用于此数据库,但用户告诉我查询速度较慢,因为服务器未正确调整 MariaDB 10.6、Apache 2.4.52。
如何准确解读mysqltuner给出的建议?各种指标和值的含义是什么?它们与我的 MariaDB 数据库的性能有何关系?
服务器要求
CPU(s) 32 x Intel(R) Xeon(R) Silver 4314 CPU @ 2.40GHz
RAM 32 GB
MYSQL 调优器输出
>> MySQLTuner 2.2.6
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------------------------------------------- --------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in MyISAM tables: 13.3M (Tables: 165)
[--] Data in InnoDB tables: 33.4G (Tables: 2578)
[--] Data in MEMORY tables: 0B (Tables: 6)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 10.6.14-MariaDB-1:10.6.14+maria~u bu2204
-------- Log file Recommendations ---------------------------------------------- --------------------
[!!] Log file doesn't exist
-------- Analysis Performance Metrics ------------------------------------------ --------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
Use of uninitialized value $opt{"structstat"} in numeric eq (==) at
./mysqltuner.pl line 5810 (#1)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.
To help you figure out what was undefined, perl will try to tell you
the name of the variable (if any) that was undefined. In some cases
it cannot do this, so it also tells you what operation you used the
undefined value in. Note, however, that perl optimizes your program
and the operation displayed in the warning may not necessarily appear
literally in your program. For example, "that $foo" is usually
optimized into "that " . $foo, and the warning will refer to the
concatenation (.) operator, even though there is no . in
your program.
-------- CVE Security Recommendations ------------------------------------------ --------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics --------------------------------------------------- --------------------
[--] Up for: 3d 0h 50m 58s (27M q [106.285 qps], 5M conn, TX: 29G, RX: 3G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 31.3G
[--] Max MySQL memory : 86.1G
[--] Other process memory: 0B
[--] Total buffers: 15.3G global + 480.6M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 86.6G (276.42% of installed RAM)
[!!] Maximum possible memory usage: 86.1G (274.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (27/27M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.21% (12101/5634183)
[!!] Name resolution is active: a reverse name resolution is made for each new c onnection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocesso r machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 30037
[OK] Temporary tables created on disk: 2% (28K on disk / 997K total)
[OK] Thread cache hit rate: 99% (253 created / 5M connections)
[OK] Table cache hit rate: 99% (26M hits / 27M requests)
[!!] table_definition_cache (400) is less than number of tables (3041)
[OK] Open file limit used: 0% (121/16K)
[OK] Table locks acquired immediately: 100% (189K immediate / 189K locks)
-------- Performance schema ---------------------------------------------------- --------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.
-------- ThreadPool Metrics ---------------------------------------------------- --------------------
[--] ThreadPool stat is disabled.
Use of uninitialized value $opt{"myisamstat"} in numeric eq (==) at
./mysqltuner.pl line 3860 (#1)
-------- InnoDB Metrics -------------------------------------------------------- --------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDb Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (1 7179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 33.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128 .0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 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: 99.87% (157285991602 hits / 157486582048 tot al)
[!!] InnoDB Write Log efficiency: 49.38% (563905 hits / 1141959 total)
[OK] InnoDB log waits: 0.00% (0 waits / 578054 writes)
-------- Aria Metrics ---------------------------------------------------------- --------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[OK] Aria pagecache hit rate: 99.9% (27M cached / 27K reads)
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
mysqltuner推荐
-------- Recommendations ------------------------------------------------------- --------------------
General recommendations:
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Performance schema should be activated for better diagnostics
Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
skip-name-resolve=ON
join_buffer_size (> 256.0K, or always use indexes with JOINs)
table_definition_cache (400) > 3041 or -1 (autosizing if supported)
performance_schema=ON
innodb_buffer_pool_size (>= 33.4G) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
我的cnf请给建议请需要添加什么
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
我非常感谢任何与使用 mysqltuner 进行 MariaDB 性能调整相关的见解、解释或资源。预先感谢您的宝贵帮助!
大问题:更改
innodb_buffer_pool_size
为20G
.解决这个问题,运行几天,然后让我们看看情况如何。
更多工具: