Попытаюсь в этой статье рассказать об особенностях применения хранилища InnoDB в высоконагруженных проектах, а так же дать поверхностное сравнение MyISAM и InnoDB. Безусловно, MySQL не ограничивается только этими двумя типами хранилища данных, однако они являются подавляющими в своей распространенности использования.
Несмотря на то, что много в InnoDB для меня очевидно, все еще остаются некоторые темные пятна и если меня где то поправят, буду только благодарен.
Почему народ выбирает InnoDB? InnoDB обладает преимуществами перед MyISAM.
InnoDB не готова корректно работать из коробки на высоких нагрузках. Надо хорошо понимать о происходящих в недрах InnoDB процессах дабы правильно настроить этот тип хранилища.
Ниже описаны ключевые моменты конфигурации, существенно влияющие на производительность.
innodb_file_per_table
По умолчанию, InnoDB использует общее хранилище для всех таблиц и индексов. Данная опция позволяет содавать на каждую таблицу свой .ibd файл. Наиболее частая причина применения этой опции — раскидать отдельные таблицы по отдельным физическим устройствам.
Так же бывают определенные таблицы, в которые очень часто пишутся и удаляются данные. Это серъезно фрагментирует общее хранилище таблиц и от этого может пострадать производительность других таблиц. В этом случае имеет смысл разбивать общее хранилище на отдельные куски для каждой таблицы.
Если у вас таблицы уже созданы в общем хранилище и вы перезагружаете сервер MySQL с этой опцией, старые таблицы останутся в общем хранилище, а новые будут создаваться в отдельных хранилищах. Таким образом, чтобы поместить старые таблицы в раздельные файлы, нужно их пересоздать или переименовать.
Использование выделенных блочных устройств под хранилище
Киллер-фича InnoDB. Вы можете использовать целые партиции или физические устройства вместо файлов общего хранилища InnoDB. Это сразу убирает всякую системную буферизацию ввода-вывода и всякий оверхед файловой системы. В этом случае InnoDB пишет данные прямо на устройство. Конечно же, это создает ряд ньюансов в процедуре резервного копирования.
Для того чтобы использовать эту возможность, пропишите в конфигурации
После старта InnoDB сделает инициализацию блочных устройств. Очень важно после этого остановить сервер и в конфигурации поменять «newraw» на «raw»:
и перезапустить сервер. Иначе при следующем перезапуске, если 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 секунд блокировка не была снята, транзакция отваливается с ошибкой
Если у вас нагруженный модификациями сервер, вы наверняка упретесь в это время ожидания и транзакции с изменениями данных будут завершаться с указанной ошибкой. В этом случае стоит подумать о включении опции innodb_locks_unsafe_for_binlog. Программистов нужно предупреждать (если они не в курсе), что если они словили ошибку 1205 от InnoDB, то надо повторить или отложить транзакцию! Поскольку эта ошибка де-факто не может быть воспроизведена в тестовых условиях, очень часто программисты не в курсе и бывают весьма удивлены, наблюдая пробелы в потоках данных.
Несмотря на то, что много в InnoDB для меня очевидно, все еще остаются некоторые темные пятна и если меня где то поправят, буду только благодарен.
Почему народ выбирает InnoDB? InnoDB обладает преимуществами перед MyISAM.
- Транзакционная модель. Это конечно преимущество не столько для администратора, сколько для программиста. Программист может объединить операции с базой в транзакцию, с кучей вытекающих из этого профита. Это основная причина по которой архитекторы выбирают InnoDB.
- Блокировка на уровне строки. В отличии от MyISAM, где идет блокировка на уровне таблицы, в InnoDB блокировка осуществляется на уровне строки. Проблема конкурентных блокировок стоит не так остро как в MyISAM, однако все таки присутствует. Но об этом ниже.
- Защита от сбоев. InnoDB более устойчивая к сбоям, если сказать точнее, InnoDB намного лучше восстанавливается после сбоев и практически не теряет данные. Для восстановления же MyISAM таблиц зачастую требуется потушить MySQL сервер и вручную восстанавливать таблицы утилитой myisamchk. Результатом работы myisamchk зачастую может оказаться частичная или полная потеря данных в таблице. InnoDB восстанавливается автоматически.
- Качественная работа с IO. InnoDB имеет свой собственный Buffer Pool в памяти, где держит таблицы. Для InnoDB можно отключить системную буферизацию IO при работе с таблицами InnoDB. Таким образом, можно сказать что в InnoDB нет двойной буферизации (как в 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, то надо повторить или отложить транзакцию! Поскольку эта ошибка де-факто не может быть воспроизведена в тестовых условиях, очень часто программисты не в курсе и бывают весьма удивлены, наблюдая пробелы в потоках данных.
© Pentarh Udi
