我有一个 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
对 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 工作负载将最小化。
提供所要求的补充信息将允许进行更深入的分析。