# === Оптимизированная конфигурация my.cnf для MySQL/MariaDB (на серверах Ubuntu, CentOS, Almalinux и т. д.) === # # Фотис Эвангелоу, разработчик Engintron (engintron.com) # # ~ Обновлено в декабре 2021 г. ~ # # # Приведенные ниже настройки являются отправной точкой для сервера с 8–16 ГБ ОЗУ и 4–8 ядрами ЦП. # Если у вас есть другие доступные ресурсы, вы должны настроить их соответствующим образом, чтобы сэкономить ресурсы ЦП, ОЗУ и дискового ввода-вывода. # # Настройки, помеченные специальным комментарием или словом "UPD" (после значения) # следует настроить для вашей системы с помощью инструментов диагностики базы данных, таких как: # # github.com/major/MySQLTuner-perl # или # github.com/BMDan/tuning-primer.sh # # Запустите любой из этих скриптов перед оптимизацией вашей базы данных, по крайней мере, через 1 час после оптимизации и, наконец, # не реже одного раза в день в течение 3 дней (без перезапуска базы данных), чтобы посмотреть, как работает ваш сервер и если вам нужно # для повторной настройки чего-либо. Чем больше MySQL/MariaDB работает без перезапуска, тем больше данных об использовании он собирает. # диагностические сценарии сообщат в подробностях о том, как работает MySQL/MariaDB. # # # ВАЖНОЕ ПРИМЕЧАНИЕ: Если после значения параметра НЕТ комментария, то в 99,9% случаев вам не нужно его настраивать. # # # --- ЧТО СДЕЛАТЬ ПОСЛЕ ОБНОВЛЕНИЯ MY.CNF – УСТРАНЕНИЕ НЕИСПРАВНОСТЕЙ --- # # Если упоминаются какие-либо команды терминала, убедитесь, что вы выполняете их как пользователь root. # # Если MySQL или MariaDB не могут запуститься (или перезапуститься), выполните следующие действия. # # 1. Если сервер имел стандартную конфигурацию базы данных, и вы добавляли или обновляли какие-либо # настройки "innodb_log_*" (как предложено ниже), затем выполните ТОЛЬКО эти команды # при первом применении этой конфигурации: # # $ rm -rvf /var/lib/mysql/ib_logfile* # $ chown -R mysql:mysql /var/lib/mysql # $ service mysql restart # # или используйте сокращенную команду: # $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart # # 2. Если параметр "bind-address" не закомментирован, то убедитесь, что файл /etc/hosts # настроен правильно. Хороший пример «чистого» файла /etc/hosts выглядит примерно так: # # 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 # ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 # 1.2.3.4 hostname.domain.tld hostname # <-- Замените соответственно! # # Наконец, перезапустите службу базы данных: # # $ service mysql restart # # 3. Если служба базы данных не может перезапуститься даже после первых 2 шагов, убедитесь, что папка данных базы данных # (общий для MySQL или MariaDB) "/var/lib/mysql" принадлежит пользователю И группе "mysql". # Кроме того, сама папка может иметь права доступа 0751 или 0755. Чтобы исправить это, просто сделайте это: # $ chown -R mysql:mysql /var/lib/mysql # $ chmod 0755 /var/lib/mysql # # Наконец, перезапустите службу базы данных: # # $ service mysql restart # # # ~ FIN ~ [mysql] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] # === Обязательные настройки === basedir = /usr bind_address = 127.0.0.1 # Измените на 0.0.0.0, чтобы разрешить удаленные подключения. datadir = /var/lib/mysql #default_authentication_plugin = mysql_native_password # Включите в MySQL 8+ или MariaDB 10.6+ для обратной совместимости с распространенными CMS. max_allowed_packet = 256M max_connect_errors = 1000000 pid_file = /var/run/mysqld/mysqld.pid port = 3306 skip_external_locking skip_name_resolve socket = /var/run/mysqld/mysqld.sock tmpdir = /tmp user = mysql # === Режим совместимости с SQL === # Включить b/c с базами данных, созданными в более старых версиях MySQL/MariaDB. # (например, при использовании нулевых дат) #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES # Дерьмовые SQL-запросы/схема? Забей! #sql_mode = "" # === Настройки InnoDB === default_storage_engine = InnoDB innodb_buffer_pool_instances = 4 # Используйте 1 экземпляр на 1 ГБ размера пула InnoDB — максимум 64 innodb_buffer_pool_size = 4G # Используйте до 70-80% оперативной памяти innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_log_file_size = 1G innodb_sort_buffer_size = 4M # UPD - Определяет, сколько данных считывается в память для операций сортировки перед записью на диск (по умолчанию 1M / макс. 64M) innodb_stats_on_metadata = 0 #innodb_use_fdatasync = 1 # Только (!) для MySQL v8.0.26+ #innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Контролируйте максимальный размер файла ibtmp1 #innodb_thread_concurrency = 4 # Необязательно: установите количество процессоров в вашей системе (минус 1 или 2), чтобы лучше # содержать загрузку процессора. Например. если в вашей системе 8 процессоров, попробуйте 6 или 7 и проверьте # общую нагрузку, создаваемую MySQL/MariaDB. innodb_read_io_threads = 64 innodb_write_io_threads = 64 #innodb_io_capacity = 2000 # Зависит от технологии хранения — используйте 2000 для SSD, больше для NVMe #innodb_io_capacity_max = 4000 # Обычно удваивает значение innodb_io_capacity. # === Настройки MyISAM === # Следующие 3 опции поддерживаются ТОЛЬКО MariaDB и до MySQL 5.7 # НЕ раскомментируйте MySQL 8.x+ #query_cache_limit = 4M # UPD #query_cache_size = 64M # UPD #query_cache_type = 1 # Включено по умолчанию key_buffer_size = 24M # UPD low_priority_updates = 1 concurrent_insert = 2 # === Connection Settings === max_connections = 100 # UPD - Важно: большое количество подключений = большое потребление оперативной памяти back_log = 512 thread_cache_size = 100 thread_stack = 192K interactive_timeout = 180 wait_timeout = 180 # Только для MySQL 5.7+ (по умолчанию отключено) #max_execution_time = 90000 # Установите лимит времени ожидания для операторов SELECT (значение в миллисекундах). # Этот параметр может быть полезен для борьбы с агрессивным сканированием на больших сайтах, # но это также может вызвать проблемы (например, с резервными копиями). Так что используйте с особой осторожностью и тестируйте! # Дополнительная информация по адресу: dev.mysql.com/doc/refman/5.7/en/server-system-variables.html. # For MariaDB 10.1.1+ only (disabled by default) #max_statement_time = 90 # Эквивалент «max_execution_time» в MySQL 5.7+ (установлен выше) # Переменная имеет тип double, поэтому вы можете использовать тайм-аут в доли секунды. # Например, вы можете использовать значение 0,01 для тайм-аута 10 миллисекунд. # Дополнительная информация: mariadb.com/kb/en/aborting-statements/ # === Настройки буфера === # Удобный совет по управлению использованием оперативной памяти вашей базы данных: # Со следующими значениями следует обращаться осторожно, так как они суммируются, а затем умножаются на ваше значение "max_connections". # Другие параметры также увеличивают потребление оперативной памяти (например, tmp_table_size). Так что не переключайте свой «join_buffer_size» на 1G, это вредно и неэффективно. # Используйте один из инструментов диагностики базы данных, упомянутых в начале этого файла, чтобы подсчитать потенциальное общее использование ОЗУ вашей базой данных, чтобы вы знали, находитесь ли вы в пределах # разумные пределы. Помните, что другим службам для правильной работы потребуется достаточно оперативной памяти (например, Apache или PHP-FPM), поэтому устанавливайте ограничения с умом. join_buffer_size = 4M # UPD read_buffer_size = 3M # UPD read_rnd_buffer_size = 4M # UPD sort_buffer_size = 4M # UPD # === Настройки таблицы === # В системах, управляемых systemd, таких как Ubuntu 16.04+ или CentOS 7+, вам необходимо выполнить дополнительное действие для table_open_cache и open_files_limit # для переопределения (см. также комментарий рядом с open_files_limit). # Например. для MySQL 5.7 проверьте: dev.mysql.com/doc/refman/5.7/en/using-systemd.html # и для проверки MariaDB: mariadb.com/kb/en/library/systemd/ table_definition_cache = 40000 # UPD table_open_cache = 40000 # UPD open_files_limit = 60000 # UPD - Это может быть в 2-3 раза больше значения table_open_cache или соответствовать системному # лимиту открытых файлов обычно устанавливается в /etc/sysctl.conf и /etc/security/limits.conf # В системах, управляемых systemd, это ограничение также должно быть установлено в: # - /etc/systemd/system/mysql.service.d/override.conf (for MySQL 5.7+ in Ubuntu) or # - /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+ in CentOS) or # - /etc/systemd/system/mariadb.service.d/override.conf (для MariaDB) # в противном случае изменение open_files_limit не будет иметь никакого эффекта. # # Чтобы отредактировать нужный файл, выполните: # $ systemctl edit mysql (или mysqld или mariadb) # и установите "LimitNOFILE=" примерно на 100000 или больше (в зависимости от ваших системных ограничений для MySQL) # или используйте "LimitNOFILE=infinity" только для MariaDB. # Наконец, объедините изменения с: # $ systemctl daemon-reload; systemctl restart mysql (или mysqld или mariadb) max_heap_table_size = 128M # Увеличьте до 256M или 512M, если у вас много временных таблиц из-за отсутствия индексов в JOIN. tmp_table_size = 128M # Используйте то же значение, что и max_heap_table_size # === Search Settings === ft_min_word_len = 3 # Минимальная длина слов для индексации результатов поиска # === Binary Logging === disable_log_bin = 1 # Двоичное ведение журнала отключено по умолчанию #log_bin # Чтобы включить ведение двоичного журнала, раскомментируйте эту строку и только одну из следующих двух строк. # это соответствует вашей фактической версии MySQL/MariaDB. # Не забудьте закомментировать строку с помощью «disable_log_bin». #expire_logs_days = 1 # Хранить журналы в течение 1 дня — только для MySQL 5.x и MariaDB до 10.6. #binlog_expire_logs_seconds = 86400 # Хранить журналы в течение 1 дня (в секундах) — только для MySQL 8+ и MariaDB 10.6+ # === Error & Slow Query Logging === log_error = /var/lib/mysql/mysql_error.log log_queries_not_using_indexes = 0 # Отключено в продакшн long_query_time = 5 slow_query_log = 0 # Отключено в продакшн slow_query_log_file = /var/lib/mysql/mysql_slow.log [mysqldump] # Ссылки на переменные # Для MySQL 5.7+: dev.mysql.com/doc/refman/5.7/en/mysqldump.html # Для MariaDB: mariadb.com/kb/en/library/mysqldump/ quick quote_names max_allowed_packet = 1024M