我们有一个专用服务器，上面托管 1 个使用 WordPress 的网站，
- CPU：英特尔® 至强® W-2145 八核（8 核）
- 内存：128 GB DDR4 ECC 内存
- 连接：1 Gbit/s 端口
- 流量：无限制
- 操作系统：CENTOS 7.6
- WHM/cPanel v76.0.17
- mysql 版本 14.14 发行版 5.7.24
该网站每天的访问量约为 40K - 50K，这是一个繁忙的网站！问题是，每当网站的活跃用户超过 1000 - 1500 人时，网站响应就会变得越来越慢，然后网站就会瘫痪，我联系了 Cpanel 支持，他们检查了 cPanel 软件，他们说这个问题似乎与 cPanel 或 cPanel 捆绑软件的基本配置无关或由其引起。他们建议我“由于您的服务器有大量可用的可用 RAM，我建议为 Apache 和 MySQL 分配更多资源，因为我看到 MySQL 在 /etc/my.cnf 中只有默认设置”所以我正在寻找最佳的 my.cnf 配置这是我当前的服务器默认配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance-schema=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
非常感谢您的帮助
这是 mysqltuner 的结果：
[root@cpc ~]# ./mysqltuner.pl
>> MySQLTuner 1.7.14 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.24
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(98K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 86 warning(s).
[!!] /var/log/mysqld.log contains 177 error(s).
[--] 7 start(s) detected in /var/log/mysqld.log
[--] 1) 2019-01-16T04:40:57.296462Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-01-15T23:34:47.207026Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-01-07T00:33:57.293154Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-01-06T20:10:21.026368Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-01-03T00:13:01.078628Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-01-03T00:05:49.883857Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-01-02T16:03:50.853606Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2019-01-16T04:38:48.819118Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-01-15T23:34:44.152444Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-01-07T00:25:29.383521Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1.1G (Tables: 86)
[--] Data in InnoDB tables: 19.8M (Tables: 8)
[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: 20h 6m 21s (29M q [414.302 qps], 474K conn, TX: 450G, RX: 3G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 156.9G
[--] Max MySQL memory : 338.9M
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 175.8M (0.11% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (0.21% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/29M)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.01% (35/474804)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 27M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5K temp sorts / 12M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 73% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (6 created / 474K connections)
[OK] Table cache hit rate: 60% (905 open / 1K opened)
[OK] Open file limit used: 7% (711/10K)
[OK] Table locks acquired immediately: 99% (31M immediate / 31M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/267.0M
[OK] Read Key buffer hit rate: 97.6% (22B cached / 543M reads)
[!!] Write Key buffer hit rate: 85.8% (36K cached / 31K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/19.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 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: 100.00% (62131502 hits/ 62131969 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
performance_schema = ON enable PFS
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
[root@cpc ~]#
更新：我添加了以下变量，但不确定这是否会对提高性能产生任何影响！
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=17M
max_heap_table_size=17M
performance_schema=ON
以下是 MySQL 文本结果：
显示完整流程清单； https://pastebin.com/t6cKsLJz
显示变量； https://pastebin.com/vu0KEXdd
显示全局变量； https://pastebin.com/kpvVDU7D
显示全球状态； https://pastebin.com/xtH3tD6w
并且对大多数时间运行的查询进行一些额外的检查！ https://pastebin.com/z7AsDjfD
更多信息！ https://pastebin.com/3ZGzXvMT
====================================
显示创建表 wp_posts :-
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT '0',
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`),
KEY `post_name` (`post_name`(191)),
FULLTEXT KEY `wp_greet_box_post_related` (`post_title`,`post_content`)
) ENGINE=MyISAM AUTO_INCREMENT=248367 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
答案1
- 打开 Slowlog 来找出哪些查询造成了最大的麻烦
- 提供
SHOW VARIABLES;并SHOW GLOBAL STATUS;以供进一步分析。
关于如何完成上述事项的更多细节： http://mysql.rjweb.org/doc.php/mysql_analysis
- 从 MyISAM 切换到 InnoDB。
postmeta按照此处指示 修复索引：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta- 增加下面的设置
一些设置更改（上面的 SHOW 可能会有更多更改）。由于您有大量的 RAM，因此在切换到 InnoDB 之前或之后，以下内容将绰绰有余：
key_buffer_size = 2G
innodb_buffer_pool_size = 20G
向 WordPress 论坛或 stackoverflow.com 提出更多问题；serverfault 适用于其他内容。