Назад | Перейти на главную страницу

Какие варианты конфигурации MySQL обеспечивают наибольшее повышение скорости?

Какие варианты конфигурации MySQL обеспечивают наибольшее повышение скорости?

Мне интересно узнать о фактических улучшениях файла конфигурации, типах таблиц, настройках оборудования, репликации и т. Д. Что-нибудь кроме структуры запроса и структуры таблицы (их легко найти на веб-сайте и в Stack Overflow). Такие вещи, как настройки кеша запросов, дали вам наибольшую скорость? Как насчет дисков; лучше на внешнем RAID или внутреннем? Дала ли репликация лучшую производительность, особенно при чтении больших запросов?

Какие еще настройки / изменения вы внесли для повышения производительности MySQL?

Примечание: я понимаю, что они очень зависят от использования (то есть небольшой веб-сайт или хранилище данных), но, поскольку я думаю, что большинство из нас, вероятно, работает на разных сайтах / системах, хорошо знать различные методы, которые могут применяться к разным ситуации. Кроме того, я думаю, что некоторые приемы можно переносить между ситуациями.

Вот мои рекомендации (ваша толщина может отличаться)

  • Используйте аппаратный RAID. Это противоречит моим рекомендациям использовать программный RAID в других сообщениях, однако это особая ситуация, когда вам нужна аппаратная карта RAID. В частности, вы хотите, чтобы энергонезависимая память с резервным питанием от батареи на карте RAID сокращала время, необходимое для переноса файла журнала на диск с помощью fsync.
  • Используйте ТОЛЬКО тома RAID 1 или RAID 10. Стоимость записи в RAID 5 или 6 слишком высока, чтобы выдержать смешанную рабочую нагрузку чтения / записи.
  • Используйте отдельные LUN ​​для томов data, log и tmp. Все они должны быть отделены от ОС и томов подкачки.
  • Использовать InnoDB.
  • Используйте innodb_file_per_table
  • Используйте 64-битную ОС
  • Установите буферный пул InnoDB на ~ 80% доступной оперативной памяти.
  • Задайте для файлов журнала 1/4 размера пула буферов, от 2 до 4 файлов журнала. Файлы журнала большего размера означают более медленное завершение работы и время восстановления, но позволяют быстрее восстанавливать большие дампы базы данных.
  • log_slow_queries, log-query-not-using-indexes, set-variable = long_query_time = 1, исследуйте каждый запрос в этом журнале, реорганизуйте вашу схему, чтобы избежать сканирования таблиц и таблиц tmp, когда это возможно.

В очередной раз Дэйв Чейни действительно выбил это из парка здесь. Я действительно не могу ничего добавить к его ответу на ваш вопрос. Однако я хотел бы указать на то, о чем вы не спрашивали. Как Джереми Заводный и Петр Зайцев научили меня несколько лет назад, ваша рентабельность инвестиций за время, потраченное на отслеживание и оптимизацию плохих запросов, будет в 10 раз меньше рентабельности инвестиций за время, потраченное на внесение изменений в конфигурацию. Конечно, вы не хотите иметь плохую конфигурацию, неправильную настройку RAID или недостаточную оперативную память. Но среди отличных и даже маргинальных плохих запросов администраторов баз данных MySQL (обычно от разработчиков / фреймворков, а не администраторов баз данных) есть хронический состояние, где плохая конфигурация выносимый один.

(Некоторое время я копался в поисках этих прилагательных и до сих пор не доволен теми, которые я выбрал.)

Я хотел бы еще раз подчеркнуть, что если ваши разработчики используют ORM, подобные тем, которые распространены в таких фреймворках, как Ruby on Rails и Django, вы ДЕЙСТВИТЕЛЬНО ДОЛЖНЫ отслеживать запросы, которые попадают в вашу БД. Когда разработчики перестают думать о SQL и позволяют БД абстрагироваться, это действительно неприятно. Мне нравятся две только что упомянутые структуры. (Не голосуйте за меня за плохое высказывание.) Это просто делает Query Sleuthing очень важным. (Читать: Безопасность работы)

Несколько других вещей (которые не были упомянуты в ответе Дэйва Чейни)

  • Попробуйте установить innodb_flush_method на O_DIRECT, чтобы избежать двойной буферизации данных. Избегайте этого, если на вашей карте RAID нет кэша записи с резервным питанием от батареи или ваши данные находятся в SAN.

  • Также поиграйте с innodb_thread_concurrency. Я считаю, что значение по умолчанию - 8, но стоит настроить это, чтобы посмотреть, улучшит ли это производительность.

  • Убедитесь, что кеш запросов включен, и проверьте статистику, чтобы узнать, какова частота запросов. Если это хорошо, попробуйте увеличить его, чтобы увидеть, повысит ли он процент попаданий.

  • В зависимости от запущенных приложений вы можете изменить уровень изоляции по умолчанию. По умолчанию используется REPEATABLE_READ, но READ_COMMITTED может дать вам лучшую производительность.

  • Если ваши операторы в основном представляют собой UPDATE и DELETE, вы можете попробовать заполнить кеш на ведомом устройстве, выполнив запрос SELECT, который возвращает набор результатов, который должен быть изменен. Проверьте mk-slave-prefetch инструмент, который сделает это за вас

  • Взгляните на другие механизмы хранения, кроме MyISAM и InnoDB.

Ничего не могу сказать об оборудовании, но можете дать http://blog.mysqltuner.com попытка. Это Perl-скрипт, который анализирует ваши настройки MySQL.

Вы можете увидеть образец вывода на http://www.thomas-krenn.com/de/wiki/MySQL_Performance_Tuning#mysqltuner.pl

Первое, что вам следует сделать, это посмотреть на параметры памяти. Настройки MySQL по умолчанию очень консервативны. Какой бы движок вы ни использовали, вам, вероятно, придется поднять несколько параметров памяти в десять или даже в сто раз.

Следующее, что вам нужно сделать, это посмотреть на кеш таблицы. Значение по умолчанию - 64, что полезно, только если у вас не более 60 таблиц. Вы захотите поднять этот долгий путь.

Третье, что вам нужно сделать, это посмотреть на параметры потока и соединения. Значение wait_timeout по умолчанию для большинства веб-приложений очень велико и может быть сокращено до примерно 30 секунд. Это также улучшит использование памяти, так как MySQL будет быстрее подключаться к соединениям, оставляя гораздо меньше лежать в состоянии «сна».