Проводя опрос среди посетителей раздела Job Opening я задавал им
один простой вопрос: если бы у вас был сервер с 16GB RAM, который был
бы предназначен для MySQL-сервера с очень большим объемом
innodb-таблиц, работающий с стандартным веб-проектом, какие бы
настройки вы скорректировали? И самое интересное, что большинство не
смогло четко ответить. Поэтому и было принято решение опубликовать эту
заметку, которая, возможно, расширит ваши знания об оптимизации
программной и аппаратной частей сервера.
Я назвал эту статью Основы оптимизации производительности InnoDB,
желая подчеркнуть, что это именно основы. Это универсальные советы,
работающие на большинстве систем. Но более точную настройку необходимо
производить, исходя из конкретных поставленных задач.
Hardware
Если у вас есть база данных innodb большого объема, и это
действительно важные данные, то 16 - 32 GB оперативной памяти будут
оптимальным решением. Процессоры - 2*DualCore подойдут для большой
нагрузки, а два Quad Core помогут решить проблемы с дальнейшим
масштабированием системы. Хотя имеется множество нюансов. Третий
момент - это подсистема ввода/вывода. Напрямую подключенный
DataStorage с большим количеством дисков и RAID с возможностью
сохранения кэша будут отличным выбором. Обычно необходимо 6 - 8
жестких дисков в стандартный блок, но порой может понадобиться и
больше. Также обратите внимание на новые 2.5" SAS диски. Они меньше,
но часто работают гораздо быстрее, чем обычные HDD. RAID10 хорошо
подходит как для хранения, так и для чтения данных, но в случае, если
вы можете позволить некоторую избытычность. В противном случае можно
сделать RAID5, но опасайтесь случайных записей.
Операционная система
Дя начала: установите 64-битную операционную систему. Часто можно
увидеть 32-битный linux, или запущенный в режиме совместимости с
64-bit. Не делайте так. Если вы используете LVM для хранения базы
данных, вы сможете более эффективно работать с резервными копиями.
Файловая система Ext3 будет оптимальным выбором в большинстве случаев,
но если вы запускаете particular roadblocks, то попробуйте XFS. Вы
можете использовать опции noatime и nodiratime, если вы используете
innodb_file_per_table и большое количество таблиц, но это, в принципе,
не столь важно. Также убедитесь, что OS резервирует достаточно большое
количество памяти для MySQL.
Опции MySQL InnoDB
Важнейшими опциями являются:
innodb_buffer_pool_size - 70 - 80% оперативной памяти. Я ставлю это
значение в 12G на системе с 16G RAM
innodb_log_file_size - зависит от необходимого вам объема данных для
восстановления, но 256МБ будут разумным компромиссом между
производительностью и рамером лог-файла
innodb_log_buffer_size=4M - 4 мегабайта - нормальное значение, если вы
не используете подачу больших блоков данных в InnoDB через каналы
(pipes). Если используете, это значение лучше увеличить.
innodb_flush_logs_at_trx_commit=2 - если вас не особо заботит ACID, и
вы можете себе позволить потерять транзакции за последние секунду или
две, в случае полного краха ОС, то установите это значение. Но это
может повлечь печальные эффекты при коротких записях транзакций.
innodb_thread_concurrency=8 - даже при имеющихся InnoDB Scalability
Fixes будет совсем не лишним иметь ограниченное количество потоков.
Значение может быть больше или меньше в зависомости от ваших
потребностей, но 8 будет оптимальным значением для начала.
innodb_flush_method=O_DIRECT - избегайте двойной буферизации и
уменьшите активность swap, в большинстве случаев это увеличивает
производительность. Но будьте осторожны, если у вас нет RAID с
возможностью сохранения данных, операции ввода-вывода могут проходить
некорректно и данные могут быть повреждены.
innodb_file_per_table - если у вас немного таблиц, используйте эту
опцию и рост занимаемого таблицами места не будет бесконтрольным. Эта
опция добавлена в MySQL 4.1 и сейчас достаточно стабильна для
использования.
Проверьте также, могут ли ваши приложения запускаться в режиме
изоляции READ-COMMITED. Если это так, то установите опцию
transaction-isolation=READ-COMITTED. Этот вариант увеличит
производительность.
Есть еще немало опций, значения которых можно поменять для достижения
лучшей производительности. Об этом можно прочитать в заметке
Настройка опций mysql-server (перевод) или в одной из наших
презентаций.
Настройка приложений для работы с InnoDB
При переходе с типа MyISAM, вам конечно будет интересно, что
изменилось и какие новые возможности вам теперь доступны. Во-первых:
убедитесь, что вы используете транзакции при обновлениях. Это
необходимо для повышения производительности. Во-вторых: готово ли ваше
приложение обрабатывать проблемы, которые могут произойти? И
в-третьих: возможно вы захотите пересмотреть структуру своих таблиц и
посмотреть как вы можете использовать свойства InooDB: распределение
по первичному ключу, использование первичного ключа на всех индексах
(это позволяет сократить первичеый ключ), быстрый просмотр по
первичным ключам (попробуйте использовать это при запросах с JOIN) или
большие несжаты индексы (облегчают индексирование).
При помощи этого краткого описания вы сможете провести первичную
настройку InnoDB, которая повысит производительность на системах без
battery backup, без изменения настроек ОС и не внося изменения в
настройки приложений, до сих пор использующих MyISAM
Vova
