MySQL: Оптимальная настройка

Настройки MySQL не подходят для большинства проектов и пригодны только для тестов.

Mysql

Процесс оптимизации состоит из двух этапов

  1. Первоначальная настройка
  2. Корректировка параметров в процессе работы

innodb_buffer_pool_size

Если используются только InnoDB, драйвер по умолчанию, необходимо установить буфер максимально возможный для используемой системы. В буфере InnoDB хранятся (кешируются) данные и индексы.

Значение данного параметра рекомендуется устанавливать в 70%-80% доступной оперативной памяти.

К примеру при наличии в системе 32 гигабайт оперативной памяти, оптимальным будет значение 24 гигабайта.

innodb_buffer_pool_size = 24G

innodb_log_file_size

Данная опция напрямую влияет на скорость записи данных.

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

Все операции сначала записываются в лог, а только потом применяются к данным на диске, соответственно чем больше лог, тем больше операций в лог файл.

Важный момент: файлов журналов всегда два, с одинаковым размером.

innodb_log_file_size = 512M

Соответственно при указании размера лога в 512 мегабайт, размер лога будет составлять 1 гигабайт.

2x512M = 1G

Необходимо учитывать, что увеличение данного параметра увеличит и время восстановления системы при сбоях.

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

Если необходимо снизить время восстановления, то стоит подумать об использовании реплик,чтобы не зависеть от времени восстановления базы данных.

innodb_log_buffer_size

Данный параметр отвечает за размер буфера транзакций, которые не были еще применены (закомичены).

Значение данного поля стоим изменять при использовании больших полей BLOB или TEXT.

innodb_log_buffer_size = 2M

Значение по умолчанию в 1 мегабайт обычно достаточно для большинства случаев.

innodb_file_per_table

При включении данной опции, InnoDBбудет сохранять данные всех таблиц в отдельных файлах. Данная опция не увеличивает производительность, но обеспечиват дополнительыне приемущества:

  • При удалении таблиц, диск будет освобождаться. По умолчанию общий файл данных может только расширяться, но не уменьшаться.
  • Использование компрессионного формата таблиц потребует включить этот параметр.
innodb_file_per_table = ON

Начиная с MySQL 5.6 данная опция включена по умолчанию.

innodb_flush_method

Данный параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узлов, вы будете выбирать между O_DSYNC и O_DIRECT

innodb_flush_method = O_DSYNC
  • O_DIRECT- метод использует 4 операции записи на диск на каждый вызов. Все эти операции верифицируются, что обеспечивает дополнительную надежность записи на диск, однако замедляет выполнение.
  • O_DSYNC - метод также использует 4 операции записи на диск на каждый вызов. Однако при этом, только две из этих операций будут верифицированы . Это обеспечит более эффективную запись, что позволит увеличить пропускную способность записи MySQL.

O_DSYNC работает быстрее, чем O_DIRECT, но не так надежно.

innodb_flush_log_at_trx_commit

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

Тут следует руководствоваться такой логикой:

  • innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных – это приоритет.
  • innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.

Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:

innodb_flush_log_at_trx_commit = 2

Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных

query_cache_size

Данный параметр определяет размер памяти выделяемый для кеширования запросов.

Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных

query_cache_size = 0

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

max_connections

Не стоит изменять данный параметр без явной на то необходимости. Если при работе возникают ошибки "Too many connections", то значение данного параметра стоит увеличить.

max_connections = 256

Автоматический подбор параметров

Если не хочется разбираться в параметрах, то можно воспользоваться утилитой mysqltuner.

Добавить комментарий