Tmp table size где находится. Оптимальная настройка MySQL сервера

Но это лишь маленькие игрушки гиков, которые мечтают покрыть связью всю планету. Их амбициозные сервисы станут крохотным дополнением к мощной базовой инфраструктуре Всемирной сети - разветвлённой сети наземных и подводных магистральных каналов. Вот где настоящая кровеносная система современной цивилизации. Именно здесь бьётся её пульс.

Крупнейшие хабы

На физическом уровне интернет представляет сеть хабов (точек обмена трафиком), связанных магистральными каналами. В точках обмена трафиком концентрируется не только трафик, но и сетевая инфраструктура (дата-центры, хостинг и т.д). Крупнейшие точки обмена находятся во Франкфурте, Амстердаме, Лондоне и Париже. В каком-то смысле эти города можно считать столицами мирового интернета. По крайней мере, точно крупнейшими сетевыми узлами, вместе с Нью-Йорком, который тоже входит в пятёрку основных хабов.

По данным на 2014 год , по дну океана проложено 285 кабелей связи, из них 22 не использовались, это так называемые «тёмные кабели» («тёмное оптоловокно») - такие неиспользуемые кабели в большом количестве есть и на суше. Например, та же компания Google скупает тёмное оптоволокно для связи между дата-центрами. Когда по тёмному оптоволокну пускают сигнал, говорят, что его «зажгли», как лампу.

Расчётный срок службы оптоволокна составляет 25 лет - это чисто теоретическая величина. Предполагается, что в течение такого времени коммерческая эксплуатация канала будет иметь смысл. Соответственно, исходя из такого срока экономисты рассчитывают окупаемость инвестиций. Например, для компании Google выгоднее проложить собственный кабель через Тихий океан, чем 25 лет арендовать чужой.

По мере роста трафика в интернете (он растёт примерно на 37% в год) операторы производят апгрейд оптоволокна - «уплотняют» его, чтобы передавать данные одновременно в нескольких спектральных каналах за счёт спектрального уплотнения . Кроме того, внедряются более эффективные техники фазовой модуляции и устанавливается более современное оконечное оборудование. Соответственно, пропускная способность магистрального канала увеличивается пропорционально полосе частот, на которых передаются данные.

Хорошей иллюстрацией является трансатлантическая информационная магистраль. В 2003-2014 годы здесь не было проложено ни одного (!) нового кабеля, зато пропускная способность действующих каналов увеличилась в 2,4 раза исключительно за счёт уплотнения каналов и апгрейда оборудования. И у этих кабелей ещё остался большой запас на будущее.

Увеличение пропускной способности трансатлантических каналов связи в 2003-2014 годы

Прокладка нового кабеля и ввод его в эксплуатацию - длительная процедура, которая продолжается несколько лет, и довольно дорогостоящая, поэтому несколько корпораций обычно сообща финансируют такие проекты, а потом делят между собой оптоволоконные пары в кабеле. Например, 29 июня 2016 года компания Google с партнёрами (China Mobile International, China Telecom Global, Global Transit, KDDI, Singtel) объявили о вводе в эксплуатацию крупнейшего подводного кабеля в мире - транстихоокеанского кабеля FASTER на 60 Тбит/с . Кабель длиной 9000 км связал Японию и США (здесь Япония выполняет роль хаба между США и Китаем).

FASTER

Этот конкретный кабель состоит из 6 оптоволоконных пар. Каждая пара способна передавать сигнал в 100 диапазонах длины волны по 100 Гбит/с на каждую длину (10 Тбит/с на каждую оптоволоконную пару). Это соответствует 60 Тбит/с максимальной пропускной способности для каждого кабеля - это не теоретическая, а реальная максимальная пропускная способность, продемонстрированная в тестах.

Но в первое время пропускная способность даже близко не приблизится к этому пределу. На первом этапе будут задействованы всего лишь от 2 до 10 каналов, то есть 2-10% максимальной пропускной способности кабеля. В течение 25-летнего срока эксплуатации Google с партнёрами будут постепенно увеличивать его пропускную способность, по мере необходимости.

Google принадлежит один или два из шести оптоволоконных пар в кабеле, точная информация держится в секрете. Хотя стоимость прокладки магистрали FASTER составила $300 млн, для интернет-компании это действительно дешевле, чем арендовать такие же каналы у других. Кроме того, так Google получает больший контроль над линиями связи, которые связывают её дата-центры.

Кстати, Microsoft и Facebook по примеру Google сейчас тоже формируют консорциум для прокладки своего трансатлантического кабеля MAREA.

Сети в Европе

Если магистральные каналы связи сравнить с кровеносной системой современной цивилизации, то Европа - её сердце.

Карта магистральных каналов в Европе с каждым годом немного изменяется. Между крупнейшими узлами сети иногда прокладываются новые каналы с большей пропускной способностью и/или меньшей задержкой (то есть по более оптимальному маршруту). В некоторых случаях каналы могут вообще «пропадать», то есть их перестают использовать, если оператор по какой-то причине решит перенаправить линк от одного города к другому. В начале 2000-х крупнейшим международным каналом связи в мире был трансатлантический маршрут Нью-Йорк–Лондон, но в 2009 году проложили более толстый канал Амстердам–Лондон, а затем и этот рекорд был побит новым «чемпионом» - трассой Франкфурт–Париж.

Примерно в это время сформировалась окончательная структура сетевых магистралей в Европе с четырьмя крупнейшими в мире точками обмена трафиком.

1. Франкфурт
2. Лондон
3. Париж
4. Амстердам

По мировой статистике , всего лишь около 25% самых популярных сайтов каждой страны размещаются у себя на родине (в среднем). Доля национального хостинга заметно выше в Китае, Иране, Турции и России, по понятным причинам.

Физическое местоположение серверов 100 самых популярных сайтов в некоторых странах, апрель 2015 год.

Настройка MySQL сводится, в основном, к редактированию главного конфигурационного файла (/etc/my.cnf в FreeBSD). Перед настройкой следует учесть, что в MySQL 5.6 названия некоторых параметров и их наличие отличается от тех, которые использовались в предидущих версиях.

MySQL 5.6 - конфигурирование my.cnf

Для того, чтобы изменения в файле my.cnf вступили в силу, необходимо перезагрузить сервер MySQL:

/usr/local/etc/rc.d/mysqld restart

Проверить, восприняты ли новые настройки сервером, можно с помощью запроса к БД:

mysql> SHOW WARIABLES;

Чтобы просмотреть только определенные настройки, нужно конкретизировать запрос. Например, чтобы увидеть параметр max_connections нужно отправить в MySQL такой запрос: mysql> SHOW VARIABLES LIKE "max_ conn% " ;

Если после перезагрузки, изменения применились частично или не воспринимаются сервером MySQL, проверьте, возможно отредактирован не тот файл или MySQL дополнительно подгружает другой конфигурационный файл, директивы которого переназначают измененные вами параметры. Например, при установке панели управления хостингом DirectAdmin, сервер MySQL устанавливается автоматически и содержит 2 конфигурационных файла: /etc/my.cnf и дополнительно подгружаемый /usr/local/mysql/my.cnf. Изменяя параметр sql_mode в /etc/my.cnf я долго не мог понять, почему он не применяется к в MySQL сервере, как оказалось, он переопределялся в /usr/local/mysql/my.cnf (FreeBSD) или /usr/my.cnf (CentOS). Как найти список всех файлов my.cnf использующихся в MySQL можно посмотреть, введя запрос в поисковой системе: "my.cnf location".

Полный список настроек, которые используются в my.cnf можно посмотреть в официальном руководстве пользователя MySQL (eng), в колонке Option File.

Настройки в разделе

local_infile

Этой переменной можно разрешить (ON или 1 - по умолчанию) или запретить (OFF или 0) использовать LOCAL в запросе LOAD DATA. Если вы не знаете точно что это и зачем нужно, настоятельно рекомендуется переключить local_infile в OFF (local_infile=OFF ) из соображения безопасности сервера в целом.

skip_external_locking

skip_external_locking - параметр отвечающий за внешнюю блокировку файлов баз данных типа MyISAM (по умолчанию установлен в ON - блокировка включена). Рекомендуется не менять этот параметр из соображений быстродействия сервера MySQL.

skip_name_resolve

Если параметр skip_name_resolve установлен в ON или 1 (skip_name_resolve=OFF - по умолчанию), то при внешнем подключении к MySQL сервер пытается перевести название домена в IP-адрес, что заметно снижает скорость обработки запроса. Для повышения быстродействия, рекомендуется установить skip_name_resolve в OFF, в этом случае в качестве хоста при подключении к MySQL можно будет использовать только IP-адрес или localhost.

low_priority_updates

По умолчанию, такие операторы MySQL как INSERT, REPLACE, UPDATE, DELETE имеют более высокий приоритет, чем, например, SELECT, и параметр low_priority_updates, соответственно, установлен в OFF. Если Ваш сервер больше посылает запросов на чтение, чем изменение данных таблиц, можно установить low_priority_updates в ON. Следует отметить, что low_priority_updates применяется только к типам таблиц MyISAM, MEMORY и MERGE.

sql_mode

От параметров, указанных в sql_mode сильно зависит работа сервера MySQL. Не правильное указание настроек может полностью остановить работу сайта, использующего MySQL привести к вставке некорректных параметров в БД и другим проблемам. Подробнее об sql_mod можно прочитать тут: , Server SQL Modes 5.6 (eng) .

По умолчанию, в MySQL 5.6.6 и более поздних версиях значение sql_mode установлено в NO_ENGINE_SUBSTITUTION (sql_mode=NO_ENGINE_SUBSTITUTION ), что будет достаточно для большинства сайтов, но все же для понимания работы MySQL следует знать и о других способах работы MySQL, задаваемых в sql_mode.

max_connections

Этот параметр отвечает за максимально-допустимое кол-во одновременных подключений к MySQL. По умолчанию его значение равно 151 и может быть изменено в пределах от 1 до 100000. Увеличивать это значение следует, если появляется ошибка "Too many connections" или администратор уверен, что значения по умолчанию будет не достаточно.

query_cache_type

Значение query_cache_type включает (ON) или выключает (OFF) кеширование запросов. Кеширование - хороший способ снизить нагрузку, если сервер обрабатывает много одинаковых запросов. Использовать query_cache_type следует практически всегда, за исключением случаев, когда запросы MySQL кеширует memcached.

query_cache_size

Размер кеша запросов MySQL. Значение можно записать в Mb - query_cache_size=32M .

Настройки для таблиц MyISAM

key_buffer_size

Если используются только таблицы MyISAM , размер буфера следует установить в размере около 30-35% от размера доступной оперативной памяти. Если же MyISAM-таблиц очень мало или нет совсем, то key_buffer_size можно установить значение 32 МБ, место будет использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске. Выбор объема памяти для key_buffer_size зависит от размеров индексов, данных и нагрузки на сервер. Следует знать, что MyISAM использует кэш операционной системы, чтобы хранить там данные, поэтому нужно оставить достаточно места в ОЗУ под них. Данные могут занимать значительно больше места, чем индексы. Однако стоит проверить, что вся память, указанная в key_buffer_size под кэш, постоянно используется, иначе это будет расходование ресурсов в никуда.

Настройки для таблиц InnoDB

innodb_buffer_pool_size

innodb_buffer_pool_size - размер буфера таблиц InnoDB. Таблицы типа InnoDB используют свой буфер для хранения индексов и данных, поэтому нет необходимости оставлять память под кэш операционной системы, устанавливайте innodb_buffer_pool_size в 75% доступной оперативной памяти, если планируется использовать только таблицы с типом InnoDB. Рекомендации по максимальному размеру данной опции аналогичны key_buffer_size для MyISAM: не стоит устанавливать максимальный размер, нужно найти оптимальный вариант, а доступной ОЗУ можно найти применение и в других задачах.

Дефолтные конфигурационные параметры в Mysql рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.

Настройка некоторых параметров может повысить производительность базы данных в сотни раз!

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

innodb_buffer_pool_size

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

Innodb_buffer_pool_size = 24G

# При том, что на нашем сервере 32Гб оперативной памяти

innodb_log_file_size

Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:

Innodb_log_file_size = 512M

# Так два файла дадут размер лога в 2x512M = 1G

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

innodb_log_buffer_size

Это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT.

Innodb_log_buffer_size = 2M

# Значения по умолчанию в 1М должно быть достаточно для большинства случаев

innodb_file_per_table

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

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

innodb_file_per_table = ON

# С версии 5.6 этот параметр включен по умолчанию

innodb_flush_method

Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT :

Innodb_flush_method = O_DSYNC

# Помните об обязательном использовании резервных узлов (например, реплик)

innodb_flush_log_at_trx_commit

Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).

Тут следует руководствоваться такой логикой:

• innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
• innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.

Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:

Innodb_flush_log_at_trx_commit = 2

# Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных

query_cache_size

Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных:

Query_cache_size = 0

# Однако убедитесь, что используете индексы для обеспечения высокой скорости работы запросов

max_connections

Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки "Too many connections" , эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:

Max_connections = 256

# Поднимайте значение постепенно при появлении ошибок соединений

TL;DR

Настройки по умолчанию скорее всего не подойдут. Поэтому обязательно стоит пройтись по указанным параметрам в статье и подобрать для них значения. Если совсем лень — .

• Перевод

Вольный перевод довольно старой статьи с 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, преимущественно те, которые влияют на производительность. Для удобства все переменные разделены по разделам (базовые настройки, ограничения, настройки потоки, кэширование запросов, тайминги, буферы, 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 дает советы по тюнингу тех или иных переменных в зависимости от параметров работы сервера.

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