Попытаюсь в этой статье рассказать об особенностях применения хранилища InnoDB в высоконагруженных проектах, а так же дать поверхностное сравнение MyISAM и InnoDB. Безусловно, MySQL не ограничивается только этими двумя типами хранилища данных, однако они являются подавляющими в своей распространенности использования.
Несмотря на то, что много в InnoDB для меня очевидно, все еще остаются некоторые темные пятна и если меня где то поправят, буду только благодарен.


Почему народ выбирает InnoDB? InnoDB обладает преимуществами перед MyISAM.
  1. Транзакционная модель. Это конечно преимущество не столько для администратора, сколько для программиста. Программист может объединить операции с базой в транзакцию, с кучей вытекающих из этого профита. Это основная причина по которой архитекторы выбирают InnoDB.
  2. Блокировка на уровне строки. В отличии от MyISAM, где идет блокировка на уровне таблицы, в InnoDB блокировка осуществляется на уровне строки. Проблема конкурентных блокировок стоит не так остро как в MyISAM, однако все таки присутствует. Но об этом ниже.
  3. Защита от сбоев. InnoDB более устойчивая к сбоям, если сказать точнее, InnoDB намного лучше восстанавливается после сбоев и практически не теряет данные. Для восстановления же MyISAM таблиц зачастую требуется потушить MySQL сервер и вручную восстанавливать таблицы утилитой myisamchk. Результатом работы myisamchk зачастую может оказаться частичная или полная потеря данных в таблице. InnoDB восстанавливается автоматически.
  4. Качественная работа с IO. InnoDB имеет свой собственный Buffer Pool в памяти, где держит таблицы. Для InnoDB можно отключить системную буферизацию IO при работе с таблицами InnoDB. Таким образом, можно сказать что в InnoDB нет двойной буферизации (как в MyISAM), следовательно, оперативная память разумно расходуется.
MyISAM конечно же тоже обладает преимуществами, в основном это простота и скорость. На небольших объемах данных и большом количестве операций чтения лучше хранилища не найти, если конечно вам не нужны транзакции. Но сейчас не об этом. На этой ноте про MyISAM больше ни слова.
InnoDB не готова корректно работать из коробки на высоких нагрузках. Надо хорошо понимать о происходящих в недрах InnoDB процессах дабы правильно настроить этот тип хранилища.
Ниже описаны ключевые моменты конфигурации, существенно влияющие на производительность.
innodb_file_per_table
По умолчанию, InnoDB использует общее хранилище для всех таблиц и индексов. Данная опция позволяет содавать на каждую таблицу свой .ibd файл. Наиболее частая причина применения этой опции – раскидать отдельные таблицы по отдельным физическим устройствам.
Так же бывают определенные таблицы, в которые очень часто пишутся и удаляются данные. Это серъезно фрагментирует общее хранилище таблиц и от этого может пострадать производительность других таблиц. В этом случае имеет смысл разбивать общее хранилище на отдельные куски для каждой таблицы.
Если у вас таблицы уже созданы в общем хранилище и вы перезагружаете сервер MySQL с этой опцией, старые таблицы останутся в общем хранилище, а новые будут создаваться в отдельных хранилищах.  Таким образом, чтобы поместить старые таблицы в раздельные файлы, нужно их пересоздать или переименовать.
Использование выделенных блочных устройств под хранилище
Киллер-фича InnoDB. Вы можете использовать целые партиции или физические устройства вместо файлов общего хранилища InnoDB. Это сразу убирает всякую системную буферизацию ввода-вывода и всякий оверхед файловой системы. В этом случае InnoDB пишет данные прямо на устройство. Конечно же, это создает ряд ньюансов в процедуре резервного копирования.
Для того чтобы использовать эту возможность, пропишите в конфигурации

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

После старта InnoDB сделает инициализацию блочных устройств. Очень важно после этого остановить сервер и в конфигурации поменять «newraw» на «raw»:
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

и перезапустить сервер. Иначе при следующем перезапуске, если InnoDB встретит «newraw», партиция будет заново отформатирована! Так же надо иметь в виду, что пользователь, под которым запускается MySQL должен иметь права на запись в обозначенные партиции.
При использовании данной возможности, очевидно лучше для InnoDB выделять логические тома LVM. Это существенно упрощает бекап (по снятому снапшоту) и восстановление.
innodb_buffer_pool_size
Размер памяти, выделяемый под кеш данных и индексов. Строго говоря, чем больше таблиц сидит в этой памяти, тем лучше. Если есть возможность, размер этого буфера должен быть чуть больше общего размера innodb таблиц. Однако он не должен быть больше 80% объема ОЗУ.
innodb_log_file_size
Размер файла лога транзакций. Чем больше размер, тем реже InnoDB будет сбрасывать страницы Buffer Pool на диск, и тем больше требуется времени на восстановление после аварии. Размер варьируется от нескольких мегабайт до размера innodb_buffer_pool_size, но не более 4Gb суммарно во всех лог-файлах.
innodb_log_buffer_size
Размер буфера памяти для записи лога транзакций. Размер варьируется в пределах единиц-десятков мегабайт. Большой размер буфера позволяет запускать объемные транзакции без сброса лога на диск, что позволяет уменьшить IO при объемных транзакциях.
innodb_flush_log_at_trx_commit
Принимает одно из трех значений: 0, 1, 2. При значении 1, лог скидывается на диск при каждом коммите транзакции и буфер записи так же скидывается на диск. При 0 эта операция производится не при каждой транзакции а 1 раз в секунду. При значении 2, лог скидывается на диск при каждом коммите, но сброс буферов не производится.
Если вы ищете производительность в ущерб надежности – ставьте 0. Если наоборот – ставьте 1.
innodb_thread_concurrency
Количество рабочих тредов InnoDB. Начать надо с количества ядер CPU*2 + количество физических блочных устройств. Мне всегда этой формулы хватало. Официальная документация рекомендует поиграть с этим значением.
innodb_flush_method
Установка характера работы с файловой системой. Данная переменная не имеет эффекта при использовании выделенных блочных устройств под хранилище. Представляет из себя комбинацию значений O_DSYNC,O_DIRECT,fdatasync. Если вы используете большой размер innodb_buffer_pool_size, имеет смысл дать InnoDB доступ к файлам, минуя системные буфера с помощью опции O_DIRECT.
В официальном руководстве сказано, что при использовании определенных Storage Area Network, O_DIRECT может дать серъезный пенальти производительности.
Для OS GNU/Linux читайте про опции O_DSYNC (O_SYNC) и O_DIRECT на данной странице руководства open(2). FreeBSD очевидно имеет много сходств с GNU/Linux в этом вопросе.
Для OS MS Windows © ® ™ данная опция не имеет смысла, как и данная статья вообще.
innodb_locks_unsafe_for_binlog
Эта опция не может быть никак пропущена, если у вас серьезная нагрузка на конкурентную запись. Очень сложно это объяснить, да и не понимаю я этого до самой глубины, но если вкратце…
Если вам нужна полноценная изоляция транцакций, то эта опция не для вас. Тогда придется пренебрегать производительностью в пользу целостности транзакций.
Например, если вы используете чтение по диапазону (SELECT a FROM b WHERE c>100) внутри тразакции, то с включенной опцией innodb_locks_unsafe_for_binlog, следующий такой же запрос вернет тот же результат, даже если между ними кто то что то в эту таблицу пытался писать.
При выключенной (по умолчанию) опции innodb_locks_unsafe_for_binlog, во второй раз указанный запрос может вернуть отличный от первого раза результат в одной транзакции, поскольку пытающимся писать в эту таблицу процессам не было выставлено препятствий.
То есть, эта опция во включенном состоянии снимает туеву хучу локов при конкурентной записи-чтении в таблицы. Цена вопроса – не обеспечивается консистентный снапшот данных на время всей транзакции. Как то так в общем.
В моем случае, прирост производительности при включении этой опции был колоссальный. Однако это имеет смысл на реально массивных смешанных записях-удалениях-чтениях.
Ах да. И для репликации соответственно это не канает.
innodb_lock_wait_timeout
Довольно странная настройка, но она имеет место и является частой причиной потери данных. Когда тред ожидает снятия блокировки строки для модификации записей, он ожидает вплоть до указанного в этой опции количества секунд. По умолчанию это 50 секунд. Если за 50 секунд блокировка не была снята, транзакция отваливается с ошибкой

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Если у вас нагруженный модификациями сервер, вы наверняка упретесь в это время ожидания и транзакции с изменениями данных будут завершаться с указанной ошибкой. В этом случае стоит подумать о включении опции innodb_locks_unsafe_for_binlog. Программистов нужно предупреждать (если они не в курсе), что если они словили ошибку 1205 от InnoDB, то надо повторить или отложить транзакцию! Поскольку эта ошибка де-факто не может быть воспроизведена в тестовых условиях, очень часто программисты не в курсе и бывают весьма удивлены, наблюдая пробелы в потоках данных.