Включение опции innodb_file_per_table

По умолчанию, при использовании движка базы данных InnoDB, все таблицы и индексы хранятся в системном табличном пространстве (по сути в одном файле /var/lib/mysql/ibdata). Когда данных накапливается много, файл значительно разрастается, и с этим могут возникать различные проблемы и неудобства.

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

  • можно хранить разные таблицы одной базы данных на разных физических устройствах;
  • операция TRUNCATE [TABLE] tbl_name выполняется гораздо быстрее;
  • можно выполнять операцию OPTIMIZE TABLE tbl_name (в случае использования движка базы данных InnoDB — то же самое, что и ALTER TABLE tbl_name ENGINE=InnoDB);
  • таблицы, созданные с параметром innodb_file_per_table могут использовать innodb_file_format=Barracuda, а этот формат в свою очередь дает возможность работать с ROW_FORMAT=COMPRESSED и ROW_FORMAT=DYNAMIC;
  • в очень больших базах данных этот параметр и вовсе незаменим — согласно официальной документации, системное табличное пространство ограничено объемом в 64TB, а включение опции innodb_file_per_table устанавливает лимит 64TB для каждой таблицы (есть куда расти).

Алгоритм включения опции innodb_file_per_table следующий:

  • останавливаем сервисы, использующие базу данных (apache, zabbix, sphinxsearch и т. д.);
  • делаем резервную копию (дамп) базы данных;
  • удаляем базу данных;
  • останавливаем MySQL-сервер;
  • в конфиге /etc/mysql/my.cnf удаляем параметр innodb_data_file_path и добавляем параметр innodb_file_per_table;
  • удаляем файлы /var/lib/mysql/ibdata/var/lib/mysql/ib_logfile0/var/lib/mysql/ib_logfile1 и т. д.;
  • запускаем MySQL-сервер;
  • создаем базу данных;
  • восстанавливаем данные из резервной копии;
  • проверяем наличие файлов с расширением *.ibd для каждой таблицы;
  • запускаем остановленные в первом пункте сервисы.

Наример, так можно проделать вышеописанную процедуру с базой данных zabbix:

  • останавливаем сервисы работающие с базой (zabbix-server):

  • удаляем базу данных zabbix:

drop database zabbix;
  • останавливаем MySQL-сервер:

  • вносим правки в конфиг /etc/mysql/my.cnf;

  • удаляем файлы в директории /var/lib/mysql;
  • запускаем MySQL-сервер:

  • создаем базу данных zabbix:

create database zabbix;
  • восстанавливаем данные из резервной копии:

  • проверяем файлы с расширением *.ibd для каждой таблицы:

  • запускаем zabbix-server: