Убивают ли задержки ввода-вывода вашу производительность?

Автор: Paul Randal, Are I/O latencies killing your performance?

В этой статье я объясняю некоторые методы исследования и снижения задержек ввода-вывода для tempdb и журнала транзакций, которые могут серьёзно препятствовать производительности вашей рабочей нагрузки.

Я запускал опрос, попросив выполнить код для расчёта средних задержек ввода-вывода и отправить мне результаты и получил результаты с 1094 случайных серверов по всему миру, на которых размещено 25445 баз данных, и мне потребовалось некоторое время, чтобы загрузить все результаты в SQL Server для агрегации данных. Вот они.

Что хорошо, а что плохо?

Для всего, о чём мы говорим, необходимо учитывать две вещи:

• Что является хорошей или плохой задержкой ввода-вывода?
• Даже если у вас «плохая» задержка ввода-вывода, волнует ли вас это?

У каждого есть своё представление о том, что означает хорошую или плохую задержку ввода-вывода, и вот моё мнение:

• Отлично: < 1 мс
• Очень хорошо: < 5 мс
• Хорошо: 5 – 10 мс
• Плохо: 10 – 20 мс
• Очень плохо: 20 – 100 мс
• Шокирующе плохо: 100 – 500 мс
• Невероятно! > 500 мс

Возможно, вы захотите поспорить, что мои цифры слишком высоки, слишком низки или неверны, основываясь на том, что вы видели/испытывали, и это нормально, но это мой блог, и это мои цифры :-) Возможно, вы не сможете достичь моих показателей «Отлично» или «Очень хорошо», если не используете новейшее локально подключённое флеш-хранилище и не нагружаете его интенсивно своей рабочей нагрузкой. Помните, это всего лишь мои рекомендации, а не какое-либо правило, обязательное для SQL Server.

Даже если ваша задержка ввода-вывода находится в категории, которую я считаю «плохой», ваша рабочая нагрузка может работать в приемлемых для вашей организации и ваших клиентов пределах, и вы готовы с этим мириться. Я согласен с этим, если вы осведомлены о своих задержках ввода-вывода и сознательно решили принять их такими, какие они есть. Незнание своих задержек ввода-вывода и простое принятие производительности рабочей нагрузки как данности, потому что «это просто такая производительность», для меня неприемлемо.

Перейдём к данным опроса…

Файлы данных tempdb

Для этих данных я рассчитал среднюю задержку чтения и записи по всем файлам данных tempdb для каждого экземпляра. Я не заметил экземпляров, где один файл данных tempdb имел бы огромную задержку по сравнению с другими, поэтому считаю этот подход корректным.

TempdbAvgRead

Честно говоря, задержки чтения файлов данных tempdb не так уж плохи: более 93% всех серверов в опросе имеют задержки чтения менее 20 мс.

TempdbAvgWrite

Это очень интересно – почти 42% всех серверов в опросе имели среднюю задержку записи файлов данных tempdb более 20 мс, и чуть более 12% всех серверов имели среднюю задержку записи файлов данных tempdb более полусекунды на запись – это абсурдно высоко!

Честно говоря, я довольно шокирован этими результатами, особенно относительно высоким числом серверов со средними задержками записи в несколько секунд для tempdb.

Итак, если вы проверите среднюю задержку ввода-вывода для tempdb (используя скрипт, например, тот, о котором я писал в блоге, с использованием sys.dm_io_virtual_file_stats) и обнаружите, что она действительно высока по моей (или вашей) шкале, что вы можете сделать?

Что ж, я могу предложить четыре подхода:

1. Не проводить никакого расследования и просто немедленно переместить tempdb на более быструю подсистему ввода-вывода, например, на две SSD-карты в конфигурации RAID-1 (помните, один SSD — это RAID-0, и это недостаточно хорошо, потому что если tempdb повреждён или недоступен, ваш экземпляр завершает работу!). Вы можете подумать, что это ленивый подход, но если вы знаете, что не можете внести изменения в рабочую нагрузку, это может быть вашим единственным вариантом. Я столкнулся с этим сценарием, работая с клиентом, который поставляет программное обеспечение для казино. Оптимальным решением было изменить некоторые хранимые процедуры для уменьшения использования tempdb, но это должно было занять 18 месяцев на утверждение игровой комиссией в штате, где находился клиент моего клиента. Единственное решение на данный момент? Раскошелиться на более быструю подсистему ввода-вывода для tempdb.
2. Исследовать подсистему ввода-вывода, где расположен tempdb, обращая внимание на такие вещи, как (неисчерпывающий список):
   • Проблемы с маршрутизацией/сетью, например, наличие коммутатора 1 Гбит/с на пути с пропускной способностью 4 Гбит/с или несовпадающие настройки jumbo frame в конфигурации iSCSI, или когда сеть к SAN перегружена чем-то, кроме трафика ввода-вывода SQL Server.
   • Неправильные настройки SAN, например, отключённое кэширование записи для рабочей нагрузки с интенсивной записью, неправильная глубина очереди по сравнению с рекомендациями производителя SAN или tempdb, застрявший на медленном хранилище в авто-уровневой SAN из-за неправильного «обучения» SAN.
   • Множество пользователей части подсистемы ввода-вывода, где расположен tempdb, например, tempdb, объединённый с другими изменчивыми базами данных, или даже LUN, разделяемые между SQL Server и другими приложениями, такими как Exchange или IIS.
   • Наличие только одного файла данных tempdb, из-за чего теряется обычная более высокая производительность подсистемы ввода-вывода, достигаемая за счёт наличия нескольких файлов данных в файловой группе (примечание: не путайте это с добавлением большего количества файлов данных tempdb для уменьшения конкуренции PAGELATCH_XX над битовыми картами выделения в памяти).
3. Попытаться уменьшить использование tempdb, ища (неисчерпывающий список):
   • Предупреждения о сбросе (spill warnings) в планах запросов (хэш, сортировка или обмен), указывающие на то, что не хватило памяти выполнения запроса и оператору пришлось сбросить результаты в tempdb. См. эти сообщения в блоге для получения дополнительной информации: здесь, здесь, здесь и моё сообщение в блоге, объясняющее, как понять использование данных и журнала при сбросе памяти в tempdb здесь.
   • Некорректное, чрезмерное использование временных таблиц, например, всегда использование временной таблицы, когда иногда лучше этого не делать, выборка большего количества столбцов или строк во временную таблицу, чем действительно необходимо (например, SELECT * во временную таблицу из пользовательской таблицы без предложения WHERE), создание некластерных индексов на временной таблице, которые не используются. Я видел это снова и снова в клиентском коде.
   • Перестроение индексов с использованием SORT_IN_TEMPDB.
   • Использование одного из вариантов изоляции снимков и разрешение долго выполняющихся запросов, которые приводят к значительному росту хранилища версий.

   Существует множество полезных запросов и другой информации в техническом документе Working with tempdb in SQL Server 2005 (который всё ещё применим ко всем текущим версиям).

4. Комбинация пунктов 2 и 3, и, возможно, вам просто придётся перейти на более быструю подсистему ввода-вывода, как в пункте 1.

Ещё один момент, который следует учитывать, — это риск внесения изменений в ваш код и/или стоимость инженерных усилий (разработка и тестирование) для этого. Может быть дешевле и менее рискованно перейти на более быструю подсистему ввода-вывода. Решать вам. Другая проблема, с которой вы можете столкнуться, заключается в том, что плохой код находится в стороннем приложении, над которым вы не имеете контроля. В этом случае у вас может не быть выбора, кроме как «закидать» проблему аппаратным обеспечением.

Файлы журнала транзакций

Для этих данных я рассматривал каждую базу данных отдельно, а не агрегировал по экземпляру.

LogReadAvg2

LogWriteAvg2

Для журнала транзакций вы действительно хотите, чтобы средняя задержка записи находилась в диапазоне 0–5 мс, и приятно видеть, что более 79% файлов журнала транзакций в опросе достигают этого. Я бы сказал, что задержка записи для журнала транзакций гораздо важнее, чем задержка чтения, поскольку задержка записи замедляет транзакции в вашей рабочей нагрузке. Это не значит, что вы должны игнорировать высокие задержки чтения, так как они замедляют «читатели» журнала (такие как резервное копирование журнала, транзакционная репликация, отслеживание изменённых данных, асинхронное зеркалирование баз данных/группы доступности), но задержки чтения журнала обычно не замедляют вашу рабочую нагрузку, если только у вас нет транзакций, которые выполняют откат (единственный случай, когда транзакции вызывают чтение журнала), или вы сильно полагаетесь на отслеживание изменённых данных.

Итак, вы сосредоточены на задержке записи. Опять же, существует несколько подходов, аналогичных пунктам 1–4 выше. Однако в подходе 3 вы ищете другое. Я написал несколько подробных статей о настройке производительности журнала транзакций (включая уменьшение объёма генерируемого журнала и изменение шаблона сброса блоков журнала) для блога SQL Sentry’s SQLPerformance.com, поэтому вместо дублирования я просто укажу на них:

• Trimming the Transaction Log Fat – общие проблемы, приводящие к низкой производительности журнала транзакций.
• Trimming More Transaction Log Fat – более тонкие проблемы, способствующие низкой производительности журнала транзакций.
• Transaction Log Configuration Issues – пояснение.

Резюме

Очень важно обращать внимание на задержки чтения и записи ввода-вывода для критических компонентов вашей среды SQL Server: tempdb и журналов транзакций. Я предоставил вам много информации выше о том, что можно сделать внутри и вне SQL Server для снижения этих задержек и увеличения пропускной способности рабочей нагрузки.

Если у вас нет выбора, не просто добавляйте SSD-диски, не выяснив предварительно, можно ли что-то сделать внутри SQL Server для снижения нагрузки ввода-вывода. А если вы всё же добавляете SSD-диски, убедитесь, что вы используете их для размещения файлов, которые являются вашим самым большим узким местом ввода-вывода, и используйте как минимум два из них в конфигурации RAID-1 для защиты от сбоев.

Надеюсь, это было полезно – удачной настройки!