Установка и первоначальная настройка MySQL в linux

Оставьте комментарий 6,950

В этой статье будут описаны различные настройки 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
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 МБ в зависимости от размера сервера (оценив его субъективно).

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

Хочу рассказать о настройках, которые применял, но не все из них помогли, просто опишу их.

Введение

Как я успел разобраться, при каждом соединении с MySQL создается mysqld (демон), который и обрабатывает все запросы соединения. Вот в блоке описывается именно настройки таких демонов.

Итак, давайте рассмотрим настройки демона .

Выставить кодировку по умолчанию можно так:

character-set-server = utf8
collation-server = utf8_unicode_ci

Защитить сервер от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер, можно так:

max_join_size = 1000000

Буфер можно выставить 25% от общего объема оперативной памяти:

key_buffer_size = 2048M

как я понял, это буфер обмена для всех демонов, т.е. реально будет: key_buffer_size / кол-во демонов = ???M

Размер стека для каждого потока (демона):

thread_stack = 512K

стек - это место для хранения списка задач (открыть таблицу, выполнить запрос, закрыть и т.п.)

Кол-во потоков, которые сервер должен поместить в кэш для повторного использования:

thread_cache_size = 32

т.е. если к примеру есть часто повтояющийся SELECT * FROM myTable, то он попадет в кэш, чтобы не выполняться каждый раз.

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

tmp_table_size = 512M

Установить максимальный размер таблиц типа MEMORY (HEAP) можно так:

max_heap_table_size = 256M

Размер буфера, выделяемого демону при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение

Размер буфера выделяемого для сортировки MyISAM индексов с помощью оператора REPAIR TABLE или при создании индексов операторами CREATE TABLE, ALTER TABLE:

myisam_sort_buffer_size = 256M

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

read_buffer_size = 4M

Размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличение значения данной переменной может существенно увеличить эффективность конструкции ORDER BY. Имейте в виду, так как данный буфер выделяется для каждого демона, не следует устанавливать чересчур большое значение.

read_rnd_buffer_size = 8M

Размер буфера использующегося при операциях объединения таблиц (если не используются индексы). Буфер устанавливается один раз во время каждой операции объединения

join_buffer_size = 8M

Величина буфера, который используется для индексов, всех демонов. Если используется много DELETE или INSERT запросов к таблицам с большим кол - индексов, то увеличение значения повысит скорость выполнения таких запросов. Для достижения еще большей скорости нужно использовать LOCK TABLES. Советуют устанавливать не больше чем 1/3 озу и не больше объема всех б.д.

key_buffer = 2048M

Максимально количество соединений клиентов с сервером

max_connections = 35

Задает максимально количество неудачных попыток подключения с хоста. Значение по-умолчанию 10. При достижении данного значения, хост блокируется. Разблокировать хост можно с помощью: mysql> FLUSH HOSTS

max_connect_errors = 50

Максимальное число одновременных подключений для одной учетной записи MySQL. Значение по-умолчанию 0, отсутствие каких-либо ограничений

max_user_connections = 25

table_cache старое название для переменной table_open_cache, в нем указывается к оличество открытых таблиц для всех демонов. Увеличение значения приведет к увеличению количества используемых дескрипторов файла. Советуют рассчитывать по формуле : количество одновременных соединений * количество открытых таблиц в соединении. Т .е. для каждого соединения используется свои ячейки из кэша. Для проверки можно запустить mysqltuner.pl

table_cache = 128

Количество одновременно запускаемых демонов, советуют формулу: количество ядер процессора умножаем на 2

thread_concurrency = 16

Размер буфера для соединений, устанавливаемый сервером в промежутках между запросами

net_buffer_length = 1024

Максимальный объем одного SQL-запроса к серверу. Изначально буфер сообщений имеет размер net_buffer_length и при необходимости, автоматически увеличивается до значения данной переменной.

max_allowed_packet = 512M

Переменная задает количество байт при операциях сортировки значений BLOB или TEXT. Использованы только первые max_sort_length, остальные игнорируются

max_sort_length = 512

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

query_cache_limit = 2M

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

query_cache_size = 16M

Полезная настройка: вид кэширования:

0 - ничего не кэшировать (по-умолчанию)
1 - кэшировать все запросы, кроме SELECT SQL_NO_CACHE
2 - кэшировать только запросы, начинающихся с конструкции SELECT SQL_CACHE

query_cache_type = 2

Настройки innodb (извините, что без пояснений, просто оставлю их тут, чтобы не забыть):

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M :autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 64M
innodb_additional_mem_poo l_size = 32M
innodb_file_io_threads = 8
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_c ommit = 2
innodb_flush_method = O_DIRECT

transaction-isolation = READ-COMMITTED

Настройки блока для создания дампов

default-character-set = utf8

Старое название следующей настройки: character-set-server = utf8 выдает ошибку: /usr/bin/mysql_upgrade: unknown variable "character-set-server=utf 8

default-character-set = utf8

Надеюсь, кому-нибудь помог разобраться, удачки в освоении MySQL.

Перевод

Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL.

Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.

Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.

key_buffer_size - крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер - помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось - нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех.MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ - они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
innodb_buffer_pool_size - не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size , однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size . InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС - устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции - аналогично key_buffer_size - не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
innodb_additional_mem_pool_size - данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
innodb_log_file_size - крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Бо льшие размеры увеличивают быстродействие, однако будьте осторожны - увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
innodb_log_buffer_size - стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size . Однако не переусердствуйте - слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение - около 8-16 МБ, а для небольших баз - и того меньше.
- жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit . Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
table_cache - открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки.MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений - увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
thread_cache - создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache . Цель - не допускать создания новых потоков в условиях нормального функционирования сервера.
query_cache_size - если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение - от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов - в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.

Как Вы можете видеть, это - глобальные настройки. Эти переменные зависят от «железа» сервера и используемых движков mySQL, в то время как сессионные переменные обычно настраиваются специально под конкретные задачи. Если Вы в основном используете простые запросы, то нет никакой необходимости увеличивать значение sort_buffer_size , даже если у Вас есть лишние 64 ГБ оперативной памяти. Более того, большие значения кэшей могут только ухудшить производительность сервера. Сессионные переменные лучше оставить на потом, для тонкой настройки сервера.

PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.

Итак, в 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 выделяет блок памяти размером в 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
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 дает советы по тюнингу тех или иных переменных в зависимости от параметров работы сервера.

При подготовке статьи кроме официальной документации и собственной головы были использованы следующие материалы.

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

MySQL - свободная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компанию MySQL AB. Продукт распространяется как под GNU General Public License, так и под собственной коммерческой лицензией. Помимо этого разработчики создают функциональность по заказу лицензионных пользователей, именно благодаря такому заказу почти в самых ранних версиях появился механизм репликации.

Откройте файл настроек mysql, например:

/etc/mysql/my.cnf

Сымае распространенные параметры, на которые стоит обратить внимание и изменить под Ваши требования:

key_buffer_size

Если Вы используете только MyIsam таблицы, устанавливайте это значение в 30%…40% всей доступной оперативной памяти на сервере. MyIsam использует кеш операционной системы для данных, поэтому учтите, что оставшаяся свободная память понадобится именно для этого. Если же MyIsam таблиц у Вас немного и их совокупный размер маленький - оставьте это значение в пределах 32M.

innodb_buffer_pool_size

Если Вы использутете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буффер InnoDB кеширует и данные и индексы (а кеш операционной системы не используется), поэтому значение этого ключа стоит устанавливать в 70%…80% доступной памяти.

Если Ваш сервер работает на линкусе или юниксе, не забудьте установить параметр innodb_flush_method в значение “O_DIRECT”, что-бы избежать кеширования на уровне ОС того, что уже кеширует Mysql.

innodb_log_file_size

Обратите внимание на этот параметр, если у Вас предусматривается большой показатель записей. Чем больше размер этого ключа, тем более эффективно будет происходить запись данных. Но учтите, что при этом увеличится время восстановления системы! Этот параметр обычно устанавливают в 64M-512M.

innodb_flush_log_at_trx_commit

Этот параметр в значительной степени влияет на скорость работы (записи) innoDB таблиц.
Значение “1″ означает, что любая завершенная транзакция будет синхронно сбрасывать лог на диск.
Значение “2″ делает то же самое, только сбрасывает лог не на диск, а в кеш операционной системы. Это значение подойдет в большинстве случаев, т.к. не выполняет дорогой операции записи после каждой транзакции. При этом лог пишется на диск с задержкой в несколько секунд, что весьма безопасно с точки зрения сохранности данных.
Значение “0″ даст наибольшую производительность. В этом случае буфер будет сбрасывать в лог файл независимо от транзакций. Устанавливайте этот параметр в “0″ на свой риск, т.к. в этом случае риск потери данных возрастает.

table_cache

Этот ключ определяет память, выделяюмую для хранения открытых таблиц. Если у Вас несколько сотен таблиц, устанавливайте это значение в 1024. Если же у Вас огромное количество соединений, увеличивайте постепенно это значение, т.к. для каждого соединения храниться отдельная запись.

thread_cache

Этот параметр помагает избежать операций создания/уничтожения потоков при соединении к серверу. Установите этот параметр в 16 и наращивайте по мере потребности. Проверяйте показатель “Threads_created”, идеально он должан быть равным нулю:

Mysql> show status like ‘threads_created’; +-----–+--–+ | Variable_name | Value | +-----–+--–+ | Threads_created | 423312 | +-----–+--–+

query_cache_size

Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Не увлекайтесь установкой огромных значений. Кеш запросов не должен быть большим, т.к. mysql будет съедать ресурсы на управление данными в кеше. Начните с 32М…128М, и увеличивайте по мере необходимости.