Настройка my.cnf для стабильной работы Mysql

Один из важных моментов в скорости работы сайта, это правильная оптимизация конфигурационного файла базы данных my.cnf, с которым многие сталкиваются в процессе оптимизации системных ресурсов. Зачастую многие приведенные в сети мануалы рассказывают о настройках которые были применены к слабым конфигурациям и это не дает нужного ответа.

Я обычно использую сервера на уровне ксеонов с 32 гигабайтами памяти, поэтому найти нужную конфигурацию настроек mysql достаточно трудно, отсюда и попытки настроить самому методом тыка и проб. Что из этого получилось сегодня я постараюсь рассказать. 

Исходные данные для настройки

Итак рассматриваем систему с установленным ISP manager на котором стоит Centos и MariaDB. Задача, оптимизировать работу Mysql и ускорить тем самым обработку запросов на сайтах. Для начала я приведу, пример своего my.cnf который находится по адресу etc/my.cnf, если у вас стоит Debian то смотреть надо в папке другой. Итак вот так выглядит настроенный файл, но иногда я все таки еще изменяю некоторые настройки, о которых расскажу ниже.

[mysqld]
#open_files_limit = 2000

local-infile=0
innodb_file_per_table = 1

pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
ignore-db-dir=lost+found
max_allowed_packet = 1024M
skip-external-locking
skip-name-resolve

key_buffer = 2G

key_buffer = 2G

key_cache_division_limit = 70

thread_stack = 192K

tmp_table_size = 2G

max_heap_table_size = 2G

key_buffer_size = 4G

sort_buffer_size    = 1G

read_buffer_size    = 1G

read_rnd_buffer_size = 2G

myisam-recover = BACKUP
max_connections = 500
table-cache = 120000
table-open-cache = 120000

thread-cache-size = 500

thread-cache-size = 500

interactive-timeout = 360

query_cache_limit = 12M

query_cache_size    = 4G

join_buffer_size = 512M

#log_slow_queries = /var/log/mysql/mysql-slow.log

#log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 75#log-queries-not-using-indexes

expire_logs_days = 10

expire_logs_days = 10

max_binlog_size         = 100M

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 4

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = O_DIRECT

symbolic-links=0

bind-address = 127.0.0.1

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

Тюнинг базы данных Mysql варианты

Итак что я меняю и что вижу при этом. Для начала выведу основные параметры которые считаю спорными в настройке.

key_buffer = 2G

key_cache_division_limit = 70

thread_stack = 192K

tmp_table_size = 2G

max_heap_table_size = 2G

key_buffer_size = 4Gsort_buffer_size    = 1G

read_buffer_size    = 1G

read_rnd_buffer_size = 2G
myisam-recover         = BACKUP

max_connections        = 500

table-cache = 120000

table-open-cache = 120000

thread-cache-size = 500

interactive-timeout = 360

query_cache_limit = 12M

query_cache_size    = 4G

join_buffer_size = 512M

Разбор параметров тюнинга Mysql

Разберёмся по порядку с каждым параметром настройки и вопросами которые есть при этом. Итак по пунктам.

key_buffer = 2Gkey_buffer_size = 4G
Так и не смог я понять, различаются ли эти два параметра или первый является устаревшим значением второго. 

max_connections        = 500 и thread-cache-size = 500 
По замерам выходило, что не более 90 одновременных подключений, так и поставил 500 с запасом. Тут следует учесть что следующий параметр thread-cache-size должен быть одинаковым числом с максимальным соединением. Поэтому там также стоит 500.

table-cache = 120000 и table-open-cache = 120000 
Здесь я поставил по 120000, так как таблиц у меня достаточно много, если у вас не много сайтов, то этот параметр можно не повышать.

interactive-timeout = 360
Установил в 360, чтобы снимались запросы, которые находятся без активности 6 минут или 360 секунд.

query_cache_limit = 12Mquery_cache_size    = 4Gjoin_buffer_size = 512M
Следующие три параметра настроил исходя из следующих наблюдений. Пробовал ставить query_cache_size от 2 до 6 гигабайт, в итоге оптимально показалось 4. Обработка запросов до 12 мегабайт мне вполне хватало, поэтому оставил 12. Но есть такое мнение, что большой query_cache_size на самом деле сильно грузит систему и желательно держать кеш в memcashed, на практике я не заметил особо, чтобы он забирал мощность, а вот при проверке кеша, обнаружил, что много запросов проходит через него. 

sort_buffer_size    = 1Gread_buffer_size    = 1Gread_rnd_buffer_size = 2G
Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.

РЕКОМЕНДУЕМ

10 плагинов для всплывающих форм на сайте WordPress 2019, плюсы и...

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

10 распространенных ошибок WordPress и решения для их исправления

Для инструмента, которому принадлежит 50-процентная доля рынка всей системы управления контентом (CMS), WordPress стал собственным стандартом. Но, как и любой инструмент, вы можете время от...

Полный обзор админ панели WordPress: функционал, пункты меню, настройки

Итак, вы только что установили WordPress и вошли в свой новый WordPress через «site.com/wp-admin». Что теперь? Если вы не знакомы с WordPress, ваше первое знакомство...

WP Rocket правильная и оптимальная настройка на сайте для кеширования

WP Rocket называют лидером среди плагинов кеширования для сайтов сделанных на системе управления сайтом Wordpress. Конечно есть некоторые споры какие плагины кеширования лучше и...
Alecs Retin
Работаю в сфере web с 2009 года. Занимался созданием своих проектов в разных тематиках. В основном делаю сайты на Instantcms и Wordpress. Оказываю услуги разработки сайтов и стартапов, инвестирую в интернет проекты и сервисы. Более 10 лет занимаюсь Web разработкой и создание интернет проектов. Интернет магазины, информационные порталы, городские порталы. Оказываю поддержку в развитии и продвижении сайтов.