Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Добрый день !!!
Беда случилась :( уже 3тью неделю на сервере
Microsoft SQL Server 2000 - 8.00.2282 (Intel X86) Dec 30 2008 02:22:41 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
почти на порядок увеличился параметр locks request/sec, стали появляться дедлоки и очень большие периодические скачки по total wait time(ms) для блокировок. Блокируються всё время разные таблицы, ключи и прочие структуры в БД, висят в ожидании всё время разные процедуры. Очередь к диску Иногда сервер героически вылезает сам из накопившихся жёстких блокировок при их эсколации, а иногда просто встаёт колом. Конкретной причины я выявить так и не смог, начинаю грешить на встроенный в материнку контроллер райд массива, он ну уж очень конченный судя по отзывам
supermicro x7-db3
.

В чём собственно заключаеться вопрос: как и по каким счётчикам или другим аналитическим данным со скриптов и прочее мне окончательно в это уверовать, а свести к минимуму возможность выдачи неправильного диагноза ??? А вдруг не в этом дело, а вдруг всё таки структура БД просто разраслась и при кривой реализации просто плохо стала шевелиться ??? А вдруг диверсия и drop индексов ??? Как исключить остальные "а вдруг" ???
25 дек 12, 12:51    [13682016]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
stavgreengo,

для начала определитесь с типом блокировок, прежде чем грешить на железо
25 дек 12, 13:01    [13682091]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3650
Ну профайлер и смотреть запросы, что и на чем тормозит, когда блокирует...
Может реально индекса не хватает. Даже больше, индекс может быть, но не стоит крыжек USE Index.
25 дек 12, 13:02    [13682103]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
WarAnt
stavgreengo,

для начала определитесь с типом блокировок, прежде чем грешить на железо

основное ожидание в ~90% PAGEIOLATCH_SH (постраничные блокировки) ~10% LCK_M_S (по ключу). Похож же на правду с глюками райд контроллера такой расклад ?
25 дек 12, 13:10    [13682193]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Ozerov
Ну профайлер и смотреть запросы, что и на чем тормозит, когда блокирует...
Может реально индекса не хватает. Даже больше, индекс может быть, но не стоит крыжек USE Index.

Имеете в виду использовать шаблон T-SQL:locks и фильтрануть по SID блокированного процесса ? Я правильно понял ?
25 дек 12, 13:12    [13682219]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
stavgreengo
основное ожидание в ~90% PAGEIOLATCH_SH (постраничные блокировки)
Это не постраничные блокировки, это ожидание завершения запроса от сервера к дисковому контроллеру.
stavgreengo
Похож же на правду с глюками райд контроллера такой расклад ?
Не обязательно, может просто нагрузка слишком большая для имеющейся дисковой подсистемы.

Вы посмотрите очередь, поток IO и задержки на операцию (в Windows Server 2008 и выше удобно пользоваться ресурс-монитором)
25 дек 12, 14:08    [13682840]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
stavgreengo
WarAnt
stavgreengo,

для начала определитесь с типом блокировок, прежде чем грешить на железо

основное ожидание в ~90% PAGEIOLATCH_SH (постраничные блокировки) ~10% LCK_M_S (по ключу). Похож же на правду с глюками райд контроллера такой расклад ?


C большой долей вероятности да, но чтобы убедиться в достоверности нужно еще проанализировать счетчики производительности самой дисковой подсистемы
25 дек 12, 14:48    [13683221]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Вот наглядность на моменте, когда серверу стало совсем не хорошо :( Диск D: это 4х дисковый на SAS 15K RAID 1+0 Оцените плиз IOP\s - это вообще допустимо ?
Картинка с другого сайта.
Картинка с другого сайта.
25 дек 12, 15:04    [13683393]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Забыл пароль
Guest
stavgreengo,

Смотрим табличку и сравниваем со своим Phys Reads / sec + Phys Writes / sec
25 дек 12, 17:26    [13684700]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Забыл пароль
Guest
Забыл пароль
stavgreengo,

Смотрим табличку и сравниваем со своим Phys Reads / sec + Phys Writes / sec


2-япопытка вложить файл?

К сообщению приложен файл. Размер - 42Kb
25 дек 12, 17:28    [13684718]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
stavgreengo
Вот наглядность на моменте, когда серверу стало совсем не хорошо :( Диск D: это 4х дисковый на SAS 15K RAID 1+0 Оцените плиз IOP\s - это вообще допустимо ?
IOP\s нормальные (я вижу 600 в табличке), но почему то большая задержка - 6000 мс
25 дек 12, 17:56    [13684924]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а никто "вдруг" убивать сервер не начал, случаем? реплику там стали инитить, к примеру, или бакапы вдруг решили всем базам сразу сделать и выложить на диск с данными?
а может индексы устарели / фрагментировались настолько, что сики в сканы превратились и теперь душат диск?
25 дек 12, 18:29    [13685072]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Crimean
а никто "вдруг" убивать сервер не начал, случаем? реплику там стали инитить, к примеру, или бакапы вдруг решили всем базам сразу сделать и выложить на диск с данными?
а может индексы устарели / фрагментировались настолько, что сики в сканы превратились и теперь душат диск?

не не не, с этим всё в порядке я за этим слежу.
alexeyvg
stavgreengo
Вот наглядность на моменте, когда серверу стало совсем не хорошо :( Диск D: это 4х дисковый на SAS 15K RAID 1+0 Оцените плиз IOP\s - это вообще допустимо ?
IOP\s нормальные (я вижу 600 в табличке), но почему то большая задержка - 6000 мс

Да, действительно...помониторил и заметил что когда нагрузка до 500 IOP\s то ожидание 2,5-2,8сек., что уже много :) при нагрузке > 500 IOp\s резко возрастает до 6-8сек. это вообще ужас. Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?
26 дек 12, 10:40    [13686930]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
42323423
Guest
stavgreengo
Crimean
а никто "вдруг" убивать сервер не начал, случаем? реплику там стали инитить, к примеру, или бакапы вдруг решили всем базам сразу сделать и выложить на диск с данными?
а может индексы устарели / фрагментировались настолько, что сики в сканы превратились и теперь душат диск?

не не не, с этим всё в порядке я за этим слежу.
alexeyvg
пропущено...
IOP\s нормальные (я вижу 600 в табличке), но почему то большая задержка - 6000 мс

Да, действительно...помониторил и заметил что когда нагрузка до 500 IOP\s то ожидание 2,5-2,8сек., что уже много :) при нагрузке > 500 IOp\s резко возрастает до 6-8сек. это вообще ужас. Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?


1 Может сделать архивацию данных!!!

2 Развернуть базу на другом сервере,потом усечь данные и посмотреть как будет все работать.
26 дек 12, 10:59    [13687060]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
42323423
stavgreengo
пропущено...

не не не, с этим всё в порядке я за этим слежу.
пропущено...

Да, действительно...помониторил и заметил что когда нагрузка до 500 IOP\s то ожидание 2,5-2,8сек., что уже много :) при нагрузке > 500 IOp\s резко возрастает до 6-8сек. это вообще ужас. Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?


1 Может сделать архивацию данных!!!

2 Развернуть базу на другом сервере,потом усечь данные и посмотреть как будет все работать.

1 Архивацию было бы сделать конечно хорошо, но тут сложности с точки зрения наших программистов, в подробности не вникал особо, но смысл в том что риск подводных грабель велик очень :)
2 Так наверное и придётся делать в последующим, был бы сервер и желание, но это долгосрочная перспектива, а проблему надо купировать как можно быстрее.
26 дек 12, 11:23    [13687210]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
stavgreengo
Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?
Может быть памяти не хватает для кеширования.

Ещё нужно трейсить, смотреть и оптимизировать запросы и характер работы приложения с базой.

Может, достаточно индекс сделать?

Это я делаю выводы исходя из того, что нагрузка в основном по чтению.
26 дек 12, 11:37    [13687339]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Crimean
Member

Откуда:
Сообщений: 13148
повторю вопрос. на основании чего был сделан вывод что в последнее время некоторые запросы не стали потреблять аномально много ресурсов? это раз. два. контролируете ли длинные транзакции? может кто злоупотребляет, скажем, xp_cmdshell и прочими обращениями к внешним сервисам из триггеров?
26 дек 12, 11:37    [13687345]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
stavgreengo
Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?
Кроме того, сколько там дисков и какие, какой рейд, какое распределение файлов?

Может, 500 IOP\s для них предел, независимо от контроллера.

Да, встроенные в мамы контроллеры обычно действительно не супер-производлительные, но вышесказанное тоже не нужно упускать из виду. Нужно хотя бы это исследовать.
26 дек 12, 11:39    [13687365]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
alexeyvg
stavgreengo
Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?
Может быть памяти не хватает для кеширования.

Ещё нужно трейсить, смотреть и оптимизировать запросы и характер работы приложения с базой.

Может, достаточно индекс сделать?

Это я делаю выводы исходя из того, что нагрузка в основном по чтению.

С памятью судя по мониторингу всё ОК. Трасу собираю уже, буду потом по шапкам раздавать :) Нагрузка OLTP и от экспериментов с индексами я отказался, поналепил в одну из самых могучих таблиц, а потом пользователи жаловались при операции удаления на тормоза ощутимые.
Crimean
повторю вопрос. на основании чего был сделан вывод что в последнее время некоторые запросы не стали потреблять аномально много ресурсов? это раз. два. контролируете ли длинные транзакции? может кто злоупотребляет, скажем, xp_cmdshell и прочими обращениями к внешним сервисам из триггеров?

Ну запросы и стали потреблять ресурсов больше, конец года, могучая отчётность, отсюда и проседание сервера на IOP\s по чтению пошёл. Хмм...а за идею с xp_cmdshell и прочими adhoc спасибо, действительно надо проверить, не смотрел пока.
alexeyvg
stavgreengo
Получается опять я крепчаю в мысли что виноват контроллер или ещё варианты есть ?
Кроме того, сколько там дисков и какие, какой рейд, какое распределение файлов?

Может, 500 IOP\s для них предел, независимо от контроллера.

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

RAID 1+0 из 4х SAS 15K - это вот как раз злополучный логический D: там база одним файлом mdf в 150Гб данных spaceused

to all:согласовал с начальством, скоро отключу репорт сервайс на часик и посмотрю как всё жить будет,о результатах отпишусь.
26 дек 12, 12:22    [13687811]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Crimean
Member

Откуда:
Сообщений: 13148
с памятью ой как непросто бывает. легкий дефицит отбивает охоту серверу использовать HASH стратегии, к примеру
а результате страдает диск. сильно. а по памяти вроде все окейно
с индексами и удалениями - крайне странно, сдается вы с прямым углом, местами, путаете. оно-то да, конечно, все модификации просядут, но чтобы настолько.. разве что крайне неселективные индексы и увеличение времени выполнения за счет ожиданий блокировок на них..
26 дек 12, 12:43    [13687990]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Забыл пароль
Guest
alexeyvg
stavgreengo
Вот наглядность на моменте, когда серверу стало совсем не хорошо :( Диск D: это 4х дисковый на SAS 15K RAID 1+0 Оцените плиз IOP\s - это вообще допустимо ?
IOP\s нормальные (я вижу 600 в табличке), но почему то большая задержка - 6000 мс


Нормальные 600 IOPS???
600 - это не нормально. Это максимум, который может выдать Ваш рейд (2 диска в страйпе + случайное I/O). С нормально работабщим контроллером.

Просто нет больше слов. Ухожу.
26 дек 12, 13:27    [13688370]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
Забыл пароль
Нормальные 600 IOPS???
600 - это не нормально. Это максимум, который может выдать Ваш рейд
Я имею в виду, что диски загружены.

То есть если бы было 60, при этом большие очереди и большое время ожидания - то это ненормально. А 600 означает, что с контроллером всё в порядке.

Значит, нужно смотреть, где аномально большая нагрузка.

Ну и характер нагрузки - почему случайный доступ, почему не хватает 600 IOPs?

Может, там случайное чтение маленькими блоками? Может, для чтения 100К маленьких записей при построении отчёта нужно прочитать 100К разбросанных по всему диску страниц, а не несколько 64КБ экстентов, то есть неправильный дизайн базы, плохие индексы, фрагментация?
26 дек 12, 13:57    [13688701]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
М-да уж, печаль великая, отключил репорт сервис, а картина всё равно печалит. Архитектура БД просто ужаснейшая, основа БД была получена конвертацией с Postgre, каждую ночь идёт реорганизация индексов с фрагментацией >5%, затем собирается статистика с параметром ALL и выполняется
DBCC FREEPROCCACHE
go
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO

Картинка с другого сайта.
26 дек 12, 14:23    [13689046]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
Crimean
Member

Откуда:
Сообщений: 13148
вы бы характер нагрузки определили. это сотни мелких запросов или единицы крупных?
после того как будет понятно кто/что делает нагрузку - уже смотреть с чего бы это такой затык
а то вы сначала пытаетесь постучать во все известные бубны и совершенно не хотите ничего знать про причины
26 дек 12, 14:30    [13689104]     Ответить | Цитировать Сообщить модератору
 Re: Резкое увеличение числа блокировок на сервере  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Crimean
вы бы характер нагрузки определили. это сотни мелких запросов или единицы крупных?
после того как будет понятно кто/что делает нагрузку - уже смотреть с чего бы это такой затык
а то вы сначала пытаетесь постучать во все известные бубны и совершенно не хотите ничего знать про причины

как именно это определить ? что я должен проанализировать ? Я так понимаю трассу в профайлере, но как именно мне настроить фильтрацию для этого ? По duration ?
26 дек 12, 14:36    [13689161]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить