Проблема
Суть проблематики данной статьи - регулярное замедление в работе баз данных SQL Server. После статей, посвящённых анализу использования памяти и CPU, мы хотели бы продолжить исследование причины замедления путём анализа узких мест ввода-вывода.
Решение
Подсистема ввода-вывода - ключевой фактор производительности SQL Server, поскольку страницы базы данных постоянно перемещаются с диска в буферный пул или обратно. Помимо этого существенный трафик ввода-вывода генерируют журналы транзакций и системная база данных tempDB. Учитывая эти факторы, Вы должны быть уверены, что используемая подсистема ввода-вывода работает стабильно, иначе проблемы в работе этой подсистемы приведут к увеличению времени отклика запросов и частым тайм-аутам. В этой статье я опишу несколько способов поиска узких мест ввода-вывода, используя для этого встроенные инструменты, и представлю некоторые идеи, связанные с конфигурацией дисков.
Performance Monitor
Чтобы определить загрузку подсистемы ввода-вывода, можно воспользоваться системной утилитой Performance Monitor. Перечисленные ниже счётчики производительности могут оказаться полезны для этих целей:
PhysicalDisk Object: Avg. Disk Queue Length. Этот счетчик показывает среднее число запросов чтения и записи, которые были поставлены в очередь для указанного физического диска. Чем выше это число, тем большее дисковых операций ожидает ввода-вывода. Если это значение во время пиковой нагрузки на SQL Server частенько превышает двойку, следует задуматься о необходимости принятия адекватных мер. Если используется несколько дисков, показания счётчика нужно разделить на число дисков в массиве и убедиться, не превышает ли результирующее значение число 2. Например, у Вас есть 4 диска и длина очереди диска 10, искомая глубина очереди находится следующим образом: 10/4 = 2,5, это и будет значением, которое нужно анализировать, а не 10.
Avg. Disk Sec/Read и Avg. Disk Sec/Write показывают среднее время чтения и записи данных на диск. Хорошо, если это значение не превышает 10 ms, но все еще приемлемо, если значение меньше 20 ms. Значения, превышающие этот порог, требуют исследования возможностей оптимизации.
Physical Disk: %Disk Time - время, которое диск был занят обслуживанием запросов записи или чтения. Это значение должно быть ниже 50%.
Disk Reads/Sec и Disk Writes/Sec - показатель уровня загруженности диска операциями чтения - записи. Значение должно быть меньше 85% от пропускной способности диска, поскольку при превышении этого порога время доступа увеличивается по экспоненте.
Пропускную способность диска можно определить постепенно увеличивая нагрузку на систему. Одним из способов определения пропускной способности дисковой подсистемы является использование специализированной утилиты SQLIO. Она позволяет определить ту точку, где пропускная способность перестаёт расти при дальнейшем увеличении нагрузки.
При выборе конфигураций RAID можно использовать следующие формулы вычисления числа операций ввода-вывода (I/Os), приходящихся на один диск:
Raid 0: I/O на диск = (чтений + записей) / число дисков массива
Raid 1: I/O на диск = [чтений + (записей *2)] / 2
Raid 5: I/O на диск = [чтений + (записей *4)] / число дисков массива
Raid 10: I/O на диск = [чтений + (записей *2)] / число дисков массива
Вот пример вычисления количества операций ввода-вывода на диск для RAID 1 на основе значений счетчиков:
Disk Reads/sec = 90
Disk Writes/sec = 75
Формула для ввода-вывода на RAID-1 массив является [чтений + (записей*2)] / 2 или [90 + (75*2)] / 2 = 120 I/Os на диск.
Динамические административные представления
Есть полезные динамические административные представления (DMV), с помощью которых можно выявить узкие места ввода-вывода.
Специальный тип ожидания краткой блокировки для операции ввода-вывода (I/O latch) имеет место тогда, когда задача переходит в состояние ожидания завершения кратковременной блокировки буфера, находящегося в состоянии обслуживания запроса ввода-вывода. В зависимости от типа запроса, это приводит к появлению ожиданий с именами PAGEIOLATCH_EX или PAGEIOLATCH_SH. Длительные ожидания могут указывать на проблемы с дисковой подсистемой. Чтобы посмотреть статистику таких ожиданий можно использовать системное представление sys.dm_os_wait_stats. Для того, что бы определить наличие проблем ввода-вывода, нужно посмотреть значения waiting_task_counts и wait_time_ms при нормальной рабочей нагрузке SQL Server и сравнить их со значениями, полученными при ухудшении производительности.
select * from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
ORDER BY wait_type asc
Ожидания запросов ввода-вывода можно посмотреть с помощью соответствующих DMV и эту информацию можно использовать для определения того, какой именно диск является узким местом.
select db_name(database_id),
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats (NULL, NULL) iovfs,
sys.dm_io_pending_io_requests as iopior
where iovfs.file_handle = iopior.io_handle
Дисковая фрагментация
Я рекомендую регулярно проверять уровень фрагментации и конфигурацию дисков, используемых экземпляром SQL Server.
Фрагментация файлов на разделе NTFS может стать причиной существенной потери производительности. Диски должны регулярно дефрагментироваться. Исследование показывают, что в некоторых случаях диски, подключаемые из сетей SAN, менее производительны, если их файлы дефрагментированы, т.е. эти СХД оптимизированы под случайный ввод-вывод. Прежде чем устранять файловую фрагментацию, стоит выяснить, как она сказывается на производительности работы SAN.
Фрагментация индексов также может стать причиной повышения нагрузки ввода-вывода на NTFS, но на это влияют уже другие условия, отличные от тех, что существенны для SAN, оптимизированных для случайного доступа.
Конфигурация дисков / Best Practices
Как правило, для повышения производительности, файлы журналов кладут на отдельные физические диски, а файлы данных размещают на других физических дисках. Ввод-вывод для высоко нагруженных файлов данных (включая tempDB) носит случайный характер. Ввод-вывод для файла журнала транзакций носит последовательный характер, кроме случаев отката транзакций.
Встроенные в шасси сервера (локальные) диски можно использовать только для файлов журнала транзакций, потому что они хорошо ведут себя при последовательном вводе-выводе, а при случайном вводе-выводе ведут себя плохо.
Файлы данных и журналов должны размещаться на разных дисковых массивах, у которых используются разные наборы физических дисков. В большинстве случаев, когда решение должно укладываться в не большой бюджет, я рекомендую размещать файл журнала транзакций на массиве RAID1, собранном из локальных дисков. Файлы данных БД лучше разместить на внешней системе хранения в сети SAN, так, чтобы к используемым для данных физическим дискам доступ получал только SQL Server, что позволит контролировать обслуживание его запросов и получать достоверные отчёты загрузки дисковой подсистемы. От подключения дисковых подсистем напрямую к серверу лучше отказаться.
Кэширование записи должно быть включено везде, где только это возможно, и вы должны удостовериться, что кэш защищен от перебоев в питании и других возможных отказов (независимая батарея подпитки кэша на контроллере).
Во избежание появления узких мест ввода-вывода для OLTP систем, лучше не смешивать нагрузки, характерные для OLTP и OLAP. Кроме того, удостоверьтесь, что серверный код оптимизирован и, где это необходимо, созданы индексы, которые тоже позволяют избавиться от ненужного ввода-вывода.