Оптимизация MySQL с помощью настроек в my.cnf

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

Введение

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

Итак, давайте рассмотрим настройки демона [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 рекомендуется увеличить данное значение

sort_buffer_size        = 4M

Размер буфера выделяемого для сортировки 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

Полезная настройка: Изначальная величина буфера запросов. Если большинство запросов большого размера (например, при вставке записей с BLOB), его необходимо увеличить!

query_buffer_size

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

query_cache_limit = 2M

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

query_cache_size        = 16M

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

Значение Объяснение Примечание
0 OFF Не кэширует и не извлекает результаты (по-умолчанию).
1 ON Кэширует все результаты, кроме запросов SELECT SQL_NO_CACHE ....
2 DEMAND Кэширует только запросы SELECT SQL_CACHE ....

query_cache_type  = 2

не забывайте про query_cache_startup_type.

Настройки 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_pool_size = 32M
innodb_file_io_threads          = 8
innodb_lock_wait_timeout        = 50
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT

Битрикс рекомендует:

transaction-isolation   = READ-COMMITTED
default-character-set = utf8

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

[mysqldump]

default-character-set = utf8

[mysql]

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

default-character-set = utf8

Как находить проблемные места

  1. смотрим превышения в /myadmin/server_status.php
  2. смотрим инфу по ним в мане
  3. исправляем конфиги
  4. улучшаем SQL-запросы

И не забываем про разгон: 1 - 2 - 3 - 4

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


08.04.2014 09:01

Комментарии

max_useD_connections   - вроде правильно так
Евгений | 19.02.2016 13:52
Вот все было нормально, пока я не увидел пару строк про битрикс.
Павел | 26.01.2018 21:00