Автор: Анатолий Котелевец
Предыстория:
В нашу компанию пришел достаточно интересный проект, связанный с обработкой очереди задач. Проект был разработан ранее другой командой. Нам необходимо было разобраться с проблемами, возникающими при большой нагрузке на очередь, и, соответственно, исправить найденные.
Если вкратце, то проект из себя представляет несколько БД и приложений, расположенных на разных серверах. «Задача» в данном проекте – это хранимая процедура или .Net приложение. Соответственно «задача» должна быть выполнена на определённой БД и на определенном сервере.
Все данные, которые относятся к очереди, хранятся на выделенном сервере. На серверах, где необходимо выполнять задачи, хранятся только метаданные, т.е. процедуры, функции и служебные данные, которые относятся к этому серверу. Соответственно, данные, относящиеся к задачам, мы получаем запросами с использованием LinkedServer.
Все данные, которые относятся к очереди, хранятся на выделенном сервере. На серверах, где необходимо выполнять задачи, хранятся только метаданные, т.е. процедуры, функции и служебные данные, которые относятся к этому серверу. Соответственно, данные, относящиеся к задачам, мы получаем запросами с использованием LinkedServer.
Почему так?
- Удобство. Мы можем в любой момент указать, что теперь на сервере Б хранятся данные.
- Так было реализовано до нас.
Ниже приведены два наиболее популярных классических способа обработки очереди:
- Отправлять уведомление обработчику задач о наличии задачи.
- Производить опрос очереди на наличие задач.
Изначально в проекте был реализовал второй вариант. Чтобы минимизировать время ожидания обработки задач, наше приложение опрашивает очередь каждый 100-500ms.
Cобственно, в этом ничего страшного и нет, кроме одного – при такой реализации таблица лишний раз блокируется. Наперед скажу, в запросе используется блокировка строк с возможностью только чтения незаблокированных строк:
1 2 |
READPAST, ROWLOCK, UPDLOCK |
Итак, вернемся к проблеме. При анализе я обратил внимание на значение счетчика — batch requests/sec в Active Monitor. Данное значение при малом количестве (около 50) задач в очереди, зашкаливало за 1000, а также нагрузка на CPU резко возрастала.
Первая мысль: нужно переходить к реализации первого варианта (отправка уведомления обработчику задач). Данный метод был реализован с использованием службы Service Broker и SignalR:
- Service Broker использовали для отправки уведомления о появлении задачи;
- SignalR использовали для отправки уведомления обработчикам задач.
Почему SignalR?
Данный инструмент уже используется в проекте, а сроки были сжаты, поэтому я не стал внедрять что-то аналогичное, например, NServiceBus.
Моему удивлению не было предела, когда данное решение не помогло. Да, был получен прирост в производительности, но это не решило проблему окончательно. Для отладки был написан стресс-тест, когда в очередь добавляется более 500 задач.
Создание такого стресс-теста позволило найти «корень зла».
Анализ списка активных запросов и отчетов производительности, во время большой нагрузки показа наличие «очень интересных запросов», которые состояли из одной команды:
1 2 |
fetch api_cursor0000000000000003 |
Дальнейший анализ показал, что это запросы с LinkedServer. Сразу возник вопрос: ”Неужели запрос такого типа select * from RemoteServer.RemoteDatabase.dbo.RemoteTable where FieldId = Value порождает запрос (fetch api_cursor0000000000000003) на RemoteServer?” Оказывается, да, и даже тогда, когда LinkedServer — это MS SQL.
Для более наглядного примера созданим таблицу «Test» (код создания таблицы доступен в приложении к статье) на сервере “А”, а на сервере “B” выполним запрос:
1 2 |
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> dev2.test_db.dbo.<span class="hljs-keyword">test</span> |
где dev2 — это наш сервер “А”.
При первом выполнении такого запроса у нас будет подобный лог в профайлере на сервере А:
Полный лог доступен здесь.
А теперь выполним запросы уже по ID:
1 2 |
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> dev2.test_db.dbo.<span class="hljs-keyword">test</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">ID</span> = <span class="hljs-number">3</span> |
Лог профайлера для второго запроса:
План запроса был добавлен в кэш. Если выполнить этот запрос второй раз, то уже немного лучше.
Лог профайлера после повторного запуска
Как мы видим, данные уже берутся из кэша.
При изменении условий мы получим аналогичную выборку — первая выборка по заданному Id. Но суть в том, что при больших количествах разных запросов кэша не хватает. И sql начинает городить кучу запросов к таблице, что приводит к «тормозам». Вы спросите: «А как же индексы?» Индексы есть, но запросы даже с условием по Primary Key (PK) порождали данную проблему.
А что Google говорит по этому поводу? А много чего, только толку нет:
- Что запросы должны выполняться от пользователя, который относится к одной из следующих ролей: sysadmin, db_owner, db_ddladmin, чтобы можно было использовать статистику;
- Неверно настроен LinkedServer.
Более толковые ответы были найдены только в 3-х статьях:
- Exposing API Server Cursors
- Top 3 Performance Killers For Linked Server Queries
- Технологии Push и Pull при работе с linked servers в Microsoft SQL Server
Насколько я разобрался, нельзя настроить LinkedServer так, чтобы всегда использовалась Pull технология для получения данных с LinkedServer. Все зависит от того, где вы обрабатываете запрос.
Время поджимало, и единственное решение, которые нас могло спасти, это переписать часть запросов на dynamic sql. Т.е. выполнять запросы на сервере, на котором хранятся данные.
Работать с данным на LinkedServer можно несколькими способами:
- В запросе непосредственно указать источник данных – удаленный сервер. Данная реализация имеет несколько недостатков:
- низкая производительность;
- возвращает большой объем данных.
12<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> RemoteServer.RemoteDatabase.dbo.RemoteTable <span class="hljs-keyword">where</span> <span class="hljs-keyword">Id</span> = @<span class="hljs-keyword">Id</span> - Использовать OPENQUERY. Не подходит по ряду причин:
- невозможно указать имя удаленного сервера в качестве параметра;
- передать параметры в запрос;
- отсутствие параметризации запроса
- возможны Sql инъекции
- происходит неявное приведение типов, т.к. параметры передаются в виде строки.
12<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> OPENQUERY(RemoteServer, <span class="hljs-string">'select * from RemoteDatabase.dbo.RemoteTable'</span>).По ссылкам доступны примеры логов для следующих запросов. Данные запросы выполнятся на сервере “B”, а логи с сервера “A”:
12<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> OPENQUERY(dev2, <span class="hljs-string">'select * from test_db.dbo.test'</span>) <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">26</span>12<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> OPENQUERY(dev2, <span class="hljs-string">'select * from test_db.dbo.test where ID = 26'</span>) - Выполнить запрос на удаленном сервере. Аналогично OPENQUERY:
- нельзя указать имя сервера в качестве параметра, так как имя задается на этапе компиляции процедуры;
- отсутствие параметризации запроса
- возможны Sql инъекции
- происходит неявное приведение типов, т.к. параметры передаются в виде строки.
12exec ('<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> RemoteDatabase.dbo.RemoteTable<span class="hljs-string">') at RemoteServer</span>По ссылкам доступны примеры логов для следующих запросов:
12exec ('<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_db.dbo.<span class="hljs-keyword">test</span><span class="hljs-string">') at dev2</span>12exec ('<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_db.dbo.<span class="hljs-keyword">test</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">Id</span> = <span class="hljs-number">30</span><span class="hljs-string">') at dev2</span> - нельзя указать имя сервера в качестве параметра, так как имя задается на этапе компиляции процедуры;
- Еще возможно выполнить запрос на удаленном сервере, выполнив процедуру sp_executesql.
1234<span class="hljs-keyword">DECLARE</span> @C_SP_CMD <span class="hljs-keyword">nvarchar</span>(<span class="hljs-number">50</span>) = <span class="hljs-keyword">QUOTENAME</span>(@RemoteServer) + <span class="hljs-keyword">N</span><span class="hljs-string">'.'</span>+@RemoteDatabase +<span class="hljs-keyword">N</span><span class="hljs-string">'.sys.sp_executesql'</span><span class="hljs-keyword">DECLARE</span> @C_SQL_CMD <span class="hljs-keyword">nvarchar</span>(<span class="hljs-number">4000</span>) = <span class="hljs-string">'select * from dbo.RemoteTable'</span>EXEC @C_SP_CMD @C_SQL_CMD
По ссылкам доступны примеры логов выполнения запросов с использованием sp_executesql:
Четвертый способ и был использовал для решения задачи.
Ниже приведены несколько графиков входящего и исходящего трафика на сервере, где расположена основная база очереди до и после использования sp_executesql. При этом размер БД 200-300Мб.
Входящий и исходящий трафик за несколько дней на сервере, до использования sp_executesql:
Входящий и исходящий трафик, после начала использования sp_executesql:
Исходящие пики – это копирование backup на NFS.
Вывод:
изначально драйвер от MS для работы с «MS sql linked server» не может сам выполнять запросы на сервере источнике данных. Следовательно, коллеги, давайте стараться выполнять их на источнике данных, для решения хотя бы части вопросов с производительностью.
Файлы к статье.