Тестирование параллелизма SQL в среде 1С Предприятие

16.05.2017 50 Comments

Администрирование, Производительность и оптимизация (HighLoad)

, , ,

Какое отношение параллелизм SQL имеет к среде 1С Предприятие? Целью данной работы является изучение влияния параллелизма SQL на работу с запросами 1С

Цель: изучить
влияние параллелизма SQL
на работу с запросами 1С

Литература: 

Понимание и использование параллелизма в SQL Server

Тестовая среда:

·        
Windows
server 2008 R2 Enterprise

·        
MS
SQL server 2008 R2

·        
1С Предприятие 8.2.19.90

SQL properties:

Рисунок 1. SQL
properties “General”

Рисунок 2.
SQL properties “Advansed”

Инструменты:

·        
SQL
server profiler

·        
SQL
Query

·        
Консоль запросов 1С

Тестовый запрос:

ВЫБРАТЬ

      АК.Наименование КАК Наименование

ИЗ

      РегистрСведений.АдресныйКлассификатор
КАК АК

              ВНУТРЕННЕЕ
СОЕДИНЕНИЕ РегистрСведений.АдресныйКлассификатор КАК АК1

                   ПО
АК.Код = АК1.Код

Подготовка:

Запускаем SQL server Profiler,
устанавливаем соединение, отмечаем события и колонки как показано на рисунке 3.

Рисунок 3. Trace properties

Устанавливаем отбор для нашей базы 

Рисунок 4. Фильтр по базе


Сокращения:

·        
Max
degree of parallelism – MDOP

·        
Сost threshold for parallelism — cost

 

Тестирование последовательного плана запроса (MDOP = 1)

Далее, в среде 1С Предприятие в тестовой базе запускаем
консоль запросов и выполняем запрос (Рис.5)

Рисунок 5. Консоль запросов – время выполнения 20 сек.

Параметр SQL сервера “Max degree of parallelism”
установлен в  1 (без параллелизма).
Смотрим результат в профайлере (рис.6)

Рисунок 6. Последовательный план запроса

SQL
сервер сформировал последовательный план запроса, при этом: общая загрузка CPU = 6,750 (сек), а время на
выполнение запроса  = 7,097(сек)

 

Тестирование параллельного плана запроса (MDOP = 0, cost =5)

Переводим SQL server в режим параллелизма (в SQL Query):

USE
master;

GO

EXEC
sp_configure ‘show advanced option’,
1;

GO

RECONFIGURE
WITH OVERRIDE

 

 

USE
master;

GO

exec
sp_configure‘max degree of parallelism’,
0;

GO

RECONFIGURE WITH
OVERRIDE

 

Выполняем тот же запрос (Рисунок 7)

Рисунок 7. Консоль запросов – время выполнения 16 сек.

Проверяем результат в профайлере (Рисунок 8)

Рисунок 8. Параллельный план запроса

Сервер SQL
в этот раз сформировал параллельный план запроса, при этом общая загрузка CPU = 7,905 сек, а
длительность выполнения запроса = 3,458 сек

 

Тестирование последовательного плана запроса (MDOP = 0, cost = 150)

Попытаемся избавиться от
параллельного плана, используя параметр «Сost threshold for parallelism». По умолчанию
параметр установлен в 5. В нашем случае от формирования параллельного плана
удалось избавиться при значении 150 (в SQL Query):

USE
master;

GO

exec
sp_configure ‘cost threshold for parallelism’, 150 ;

GO

Проверяем выполнение запроса в данных
условиях (рис. 9)

 

Рисунок 9. Консоль запросов – время выполнения
20 сек.

Проверяем результат в профайлере (рис.10)

Рисунок 10. Последовательный план
запроса.

 

Сервер SQL сформировал последовательный план запроса.  Общая загрузка CPU = 7,171 сек, время выполнения запроса =7, 864 сек.

 

 

Выводы:

·        
Выполнение тестового запроса в среде 1С
Предприятия с использованием SQL
сервером параллельного плана запроса дает значительный прирост
производительности по сравнению с последовательным планом (16 сек. против 20
сек. – выигрыш 4 сек.)

·        
Выполнения тестового запроса самим сервером SQL при
использовании параллельного плана запроса происходит в два раза быстрее, чем при
использовании последовательного плана запроса (3,5 сек. против 7,1 сек.)

·        
Параллелизм SQL сервера можно регулировать не только, используя параметр MDOP, но и параметр «Сost
threshold for parallelism»

Related Posts

50 Comments

  1. mbreaker

    Почему-то после прочтения возникает ощущение исследования сферического коня в вакууме. Хотя читалось не без интереса и за труды не могу не поставить плюса. Но…

    Во-первых, удручает практичность выводов… Учитывая то, что самим техсапом MS не рекомендуется менять значение параметра ctfp, а также то, что понятие стоимости запроса неизвестно в каких попугаях меряется (об этом красноречиво говорит разница между значением ctfp и фактическим временем исполнения запроса) — напрашивается вывод о нецелесообразности оперирования данным инструментом. Кстати, вопрос: а UPDATE STATISTICS перед замерами делался? а то может 150 и получилось из-за неверной предварительной расценки стоимости запроса?

    Во-вторых, hash match по декартову произведению одной из крупнейших таблиц (в среднестатической ИБ) самой на себя вряд ли можно назвать достаточно репрезентативной операцией для оценки такой глобальной настройки, как ctfp.

    Ну и в-третьих, если уж идти в отрыв от реальности и оценивать синтетические результаты, то куда интереснее посмотреть выигрыш при параллельном выполнении TABLE SCAN, а не при переборе упорядоченного индекса. И вообще пойдёт ли оптимизатор на распараллеливание при TABLE SCAN?

    Reply ↓
  2. jan27

    (1) с обновлением и без обновления статистик результаты схожие. Относительно ctfp и техсапа, хотелось бы увидеть ссылочку. Все выводы сделаны по конкретному запросу в конкретной тестовой среде.Про Table Scan пока не могу сказать. Параллелизм накладывает ряд ограничений. В одной из статей мне попадалась эта тема.

    Reply ↓
  3. mbreaker

    (2) Степень параллелизма

    Операции с индексами, которые создают или перестраивают индекс или удаляют кластеризованный индекс и запросы, интенсивно использующие циклы ЦП, являются лучшими кандидатами для параллельного плана. Например, хорошими кандидатами являются соединения больших таблиц, больших статистических выражений и сортировка больших результирующих наборов. Простые запросы, часто находящиеся в приложениях обработки транзакций, находят дополнительную координацию, запрашиваемую для выполнения запроса в параллельном перевешивании возможного повышения производительности. Чтобы отличить запросы, которые выигрывают от параллелизма, и запросы, которые не выигрывают, компонент Database Engine сравнивает предполагаемую стоимость выполняемого запроса или операции с индексами со значением cost threshold for parallelism. Несмотря на то, что это не рекомендуется, пользователи могут менять значение по умолчанию 5 при помощи процедуры sp_configure.
    Reply ↓
  4. jan27

    (3) ссылка не открывается

    Reply ↓
  5. jan27

    (3) нашел поиском, однако там же http://technet.microsoft.com/ru-ru/library/ms188603(v=sql.105).aspx

    Reply ↓
  6. jan27

    (1) Table Scan тоже параллелится, попробую потестировать

    Reply ↓
  7. mbreaker

    (5) да это именно та статья… почему-то то ссылка при вставке «побилась»…

    Reply ↓
  8. mbreaker

    (6) практически не сомневался в этом, потому как сегментировать перебор кучи вполне логично для оптимизатора при доступной возможности распараллеливания операций. Интересен размер эффекта… пусть даже на синтетическом примере…

    Reply ↓
  9. jan27

    (8) SELECT

    Count(*)

    FROM [ExchangeDB].[dbo].[_Employee_Changes] as Part1

    LEFT JOIN [ExchangeDB].[dbo].[_Employee_Changes] as Part2 ON Part1._IDRRef = Part2._IDRRef

    Описанный выше запрос на неиндексированной таблице при параллельном плане дает двойной прирост (0,250) против (0,573). Запрос возвращает 260603669

    Reply ↓
  10. bomba

    Алексей, жду исследования сферического коня в PostgreSQL. Мне MS SQL не дают =(

    Reply ↓
  11. jan27

    (10) мы пока на скуле сидим

    Reply ↓
  12. comol

    Статья удивила… Вообще max degree of parallelism ставят в 1 изначально потому что MS SQL при параллельном исполнении накладывал «непонятные блоикровки»и доходило до DeadLock — ов. На текущей версии (2012) ничего не поменялось… если только в 2014 что-то «доделали». Во-вторых 1С это же СКД + Консоли запросов/отчетов всякие… если в базе 300+ пользователей и куча разных «фич» то установка данного свойства не в «1» приводит к «убиению» SQL сервера неправильным запросом… или правильным, но 1 пользователь мешает работе остальных 300…

    А показать что использование нескольких ядер вместо одного даёт преимущества можно к примеру и на MS Excel если его запустить с нужным Affinity…

    Reply ↓
  13. jan27

    (12) у нас работает на 2008 R2 SQL server проблем не отмечается. Все-таки, хотелось уточнить на каких конфигурациях сервера вы рекомендуете ставить MDOP в 1

    Reply ↓
  14. jan27

    (12) к тому же не все запросы выполняются параллельно и параллелизм можно регулировать параметром «Сost threshold for parallelism»

    Reply ↓
  15. andreyagafonov

    Каким образом было снижено или исключено влияние процедурного и буферного кэшей на производительность выполняемых (в ходе тестов) запросов?

    Reply ↓
  16. lustin

    (12) comol, На 1С мне известен только один нормальный кейс когда возможно выставлять 0. Это обновление информационной базы с реструктуризацией. Лет 5 назад, еще до 8.3 и фонового обновления использовали такой вот хинт подсмотренный у DBA, которые так оптимизировали BULK INSERT операции.

    код скрипта выглядел так на псевдо языке

    
ОчиститьПроцедурныеКэши()
УстановитьУровеньMAXDOPСПаралельностью() //MAXDOP=0
ОбновитьИнформационнуюБазу()
УстановитьУровеньMAXDOPБезеПарралельности() //MAXDOP=1

    В остальных случаях согласен — Maxdop = 1 на весь сервер. Чистые SQL специалисты играются с хинтами в запросах, у нас же такой возможности нет.

    Reply ↓
  17. lustin

    (16) lustin, соврал насчет нет возможности. Есть… Но очень дорого

    http://www.softpoint.ru/article_id424.htm

    Reply ↓
  18. asved.ru

    (17) lustin, это параллелизм на уровне прикладного кода, а не запросов, и к обсуждаемой тематике отношения не имеет.

    Reply ↓
  19. jan27

    (15) использовался dbcc freeproccache, но заметного влияния замечено не было

    Reply ↓
  20. jan27

    (16) для параллелизма необязательно выставлять MDOP в 0, достаточно установить отличным от 1

    Reply ↓
  21. andreyagafonov

    (19) уже лучше, но почему в статье об этом ничего не написано? А что насчет буферного кэша? Или на ваш взгляд его состояние (как и состояние процедурного) незначительно влияет на скорость выполнения запросов?

    Reply ↓
  22. jan27

    (21) где можно посмотреть данные о влиянии буферного кэша на скорость выполнения запроса?

    Reply ↓
  23. jan27

    (21) уточнения по тому же запросу:

    после

    CHECKPOINT

    DBCC FREEPROCCACHE

    GO

    dbcc dropcleanbuffers

    GO

    MDOP = 1 первый раз — 40,739 сек, второй раз — 7,116 сек

    MDOP = 0 первый раз — 15,803 сек, второй раз — 3,498 сек

    Reply ↓
  24. zoytsa

    проверил, SQL 2008 — на четырех серверах MDOP = 0.

    настройки на серверах «по умолчанию», поэтому что-то не совсем понял на счет споров об отмене «MDOP = 1» —

    изначально и так «MDOP = 0», разве нет? 🙂

    Reply ↓
  25. jan27

    (24) некоторые настойчиво рекомендуют устанавливать MDOP = 1, без вариантов

    Reply ↓
  26. jan27

    (12) кстати, столкнулся с тем, что не для всех очевидно «что использование нескольких ядер вместо одного даёт преимущества», поэтому пришлось проделать данную работу

    Reply ↓
  27. andreyagafonov

    (23) ну вот уже лучше. Теперь понятно что этот конкретный select в лабораторных условиях при mop<>1 выполняется быстрее. А теперь откройте тайну, какую практическую ценность несет эта информация?

    Reply ↓
  28. jan27

    (27) лично для меня одно из направлений повышения производительности

    Reply ↓
  29. asved.ru

    (28) ну так возьмите стандартный нагрузочный тест и докажите, что применение параллелизма существенно повышает производительность при многопользовательской работе.

    Reply ↓
  30. asved.ru

    (24) zoytsa, MDOP=0 есть настройка, разрешающая параллелизм и не ограничивающая количество CPU, участвующих в запросе. MDOP=1 ограничивает количество CPU, участвующих в исполнении запроса, одним и таким образом запрещает параллелизм.

    Reply ↓
  31. jan27

    (29) у меня уже есть такие данные

    Reply ↓
  32. andreyagafonov

    (28) В таком случае я предлагаю перенести эти исследования в ваш личный блог. Потому как запуск одного запроса (отражающего специфику работы вашей системы) в лабораторных условиях, ничего не доказывает. Кроме того не раскрыты методы борьбы с ошибками СУБД:

    — «Intra-query parallelism caused your server command (process ID #XX) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)»

    — «Transaction (Process ID #XX) was deadlocked on thread communication buffer resources with another process and has been chosen as the deadlock victim»

    Reply ↓
  33. jan27

    (32) вы предлагаете в одной работе затронуть такие темы, каждой из которой посвящаются отдельные работы?

    Intra-query parallelism caused your server command… зачастую не ошибки СУБД а кривой код.

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

    Reply ↓
  34. lustin

    (33) не надо блокировать. Вы затронули достаточно больную и серьезную тему. В эту сторону так или иначе смотрели многие. Просто единого подхода так и не выработалось. Точнее скажу немного по другому «В Prodcution решениях на 1С многие отказались от исследования данной тематики»

    Оказалось, что дешевле, по затратам времени и по затратам на поддержку, реалилизовывать парралельность на уровне кода ORM.

    Предвосхищу, что настоящие DBA меня сейчас закидают «тряпками», но все же, я не зря дал выше пример кода от СофтПоинта на чистом 1С.

    Обычно в 1С, да и не в 1С смотрят в сторону адаптации метаданных (таблиц СУБД) и кода контролера под многопоточность выборок. Здесь необходимо явно управлять блокировками по ключам, одновременно с этим обратить внимание на сортировку индексных выражений. Но зато слияние данных происходит на уровне сервера приложений — то есть там где также существует возможность организовать и кэш частично рассчитанных данных.

    То есть фактически это такой MapReduce подход (с большой конечно натяжкой). И в этом случае задача сервера СУБД обеспечить многопоточное выполненение транзакций на чтение небольших наборов данных, а уже сервер приложений обеспечит агрегирование на уровне контролера или агрегатов таблиц: но в общем случае это более управляемый процесс, потому что код открыт для вас как разработчика.

    Также при использовании кода фрэймворка для реализации парралельности выполнения операций с СУБД, оказалось проще «разруливать» те самые «дедлоки», так как явно понятно какой участок кода какой участок кода блокирует. А не разбираться между сессиями и кэшем SQL запросов, которые НЕ совсем прозрачны для этих целей.

    Вместе с тем, я еще раз повторюсь — не обращайте внимание на наше ворчание в комментариях. Если вы решили исследовать этот вопрос — это уже круто, не каждый решится на такое. Да и мой комментарий не яляется истиной в последней инстанции.

    Reply ↓
  35. asved.ru

    (34) lustin,

    исследовать этот вопрос

    целесообразно лишь при наличии уверенности в существенном положительном эффекте от использования параллелизма при многопользовательской работе. В чем лично я совсем не уверен.

    Если речь о физических чтениях — мы по определению не можем их выполнять параллельно, шина-то одна. В целом параллелизм позволяет более эффективно использовать ресурсы нескольких CPU, но наращивая нагрузку, упираемся мы, как правило, совсем не в CPU и даже не на стороне SQL.

    Reply ↓
  36. lustin

    (36) asved.ru, полностью согласен. у меня сложилось такое же мнение, просто я его не так конкретно формулирую.

    Reply ↓
  37. zoytsa

    (34) lustin,

    согласен с Вами,

    чем ждать панацеи на уровне базы данных, лучше оптимизировать сами запросы;

    особенно когда, как Вы заметили, источник блокировки ясен — зачем менять курс всего корабля 🙂

    Reply ↓
  38. AlX0id

    (36) asved.ru,

    но наращивая нагрузку, упираемся мы, как правило, совсем не в CPU и даже не на стороне SQL.

    А вот тут можно детальнее? Во что? )

    Reply ↓
  39. Fragster

    автор лучше бы восстановление последовательности потестил

    Reply ↓
  40. jan27

    (40) а причем тут восстановление последовательности?

    Reply ↓
  41. Fragster

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

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

    Reply ↓
  42. jan27

    (42) мелкие запросы не параллелизируются, нужно очень сильно изгаляться, чтобы добиться этого — для этой задачи нужно пробовать другие механизмы, первое, что приходит на ум — ручной параллелизм или многопоточность

    многопоточный попробую

    Reply ↓
  43. acsent

    Почему в качестве теста выбран джойн таблицы с самой собой? Есть что-нибудь более жизненное?

    Reply ↓
  44. jan27

    (44) запрос приведен в качестве примера, готовых рецептов нет, нужно смотреть, экспериментировать, подбирать оптимальный вариант. Посмотрите предыдущие и последующие публикации на эту тему, возможно, попадется что-то жизненное

    Reply ↓
  45. sanfoto

    а я бы рекомендовал лучше — всем ускоряльщикам SQL почитать про ORM…. )))

    https://ru.wikipedia.org/wiki/ORM

    Я думаю станет понятно, что ускорять надо больше саму 1с + ускорять канал связи 1С с SQL.

    Reply ↓
  46. jan27

    (46) а замедляльщикам SQL, я бы рекомендовал этого не делать

    Reply ↓
  47. botcc

    MDOP = 37

    Кто мне скажет что надо ставить 0 или 1 заплюю пока не захлебнётесь!!!

    Reply ↓
  48. jan27

    (48) это у вас такая манера задавать вопросы?

    Reply ↓
  49. botcc

    (49) Ну нет :). Просто при отключенном параллелизме книга продаж за месяц крутится более 6 часов при включенном около 30 — 35 минут.

    Reply ↓
  50. jan27

    (50) Логично

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *