Thank you for reading this post, don't forget to subscribe!
Дефолтные конфигурационные параметры в Mysql рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.
Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Корректировка параметров в рабочем режиме во многом зависит от специфики Вашей системы и ее мониторинга. Разберемся с параметрами и рекомендациями по установке их значений.
Настройки нужно вносить в my.cnf.
innodb_buffer_pool_size
Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кеширует и данные и индексы. Поэтому значение этого ключа стоит устанавливать в 70%...80% всей доступной памяти.
1 |
innodb_buffer_pool_size = 24G |
# При том, что на нашем сервере 32Гб оперативной памяти
innodb_log_file_size
Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:
1 |
innodb_log_file_size = 512M |
# Так два файла дадут размер лога в 2x512M = 1G
Стоит понимать, что увеличение этого параметра увеличит и время восстановления системы при сбоях. Это происходит потому, что при запуске системы все данные из логов будет накатываться на данные. Однако с каждой новой версией, производительность этого процесса растет. Подумайте над использованием реплик для обеспечения доступности, чтобы не зависеть от времени восстановления базы данных.
innodb_log_buffer_size
Это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT.
1 |
innodb_log_buffer_size = 2M |
# Значения по умолчанию в 1М должно быть достаточно для большинства случаев
innodb_file_per_table
Если включить эту опцию, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). Прироста в производительности не будет, однако есть ряд преимуществ:
- При удалении таблиц, диск будет освобождаться. По умолчанию общий файл данных может только расширяться, но не уменьшаться.
- Использование компрессионного формата таблиц потребует включить этот параметр.
1 |
innodb_file_per_table = ON |
# С версии 5.6 этот параметр включен по умолчанию
innodb_flush_method
Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT:
1 |
innodb_flush_method = O_DSYNC |
# Помните об обязательном использовании резервных узлов (например, реплик)
innodb_flush_log_at_trx_commit
Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).
Тут следует руководствоваться такой логикой:
- innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
- innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.
Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:
1 |
innodb_flush_log_at_trx_commit = 2 |
# Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных
query_cache_size
Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных:
1 |
query_cache_size = 0 |
# Однако убедитесь, что используете индексы для обеспечения высокой скорости работы запросов
max_connections
Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки "Too many connections", эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:
1 |
max_connections = 256 |
# Поднимайте значение постепенно при появлении ошибок соединений
Настройки по умолчанию скорее всего не подойдут. Поэтому обязательно стоит пройтись по указанным параметрам в статье и подобрать для них значения. Если совсем лень — генератор настроек Mysql.