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

Введение

Как я успел разобраться, при каждом соединении с 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, преимущественно те, которые влияют на производительность. Для удобства все переменные разделены по разделам (базовые настройки, ограничения, настройки потоки, кэширование запросов, тайминги, буферы, 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 существует 2 основных движка: InnoDB и MyISAM . Таблицы баз данных обоих типов могут существовать на одном сервере. При необходимости их можно конвертировать, использование MyISAM оправдано при преобладающем количестве операций с данными одного вида: например SELECT или INSERT.

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

Любой тюнинг MySQL — любая тонкая настройка должна начинаться с определения преобладающего количества таблиц определенного типа.

Определение типа таблиц в MySQL

Делается это при помощи запроса вида (для innodb):

WHERE engine = "innodb";

Или (для myisam):

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = "myisam";

Стоит выполнить оба запроса и оценить количество таблиц, относящихся к каждому движку.

Конфигурация MySQL в my.cnf

Ниже рассматриваются основные опции при конфигурации MySQL с преимущественным использованием InnoDB .

Все дальнейшие модификации производятся в конфигурационном файле /etc/mysql/my.cnf

innodb_buffer_pool_size — размер буфера под InnoDB таблицы и индексы. При преобладании InnoDB таблиц стоит устанавливать значение равным 80% общего количества ОЗУ (8 Гб для сервера с 10 Гб RAM является нормой). Для более мощных серверов данное значение можно увеличивать еще вплоть до 95% доступной RAM.

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

innodb_buffer_pool_size in Gb + CPUs)/2

innodb_flush_log_at_trx_commit - значение устанавливается в 0, 1, 2. 0 означает, что лог сбрасывается на диск раз в секунду, вне зависимости от транзакций. При 1 лог сбрасывается при каждой завершенной транзакции. 2 — лог хранится в ОЗУ. Быстрее всего сервер баз данных будет работать при 0.

innodb_log_buffer_size - размер буфера лога 1-8 Мб являются хорошими значениями

innodb_log_file_size - максимальный размер каждого лог-файла. Можно увеличивать значение, это даст большую производительность поскольку не нужно будет ротировать лог слишком часто, открывать и закрывать новые файлы.

Общие параметры (не имеющие отношения к типу движка)

max_connections=2000 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.

key_buffer=1024M — размер буфера под индексы в оперативной памяти. Оптимальное значение 20-25% доступной RAM. Если значение параметра слишком маленькое — данные начнут писаться в SWAP, что снизит скорость работы в разы.

Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01

Узнать значения параметров можно выполнив в консоли сервера баз данных запрос

SHOW STATUS LIKE «Key%»;

Также в выводе будут значения Key_write_requests и Key_writes

table_cache=2048 – максимальное число открытых таблиц для всех потоков.

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

Чтобы выявить необходимое значение нужно выполнить запрос

SHOW STATUS LIKE «Opened_tables%»;

Затем установить значение переменной несколько больше значения в выводе:
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+

open_files_limit = 2048

Утсанавливать значение стоит опирясь на существующее количество открытых файлов MySQL

В конфигурационном файле задается большее значение.

sort_buffer=128M – ускоряет запросы ORDER BY или GROUP BY. Устанавливается не очень большим (как правило — в зависимости от среды)

Выделяемое количество ОЗУ будет определяться как значение sort_buffer, умноженное на Threads_running

SHOW STATUS LIKE «Threads_running%»;

record_buffer=720M — хорошим значеинем будет эквивалент sort_buffer умноженный на 4-6.

query_cache_limit=2M – максимальный размер результата выборки (или другого запроса), который будет кэшироваться. Значение можно увеличивать, по умолчанию установлен 1 Мб.

max_join_size=1000000 — максимальное количество записей, которые могут обрабатываться единовременно. Своего рода «защита от дупака» при использовании JOIN.

thread_cache_size=64 — задает колчество потоков обработки данных в кэше, т.е. количество потоков которые не создаются вновь при каждом запросе. Можно увеличивать параметр, это положительно скажется на быстродействии

Не знаю почему, но по умолчанию настройки 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

Настройка 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: не стоит устанавливать максимальный размер, нужно найти оптимальный вариант, а доступной ОЗУ можно найти применение и в других задачах.