Оптимизация InnoDB основного движка хранения данных MySQL

InnoDB — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле, изменить это можно с помощью настроек опции innodb_file_per_table.

MySQL по умолчанию все таблички innodb хранит в одном файле - когда их накапливается приличное количество - файл значительно разрастается. Плюс не забывайте, что при удалении данных в innodb - размер файла не уменьшается - он растет только в большую сторону. Так что если данных в базе у вас много или идет активное удаление - рано или поздно вы задумаетесь о том, чтобы выполнить подобное разделение. Что нужно сделать:

  1. Выключаем всех клиентов MySQL, не должно быть никаких обращений.
  2. Делаем ПОЛНЫЙ бекап всех баз данных и конфигурационных файлов
  3. Удаляем все таблицы из БД
  4. Выключаем mysqld
  5. В /etc/my.cnf удаляем старое значение innodb_data_file_path и добавляем
    innodb_data_file_path=ibdata1:10M:autoextend
    innodb_file_per_table=1
  6. удаляем cледующие файлы или сколько там файлов ibdata - оставлять старые logfile нельзя!
    /var/lib/ibdata1
    /var/lib/ib_logfile0
    /var/lib/ib_logfile1
  7. запускаем mysqld
  8. Загружаем таблицы обратно из бэкапов
  9. Проверяем наличие свежесозданных файликов *.ibd

Все!

Скрипт для оптимизации файлов 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, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.

PQ VPS сервера в 28+ странах.
Dolphin anty. Инглекс (Englex) — онлайн школа английского языка.