Размер таблицы MySQL намного больше, чем размер данных в ней, что приводит к медленным запросам сканирования индекса
У меня есть таблица, которая вставляется и удаляется с высокой скоростью. Количество строк никогда не превышает нескольких сотен, и я бы не ожидал, что оно будет больше нескольких мегабайт, но размер таблицы составляет 20 гигабайт и продолжает расти. Он использует MySQL 5.6.35 и движок InnoDB. Я ожидал бы, что вставленные строки будут вставлены в пространство, оставленное удаленными строками, но, похоже, это не так. Он растет до этого уровня в течение недели, и в какой-то момент кажется, что какой-то фоновый процесс запускается и медленно уменьшает его на несколько гигабайт.
Проблема с такой большой таблицей заключается в том, что любые запросы, сканирующие всю таблицу, такие как count(*), выполняются очень медленно.
Есть ли что-то, что я могу изменить, чтобы предотвратить такой рост таблицы?
Ответы 2
Попробуйте OPTIMIZE TABLE на этой таблице.
OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.
InnoDB освобождает место по мере удаления, но не мгновенно. Он быстро помечает записи как удаленные, но свободное пространство позже помечается потоком очистки. Если вы продолжаете вставлять больше данных быстро, вы, вероятно, заставляете InnoDB расширять табличное пространство, по крайней мере, часть времени, потому что вы пытаетесь повторно использовать пространство из удаленных строк до того, как они были очищены.
Например, прочитайте:
- https://dev.mysql.com/doc/refman/5.6/en/innodb-improved-purge-scheduling.html
- https://dev.mysql.com/doc/refman/5.6/en/innodb-multi-versioning.html
If you insert and delete rows in smallish batches at about the same rate in the table, the purge thread can start to lag behind and the table can grow bigger and bigger because of all the “dead” rows, making everything disk-bound and very slow. In such a case, throttle new row operations, and allocate more resources to the purge thread by tuning the innodb_max_purge_lag system variable. See Section 14.14, “InnoDB Startup Options and System Variables” for more information.
Я никогда не сталкивался ни с одним сайтом, который устанавливал innodb_max_purge_lag, потому что это означает, что их непрерывная запись в базу данных может иногда замедляться. Обычно они этого не хотят.
Вместо этого я установил innodb_purge_threads=4 вместо 1 по умолчанию. Это помогает очистке действовать быстро, когда она запускается.
Очистка еще больше задерживается, если ваше приложение держит транзакции открытыми, которым необходимо удерживать эти удаленные строки, чтобы удовлетворить их представление базы данных REPEATABLE-READ. Неважно, насколько быстро выполняется очистка или сколько потоков у вас есть для ее выполнения, если очистка предотвращается требованиями к моментальным снимкам открытых транзакций.
Вы должны совершать транзакции быстро, а не оставлять их открытыми на неопределенное время. Или измените уровень изоляции транзакций на READ-COMMITTED, чтобы ваши транзакции не нуждались в базе данных для сохранения старых версий записей.
Помогут ли дополнительные потоки очистки, если это всего лишь одна таблица? Документация, казалось, предполагала обратное.
Я не знаю точно, но я ожидаю, что это зависит от того, какие строки вы удалили, и насколько вероятно их перекрытие при работе потоков очистки.
@BillKarwin Я также столкнулся с той же проблемой с большой таблицей innodb. Моя скорость удаления намного превышает скорость вставки, а строки, которые я удаляю, очень старые и не читаются/не обновляются ни в каких запросах. Тем не менее, размер данных и размер индекса растут.
@CJ. У вас может быть фрагментация таблицы, когда страницы и экстенты частично заполнены, но в основном пусты. Запуск OPTIMIZE TABLE (или любого другого ALTER TABLE, который копирует таблицу) должен дефрагментировать ее, скопировав таблицы в новое табличное пространство в дефрагментированном формате. Если вы не хотите, чтобы таблица была заблокирована, используйте pt-онлайн-схема-изменение.
В будущем рассмотрите [эти методы] для больших удалений/обновлений.