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

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

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

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

innodb_buffer_pool_size

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

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

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

innodb_log_file_size

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

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

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

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

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

2x512M = 1G

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

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

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

innodb_log_buffer_size

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

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

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

innodb_file_per_table

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

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

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

innodb_flush_method

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

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

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

query_cache_size

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

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

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

max_connections

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

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

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