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

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

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

СЕГОДНЯ ЧИТАЮТ

Ускорение сайта на WordPress – разгоняем максимум скорости

Итак решил немного заняться ускорением сайта на примере ok2web.ru. Для начала я сниму несколько показателей, которые определяют его скорость, далее проведу некоторые эксперименты по...

Как определить задействованные файлы WordPress на странице сайта

Часто бывает что при работе с сайтом надо понимать структуру файлов, задействованных в формировании страницы со стороны Wordpress. Это вроде бы простая операция, однако...

Использование WP-CLI, для импорта и экспорта меню с WordPress

Экспорт меню может быть очень сложно в WordPress. Каждый раз, когда я пробовал с плагинами что-то казалось, никогда не переносятся, а мне придется воссоздать...

Плагин для сайтов туризма WP TripAdvisor Review Slider

Показывайте обзоры TripAdvisor на своем сайте с помощью шорткода или виджета! Посмотрим правде в глаза, никто на самом деле не доверяет отзывам, если они...

Оптимизация картинок и графики на сайте и сервере с помощью скрипта

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

Секреты и навыки работы с файлами Excel для web мастера

В своей работе web мастеру приходится использовать различные программы, которые работают с файлами, например notepad +++ и MS Office Excel. Зачастую приходится выполнять различные...

ПОХОЖИЕ МАТЕРИАЛЫ

Хак Woocommerce для отображения цену в руб и грв

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

BulletProof Security плагин WordPress: безопасность, сканер, файрвол

Защита безопасности сайта на WordPress: Сканер вредоносного кода, Файрвол, защита формы авторизации, создание резервных копий базы данных, анти-спам и многое другое. Смотрите раздел Основные...

BuddyPress плагин для создания социальной сети на WordPress (профили, группы)

Вы ищете современный, надежный и утонченный движок социальной сети? BuddyPress — это набор компонентов, составляющих социальную сеть, с отличными дополнениями из обширной библиотеки плагинов...

Age Gate плагин WordPress для создания ограничения по возрасту при входу на сайт

Существует много применения для ограничения контента по возрасту, такие как трейлеры фильмов, алкоголь или другие «взрослые» темы. Данный плагин позволяет установить возрастное ограничение на...

Simple Masonry Gallery простой плагин для организации галереи Masonry на сайте

Добавьте эффект Masonry к изображению. Поддерживает блок и шорткод. Добавьте эффект к изображениям, вставляемым в запись WordPress, пользовательские записи и страницы. Добавляет эффект в...

Direct Checkout for WooCommerce плагин для оптимизации оформления товаров Woocommerce

WooCommerce Direct Checkout - это плагин, позволяющий сократить количество шагов в процессе оформления заказа Woocommerce. Вы можете упростить процесс оформления заказа, пропустив страницу корзины...