В этой статье будут описаны различные настройки MySQL, преимущественно те, которые влияют на производительность. Для удобства все переменные разделены по разделам (базовые настройки, ограничения, настройки потоки, кэширование запросов, тайминги, буферы, InnoDB). Сначала уточним имена некоторых переменных, которые изменились в версии 4 MySQL, а в сети продолжают встречаться и старые и новые варианты имен, что вызывает вопросы. тесты, это значение не следует особо увеличивать. Размер по умолчанию 128 КБ, попробуйте увеличить его до 256 КБ, а затем до 512 КБ и понаблюдайте за скоростью выполнения запросов типа SELECT COUNT(*) FROM table WHERE expr LIKE "a%"; на больших таблицах. read_rnd_buffer_size — актуально для запросов с "ORDER BY", т.е. для запросов, результат которых должен быть отсортирован и которые обращаются к таблице, имеющей индексы. Значение по умолчанию 256 КБ, увеличьте его до 1 МБ или выше, если позволяет память. Учтите, что указанное значение памяти также выделяется на каждый поток. sort_buffer_size — каждый поток, производящий операции сортировки (ORDER BY) или группировки (GROUP BY), выделяет буфер указанного размера. Значение по умолчанию 2 МБ, если вы используете указанные типы запросов и если позволяет память, то значение стоит увеличить. Большое значение переменной состояния Sort_merge_passes указывает на необходимость увеличения sort_buffer_size. Также стоит проверить скорость выполнения запросов вида SELECT * FROM table ORDER BY name DESC на больших таблицах, возможно увеличение буфера лишь замедлит работу (в некоторых тестах это так). table_cache (table_open_cache с версии 5.1.3) — количество кэшированных открытых таблиц для всех потоков. Открытие файла таблицы может быть достаточно ресурсоемкой операцией, поэтому лучше держать открытые таблицы в кэше. Следует учесть, что каждая запись в этом кэше использует системный дескриптор, поэтому возможно придется увеличивать ограничения на количество дескрипторов (ulimit). Значение по умолчанию 64, его лучше всего увеличить до общего количества таблиц, если их количество в допустимых рамках. Переменная состояния Opened_tables позволяет отслеживать число таблиц, открытых в обход кэша, желательно, чтобы ее значение было как можно ниже. tmp_table_size — максимальный размер памяти, выделяемой для временных таблиц, создаваемых MySQL для своих внутренних нужд. Это значение также ограничивается переменной max_heap_table_size, поэтому в итоге будет выбрано минимальное значение из max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по умолчанию зависит от системы, попробуйте установить его равным 32 МБ и понаблюдать за переменной состояния Created_tmp_disk_tables, ее значение должно быть как можно меньше. Значения в конфигурационном файле задаются в байтах, соответственно килобайты и мегабайты нужно переводить в байты. InnoDB innodb_additional_mem_pool_size — размер памяти, выделяемый InnoDB для хранения различных внутренних структур. Если InnoDB будет недостаточно этой памяти, то будет запрошена память у ОС и записано предупреждение в лог ошибок MySQL. innodb_buffer_pool_size — размер памяти, выделяемый InnoDB для хранения и индексов и данных. Значение — чем больше, тем лучше. Можно увеличивать вплоть до общего размера всех InnoDB таблиц или до 80% ОЗУ, в зависимости от того, что меньше. innodb_flush_log_at_trx_commit — имеет три допустимых значения: 0, 1, 2. При значении равном 0, лог сбрасывается на диск один раз в секунду, вне зависимости от происходящих транзакций. При значении равном 1, лог сбрасывается на диск при каждой транзакции. При значении равном 2, лог пишется при каждой транзакции, но не сбрасывается на диск никогда, оставляя это на совести ОС. По умолчанию используется 1, что является самой надежной настройкой, но не самой быстрой. В общем случае вы можете смело использовать 2, данные могут быть утеряны лишь в случае краха ОС и лишь за несколько секунд (зависит от настроек ОС). 0 — самый быстрый режим, но данные могут быть утеряны как при крахе ОС, так и при крахе самого сервера MySQL (впрочем данные лишь за 1-2 секунды). innodb_log_buffer_size — размер буфера лога. Значение по умолчанию 1 МБ, увеличивать его стоит, если вы знаете, что будет большое количество транзакций InnoDB или если значение переменной состояния Innodb_log_waits растет. Вам вряд ли придется увеличивать его выше 8 МБ. innodb_log_file_size — максимальный размер одного лог-файла. При достижении этого размера InnoDB будет создавать новый файл. Значение по умолчанию 5 МБ, увеличение размера улучшит производительность, но увеличит время восстановления данных. Установите это значение в диапазоне 32 МБ — 512 МБ в зависимости от размера сервера (оценив его субъективно). Также для мониторинга работы сервера удобно использовать phpMyAdmin, интерес представляют вкладки Состояние и Переменные. Дополнительно phpMyAdmin дает советы по тюнингу тех или иных переменных в зависимости от параметров работы сервера. При подготовке статьи кроме официальной документации и собственной головы были использованы следующие материалы: http://blogfreebsd.com/freebsd/optimizaciya-mysql.html http://habrahabr.ru/blogs/mysql/41166/ Database Performance Blog – Percona http://mysyslog.ru/posts/223
