具有 64 GB RAM 的 Mysql 调整服务器消耗 50% 的 CPU

Question

我有一台配备 64 GB RAM 的专用服务器，运行 Centos 7，具有 4 核和 8 线程。

我在那里托管了一些流量不大的 WordPress 网站。

总共大约 20 个网站，其中只有 2-3 个每天有 1000 名访问者

我的 CPU 负载约为 200% - 400%，其中 90% 由 MySQL 占用

有人可以帮我吗？我花了几天时间研究并尝试优化，但以我的知识来说这是不可能的。

my.cnf如下:

    [mysql]

# CLIENT #
port                           = 3306
socket                         = /home/mysql/mysql.sock

[mysqld]
local-infile                   = 0
performance_schema             = ON

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /home/mysql/mysql.sock
pid-file                       = /home/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /home/mysql/

# BINARY LOGGING #
log-bin                        = /home/mysql/mysql-bin
expire-logs-days               = 2
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 256M
max_heap_table_size            = 256M
query_cache_type               = 1
query_cache_size               = 128M
query_cache_limit              = 2M
max_connections                = 500
thread_cache_size              = 50
open-files-limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096


# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G
innodb-buffer-pool-instances   = 40
join_buffer_size               = 4M


# LOGGING #
log-error                      = /home/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 0
slow-query-log-file            = /home/mysql/mysql-slow.log

Answer 1

针对 my.cnf-ini [mysqld] 部分考虑的建议

max_connect_errors=10  # from 1000000 to limit hacker/cracker pw guessing
#join_buffer_size=4M  # lead to allow DEFAULT to work for you
innodb_buffer_pool_instances=8  # from 40  will serve you better than 40
innodb_lru_scan_depth=128  # from 1024 default
log_warnings=2  # for additional info in error log when aborted_connection

请参阅 refman 的 innodb_lru_scan_depth 描述以了解为什么 8 比 40 更好。您每秒钟的 CPU 工作负载将被最小化。

提供所需的附加信息将有助于进行更深入的分析。