WordPress的MySQL表碎片 [英] Wordpress & MySQL table fragmentation

问题描述

因此，我正在使用Wordpress，MySQL(8.0.16)，InnoDB. wp_options 表通常为13 MB.问题是，由于没有更多可用空间，它突然(至少在几天之内)变为27 GB，然后停止增长.那27 GB被认为是数据，而不是索引.

So, I'm using Wordpress, MySQL (8.0.16), InnoDB. The wp_options table normally is 13 MB. The problem is, it suddenly (at least within a span of a few days) becomes 27 GB and then stops growing, because there's no more space available. Those 27 GB are considered data, not indexes.

转储和导入表将为您提供正常大小的表.条目数约为4k，自动递增索引为200k +.使用 ALTER TABLE wp_options ENGINE = InnoDB; 对表进行碎片整理会将磁盘上的表大小更改为普通表，但是mysql还是认为是这样，即使在服务器重启后也是如此.

Dumping and importing the table gives you a table of the normal size. The number of entries is around 4k, the autoincrement index is 200k+. Defragmenting table with ALTER TABLE wp_options ENGINE = InnoDB; changes the table size on disk to normal, but mysql thinks otherwise, even after the server restart.

+------------+------------+
| Table      | Size in MB |
+------------+------------+
| wp_options |   26992.56 |
+------------+------------+
1 row in set (0.00 sec)

MySQL日志不多说:

MySQL logs don't say much:

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'

我的猜测是，某些东西开始添加选项(可能是与瞬态相关的东西?)，而且永远不会停止.

My guess is that something starts adding options (something transient-related, maybe?) and never stops.

问题是，如何调试它?任何帮助/提示将不胜感激.

The question is, how to debug it? Any help/hints would be appreciated.

小时Cron进行碎片整理似乎是一个非常糟糕的解决方案.

Hourly Cron to defragment looks like a very bad solution.

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

1 day had passed, free disk space decreased by 7 GB. Current autoincrement index is 206975 (and it was 202517 yesterday when there were 27 GB free). So 4.5K entries = 7 GB, I guess?

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 |
+--------+------------+-------------+--------------+-----------+

我将监视自由空间减少的动态情况，也许这将使我们对该问题有更多的了解.

I will monitor the dynamics of how free space decreases, maybe that would shed some more light on the problem.

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

I had a feeling it was something stupid, and I was right. There was a flush_rewrite_rules(); of all things unholy right in the functions.php. Examining the general log was helpful.

推荐答案

您是否尝试过使用

Have you tried with slow log? That might give you some hint where all the queries come from.

这篇关于WordPress的MySQL表碎片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！