Почему большие БД работают не как хочется, или про несбыточные мечты SQL-запросов13.04.2017 17:19

В команде эксплуатации подумали, что наш опыт с Microsoft SQL в нагруженной среде больше нельзя скрывать, и потому родилась эта статья. В ней я расскажу о нюансах работы с этой СУБД из практики.

Microsoft SQL Server давно нашел свое место в семействе продуктов Яндекс.Денег и успешно решает задачу сбора разрозненной информации обо всех операциях из множества отдельных сервисов. Без такой сборки воедино нельзя было бы отследить платеж, собрать статистику или решить проблему.

Все сказанное далее будет полезно администраторам крупных БД — тем, кто заботится о быстрой и точной работе аналитики Microsoft SQL Server 2012–2014.

Если информация окажется вам полезной и интересной — обязательно дайте знать в комментариях, чтобы автор не расслаблялся.

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

Из этих данных ежесуточно строятся кубы с сотнями измерений, генерируются отчеты и реестры, происходят сверки. После чего «причесанные» данные отдаются пользователям и финансовым системам для дальнейшей работы. Для этих задач отлично подошел Microsoft SQL Server с его мощным Integration Services и процессом ETL (Extract — Transform — Load).

Еще мы активно используем Analysis Services для построения OLAP кубов. Они предоставляют данные в многомерном виде с любым срезом, простым для аналитиков, финансистов, продакт менеджеров и руководства.

Самая частая причина такого — устаревшие статистики (информация о состоянии колонок таблицы) и, как следствие, не оптимальные планы.

На Хабре уже есть статьи по автоматизации обновления статистики, поэтому подробно на этом моменте останавливаться не буду. Но обновление статистик может быть накладным по времени и нагрузке, а результат — нестабильным из-за того, что данные в таблице меняются быстрее обновления статистик. В таких случаях я просто глобально включаю флаг трассировки, который изменяет 20%-ный порог обновления статистики на динамически изменяющийся процент изменений таблицы:

DBCC TRACEON (2371,-1)

С включенным флагом статистики будут обновляться чаще с ростом количества строк.

Это здорово помогает, но на таблицах с миллиардом строк и больше процесс может проходить крайне медленно, несмотря на нормальный план.

В этом случае помогает включение асинхронного обновления статистик:

ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

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

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

При одновременном запуске пяти и более запросов, прожорливых до памяти, а также при наличии ограничения «в одни руки» по умолчанию в 25% — первым четырем отдается вся память. Остальным запросам остается только встать в очередь с индикатором RESOURCE_SEMAPHORE, ожидая освобождения памяти.

Около половины доступной оперативной памяти сервера используется для выполнения запросов. В системах, где RAM более 128 ГБ, 25% имеют весьма существенное значение.

По моим наблюдениям, от 20 до 80% выделенной памяти запрос просто не использует — это отлично видно, если во время работы запросов запустить команду:

select * from sys.dm_exec_query_memory_grants

После выполнения команды вы увидите обрабатывающиеся в настоящий момент запросы в базе данных с ожиданиями ресурсов, если они возникли в процессе работы.

Результат отработки запроса на одной из наших тестовых БД.

Запрос попросил и получил 9 ГБ, но использовал только 10%. Обратите внимание на поле ideal_memory_kb — в нем фиксируются несбыточные мечты запросов.

Эффективно бороться с нерациональным расходом памяти при выполнении запросов можно с помощью Resource Governor. Он позволяет задать ограничения на загрузку ЦП и использование памяти.
Еще рекомендую использовать хранимую процедуру sp_WhoIsActive. Она собирает информацию о состоянии сервера через динамические представления (DMV). Очень простой и мощный инструмент.

Я использую процедуру sp_WhoIsActive с таким набором параметров:

sp_whoisactive @not_filter = 'ReportServer',    @not_filter_type = 'database', @get_plans=1, @find_block_leaders = 1, @get_task_info = 2, @get_additional_info=1--, @get_locks = 1

Идентифицировать такие запросы можно простым наблюдением за активными запросами в момент чрезмерной нагрузки. Также можно создать задачу, которая будет раз в 5–10 минут запускать запрос select * from sys.dm_exec_query_memory_grants, сохраняя в таблицу результаты. Итоговые значения можно проанализировать и найти проблемные запросы по разности между granted_memory_kb и max_used_memory_kb.

Далее нужно идентифицировать соединения, берущие память сверх необходимого, и перенаправить их с помощью ResourceGovernor в группу, где выдача памяти происходит с запасом 10–20% вместо 80%.

Значение для параметра REQUEST_MAX_MEMORY_GRANT_PERCENT (задается в процентах от доступной памяти) можно рассчитать с помощью следующего скрипта:

SELECT 

    res.name, 

    sem.target_memory_kb /1024 as target_memory_Mb, 

    sem.available_memory_kb / 1024 as available_memory_MB, 

    sem.granted_memory_kb/1024 as granted_memory_Mb, 

    sem.used_memory_kb / 1024 as used_memory_Mb, 

    sem.grantee_count, sem.waiter_count 

FROM sys.dm_exec_query_resource_semaphores sem 

join sys.resource_governor_workload_groups res on sem.pool_id = res.pool_id where sem.resource_semaphore_id =0

В результате можно определить, каким объемом памяти для запросов располагает сервер и каждая группа, которую вы добавите или настроите.

После всех перечисленных выше манипуляций распределение памяти между запросами станет более эффективным, с минимумом ситуаций, когда запросы просто ждут выделения памяти и ничего не делают.

О плюсах и минусах AlwaysON, среды высокой доступности для БД, не писал только ленивый. Однако, информации о практическом использовании этой технологии для терабайтных баз уже не так много. Когда в далеком 2013 мы внедряли группы высокой доступности в Яндекс.Деньгах, информации не было даже о реальной эксплуатации в боевых средах. Наша основная база данных на тот момент как раз занимала около 4 ТБ, поэтому до многих вещей приходилось доходить самостоятельно.

За четыре года объем этой базы вырос до 20 ТБ, поэтому серверы с трудом справлялись с новой нагрузкой, а оптимизация индексов вызывала дополнительные проблемы, о которых ниже. Производительность БД во время процессинга кубов по сети была такая, что трафик мешал другим компонентам в сети, пока кластер не пересадили на отдельный мощный коммутатор.

В пиках доходило до того, что узлы теряли друг друга и БД на втором узле отключалась (привет кластерному кворуму). Но с этим разобрались добавлением новых сетевых карт;, а в новых машинах просто сразу ставили 10 Гб карты.

За вычетом перечисленных архитектурных проблем, общее впечатление от технологии AlwaysON положительное:

• Трафик Между узлами сжимается до 80–90%.

  
  

• Read-Only (RO) реплики быстро синхронизируются и возвращаются в строй после длительного отключения.

  
  

• Для клонирования БД в тестовую среду можно использовать log shipping (доставку журналов транзакций) в ручном режиме.

  
  
• AlwaysON очень надежен, а вот Windows Server Failover Clustering (WSFC) порой дает сбой и тянет за собой группы AlwaysON.

Чтобы минимизировать количество необоснованных решений службы кластеризации погасить кластер в конфигурации с двумя узлами, мы выработали решение с принудительным лишением голоса вторичного узла:

Import-Module FailoverClusters 

$node = "Srv1" 
(Get-ClusterNode $node).NodeWeight = 0 

С такими параметрами становится спокойнее при любых работах на вторичных узлах.

При эксплуатации больших БД с распределением нагрузки в группах AlwaysON может возникать рассинхронизация главного и вторичного узлов. Если на узле RO запустить долгую операцию SELECT к таблице, которая в этот же момент обновляется на главном узле, произойдет блокировка восстановления цепочки журналов изменений (LSN). Рассинхронизация коснется всех таблиц и будет держаться столько, сколько работает запрос — в это время данные в базе на реплике станут неактуальными.

Явных признаков рассинхронизации обычно нет, но на Read-Only узлах появляется новый процесс, запущенный от имени sa. Также часто возникают каскадные блокировки, которые можно быстро обнаружить с помощью описанной выше процедуры sp_whoisactive.

Причина — в Select, запущенном с уровнем изоляции READ COMMITTED (то есть со значением по умолчанию).

В качестве решения можно использовать параметр WITH (NOLOCK) в самом запросе или поменять уровень изоляции для базы данных либо сессии на SNAPSHOT для изоляции при долгих запросах выборки к таблицам.

Чтобы узнавать о рассинхронизациях раньше пользователей, мы используем скрипт для мониторинга. Он раз в 5 минут проверяет статус синхронизации и отправляет e-mail в случае проблемы.

declare @Delay int

set @Delay = 2

      select * into #tmpag_availability_groups from master.sys.availability_groups

      select group_id, replica_id,replica_server_name,availability_mode into #tmpdbr_availability_replicas from master.sys.availability_replicas

      select replica_id,group_database_id,database_name,is_database_joined,is_failover_ready into #tmpdbr_database_replica_cluster_states from master.sys.dm_hadr_database_replica_cluster_states

      select * into #tmpdbr_database_replica_states from master.sys.dm_hadr_database_replica_states

      select replica_id,role,is_local into #tmpdbr_availability_replica_states from master.sys.dm_hadr_availability_replica_states

      select ars.role, drs.database_id, drs.replica_id, drs.last_commit_time into #tmpdbr_database_replica_states_primary_LCT from  #tmpdbr_database_replica_states as drs left join #tmpdbr_availability_replica_states ars on drs.replica_id = ars.replica_id where ars.role = 1

SELECT   

--'' + AR.replica_server_name + '- ' + AG.name + ' ' + dbcs.database_name + '' AS SRV_AG_DB,

AR.replica_server_name + '- ' + AG.name + ' ' + dbcs.database_name  AS SRV_AG_DB,

--CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END  AS [EstimatedDataLoss],

--'' + Cast(ISNULL(dbr.last_redone_time, 0) AS varchar(50)) + '' As LastRedoneTime ,

Cast(ISNULL(dbr.last_redone_time, 0) AS varchar(50))  As LastRedoneTime ,

--'' + Cast((CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END)as nvarchar(10)) + '' AS [EstimatedDataLoss2],

Cast((CASE dbcs.is_failover_ready WHEN 1 THEN 0 ELSE ISNULL(DATEDIFF(ss, dbr.last_commit_time, dbrp.last_commit_time), 0) END)as nvarchar(10))  AS [EstimatedDataLoss2],

--'' + Cast(ROUND(ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1),-1) as nvarchar(10)) + ' ' AS [EstimatedRecoveryTime]

Cast(ROUND(ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1),-1) as nvarchar(10))  AS [EstimatedRecoveryTime]

INTO #tt

FROM

#tmpag_availability_groups AS AG

INNER JOIN #tmpdbr_availability_replicas AS AR ON AR.group_id=AG.group_id

INNER JOIN #tmpdbr_database_replica_cluster_states AS dbcs ON dbcs.replica_id = AR.replica_id

LEFT OUTER JOIN #tmpdbr_database_replica_states AS dbr ON dbcs.replica_id = dbr.replica_id AND dbcs.group_database_id = dbr.group_database_id

LEFT OUTER JOIN #tmpdbr_database_replica_states_primary_LCT AS dbrp ON dbr.database_id = dbrp.database_id

INNER JOIN #tmpdbr_availability_replica_states AS arstates ON arstates.replica_id = AR.replica_id

WHERE

--(AG.name='Nastro') and ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1) > @Delay

--(AG.name=@AGN) and 

ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1 ELSE CAST(dbr.redo_queue_size AS float) / dbr.redo_rate END, -1) > @Delay

IF  EXISTS (SELECT * from #tt)

BEGIN

declare @tableHTML nvarchar(max)

set @tableHTML =N'
AlwaysOn Status 
' 

set @tableHTML = @tableHTML +N'' + -- change the background color if you want

           N'' +            

            N'' +

            N'' +

            N'' +

            N'' +

            N'' +

           ISNULL(CAST ( ( 

SELECT * from #tt

    FOR XML PATH('tr'), TYPE 

            ) AS NVARCHAR(MAX) ),'') +

            N'' +

            N'
Server Group DB
LastRedoneTime
EstimatedDataLoss
EstimatedRecoveryTime
' ;

--send email 

EXEC msdb.dbo.sp_send_dbmail 

            @profile_name = 'mail', -- change here !!

            @recipients='Admin1@yandex.ru;Admin2@yandex.ru', -- change here !!

            @subject = 'AlwaysON Report',

            @body = @tableHTML,

            @body_format = 'HTML' ;

END

      DROP TABLE #tmpdbr_availability_replicas

      DROP TABLE #tmpdbr_database_replica_cluster_states

      DROP TABLE #tmpdbr_database_replica_states

      DROP TABLE #tmpdbr_database_replica_states_primary_LCT

      DROP TABLE #tmpdbr_availability_replica_states

      drop table #tmpag_availability_groups

      SELECT * from #tt

      drop table #tt

Еще одна частая причина рассинхронизации — работы по обслуживанию индексов. Например, у нас при перестройке кластерного индекса в таблице объемом 500 ГБ наблюдалась рассинхронизация на 10 часов.

Самый простой способ минимизировать это время — использовать опцию MAXDOP при создании или перестройке индексов. Я обычно ставлю значение »2» для индексов от 10–20 ГБ, а для более мелких — »4».

Нюанс с запросами на чтение из Read-Only реплик баз данных в том, что мало прописать в строке соединения параметр ApplicationIntent=ReadOnly — потребуется еще настроить маршрутизацию. Конечно, эта настройка относится к тем, которые, настроив однажды, больше не трогаешь, но его величество Случай ломал и не такие схемы. Например, в процессе работы может потребоваться перенаправить запросы с RO реплики на главную.

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

• Переключение было жестким, терялись текущие запросы к RO, что не очень здорово.

  
  
• При возврате трафика в RO часто возникала блокировка всех запросов с индикатором hadr_sync_commit для каждого из них. И вот это уже было совсем не весело, ведь, пока они висят, commit не происходит. Приходилось менять адреса подключений в клиентских приложениях, пока не нормализуется реплика.

Позже выяснилось, что правильнее перенаправлять RO-запросы через смену маршрутизации.

Вот пример для конфигурации с распределенной нагрузкой на 2 узла:

ALTER AVAILABILITY GROUP [AGGroupName]

MODIFY REPLICA ON N’PrimaryDB01′

WITH (PRIMARY_ROLE

(READ_ONLY_ROUTING_LIST=(‘SecondaryDB01‘,’PrimaryDB01‘))); -- Указываем, что RO запросы должны направляться сначала на узел SecondaryDB01

Пример с маршрутизацией всего трафика на главный узел

ALTER AVAILABILITY GROUP [AGGroupName]

MODIFY REPLICA ON N’PrimaryDB01′

WITH (PRIMARY_ROLE

(READ_ONLY_ROUTING_LIST=(’PrimaryDB01‘))); -- В данном случае все запросы будут направляться на узел PrimaryDB01;

В процессе смены маршрутизации запросы штатно завершаются, а следующие идут сразу на главную реплику.

За 4 года работы системы однажды случился сбой на RO реплике. Упал один из разделов с ошибкой CRC в файле с данными. Главный узел продолжал работать, а вот база на RO реплике нет.

Обработку отказа на уровне базы на RO узле и автоматическое переключение трафика на главный узел можно реализовать все той же маршрутизацией:

1. создаем задание с запросом к любой таблице в БД;

   
   

2. если запрос завершается с ошибкой, проверяем таким же способом БД на главном узле;

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

Что касается восстановления базы после подобного сбоя, то большую БД проще удалить из группы доступности (на главном узле она продолжит работу даже через Listener) и восстановить резервную копию на Read-Only узле в режиме no-recovery. Потом достаточно добавить восстановленную базу в группу доступности в режиме Join Only.

Комментарии (1)

• Diaskhan

  13 апреля 2017 в 17:02 (комментарий был изменён)

  0

  ↑

  ↓

  Стоило бы отметить колоночные индексы. То за что я люблю Ms Sql 2014.

  
  

  В банках кстати покупают обычно Oracle и Oracle Bi, потому что АБС обычно на оракле и пишут. Мол можно бизнес логику перенести!
  В качестве Bi инструмента используем Tableua. ну очень хороший Bi инструмент, самый первый !

  
  

  1.Вопрос каким Bi инструментом пользуетесь?
  2.Используете ли Tabular модель хранения, говорят она не умеет масштабироваться?
  3.Используете ли партиции для более эффективного расчета ?
  4.Как Масштабируетесь?
  5.Расскажите еще че нить интересное:)

  
  

  Ms поглотил DATAZEN и внедрил наработки в SSRS 2016, там тоже много нового и интересного !