Почему InnoDB дает явно ложную информацию о свободном пространстве

Я пытался узнать, насколько сильно ( "свободное пространство" ) делает мою базу данных после удаления довольно большой таблицы. (Около 10 ГБ)

Я запустил команду:

SELECT table_schema "Data Base Name", 
round( sum( data_free ) / 1024 / 1024 / 1024 ) "Free Space in GB" 
FROM information_schema.TABLES
GROUP BY table_schema;

который дал мне список баз данных и их "свободные пространства".

Проблема заключается в том, что в базе данных, у которой теперь была удалена таблица с размером 10 ГБ, есть 1500 ГБ + свободное пространство в соответствии с этим отчетом, который значительно больше, чем мой фактический объем жесткого диска. (около 200 ГБ)

Как это возможно? Как я могу получить более реалистичный отчет? Я что-то пропустил?

UPDATE

В качестве эксперимента я добавил и удалил таблицу 1GB в этой базе данных, теперь отчет показывает примерно на 110 ГБ больше свободного места. Может возникнуть проблема с моей конфигурацией, или это общая проблема?

Ответ 1

(Это отвечает на некоторые вопросы, зарытые в комментарии.)

Misnomer "Свободное" пространство включает только целые блоки, а не запасные комнаты внутри блоков и многие другие детали.

Случай 1: Все таблицы находятся в ibdata1 - SHOW TABLE STATUS (или эквивалентный запрос в information_schema будет показывать то же значение Data_free, а именно, сколько свободного в ibdata1 Это пространство может быть повторно использовано любой таблицей. Трудно вернуть пространство в ОС.

Случай 2: Все таблицы file_per_table - теперь каждый Data_free относится к пространству для таблицы. И SUM() имеет смысл. (ibdata1 все еще существует, но он не содержит реальных таблиц, есть много других вещей, которые нужны InnoDB.)

Случай 3: Смесь. Если вы включите/отключите file_per_table в разное время, некоторые таблицы будут в ibdata1, у некоторых будут свои собственные табличные пространства.

Случай 4: CREATE TABLESPACE в 5.7. Например, вы можете иметь табличное пространство для каждой базы данных.

Случай 5: PARTITIONed tables. Каждый раздел действует как таблица.

Случай 6: 8.0. Еще больше изменений.

База данных == Каталог В дереве каталогов MySQL каждая база данных может рассматриваться как каталог файловой системы. Внутри этого каталога можно увидеть несколько наборов файлов для каждой таблицы. Файл .frm содержит определение таблицы. Если существует файл .ibd, таблица была создана с помощью file_per_table. Это может быть самый надежный способ узнать, является ли таблица file_per_table. (8.0 здесь будут иметь значительные изменения.)

Сколько места можно использовать повторно? Хорошего ответа нет. Обычно вставка строки найдет место в блоке, где она принадлежит, и Data_free не будет сокращаться. Но, если были разделенные блоки, Data_free может упасть на несколько кратных 16KB (размер блока) или 4MB ( "размер экстента" - или, может быть, 8MB?). Кроме того, случайные вставки приводят к тому, что блоки Брри составляют в среднем около 69%.

Изменение innodb_file_per_table не действует до следующего CREATE TABLE или ALTER TABLE. И тогда это только влияет на то, где положить вновь созданные/скопированные данные + индексы (ibdata1 или .ibd). Он не уничтожит данные.

Большие таблицы обычно имеют от 4 МБ до 7 МБ Data_free. При вычислении количества строк, которые вы можете добавить, не планируйте падение Data_free ниже этого диапазона.

Avg_row_size должен быть полезен. Но иногда это (и строки) слабо аппроксимируются. Их продукт (Data_length) всегда корректен. Таким образом, это может быть хорошей оценкой "строк, которые нужно выполнить, прежде чем захватить больше места у ОС:

(Data_free - 7M) / Avg_row_size

Рекомендации по табличным пространствам. Поместите большие таблицы в файл_пер_ таблицы. Поместите "крошечные" таблицы в ibdata1 или в табличные пространства для базы данных (5.7). Извините, нет простой рекомендации по разделительной линии между "большой" и "крошечной". И неудобно переносить таблицу: SET global innodb_file_per_table = ...;; выйти; логин (чтобы получить глобальный); ALTER TABLE tbl ENGINE=InnoDB;. И это обязательно полная копия таблицы.

( Предостережение: я не упоминал много деталей.)

Ответ 2

Звучит так, как будто у вас нет innondb_file_per_table и поэтому использует общее табличное пространство. Если это так, то вы будете возвращать глобальное "распределенное, но неиспользуемое" разделяемое пространство, повторно для каждого table_schema.