Один из важных моментов в скорости работы сайта, это правильная оптимизация конфигурационного файла базы данных my.cnf, с которым многие сталкиваются в процессе оптимизации системных ресурсов. Зачастую многие приведенные в сети мануалы рассказывают о настройках которые были применены к слабым конфигурациям и это не дает нужного ответа.
Содержание
Я обычно использую сервера на уровне ксеонов с 32 гигабайтами памяти, поэтому найти нужную конфигурацию настроек mysql достаточно трудно, отсюда и попытки настроить самому методом тыка и проб. Что из этого получилось сегодня я постараюсь рассказать.
Исходные данные для настройки
Итак рассматриваем систему с установленным ISP manager на котором стоит Centos и MariaDB. Задача, оптимизировать работу Mysql и ускорить тем самым обработку запросов на сайтах. Для начала я приведу, пример своего my.cnf который находится по адресу etc/my.cnf, если у вас стоит Debian то смотреть надо в папке другой. Итак вот так выглядит настроенный файл, но иногда я все таки еще изменяю некоторые настройки, о которых расскажу ниже.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
[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_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 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 варианты
Итак что я меняю и что вижу при этом. Для начала выведу основные параметры которые считаю спорными в настройке.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
key_buffer = 2G key_cache_division_limit = 70 thread_stack = 192K tmp_table_size = 1G max_heap_table_size = 1G 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 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
Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.
Новые изменения настройки my.cnf в 2021 году
Времена идут, знания становятся лучше, поэтому я уже практически во многом перенастроил свои файлы конфигов базы данных. Сразу скажу, что в основном этот конфиг рассчитан по моим базам данных, это порядка 14 гигабайт данных на серверах с 32 памяти оперативной, ssd дисками и собственно выкручено все на соотношение скорость работы + стабильность. Поэтому вот такой конфиг сейчас использую на Centos 8 с указанными параметрами серверов.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
collation-server = utf8_general_ci character-set-server = utf8 local-infile=0 innodb_file_per_table = 1 skip-log-bin = true symbolic-links=0 skip-external-locking skip-name-resolve max-connect-errors = 1000 low-priority-updates=1 max_allowed_packet = 32M open_files_limit = 165536 key_buffer_size = 512m max_connections = 150 thread-cache-size = 150 wait_timeout = 90 interactive_timeout = 90 innodb_buffer_pool_instances = 16 innodb_buffer_pool_size = 16G innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb-log-files-in-group = 2 innodb_log_file_size = 2g innodb_log_buffer_size = 16M |
В принципе производительность и уровень работы меня устраивает. Если у вас есть вопросы или конфигурация сервера другая, желательно написать все таки вопрос, я помогу разобраться какие параметры следует учесть. Из основного: сделан упор на работу Innodb + конфиги учитывают максимально настройки устраняющие узкие места в работе базы данных.
