Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 554
Дано - есть некая система, нагруженная и многопоточная, работает на SQL 2008 R2. Потоки независимо друг от друга пишут в таблицу MsgStatusLog со следующими полями:
  • Msg_GUID - GUID сообщения
  • Msg_Status - статус сообщения
  • Msg_DT - временная метка присвоения статуса
    Каждая запись пишется только 1 раз, потом не изменяется и только читается планировщиком задач/потоков.
    Раз в неделю таблице проводится обрезание старых записей (старше N дней), при этом требуется не прерывать основную работу системы. То есть - чтобы блокировки страниц во время удалений не пересекались с добавлением записей.

    По идее, если удаляем небольшими кусками (до 5000 строк), то эскаляции блокировок на таблицу можно избежать. Но при удалении выдается page lock. Значит, PageLock должен быть "далеко" от зоны активной работы для обеспечения непрерывности, в сильно других страницах (или я не прав?)

    Методики достижения результата, как видится сейчас:
  • Identity column и кластерный ключ на ней. Конструкция абсолютно бесполезная, только для обеспечения порядка страниц
  • Таблица типа heap с 100%-ным использованием страниц, и ожидаем, что страницы со старыми данными не содержат новые.
    Преимущества heap таблицы - нет избыточной сущности кластерного ключа. Минусы - порядок заполнения страниц не гарантирован.

    Буду признателен за комментарии и предложения, вплоть до смены подхода.
  • 27 сен 16, 16:02    [19714280]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9413
    Ferdipux
    Identity column и кластерный ключ на ней. Конструкция абсолютно бесполезная, только для обеспечения порядка страниц
    Это точно, бесполезная. Особенно если критерий отбора по дате. Кластерный нужно по Msg_DT.
    Ferdipux
    и ожидаем, что страницы со старыми данными не содержат новые
    Очень даже могут содержать, ибо куча.

    Ferdipux
    вплоть до смены подхода.
    http://www.databasejournal.com/features/mssql/partitioning-in-sql-server-managing-sliding-window-scenario.html
    27 сен 16, 16:20    [19714397]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    sti
    Member

    Откуда:
    Сообщений: 769
    Если хочется совсем беспроблемного удаления - партиционирование по дате. Но PK создавать также придётся.
    27 сен 16, 16:46    [19714554]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    WarAnt
    Member

    Откуда: Питер
    Сообщений: 2421
    Ferdipux,

    1. Msg_DT сделать default, повесть на него кластерный индекс, удалять по кластерному.
    2. 1 + таблицу сделать секционированной по этому же кластерному, удалять устаревшие секции.
    27 сен 16, 16:49    [19714581]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    aleks2
    Guest
    invm
    Ferdipux
    Identity column и кластерный ключ на ней. Конструкция абсолютно бесполезная, только для обеспечения порядка страниц
    Это точно, бесполезная. Особенно если критерий отбора по дате. Кластерный нужно по Msg_DT.

    Сам ты бесполезный.
    1. Кластерный индекс Identity точно обеспечивает запись очередного события в хвост списка.
    2. Msg_DT этого не обеспечивает, ибо неизвестно чего там дата и сколько между датой и фактом записи проходит времени.
    3. Отбор на удаление, скорее всего, не критичен по времени.
    4. Поскольку некая корреляция между Identity и Msg_DT наблюдаться будет - удаление будет беспроблемным.
    27 сен 16, 17:25    [19714769]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9413
    aleks2
    Сам ты бесполезный.
    1. Кластерный индекс Identity точно обеспечивает запись очередного события в хвост списка.
    2. Msg_DT этого не обеспечивает, ибо неизвестно чего там дата и сколько между датой и фактом записи проходит времени.
    Скучно что ли стало? Или в астрал познавать суть вещей не пустили?
    Перечитай задачу ТС и матчасть заодно в памяти освежи.
    27 сен 16, 18:00    [19714966]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    Ferdipux
    Member

    Откуда: Москва
    Сообщений: 554
    aleks2
    ...
    Сам ты бесполезный.
    1. Кластерный индекс Identity точно обеспечивает запись очередного события в хвост списка.
    2. Msg_DT этого не обеспечивает, ибо неизвестно чего там дата и сколько между датой и фактом записи проходит времени.
    3. Отбор на удаление, скорее всего, не критичен по времени.
    4. Поскольку некая корреляция между Identity и Msg_DT наблюдаться будет - удаление будет беспроблемным.

    В общем, есть рациональное зерно в ваших словах. Но - решение с кластерным индексом по Msg_DT и партицированием мне нравится больше, так как нет танцев с бубном - чтобы page lock не эскалировался на table lock, просто переключил партицию на пустую. Можно сделать модификацию решения с identity - сделать вместо него поле Creation_DT datetime с default - getdate(), по нему кластерный ключ и партиции, с точки зрения размера datetime и bigint - разницы никакой.
    27 сен 16, 18:12    [19715021]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    Adx
    Guest
    Не буду растекаться мыслею по древу, но тут классика - очереди FIFO через таблицу.
    Почему требуется PageLock? Что мешает обойтись без него?
    27 сен 16, 18:43    [19715136]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31444
    Ferdipux
    только читается планировщиком задач/потоков.
    Так как она читается?
    По Msg_GUID? По Msg_Status и Msg_DT? Как то ещё?
    А то сделаете identity, или кластерный индекс по Msg_DT, или Creation_DT - а потом ещё будет дополнительный индекс, с которым будут такие же проблемы при удалении записей.

    Если чтение этими вашими планировщиками идёт по Msg_DT, то лучше использовать кластерный индекс по Msg_DT и секционирование.
    Если чтение идёт по Msg_GUID, то может сделать кластерный индекс по Msg_GUID, и удалять мелкими порциями, вне общей транзакции? Через некоторое время на каждой странице будут старые и новые записи, сплиты прекратятся, выборки будут быстрые, а удаления мешать не будут.

    В общем, для принятия решения об архитектуре нужно смотреть на все операции, а не только на вставку и на проблемное удаление.
    28 сен 16, 00:38    [19716213]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    Ennor Tiegael
    Member

    Откуда:
    Сообщений: 3274
    А читаются записи потом сколько раз? А то, может, вам Service Broker нужен.
    28 сен 16, 08:24    [19716443]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    Ferdipux
    Member

    Откуда: Москва
    Сообщений: 554
    alexeyvg
    Ferdipux
    только читается планировщиком задач/потоков.
    Так как она читается?
    ...
    В общем, для принятия решения об архитектуре нужно смотреть на все операции, а не только на вставку и на проблемное удаление.

    Читается оно по Msg_GUID. Задача этой таблицы - получить последний по времени статус при проверке или восстановлении, которые бывают не часто, приблизительно раз в 2 часа. Так как сервер 2008R2 - делается это через select с rownum. Причина почему так - insert должен проходить как можно быстрее за счет чтения. Поэтому триггеры на эту таблицу не рассматривали.
    Также раз в день выполняется запрос - время между 2 соседними состояниями (длительность обработки) для сообщения. Тут вообще с точки зрения запроса все плохо-тяжело.
    Сервис-брокер - может быть, у меня с ним мало опыта, нужно проработать вопрос.

    alexeyvg
    Если чтение идёт по Msg_GUID, то может сделать кластерный индекс по Msg_GUID, и удалять мелкими порциями, вне общей транзакции? Через некоторое время на каждой странице будут старые и новые записи, сплиты прекратятся, выборки будут быстрые, а удаления мешать не будут.

    Хм, Msg_GUID - это случайный GUID, не sequentialguid MS SQL. Поэтому при кластерном индексе по нему - нельзя исключить ситуацию, когда новое сообщение попадет на страницу с уже старыми, вызвав ее сплит, в то время когда мы решили почистить эти старые данные.
    Это сторона приложения (сервиса), хотя попинать приложение тоже можно (вероятность решения меньше).
    28 сен 16, 10:25    [19716860]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31444
    Ferdipux
    Читается оно по Msg_GUID. Задача этой таблицы - получить последний по времени статус при проверке или восстановлении, которые бывают не часто, приблизительно раз в 2 часа.
    А Msg_GUID неуникальный?
    Ferdipux
    Поэтому при кластерном индексе по нему - нельзя исключить ситуацию, когда новое сообщение попадет на страницу с уже старыми, вызвав ее сплит, в то время когда мы решили почистить эти старые данные.
    Да, так я же говорю - сплит будет только первое время, при наполнении базы, до удаления старых данных.
    А потом на страницах будет по некоторому количеству старых и новых записей, в страницы будут добавляться новые записи, удаляться старые, сплитов не будет, если записи узкие.

    По сути, оно ведь так и происходит уже сейчас.
    Вы же всё равно вынуждены держать индекс по Msg_GUID, раз вы делаете по нему выборки?
    Вот этот индекс фактически всё равно будет той самой таблицей с кластерным индексом по Msg_GUID, там будут те же расщепления страниц и т.д.
    Ferdipux
    insert должен проходить как можно быстрее
    Правда, если вставки массовые, то это будет небыстро, т.к. доступ случайный. Впрочем, как и сейчас, при наличии индекса по Msg_GUID.
    28 сен 16, 11:31    [19717253]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9413
    Ferdipux
    insert должен проходить как можно быстрее
    Только инструкция insert? Реальное добавление данных можно отсрочить?
    28 сен 16, 11:36    [19717307]     Ответить | Цитировать Сообщить модератору
     Re: Выбор между кучей и таблицей с кластерным суррогатным ключом  [new]
    Ferdipux
    Member

    Откуда: Москва
    Сообщений: 554
    alexeyvg,

    Msg_GUID в таблице не уникально. Более того, получается что из перечисленных 3 полей ключа и нет, только суррогат.

    В целом, обдумал исходную задачу еще раз. По сути, приложение использует некую модель очередей в памяти. Для восстановления после сбоя - состояния объектов (Msg_...) и их данные сбрасываются в SQL. Можно попинать архитектора приложения выбирать средства по задачам и переползти на MSMQ или RabbitMQ или еще куда-то, но это сильно небыстро и не факт что будет... (:

    Итак, акторы приложения сбрасывают данные (куда-то, вне рамок данного обсуждения) и текущее состояние обработки. Читать это состояние нужно только или в случае восстановления очереди (и тогда нужно читать одно последнее), или всю таблицу для анализа статистики производительности приложения.
    Думается, что суррогат все-таки нужен, он хорошо разделит страницы.

    К исходной задаче - спасибо всем за ответы, сдвинули в осмыслении задачи с мертвой точкит. Возможно, лучше запилить через сервис брокер insert статуса как update в таблице текущего статуса и insert в таблицу истории статусов. Буду признателен за советы тех, кто Service Broker ел, у меня такого опыта мало.
    28 сен 16, 13:23    [19717890]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить