Автор — Александр Каленик

В предыдущих статьях мы рассмотрели примеры анализа производительности SQL Server связанные с проблемами с процессорами и памятью (http://sqlcom.ru/optimization_query/sql-server-cpu-overload/,http://sqlcom.ru/optimization_query/sql-server-memory-optimization-1/)

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

Сначала несколько предложений о том, каким образом сервер использует дисковую подсистему и операциях, где может происходить потеря производительности.

• операции чтение страниц с файла данных для загрузки их в Буферный пул;
• операции записи данных в файл журнала транзакций при завершении (Commit)  транзакций;
• операции записи страниц в файл данных для фиксации произведенных модификаций.

Операции чтение страниц с файла данных для загрузки их в Буферный пул

Несмотря на то, что SQLServer: Buffer Manager: %Buffer Cache Hit Ratio может составляет 98…99%%, однако существует вероятность, что необходимая вам для выполнения запроса строка окажется на диске, и потребуется две операции ввода – физическая (время выполнения которой может быть до десятков миллисекунд), и логическая (с временем выполнения несколько десятков нано…микросекунд). Как видно разница между микро и мили 1 000 раз, а нано и мили 1 000 000 раз!!! Отсюда становиться ясно, что даже при огромных размерах страничного кэша (Buffer Pool), достаточно одной строчки, чтобы испортить радужную картину. Конечно не все так ужасно, как я описал, поскольку существует и работает множество алгоритмов для минимизации данной проблемы, но реально, все же, это возможно и такие задержки действительно возникают.

Примечание: Вообще, строго говоря, счетчик SQLServer: Buffer Manager: %Buffer Cache Hit Ratio не может использоваться как индикатор недосттка памяти и показатель достаточности размера буферного пула, поскольку на выполнение операции ReadAhead данный счетчик никак не реагирует, хотя при этом считывается огромные объемы данных. Связано такое поведение с тем, что данный счётчик учитывает только чисто физические операции чтения выполнение которых инициировано процессором запросов для загрузки страниц необходимых для выполнения данного запроса, и он никак не учитывает операции ReadAhead, которые по объему загружаемых с диска данных могут в разы превышать эти операции.

У нас (MSFT) есть пороговые значение на латентность операции чтения с дисков для SQL Server. Эта латентность меряется счетчиком Physical Disk: Avg. Disk sec/Read и не должна превышать 15 мсек. Если вы видите время задержки более этой величины и продолжительное по времени, то это может обозначать наличие дисковой проблемы.

Примечание: Далее рекомендуется посмотреть какое количество операций физического чтения делает ваш SQL Server и присутствуют ли необходимые индексы. Поскольку построив необходимые индексы вы уменьшаете количество физических чтений и, тем самым, снижаете нагрузку на дисковую систему, и, как следствие, уменьшаете латентность дисковых операций. Показания счетчика BufferManager:Page Reads/sec, должны быть не более 90…100, а соотношение SQLServer:Access Method: Index Searches/sec и SQLServer:Access Method: Full Scans/sec должно быть около или более 1000.

Операции записи данных в файл журнала транзакций при завершении (Commit)  транзакций

SQL Server подобно другим современным СУБД использует механизм упреждающей записи при модификации данных. Суть механизма состоит в том, что модифицированная запись не может появиться в файле данных, до тех пор, пока модификации не будут отображены в журнале транзакций. Особенно критично это для операции Commit, которая является сигналом для СУБД, что данная модификация выполнена успешно и доведена до конца. По сути латентность выполнения транзакций напрямую зависит от задержек связанных с появлением Commit записи в журнале транзакций. Запись в журнал транзакций производится через специальный внутренний буфер (Log Buffer), размер которого управляется SQL Server-ом. Временные задержки, возникающие при этом можно оценить с помощью счетчика SQL Server:Databases: Log Flush Wait Time и SQL Server:Databases: Log Flush Waits/sec. Разделив SQL Server:Databases: Log Flush Wait Time наSQL Server:Databases: Log Flush Waits/sec, мы получим среднее время ожидания при записи данных в журнал транзакций в миллисекундах.

У нас (MSFT) есть пороговые значение на латентность операции записи на диск для SQL Server. Эта латентность меряется счетчиком Physical Disk: Avg. Disk sec/Write и не должна превышать 15 мсек. Реально она должна быть менее этой величины  (поскольку операция записи подтверждается при попадании данных в кэш записи контроллера диска) и составлять 3…4 мсек (для SAN) и 8…10 мсек (для локально присоединенных дисков). Если вы видите время задержки более этой величины и продолжительное по времени, то это может обозначать наличие дисковой проблемы.

Операции записи страниц в файл данных для фиксации произведенных модификаций

Выполнение данного вида записи, как правило, не приводит к проблеме, поскольку эти операции производятся специальными Redo thread-ами в асинхронном режиме. К проблемам может стать ситуации, когда:

• производилась перестройка индексов и Redo thread-ы не успевают достаточно быстро записать модифицированные данные в файлы данных, отчего объекты с индексами не доступны.
• Redo thread на зеркальной (AlwaysOn) базе не успевает записать данные в файлы данных, отчего зеркало (AlwaysOn) не переходит в работоспособное состояние.
• Redo thread при восстановлении базы из резервной копии не успевает быстро записать данные в файлы данных, отчего база не переходит в работоспособное состояние.

Проблема с перестройкой индексов может быть решена путем выполнения Online Index Rebuilding.

Скорость работы REDO-thread зависит, в основном, от скорости работы дисковой подсистемы. Вы можете рассчитать какова текущая разница между данными, хранящимися в файле первично и вторичной реплик. Для этого вам необходимо Database Replica: Recovery Queue разделить на Database Replica: Redone Bytes/sec.

Ниже приведен пример одной из проблем заказчика, у которого возникали периодические проблемы с работой Log Shipping.

Итак начнем.

Сначала посмотрим на основные счетчики системы и начнем мы с дисков, поскольку алгоритм Log Shipping устроен таким образом, что при его работе активно используются именно диски.

Обратите внимание, что в некоторые моменты времени латентность дисковых операций составляет 91 секунду, что 6 000 раз более максимально допустимого значения для SQL Server (15 мсек).

Необходимо понять, что является причиной таких огромных задержек дисковых операций. Для этого попробуем проанализировать отдельно счетчики Avg. Disk sec/Read и Avg. Disk sec/Write. Как мы знаем латентность операций записи должна быть в несколько раз меньше латентности операции чтения, поскольку подтверждение операции записи приходит с кэш-контроллера, а чтения после переноса данных с физических носителей в буферы сервера.

Однако это не так, как видно из рисунка оба вида задержек почти однопорядковые. Отсюда мы можем сделать вывод, что перегружена вся дисковая система.
В данном случае мы имеем дело с локально присоединенными дисками (Local Attached Storage), в качестве которой используется PowerVault MD3000 Direct Attached Storage. Стойка состоит из 20-ти (10 000 rpm) дисков объединенных в RAID 10. Т.е. количество информационных дисков в данном случае 10. Исходя из расчетной скорости передачи одного диска: MAX 10 000/60 = 166 IOPS (при полностью последовательном чтении) и MIN 10 000/60/2 = 83 IOPS.(при полностью произвольном чтении) получаем среднюю скорость передачи таких дисков около 110…120 IOPS. Отсюда получаем производительность стойки из 10 дисков равную MAX 10х166 = 1660 IOPS и MIN 10х83 = 830 IOPS.
Давайте же посмотрим какое количество IOPS отправляется на стойку.

Как видно из рисунка количество операций ввода/вывода превышает возможности дисковой подсистемы. Если это так, то на входе стойки должны скапливаться очередь.

И это действительно так. Как видно из рисунка ниже очередь действительно есть.

В среднем она составляет 111, максимум 255. Очередь 111 превышает норму для локально присоединенных дисков в 5 раз (норма составляет не более чем 2 умножить на  количество информационных дисков, т.е. в данном случае очередь должна быть не более 20). Очередь же 255 указывает на полное блокирование контроллера дисковой стойки, и о его не способности обрабатывать такой поток данных. Очередь 255 говорит о том, что дисковый стек Windows полностью загружен IRP пакетами и они более не передаются на контроллер, поступление новых пакетов от приложения отвергается ОС.

Примечание: Анализ дисковых систем на основе очередей применим лишь для локально присоединенных систем и не может применяться для SAN (Storage Area Network), поскольку там не возможно определить количество дисков задействованных в операциях ввода/вывода. 

Возникает справедливый вопрос: Неужели при таких задержках нет никаких записей в журналах? Да они есть.

Это строки из журнала Application

BackupDiskFile::CreateMedia: Backup device ‘E:\backup_tmp\TransactionLogShipping\Приложение_службы_поиска_DB\Приложение_службы_поиска_DB_36993c336ce14e99a678fd3a52f4dd3f_20140305014550.trn’ failed to create. Operating system error 1117(failed to retrieve text for this error. Reason: 15105).

Ошибка 1117 означает “Запрос не может быть выполнен из-за ошибки ввода/вывода.” Данная ошибка являются индикатором того, что существует проблема с дисками.

Отсюда рекомендации заказчику: “Непременно и срочно заняться своей дисковой подсистемой”.

Давайте выполним еще одно упражнение. Попробуем понять какой из процессов ОС “съедает” все ресурсы. Для этого мы воспользуемся счетчиком

Как показал анализ счетчика Process(*):IO Data Bytes/sec наибольшее количество операций ввода/вывода порождает SQL Server. Более того, его активность по времени точно совпадает с дисковыми проблемами. Следовательно, можно предположить, что именно этот процесс и является основной причиной дисковых перегрузок.

Как видно из рисунка, в некоторые моменты времени количество операций ввода/выводе, генерируемое данным процессом достигает 455 000 000, что очень много даже для мощных дисковых систем.

Заключение

Методика анализа, показанная выше основана на критической ситуации с дисками, но она позволяет осветить все основные подходы при анализе дисковой подсистемы.

Александр Каленик, Senior Premier Field Engineer (PFE), MSFT (Russia)