Данная статья фактически является вольным переводом презентации с MySQL User Conference, проходившей в апреле 2008. Оригинал размещен на сайте GreenMice.

Обзор InnoDB

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

• Скорость:
  
  • построчные блокировки (а не целых таблиц как в MyISAM)
  • эффективное использование 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 содержит много служебной информации и статистики. Пример:

mysql -u root -p

;

USE INFORMATION_SCHEMA SELECT * FROM INNODB_TRX; 

Подробнее о таблицах читайте в документации.

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

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

Ссылки

• www.innodb.com
• forums.innodb.com