- PVSM.RU - https://www.pvsm.ru -
В нашу компанию пришел достаточно интересный проект, связанный с обработкой очереди задач. Проект был разработан ранее другой командой. Нам необходимо было разобраться с проблемами, возникающими при большой нагрузке на очередь, и, соответственно, исправить найденные.
Если вкратце, то проект из себя представляет несколько БД и приложений, расположенных на разных серверах. «Задача» в данном проекте – это хранимая процедура или .Net приложение. Соответственно «задача» должна быть выполнена на определённой БД и на определенном сервере.
Все данные, которые относятся к очереди, хранятся на выделенном сервере. На серверах, где необходимо выполнять задачи, хранятся только метаданные, т.е. процедуры, функции и служебные данные, которые относятся к этому серверу. Соответственно, данные, относящиеся к задачам, мы получаем запросами с использованием LinkedServer.
Все данные, которые относятся к очереди, хранятся на выделенном сервере. На серверах, где необходимо выполнять задачи, хранятся только метаданные, т.е. процедуры, функции и служебные данные, которые относятся к этому серверу. Соответственно, данные, относящиеся к задачам, мы получаем запросами с использованием LinkedServer.
Ниже приведены два наиболее популярных классических способа обработки очереди:
Изначально в проекте был реализовал второй вариант. Чтобы минимизировать время ожидания обработки задач, наше приложение опрашивает очередь каждый 100-500ms.
Cобственно, в этом ничего страшного и нет, кроме одного – при такой реализации таблица лишний раз блокируется. Наперед скажу, в запросе используется блокировка строк с возможностью только чтения незаблокированных строк:
READPAST, ROWLOCK, UPDLOCK
Итак, вернемся к проблеме. При анализе я обратил внимание на значение счетчика — batch requests/sec в Active Monitor. Данное значение при малом количестве (около 50) задач в очереди, зашкаливало за 1000, а также нагрузка на CPU резко возрастала.
Первая мысль: нужно переходить к реализации первого варианта (отправка уведомления обработчику задач). Данный метод был реализован с использованием службы Service Broker и SignalR:
Данный инструмент уже используется в проекте, а сроки были сжаты, поэтому я не стал внедрять что-то аналогичное, например, NServiceBus.
Моему удивлению не было предела, когда данное решение не помогло. Да, был получен прирост в производительности, но это не решило проблему окончательно. Для отладки был написан стресс-тест, когда в очередь добавляется более 500 задач.
Создание такого стресс-теста позволило найти «корень зла».
Анализ списка активных запросов и отчетов производительности, во время большой нагрузки показа наличие «очень интересных запросов», которые состояли из одной команды:
fetch api_cursor0000000000000003
Дальнейший анализ показал, что это запросы с LinkedServer. Сразу возник вопрос: ”Неужели запрос такого типа select * from RemoteServer.RemoteDatabase.dbo.RemoteTable where FieldId = Value [1] порождает запрос (fetch api_cursor0000000000000003) на RemoteServer?” Оказывается, да, и даже тогда, когда LinkedServer — это MS SQL.
Для более наглядного примера созданим таблицу «Test» (код создания таблицы доступен в приложении [2] к статье) на сервере “А”, а на сервере “B” выполним запрос:
select * from dev2.test_db.dbo.test
где dev2 — это наш сервер “А”.
При первом выполнении такого запроса у нас будет подобный лог в профайлере на сервере А:
Полный лог доступен здесь [3].
А теперь выполним запросы уже по ID:
select * from dev2.test_db.dbo.test where ID = 3
Полный лог доступен тут [4].
Как видно на скриншоте, план запроса был добавлен в кэш. Если выполнить этот запрос второй раз, то уже немного лучше.
Полный лог доступен здесь [5]
Как мы видим, данные уже берутся из кэша.
При изменении условий мы получим аналогичную выборку — первая выборка по заданному Id. Но суть в том, что при больших количествах разных запросов кэша не хватает. И sql начинает городить кучу запросов к таблице, что приводит к «тормозам». Вы спросите: «А как же индексы?» Индексы есть, но запросы даже с условием по Primary Key (PK) порождали данную проблему.
А что Google говорит по этому поводу? А много чего, только толку нет:
Более толковые ответы были найдены только в 3-х статьях:
Насколько я разобрался, нельзя настроить LinkedServer так, чтобы всегда использовалась Pull технология для получения данных с LinkedServer. Все зависит от того, где вы обрабатываете запрос.
Время поджимало, и единственное решение, которые нас могло спасти, это переписать часть запросов на dynamic sql. Т.е. выполнять запросы на сервере, на котором хранятся данные.
Работать с данным на LinkedServer можно несколькими способами:
select * from RemoteServer.RemoteDatabase.dbo.RemoteTable where Id = @Id
select * from OPENQUERY(RemoteServer, 'select * from RemoteDatabase.dbo.RemoteTable').
По ссылкам доступны примеры логов для следующих запросов. Данные запросы выполнятся на сервере “B”, а логи с сервера “A”:
select * from OPENQUERY(dev2, 'select * from test_db.dbo.test') where id = 26
select * from OPENQUERY(dev2, 'select * from test_db.dbo.test where ID = 26')
exec ('select * from RemoteDatabase.dbo.RemoteTable') at RemoteServer
По ссылкам доступны примеры логов для следующих запросов:
exec ('select * from test_db.dbo.test') at dev2
exec ('select * from test_db.dbo.test where Id = 30') at dev2
DECLARE @C_SP_CMD nvarchar(50) = QUOTENAME(@RemoteServer) + N'.'+@RemoteDatabase +N'.sys.sp_executesql'
DECLARE @C_SQL_CMD nvarchar(4000) = 'select * from dbo.RemoteTable'
EXEC @C_SP_CMD @C_SQL_CMD
По ссылкам доступны примеры логов выполнения запросов с использованием sp_executesql:
Четвертый способ и был использовал для решения задачи.
Ниже приведены несколько графиков входящего и исходящего трафика на сервере, где расположена основная база очереди до и после использования sp_executesql. При этом размер БД 200-300Мб.
Исходящие пики – это копирование backup на NFS.
Напрашивается вывод: изначально драйвер от MS для работы с «MS sql linked server» не может сам выполнять запросы на сервере источнике данных. Следовательно, коллеги, давайте стараться выполнять их на источнике данных, для решения хотя бы части вопросов с производительностью.
Файлы [20] к статье.
Автор: akotelevets
Источник [21]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/sql/130458
Ссылки в тексте:
[1] Value: https://habrahabr.ru/users/value/
[2] приложении: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/f69337e12bfa/LinkedServer/?at=default
[3] здесь: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/select%20from%20linked%20server/%D0%BF%D0%BE%D0%BB%D0%BD%D0%B0%D1%8F%20%D0%B2%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D0%BF%D1%80%D0%B8%20%D0%BF%D0%B5%D1%80%D0%B2%D0%BE%D0%BC%20%D0%B7%D0%B0%D0%BF%D1%83%D1%81%D0%BA%D0%B5.trc?at=default
[4] тут: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/select%20from%20linked%20server/%D0%BF%D0%B5%D1%80%D0%B2%D0%B0%D1%8F%20%D0%B2%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D0%BF%D0%BE%20%D0%B7%D0%B0%D0%B4%D0%B0%D0%BD%D0%BD%D0%BE%D0%BC%D1%83%20ID.trc?at=default
[5] здесь: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/select%20from%20linked%20server/%D0%BF%D0%BE%D0%B2%D1%82%D0%BE%D1%80%D0%BD%D0%B0%D1%8F%20%D0%B2%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D0%BF%D0%BE%20%D0%B7%D0%B0%D0%B4%D0%B0%D0%BD%D0%BD%D0%BE%D0%BC%D1%83%20Id.trc?at=default
[6] Exposing API Server Cursors: http://www.sql-server-performance.com/2006/api-server-cursors/
[7] Top 3 Performance Killers For Linked Server Queries: http://stage.toadworld.com/platforms/sql-server/b/weblog/archive/2013/05/14/top-3-performance-killers-for-linked-server-queries
[8] Технологии Push и Pull при работе с linked servers в Microsoft SQL Server: http://www.sql.ru/articles/mssql/2007/051803pushandpullinmicrosoftsqlserverlinkedservers.shtml
[9] Dynamic T-SQL и как он может быть полезен: http://inostudio.com/ru/article/dynamic-sql.html
[10] OpenQuery/выборка с заданным Id.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/OpenQuery/%D0%B2%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D1%81%20%D0%B7%D0%B0%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D0%BC%20Id.trc?at=default
[11] OpenQuery/выборка с заданным Id в качестве параметра.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/OpenQuery/%D0%B2%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D1%81%20%D0%B7%D0%B0%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D0%BC%20Id%20%D0%B2%20%D0%BA%D0%B0%D1%87%D0%B5%D1%81%D1%82%D0%B2%D0%B5%20%D0%BF%D0%B0%D1%80%D0%B0%D0%BC%D0%B5%D1%82%D1%80%D0%B0.trc?at=default
[12] exec at server/На стороне клиента.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/exec%20at%20server/%D0%9D%D0%B0%20%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%BD%D0%B5%20%D0%BA%D0%BB%D0%B8%D0%B5%D0%BD%D1%82%D0%B0.trc?at=default
[13] exec at server/На стороне сервера.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/exec%20at%20server/%D0%9D%D0%B0%20%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%BD%D0%B5%20%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%B0.trc?at=default?at=default
[14] exec at server/На стороне клиента с заданным Id.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/exec%20at%20server/%D0%9D%D0%B0%20%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%BD%D0%B5%20%D0%BA%D0%BB%D0%B8%D0%B5%D0%BD%D1%82%D0%B0%20%D1%81%20%D0%B7%D0%B0%D0%B4%D0%B0%D0%BD%D1%8B%D0%BC%20Id.trc?at=default
[15] exec at server/На стороне сервера с заданым Id.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/exec%20at%20server/%D0%9D%D0%B0%20%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%BD%D0%B5%20%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%B0%20%D1%81%20%D0%B7%D0%B0%D0%B4%D0%B0%D0%BD%D1%8B%D0%BC%20Id.trc?at=default
[16] sp_executesql/Полная выборка на клиенте.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/sp_executesql/%D0%9F%D0%BE%D0%BB%D0%BD%D0%B0%D1%8F%20%D0%B2%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%20%D0%BD%D0%B0%20%D0%BA%D0%BB%D0%B8%D0%B5%D0%BD%D1%82%D0%B5.trc?at=default
[17] sp_executesql/Полная выбрка на стороне сервера.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/sp_executesql/%D0%9F%D0%BE%D0%BB%D0%BD%D0%B0%D1%8F%20%D0%B2%D1%8B%D0%B1%D1%80%D0%BA%D0%B0%20%D0%BD%D0%B0%20%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%BD%D0%B5%20%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%B0.trc?at=default
[18] sp_executesql/Выборка по Id на клиенте.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/sp_executesql/%D0%92%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D0%BF%D0%BE%20Id%20%D0%BD%D0%B0%20%D0%BA%D0%BB%D0%B8%D0%B5%D0%BD%D1%82%D0%B5.trc?at=default
[19] sp_executesql/Выборка по Id на сервере.trc: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b9966972b87ec943e2a7e2a881743f2/LinkedServer/%D0%A0%D0%B5%D0%B7%D1%83%D0%BB%D1%8C%D1%82%D0%B0%D1%82%20%D1%82%D1%80%D0%B0%D1%81%D1%81%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8/sp_executesql/%D0%92%D1%8B%D0%B1%D0%BE%D1%80%D0%BA%D0%B0%20%D0%BF%D0%BE%20Id%20%D0%BD%D0%B0%20%D1%81%D0%B5%D1%80%D0%B2%D0%B5%D1%80%D0%B5.trc?at=default
[20] Файлы: https://bitbucket.org/akotelevets/for-articles-by-inostudio/src/6723e6787b99/LinkedServer/?at=default
[21] Источник: https://habrahabr.ru/post/302958/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best
Нажмите здесь для печати.