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

Какое отношение параллелизм 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»

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 не будет опубликован. Обязательные поля помечены *