Меню
ГлавнаяMicrosoft

Настройка MYSQL под Linux. Что нужно настроить в 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
  • 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 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, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.

Конфигурационные параметры по умолчанию в 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М, и увеличивайте по мере необходимости.

Начиная с версии 3.22 MySQL может считывать принятые по умолчанию параметры запуска для сервера и клиентов из файлов параметров.

В Unix считывание принятых по умолчанию параметров MySQL производится из следующих файлов:

DATADIR является каталогом данных MySQL (обычно /usr/local/mysql/data для бинарной установки или /usr/local/var для установки из исходных текстов). Обратите внимание, что это тот каталог, который был задан во время настройки, а не указанный при помощи --datadir при запуске mysqld ! (--datadir не оказывает влияния на просмотр файлов параметров сервером, так как их просмотр происходит до обработки аргументов командной строки).

В Windows считывание принятых по умолчанию параметров MySQL производится из следующих файлов:

Обратите внимание на то, что в Windows все пути необходимо указывать при помощи / вместо \. Если необходимо использовать \, то его нужно указать дважды, так как \ является знаком перехода в MySQL.

MySQL пытается прочитать файлы параметров в указанном выше порядке. Если существует несколько таких файлов, то параметр, указанный в файле, идущем позже, имеет преимущество над таким же параметром, указанным в файле, расположенном ранее. Параметры, указанные в командной строке, обладают более высоким приоритетом по отношению к параметрам, указанным в любом из файлов параметров. Некоторые параметры можно задавать при помощи переменных окружения. Параметры, указанные в командной строке или в файлах параметров, обладают преимуществом по отношению к переменным окружения (see Приложение F, Переменные окружения ).

Приводим список программ, поддерживающих файлы параметров: mysql , mysqladmin , mysqld , mysqld_safe , mysql.server , mysqldump , mysqlimport , mysqlshow , mysqlcheck , myisamchk и myisampack .

Любой параметр, который может быть задан в командной строке при запуске программы MySQL, может быть также задан в файле параметров (без предваряющего двойного слэша). Чтобы получить список доступных параметров, следует запустить программу с параметром --help .

Файлы параметров могут содержать строки следующего вида:

    Строки комментариев начинаются с символа " # " или " ; ". Пустые строки игнорируются.

    группа представляет собой имя программы или группы, для которой необходимо задать параметры. Любые параметры или строки, задающие значения переменных, которые указаны после строки группы, будут относиться к названной группе, пока не закончится файл параметров или же не будет указана другая строка группы.

    Эквивалент --option в командной строке.

    Эквивалент --option=value в командной строке.

    set-variable = variable=value

    Эквивалент --set-variable variable=value в командной строке. Данный синтаксис необходимо использовать для задания переменных mysqld . Заметьте, --set-variable не используется с MySQL 4.0. Просто используйте --variable=value .

Группа client обеспечивает возможность задавать параметры, относящиеся ко всем клиентам MySQL (кроме самого mysqld). Эта группа великолепно подходит для указания пароля, используемого при подсоединении к серверу (но при этом следует убедиться, что разрешение на чтение и запись этого файла есть только у вас).

Обратите внимание на то, что для параметров и значений все введенные перед ними и после них пробелы автоматически удаляются. В строках значений можно использовать такие экранирующие секвенции: " \b ", " \t ", " \n ", " \r ", " \\ " и " \s " (" \s " - это пробел).

Пример типичного глобального файла параметров:

Port=3306 socket=/tmp/mysql.sock port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M quick

Пример типичного файла параметров пользователя:

# Указанный пароль будет направлен всем стандартным клиентам MySQL password=my_password no-auto-rehash set-variable = connect_timeout=2 interactive-timeout

Если у вас дистрибутив исходного кода, то примеры конфигурационных файлов с именами my-xxxx.cnf можно найти в каталоге support-files . В случае бинарного дистрибутива следует обратиться к каталогу DIR/support-files , где DIR - имя каталога установки MySQL (обычно /usr/local/mysql). На данный момент там приведены примеры файлов конфигурации для малых, средних, больших и очень больших систем. Чтобы поэкспериментировать с файлом, можно скопировать my-xxxx.cnf в свой домашний каталог (переименуйте копию в.my.cnf).

Все поддерживающие файлы параметров клиенты MySQL принимают следующие параметры:

Обратите внимание на то, что указанные выше параметры должны идти первыми в командной строке! Однако параметр --print-defaults может использоваться сразу после команд --defaults-xxx-file .

Примечание для разработчиков: обработка файла параметров происходит следующим образом: все совпадающие параметры (т.е. параметры в соответствующих группах) обрабатываются перед любыми аргументами командной строки. Этот алгоритм хорошо подходит для программ, которые в случае, если один и тот же параметр указывается несколько раз, используют последний экземпляр параметра. Если же вы работаете со старой программой, которая считывает заданные несколько раз параметры указанным образом, но не читает файлы параметров, необходимо добавить только две строки, чтобы у нее появилась эта возможность. Чтобы увидеть, как это делается, достаточно ознакомиться с исходным кодом любого стандартного клиента MySQL.

Не знаю почему, но по умолчанию настройки MySQL рассчитаны на десктопы 90-х годов. Например, 8Mb памяти под индексы InnoDB. Помните, как Билл Гейтс заявил, что «640 Кб памяти должно хватать каждому». Дефолтные настройки MySQL из этой серии.

Для начала моя выжимка из конфига (4G RAM, AMD Athlon 64 X2 Dual 5600+)

# ТОЛЬКО UTF! ТОЛЬКО ХАРДКОР! collation_server=utf8_general_ci character_set_server=utf8 default-character-set = utf8 # по умолчанию пускай будет InnoDB default-storage-engine = InnoDB key_buffer_size = 512M innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 16M innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_thread_concurrency = 8 join_buffer_size = 8M sort_buffer_size = 8M read_rnd_buffer_size = 8M tmp_table_size = 64M max_heap_table_size = 32M table_cache = 256 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 query_cache_type = 2 query_cache_limit = 1M query_cache_size = 32M

Как посмотреть что с базой что-то не в порядке
Самый быстрый вариант зайти в phpMyAdmin во вкладку «Текущее состояние MySQL»
Там вы увидите подсказки, какие настройки нужно подкрутить.

Сами настройки можно посмотреть в том же phpMyAdmin во вкладке «Системные переменные».

У MySQL есть несколько настроек, с помощью которых можно разогнать базу до первой космической. Во-первых, настройки по хранению индексов в памяти. Мало того, что индексы значительно ускоряют выборки, но если их хранить в памяти, а не на диске (где они обычно лежат), то профит будет значительным.

key_buffer_size = 512M
Таким образом выделяем 512 Mb под индексы таблиц MyISAM. Дело в том, что у меня половина баз в MyISAM (так исторически сложилось). На 99,9% эти базы используются на чтение, так что переходить на InnoDB смысла нет.

innodb_buffer_pool_size = 512M
Такой же объем памяти выделяем на таблицы InnoDB.
Тут нужно знать меру. Если у вас 1 база размером 100 Mb, то нет смысла выделять 1 Гб памяти – она всё равно не будет использована.
Во-вторых, нужно смотреть не на размер таблицы, а на размер индексов. Пример из жизни: таблица 300 000 комментариев весит 300 Мб, а ее индексы занимают в 15 раз меньше, что вполне логично, так как обычно индексы расставляются на числовые и временные столбцы, а не на текст. Посмотреть это опять же можно в phpMyAdmin

innodb_additional_mem_pool_size = 16M
Размер памяти, выделяемый InnoDB для хранения различных внутренних структур.

innodb_flush_method = O_DIRECT
Тут мы вырубаем буферизацию таблиц для файловой системы и говорим MySQL обращаться к файлам напрямую.

innodb_flush_log_at_trx_commit = 2
При каждой транзакции MySQL пишет лог и сбрасывает на диск (значение 1). Значение 2 – сбрасываем в память. Мне не критично потерять транзакции за последние 2 секунды в случае падения сервера.

join_buffer_size = 8M
Память для запросов с джойнами, когда объединение происходит без использования индексов.

sort_buffer_size = 8M
read_rnd_buffer_size = 8M
Полезно для запросов с сортировкой ORDER BY и группировкой GROUP BY. При малом значении сортировка идет во временной таблице на диске.

tmp_table_size = 64M
max_heap_table_size = 32M
Настройки для хранения временных таблиц в памяти. Временные таблицы часто образуются при больших джойнах.

table_cache = 256
Максимальное число одновременно открытых таблиц.

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1