Почему размер MySQL базы вырос с 13ГБ до 55ГБ после импорта?

Резкое увеличение размера базы данных MySQL с 13ГБ до 55ГБ после импорта из SQL файла обычно связано с техническими особенностями работы InnoDB, особенно при изменении окружения. Основные причины включают различия в размере страниц InnoDB, фрагментацию данных, настройки хранения и стратегию выделения пространства между исходной и целевой системами.

Содержание

• Основные причины увеличения размера базы
• Различия в размерах страниц InnoDB
• Фрагментация и стратегия выделения пространства
• Настройки хранения и файловая структура
• Решения и оптимизация
• Диагностика и мониторинг
• Заключение

Основные причины увеличения размера базы

Увеличение размера базы данных более чем в 4 раза при идентичных данных и структуре таблицы связано с фундаментальными особенностями работы MySQL InnoDB. Как отмечается в исследованиях, InnoDB создает страницы данных и индексные страницы размером 16К каждая, и при операциях вставки, обновления, удаления, фиксации и отката транзакций происходит фрагментация данных [source].

В вашем случае при переходе из Docker в виртуальную машину через Bitrix24 несколько факторов могли повлиять на размер:

1. Разные настройки InnoDB page size - если исходная система использовала страницу 8K, а целевая - 16K, это может удвоить требования к хранению
2. Разные стратегии фрагментации - оригинальная база могла иметь высокую фрагментацию, которая при экспорте-импорте “сбрасывалась” с перераспределением пространства
3. Разные значения innodb_file_per_table - эта настройка определяет, хранятся ли таблицы в отдельных файлах или общих табличных пространствах

Различия в размерах страниц InnoDB

Одной из наиболее значимых причин может быть разница в размере страниц InnoDB между окружениями. Как указано в документации MySQL, MySQL 5.6 и более поздние версии поддерживают разные размеры страниц без перекомпиляции [source].

Важные моменты:

• Стандартный размер страницы InnoDB: 16K
• Доступные варианты: 4K, 8K, 16K
• Размер страницы влияет на эффективность хранения и производительность

В вашем случае, если исходная система использовала страницы 4K или 8K, а целевая - 16K, это объясняет резкое увеличение размера. Даже при одинаковом количестве данных, разные размеры страниц приводят к разному количеству страниц для хранения тех же данных, особенно при наличии переменных-length полей и индексов.

Фрагментация и стратегия выделения пространства

Фрагментация данных - ключевая концепция, объясняющая разницу в размерах. Как объясняют эксперты, при операциях вставки, обновления и удаления строк вы получаете частично заполненные страницы [source].

Особенности InnoDB:

• InnoDB резервирует процент страниц для будущего роста чтобы страницы в B-tree могли выделяться непрерывно
• Способность изменять процент зарезервированных страниц позволяет тонко настраивать InnoDB для решения проблем фрагментации или неэффективного использования пространства хранения [source]
• Когда вы восстанавливаете базу данных с помощью mysqldump, вы пересобираете таблицы с нуля и получаете менее фрагментированную версию базы данных [source]

В вашем сценарии:

1. Исходная база (13ГБ) имела определенный уровень фрагментации
2. При экспорте через Bitrix24 и импорте в новое окружение
3. Произошло пересоздание таблиц с нуля, но с новой стратегией выделения пространства
4. Новая система выделила больше зарезервированного пространства для будущего роста индексов
5. В результате база стала 55ГБ вместо 13ГБ

Настройки хранения и файловая структура

Настройка innodb_file_per_table критически важна для понимания разницы в размерах. Эта настройка определяет, хранятся ли таблицы в отдельных файлах .ibd или в общих табличных пространствах.

Возможные сценарии:

-- Проверка текущей настройки
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- Если = 1 (ON) - таблицы в отдельных файлах
-- Если = 0 (OFF) - таблицы в общих табличных пространствах

Различия в вашем случае:

1. В Docker: возможно использовалось innodb_file_per_table = 0

   • Все таблицы в одном большом файле данных
   • Эффективное использование пространства при фрагментации
   • Размер 13ГБ

2. В VM: возможно innodb_file_per_table = 1

   • Каждая таблица в отдельном .ibd файле
   • Больше накладных расходов на метаданные
   • Размер 55ГБ

Как отмечено в обсуждениях, MySQL не перестраивает весь файл данных, как только вы удаляете строку, по очевидным причинам производительности [source]. В результате при восстановлении в другом окружении с разными настройками происходит перераспределение пространства.

Решения и оптимизация

Для уменьшения размера базы данных и улучшения производительности можно предпринять следующие шаги:

1. Оптимизация таблицы

-- Анализ фрагментации
ANALYZE TABLE ваша_таблица;

-- Оптимизация (дефрагментация)
OPTIMIZE TABLE ваша_таблица;

2. Изменение размера страницы InnoDB

Если проблема в размере страницы, можно пересобрать базу с нужным размером:

-- Проверка текущего размера страницы
SHOW VARIABLES LIKE 'innodb_page_size';

-- Если отличается, потребуется экспорт-импорт с новым размером

3. Настройка зарезервированного пространства

-- Уменьшение процента зарезервированного пространства
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_buffer_pool_size = 4G; -- 25-30% от RAM

4. Пересоздание таблиц с нуля

Как рекомендуют эксперты, чтобы уменьшить размер InnoDB, вам нужно выгрузить данные и импортировать обратно [source]. Это эффективно удаляет фрагментацию.

-- Экспорт структуры
mysqldump -u пользователь -p --no-data база > структура.sql

-- Экспорт данных
mysqldump -u пользователь -p --no-create-info база > данные.sql

-- Импорт в новой среде
mysql -u пользователь -p база < структура.sql
mysql -u пользователь -p база < данные.sql

Диагностика и мониторинг

Для понимания причин увеличения размера и оптимизации производительности необходимо провести диагностику:

1. Проверка размера страниц

-- Размер страниц InnoDB
SHOW VARIABLES LIKE 'innodb_page_size';

-- Размеры файлов таблиц
SELECT table_name, data_length, index_length 
FROM information_schema.tables 
WHERE table_schema = 'ваша_база';

2. Анализ фрагментации

-- Информация о фрагментации
SELECT table_name, data_free, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'ваша_база';

3. Проверка настроек хранения

-- Ключевые настройки InnoDB
SHOW VARIABLES LIKE 'innodb_%';

4. Мониторинг производительности

Поскольку у вас проблемы с производительностью (запросы до 1 минуты), необходимо проверить:

-- Размер буферного пула
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Размер буферного пула в использовании
SHOW STATUS LIKE 'Innodb_buffer_pool%';

Заключение

Резкое увеличение размера базы данных MySQL с 13ГБ до 55ГБ после импорта связано с комплексом технических факторов, связанных с особенностями работы InnoDB при изменении окружения. Основные выводы:

1. Разница в размере страниц InnoDB между Docker и VM - наиболее вероятная причина увеличения размера более чем в 4 раза
2. Фрагментация данных в исходной базе при восстановлении в новом окружении привела к перераспределению пространства
3. Настройки хранения (innodb_file_per_table, зарезервированное пространство) существенно влияют на итоговый размер
4. Производительность упала из-за неоптимальной конфигурации буферного пула и высокой нагрузки на диск

Для решения проблемы рекомендуется проверить и унифицировать настройки InnoDB между окружениями, провести оптимизацию таблиц, а при необходимости выполнить повторный экспорт-импорт с корректными параметрами. Важно понимать, что при изменении окружения MySQL требует пересмотра конфигурации для сохранения производительности и эффективности использования пространства.

Источники

1. Different MySQL Datafile Sizes After Restoration - Database Administrators Stack Exchange
2. MySQL :: MySQL 8.0 Reference Manual :: 17.11.2 File Space Management
3. mysql - why does mysqldump restore to a smaller size? - Database Administrators Stack Exchange
4. How do you remove fragmentation from InnoDB tables? - Database Administrators Stack Exchange
5. Overview of fragmented MySQL InnoDB tables - lefred blog
6. different database size after export and import - HeidiSQL Forum
7. mysql - Innodb page size setting - Stack Overflow
8. r/mysql on Reddit: Mysql.ibd file is huge(40 GB) whereas actual tables are 100 MB only