Для активации разделения баз данных по разным файлам используется следующая опция в 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
Благодарю за информацию, а не приведет ли этот перевод на "отдельный файл для каждой базы данных и таблицы" к усугублению ситуации?
ReplyDeleteВедь файл ibdata1 растет не просто так сам по себе, он просто не умеет уменьшаться. Т.е. если временно понадобилось больше места для хранения данных в БД то файл вырос, а потом мы БД почистили, а файл уменьшиться не может.
Если мы переведем в режим "отдельный файл для каждой базы данных и таблицы", то, насколько я понимаю, такой оверхед начнет копиться не в общем файле, а по-немногу в файле каждой таблицы.
И в итоге общий размер файлов получится со временем гораздо больше чем общий файл ibdata1 в котором все таблицы делят пустое место на равных.
Я верно понимаю? Или в режиме innodb_file_per_table mysql уже умеет уменьшать размер файла, если табличка становится меньше?
Тут вопрос двоякий. С пер файл становится ясно - какая таблица расползлась и ее можно сжать банальным дамп/ресторе (и только ее одну - а не всю бд). А вот когда все в одном файле - во-первых, не ясно, кем оно забито.
DeleteНу если постоянно следить за размером всех файлов то да, смысл понятен, но ведь это в теории, а на практике времени следить за этим не будет, в итоге в каждой таблице будет расти оверхед понемногу.... и точно также не будет уметь уменьшаться назад.
DeleteВ итоге при активной работе с БД сумма размеров всех файлов со временем получится значительно больше чем размер ibdata1 файла с общей помойкой.
Т.е. этим действием мы просто создаем иллюзию освобождения места, а на самом деле откладываем проблему на попозже ;)
Так что хотелось бы какую-то опцию вида innodb_file_per_database вместо innodb_file_per_table чтобы потом когда место загадится оверхедом - вычищать его не по одной табличке, а сразу целыми базами оперировать.
Вообще конечно очень странно, что до сих пор не изобрели каких-то методов пересоздать ibdata1 файл без жесткого удаления базы ручками и перезапуска mysql.
Хм, исходя из этого http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 поста получается что все же при innodb_file_per_table данные хранятся немного по-другому, поэтому размеры файлов могут уменьшаться в обратную сторону. Так что вроде как не все так грустно ;)
DeleteА Вы можете проверить, если не сложно, сейчас на своих данных при innodb_file_per_table какой оверхед в размерах файлов присутствует на текущий момент?
У меня сейчас нету крупных Innodb баз :(
DeleteЖаль, попробую тогда у себя конвертнуть сейчас и проверить спустя пару недель, главное не забыть отписаться тут ;)
DeleteИ ещё один вопрос, который меня волнует - это быстродействие: наверное mysql быстрее должен работать с одним общим файлом ibdata1 чем с кучей отдельных файлов на каждую таблицу при innodb_file_per_table
Плюс к этому получаем ещё фрагментацию файлов в самой файловой системе. Хотя, наверное, это не сильно тормозов добавит, т.к. myisam тоже работает с отдельным файлом на таблицу.
Мда, результаты эксперимента печальные ;(
ReplyDeleteКонвертнул сейчас базы свои в innodb_file_per_table, до конвертации был один общий файл ibdata1 - весил 6 гигов, в нем было 2 базы по 2.1 GiB там (информация об объеме данных из phpmyadmin).
Сейчас те же самые базы, но каждая папка базы стала весить по 3.9 гига, итого почти 8 гигов заняло вместо 6 :(
И это сразу после экспорта-импорта, без каких-либо других действий по чтению-записи.
В качестве эксперимента конвертнул одну из баз в формат MyISAM - стала занимать 1.3 гига, обратно в InnoDB - снова 3.9 гига.
Вроде бы есть возможность очистить старые транзакции и сжать Innodb файл, посмотрите тулзы от Percona.
DeleteЯ уже долго смотрел всякие тулзы, в итоге везде приходят к выводу что только жесткое удаление файла может спасти от перерасходования места.
DeleteДа и тут я сделал drop table, потом импорт, и уже получаю 3.9 гига вместо 1.3, навряд ли это из-за хранения каких-то транзакций так получилось, видимо просто формат хранения данных такой с большим излишком. Попробую ещё на досуге импортнуть на голую mysql с выключенным innodb_file_per_table, чтобы понять сколько в таком режиме база займет.