Выдана 102251 лицензия

Настройка MySQL

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

Указание кодировок и collation

В секцию [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

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

*         hard    nofile      35000
*         soft    nofile      35000
root      hard    nofile      35000
root      soft    nofile      35000

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

ulimit -n 35000

Проверим soft limit:

ulimit -Sn

и hard limit

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, т.к. это может привести к ожиданию блокировок (Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache). Мы не рекомендуем устанавливать значение больше 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