войти зарегистрироваться

MySQL whois

индекс
202,61

InnoDB Storage Engine в Mysql 5.1

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

Ссылки
+36
22 января 2009, 21:59
B_dot

комментарии (26)

  • В самом начале:

    «Скорость:
    — построчные (а не целых таблиц как в MyISAM) „

    Наверное речь идет о блокировках. Слово пропало
    • спасибо, fixed
      • повторяется «TRUNCATE TABLE теперь пересоздает .ibd файлы, очищая место» вначале обзора и в Другие улучшения…
        а в целом очень хорошая статья! :)
  • К сожалению, русские хостеры не очень-то и ставят InnoDB к себе на сервера. Потому остаётся только MyISAM и использовать.
    • ну, на shared оно обычно и не нужно
      • Но и не помешало бы.
  • Про минусы забыли. Особенно для использования в веб-проектах.
    Попробуйте какой нибудь из распространенных форумов (Invision, phpBB и т.д.) перевести с MyISAM на InnoDB, при средней нагрузке, будет очень много танцев с бубном.

    Они забыли написать что транзакционность убивает производительность в некоторых видах задач. (которых в вебе как раз болше всего — важна скорость выборки а не скорость записи)
    • Для каждого конкретного случая нужно хорошо думать и выбирать тип таблицы. Кроме этого, MySQL позволяет использовать разные типы таблиц в рамках одной БД. Так что не обязательно все MyISAM или InnoDB делать.
    • ian
    • ian
    • 23 января 2009, 02:06
    • #
      • –5
    раскрыть комментарий
    • Разница есть, поскольку поддержать ссылочную целостность на внешних ключах проще (и, наверно, правильнее), чем на триггерах. Делать это в коде — совсем уж неблагодарное дело.
      Ну, и транзакции, опять же…
      • поскольку поддержать ссылочную целостность на внешних ключах проще (и, наверно, правильнее), чем на триггерах.

        Не только проще и правильнее, это единственный способ. Целостность на триггерах это грабли, поскольку триггеры работают в контексте транзакции, а ограничения целостности — нет.
  • Самое вкусное это транзакции для меня) Жаль, что многие хостинги не поддерживают innodb
    • Транзакции это не только вкусное мясо и густая шерсть, но и способ заставить работать инсерты в десятки раз быстрее
      ПС: сказал специально для тех кто думал что транзакции созданы чтобы их откатывать( а таких как не странно много)
  • Хранимые процедуры, тарнзакции — это вкусно =)
    • Хранимые процедуры и триггеры работают и для MyISAM.
  • Эсли бы сделали таки fulltext–поиск, цены бы ему не было.
  • Это последствия вчерашней встречи? :)
    • bat
    • bat
    • 23 января 2009, 11:38
    • #
      • 0
    Во встроенном InnoDB при создании/удалении нового вторичного (не primary) индекса таблица пересоздавалась заново

    Это действительно так? :-o
    Где можно почитать по подробнее?
  • Какой движок лучше подойдет для mysql-таблицы, если в одну и ту же таблицу параллельно идут как инсерты так и селекты?
    • couchdb тебе в помощь :)
      • couchdb _очень_ нетороплив на вставках
    • Лучше всего пойдет две таблицы :) Одна на запись, вторая на чтение и синк между ними раз в какое-то время. А для одной таблицы innodb будет лучше из-за построчных блокировок.
      • Сейчас как раз так и реализовано — 1я таблица на запись, и 2я(для выборки) дублирует ее через промежутки времени.

        Про блокировки я в курсе, но _говорят_ выборка из инноДБ медленнее чем майИСАМ например. Надо погонять тесты на эту тему…
  • «автоматическое восстановление после сбоев» — вчера, на встрече, Осипов(mysql dev team) говорил что этого и не надо, т.к. там само по себе все предусмотрено. Надо заметить, что и сбои бывают разными.
    • Имелось в виду что после неожиданного краха процесса в большинстве случаев все восстановится само и не надо будет гонять CHECK TABLE и REPAIR TABLE.
      • Кем имелось ввиду? =) Осиповым или автором топика?
Только авторизованные пользователи могут оставлять комментарии. Авторизуйтесь, пожалуйста.