Настройка MySQL

Сегодня выкладываю основные параметры для конфигурирования работы MySQL-сервера, информация будет полезна владельцам VPS/VDS серверов или любых других при наличии root-доступа к настройкам. Данные настройки пригодятся для высоконагруженных сайтов и интернет-магазинов с очень большим количеством товаров.

Основное настройки конфигурации MySQL осуществляется с помощью файла /etc/my.cnf или /etc/mysql/my.cnf

В секцию [mysqld] добавляем строки:

character-set-server=utf8

collation-server=utf8_general_ci

init-connect="SET NAMES utf8"

character-set-server - кодировка для всего сервера;

collation-server - порядок символов и строк на основе алфавитного порядка и классов эквивалентности;

init-connect - строка, выполняемая для каждого клиента при соединении.

 

Ограничение количества, таймауты и источники соединений

bind-address=localhost

Отключаем определение доменного имени для IP-адресов

skip-name-resolve

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

max_connections = 250

Отключаем использованием symbolic-links

symbolic-links=0

Таймауты

interactive_timeout=60

wait_timeout=60

 

Увеличение числа открытых файлов

В большинстве Linix-систем по умолчанию лимит открытия файловых дескрипторов установлен в 1024, но этого недостаточно.

Проверяем текущие опции:

ulimit -n
/p>

Внесем требуемые лимиты в /etc/security/limits.conf

*         hard    nofile      35000

*         soft    nofile      35000

root      hard    nofile      35000

root      soft    nofile      35000

 

Динамически изменим текущие лимиты:

ulimit -n 35000

ulimit -Sn

ulimit -Hn

 

Текущие лимиты в MySQL проверим SQL-запросом:

SHOW VARIABLES LIKE '%open_files%'

innodb_open_files 2048

open_files_limit 35000

 

Оптимизация MySQL для MyISAM

Оптимизация параметров MySQL позволяет значительно увеличить производительность MyISAM.

Буферы

Основными параметрами являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer (буфер для сортировки).

key_buffer_size = 64M

sort_buffer_size = 32M

При наличии 16Гб памяти и более, рекомендуется увеличить key_buffer_size до 128M-256M. Если не используются MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.

Кэши

Кэш запросов указывается в опции query_cache_size, ограничение на кэшируемый элемент в query_cache_limit, кэш открытых таблиц в table_open_cache.

table_open_cache = 2048

query_cache_limit = 2M

query_cache_size = 128M

query_cache_type = 1

thread_cache_size = 16

max_heap_table_size = 128M

tmp_table_size = 128M

Осторожнее при установке завышенного значения query_cache_size, т.к. это может привести к ожиданию блокировок. Не рекомендуется устанавливать значение больше 256M.

Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках. При наличии 32Гб памяти и более рекомендуем увеличить thread_cache_size до 32, table_open_cache в диапазон 4096-8192, query_cache_size до 256M.

 

Перенос временных файлов MySQL в память

Проверяем наличие /dev/shm:

df -h

Настройки размещаются в /etc/fstab, рекомендуем указать размер, например, 1G:

none      /dev/shm        tmpfs   defaults,size=1G        0 0

Если внесли изменения, то перемонтируем:

mount -o remount /dev/shm

В конфигурационном файле указываем:

tmpdir = /dev/shm

Оптимизация MySQL для InnoDB

Стандартно все таблицы и индексы хранятся в одном файле, рекомендуется использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Дополнительно необходимо корректно рассчитать параметр innodb_open_files, до версии MySQL 5.6.6 он устанавливался в значение 300, с версии MySQL 5.6.6 рассчитывается автоматически и имеет значение по умолчанию -1.

Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуется устанавливать обе опции в 4096 или 8192.

innodb_file_per_table = 1

table_open_cache = 4096

innodb_open_files = 4096

При использовании только InnoDB часть опций требует корректировки:

key_buffer_size = 32M

max_allowed_packet = 1M

sort_buffer_size = 32M

read_buffer_size = 256K

read_rnd_buffer_size = 1M

thread_stack = 128K

query_cache_limit = 1M

query_cache_size = 0

query_cache_type = 1

thread_cache_size = 32

max_heap_table_size = 128M

tmp_table_size = 128M

Обратите внимание, при работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных.

 

Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу "чем больше, тем лучше". Рекомендуется выделять до 70-80% оперативной памяти сервера под innodb_buffer_pool_size. Необходимо провести расчет использования памяти Apache и дополнительным программным обеспечением для исключения сваливания системы в swap. Для сервера с 16Гб устанавливается в диапазоне 10-12G и разделяем его на 4 секции, для сервера с 32Гб устанавливаем значение в диапазоне 20-22G и разделяем его на 8 секций:

innodb_buffer_pool_size = 10G

innodb_buffer_pool_instances = 4

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

innodb_additional_mem_pool_size = 20M

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

innodb_log_file_size = 128M

innodb_log_buffer_size = 16M

При установке значения в 2 буфер не сбрасывается на диск, а только в кэш операционной системы. Установка значения в 0 увеличит быстродействие, однако возможна потеря последних данных при аварийном выключении mysql-сервера.

innodb_flush_log_at_trx_commit = 0

Количество потоков ввода/вывода файлов в InnoDB задается опцией innodb_file_io_threads, обычно этому параметру присваивается значение 4 или 8.

innodb_file_io_threads = 8

С версии MySQL 5.5 разделено на 2 опции:

innodb_read_io_threads = 8

innodb_write_io_threads = 8

Для ускорения работы с INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключаем обновление статистики при выполнении таких операций, с версии MySQL 5.6.6 innodb_stats_on_metadata отключено по умолчанию:

innodb_stats_on_metadata = 0

После применения изменений перезагружаем MySQL:

service mysqld restart

Tags: MySQL, Производительность

 

Хотите поработать с нами?
Пишите или звоните!