我在 wordpress 和 Woocomerce 上有一个网站，上面有 98000 种产品，但没有图片。我有一台专用服务器，配备 8 核 2 ghz xeon 处理器、12 GB RAM。当我在 WOOcomerce 中编辑产品或编辑帖子时，问题仍然存在
编辑产品大约需要 3 分钟，同时会出现消息
连接丢失
并且 CPU 使用率会跃升至 80-100%。
php.ini
max_execution_time = 30
max_input_time = 60
memory_limit = 512M
wp-配置
define('WP_MEMORY_LIMIT', '512M');
define('WP_MAX_MEMORY_LIMIT', '1024M');
这个问题该如何解决？
调整 MySQL 服务器设置
[root@lnx-vps01 ~]# perl mysqltuner.pl
MySQLTuner 1.6.20 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 10.1.21-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 973M (Tables: 193)
[--] Data in InnoDB tables: 21M (Tables: 76)
[OK] Total fragmented tables: 0
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 13h 41m 30s (2M q [46.245 qps], 21K conn, TX: 25G, RX: 400M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory : 11.6G
[--] Max MySQL memory : 6.3G
[--] Other process memory: 2.2G
[--] Total buffers: 5.7G global + 2.9M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 5.8G (50.42% of installed RAM)
[OK] Maximum possible memory usage: 6.3G (54.54% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 16% (32/200)
[OK] Aborted connections: 0.00% (1/21255)
[OK] Query cache efficiency: 35.5% (1M cached / 3M selects)
[!!] Query cache prunes per day: 225257
[OK] Sorts requiring temporary tables: 4% (6K temp sorts / 146K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 76% (149K on disk / 195K total)
[OK] Thread cache hit rate: 99% (32 created / 21K connections)
[OK] Table cache hit rate: 74% (220 open / 294 opened)
[OK] Open file limit used: 1% (247/16K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.1.21-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 36.9% (198M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/323.9M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 97K reads)
[!!] Write Key buffer hit rate: 15.5% (112K cached / 94K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 100.0% (308M cached / 112K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 4.0G/21.5M
[OK] InnoDB buffer pool instances: 4
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (2335483 hits/ 2336273 total)
[!!] InnoDB Write Log efficiency: 27.16% (28436 hits/ 104699 total)
[OK] InnoDB log waits: 0.00% (0 waits / 76263 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 100M)
答案1
由于低声望，我无法发表评论，但为了更好地诊断此问题，您需要查看正在运行并占用所有 CPU 和时间的实际查询。在更新脚本运行时，登录 mysql 并输入 show full processlist
理想情况下，您将看到一个正在运行的查询，它占用了您所有的 CPU，并且运行了一段时间。选择整个查询并EXPLAIN在其前面添加，这样如果您看到的查询是select * from ....make it EXPLAIN select * from ...，它将打印出查询计划。您很可能只需要添加几个索引就可以让您的查询运行得更快。这很可能不是数据库设置问题，而是实现问题。