Настройки mysql-server сразу после установки

Мой любимый вопрос, задаваемый DBA, которые хотят увеличить производительность MySQL: “какие параметры надо настраивать в первую очередь, сразу после установки сервера?”


Я удивлен количеством людей, которые не могут дать ответа на этот вопрос. И еще более удивлен количеством серверов, которые работают с настройками по умолчанию

Для настройки доступно довольно большое количество параметров, но лишь некоторые из них действительно влияют на производительность сервера. После установки этих параметров в правильные для вашего проекта значения, остальные настройки будут лишь незначительно влиять на поведение сервера.

key_buffer_size - очень важный параметр, если вы используете MyISAM-таблицы. Установите его равным 30-40% от имеющейся оперативной памяти, если вы используете только MyISAM. Актуальное для вашей системы значение зависит от индексов, размера данных и рабочего процесса. Помните, MyISAM использует кэш операционной системы для хранения данных и вам необходимо позаботиться о достаточных размерах выделяемой памяти. Во многих случаях, объем данных может быть значительно больше. Проверьте, не используется ли завышенное значение key_buffer. Нередко параметр установлен в значение 4GB при суммарном объеме .MYI-файлов в один гигабайт. Это напрасная трата ресурсов. Если вы используете несколько таблиц MyISAM - понизьте значение этого параметра. Но не опускайте его ниже 16-23 MB - этого будет достаточно для размещения индексов временных таблиц, которые создаются на диске.

innodb_buffer_pool_size - это очень важный параметр для настройки InnoDB. Таблицы этого типа гораздо более чувствительны к размеру буфера, нежели MyISAM. MyISAM может нормально работать даже при дефолтном значении buffer_size, в отличии от InnoDB, производительность которых будет заметно ниже при значении innodb_buffer_pool_size по умолчанию и больших объемах данных. Также пул буферов InnoDB самостоятельно кэширует индексы и данные, так что не нужно оставлять место для кэша ОС. Обычно предполагается выделение 70 - 80% памяти для серверов, на которых ничего не запущено, кроме InnoDB. Некоторые правила key_buffer применимы и в этом параметре: если у вас небольшие объемы данных и они не собираются стремительно увеличиваться, не завышайте значение innodb_buffer_pool_size, вы сможете найти свободной оперативной памяти лучшее применение.

innodb_additional_mem_pool_size - этот параметр не имеет сильного влияния на производительность. По крайней мере в операционных системах с грамотным распределением памяти. Но вы можете установить значение этого параметра раным 20MB (иногда больше) и вы можете видетm сколько памяти выделяет InnoDB для различных нужд.

innodb_log_file_size - очень важный параметр для систем с интенсивной записью, особенно больших объемов данных. Увелечение значения этого параметра обычно дает прирост производительности, но будьте осторожны. Обычно я использую значения 64M - 512 MB в зависимости от сервера.

innodb_log_buffer_size - значение по умолчанию вполне подойдет для большинства проектов со средней интенсивностью записи и короткими транзакциями. Однако если у вас бывают пики активности или работа с большим объемом данных, вы, вероятно, захотите увеличить значение этого параметра. Не делайте его слишком большим, это повлечет лишний расход памяти. Буфер сбрасывается каждую секунду и вам не нужен бОльший объем памяти. Обычно вполне хватает 8 - 16МB. Чем меньше система - тем меньше должно быть значение.

innodb_flush_logs_at_trx_commit - Вам кажется, что InnoDB в сто раз медленнее MyISAM? Вероятно, вы забыли изменить значение этого параметра. Значение по умолчанию 1 означает, что после каждой завершенной транзакции (или после изменения состояния транзакции) лог должен быть сброшен на диск. Это достаточно дорогая операция, особенно если у вас нет Battery backed up cache. Многие приложения, особенно те, в которых раньше использовался MyISAM будут хорошо работать при значении 2, который означает, что не надо сбрасывать буфер на диск, а следует отправить его в кэш операционной системы. Лог по-прежнему будет сбрасываться на диск каждую секунду и максимум, что вы можете потерять - это 1-2 секунды записей. Значение 0 обеспечивает более высокую скорость, но и более низкую надежность. Есть вероятность потерять транзакции даже при падении mysql-сервера. При значении равном 2 единственная возможность потерять данные - это фатальный сбой операционной системы.

table_cache - открытые таблицы могут разрастаться. Например, таблицы MyISAM помечают MYI-заголовок, как используемый. Вы, конечно же, не хотите, чтобы это происходило слишком часто и это, как правило, хорошее решение. Лучше увеличить размер кэша, чтобы он мог вместить большинство открытых таблиц. Кэш использует некоторое количество памяти и ресурсов ОС, но для современной техники это, как правило, не проблема. Значение 1024 будет оптимальным решением для нескольких сотен открытых таблиц (помните, каждое соединение нуждается в собственной копии). Если у вас много соединений или большое количество открытых таблиц - увеличьте это значение. Я видел системы со значением этого параметра > 100.000

thread_cache - Создание/уничтожение нитей (threads) может ухудшать производительность, особенно если они создаются/уничтожаются при каждом соединении/разъединении. Обычно я устанавилваю значение этого параметра равным 16. Если приложение делает большие прыжки в параллельных соединениях, то можно увидеть, как быстро растет переменная Threads_Created. Параметр предназначен для того, чтобы не создавать новых нитей в нормальных операциях.

query_cache_size - если ваше приложение читает много данных и у вас нет кэша на уровне приложения, этот параметр может неплохо помочь. Но не устанавливайте слишком большого значения - это может замедлить работу и содержание такого кэша может обойтись довольно дорого. Оптимальные значения - от 32MB до 512MB. Тем не менее, проверьте эффективность работы кэша через некоторое время. Вполне возможно, что текущее значение слишком велико.

Примечание: как вы заметили, все эти переменные являются глобальными и зависят от аппаратного обеспечения и устройств хранения данных. Сессионные переменные зависят от специфики конкретного проекта. Если у вас простые запросы, вам совершенно не нужно увеличивать параметр sort_buffer_size, даже если в вашем распоряжении 64GB оперативной памяти. Кроме того, это может снизить производительность. Обычно я оставляю настройку сессионных переменных на второй шаг, уже после оценки фронта работ.

P.S.: в дистрибутиве MySQL есть отличные примеры файла my.cnf для систем различных размеров. Они могут использоваться в качестве базы для ваших собственных файлов конфигурации, главное правильно выбрать шаблон.

Оригинал: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/