Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
Добрый день!
Дано: Синхронная реплика AlwaysOn, таблица 763 млн. строк, 77 Гб весом. На ней кластерный индекс с таким же весом в 77 Гб и фрагментацией 42%.
Задача: перестроить его для уменьшения фрагментации.
Проблема: При ребилде для других процессов начинаются множественные ожидания типа HADR_SYNC_COMMIT (ожидание коммита на синхронной реплике), из-за чего всё встает колом. Узкое место - сеть, но между серверами стоит 10 Гбит, расширять некуда.
Ребилд выполняется командой
ALTER INDEX [CI_Index] ON Table
REBUILD WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = ON, ONLINE = ON)

Рекомендуют ограничить ребилд одним потоком:
ALTER INDEX [CI_Index] ON Table
REBUILD WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = ON, ONLINE = ON, MAXDOP=1)

Но картина не меняется, что неудивительно, поскольку MAXDOP=1 стоит на уровне всего сервера.

Дело именно в размере индекса, поскольку после проявления проблемы я ограничил размер обслуживаемых индексов 5 Гб, после чего план отработал без подобных проблем. Осталось считанное число по факту необслуживаемых индексов.

Один из вариантов решения, которые предлагают - на время реиндексации переводить реплику в асинхронный режим, после окончания - возвращать обратно. Но хотелось бы решить проблему как-то более гуманно.
8 апр 18, 22:59    [21322143]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
делайте реорганизацию, от фрагметации не избавит,я так делаю, но уменьшит ее, либо стройте по партициям, можно вообще тогда не делать обслуживание индексов, если получится.
А вообще я бы понаблюдал , есть ли отставание реплики, возможно и те индексы негатив создают.
И вообще так необходимо перестраиваить, реально смотрели ? или по феншую типа тоого?
9 апр 18, 01:22    [21322189]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Oblom,

Как сказали выше а вам точно это надо.
Смотрите. У вас стоит maxdop 1, значит или вы не понимаете зачем он или у вас прям чуть ли не 100% OLTP система. Если последнее, или если у вас SSD, фрагментация (external) не будет влиять на производительность никак. В таком случае будет влиять internal, т.е. если страницы у вас полупустые, тогда у вас будет больше индекс и он будет больше занимать места в памяти.
Плюс вы делаете Online ребилд, он пишет в лог намного больше чем offline, попробывал сейчас на свой системе при ребилде индекса 5.4 MB при offline лог вырос примерно на эту цифру, при online на 12-13 MB.
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
Поэтому у вас вариант или делать offline (если можно конечно) или делать reorginize частями.
9 апр 18, 06:32    [21322229]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
aleksrov
Member

Откуда:
Сообщений: 948
aleksrov,

Реарганизация также генерит множество записей, в особенности для сильно фрагментированных индексов
https://blogs.msdn.microsoft.com/timchapman/2012/09/28/index-rebuild-vs-reorganize-the-transaction-log-edition/
Но зато вы можете ее остановить без потери результата.
9 апр 18, 06:36    [21322233]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Alexander Titkin
Member

Откуда: Москва
Сообщений: 91
При построении индекса эффект тот же самый, это чтобы отмести гениальные вариации на тему "оно вам надо?". SQL Server 12, 14.
9 апр 18, 08:49    [21322305]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
aleksrov
Oblom,

Как сказали выше а вам точно это надо.
Смотрите. У вас стоит maxdop 1, значит или вы не понимаете зачем он или у вас прям чуть ли не 100% OLTP система. Если последнее, или если у вас SSD, фрагментация (external) не будет влиять на производительность никак. В таком случае будет влиять internal, т.е. если страницы у вас полупустые, тогда у вас будет больше индекс и он будет больше занимать места в памяти.
Плюс вы делаете Online ребилд, он пишет в лог намного больше чем offline, попробывал сейчас на свой системе при ребилде индекса 5.4 MB при offline лог вырос примерно на эту цифру, при online на 12-13 MB.
http://sqlblog.com/blogs/kalen_delaney/archive/2011/03/08/what-gets-logged-for-index-rebuilds.aspx
Поэтому у вас вариант или делать offline (если можно конечно) или делать reorginize частями.


1. У меня прям 100% OLTP-система и да, конкретно эта таблица на SSD.
2. ONLINE жизненно необходим. Таблица высоконагруженная, по факту ядро системы. Любой простой - минус в премии и в карме. По факту online-rebuild единственная причина, почему куплен Enterprise, а не Standart. Вторая причина - этот самый AlwaysOn )))

За ссылку и совет по REORGINIZE огромное спасибо, буду курить.
9 апр 18, 08:58    [21322312]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
да, забыл SELECT @@VERSION
Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)
Nov 8 2017 17:32:23
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
9 апр 18, 09:00    [21322314]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 648
Блог
Oblom,

обновляйтесь до 2017-ого сиквела
9 апр 18, 09:28    [21322340]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 648
Блог
В 2017 -ом такая фитча есть
resumable index rebuild
9 апр 18, 09:40    [21322374]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
ОперацияПингвин
В 2017 -ом такая фитча есть
resumable index rebuild


Круть.
Спасибо за совет. Только мы все новогодние праздники убили, чтоб обновиться с 2005 на 2016. Боюсь теперь ещё лет 10 на такую авантюру никто не подпишется )))
9 апр 18, 09:47    [21322407]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
ОперацияПингвин
Member

Откуда:
Сообщений: 648
Блог
Oblom
ОперацияПингвин
В 2017 -ом такая фитча есть
resumable index rebuild


Круть.
Спасибо за совет. Только мы все новогодние праздники убили, чтоб обновиться с 2005 на 2016. Боюсь теперь ещё лет 10 на такую авантюру никто не подпишется )))


C 2016 до 2017 должно быть более безболезненно, чем с 2005 до 2016
9 апр 18, 09:58    [21322439]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
ОперацияПингвин
Oblom
пропущено...


Круть.
Спасибо за совет. Только мы все новогодние праздники убили, чтоб обновиться с 2005 на 2016. Боюсь теперь ещё лет 10 на такую авантюру никто не подпишется )))


C 2016 до 2017 должно быть более безболезненно, чем с 2005 до 2016


Говорят, у нас даже лицензионные права это позволяют. Но точно не раньше нового года, а сейчас ещё только апрель ))) Так что проблему как-то надо решать.

Но судя по всему проблема известная и красивых решений не имеет. Либо сваливаться с синхронной на асинхронную реплику, либо партицировать таблицу вместе с индексом и жрать его по кусочкам.
9 апр 18, 10:04    [21322454]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Oblom,

Ребилд больших индексов в принципе гемор как таковой, даже без AG.
У нас стандарт, ребилд делать не могу, т.к. приложение валится в ошибки если таблица недоступна, даже ночью, когда никто не работает, идут потоком автоматические задания, которые перенести нельзя, поэтому тоже делаю только реорг. и иногда ребилд, когда система отключается для проведения каких либо работ.
9 апр 18, 10:14    [21322475]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Danion
Member

Откуда: Москва
Сообщений: 252
Встретил похожую проблему. Причем при попытке сжать раздутый лог появилась похожая проблема и пришлось отменять.
При обработке ребилде индекса размером около 5 ГБ вверх полезли ожидания HADR_SYNC_COMMIT, у пользовательских запросов последнее ожидание стало HADR_SYNC_COMMIT, начали блокировть друг друга. (а о сессию ребилда нет).
По дашборду AlwaysOn виден рост Log send queue size и redo queue size.
При этом между машинами сетка 10Гбит, а нагрузку в этом момент показывает около 40 mbytes, что далеко от максимума.
Реплики синхронные.
Этот же кластер AlwaysOn из 2 ВМ на прошлом железе сидел на одном хосте, потому что на разных даже на пользовательских запросах вставал в HADR_SYNC_COMMIT, сетевики присылали скрин передачи файла между хостами с нормальной скоростью и говорили, что всё ок. Но что-то такой метод не убеждает.
На новом железе реплики смогли жить уже на разных нодах, но при заметных изменениях опять лезет вверх HADR_SYNC_COMMIT. Базы большие, как и таблицы, там и пользовательские изменения могут быть значительными.
Проблему хочется решить, а не убирать вызывающие действия. Пока выглядит, что что-то настроено не так. Возможная настройка MS SQL (но вряд ли, ничего не менялось, а на разных хостах виртуализации работа разная), настройка ВМ или сетевая. У кого-то есть мысли в какую сторону искать и что проверить?
25 мар 21, 10:02    [22299686]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10802
Блог
Проблема не в сети, а в низкой производительности REDO на репликах. Это самое узкое место AlwaysON.
От ребилда можно уйти, если разместить данные на SSD, где его не нужно делать. Этим и решите проблему.
Убедитесь для начала, что REDO у вас на репликах параллельный, это видно в системных сессиях.
25 мар 21, 10:37    [22299697]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Если редакция позволяет, на время ребилда можно переключить реплику в асинхронный режим.
25 мар 21, 11:05    [22299711]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Danion
Member

Откуда: Москва
Сообщений: 252
Александр Гладченко,

На время убирали дефрагментацию индексов, но встретили пару случаев со странным выполнением запросов на ноде для чтения, тогда помог ребилд одного из индексов участников. Про SSD и дефрагментация руководство в курсе, их это устраивает. Настаивают на возвращении раз в неделю, смысла спорить особо нет. Да и рост HADR_SYNC_COMMIT наблюдается не только во время обслуживания индексов, просто им проще всего отловить.

Для однократного выполнения перевести в асинхронный режим возможно, но для постоянного не стоит. Да и сколько будет идти синхронизация после? Сейчас при минуте выполнения, при остановке потом ещё минуты 2 в норму приходит.

Вот по redo можно проверить. При проверке sp_who на ноде для чтения есть сессии background PARALLEL REDO TA, но не по всем базам, при этом по основным БД таких сессий сейчас не вижу. TF 3459 не включен и он вроде на весь сервер, а не отдельные БД.
25 мар 21, 11:30    [22299738]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10802
Блог
Danion,

Паралельный REDO включается для тех баз, для которых выполняются необходимые для этого условия, они для разных версий/редакций могут отличаться. Но есть ещё одно неприятное ограничение, он включается только для первых 7 баз в порядке их перехода в онлайн после старта. Кто первый встал - того и тапки. Это может стать проблемой для больших баз с высокой нагрузкой, которые попадают под раздачу на стартапрекавери...
Ребилд на SSD полезен только для ускорения чистки фантомных записей, когда в пользователькой таблице устраивают "звёздные войны" с большими перетрубациями строк. Такое лучше переводить на хекатон. Ребилд же для дефрагментации даже раз в неделю ни к чему хорошему не приведёт, зато время жизни SSD подсократит...

Сообщение было отредактировано: 25 мар 21, 11:58
25 мар 21, 11:40    [22299741]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Danion
Member

Откуда: Москва
Сообщений: 252
Александр Гладченко,

Вот тоже наткнулся на информацию, что там первый попадают, а не все.
Версия 2017 Enterprise.
Вижу 6 из 18. Примерно самые мелкие.

When the host server has 32 or more CPU cores, each database will occupy 16 parallel redo worker threads and one helper worker thread. It means that all databases starting with the 7th database (ordered by database id ascending) that has joined availability group it will be in single thread redo or serial redo irrespective which database has actual redo workload. If a SQL Server Instance has a number of databases, and it is desired for a particular database to run under parallel redo model, the database creation order needs to be considered. Same idea can be applied to force a database always runs in serial redo model as well.

Базы добавлены в Always On добавлены давным давно, порядок мог быть любой. Если при включении инстанса набирается заново, то основные тяжелые базы будут последними подниматься.

Вроде бы упоминается возможно заставить базу всегда работать в serial redo. Попробую поискать как определенную базу из мелких заставить работать без параллельного и освободить места.
25 мар 21, 11:50    [22299744]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10802
Блог
Можно попробовать перед рестартом службы перевести некритичные базы в офлайн...
Про превые 7 в порядке ID кажется лажа, я пробовал создавать базы на новых зеркалах в "правильном" порядке - но наблюдал неоднократно, что на меньших ID не было паралельного REDO, приходилось отключать паралельный REDO трейсфлагом (DBCC TRACEON (3459, -1); -- Disables parallel redo.) и потом убирать флаг - после такой взбучки включалось как надо. ИМХО, он включается по порядку ID за вычетом недоступных на старте баз.

Сообщение было отредактировано: 25 мар 21, 12:08
25 мар 21, 12:12    [22299757]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Danion
Member

Откуда: Москва
Сообщений: 252
Александр Гладченко,

На постоянной основе пока варианта отключить только для конкретной базы не вижу. Нужные базы с ID 8 и 9. При этом работает для 14, 20, 21.

Через флаг для всего сервера можете подсказать? Там на вторичной ноде нужно делать?
Вроде так включить\выключить. Рестарт пишут, что с 2017 для включения параллельного redo не нужен.

DBCC TRACEON (3459,-1);
GO
DBCC TRACEOFF (3459,-1);

Правда читал, что у кого-то наоборот с параллельным редо ещё проблемы начались, ну отключить тут проще.
25 мар 21, 12:21    [22299761]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10802
Блог
Да речь как раз про тот флаг, что у Вас. Он действует на все базы зеркала.
REDO много на что плохо реагирует, паралельный в некоторых случаях "замирает" на некоторых операциях с кучей, это сигнализируется особым ожиданием и есть на это статья в КБ.

Сообщение было отредактировано: 25 мар 21, 12:36
25 мар 21, 12:44    [22299775]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Я просто хочу заметить, что HADR_SYNC_COMMIT -- это не про redo, а про передачу лога на реплики.
Т.е. это ожидание того, что commit запишется в лог реплики (а не применится вся транзакция).
26 мар 21, 00:17    [22300154]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Maks Bragar
Member

Откуда: UA->AT
Сообщений: 165
Гавриленко Сергей Алексеевич
Я просто хочу заметить, что HADR_SYNC_COMMIT -- это не про redo, а про передачу лога на реплики.
Т.е. это ожидание того, что commit запишется в лог реплики (а не применится вся транзакция).


Именно так + лог блоки жмутся и распаковываются.

А у вас точно секондари такой же по железу как и праймери?
Что с ЦПУ на обоих репликах в момент ребилда?

п.с.
у меня HADR_SYNC_COMMIT растет во время таких операций, видно даже глазами в мониторе, но олтп нагрузка "успевает" коммититься

С ув, Макс
26 мар 21, 15:30    [22300392]     Ответить | Цитировать Сообщить модератору
 Re: HADR_SYNC_COMMIT при REBUILD огромного индекса в синхронном AlwaysOn  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10802
Блог
Если глаза морочит HADR_SYNC_COMMIT, проверьте журнал на зеркале, вдруг у вас для журналов размер сектроа разный и весь журнал забит сообщениями о "подгонке" записи под правильный сектор.
26 мар 21, 15:34    [22300397]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить