FastNetMon

Thursday 21 January 2010

Cлишком большой файл ibdata1 в MySQL

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

Для активации разделения баз данных по разным файлам используется следующая опция в my.cnf (добавлять в блок [mysqld]):

innodb_file_per_table


После этого необходим рестарт СУБД:
/etc/init.d/mysqld restart


Все, теперь все вновь создаваемые InnoDB таблицы будут создаваться в отдельных файлах внутри папок с именами баз данных (с расширением .ibd, причем и индексы и данные лежат вместе).

источник: http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

9 comments :

  1. Благодарю за информацию, а не приведет ли этот перевод на "отдельный файл для каждой базы данных и таблицы" к усугублению ситуации?

    Ведь файл ibdata1 растет не просто так сам по себе, он просто не умеет уменьшаться. Т.е. если временно понадобилось больше места для хранения данных в БД то файл вырос, а потом мы БД почистили, а файл уменьшиться не может.

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

    И в итоге общий размер файлов получится со временем гораздо больше чем общий файл ibdata1 в котором все таблицы делят пустое место на равных.

    Я верно понимаю? Или в режиме innodb_file_per_table mysql уже умеет уменьшать размер файла, если табличка становится меньше?

    ReplyDelete
    Replies
    1. Тут вопрос двоякий. С пер файл становится ясно - какая таблица расползлась и ее можно сжать банальным дамп/ресторе (и только ее одну - а не всю бд). А вот когда все в одном файле - во-первых, не ясно, кем оно забито.

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

      В итоге при активной работе с БД сумма размеров всех файлов со временем получится значительно больше чем размер ibdata1 файла с общей помойкой.

      Т.е. этим действием мы просто создаем иллюзию освобождения места, а на самом деле откладываем проблему на попозже ;)

      Так что хотелось бы какую-то опцию вида innodb_file_per_database вместо innodb_file_per_table чтобы потом когда место загадится оверхедом - вычищать его не по одной табличке, а сразу целыми базами оперировать.

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

      Delete
    3. Хм, исходя из этого http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 поста получается что все же при innodb_file_per_table данные хранятся немного по-другому, поэтому размеры файлов могут уменьшаться в обратную сторону. Так что вроде как не все так грустно ;)
      А Вы можете проверить, если не сложно, сейчас на своих данных при innodb_file_per_table какой оверхед в размерах файлов присутствует на текущий момент?

      Delete
    4. У меня сейчас нету крупных Innodb баз :(

      Delete
    5. Жаль, попробую тогда у себя конвертнуть сейчас и проверить спустя пару недель, главное не забыть отписаться тут ;)

      И ещё один вопрос, который меня волнует - это быстродействие: наверное mysql быстрее должен работать с одним общим файлом ibdata1 чем с кучей отдельных файлов на каждую таблицу при innodb_file_per_table
      Плюс к этому получаем ещё фрагментацию файлов в самой файловой системе. Хотя, наверное, это не сильно тормозов добавит, т.к. myisam тоже работает с отдельным файлом на таблицу.

      Delete
  2. Мда, результаты эксперимента печальные ;(

    Конвертнул сейчас базы свои в innodb_file_per_table, до конвертации был один общий файл ibdata1 - весил 6 гигов, в нем было 2 базы по 2.1 GiB там (информация об объеме данных из phpmyadmin).

    Сейчас те же самые базы, но каждая папка базы стала весить по 3.9 гига, итого почти 8 гигов заняло вместо 6 :(

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

    В качестве эксперимента конвертнул одну из баз в формат MyISAM - стала занимать 1.3 гига, обратно в InnoDB - снова 3.9 гига.

    ReplyDelete
    Replies
    1. Вроде бы есть возможность очистить старые транзакции и сжать Innodb файл, посмотрите тулзы от Percona.

      Delete
    2. Я уже долго смотрел всякие тулзы, в итоге везде приходят к выводу что только жесткое удаление файла может спасти от перерасходования места.
      Да и тут я сделал drop table, потом импорт, и уже получаю 3.9 гига вместо 1.3, навряд ли это из-за хранения каких-то транзакций так получилось, видимо просто формат хранения данных такой с большим излишком. Попробую ещё на досуге импортнуть на голую mysql с выключенным innodb_file_per_table, чтобы понять сколько в таком режиме база займет.

      Delete

Note: only a member of this blog may post a comment.