Статьи

Есть ли место для дополнительной оптимизации ввода-вывода MySQL?

Я предпочитаю запускать MySQL с innodb_flush_method = O_DIRECT в большинстве случаев — он гарантирует, что нет никаких накладных расходов на двойную буферизацию, и я могу сохранить ограниченный объем кэша файловой системы, который я обычно имел бы на сервере базы данных для тех вещей, которые необходимо кэшировать — системные файлы, двоичный журнал, FRM-файлы, системные таблицы MySQL MyISAM и т. д. Начиная с MySQL 5.5, MySQL использует асинхронный ввод-вывод, который должен очень эффективно загружать подсистему ввода-вывода, позволяя выдавать много невыполненных запросов, которые могут быть объединены на уровне ОС или RAID-контроллера. поэтому мы должны ожидать, по крайней мере, такую ​​же хорошую производительность от O_DIRECT, как от буферизованного режима? Оказывается, это не всегда так.

Я пришел к этому дублю случайно, поэтому у него не так много науки, но я думаю, что он все еще довольно представительный. У меня есть старый тестовый сервер с жесткими дисками SATA 4 * 7200 об / мин в RAID10. Он имеет 8 ГБ ОЗУ, и я использую Percona Server 5.5.28-29.1, на котором 6 ГБ выделено для innodb_buffer_pool, что при остальной потребности в памяти оставляет примерно 1 ГБ для кэша ОС.

Вот фрагмент кода pt :

# Percona Toolkit System Summary Report ######################
        Date | 2013-01-03 22:29:39 UTC (local TZ: EST -0500)
    Hostname | smt2
      Uptime | 6 days,  6:21,  4 users,  load average: 0.00, 0.01, 0.05
      System | Supermicro; PDSMi; v0123456789 (Other)
 Service Tag | 0123456789
    Platform | Linux
     Release | Ubuntu 12.04.1 LTS (precise)
      Kernel | 3.2.0-35-generic
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.15
     SELinux | No SELinux detected
 Virtualized | No virtualization detected
# Processor ##################################################
  Processors | physical = 1, cores = 2, virtual = 2, hyperthreading = no
      Speeds | 2x1200.000
      Models | 2xIntel(R) Pentium(R) D CPU 3.00GHz
      Caches | 2x2048 KB

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

mysql> show table status like "post_channel" \G
*************************** 1. row ***************************
           Name: post_channel
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 278987416
 Avg_row_length: 75
    Data_length: 20975714304
Max_data_length: 0
   Index_length: 9047113728
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2012-12-31 16:24:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

Размер таблицы составляет приблизительно 30 ГБ, включая индексы, что намного больше, чем размер пула буферов и доступный кэш ОС. Я запускаю CHECK TABLE для этой таблицы, которая в основном выполняет сканирование данных и индексов, что будет отражать запросы на сканирование больших таблиц и сканирование таблиц, хотя на самом деле это не главное.

Суть в том, что я обнаружил, что в режиме буферизованного ввода-вывода запрос занимал почти половину времени, затрачиваемого на его выполнение в режиме O_DIRECT. Это не может быть объяснено дополнительным кешем, так как 1 ГБ кеша ОС по сравнению с 6 Б пула буферов означает, что от этого мало что получится, и поэтому прирост производительности, скорее всего, будет достигнут за счет того, что операционная система Read Ahead делает вместо этого , Это может быть подтверждено числом innodb_data_reads (значения с интервалом в 100 секунд)

| Innodb_data_reads                        | 54561         |
| Innodb_data_reads                        | 38277         |
| Innodb_data_reads                        | 25895         |
| Innodb_data_reads                        | 31363         |
| Innodb_data_reads                        | 170816        |
| Innodb_data_reads                        | 207432        |
| Innodb_data_reads                        | 156896        |
| Innodb_data_reads                        | 19059         |
| Innodb_data_reads                        | 16805         |

с числом считываемых данных, превышающим 2000 в секунду — намного больше, чем этот программный RAID может сделать в реальных IOP.

Я начал экспериментировать с другой конфигурацией read_ahead. Percona Server по-прежнему позволяет мне включить Random Read_Ahead, который недоступен в MySQL 5.5 (другая версия добавлена ​​обратно в MySQL 5.6), поэтому я попробовал это, а также отключил все операции чтения вперед. В обоих случаях для данной заданной рабочей нагрузки производительность была наилучшей при включении обоих типов read_ahead, но в буферизованном режиме она оставалась намного лучше. Посмотрите на график:

chart_1

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

MySQL 5.6 имеет больше изменений в коде ввода-вывода, включая то, что должно быть значительно улучшено упреждающим чтением, и я проверю его поведение дальше. Давайте посмотрим, если ситуация резко отличается.

Хорошая новость заключается в том, что даже несмотря на то, что преимущества производительности в режиме с буферизацией могут быть существенными для некоторых рабочих нагрузок, такие случаи редки — большинство рабочих нагрузок OLTP действительно работают лучше в режиме O_DIRECT. Контроллер RAID также может иметь существенное значение, так как O_DIRECT применяется только к логике операционной системы, в то время как контроллер RAID может выполнять некоторую форму упреждающего чтения, что делает ненужной помощь опережающего чтения ОС. Что ж, здесь есть над чем поэкспериментировать даже с таким второстепенным предметом — у специалистов по базам данных работа никогда не будет завершена.