Фрагментация таблиц Wordpress и MySQL
Итак, я использую Wordpress, MySQL (8.0.16), InnoDB. wp_options Таблица обычно составляет 13 МБ. Проблема в том, что он внезапно (по крайней мере, в течение нескольких дней) становится 27 ГБ, а затем перестает расти, потому что больше нет свободного места. Эти 27 ГБ считаются данными, а не индексами.
Сброс и импорт таблицы дает вам таблицу нормального размера. Количество записей около 4 тыс., Индекс автоинкремента 200 тыс. +. Дефрагментация таблицы с 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'
Я предполагаю, что что-то начинает добавлять опции (что-то связанное с переходными процессами, может быть?) И никогда не останавливается.
Вопрос в том, как это отладить? Любая помощь / советы будут оценены.
Почасовой Cron для дефрагментации выглядит очень плохим решением.
UPD:
Прошел 1 день, свободное место на диске сократилось на 7 ГБ. Текущий индекс автоинкремента - 206975 (вчера было 202517, когда было свободно 27 ГБ). Значит, 4,5 тыс. Записей = 7 Гб, наверное?
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 (финал)
У меня было чувство, что это что-то глупое, и я был прав. Был flush_rewrite_rules(); всего нечестивого прямо в functions.php, Изучение общего журнала было полезно.
5 ответов
Вы пробовали с медленным логом? Это может дать вам подсказку, откуда поступают все запросы.
Возможно, вы видите неверную статистику о размере таблицы.
MySQL 8.0 пытается кэшировать статистику о таблицах, но в реализации, похоже, есть некоторые ошибки. Иногда он показывает статистику таблицы как NULL, а иногда показывает значения, но не может обновить их при изменении данных таблицы.
См., Например, https://bugs.mysql.com/bug.php?id=83957, ошибку, которая обсуждает проблемы с этим поведением кэширования.
Вы можете отключить кеширование. Это может привести к тому, что запросы к INFORMATION_SCHEMA или SHOW TABLE STATUS будут немного медленнее, но я думаю, что это не хуже, чем в версиях MySQL до 8.0.
SET GLOBAL information_schema_stats_expiry = 0;
Целочисленное значение - это количество секунд, в течение которых MySQL сохраняет статистику в кэше. Если вы запрашиваете статистику таблицы, вы можете видеть старые значения из кэша, пока они не истекут, и MySQL обновит их, считав из механизма хранения.
Значение по умолчанию для срока действия кэша - 86400 или 24 часа. Это кажется чрезмерным.
См. https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
Если вы думаете, что 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
У вас есть промежуточный сайт, и проблема там повторяется? Если это так, то отключите все плагины (если отключение плагина не нарушает работу вашего сайта) на промежуточном сайте, чтобы убедиться, что проблема прекратится. Если это так, то включите их снова по одному, чтобы узнать, какой плагин вызывает проблему.
Если у вас нет промежуточного сайта, то вы можете попробовать сделать это вживую, но имейте в виду, что вы будете вмешиваться в вашу живую функциональность (как правило, не рекомендуется). В этом случае просто удалите плагины, которые вам абсолютно не нужны, и, надеюсь, один из них является виновником. Если это работает, то добавляйте их по одному, пока не найдете плагин, вызывающий проблему.
Мое предложение выше предполагает, что плагин вызывает эту проблему. Из коробки WordPress не делает этого (не то, что я слышал). Вполне возможно, что для этого используется специальное программирование, но вам необходимо сообщить нам более подробную информацию о последних сценариях.
Самая большая проблема на данный момент заключается в том, что вы не знаете, в чем проблема. Пока вы этого не сделаете, трудно принять корректирующие меры.
Некоторые плагины не могут очистить после себя. Выясните, какие плагины вы добавили во время возникновения проблемы. Посмотри в строках options чтобы увидеть, есть ли подсказки, которые дополнительно затрагивают конкретные плагины.
Нет, ничто из статистики MySQL и т. Д. Не может объяснить "ошибку" в вычислениях размером 27 ГБ. Нет количества OPTIMIZE TABLEи т. д. исправит больше, чем часть этого. Вам нужно удалить большинство строк. Покажите нам некоторые из последних строк (высокий AUTO_INCREMENT Идентификаторы).
Если таблица часто удаляется / обновляется / вставляется, вы можете запустить OPTIMIZE TABLE yourTable;
Его нужно запустить в окне обслуживания.
Это освободит место для повторного использования, но дисковое пространство не будет уменьшено.