我有点不确定为什么我们会看到这些内存数字，而 CPU 使用率实际上非常低。
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 11.1M (Tables: 6)
[--] Data in InnoDB tables: 15.1G (Tables: 1033)
[!!] Total fragmented tables: 2
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5s (1K q [255.800 qps], 31 conn, TX: 15M, RX: 252K)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 125.4G
[--] Max MySQL memory : 109.0G
[--] Other process memory: 0B
[--] Total buffers: 30.7G global + 266.7M per thread (300 max threads)
[--] P_S Max memory usage: 129M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 31.4G (25.01% of installed RAM)
[!!] Maximum possible memory usage: 109.0G (86.87% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 0% (2/300)
[!!] Aborted connections: 3.23% (1/31)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 109 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 70 total)
[OK] Thread cache hit rate: 93% (2 created / 31 connections)
[OK] Table cache hit rate: 92% (72 open / 78 opened)
[OK] table_definition_cache(8000) is upper than number of tables(1313)
[OK] Open file limit used: 0% (25/158K)
[OK] Table locks acquired immediately: 100% (11 immediate / 11 locks)
[OK] Binlog cache memory access: 100.00% (89 Memory / 89 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 129.9M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.3.22-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/3.6M
[!!] Read Key buffer hit rate: 50.0% (8 cached / 4 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 10
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 30.0G/15.1G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3.0G * 2/30.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 30
[--] Number of InnoDB Buffer Pool Chunk : 240 for 30 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: 91.93% (153107 hits/ 166539 total)
[!!] InnoDB Write Log efficiency: 57.72% (172 hits/ 298 total)
[OK] InnoDB log waits: 0.00% (0 waits / 126 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
-------- 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
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
/var/log/mysqld.log is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Run OPTIMIZE TABLE to defragment tables for better performance
Total freed space after theses OPTIMIZE TABLE : 1262 Mb
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
[mysql]
# CLIENT #
port =3306
socket ="/var/lib/mysql/mysql.sock"
[mysqld]
bind-address = 127.0.0.1
tmpdir = "/dev/shm"
interactive_timeout = 3600
connect_timeout = 3600
performance_schema = on
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = "/var/lib/mysql/mysql.sock"
pid-file = "hidden"
local-infile = 0
# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 256M #was 32M
max-connect-errors = 1000000
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
# DATA STORAGE #
datadir = "/var/lib/mysql"
# BINARY LOGGING #
log-bin = "/var/lib/mysql/mysql-bin"
expire-logs-days = 7
sync-binlog = 0
binlog_format = mixed
binlog_cache_size = 64M #Default = 32M, increase due to faults
# CACHES AND LIMITS #
tmp-table-size = 64M #was 32M
max-heap-table-size = 64M #was 32M
max-connections = 300 #was150
thread-cache-size = 75 #was 50
table-definition-cache = 8000
table-open-cache = 79230 #was 22000
open_files_limit = 98460 #was 65000
# Optimization by #
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 0
join_buffer_size = 8M
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 3G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 30G
innodb-thread_concurrency = 10
concurrent_insert = ALWAYS
innodb_log_buffer_size = 512M
innodb_buffer_pool_instances = 30
innodb_data_file_path = "ibdata1:10M:autoextend:max:10G"
innodb_flush_log_at_trx_commit = 2
innodb_stats_on_metadata = 0
innodb_lru_scan_depth = 100
# LOGGING #
log_queries_not_using_indexes = 0
slow_query_log = 1
slow_query_log_file = /var/log/slow_mysql.log
long_query_time = 1
log-error = /var/log/mysqld.log
[mysqldump]
max_allowed_packet =256M
quick
quote_names#
服务器规格如下：
128GB 内存 16 核 CPU 960GB NVMe 磁盘 RAID10
我通常会预期服务器会使用比我们当前看到的更多的 CPU 和更少的内存。
有人知道我错过了什么吗？
答案1
我注意到您没有发布内存使用情况图表。您使用的内存可能没有您想象的那么多。
[--] Physical Memory : 125.4G
这就是您的服务器的容量。128GB。
[--] Max MySQL memory : 109.0G
这就是你能做到的可能使用。取决于：
[OK] InnoDB buffer pool / data size: 30.0G/15.1G
然后添加：
[--] Total buffers: 30.7G global + 266.7M per thread (300 max threads)
因此基本上 30GB + 79GB (300*266.7) = 110GB。请注意，其中还包括较小的缓冲区。
然而，你实际上使用：
[OK] Maximum reached memory usage: 31.4G (25.01% of installed RAM)
答案2
大部分 RAM 应该专用于innodb-buffer-pool-size-- 可能是 100G，而不是仅仅 30G。但是“InnoDB 表中的数据：15.1G”表示即使 30G 也比必要的大。
缓冲池是所有正在运行的进程共享的缓存。因此“每个线程的内存使用量”并不是一个有意义的指标。
运行良好的 MySQL 服务器仅使用硬件 CPU 能力的一小部分。
如果您遇到性能问题，请使用此处的指南获取帮助：http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
根据您向我们展示的内容，您可以降低到更便宜的配置，而不会损失明显的功能。