Обзор InnoDB

Автор: Александр Степанов

2011.11.01  814


Обзор InnoDB

InnoDB — один из почти десятка доступных движков для MySQL, и вот его основные достоинства:

  • Скорость:
    • построчные блокировки (а не целых таблиц как в InnoDB)
    • эффективное использование CPU, памяти и i/o
    • эффективные индексы
  • Стабильность и целостность:
    • автоматическое восстановление после сбоев
    • транзакции и ссылочная целостность
    • возможен онлайн бекап с помощью InnoDB Hot Backup
    • хороший, протестированный код
  • Проверенность:
    • распространяется в составе MySQL с 2001 года
    • широко используется в различных крупных проектах

InnoDB в MySQL 5.1

В свежем релизе MySQL InnoDB:

  • полностью поддерживает все новые функции (partitioning, построковая репликация)
  • исправлено множество багов из 5.0
  • существенное улучшение производительности при использовании AUTO_INCREMENT

InnoDB AUTO_INCREMENT в 5.1

До 5.1.22 InnoDB делал блокировку на AUTOINC счетчике до конца выполнения SQL запроса. Это нормально для коротких запросов, но разумеется плохо для долго выполняющихся. Такое поведение было связано с требованиями репликации. Начиная с 5.1.22 в MySQL используется более легкие блокировки. Введен новый параметр: innodb_autoinc_lock_mode, который может принимать следующие значения:
0 — «Traditional/Традиционный» — для обратной совместимости
1 — «Consecutive/Последовательный» — по умолчанию в 5.1
2 — «Interleaved/Перемежающийся» — быстрее чем последовательный, но работает только с построчной репликацией Благодаря этим изменениям теперь производительность AUTO_INCREMENT изменяется практически линейно при увеличении количества одновременных вставок.


InnoDB Plugin

Обзор

Одно из существенных изменений в MySQL 5.1 — введение плагинов. Теперь движок может поставляться в виде отдельной, независимой библиотеки, установка которой не требует перекомпиляции всего сервера. В данный момент на сайте www.innodb.com доступна тестовая версия InnoDB Plugin для MySQL 5.1. Он обладает бОльшим количеством функций по сравнению с InnoDB встроенным в MySQL:

  • более быстрое создание и удаление индексов
  • сжатие данных
  • новый формат строк: хранение длинных BLOB, TEXT и VARCHAR вне страницы
  • таблицы со служебной информацией и статистикой в INFORMATION_SCHEMA
  • изменения для удобства:
    • возможность динамического изменения innodb_file_per_table
    • TRUNCATE TABLE теперь пересоздает .ibd файлы, очищая место
    • «режим соответствия»

InnoDB Plugin распространяется как в виде бинарников для разных платформ, так и в виде исходников под лицензией GPLv2 (также как и MySQL). InnoDB Plugin поддерживает существующие базы и может быть использован временно (можно откатиться до встроенного InnoDB, но если не использовался новый формат базы).


Установка

  1. Скачайте InnoDB в бинарном виде, либо в виде исходников и соберите его.
  2. Скопируйте ha_innodb.so в директорию с библиотеками MySQL (обычно префикс установки/lib)
  3. Остановите MySQL
  4. Добавите в my.cnf следующее: skip_innodb // отключаем встроеный InnoDB   innodb_file_per_table // храним каждую таблицу в отдельном файле (не обязательно)   innodb_file_format=Barracuda // включаем новый формат файлов данных (не обязательно)
  5. Запустите MySQL.
  6. Залогинтель в MySQL в под аккаунтом суперпользователя: mysql -u root -p
  7. установите плагин: INSTALL PLUGIN INNODB SONAME 'ha_innodb.so'
  8. Если вам нужна статистика установите еще плагинов: INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb.so' и др.
  9. Проверьте что все загрузилось правильно: SHOW PLUGINS

Если что-то не работает — проверьте логи.


Быстрое создание индексов

Во встроенном InnoDB при создании/удалении нового вторичного (не primary) индекса таблица пересоздавалась заново, а InnoDB Plugin создает только индекс, и не трогая саму таблицу. Разумеется это гораздо быстрее. Добавление primary key индекса все еще требует пересоздания всей таблицы.


Сжатие таблиц

InnoDB Plugin может создавать и использовать таблицы со сжатыми блоками. При этом сжимаются и страницы с данными и с индексами. Для работы сжатых таблиц требуется innodb_file_per_table=1 и innodb_file_format=Barracuda. Как известно, данные в innodb таблицах обычно содержатся в страницах размером 16 Кб. При использовании сжатия InnoDB пытается сжать (с помощью zlib) 16-килобайтные страницы в страницы меньшего размера (обычно 8 или 4 килобайта). Для среднестатистического набора данных страницы сжимаются более чем на 50%, т.е. обычная 16 кб страница влезает в 8 кб сжатую. InnoDB пытается минимизировать количество компрессий/декомрессий при изменениях страниц. для этого InnoDB хранит лог изменений каждой страницы и пересжимает страницу (расжимает сжатую страницу, применяет изменения из лога и сжимает ее заново) только когда заканчивается место для лога. Если страница не сжимается, InnoDB разбивает ее и пытается снова. На это тратится существенно больше времени, чем если она сжимается с первого раза. Сжатые страницы кешируются MySQL в buffer pool (точно также как и обычные). Если страница используется часто, то в buffer pool хранится еще и ее расжатая копия. В зависимости от вида нагрузки, соотношение сжатых и несжатых страниц в buffer pool может быть разным: при активном i/o до 90% в buffer pool занимают сжатые страницы, при активном использовании CPU — наоборот. Также в зависимости от нагрузки InnoDB пытается подобрать оптимальную степень сжатия. В будущем планируется позволить пользователю указывать степень сжатия самостоятельно. Компрессию следует использовать при активном i/o: сжатие означает большее количество страниц в buffer pool, следовательно процент чтений из buffer pool, а не с диска будет выше, следовательно сканирование таблиц будет быстрее и дисковых i/o операций будет меньше. Компрессию не следует использовать:

  • при высокой нагрузке на CPU: сжатие и декомпрессия будет требовать еще больше CPU
  • на небольших и часто используемых таблицах, которые и так целиком помещаются в buffer pool
  • если ваши данные плохо сжимаются (можно проверить сжатием .ibd файла: если он сжимается с помощью gzip менее чем на 50%, сжатие лучше не использовать).

Мониторинг сжатия: Используйте таблицу INFORMATION_SCHEMA.INNODB_CMP. Best practice: менее 1% неудачных сжатий.


Форматы файлов

В связи с введением новых функций, InnoDB Plugin поддерживает несколько форматов файлов. Формат меняется с помощью параметра innodb_file_format Старый формат: Antelope, используется в обычном InnoDB Новый формат: Barracuda, поддерживается InnoDB Plugin — возможна компрессия, ROW_FORMAT=DYNAMIC. Встроенный InnoDB может работать только с Antelope файлами.


INFORMATION_SCHEMA

Виртуальная база данных INFORMATION_SCHEMA содержит много служебной информации и статистики. Пример:  USE INFORMATION_SCHEMA SELECT * FROM INNODB_TRX; Подробнее о таблицах читайте в документации.


Другие улучшения

  • TRUNCATE TABLE пересоздает .ibd файл, тем самым сразу освобождая место (ранее для этого требовалось сделать DROP и CREATE TABLE).
  • новый параметр innodb_strict_mode=on включает режим соотвествия стандартам (strict mode). В этом режиме все предупреждения станут ошибками. Strict mode скоро станет включеной по умолчанию, поэтому рекомендуется проверить ваш код заранее!


Ссылки