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

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

Разделение файла ibdata1 в MySQL с использованием InnoDB на несколько файлов может привести к улучшению производительности и упрощению управления данными. Вот несколько ключевых преимуществ разделения ibdata1 на несколько файлов:

  1. Уменьшение фрагментации: Все данные и индексы InnoDB по умолчанию хранятся в одном файле ibdata1. Это может привести к фрагментации, особенно в системах с большим объемом операций вставки, обновления и удаления данных. Использование нескольких файлов может помочь уменьшить фрагментацию, так как данные могут быть более равномерно распределены по файлам.
  2. Параллельный ввод/вывод (I/O): Разделение на несколько файлов может позволить выполнять операции ввода/вывода параллельно, особенно если файлы расположены на разных физических носителях или разных подсистемах хранения. Это может значительно улучшить производительность для операций, требующих интенсивного чтения и записи данных.
  3. Лучшее управление пространством: Когда все данные хранятся в одном файле ibdata1, этот файл может очень быстро расти, особенно в больших системах. Разделение файла на несколько частей позволяет более гибко управлять дисковым пространством, например, добавлять новые файлы на другие диски, когда это необходимо.
  4. Упрощение резервного копирования и восстановления: Работа с меньшими файлами может упростить и ускорить процессы резервного копирования и восстановления, так как можно выборочно копировать и восстанавливать данные.
  5. Улучшение производительности в многопользовательских средах: В системах с высокой конкурентной нагрузкой разделение ibdata1 на несколько файлов может уменьшить контенцию за ресурсы ввода/вывода, что повысит производительность приложения.

Обычно первая команда, которую нам нужно запустить при возникновении проблемы с MySQL: 

MariaDB [(NONE)]> SHOW ENGINE INNODB STATUS;

Как включить MySQL innodb_file_per_table?

InnoDB Startup Configuration: для разделения ibdata1 на несколько файлов, в конфигурации InnoDB можно использовать параметры innodb_file_per_table или innodb_data_file_path. Включение innodb_file_per_table заставляет InnoDB создавать отдельный файл для каждой таблицы, что также помогает в управлении пространством и производительности.

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

  • В этом примере файл данных ibdata1 в параметре innodb_data_file_path начинается с размера 10 мегабайт и будет автоматически увеличиваться по мере необходимости.
  • Статус параметра innodb_file_per_table в MySQL может быть либо ON (включен), либо OFF (выключен). Когда он установлен в ON, это означает, что InnoDB будет хранить данные каждой новой таблицы в отдельном файле .ibd. Если он установлен в OFF, InnoDB будет хранить данные всех таблиц в общем файле табличного пространства (ibdata1). innodb_file_per_table = ON: Этот режим рекомендуется для большинства приложений, так как он обеспечивает лучшее управление дисковым пространством и может улучшить производительность, особенно при частых операциях создания и удаления таблиц. Каждая таблица имеет свой собственный файл, что упрощает управление размером базы данных и уменьшает фрагментацию. С версии MySQL 5.6.6 и выше, параметр innodb_file_per_table по умолчанию установлен в ON. Однако если вы работаете с более старыми версиями MySQL или если параметр был изменен, его значение может быть отличным. Чтобы проверить текущее значение этого параметра, можно использовать следующий SQL-запрос в MySQL: 
    mysql -e "SHOW VARIABLES LIKE '%'\G;" | grep innodb_file_per_table  -B 1 -A 1
Variable_name: innodb_file_per_table
        VALUE: ON

Что нужно сделать:

  1. Выключаем всех клиентов MySQL, не должно быть никаких обращений.
  2. Делаем ПОЛНЫЙ бекап всех баз данных и конфигурационных файлов 
    mysqldump --all-databases -p > mysqldump-all.sql
  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? 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, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.

Заключение

Когда файл ibdata1 растет в MySQL слишком быстро, это обычно вызвано длительной транзакцией, о которой мы забыли. Постарайтесь решить проблему как можно быстрее (зафиксировав или завершив транзакцию), поскольку вы не сможете восстановить потраченное впустую дисковое пространство без мучительно медленного процесса mysqldump.

Также рекомендуется осуществлять мониторинг базы данных, чтобы избежать подобных проблем со слишком старой выполняющейся транзакцией.

PQ VPS сервера в 28+ странах.