Wordpress & MySQL 表碎片化

Question

所以，我使用的是 Wordpress、MySQL (8.0.16)、InnoDB。该wp_options表通常为 13 MB。问题是，它突然（至少在几天之内）变成 27 GB，然后停止增长，因为没有更多可用空间。这 27 GB 被视为数据，而不是索引。转储和导入表会给你一个正常大小的表。条目数约为 4k，自增索引为 200k+。对表进行碎片整理ALTER TABLE wp_options ENGINE = InnoDB;会将磁盘上的表大小更改为正常，但即使在服务器重新启动后，mysql 也不这么认为。+------------+------------+| Table      | Size in MB |+------------+------------+| wp_options |   26992.56 |+------------+------------+1 row in set (0.00 sec)MySQL 日志不多说了：2019-08-05T17:02:41.939945Z 1110933 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./XXX/wp_options.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/8.0/en/operating-system-error-codes.html2019-08-05T17:02:41.941604Z 1110933 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 774144 bytes written. Retrying for the remaining bytes.2019-08-05T17:02:41.941639Z 1110933 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.2019-08-05T17:02:41.941655Z 1110933 [ERROR] [MY-012639] [InnoDB] Write to file ./XXX/wp_options.ibd failed at offset 28917628928, 1048576 bytes should have been written, only 774144 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.2019-08-05T17:02:41.941673Z 1110933 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'我的猜测是有些东西开始添加选项（一些与瞬态相关的东西，也许？）并且永远不会停止。问题是，如何调试呢？任何帮助/提示将不胜感激。每小时进行碎片整理的 Cron 看起来是一个非常糟糕的解决方案。

Answer 1

一种可能性是您看到有关表大小的统计数据不正确。

MySQL 8.0 尝试缓存有关表的统计信息，但在实现中似乎存在一些错误。有时它会将表统计信息显示为 NULL，有时它会显示值，但在您修改表数据时无法更新它们。

例如，参见https://bugs.mysql.com/bug.php?id=83957，一个讨论这种缓存行为问题的错误。

您可以禁用缓存。它可能会导致对 INFORMATION_SCHEMA 或 SHOW TABLE STATUS 的查询慢一点，但我想它不会比 8.0 之前的 MySQL 版本差。

SET GLOBAL information_schema_stats_expiry = 0;

整数值是 MySQL 缓存统计信息的秒数。如果您查询表统计信息，您可能会看到缓存中的旧值，直到它们过期并且 MySQL 通过从存储引擎读取来刷新它们。

缓存过期的默认值为 86400，即 24 小时。这似乎太过分了。

见https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_schema_stats_expiry

如果您认为 Wordpress 正在写入表格，那么它可能是。您可以启用二进制日志或查询日志来查找。或者只是观察SHOW PROCESSLIST几分钟。

您可能有一个经常更新或插入表格的 wordpress 插件。您可以查找最新的update_time：

SELECT * FROM INFORMATION_SCHEMA.TABLES
ORDER BY UPDATE_TIME DESC LIMIT 3;

观看此内容以找出最近写入的表。

此 UPDATE_TIME 统计数据有一些注意事项。它并不总是与更新表的查询同步，因为写入表空间文件是异步的。在这里阅读：https : //dev.mysql.com/doc/refman/8.0/en/tables-table.html

Answer 2

你试过慢日志吗？这可能会给您一些提示，所有查询来自哪里。

Answer 3

某些插件无法自行清理。追查您在问题开始时添加的插件。查看行中options是否有进一步暗示特定插件的线索。

不，MySQL 的统计数据等无法解释计算中的 27GB '错误'。再多的OPTIMIZE TABLE, 等也无法解决其中的一小部分。您需要删除大部分行。向我们展示一些最近的行（高AUTO_INCREMENTid）。