Настройка MySQL

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

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

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

• character-set-server – кодировка для всего сервера;
• collation-server – порядок символов и строк на основе алфавитного порядка и классов эквивалентности;
• init-connect – строка, выполняемая для каждого клиента при соединении.

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

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

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

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

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

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

Проверим soft limit:

и hard limit

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

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

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

Буферы

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

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

Кэши

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

Будьте внимательны при установке завышенного значения 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:

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

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

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

В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:

Затем перезапустите:

Оптимизация 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 часть опций требует корректировки:

Обратите внимание, при работе с 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 для хранения различных внутренних структур, устанавливается 16M-32M

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

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

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

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

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

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

 

Источник: https://www.hostcms.ru/documentation/server/mysql/