Увеличение размера буфера пула

Когда данные таблицы кэшируются в буферном пуле InnoDB, к ним можно многократно обращаться с помощью запросов, не обращаясь к диску. Укажите размер буферного пула с помощью параметра innodb_buffer_pool_size. Эта область памяти достаточно важна, поэтому обычно рекомендуется настроить innodb_buffer_pool_size на 50–75 процентов от объёма оперативной памяти. Дополнительную информацию можно найти в разделе 10.12.3.1 «Как MySQL использует память».

Настройка метода сброса

В некоторых версиях GNU/Linux и Unix сброс файлов на диск с помощью системного вызова Unix fsync() и аналогичных методов может быть неожиданно медленным. Если производительность записи в базу данных вызывает проблемы, выполните бенчмаркинг с параметром innodb_flush_method, установленным в значение O_DSYNC.

Настройка порога для сбросов операционной системой

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

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

Использование fdatasync() вместо fsync()

На платформах, поддерживающих fdatasync() системные вызовы, переменная innodb_use_fdatasync позволяет использовать fdatasync() вместо fsync() для сбросов операционной системой. Системный вызов fdatasync() не сбрасывает изменения в метаданные файла, если это не требуется для последующего извлечения данных, что может принести потенциальную выгоду в плане производительности.

Подмножество настроек innodb_flush_method, таких как fsync, O_DSYNC и O_DIRECT, используют fsync() системные вызовы. Переменная innodb_use_fdatasync применима при использовании этих настроек.

Использование планировщика ввода-вывода noop или deadline с помощью native AIO в Linux

InnoDB использует подсистему асинхронного ввода-вывода (native AIO) в Linux для выполнения запросов предварительной выборки и записи для страниц файла данных. Это поведение управляется опцией конфигурации innodb_use_native_aio, которая включена по умолчанию. При использовании native AIO тип планировщика ввода-вывода оказывает большее влияние на производительность ввода-вывода. Обычно рекомендуются планировщики ввода-вывода noop и deadline. Проведите бенчмаркинг, чтобы определить, какой планировщик ввода-вывода обеспечивает лучшие результаты для вашей рабочей нагрузки и среды. Дополнительную информацию можно найти в разделе 17.8.6 «Использование асинхронного ввода-вывода в Linux».

Использование прямого ввода-вывода на Solaris 10 для архитектуры x86_64

При использовании хранилища InnoDB на Solaris 10 для архитектуры x86_64 (AMD Opteron) используйте прямой ввод-вывод для файлов, связанных с InnoDB, чтобы избежать ухудшения производительности InnoDB. Для использования прямого ввода-вывода для всего файловой системы UFS, используемой для хранения файлов, связанных с InnoDB, смонтируйте её с опцией forcedirectio; см. mount_ufs(1M). (По умолчанию на Solaris 10/x86_64 эта опция не используется.) Чтобы применить прямой ввод-вывод только к операциям с файлами InnoDB, а не ко всей файловой системе, установите innodb_flush_method = O_DIRECT. С этой настройкой InnoDB вызывает directio() вместо fcntl() для ввода-вывода в файлы данных (не для ввода-вывода в файлы журнала).

Использование необрабатываемого хранилища для файлов данных и журналов с Solaris 2.6 или новее

При использовании хранилища InnoDB с большим значением innodb_buffer_pool_size в любой версии Solaris 2.6 и выше, на любой платформе (sparc/x86/x64/amd64), проведите бенчмаркинг с файлами данных InnoDB и файлами журналов на необработанных устройствах или на отдельной файловой системе UFS с прямым вводом-выводом, используя опцию монтирования forcedirectio, как описано ранее. (Необходимо использовать опцию монтирования, а не устанавливать innodb_flush_method, если вы хотите прямой ввод-вывод для файлов журналов.) Пользователи файловой системы Veritas VxFS должны использовать опцию монтирования convosync=direct.

Не размещайте другие файлы данных MySQL, такие как файлы для таблиц MyISAM, на файловой системе с прямым вводом-выводом. Исполняемые файлы или библиотеки не должны размещаться на файловой системе с прямым вводом-выводом.

Использование дополнительных устройств хранения

Дополнительные устройства хранения можно использовать для настройки конфигурации RAID. Дополнительную информацию см. в разделе 10.12.1 «Оптимизация ввода-вывода на диске».

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

Рассмотрите возможность использования невращающегося хранилища

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

Файлы, ориентированные на операции произвольного ввода-вывода, обычно включают файлы данных, файлы журналов и файлы InnoDB. Файлы, ориентированные на операции последовательного ввода-вывода, включают файлы, файлы doublewrite и файлы журнала, такие как файлы и файлы.

При использовании невращающегося хранилища пересмотрите настройки следующих параметров конфигурации:

Убедитесь, что для вашей операционной системы включена поддержка TRIM. Обычно она включена по умолчанию.

Увеличьте емкость ввода-вывода, чтобы избежать очередей

Если пропускная способность периодически снижается из-за InnoDB операций, рассмотрите возможность увеличения значения параметра конфигурации innodb_io_capacity. Более высокие значения приводят к более частым операциям, предотвращая накопление работы, которое может привести к снижению пропускной способности.

Уменьшите емкость ввода-вывода, если сброс не отстаёт

Если система не отстаёт от InnoDB операций, рассмотрите возможность уменьшения значения параметра конфигурации innodb_io_capacity. Обычно это значение поддерживается как можно ниже, но не настолько низко, чтобы это вызывало периодическое падение пропускной способности, как упоминалось в предыдущем пункте. В типичной ситуации, когда вы можете уменьшить значение параметра, в выводе SHOW ENGINE INNODB STATUS вы можете увидеть комбинацию такого типа:

Храните файлы табличного пространства системы на устройствах Fusion-io

Вы можете воспользоваться оптимизацией ввода-вывода, связанной с буфером двойной записи, разместив файлы, содержащие область хранения буфера двойной записи, на устройствах Fusion-io, поддерживающих атомарные записи. (Область хранения буфера двойной записи находится в файлах двойной записи. См. Раздел 17.6.4, «Буфер двойной записи».) При размещении файлов области хранения буфера двойной записи на устройствах Fusion-io, поддерживающих атомарные записи, буфер двойной записи автоматически отключается, и для всех файлов данных используются атомарные записи Fusion-io. Эта функция поддерживается только на оборудовании Fusion-io и только включена для Fusion-io NVMFS в Linux. Для максимальной пользы от этой функции рекомендуется значение innodb_flush_method равное O_DIRECT.

Отключить протоколирование сжатых страниц

При использовании функции таблицы InnoDB изображения повторно сжатых записываются в при внесении изменений в сжатые данные. Это поведение контролируется innodb_log_compressed_pages, который включён по умолчанию для предотвращения повреждений, которые могут возникнуть, если в процессе восстановления используется другая версия алгоритма сжатия zlib. Если вы уверены, что версия zlib не будет изменена, отключите innodb_log_compressed_pages, чтобы уменьшить создание журнала повторения для рабочих нагрузок, изменяющих сжатые данные.