最近我的 MYsql CPU 使用率是 120%，这是 mysqltuner，您能为我建议一个解决方案吗？
[root@server1 ~]# mysqltuner
>> MySQLTuner 1.8.3 - Major Hayden <[email protected]>
>> 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
[OK] Currently running supported MySQL version 10.5.20-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/nc-ph-3696.arabtimenews.com.err exists
[--] Log file: /var/lib/mysql/nc-ph-3696.arabtimenews.com.err(58M)
[OK] Log file /var/lib/mysql/nc-ph-3696.arabtimenews.com.err is not empty
[!!] Log file /var/lib/mysql/nc-ph-3696.arabtimenews.com.err is bigger than 32 Mb
[OK] Log file /var/lib/mysql/nc-ph-3696.arabtimenews.com.err is readable.
[!!] /var/lib/mysql/nc-ph-3696.arabtimenews.com.err contains 5671 warning(s).
[!!] /var/lib/mysql/nc-ph-3696.arabtimenews.com.err contains 8815 error(s).
[--] 77 start(s) detected in /var/lib/mysql/nc-ph-3696.arabtimenews.com.err
[--] 1) 2023-05-19 22:58:18 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 2) 2023-05-19 20:25:05 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 3) 2023-05-19 18:36:29 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 4) 2023-05-19 18:36:24 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 5) 2023-05-19 18:36:19 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 6) 2023-05-19 18:36:15 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 7) 2023-05-19 18:36:10 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 8) 2023-05-19 18:36:05 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 9) 2023-05-19 18:33:24 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 10) 2023-05-19 18:33:19 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 76 shutdown(s) detected in /var/lib/mysql/nc-ph-3696.arabtimenews.com.err
[--] 1) 2023-05-19 22:58:15 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 2) 2023-05-19 20:25:02 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 3) 2023-05-19 18:36:26 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 4) 2023-05-19 18:36:22 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 5) 2023-05-19 18:36:17 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 6) 2023-05-19 18:36:13 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 7) 2023-05-19 18:36:08 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 8) 2023-05-19 18:36:03 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 9) 2023-05-19 18:33:22 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 10) 2023-05-19 18:33:17 0 [Note] /usr/sbin/mariadbd: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 3.7M (Tables: 170)
[--] Data in InnoDB tables: 1.7G (Tables: 2673)
[OK] Total fragmented tables: 0
-------- 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 is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15m 30s (405K q [435.873 qps], 60K conn, TX: 252G, RX: 56M)
[--] Reads / Writes: 82% / 18%
[--] Binary logging is disabled
[--] Physical Memory : 125.7G
[--] Max MySQL memory : 518.1G
[--] Other process memory: 0B
[--] Total buffers: 16.8G global + 1.0G per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 156.2G (124.25% of installed RAM)
[!!] Maximum possible memory usage: 518.1G (412.24% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/405K)
[OK] Highest usage of available connections: 27% (139/500)
[OK] Aborted connections: 0.00% (1/60771)
[!!] 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 / 3K sorts)
[!!] Joins performed without indexes: 8
[!!] Temporary tables created on disk: 94% (27K on disk / 29K total)
[OK] Thread cache hit rate: 99% (139 created / 60K connections)
[OK] Table cache hit rate: 97% (126K hits / 129K requests)
[OK] table_definition_cache(40000) is upper than number of tables(3036)
[OK] Open file limit used: 0% (370/40K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 64 thread(s).
[--] Using default value is good enough for your version (10.5.20-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/560.0K
[OK] Read Key buffer hit rate: 99.8% (1K cached / 3 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 12.6G/1.7G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3.2G * 1/12.6G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 101 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.98% (452129093 hits/ 452197113 total)
[!!] InnoDB Write Log efficiency: 189.95% (8618 hits/ 4537 total)
[OK] InnoDB log waits: 0.00% (0 waits / 13155 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K
[OK] Aria pagecache hit rate: 95.8% (641K 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
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
/var/lib/mysql/nc-ph-3696.arabtimenews.com.err is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
Check warning line(s) in /var/lib/mysql/nc-ph-3696.arabtimenews.com.err file
Check error line(s) in /var/lib/mysql/nc-ph-3696.arabtimenews.com.err file
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.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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).
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 4.0K, or always use indexes with JOINs)
performance_schema = ON enable PFS
这是my.cnf：
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-error=/var/lib/mysql/nc-ph-3696.arabtimenews.com.err
performance-schema=0
innodb_buffer_pool_size=12928M
max_connections = 500
max_allowed_packet=1024M
open_files_limit=39999
innodb_file_per_table=1
unix_socket=OFF
join_buffer_size=4096
join_buffer_space_limit=4096
optimizer_switch='optimize_join_buffer_size=on'
tmp_table_size=4000M
max_heap_table_size=4000M
max_tmp_tables=300
table_definition_cache=40000
table_open_cache=20000
innodb_log_file_size=3232M
table_open_cache=20000
innodb_log_file_size=3232M
处理器信息
Total processors: 64
Processor #1
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #2
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #3
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #4
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2000.000 MHz
Cache
512 KB
Processor #5
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #6
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2800.000 MHz
Cache
512 KB
Processor #7
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #8
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2000.000 MHz
Cache
512 KB
Processor #9
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #10
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #11
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #12
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #13
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #14
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #15
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #16
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #17
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2800.000 MHz
Cache
512 KB
Processor #18
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2800.000 MHz
Cache
512 KB
Processor #19
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #20
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #21
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #22
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #23
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #24
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2000.000 MHz
Cache
512 KB
Processor #25
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #26
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #27
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #28
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #29
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #30
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #31
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #32
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #33
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2800.000 MHz
Cache
512 KB
Processor #34
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #35
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #36
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #37
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #38
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #39
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #40
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #41
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #42
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #43
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #44
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #45
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #46
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #47
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #48
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #49
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #50
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2000.000 MHz
Cache
512 KB
Processor #51
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2000.000 MHz
Cache
512 KB
Processor #52
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #53
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #54
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #55
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2800.000 MHz
Cache
512 KB
Processor #56
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #57
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #58
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #59
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #60
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #61
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #62
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #63
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
1500.000 MHz
Cache
512 KB
Processor #64
Vendor
AuthenticAMD
Name
AMD EPYC 7282 16-Core Processor
Speed
2000.000 MHz
Cache
512 KB
内存信息
[ 0.000000] Memory: 6621048k/135527424k available (7988k kernel code, 1448416k absent, 2459716k reserved, 5756k data, 2176k init)
当前内存使用情况
total used free shared buff/cache available
Mem: 131782472 11295012 58106300 406236 62381160 119457692
Swap: 33554416 0 33554416
Total: 165336888 11295012 91660716
答案1
这个错误是假的。但是，以下是一些设置过高且危险的事情：
max_connections = 500 -- 200 is probably reasonable
open_files_limit=39999 -- 10000
join_buffer_size=4096 -- 1M (4096 is too low)
tmp_table_size=4000M -- 1G
max_heap_table_size=4000M -- 1G
table_definition_cache=40000 -- 10000
table_open_cache=20000 -- 10000
同时，高 CPU 意味着索引或查询公式化不当。请提供查询和SHOW CREATE TABLE。
无论如何，“CPU 使用率为 120%”意味着您正在使用 16 个核心中的大约 1.2 个。没问题。