Автор: Paul Randal, How to examine IO subsystem latencies from within SQL Server
За последние несколько месяцев я читал лекции на курсах и конференциях о том, как получить представление SQL Server о подсистеме ввода-вывода и какие задержки она испытывает, так что пришло время написать об этом в блоге, чтобы помочь всем остальным.
Большинство современных SQL Server чувствительны к вводу-выводу — это общепризнанное утверждение среди администраторов баз данных и консультантов в этой области. Это означает, что основным фактором производительности сервера является его способность быстро выполнять операции ввода-вывода. Если подсистема ввода-вывода не справляется с предъявляемой к ней нагрузкой, то рабочая нагрузка SQL Server будет страдать от проблем с производительностью.
При этом одна из ловушек, в которую часто попадают многие, — это приравнивание повышенных задержек подсистемы ввода-вывода к плохой производительности самой подсистемы. Часто это совсем не так. Обычно подсистема ввода-вывода работает нормально при расчётной нагрузке, но становится узким местом производительности, когда нагрузка ввода-вывода превышает расчётную точку. Увеличение нагрузки ввода-вывода является причиной проблемы, а не подсистема ввода-вывода, — если вы спроектировали подсистему ввода-вывода для поддержки 10000 IOPS (операций ввода-вывода в секунду — убедившись, что вы используете правильный размер ввода-вывода и характеристики нагрузки имеют смысл в терминах количества случайных IOPS), а SQL Server пытается выдать 20000 IOPS, производительность будет страдать.
Если вы обнаружили, что задержка ввода-вывода увеличилась, ищите изменение в поведении SQL Server, прежде чем винить подсистему ввода-вывода. Например:
- Изменения планов запросов из-за устаревшей статистики, изменений в коде, неявных преобразований, плохого индексирования, вызывающих просмотры таблиц вместо поиска по индексу
- Создание дополнительных индексов, вызывающее увеличение нагрузки на обслуживание индексов и журналирование
- Изменение шаблонов доступа/ключей, вызывающее разбиения страниц и, следовательно, дополнительные чтения/записи страниц и журналирование
- Включение системы отслеживания изменений (change data capture или change tracking), вызывающее дополнительные записи и журналирование
- Включение изоляции снимков (snapshot isolation), вызывающее операции ввода-вывода в
tempdb, а также потенциально разбиения страниц - Уменьшение памяти сервера, приводящее к меньшему буферному пулу и увеличению активности lazy writer и чтения
И целый ряд других причин может привести к увеличению нагрузки ввода-вывода, где подсистема ввода-вывода не виновата.
С другой стороны, однако, вполне возможно, что проблема именно в подсистеме ввода-вывода, если рабочая нагрузка SQL Server осталась прежней. Администратор SAN мог решить отдать часть пространства на одном из LUN SQL Server другому серверу, что может привести к перегрузке.
Используя счётчики производительности, вы можете увидеть на уровне физического диска, каковы задержки (показатели Avg. Disk sec/Read и Avg. Disk sec/Write), но если ваши базы данных распределены по нескольким LUN, это не поможет вам определить, какие файлы базы данных испытывают проблемы с задержками и генерируют наибольшее количество операций ввода-вывода к подсистеме.
sys.dm_io_virtual_file_stats
Здесь на помощь приходит sys.dm_io_virtual_file_stats. Оно появилось аж в SQL Server 2005 как улучшенная замена fn_virtualfilestats и показывает, сколько операций ввода-вывода было выполнено, с задержками для всех файлов. Вы можете передать ему идентификатор базы данных и идентификатор файла, но я считаю наиболее полезным просматривать все файлы на сервере и сортировать по одному из показателей.
Вот стандартный вывод (с некоторыми незначительными изменениями названий столбцов для удобства отображения):
SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);
GO
db_id file_id sample_ms reads bytes_read io_stall_read_ms writes bytes_written io_stall_write_ms io_stall size_on_disk_bytes file_handle
----- ------- --------- ----- ---------- ---------------- ------ ------------- ----------------- -------- ------------------ ------------------
1 1 14433009 52 3227648 58 10 90112 12 70 515964928 0x000000000000013C
1 2 14433009 55 1241088 36 28 122880 115 151 12648448 0x0000000000000474
2 1 14433009 22 1261568 22 4 147456 1 23 8388608 0x000000000000065C
2 2 14433009 6 385024 2 7 154112 3 5 524288 0x0000000000000660
3 1 14433009 31 1859584 18 1 8192 0 18 1310720 0x0000000000000524
3 2 14433009 6 385024 5 3 16384 1 6 524288 0x000000000000056C
4 1 14433009 139 8175616 369 13 106496 45 414 260767744 0x0000000000000958
4 2 14433009 136 712704 485 7 53248 25 510 104595456 0x00000000000009F0
5 1 14433009 20 1138688 204 0 0 0 204 419430400 0x0000000000000980
5 2 14433009 13 411136 466 1 2048 13 479 104857600 0x0000000000000A44
6 1 14433009 15 811008 81 1 8192 8 89 1310720 0x0000000000000954
6 2 14433009 7 386560 89 4 11264 80 169 516096 0x00000000000009EC
7 1 14433009 15 811008 80 1 8192 9 89 1310720 0x0000000000000970
7 2 14433009 7 386560 130 3 9216 17 147 516096 0x00000000000009B4
8 1 14433009 16 876544 37 1 8192 103 140 136577024 0x0000000000000988
8 2 14433009 9 229888 11 4 10752 59 70 5242880 0x00000000000009E4
.
.Это не очень полезно, потому что: а) я не помню наизусть идентификаторы баз данных и пути к файлам, и б) он показывает совокупные задержки (io_stall_read_ms и io_stall_write_ms).
Просмотр агрегированной информации
Обычно я использую скрипт ниже — часть моего стандартного набора скриптов, которые я использую при проверке работоспособности сервера для клиента. Он частично основан на коде моего хорошего друга Джимми Мэя (Jimmy May), с рядом доработок. Он позволяет мне фильтровать по задержкам чтения или записи и объединяется с sys.master_files для получения имён баз данных и путей к файлам.
SELECT
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- файлы журналов
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO
ReadLatency WriteLatency Latency AvgBPerRead AvgBPerWrite AvgBPerTransfer Drive DB physical_name
----------- ------------ ------- ----------- ------------ --------------- ----- ----------------------- ------------------------------------------
2 103 8 54784 8192 52043 D: HotSpot D:\SQLskills\HotSpot_data.mdf
7 48 13 63061 5632 54857 C: DemoCorruptSystemTables C:\SQLskills\corrupt_log.LDF
32 41 33 43861 2048 39680 D: DemoSystemIndex3 D:\SQLskills\DemoSystemIndex3_LOG.ldf
27 40 28 43861 1024 39577 D: DemoSystemIndex5 D:\SQLskills\DemoSystemIndex5_LOG.ldf
23 39 24 43861 7168 40192 D: DemoSystemIndex4 D:\SQLskills\DemoSystemIndex4_LOG.ldf
7 38 10 43861 4608 39936 D: DemoSystemIndex1 D:\SQLskills\DemoSystemIndex1_LOG.ldf
11 25 12 43861 7168 40192 C: FSWaits C:\SQLskills\FSWaits_log.ldf
12 20 13 55222 6656 49152 C: newdatabase C:\SQLskills\newdatabase_log.LDF
12 20 15 55222 2816 36165 C: LockResDemo C:\SQLskills\LockResDemo_log.LDF
14 17 15 45933 2816 30254 C: TempdbTest C:\SQLskills\TempdbTest_log.LDF
1 14 5 25543 2688 18510 C: HotSpot C:\SQLskills\HotSpot_log.ldf
40 14 39 31625 7680 29915 D: RecompileTest D:\SQLskills\RecompileTest_log.ldf
9 13 10 51858 7680 46336 D: DemoFatalCorruption1 D:\SQLskills\DemoFatalCorruption1_log.ldf
35 13 34 31625 2048 29513 D: GUIDTest D:\SQLskills\GUIDTest_log.ldf
12 13 12 16861 1024 15872 C: FNDBLogTest C:\SQLskills\FNDBLogTest_log.LDF
.
.Это гораздо полезнее, так как позволяет быстро увидеть, где находятся «горячие точки» чтения и записи, а затем углубиться в базу данных, чтобы понять, что происходит, и, если ничего необычного не обнаружено, попросить администратора SAN переместить эти файлы с «горячими точками» на выделенные и/или более быстрые хранилища.
Использование этой информации
Предостережение: Эта информация является агрегацией всех операций ввода-вывода, выполненных с момента ввода базы данных в эксплуатацию, поэтому время ввода-вывода, сообщаемое скриптом, является средним. Чем дольше охватываемый период, тем более вероятно, что кратковременные всплески задержек будут замаскированы. Лучший способ использовать эти данные для анализа — периодически захватывать снимки данных sys.dm_io_virtual_file_stats, а затем анализировать средние значения за этот небольшой период времени. Таким образом, вы сможете увидеть всплески задержек.
Один вопрос, который вы, я уверен, зададите: «какая задержка чтения или записи является приемлемой?» — и ответ — большое «зависит!». Это зависит от подсистемы ввода-вывода и того, как она настроена. Ключевым моментом является создание базового показателя производительности для периода, когда всё работает приемлемо хорошо, и затем сравнение результатов из DMV с вашим базовым показателем. На хорошо настроенном хранилище, которое не перегружено, я ожидал бы увидеть задержки в одну цифру (миллисекунды) как для чтения, так и для записи, но это будет варьироваться в зависимости от технологии дисков (SCSI против SATA против SSD и т. д.).
Комментариев нет:
Отправить комментарий