Файл ibdata1 в MySQL

Как же раздражает, когда на сервере место заканчивается. Прямо слов нет.

Причем происходит это постоянно. И заканчивается, главное, "под ноль", до последнего байта, в результате чего все "падает".

Заходишь, чистишь набежавшие логи, системные бэкапы, разросшиеся таблицы в базе данных (в том же Друпал 7 была бага - таблицы с кэшем сами не очищались и за пару месяцев легко до гигабайта разрастались, хотя Друпал 8 в этом плане тоже не сильно лучше). "Окэй, пару гигабайт выгадал, живем." Но через месяц-другой по новому кругу...

Потом хостер накинул сверху еще гигабайт 5 пространства в качестве апгрейта аккаунта - жил спокойно с полгодика. А сейчас вот снова "Free space: 220MB". Как так?

Нет, ну в самом деле, как так? Ось Дебиан, из софта все по-минимуму, сайтов всего 10, из которых большинство заглушки, какое-то реальное место занимают только два-три. И при этом уходит суммарно 30 гигабайт. Так же быть не должно?

Пошел инвестигейтить, хотя надо было этим заняться еще очень давно.

tulvit@tnode:~$ du -hs ~/public/
9.1G	/home/tulvit/public/

Так, сайты весят сколько и должны, чуть меньше 10 гигов. Остается еще 20 гигабайт, не может же Дебиан столько "съесть".

Логи "текут"?

tulvit@tnode:~$ sudo du -hs /var/log
[sudo] password for tulvit:
31M	/var/log

Нет, не логи.

Стал "пробивать" все подряд, сначала корневые директории, потом их содержимое. И через пять минуток натыкаюсь на это:

tulvit@tnode:~$ sudo du -hs /var/lib/mysql/ibdata1
15G	/var/lib/mysql/ibdata1

15 гигабайт! И что это вообще такое?

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

Вот он и продолжал расти, день ото дня, месяц к месяцу. По чуть-чуть, но все же. За четыре года с момента изначального разворачивания окружения у меня и "накапало" 15 гигов. Ведь баз данных за это время было создано и удалено десятки, если не сотни.

Как сие пофиксить? Увы, только фактически "реланчем".

И раз: бэкапим все наши InnoDB базы, за исключением системных information_schema, mysql и performance_schema.

И два: дропаем все наши базы (акромя системых information_schema, mysql и performance_schema соответственно).

И три: выключаем MySQL.

И четыре: в конфигурационный файл my.cnf добавляем эту строчку:

innodb_file_per_table

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

И пять: удаляем этот самый файлик ibdata1, а также файлы логов ib_logfile0 и ib_logfile1 (лежат там же).

И шесть: включаем MySQL.

И семь: импортируем наши дампы обратно.

Проверяем освободившееся место. Было 220 мегабайт. Стало 14 гигабайт. Счастье-то какое!

UPDATE

См. комментарии (кратко: MyISAM не имеет никакого отношения к ibdata1, если база состоит только из таких таблиц, то ее можно не трогать, также в зависимости от версии MySQL системная база mysql может содержать таблицы типа InnoDB - в таком случае их имеет смысл тоже бэкапить и восстанавливать).

Нормальная тема, такая дырища в плане ресурсов на пустом месте.

Угу, погулил, у людей там и на сотни гигов разрастается, а если есть большая InnoDB табличка с частым insert/delete, так вообще в небеса улетает. По-хорошему, надо продолжать оптимизировать конфиги дальше, где возможно перейти на MyISAM, и т. д., но меня пока устроил и такой быстрофикс.

Здравствуйте. Столкнулся с такой же проблемой. Вы в статье написали первым пунктом, что нужно бэкапить все базы. Получается даже information_schema, mysql и performance_schema тоже бэкапить? И потом восстанавливать их из бэекапа?

Добрый день!

Нет, системные базы (information_schema, mysql и performance_schema) трогать не надо, они как были, так и остаются. Только созданные "пользовательские", я в том числе бэкапил и пересоздавал и базу под phpmyadmin, т. к. по сути тоже стороннее приложение.

Спасибо за ответ. Может было бы хорошо подправить первый пункт статьи, чтобы не было недоразумений. Ещё один вопрос, у меня есть базы как на движке MyISAM так и на InnoDB, проблема как я понял с этим возросшимся файлом касается только баз на InnoDB. Получается базы на MyISAM тоже можно не трогать, не удалять?

Пост поправил, спасибо!

Получается базы на MyISAM тоже можно не трогать, не удалять?

Погуглил чуток - так и есть, ibdata1 не имеет никакого отношения к MyISAM, соответственно удалять последние не нужно.

Хотя попутно возник еще один вопрос - системные базы, та же mysql, имеют таблицы как типов MyISAM, так и InnoDB и других. По идее, если мы их не бекапим, то после удаления файла ibdata1 таблицы типа InnoDB в системных базах станут "битыми". Скорее всего, ничего смертельного, т. к. вся важная информация (тот же список пользователей) хранится в MyISAM, а в InnoDB вспомогательная, которая *возможно* обновиться после рестарта базы.

Однако, еще раз бегло просмотрел кучу статей и обсуждений - нигде на этом не акцентируется внимание, "дропаем все базы кроме системных, удаляем файлик ibdata1, восстанавливаем базы из бэкапа". И вроде как никаких проблем (в том числе и у меня, во всяком случае видимых).

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

Но в целом да, получается, что "белое пятно" все-таки остается, что не хорошо. "Копать" дальше и тестить сейчас уже видимо не буду :(

Хотя еще чуток погулил:

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

- performance_schema содержит временные данные, и тоже так и так постоянно обновляется, можно удалять, можно не удалять.

- что с mysql - кто-то делает бекап и перезаписывает, кто-то нет. Тут главное - эту базу не удалять, иначе и в БД не зайдешь, т. к. и все доступы с паролями потрутся. И еще видимо от версии MySQL зависит. На продакшене у меня MySQL 5 - в системной базе mysql не нашел ни одной InnoDB таблицы, соответственно и никаких проблем. На локальном сервере MySQL 7 - тут уже аж 19 таких таблиц насчитал. В MySQL 6 их вроде штук пять. Т. е. выходит, если в базе все-таки присутствуют InnoDB таблицы, то их имеет смысл все-таки забэкапить и потом восстановить, потаблично или сразу дампом целой базы mysql.

Спасибо за подробный ответ. В общем сегодняшней ночью всё-таки проделал эту чистку. Значит вышло так: я сначала проверил какие базы у меня на InnoDB, оказалось только база почтовика "roundcube" (сама база занимает мало место, т.к. почтовик не используется, но ранее я сам добавлял базу для сайта, но потом удалил, а место база всё равно занимает). В трёх системных базах таблицы оказались на движке MyISAM. Я забекапил только "roundcube" и "mysql" по отдельности (последнюю просто на всякий случай). Далее удалил базу "roundcube" и остановил мускул. Удалил те самые файлы, подправил конфиг. Запустил мускул, восстановил базу "roundcube" из бекапа. При этом базу "mysql" не трогал. И в общем всё прошло нормально. За исключением, что данные всё равно пишутся в тот самый файл. Видимо файл конфига не работает. Бывает, что эти фалы раскиданы в разных местах и какой из них рабочий не понятно. В общем команда "select @@innodb_file_per_table;" выдает, что настройки у меня всё равно равны 0, а не 1. Надо будет все-таки найти рабочий файл ))

Спусти полчаса после своего же ответа, напишу ещё один. Я всё-таки разобрался почему файл настроек, а точнее изменения в нём, никак не отразились и не сработали. Оказывается при перезаписи у этого файла изменились права доступа на 700 root:root, а должны быть 644 root:mysql

Поэтому данные из файла просто не читались из-за ограничения прав доступа. Установка верных прав и групп всё изменило. Кстати, таким образом решилась моя давняя проблема ещё 2-х летней давности с "max_allowed_packet", который я никак не мог увеличить. Вот так вот )) В общем, спасибо за статью. Сегодня ещё раз проделаю процедуру по чистке файла.

Рад слышать, что все в результате получилось!

Про права никогда бы сам не догадался.

Конфигов MySQL действительно много, но вроде как они все рабочие, подгружаются каскадом. Я по-старинке обычно правлю /etc/my.cnf, хотя вроде правильней было бы иметь отдельный файл my.cnf в своей домашней папке.

Проверял, заработало или нет, следующей командой:

sudo ls /var/lib/mysql/

До "чистки" и правки конфигов был только файлик ibdata1, после - по файлику на каждую базу.

После изменения прав я всё-таки повторил процедуру. Теперь в отдельной папке базы по два файла на каждую таблицу (.frm и .ibd). И сами данные теперь хранятся в фалах .ibd. Вроде всё отлично, но пытливый ум заставил заглянуть в файл "ibdata1".

И вот незадача. В самом файле "ibdata1" также добавляются эти данные. Я так и не разобрался почему так получается. Причём отследить это можно не по размеру файла, а по содержимому файла. Т.к. размер файла изменяется по такой формуле "начальное значение, указанное в настройках + 8Мб" и каждый раз, если место в этом файле заполняется, то в файле прибавляется сразу по 8Мб, а не по размеру вставленных данных. Незаполненные данные видимо заполняются нулями что ли. В общем размер файла всегда можно разделить на 1024 и ещё на 1024 и получится точный размер в Мб без остатка. Короче говоря, я всё-так не разобрался почему названия таблиц и полей, а также их содержимое добавляется ещё в "ibdata1" при том, что таблицы уже разделены настройками как надо и вроде бы всё работает как надо. При удалении, например всей базы, папка с файлами удаляется, но данные из "ibdata1" нет :))

Чуть погуглил, это, оказывается, норма.

https://dba.stackexchange.com/a/39160

 

Question: If InnoDB tables and associative indexes are written to individual tablespace files (file extension .ibd), what information still needs to be written to ibdata1 ?

Answer: Here are the following classes of information written to InnoDB's system tablespace:

  • Data Dictionary
  • Double Write Buffer
  • Safety Net to Prevent Data Corruption
  • Helps Bypass OS for Caching
  • Insert Buffer (Streamlines Changes to Secondary Indexes)
  • Rollback Segments
  • Undo Logs

https://bugs.mysql.com/bug.php?id=45173

 

"With multiple tablespaces enabled, InnoDB  stores each *newly* created table into its own tbl_name.ibd  file"

"The --innodb_file_per_table  option affects only table creation, not access to existing tables."

"InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate. "

So this is not a bug.

Если продолжать копать дальше, то, вполне возможно, возникнет желание перейти на какой-нибудь PostgreSQL ^_^

Мдааа уж... Те ещё заморочки ))  Ладно, сохраняю эту статью в закладках, мало ли ещё пригодится. Спасибо вам за ответы ))

Добавить комментарий

Содержимое данного поля является приватным и не предназначено для показа.