8.5.8 Optimizing InnoDB Disk I/O — MySQL 5.7

8.5.8 Оптимизация ввода-вывода на диске InnoDB

Если вы следуете лучшим практикам проектирования баз данных и методам настройки операций SQL, но ваша база данных всё ещё работает медленно из-за высокой активности ввода-вывода на диске, рассмотрите эти оптимизации ввода-вывода на диске. Если инструмент Unix top или диспетчер задач Windows показывают процент использования процессора с вашей рабочей нагрузкой менее 70%, ваша рабочая нагрузка, вероятно, ограничена диском.

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

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

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

InnoDB использует подсистему асинхронного ввода-вывода (native AIO) в Linux для выполнения запросов предварительной загрузки и записи страниц данных файлов. Это поведение контролируется параметром конфигурации innodb_use_native_aio, который включён по умолчанию. С native AIO тип планировщика ввода-вывода оказывает большее влияние на производительность ввода-вывода. Обычно рекомендуются планировщики ввода-вывода noop и deadline. Проведите бенчмаркинг, чтобы определить, какой планировщик ввода-вывода обеспечивает лучшие результаты для вашей рабочей нагрузки и среды. Дополнительную информацию см. в разделе 14.8.7 «Использование асинхронного ввода-вывода в 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. Дополнительную информацию см. в разделе 8.12.2 «Оптимизация ввода-вывода на диск».

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

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

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

Проверьте настройки следующих параметров конфигурации при использовании невращающихся накопителей:
- innodb_checksum_algorithm
  
  Параметр crc32 использует более быстрый алгоритм контрольной суммы и рекомендуется для быстрых систем хранения.
- innodb_flush_neighbors
  
  Оптимизирует ввод-вывод для устройств с вращающимися накопителями. Отключите его для невращающихся накопителей или для смешанного использования вращающихся и не вращающихся накопителей.
- innodb_io_capacity
  
  Значение по умолчанию 200 обычно достаточно для накопителя с не вращающимися носителями низкого уровня. Для устройств с более высокой производительностью, подключённых к шине, рассмотрите более высокое значение, например, 1000.
- innodb_io_capacity_max
  
  Значение по умолчанию 2000 предназначено для рабочих нагрузок, использующих невращающиеся накопители. Для высокопроизводительного невращающегося накопителя, подключённого к шине, рассмотрите более высокое значение, например, 2500.
- innodb_log_compressed_pages
  
  Если файлы журнала переписывания находятся на невращающихся накопителях, рассмотрите возможность отключения этого параметра для сокращения записи журналов. См. Отключение записи сжатых страниц в журнал.
- innodb_log_file_size
  
  Если файлы журнала переписывания находятся на невращающихся накопителях, настройте этот параметр для максимального кэширования и объединения операций записи.
- innodb_page_size
  
  Рассмотрите использование размера страницы, соответствующего внутреннему размеру сектора диска. У устройств SSD ранних поколений часто размер сектора составляет 4 КБ. Некоторые более новые устройства имеют размер сектора 16 КБ. По умолчанию размер страницы InnoDB составляет 16 КБ. Поддержание размера страницы близким к размеру блока устройства хранения минимизирует количество неизменённых данных, которые переписываются на диск.
- binlog_row_image
  
  Если бинарные журналы находятся на невращающихся накопителях, и все таблицы имеют первичные ключи, рассмотрите возможность установки этого параметра на minimal для уменьшения записи журнала.
Убедитесь, что поддержка TRIM включена для вашей операционной системы. Обычно она включена по умолчанию.
Увеличение пропускной способности ввода-вывода для предотвращения очередей

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

Если система не отстаёт от операций InnoDB , рассмотрите возможность уменьшения значения параметра конфигурации innodb_io_capacity. Обычно это значение вы держите как можно ниже, но не настолько, чтобы это вызывало периодические снижения пропускной способности, как упоминалось в предыдущем пункте. В типичном случае, когда вы можете уменьшить значение параметра, вы можете увидеть комбинацию, подобную этой, в выводе из SHOW ENGINE INNODB STATUS:
- Длина списка истории низкая, ниже нескольких тысяч.
- Слияния буфера вставки близки к вставленным строкам.
- Изменённые страницы в буферном пуле постоянно находятся ниже innodb_max_dirty_pages_pct буферного пула. (Измеряйте в то время, когда сервер не выполняет массовые вставки; во время массовых вставок нормальным является значительное повышение процента изменённых страниц.)
- Log sequence number - Last checkpoint находится менее чем на 7/8, или в идеале менее чем на 6/8 от общего размера InnoDB.

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

Вы можете воспользоваться оптимизацией ввода-вывода, связанной с буфером двойной записи, храня файлы системного табличного пространства (“файлы ibdata”) на устройствах Fusion-io, поддерживающих атомарные записи. В этом случае буферизация двойной записи (innodb_doublewrite) автоматически отключается, и для всех файлов данных используются атомарные записи Fusion-io. Эта функция поддерживается только на оборудовании Fusion-io и только для Fusion-io NVMFS в Linux. Для максимальной пользы от этой функции рекомендуется значение параметра innodb_flush_method равное O_DIRECT.

Примечание

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

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