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

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

Отправить комментарий?
Напишите ваше имя