mysql - 具有64GB Ram的Mysql Tuning Server消耗了50％的CPU

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

我在那里托管了一些流量不大的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

最佳答案

您的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

有关innodb_lru_scan_depth描述，请参阅refman以了解为什么8优于40。每SECOND的CPU工作量将最小化。

提供所需的其他信息将允许进行更深入的分析。

关于mysql - 具有64GB Ram的Mysql Tuning Server消耗了50％的CPU，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50417071/