php - 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.html
2019-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'

我的猜测是某些东西开始添加选项(可能是与 transient 相关的东西？)并且永远不会停止。

问题来了，怎么调试呢？任何帮助/提示将不胜感激。

每小时 Cron 进行碎片整理看起来是一个非常糟糕的解决方案。

更新:

1 天过去了，可用磁盘空间减少了 7 GB。当前自动增量索引为 206975(昨天有 27 GB 可用空间时为 202517)。所以 4.5K 条目 = 7 GB，我猜？

mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = 'XXX' AND table_name = 'wp_options';
+------------+------------+
| Table      | Size in MB |
+------------+------------+
| wp_options |    7085.52 |
+------------+------------+
1 row in set (0.00 sec)


mysql> select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0 and TABLE_NAME = "wp_options" limit 0, 10;
+--------+------------+-------------+--------------+-----------+
| ENGINE | TABLE_NAME | data_length | index_length | data_free |
+--------+------------+-------------+--------------+-----------+
| InnoDB | wp_options |        7085 |            0 |         5 |
+--------+------------+-------------+--------------+-----------+

我将监控可用空间减少的动态，也许这会更清楚地说明问题。

UPD(最终版)

我觉得这很愚蠢，我是对的。在 functions.php 中有一个 flush_rewrite_rules(); 的所有邪恶的东西。检查一般日志很有帮助。

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