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

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

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

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

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

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

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

Разбор параметров тюнинга 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
Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.

Новые изменения настройки my.cnf в 2021 году

Времена идут, знания становятся лучше, поэтому я уже практически во многом перенастроил свои файлы конфигов базы данных. Сразу скажу, что в основном этот конфиг рассчитан по моим базам данных, это порядка 14 гигабайт данных на серверах с 32 памяти оперативной, ssd дисками и собственно выкручено все на соотношение скорость работы + стабильность. Поэтому вот такой конфиг сейчас использую на Centos 8 с указанными параметрами серверов.

В принципе производительность и уровень работы меня устраивает. Если у вас есть вопросы или конфигурация сервера другая, желательно написать все таки вопрос, я помогу разобраться какие параметры следует учесть. Из основного: сделан упор на работу Innodb + конфиги учитывают максимально настройки устраняющие узкие места в работе базы данных.

ОСТАВЬТЕ ОТВЕТ

Пожалуйста, введите ваш комментарий!
пожалуйста, введите ваше имя здесь

98 − = 93

OK2WEB
OK2WEB
Более 12 лет занимаюсь разработкой и продвижением сайтов. Городские порталы, информационные и новостные сайты. Сейчас в основном разрабатываю интернет магазины и коммерческие сайты. Если у вас есть вопросы пишите, стараюсь оперативно ответить и помочь.

Как использовать частный адрес Wi-Fi на iPhone и iPad

Последняя мобильная операционная система Apple предоставляет новую функцию безопасности, называемую частным адресом . Это помогает защитить ваши личные данные в общедоступных или незнакомых сетях Wi-Fi. У каждого устройства есть собственный встроенный адрес. Эта новая функция случайным образом создает новый адрес, поэтому ваше устройство с меньшей вероятностью будет идентифицировано злоумышленниками. Это...

Учимся делать и управлять title и description для страниц сайта плагином Seopress (уроки SEO)

Из всей когорты seo плагинов Wordpress, которых достаточно много  я обычно выделяю SEOpress и работаю больше с ним. Из плюсов которые я выделяю для себя: очень понятный интерфейс охватывающий все детали seo оптимизации сайта, удобные настройки, возможность кастомизации. В общем для вас рекомендую также....

Проблема обновления Centos и Docker, что делать если не обновляется

На одном из серверов у себя уже месяца 4 не мог произвести обновления, возникал конфликт Docker с Centos. Плюс к этому надо прибавить установленный ISP manager и сайты на двух базах, одна из них основная Mysql 8 и вторая как раз таки в контейнере...

Как оптимизировать свой сайт для показателей Google Core Web Vitals

Контент остается решающим. Но если вы сравните два сайта с одинаковым текстом и популярностью, то тот, который предлагает лучший веб-опыт, получит более высокий приоритет в результатах поиска Google. Помимо улучшенного рейтинга страницы, высокопроизводительные сайты могут быть включены в карусель мобильного поиска. Ранее это было зарезервировано для ускоренных мобильных...

Core Web Vitals и WordPress SEO, скорость сайта как фактор ранжирования

В мае 2020 года Google объявил, что показатели качества страниц под названием «Core Web Vitals» станут фактором ранжирования в поиске Google. Это было наконец выпущено как часть обновления опыта страницы в июне 2021 года. Многие владельцы веб-сайтов упорно работают над улучшением своих показателей Core Web Vitals...

Ошибка добавления записей и постов в WordPress, решаем проблему с базой данных

Работая с одним из проектов заказчика столкнулся с ошибкой добавления постов. Они просто не добавлялись, залез в базу там висят несколько постов без номеров и перекрывают возможность добавлять новые. Ошибка Начал ковырять и вот как получилось исправить данную проблему Сделал резервную копию с помощью опции...