Мой любимые вопросы в ходе собеседования, для работы в качестве MySQL DBA или связанных с производительностью MySQL это вопросы о настройках MySQL Server сразу после установки, полагая, он был установлен с настройками по умолчанию.
Я удивлен, как много людей не дают какого-либо разумного ответа на этот вопрос, и сколько серверов, впринципе, работают с настройками по умолчанию.
Даже если вы сможете настроить довольно много переменных в MySQL сервера, лишь немногие из них действительно важны для наиболее распространенных видов нагрузок. После того как вы правильно настроите эти параметры остальные изменения настроек дадут только легкие улучшения производительности.
key_buffer_size - очень важный параметр, если вы используете MyISAM таблицы. Установить до 30-40% доступной памяти, если вы используете только таблицы MyISAM. Правильный размер зависит от количества индексов, объема данных и рабочей нагрузки - помните MyISAM использует кэш операционной системы для кэширования данных так что вы должны оставить достаточно памяти для него, ведь данные весят намного больше, чем индексы, в большинстве случаев. Стоит проверить key_buffer, иногда его размер выставляют 4G в то время как сумарный размер MYI файлов только 1 ГБ.Это было бы просто пустая трата ресурсов. Если вы используете несколько таблиц MyISAM лучше устанавливать этот параметр в пределах 16-32Mb чтобы он был достаточно, чтобы вместить индексы временных таблиц, которые создаются на диске.
innodb_buffer_pool_size Это очень важная переменная для настройки, если вы используете Innodb таблицы. Innodb гораздо более чувствительны к размер буфера по сравнению с MyISAM. MyISAM могут нормально работать с key_buffer_size по умолчанию даже при больших наборах данных, но innodb_buffer_pool_size по умолчанию будет тормозить бд. Кроме того, Innodb буферный пул кэширует данные и индексы, поэтому вам не нужно оставлять место для кэша ОС, т.е. значение параметра можно ставить до 70-80% оперативной памяти для установок с только Innodb таблицами. Те же правила применяются для key_buffer - если у вас есть небольшой набор данных, и он не будет расти резко не устанавливайте максимальные размеры innodb_buffer_pool_size - ведь можно найти лучшее применение для памяти.
innodb_additional_mem_pool_size этот параметр не сильно влияет на производительность, по крайней мере на ОС с хорошо распределенной паямтью. Но вы, возможно, захотите, выставить его 20 Мб (можно больше), потом вы можете посмотреть, сколько памяти Innodb выделяет для разных потребностей.
innodb_log_file_size важный параметр при больших объемах записи в базу особенно на больших наборах данных. Больший размер, часто, улучшает производительность, но при этом увеличивает время восстановления - будьте осторожны. Я обычно использую значения 64M-512M в зависимости от размера сервера.
innodb_log_buffer_size по умолчанию неплох для нормальной рабочей нагрузки - средняя нагрузка по записи и короткие транзакции. Однако если вы имеете пики нагрузок или активно работаете с блоб вы возможно захотите увеличитье этот параметр. Не устанавливайте его слишком большим - это будет тратой памяти, он всеравно обновляется каждую секунду и если вам не нужно место больше чем на одну секунду стоящее обновлений. 8MB-16MB, как правило, достаточно. Для малых установок использовать меньшие значения.
innodb_flush_log_at_trx_commit плачитесь о том что Innodb в 100 раз медленнее, чем MyISAM? Вы, наверное, забыли, изменить это значение. По умолчанию значение 1 означает каждый коммит (или каждое выражение за пределами транзакции) нужно сбрасить в лог, что является накладным, особенно если у вас кеш без батарейки. Многие приложения, особенно, переехавшие из таблиц MyISAM чувствуют себя в порядке со значением 2, которое означает, что что лог идет не сразу на диск, а только в кэш операционной системы. Журнал по-прежнему сбрасываются на диск каждую секунду, так что вы обычно потеряете не более 1-2 сек текущих обновлений. Значение 0 немного быстрее, но немного менее безопасне, так как вы можете потерять операции даже в том случае креша MySQL Server. Значение 2 приведет к потере данных, только при при креше ОС.
table_cache - открытие таблиц дорогое удовольствие. Например MyISAM таблицы помечаються в MYI-заголовках, что они в настоящее время используются. Вы же не хотите, чтобы это происходило очень часто, как правило, лучше увеличить размер кэша так, чтобы держать большинство ваших таблиц открытыми. Это использует некоторые ресурсы ОС и некоторый объем памяти, но для современного оборудования, как правило, это не проблема. 1024 является хорошим значением для приложений с несколькими сотнями таблиц (помните, каждое соединение нуждается в собственной записи), если у вас есть много соединений или много таблиц стоит сделать его еще больше. Я видел используемые значения параметра более 100 000.
thread_cache создание/разрушения потоков - дорогая операция, она происходит при подсоединении/разъединении. По крайней мере 16. Если в приложении ожидется скачкообразное увеличение конкурирующих соединений и быстрый рост созданных потоков, стоит поставить значения параметра повыше. Цель - избежать операции создания новых потоков в нормальном режиме.
query_cache_size - если ваше приложение интесивно читает из базы и вы не имеете слоя кеширования в приложении, этот параметр вам поможет. Значения от 32M-512M с обычно имеет смысл. Через некоторое время стоит проверить параметр как он используется. Для некоторых нагрузки кэш ударил соотношение ниже, чем это было бы оправдать, имеющих это позволило.
Примечание: как вы можете увидеть все это глобальные переменные. Эти переменные зависят от аппаратной части и набора механизмов хранения, в то время как в переменных сессии, как правило, зависят от конкретной нагрузки. Если у вас есть простые запросы нет никаких оснований для увеличения sort_buffer_size даже если у вас есть 64 ГБ памяти для этого. Кроме того это может снизить производительность.
Обычно я оставляю настройку переменных сеанса напоследок после того, как можно проанализировать нагрузки.
PS Примечание MySQL дистрибутив содержит кучу файлов my.cnf образец, который может быть хорошим шаблоном для использования. Как правило, он уже будет намного лучше, чем по умолчанию, если вы выбрать его правильно.
29 сентября 2006
Posted by peter
Source
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
0 коммент.:
Отправить комментарий