Высокое ожидание IO / задержка диска

Система ждёт дисковый ввод-вывод. Сначала определим, преобладает чтение или запись, затем дойдём до причин: недостаточный буферный кэш, полные сканирования таблиц, шторм checkpoint, лимиты IOPS или вытеснение во временное пространство.

IO преимущественно на чтение или на запись?

По статистике файлового ввода-вывода определите, приходится ли ожидание на чтение (данные читаются с диска) или на запись (redo/checkpoint/запись данных). Это делит дерево на две ветви.

SELECT function_name, small_read_reqs+large_read_reqs AS reads, small_write_reqs+large_write_reqs AS writes,
       small_read_megabytes+large_read_megabytes+small_write_megabytes+large_write_megabytes AS mb
FROM v$iostat_function ORDER BY mb DESC;

• В основном IO на чтение
• В основном IO на запись

Почему высокая нагрузка IO на чтение?

Избыточные физические чтения имеют три основные причины: буферный кэш слишком мал (working set не помещается в RAM), запросы выполняют полные сканирования таблиц, или большие операции сортировки/хеширования вытесняются во временное пространство.

SELECT ROUND((1-(pr.value/(dbg.value+cg.value)))*100,2) AS buffer_hit_pct
FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg
WHERE pr.name='physical reads' AND dbg.name='db block gets' AND cg.name='consistent gets';
SELECT sql_id, disk_reads, executions FROM v$sql ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

• Попадания в буферный кэш низкие, working set не помещается
• Запросы выполняют полные сканирования таблиц
• Большие сортировки/хеширование вытесняются во временное пространство

Почему IO на запись высокий?

Узкое место на стороне записи возникает либо из-за периодических checkpoint/сбросов, кратковременно перегружающих диск (шторм checkpoint), либо из-за нехватки IOPS/пропускной способности хранилища для устойчивой нагрузки.

SELECT event, total_waits, ROUND(time_waited/100,1) AS sec
FROM v$system_event
WHERE event IN ('log file parallel write','db file parallel write','checkpoint completed')
ORDER BY time_waited DESC;

• Внезапные волны checkpoint/сброса (шторм checkpoint)
• Хранилище не справляется с устойчивой нагрузкой (лимит IOPS)

Первопричина: недостаточный буферный кэш

Часто используемый набор данных (working set) не помещается в буферный кэш, поэтому блоки постоянно перечитываются с диска. Коэффициент попаданий низкий, физических чтений много.

Первопричина: полные сканирования таблиц

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

Первопричина: сортировки/хеширование вытесняются во временное пространство

Большие операции sort, hash join или group by не помещаются в рабочую память и пишут во временное пространство (temp/tempdb), создавая интенсивный ввод-вывод на чтение/запись.

Первопричина: шторм checkpoint

Грязные страницы долго накапливаются и массово записываются на диск через короткие интервалы; эти внезапные волны записи периодически перегружают IO.

Первопричина: недостаточные IOPS / пропускная способность

Хранилищу не хватает IOPS или пропускной способности для обслуживания запросов чтения+записи, требуемых нагрузкой; глубина очереди и задержка постоянно высоки.