Настройка 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 + конфиги учитывают максимально настройки устраняющие узкие места в работе базы данных.

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

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

5 + 5 =

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

Оптимизация графики в WordPress, отключаем лишние, сжатие, чистка размеров графики

Графика самый тяжелый элемент сайт Wordpress и поэтому правильный подход к их оптимизации очень важен. Это не сделать одной операцией, особенно если раньше вы не особо обращали на это внимание. Раздувание размеров сайта со временем становится проблемой, решать которую надо. Но как решить оптимально...

WordPress 6 изучаем что было добавлено, улучшения и дополнения

Поздоровайтесь с «Arturo» и новой версией WordPress 6.0, вдохновленными обладателем Грэмми джазовым музыкантом Артуро О'Фарриллом . Известный своим влиянием на современный латиноамериканский джаз, Артуро выпустил более 15 альбомов, охватывающих работу за пять десятилетий. Потратьте некоторое время на изучение WordPress 6.0, созданного, чтобы помочь вам раскрыть...

Нехватки памяти для работы сайта WordPress – увеличиваем память в конфиге сайта

Просматривая журналы работы сайта вы можете увидеть очень частую ошибку нехватки Wordpress. Обычно это этом пишет предупреждение: PHP Warning: Use of undefined constant ‘WP_MEMORY_LIMIT’ - assumed '‘WP_MEMORY_LIMIT’' (this will throw an Error in a future version of PHP) То есть Вы видите ошибку исчерпания...

Google консультирует что запросы и показы в поиске отличаются по цифрам

Адвокат Google по поиску Джон Мюллер говорит, что показы, которые ваш сайт получает по ключевому слову, не являются показателем объема поиска по этому ключевому слову. Об этом говорится в Twitter в ответ на вопрос о том, можно ли использовать данные о показах в Google Search...

Инструкция WordPress cron, запуск, управление, решение ошибок, основные параметры

WP-Cron выполняет определенные задачи для сайтов на WordPress. Название Cron происходит от системы Unix для планирования заданий, от одного раза в минуту до одного раза в год. Будь то плановое обслуживание или запланированные предупреждения, любая команда, которая может быть выполнена в Unix без вмешательства...

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

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