Обзор 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, но если не использовался новый формат базы).

Установка

Скачайте InnoDB в бинарном виде, либо в виде исходников и соберите его.
Скопируйте ha_innodb.so в директорию с библиотеками MySQL (обычно префикс установки/lib)
Остановите MySQL
Добавите в my.cnf следующее: skip_innodb // отключаем встроеный InnoDB innodb_file_per_table // храним каждую таблицу в отдельном файле (не обязательно) innodb_file_format=Barracuda // включаем новый формат файлов данных (не обязательно)
Запустите MySQL.
Залогинтель в MySQL в под аккаунтом суперпользователя: mysql -u root -p
установите плагин: INSTALL PLUGIN INNODB SONAME 'ha_innodb.so'
Если вам нужна статистика установите еще плагинов: INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb.so' и др.
Проверьте что все загрузилось правильно: 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 скоро станет включеной по умолчанию, поэтому рекомендуется проверить ваш код заранее!

Обзор InnoDB

MySQL

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