Оптимизация InnoDB основного движка хранения данных MySQL
InnoDB — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле, изменить это можно с помощью настроек опции innodb_file_per_table.
Как включить MySQL innodb_file_per_table?
MySQL по умолчанию все таблички innodb хранит в одном файле - когда их накапливается приличное количество - файл значительно разрастается. Плюс не забывайте, что при удалении данных в innodb - размер файла не уменьшается - он растет только в большую сторону. Так что если данных в базе у вас много или идет активное удаление - рано или поздно вы задумаетесь о том, чтобы выполнить подобное разделение. Что нужно сделать:
- Выключаем всех клиентов MySQL, не должно быть никаких обращений.
- Делаем ПОЛНЫЙ бекап всех баз данных и конфигурационных файлов
- Удаляем все таблицы из БД
- Выключаем mysqld
- В /etc/my.cnf удаляем старое значение innodb_data_file_path и добавляем
innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1
- удаляем cледующие файлы или сколько там файлов ibdata - оставлять старые logfile нельзя!
/var/lib/ibdata1 /var/lib/ib_logfile0 /var/lib/ib_logfile1
- запускаем mysqld
- Загружаем таблицы обратно из бэкапов
- Проверяем наличие свежесозданных файликов *.ibd
Все!
Как перераспределить неиспользуемое пространство и дефрагментировать файл InnoDB? 2 варианта
Скрипт для оптимизации файлов InnoDB конкретной базы данных. Команда OPTIMIZE TABLE уменьшит размер базы данных за счет: если таблица имеет удаленные или порванные строки, ремонтирует таблицу; если индексные страницы не отсортированы, сортирует их. Если статистика не современна (и ремонт не может быть выполнен с сортировкой индекса), модифицирует ее. Команда OPTIMIZE TABLE должна использоваться после удаления большей части таблицы или если в таблице было внесено много изменений в строки переменной длины (таблицы, в которых есть столбцы VARCHAR, BLOB или TEXT). Удаленные записи поддерживаются при помощи связного списка, и последующие операции INSERT повторно используют позиции старых записей. Чтобы перераспределить неиспользуемое пространство и дефрагментировать файл данных.
#!/bin/bash # Author of the script DOR DB_NAME="battery_hp" TABLES=$(mysql -N -B -e "SHOW TABLES FROM $DB_NAME") for TABLE1 in $TABLES do mysql -e "OPTIMIZE TABLE $TABLE1" $DB_NAME done
Второй вариант оптимизации заключается в пересоздании базы данных. Чтобы уменьшить размер своей базы данных с движком InnoDB, необходимо воспользоваться mysqldump, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.
Заключение
Изучите статью my.cnf примеры конфигурации MySQL, MariaDB


