Тюнинг MySQL-сервера: включение опции innodb_file_per_table

Jul 13, 2015 22:22 · 334 words · 2 minute read mysql innodb

По умолчанию, при использовании движка базы данных 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):
/etc/init.d/zabbix-server stop

/usr/bin/mysqldump --user=USER --password=PASSWORD zabbix | gzip > /tmp/zabbix.sql.gz
  • удаляем базу данных zabbix:
mysql -uUSER -pPASSWORD
drop database zabbix;
  • останавливаем MySQL-сервер:
/etc/init.d/mysql stop
  • вносим правки в конфиг /etc/mysql/my.cnf;
nano /etc/mysql/my.cnf
  • удаляем файлы в директории /var/lib/mysql;
  • запускаем MySQL-сервер:
/etc/init.d/mysql start
  • создаем базу данных zabbix:
mysql -uUSER -pPASSWORD
create database zabbix;
  • восстанавливаем данные из резервной копии:
zcat zabbix.sql.gz | mysql -uUSER -pPASSWORD zabbix
  • проверяем файлы с расширением *.ibd для каждой таблицы:
ls -la /var/lib/mysql/zabbix
  • запускаем zabbix-server:
/etc/init.d/zabbix-server start
tweet Share