Пользователь
0,1
рейтинг
30 ноября 2010 в 22:14

Разработка → Настройка и оптимизация MySQL сервера

В этой статье будут описаны различные настройки MySQL, преимущественно те, которые влияют на производительность. Для удобства все переменные разделены по разделам (базовые настройки, ограничения, настройки потоки, кэширование запросов, тайминги, буферы, InnoDB). Сначала уточним имена некоторых переменных, которые изменились в версии 4 MySQL, а в сети продолжают встречаться и старые и новые варианты имен, что вызывает вопросы.

Итак, в 4 версии у ряда переменных появилось окончание _size. Это касается переменной thread_cache_size и переменных из раздела Буферы. А переменная read_buffer_size до версии 4 называлась record_buffer. Также переменная skip_external_locking из раздела Базовые настройки до версии 4 называлась skip_locking.
Переменные делятся на две основных категории: переменные со значениями и переменные-флаги. Переменные со значениями записываются в конфигурационном файле в виде variable = value, а переменные-флаги просто указываются. Также вы наверное заметили, что в некоторых случаях в названиях переменных используется "-", а в некоторых "_". Переменные с дефисом являются стартовыми опциями сервера и их нельзя изменить при работе сервера (при помощи SET); переменные с подчеркиванием являются опциями работы сервера и их возможно изменять на лету. Если речь идет о «переменной состояния» или рекомендуется наблюдать за значением переменной, название которой записано в виде Variable_Name, то следует выполнять запрос SHOW STATUS LIKE "Variable_Name" для получения значения этой переменной, либо заглянуть на вкладку состояние в phpMyAdmin, где дополнительно будут комментарии по значению этой переменной.
А теперь займемся описанием переменных и их возможными значениями.

Базовые настройки


  • low-priority-updates — эта опция снижает приоритет операций INSERT/UPDATE по сравнению с SELECT. Актуально, если данные важно быстрее прочитать, чем быстрее записать.
  • skip-external-locking — опция установлена по умолчанию, начиная с версии 4. Указывает MySQL-серверу не использовать внешние блокировки при работе с базой. Внешние блокировки необходимы в ситуациях, когда несколько серверов работают с одними и теми же файлами данных, т.е. имеют одинаковую datadir, что на практике не используется.
  • skip-name-resolve — не определять доменные имена для IP-адресов подключающихся клиентов. При этом пользовательские разрешения нужно настраивать не на хосты, а на IP-адреса (за исключением localhost). Если вы соединяетесь с сервером только с локальной машины, то особого значения не имеет. Для внешних соединений ускорит установку соединения.
  • skip-networking — не использовать сеть, т.е. вообще не обрабатывать TCP/IP соединения. Общение с сервером при этом будет происходить исключительно через сокет. Рекомендуется, если у вас нет ПО, которое использует только TCP/IP для связи с сервером.

Ограничения


  • bind-address — интерфейс, который будет слушать сервер. В целях безопасности рекомендуется установить здесь 127.0.0.1, если вы не используете внешние соединения с сервером.
  • max_allowed_packet — максимальный размер данных, которые могут быть переданы за один запрос. Следует увеличить, если столкнетесь с ошибкой «Packet too large».
  • max_connections — максимальное количество параллельных соединений к серверу. Увеличьте его, если сталкиваетесь с проблемой «Too many connections».
  • max_join_size — запрещает SELECT операторы, которые предположительно будут анализировать более указанного числа строк или больше указанного числа поисков по диску. Используется для защиты от кривых запросов, которые пытаются считать миллионы строк. Значение по умолчанию более 4 миллиардов, поэтому вы скорее всего захотите его значительно уменьшить.
  • max_sort_length — указывает, сколько байт из начала полей типа BLOB или TEXT использовать при сортировке. Значение по умолчанию 1024, если вы опасаетесь некорректно спроектированных таблиц или запросов, то следует его уменьшить.

Настройки потоков


  • thread_cache_size — указывает число кэшируемых потоков. После обработки запроса сервер не будет завершать поток, а разместит его в кэше, если число потоков, находящих в кэше меньше, чем указанное значение. Значение по умолчанию 0, увеличьте его до 8 или сразу до 16. Если наблюдается рост значения переменной состояния Threads_Created, то следует еще увеличить thread_cache_size.
  • thread_concurrency — актуально только для Solaris/SunOS вопреки тому, что пишут в сети. «Подсказывает» системе сколько потоков запускать одновременно, выполняя вызов функции thr_setconcurrency. Рекомендованное значение — двойное или утроенное число ядер процессора.

Кэширование запросов


  • query_cache_limit — максимальный размер кэшируемого запроса.
  • query_cache_min_res_unit — минимальный размер хранимого в кэше блока.
  • query_cache_size — размер кэша. 0 отключает использование кэша. Для выбора оптимального значения необходимо наблюдать за переменной состояния Qcache_lowmem_prunes и добиться, чтобы ее значение увеличивалось незначительно. Также нужно помнить, что излишне большой кэш будет создавать ненужную нагрузку.
  • query_cache_type — (OFF, DEMAND, ON). OFF отключает кэширование, DEMAND – кэширование будет производиться только при наличии директивы SQL_CACHE в запросе, ON включает кэширование.
  • query_cache_wlock_invalidate — определяет будут ли данные браться из кеша, если таблица, к которым они относятся, заблокирована на чтение.

Кэш запросов можно представить себе как хэш-массив, ключами которого являются запросы, а значениями — результаты запросов. Кроме результатов, MySQL хранит в кэше список таблиц, выборка из которых закэширована. Если в любой из таблиц, выборка из которой есть в кэше, проиcходят изменения, то MySQL удаляет из кэша такие выборки. Также MySQL не кеширует запросы, результаты которых могут измениться.
При запуске MySQL выделяет блок памяти размером в query_cache_size. При выполнении запроса, как только получены первые строки результата сервер начинает кэшировать их: он выделяет в кэше блок памяти, равный query_cache_min_res_unit, записывает в него результат выборки. Если не вся выборка поместилась в блок, то сервер выделяет следующий блок и так далее. В момент начала записи MySQL не знает о размере получившейся выборки, поэтому если записанный в кэш размер выборки больше, чем query_cache_limit, то запись прекращается и занятое место освобождается, следовательно, если вы знаете наперед, что результат выборки будет большим, стоит выполнять его с директивой SQL_NO_CACHE.

Тайминги


  • interactive_timeout — время в секундах, в течение которого сервер ожидает активности со стороны интерактивного соединения (использующего флаг CLIENT_INTERACTIVE), прежде чем закрыть его.
  • log_slow_queries — указывает серверу логировать долгие («медленные») запросы (выполняющиеся дольше long_query_time). В качестве значения передается полное имя файла (например /var/log/slow_queries).
  • long_query_time — если запрос выполняется дольше указанного времени (в секундах), то он будет считаться «медленным».
  • net_read_timeout — время в секундах, в течение которого сервер будет ожидать получения данных, прежде чем соединение будет прервано. Если сервер не обслуживает клиентов с очень медленными или нестабильными каналами, то 15 секунд здесь будет достаточно.
  • net_write_timeout — время в секундах, в течение которого сервер будет ожидать получения данных, прежде чем соединение будет прервано. Если сервер не обслуживает клиентов с очень медленными или нестабильными каналами, то 15 секунд здесь будет достаточно.
  • wait_timeout — время в секундах, в течение которого сервер ожидает активности соединения, прежде чем прервет его. В общем случае 30 секунд будет достаточно.

Буферы


У всех буферов есть общая черта — если из-за установки большого размера буфера данные будут уходить в файл подкачки, то от буфера будет больше вреда, чем пользы. Поэтому всегда ориентируйтесь на доступный вам объем физической ОЗУ.
  • key_buffer_size — размер буфера, выделяемого под индексы и доступного всем потокам. Весьма важная настройка, влияющая на производительность. Значение по умолчанию 8 МБ, его однозначно стоит увеличить. Рекомендуется 15-30% от общего объема ОЗУ, однако нет смысла устанавливать больше, чем общий размер всех .MYI файлов. Наблюдайте за переменными состояния Key_reads и Key_read_requests, отношение Key_reads/Key_read_requests должно быть как можно меньше (< 0,01). Если это отношение велико, то размер буфера стоит увеличить.
  • max_heap_table_size — максимальный допустимый размер таблицы, хранящейся в памяти (типа MEMORY). Значение по умолчанию 16 МБ, если вы не используете MEMORY таблиц, то установите это значение равным tmp_table_size.
  • myisam_sort_buffer_size — размер буфера, выделяемого MyISAM для сортировки индексов при REPAIR TABLE или для создания индексов при CREATE INDEX, ALTER TABLE. Значение по умолчанию 8 МБ, его стоит увеличить вплоть до 30-40% ОЗУ. Выигрыш в производительности соответственно будет только при выполнении вышеупомянутых запросов.
  • net_buffer_length — объем памяти, выделяемый для буфера соединения и для буфера результатов на каждый поток. Буфер соединения будет указанного размера и буфер результатов будет такого же размера, т.е. на каждый поток будет выделен двойной размер net_buffer_length. Указанное значение является начальным и при необходимости буферы будут увеличиваться вплоть до max_allowed_packet. Размер по умолчанию 16 КБ. В случае ограниченной памяти или использования только небольших запросов значение можно уменьшить. В случае же постоянного использования больших запросов и достаточного объема памяти, значение стоит увеличить до предполагаемого среднего размера запроса.
  • read_buffer_size — каждый поток при последовательном сканировании таблиц выделяет указанный объем памяти для каждой таблицы. Как показывают тесты, это значение не следует особо увеличивать. Размер по умолчанию 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 дает советы по тюнингу тех или иных переменных в зависимости от параметров работы сервера.
При подготовке статьи кроме официальной документации и собственной головы были использованы следующие материалы:

С интересом выслушаюпрочитаю комментарии, замечания и дополнения.
Дополнительное обсуждение статьи вы можете найти здесь.
@peter23
карма
172,2
рейтинг 0,1
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (19)

  • +1
    skip-external-locking —… когда несколько серверов работают с одними и теми же файлами данных, т.е. имеют одинаковую datadir, что на практике не используется

    почему не используется? и для чего вообще предназначено было с практической точки зрения? для поднятия двух инстансов БД на разных физических компах, которые работают с одними и теми же данными? если да, то о существовании подобного решения спрашивал у одного моего знакомого специалиста, он не знал о такой возможности
    • +1
      Для поднятия нескольких инстансов БД на одном физическом компе. Когда-то разработчики думали, что это может быть востребовано.
    • 0
      и для чего вообще предназначено было с практической точки зрения?

      dev.mysql.com/doc/refman/5.0/en/external-locking.html
  • –1
    спасибо!
  • +1
    По поводу SHOW STATUS LIKE…
    Есть сессионые значения, есть глобальные — смотреть scope в документации. Для последних надо использовать SHOW GLOBAL STATUS LIKE '%disk%' например.

    По поводу max_heap_table_size, а также tmp_table_size:
    Если в explore выражения появляется temporary — значить при выполнении этого запроса будет создаваться временная таблица MEMORY. Если в это временной таблице нет TEXT или BLOB полей — тогда они, а также те что не влезают в указанные выше параметры создаются на диске. Каждый раз при этом инкрементируется переменная Сreated_tmp_disk_tables. Она не уменьшаеться :)
    • 0
      Сорри
      Если в это временной таблице есть TEXT или BLOB поля…
    • +2
      Также по поводу где смотреть.

      По сети гуляют два замечательных скрипта tuning-primer.sh и mysqlreport — они групируют и вычисляют некоторые значения, что для начинающего будет очень позновательно. но конечно в детали надо вникать по-любому.

      Смотреть уже известные параметры проще через mysql клиент в ssh:
      создайте alias для себя типа
      alias rootdb='mysql -u root -ppass' 
      ну и для каждой базы со своим юзером и запускайте что нить типа
      rootdb --execute="show global status like '%disk%';"
  • +3
    Спасибо автору. Записал в закладки. Очень хорошая выдержка из километров документации.
  • +7
    Очень полезный скрипт:
    MySQL Performance Tuning Primer Script
    www.day32.com/MySQL/
  • +16
    Вы уж извините, но это не «Настройка и оптимизация», а перевод части документации по параметрам, с помощью которых можно оптимизировать работу сервера. Про саму настройку ничего, про оптимизацию тоже (где рекомендации, типовые случаи/примеры ?).
    • +1
      • 0
        Первая статья уже потеряла актуальность, да и вторая ориентирована на 4 версию и некоторые моменты уже неверны.
    • +1
      Это не дословный перевод, я постарался более понятно изложить описание опций. Также, к большинству опций приведено дополнительное описание, рекомендованные значения и на что ориентироваться при выборе значений, этого в документации нет.
      А типовых случаев не бывает, как я считаю. В каждом случае нужно подбирать свои опции, поэтому готовых примеров не привожу.
      Предложите более адекватное название статьи.
      • 0
        innodb_flush_log_at_trx_commit — имеет три допустимых значения: 0, 1, 2. При значении равном 0, лог сбрасывается на диск один раз в секунду, вне зависимости от происходящих транзакций. При значении равном 1, лог сбрасывается на диск при каждой транзакции. При значении равном 2, лог пишется при каждой транзакции, но не сбрасывается на диск никогда, оставляя это на совести ОС. По умолчанию используется 1, что является самой надежной настройкой, но не самой быстрой. В общем случае вы можете смело использовать 2, данные могут быть утеряны лишь в случае краха ОС и лишь за несколько секунд (зависит от настроек ОС). 0 — самый быстрый режим, но данные могут быть утеряны как при крахе ОС, так и при крахе самого сервера MySQL (впрочем данные лишь за 1-2 секунды).

        А вам не кажется, что самый быстрый режим это 2?

        А для мониторинга работы сервера лучше использовать mytop, innotop и утилиты из maatkit.

        Итого — читайте официальную документацию и блог www.mysqlperformanceblog.com/, а не переводы с «понятным» изложением.
  • 0
    Не статья а бонус прям какой то )
    Автору большое спасибо. Ждемс продолжения )
  • +2
    Я негодую!
    Ни слова про innodb_flush_method, довольно важную настроечку, избавляющую от ненужного дабл буфферинга

    www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ «Eliminate Double Buffering»
  • +3
    Следует также заметить, что при изменении innodb_log_file_size придется остановить сервер и удалить (или переименовать) старый лог файл, для чего могут потребоваться рутовые права. Если же старый файл не удалять, mysql просто откажется стартовать.
    Моя выжимка из конфига (не самая сильная dev-машина, 3G RAM, Dual CPU E2140 @ 1.60GHz)

    [innodb]
    innodb_buffer_pool_size = 1024M
    innodb_flush_method = O_DIRECT
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 4M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 8

    [mysqld]
    log_slow_queries = /var/log/mysql/mysql-slow.log
    long_query_time = 1
    log-queries-not-using-indexes

    default-storage-engine = InnoDB

    collation_server=utf8_general_ci
    character_set_server=utf8
  • –1
    За что спрашивается эта стотья попала на главную?
    • 0
      Вероятно за то, что в ней присутствует полезная информация для какой-то части хабрапользователей. Ваш Кэп.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.