Ibdata1 какой файл

Обновлено: 21.11.2024

Системное табличное пространство — это область хранения словаря данных InnoDB, буфера двойной записи, буфера изменений и журналов отмены. Он также может содержать данные таблиц и индексов, если таблицы создаются в системном табличном пространстве, а не в файловых или общих табличных пространствах.

В системном табличном пространстве может быть один или несколько файлов данных. По умолчанию в каталоге данных создается один системный файл данных табличного пространства с именем ibdata1. Размер и количество файлов данных системного табличного пространства определяется параметром запуска innodb_data_file_path. Информацию о конфигурации см. в разделе Конфигурация файла данных системного табличного пространства.

Дополнительная информация о системном табличном пространстве представлена ​​в следующих темах раздела:

Изменение размера системного табличного пространства

В этом разделе описывается, как увеличить или уменьшить размер системного табличного пространства.

Увеличение размера системного табличного пространства

Самый простой способ увеличить размер системного табличного пространства — настроить его на автоматическое расширение. Для этого укажите атрибут autoextend для последнего файла данных в параметре innodb_data_file_path и перезапустите сервер. Например:

Если указан атрибут autoextend, размер файла данных автоматически увеличивается на 8 МБ по мере необходимости. Переменная innodb_autoextend_increment управляет размером приращения.

Вы также можете увеличить размер системного табличного пространства, добавив еще один файл данных. Для этого:

Остановите сервер MySQL.

Если последний файл данных в параметре innodb_data_file_path определен с помощью атрибута autoextend, удалите его и измените атрибут размера, чтобы он отражал текущий размер файла данных. Чтобы определить подходящий размер файла данных, проверьте размер файла в своей файловой системе и округлите это значение до ближайшего значения в МБ, где МБ равен 1024 x 1024 байт.

Добавить новый файл данных к параметру innodb_data_file_path, дополнительно указав атрибут autoextend. Атрибут autoextend можно указать только для последнего файла данных в настройке innodb_data_file_path.

Запустите сервер MySQL.

Например, это табличное пространство имеет один автоматически расширяющийся файл данных:

Предположим, что файл данных со временем увеличился до 988 МБ. Это параметр innodb_data_file_path после изменения атрибута размера, чтобы он отражал текущий размер файла данных, и после указания нового файла данных с автоматическим расширением на 50 МБ:

При добавлении нового файла данных не указывайте имя существующего файла. InnoDB создает и инициализирует новый файл данных при запуске сервера.

Вы не можете увеличить размер существующего файла данных системного табличного пространства, изменив его атрибут размера. Например, изменение параметра innodb_data_file_path с ibdata1:10M:autoextend на ibdata1:12M:autoextend приводит к следующей ошибке при запуске сервера:

Ошибка указывает, что существующий размер файла данных (выраженный в страницах InnoDB) отличается от размера файла данных, указанного в файле конфигурации. Если вы столкнулись с этой ошибкой, восстановите предыдущую настройку innodb_data_file_path и обратитесь к инструкциям по изменению размера системного табличного пространства.

Уменьшение размера системного табличного пространства InnoDB

Используйте mysqldump для создания дампа всех ваших таблиц InnoDB, включая таблицы InnoDB, расположенные в схеме mysql. Определите таблицы InnoDB в схеме mysql, используя следующий запрос:

Остановите сервер.

Удалите все существующие файлы табличного пространства ( *.ibd ), включая файлы ibdata и ib_log. Не забудьте удалить файлы *.ibd для таблиц, расположенных в схеме mysql.

Удалите все файлы .frm для таблиц InnoDB.

Настройте файлы данных для нового системного табличного пространства. См. Конфигурация файла данных системного табличного пространства.

Перезапустите сервер.

Импортируйте файлы дампа.

Если ваши базы данных используют только механизм InnoDB, может быть проще создать дамп всех баз данных, остановить сервер, удалить все базы данных и файлы журналов InnoDB, перезапустить сервер и импортировать файлы дампа.

Чтобы избежать большого системного табличного пространства, рассмотрите возможность использования для ваших данных табличных пространств типа "файл на таблицу" или общих табличных пространств. Табличные пространства файлов на таблицу являются типом табличного пространства по умолчанию и используются неявно при создании таблицы InnoDB. В отличие от системных табличных пространств, табличные пространства файлов на таблицу возвращают дисковое пространство операционной системе, когда они усекаются или отбрасываются. Для получения дополнительной информации см. Раздел 14.6.3.2, «Табличные пространства File-Per-Table». Общие табличные пространства — это многотабличные табличные пространства, которые также можно использовать в качестве альтернативы системному табличному пространству. См. Раздел 14.6.3.3, «Общие табличные пространства».

Использование необработанных разделов диска для системного табличного пространства

Необработанные разделы диска можно использовать в качестве файлов данных системного табличного пространства. Этот метод обеспечивает небуферизованный ввод-вывод в Windows и некоторых системах Linux и Unix без дополнительных затрат на файловую систему.Выполните тесты с необработанными разделами и без них, чтобы проверить, улучшают ли они производительность вашей системы.

При использовании раздела необработанного диска убедитесь, что идентификатор пользователя, который запускает сервер MySQL, имеет права на чтение и запись для этого раздела. Например, если сервер запущен от имени пользователя mysql, раздел должен быть доступен для чтения и записи для mysql. При запуске сервера с параметром --memlock сервер должен запускаться от имени пользователя root , поэтому раздел должен быть доступен для чтения и записи для пользователя root .

Описанные ниже процедуры включают изменение файла опций. Для получения дополнительной информации см. Раздел 4.2.2.2, «Использование файлов опций».

Выделение необработанного раздела диска в системах Linux и Unix

При создании нового файла данных укажите ключевое слово newraw сразу после размера файла данных для параметра innodb_data_file_path. Размер раздела должен быть не меньше указанного вами размера. Обратите внимание, что 1 МБ в InnoDB – это 1024 × 1024 байта, тогда как 1 МБ в спецификации диска обычно означает 1 000 000 байт.

Перезапустите сервер. InnoDB замечает ключевое слово newraw и инициализирует новый раздел. Однако пока не создавайте и не изменяйте таблицы InnoDB. В противном случае при следующем перезапуске сервера InnoDB повторно инициализирует раздел, и ваши изменения будут потеряны. (В качестве меры безопасности InnoDB запрещает пользователям изменять данные, если указан какой-либо раздел с newraw.)

После того, как InnoDB инициализирует новый раздел, остановите сервер, измените newraw в спецификации файла данных на raw :

Перезапустите сервер. Теперь InnoDB позволяет вносить изменения.

Выделение необработанного раздела диска в Windows

В системах Windows применяются те же шаги и сопутствующие рекомендации, что и для систем Linux и Unix, за исключением того, что параметр innodb_data_file_path немного отличается в Windows.

При создании нового файла данных укажите ключевое слово newraw сразу после размера файла данных для опции innodb_data_file_path:

//./ соответствует синтаксису Windows \\.\ для доступа к физическим дискам. В приведенном выше примере D: — это буква диска раздела.

Перезапустите сервер. InnoDB замечает ключевое слово newraw и инициализирует новый раздел.

После того, как InnoDB инициализирует новый раздел, остановите сервер, измените newraw в спецификации файла данных на raw :

Мы часто получаем этот вопрос о файле ibdata1 в MySQL в службе поддержки Percona.

Паника начинается, когда сервер мониторинга отправляет предупреждение о хранилище сервера MySQL, сообщая, что диск вот-вот переполнится.

После некоторых исследований вы понимаете, что большая часть дискового пространства используется общим табличным пространством InnoDB ibdata1. У вас включен innodb_file_per_table, поэтому вопрос:

Что хранится в ibdata1?

Когда у вас включен innodb_file_per_table, таблицы хранятся в их собственном табличном пространстве, но общее табличное пространство по-прежнему используется для хранения других внутренних данных InnoDB:

  • словарь данных, также известный как метаданные таблиц InnoDB
  • изменить буфер
  • буфер двойной записи
  • журналы отмены

Некоторые из них можно настроить на Percona Server, чтобы они не стали слишком большими. Например, вы можете установить максимальный размер буфера изменений с помощью innodb_ibuf_max_size или сохранить буфер двойной записи в отдельном файле с помощью innodb_doublewrite_file.

В MySQL 5.6 вы также можете создавать внешние табличные пространства UNDO, чтобы они были в своих собственных файлах, а не хранились внутри ibdata1. Проверьте следующую ссылку на документацию.

Что заставляет ibdata1 так быстро расти?

Обычно первая команда, которую нам нужно выполнить при возникновении проблемы с MySQL, это:

ПОКАЖИТЕ СТАТУС ДВИГАТЕЛЯ INNODB

Это покажет нам очень ценную информацию. Начинаем проверять раздел ТРАНЗАКЦИИ и находим вот это:

---TRANSACTION 36E, ACTIVE 1256288 sec
Идентификатор потока MySQL 42, дескриптор потока ОС 0x7f8baaccc700, идентификатор запроса 7900290 root localhost
показать статус движка innodb
Trx read view не увидит trx с идентификатором >= 36F, видит

Это самая распространенная причина: довольно старая транзакция, созданная 14 дней назад. Состояние АКТИВНО означает, что InnoDB создал моментальный снимок данных, поэтому ему необходимо поддерживать старые страницы в состоянии отмены, чтобы обеспечить согласованное представление базы данных с момента запуска этой транзакции. Если ваша база данных сильно загружена записью, это означает, что хранится много страниц отмены.

Если вы не найдете долго выполняющихся транзакций, вы также можете отслеживать другую переменную из INNODB STATUS, «Длина списка истории». Он показывает количество ожидающих операций очистки. В этом случае проблема обычно возникает из-за того, что поток очистки (или главный поток в более старых версиях) не может обрабатывать записи отмены с той же скоростью, с которой они приходят.

Как проверить, что хранится в ibdata1?

К сожалению, MySQL не предоставляет информацию о том, что хранится в общем табличном пространстве ibdata1, но есть два очень полезных инструмента. Первая модифицированная версия innochecksum, созданная Марком Каллаганом и опубликованная в этом отчете об ошибке.

Он довольно прост в использовании:

В нем 19272 страницы UNDO_LOG из 20608. Это 93% табличного пространства.

Второй способ проверить содержимое табличного пространства — инструмент InnoDB Ruby Tools, созданный Джереми Коулом. Это более продвинутый инструмент для изучения внутреннего устройства InnoDB. Например, мы можем использовать параметр space-summary, чтобы получить список с каждой страницей и ее типом данных. Мы можем использовать стандартные инструменты Unix, чтобы получить количество страниц UNDO_LOG:

Хотя в этом конкретном случае innochecksum быстрее и проще в использовании, я рекомендую вам поиграть с инструментами Джереми, чтобы узнать больше о распределении данных внутри InnoDB и ее внутренних компонентах.

Хорошо, теперь мы знаем, в чем проблема. Следующий вопрос:

Как решить проблему?

Ответить на этот вопрос легко. Если вы все еще можете зафиксировать этот запрос, сделайте это. Если нет, вам придется убить поток, чтобы начать процесс отката. Это просто остановит рост ibdata1, но ясно, что в вашем программном обеспечении есть ошибка или кто-то допустил ошибку. Теперь, когда вы знаете, как определить, в чем проблема, вам нужно найти, кто или что ее вызывает, используя собственные средства отладки или общий журнал запросов.

Если проблема вызвана потоком очистки, решение обычно заключается в обновлении до более новой версии, в которой можно использовать выделенный поток очистки вместо главного потока. Дополнительная информация по следующей ссылке на документацию.

Есть ли способ восстановить использованное пространство?

Нет, это невозможно — по крайней мере, простым и быстрым способом. Табличные пространства InnoDB никогда не уменьшаются… см. следующий отчет об ошибке 10-летней давности, недавно обновленный Джеймсом Дэем (спасибо):

При удалении некоторых строк страницы помечаются как удаленные для повторного использования позже, но пространство никогда не восстанавливается. Единственный способ — запустить базу данных со свежим ibdata1. Для этого вам нужно будет сделать полную логическую резервную копию с помощью mysqldump. Затем остановите MySQL и удалите все базы данных, файлы ib_logfile* и ibdata*. Когда вы снова запустите MySQL, он создаст новое новое общее табличное пространство. Затем восстановите логический дамп.

Обзор

Когда файл ibdata1 растет слишком быстро в MySQL, это обычно вызвано длительной транзакцией, о которой мы забыли. Постарайтесь решить проблему как можно быстрее (зафиксировав или завершив транзакцию), потому что без мучительно медленного процесса mysqldump вы не сможете восстановить потраченное впустую дисковое пространство.

Также рекомендуется контролировать базу данных, чтобы избежать подобных проблем. Наши подключаемые модули мониторинга MySQL включают сценарий Nagios, который может предупредить вас, если он находит слишком старую выполняющуюся транзакцию.

Связанные

Автор

Мигель присоединился к Percona в октябре 2011 года. Он работал системным администратором в консультанте по бесплатному программному обеспечению и в сфере поддержки крупнейшей хостинговой компании в Испании. В настоящее время он занимается улучшением MySQL и помогает расти сообществу свободного программного обеспечения.

В MySQL, когда вы используете InnoDB, все таблицы и индексы хранятся в системном табличном пространстве MySQL.

Системное табличное пространство MySQL — ibdata1, расположенное в /var/lib/mysql

Единый файл ibdata1 содержит все таблицы и индексы в вашей базе данных MySQL. Таким образом, если у вас большая база данных, размер этого файла будет очень большим.

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

1. Большое системное табличное пространство MySQL (и MariaDB)

Существует серьезный недостаток подхода по умолчанию к большому системному табличному пространству MySQL.

В качестве примера возьмем этот сценарий: вы загрузили данные объемом 100 ГБ в несколько таблиц в MySQL.

Теперь размер файла ibdata1 будет около 100 ГБ+.

Через несколько дней вы удалили около 50 ГБ данных из всех этих таблиц. Размер файла ibdata1 не будет уменьшен примерно до 50 ГБ и более, он по-прежнему останется на уровне 100 ГБ и более.

В приведенном выше случае позже, когда вы добавите в таблицы данные объемом 10 ГБ, размер файла ibdata1 не увеличится до 110 ГБ и останется равным 100 ГБ. Потому что в файле все еще есть неиспользованное пространство из вышеупомянутых 50 ГБ удаленных данных.

Проблема в том, что вы не можете восстановить это неиспользуемое пространство после удаления 50 ГБ данных из файла ibdata1. Есть способ сделать это, но он слишком сложен (поясняется ниже) и требует отключения базы данных MySQL.

Итак, как избежать хранения всех таблиц и индексов в одном файле ibdata1; вместо этого хранить в нескольких табличных файлах по отдельности?

2. Установите параметр innodb_file_per_table

Для этого вы должны использовать параметр innodb_file_per_table внутри вашего файла /etc/my.cnf в разделе «mysqld», как показано ниже:

Примечание. Если вы используете MySQL 5.6.6 (или MariaDB) и более поздние версии, указанное выше значение является настройкой по умолчанию.

В этом примере в CentOS 6 MySQL по умолчанию, который вы получаете при установке из репозитория yum, по-прежнему имеет версию 5.1.73, как показано ниже.

Итак, в этом случае мы должны установить innodb_file_per_table в файле my.cnf.

Каждый раз, когда вы вносите какие-либо изменения в my.cnf, вы должны перезапустить базу данных MariaDB MySQL.

Примечание. По какой-то причине, если вы хотите установить этот параметр во время работы базы данных и не хотите останавливать работу MariaDB, вы можете выполнить следующую команду set global из командной строки mysql.

3. Новые таблицы (и индекс) в виде отдельных файлов

Отныне при создании новой таблицы MySQL вы будете получать отдельные файлы.

В этом примере я создал новую таблицу под названием employee и загрузил в нее около 20 ГБ данных.

Во-первых, он создаст подкаталог с именем базы данных "thegeekstuff" в каталоге /var/lib/mysql

Во-вторых, под этим именем каталога базы данных (то есть в каталоге geekstuff) вы увидите, что создается отдельный файл EMPLOYEE.IBD. Размер этого файла будет равен размеру данных, которые вы загрузили только в эту таблицу. В этом случае, поскольку мы загрузили в эту таблицу данные объемом 20 ГБ, размер файла EMPLOYEE.IBD составляет около 20 ГБ, как показано ниже.

Примечание. Если вы используете базу данных MyISAM, вы увидите отдельные файлы .MYD, .FRM и .MYI

4. Извлечь существующие таблицы из ibdata1

Далее, если вы хотите извлечь существующую таблицу из ibdata1 в отдельный файл, вам нужно оптимизировать таблицу.

Допустим, у вас есть таблица с преимуществами в базе данных geekstuff. Эта таблица преимуществ была создана до того, как мы установили innodb_file_per_table в файле my.cnf.

Таким образом, таблица преимуществ по-прежнему будет находиться в файле ibdata1. Чтобы переместить это из ibdata1 в отдельный файл IBD, мы должны оптимизировать таблицу, как показано ниже.

Это создаст следующий отдельный файл для таблицы преимуществ.

В этом примере имейте в виду, что исходный файл ibdata1 по-прежнему не сжимался. Он по-прежнему составляет около 100 ГБ.

Примечание. Вы также можете сделать следующее.

5. Уменьшить размер файла ibdata1

Имейте в виду, что размер ibdata1 остается прежним (101 ГБ), он еще не уменьшил размер.

Чтобы сжать файл ibdata1, необходимо выполнить следующие шаги:

6. Резервное копирование базы данных

Во-первых, временно остановите базу данных mysql и создайте холодную резервную копию всей базы данных. Если что-то пойдет не так, вы можете использовать эту холодную резервную копию для восстановления.

Во-вторых, сделайте резервную копию всей базы данных с помощью mysqldump.

Подробнее о mysqldump см. в статье о том, как использовать mysqldump.

7. Удалите всю свою базу данных

Затем удалите все базы данных одну за другой. Чтобы просмотреть всю удаляемую базу данных, используйте «показать базы данных»

В этом примере мы удаляем две базы данных (thegeekstuff и sales), существующие в mysql.

Примечание. Не удаляйте базу данных information_schema и mysql.

8. Удалить ibdata и ib_logfile

Затем отключите базу данных MySQL.

Далее удалите файл ibdata1 и все отдельные файлы ib_logfile*:

9. Импорт всей базы данных

Примечание. К этому моменту в файле my.cnf уже должно быть следующее.

Запустите базу данных MySQL.

Импортируйте всю базу данных из резервной копии mysqldump, которую мы сделали ранее.

На этом этапе файл ibdata1, представляющий собой системное табличное пространство MySQL, будет создан с нуля, и в нашем примере он больше не будет занимать 100 ГБ.

Теперь ibdata1 будет иметь размер всего несколько МБ. Все таблицы базы данных будут храниться как отдельные файлы в соответствующем подкаталоге базы данных в /var/lib/mysql/

Я использую MySQL на локальном хосте в качестве «инструмента запросов» для выполнения статистики в R, то есть каждый раз, когда я запускаю скрипт R, я создаю новую базу данных (A), создаю новую таблицу (B), импортирую данные в B, отправляю запрос, чтобы получить то, что мне нужно, а затем я удаляю B и удаляю A.

У меня это работает нормально, но я понимаю, что размер файла ibdata быстро увеличивается, я ничего не сохранял в MySQL, но файл ibdata1 уже превысил 100 МБ.

Я использую более или менее настройки MySQL по умолчанию для установки, есть ли способ автоматически сжимать/очищать файл ibdata1 по истечении фиксированного периода времени?

8 ответов 8

Тот факт, что ibdata1 не сжимается, является особенно раздражающей особенностью MySQL.Файл ibdata1 нельзя уменьшить, если вы не удалите все базы данных, не удалите файлы и не перезагрузите дамп.

Но вы можете настроить MySQL так, чтобы каждая таблица, включая ее индексы, сохранялась в виде отдельного файла. Таким образом, ibdata1 не будет увеличиваться до такого размера. Согласно комментарию Билла Карвина, это включено по умолчанию в версии 5.6.6 MySQL.

Это было давно. Однако, чтобы настроить сервер на использование отдельных файлов для каждой таблицы, вам нужно изменить my.cnf, чтобы включить это:

Поскольку вы хотите освободить место от ibdata1, вам фактически нужно удалить файл:

  1. Выполните mysqldump всех баз данных, процедур, триггеров и т. д., кроме баз данных mysql и performance_schema
  2. Удалить все базы данных, кроме двух указанных выше.
  3. Остановить MySQL
  4. Удалить файлы ibdata1 и ib_log
  5. Запустить MySQL
  6. Восстановить из дампа

Когда вы запустите MySQL на шаге 5, файлы ibdata1 и ib_log будут созданы заново.

Теперь можно идти. Когда вы создаете новую базу данных для анализа, таблицы будут располагаться в отдельных файлах ibd*, а не в ibdata1. Поскольку вы обычно удаляете базу данных вскоре после этого, файлы ibd* будут удалены.

С помощью команды ALTER TABLE ENGINE=innodb или OPTIMIZE TABLE можно извлечь данные и индексировать страницы из ibdata1 в отдельные файлы. Однако ibdata1 не уменьшится, если вы не выполните описанные выше действия.

Читайте также: