Оптимизация настроек MySQL сервера

Эффективность работы MySQL сервера во многом зависят от правильности настроек. К сожалению, практически невозможно предложить универсальную, оптимальную настройку значений переменных, которая одинаково хорошо работала бы во всех ситуациях. Чтобы добиться от MySQL максимальной надежности и производительности, необходимо учесть все особенности каждого конкретного случая. Нередко некоторые параметры приходится настраивать «на лету», экспериментально, полагаясь только на опыт и квалификацию...
Чтобы не запутаться в многообразии влияющих на производительность переменных, разделим их на отдельные группы (разделы). Переменные, объединенные в один раздел будут отвечать за определенные настройки MySQL: кэширование запросов, ограничения, основные и потоковые настройки, буферизацию, тайминг и InnoDB.
Вначале отметим имена переменных, изменившиеся в 4-й версии MySQL. Несмотря на нововведения, в сети нередко встречаются как новые, так и старые имена, что иногда приводит к путанице.
В 4-й версии MySQL у некоторых переменных добавилось новое окончание – “_size”. Это переменные:

— read_buffer_size (ранее record_buffer);
— skip_external_locking (ранее skip_locking) раздел «Базовые настройки»;
— thread_cache_size;
— переменные из раздела «Буферы». 

Переменные можно распределить на две основные категории. Это переменные-флаги и переменные со значениями. Переменные-флаги просто указываются, а переменные со значениями хранятся в конфигурационном файле в форме: variable=value. Также следует обратить внимание на «черточку» («-» или «_»), присутствующую в наименовании некоторых переменных. Переменные с дефисом («-»), это стартовые опции сервера, которые при работе сервера менять нельзя (при помощи «SET»).
Переменные со знаком подчеркивания («_»), это опции работы сервера и поэтому их можно менять во время работы сервера.
В том случае, когда используется переменная состояния или необходимо контролировать значение переменной, название которой имеет вид типа Variable_Name, для просмотра значения этой переменной выполняется запрос:

SHOW STATUS LIKE «Variable_Name». 

Можно также перейти на расположенную в phpMyAdmin вкладку «состояние» и посмотреть дополнительные комментарии по значению этой переменной.

А теперь рассмотрим описания переменных и рекомендации по выбору их значений.

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

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 000 000 000, то лучше его сразу уменьшить до «безопасных» значений.

max_sort_length — количество начальных байт из полей типа TEXT или BLOB, применяемых в процессе сортировки. По умолчанию установлено значение в один килобайт (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 хранит в кэше перечень таблиц, с закэшированной выборкой. Если в таблице, выборка из которой имеется в кэше, возникают изменения, то 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/tormoz_zapros).

long_query_time — запрос, выполняющийся дольше указанного времени, будет считаться «медленным» (время указывается в секундах).

net_read_timeout — время (указывается в секундах), в течение которого сервер ожидает получения данных, после чего соединение будет разорвано. Если сервер не предназначен для обслуживания клиентов с медленными или нестабильными каналами передачи данных, то 15 секунд бывает достаточно.

net_write_timeout — время (указывается в секундах), в течение которого сервер ожидает записи данных, после чего соединение будет разорвано. Если сервер не предназначен для обслуживания клиентов с медленными или нестабильными каналами передачи данных, то 15 секунд бывает достаточно.

wait_timeout — время (указывается в секундах), в течение которого сервер ожидает проявления активности соединения, перед тем, как прервать его. Как правило 30-ти секунд бывает вполне достаточно.

Буферы

Как известно, все буферы имеют общую особенность — если из-за слишком большого размера буфера данные уходят в файл подкачки, то такой буфер будет только тормозить работу системы. Поэтому всегда необходимо ориентироваться на доступный объем физической ОПЕРАТИВНОЙ памяти.

key_buffer_size — объем буфера под индексы. Данный буфер доступен всем потокам. Очень важная для производительности настройка. Имеет значение по умолчанию равное 8 МБ. Однако, такое значение давно морально устарело... Рекомендуется установить этот параметр в 15-30% от общего объема оперативной памяти, однако не стоит устанавливать его больше, чем общий объем всех *.MYI файлов.
Для оптимальной установки необходимого значения необходимо понаблюдать за переменными состояния Key_read_requests и Key_reads. Отношение Key_reads/Key_read_requests необходимо поддерживать как можно меньшим, желательно меньше 0,01. При большом значении этого отношения размер буфера необходимо увеличить.

max_heap_table_size — максимальный размер таблицы, которая может храниться в памяти (типа MEMORY). Имеет значение по умолчанию равное 16 МБ. Если в работе сервера не применяются MEMORY таблицы, то данное значение лучше сделать равным tmp_table_size.

myisam_sort_buffer_size — объем буфера, выделяемого MyISAM при сортировке индексов (REPAIR TABLE) или при создании индексов (ALTER TABLE, CREATE INDEX).
Установленное по умолчанию значение 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-2 МБ, если позволяет память. Указанное значение памяти выделяется на каждый поток.

sort_buffer_size — потоки, производящие операции сортировки или группировки, выделяют буфер указанного размера. Значение параметра по умолчанию — 2 МБ. При активном использовании указанных типов запросов (если при этом позволяет память) указанное значение рекомендуется увеличить. На необходимость увеличения sort_buffer_size может указывать большое значение переменной Sort_merge_passes. Кроме того рекомендуется проверить скорость выполнения запросов типа

SELECT * FROM table ORDER BY name DESC 

на таблицах большого размера. Возможно, что увеличение размера буфера только лишь замедлит работу.

table_cache (с версии 5.1.3 table_open_cache) — количество открытых кэшированных таблиц, используемых для всех потоков. Иногда открытие файла таблицы бывает довольно ресурсоемкой операцией, поэтому открытые таблицы лучше держать в кэше. Кроме этого необходимо учесть, что каждая запись использует системный дескриптор, следовательно придется увеличить ограничения на число дескрипторов (ulimit). Значение параметра по умолчанию — 64, но его рекомендуется увеличить до общего числа таблиц (в разумных пределах). Переменная состояния Opened_tables помогает контролировать количество таблиц, открытых не в кэше. Желательно, чтобы значение этой переменой было минимальным.

tmp_table_size — максимальный объем памяти, используемый для временных таблиц, которые MySQL создает для внутренних нужд. Указанное значение ограничивается переменной max_heap_table_size. В связи с этим, в итоге выбирается минимальное значение из tmp_table_size и max_heap_table_size. При этом остальные временные таблицы создаются на диске.
Значение параметра по умолчанию зависит от настроек системы. Можно установить его равным 32МБ и посмотреть на переменную состояния Created_tmp_disk_tables. Значение этой переменной рекомендуется минимизировать.

Все значения, указанные в конфигурационном файле заданы в байтах. Килобайты и мегабайты в процессе настройки необходимо предварительно перевести в байты.

InnoDB

innodb_additional_mem_pool_size — объем памяти, выделяемый InnoDB для различных внутренних структур. Если этой памяти будет недостаточно, то дополнительная память будет запрошена у ОС, а в лог ошибок MySQL будет записано предупреждение.

innodb_buffer_pool_size — объем памяти, выделяемый InnoDB для индексов и данных. Рекомендуется установить максимально возможное значение. Можно увеличивать до 80% ОЗУ или до общего размера InnoDB таблиц.

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 дает советы по тонкой настройке переменных в зависимости от эффективности работы сервера.

Мне понравилось! Занести себе в закладки:  

Тоже интересно: