Производительность дисковой подсистемы и SQL Server 2000

[Sergey Petrov]

Уважаемые господа специалисты!
Мы пользуемся Navision Axapta CIS SP3 в двухзвенной реализации. Имеется порядка 80 пользователей, база 40 Гб данных, столько же индексов. Сервер базы данных на платформе SPSH4 (Shasta), 4 процессора Xeon MP 2200 2Гб L3 cache на 400 MHz системной шине без Hyper Threading. Память 12 Гб. Дисковая система: двухканальный RAID-контроллер LSI AcceleRAID 320-2 (на одном канале корзина с 5 дисками Seagate Cheetah 10K.6 (ёмкость 146.8 Gb, скорость 10000 rpm), на другом канале корзина с 5 дисками Seagate Cheetah 15K.4 (ёмкость 36.7 Gb, скорость 15000 rpm); в первой корзине 4 диска в RAID-10 массив (на нём основные данные), 1 оставшийся под систему и SQL Server со служебными базами; во второй корзине 4 диска в RAID-10 (на нём дополнительный файл данных)) и 1 оставшийся под логи. Stripe Size = 64 K, Cluster size (в NTFS) = 64К. Сеть гигабитная. Задействован один сетевой адаптер. ОС Windows 2003 Enterprise Server SP1 v.1433. Сервер базы данных MS SQL Server 2000 SP4. Поддержка AWE в SQL Server, /PAE, /3GB.
Суть проблемы: тормоза SQL Servera, судя по всему из-за низкой производительности дисковой подсистемы. => 1) как можно следить за работой RAID-контроллера и дисковых массивов (нет ли аппаратных проблем); 2)можно ли доверять счётчикам стандартного Performance Monitor из Win2K3 по разделу Logical Disk, какие параметры нужно отслеживать и какие пороговые значения для этих параметров; 3)может ли помочь дефрагментация данных логических томов, размещённых на дисковых RAID-массивах?
Предыстория вопроса - http://www.3nity.ru/viewtopic.htm?t=5134 (с загрузкой процессора справились путём оптимизации кода приложения - теперь вроде с этим всё в порядке).

[gs]

Уточните плиз модель контроллера. Mylex/LSI AcceleRAID352 или LSI MegaRAID320-2? Или 320-2Х?
Конфигурация дисковой у Вас имхо радикально неверная - лучше бы на массив из быстрых дисков положить основную базу. Одиночные диски с точки зрения надежности - вообще швах.

Посмотрите перфмоном дисковые счетчики reads/sec, writes/sec, queue lenght отдельно по всем томам. Первые два счетчика для оценки нагрузки, третий - индикатор "успевает-не успевает дисковая" (его значение в идеале должно быть 0, но допустимо в пределах единиц - хотя это уже притормаживание). Перфмон как правило не врет.

[gs]

Дефрагментация на рэйде дает мизерный прирост.

Сколько кэша на контроллере и включен ли райт бэк?

[gs]

Да, смотреть не лоджикал диск, а физикал, выбрав там разные тома.

[Sergey Petrov]

Контроллер у нас LSI MegaRAID320-2 (без X). Кэш 128 Mb, write back включён, cashed IO тоже. На медленных дисках основные данные, так как места маловато на быстрых (сейчас на массиве из медленных дисков Used Space = 245 Gb, Free Space = 28,6 Gb).
С точки зрения безпасности - данные у нас на 10-м рэйде, а логи усекаются постоянно (конфигурация скуля). При имеющемся количестве и параметрах дисков в системе - эта конфигурация оптимальна.
По дисковым чтениям и записям: записей под 100 на проблемном томе, чтений - порядка 800, очереди - порядка 30.
Насчёт логических и физических дисков - объясните, пожалуйста, в чём разница?

[gs]

В перфмоне есть два счетчика - logical disk и phisical disk. Наверно можно и лоджикал смотреть, но мы привыкли физикал. Так нам просто понятнее

Данные перфмона говорят о том, что дисковая не успевает (я понимаю, что очередь 30 - это в среднем? Если пиковая 30 - это плохо, если средняя - это жопа). Ну и ридс + райтс под 1000 на четырех винтах - это предел. Т.е. дисковая работает изо всех сил и не успевает за пройессорами.

Рекомендации такие:
1. Бюджетно. Набить полный ящик одинаковых 15к дисков, поставить контроллер 320-2Х с кэшем 512МБ и все в рэйд5, порезав на луны по нуждам. Это максимум, что можно сделать не влезая в расходы.
2. Правильно. Внешняя дисковая система типа HDS AMS200 с ящиком (а лучше двумя) винтов. Как более слабые варианты - IBM DS4300 или FC-FC Infortrend. Это дороже, но правильнее со всех точек зрения. Во первых радикально повысит надежность, во вторых даст возможность масштабироваться в будущем (на годы вперед).

[gs]

Кстати, можно Вас попросить снять еще данные счетчиков read bytes/sec и write bytes/sec? Совсем хорошо отдельно во время проведения транзакций и отчетов. Это нам в плане сбора статистики для более внятных рекомендаций другим пользователям. Буду очень благодарен.

[Sergey Petrov]

Большое спасибо за оперативный ответ и комментарии!
Насчёт "бюджетно" и "правильно" - мы "будем думать".
Насчёт счётчиков - оперативные отчёты у нас создаются в основном в течение рабочего дня. Транзакции тоже не только по ночам (днём в основном). Думаю, Вас удовлетворят данные, которые мне удастся собрать за затрашний рабочий день?

[gs]

Конечно. Мы пытаемся собрать статистику по работе разных приложений с дисками - чтобы более внятно рекомендации давать. А то в этом деле промах часто сильно дорого обходится - как у Вас например

[Sergey Petrov]

Так, для информации - не могли бы Вы срообщить, пороговые значения для read\sec и write\sec в нашей конфигурации?

[gs]

Ну так Вы видите как раз предельные значения. Наличие очереди означает, что дисковая достигла максимума своих возможностей, быстрее плеваться уже не в состоянии и команды выстраиваются в очередь к диску. А процессоры курят бамбук (кстати в таск менеджере при этом вполне может быть 100% загрузка - просто процы молотят такты ожидания IO ).

[Vadik]

По дисковым чтениям и записям: записей под 100 на проблемном томе, чтений - порядка 800, очереди - порядка 30

"Размазывайте" нагрузку с него на остальные массивы (неясно, правда, что на них сейчас творится). Если ввод-вывод неравномерно распределен (обычная причина - "не угадали", разбивая БД по файловым группам, при 10 дисках, да еще разнокалиберных, вообще imho не стоило это затевать), дышать станет полегче, но все равно не панацея
Озвучьте
- нагрузку (reads, writes, avg. queue length) по всем массивам
- свободное место на массивах
- как сейчас побита tempdb
- используете ли файловые группы
- результаты dbcc sqlperf(waitstats)

возможно, что-нибудь да придумается

P.S. я правильно понял, что размер БД уже 80 гигабайт?

[Sergey Petrov]

Нагрузку "размазали" уже давно, две самые большие и нагруженные таблицы у нас в другой файловой группе на другом дисковом массиве (который меньше, но быстрее). Само собой, дисковый массив, на котором лежит основное количество таблиц (коих в приложении 906), испытывает бОльшие нагрузки по сравнению со вторым.
На большом, медленном и загруженном массиве свободно чуть больше 10% дискового пространства. На меньшем - больше 2/3.
Данные по счётчикам выложу сегодня ближе к вечеру (для полноты картины).
Ещё один вопрос - хоть в нашей системе и начинает сильно преобладать чтение над записью, но есть одна таблица, с которой постоянно производятся операции вставки-редактирования-удаления. Как на ней лучше настроить параметр fill factor для индексов? Сейчас стоят нули. Среднее значение page split/sec для сервера около 2. СтОит ли вообще заморачиваться?
По waitstats:
Wait Type    Requests     Wait Time             Signal wait time
MISCELLANEOUS 34.0 0.0 0.0
LCK_M_SCH_S 106.0 7.6138128E+7 40424.0
LCK_M_SCH_M 13.0 2.409358E+7 16.0
LCK_M_S 1224.0 2.7600186E+8 2582.0
LCK_M_U 14965.0 4.3805043E+8 19235.0
LCK_M_X 583.0 1744621.0 582.0
LCK_M_IS 194.0 1.9079072E+7 7909.0
LCK_M_IU 2777.0 7.3213872E+7 2380.0
LCK_M_IX 478.0 4.098848E+7 1047.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 1839576.0 7.0147059E+8 7.0137363E+8
IO_COMPLETION 2812379.0 1.462788E+7 70546.0
ASYNC_IO_COMPLETION 81.0 2984.0 0.0
RESOURCE_SEMAPHORE 224.0 1809226.0 0.0
DTC 0.0 0.0 0.0
OLEDB 5364893.0 2.5284861E+8 4.0172636E+9
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 8923831.0 2.094241E+9 7.001888E+8
ASYNC_DISKPOOL_LOCK 546.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 42.0 10827.0 2046.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 4365967.0 1.9332E+7 1215581.0
LOGBUFFER 1004.0 120161.0 63.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 0.0 0.0 0.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 1.0 16.0 16.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 3107.0 424.0 424.0
CXPACKET 0.0 0.0 0.0
PAGESUPP 0.0 0.0 0.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 0.0 0.0 0.0
CURSOR 0.0 0.0 0.0
EXECSYNC 0.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 143.0 31.0 16.0
LATCH_UP 1588.0 510988.0 390.0
LATCH_EX 3440.0 5610.0 93.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 6.0 0.0 0.0
PAGELATCH_SH 6502378.0 736181.0 225639.0
PAGELATCH_UP 298682.0 196861.0 136979.0
PAGELATCH_EX 8783369.0 847786.0 744413.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 1.6625958E+8 3.2619651E+9 1.8549572E+7
PAGEIOLATCH_UP 23422.0 848499.0 2072.0
PAGEIOLATCH_EX 5723738.0 1.4723939E+8 352188.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 700281.0 5.9791852E+7 0.0
Total 2.1162867E+8 7.5059164E+9 5.4402007E+9

PAGEIOLATCH_SH - лидер!

[Sergey Petrov]

tempdb лежит на дисковом массиве с основной базой вместе, разбита на 4 файла данных по 2 Гб каждый (по числу процессоров).

[Sergey Petrov]

Файловых групп 5 штук, причём то, что лежит на разных массивах, относится к разным файловым группам.