@uaggster

Коллеги, помогите советом!

Есть некая система, которая отслеживает состояние неких приборов (типа электросчетчика, но это точно).
Приборы опрашиваются периодически, существенно асинхронно, каждый по собственному расписанию.
В табличку складывается история опроса приборов:
ИД прибора, Дата/время опроса, Статус прибора (... еще что-то до кучи, но это не существенно).
Задача:
1. Максимально быстро получить верхнюю, по времени, запись, относящуюся к прибору / группе приборов / ко всем приборам.
2. Максимально быстро регистрировать такие события.
3. Максимально быстро получить верхнюю, по времени, запись, относящуюся к прибору / группе приборов / ко всем приборам на определенный момент времени, ниже текущего (это наименее приоритетная задача). Если первые 2 работают быстро, то 3 - может потерпеть.

Нюансы: приборов 10-50 млн.
Количество опросов каждого прибора: ~ 1-100 раз в сутки.

В таблице/базе не обязательно хранить данные "за всегда". Примерно за месяц они теряют актуальность и хоронятся в хранилище.
Но на период актуальности они хранятся в одном месте, и нужно обеспечить пп. 1-3.

Собственно, вопрос: "Куды бечь?"
Такое впечатление, что это задача для кликхаус, но из клихаус у нас только MSSQLSERVER и костыли.
Пожалуйста, поделитесь идеями, даже тривиальными!

0

@Ludwig Valentin

Можно проиндексировать дату записи.
Можно каждый месяц архивировать данные из текущей таблицы в большую таблицу архива- а текущая рассчитана на данные последнего месяца.

0

@uaggster

Проблема в том, что количество записей в сырой рабочей таблице будет порядка 5 млрд. в устоявшемся состоянии.
Такая таблица сама по себе довольно тяжелая.
Хотелось бы сжать ее чем-то вроде колумнстора.

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

Была мысль разрезать таблицу на 2 секции - первую с установленным признаком "самая свежая запись", вторую - со сброшенным признаком.
И, соответственно при заливке пакета данных по опросам приборов (записи, разумеется, льются не по одной, а пакетами 1000-100000 записей, буферизация, короче говоря), мерждить порцию с установкой признака "самая свежая" вновь заливаемым данным, и сбрасыванием этого признака со всех остальных записей, соответствующих id приборов из загружаемого пакета.
И таким образом получить кусок таблицы в которой заведомо самые свежие записи лежат.
Но я что-то слабо представляю себе рост затрат на загрузку. ИМХО это будет чудовищно.

Или сразу лить данные в две таблицы: "самые свежие" и "все".
Но с заливкой во "все" вопросов нет. Берем и просто валим туда записи. Кластерный индекс делаем по (дате опроса счетчика DESC, ид счетчика), и используем для запросов типа 3.
А максимально быструю таблицу на 50 млн. - "только актуальные состояния" уже для типа 1.

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

0

@Аватар

с двумя таблицами возможно еще "чудовищней" затраты будут, все таки вместо update будет insert и delete. а эксперимент с реальным объемом данных провести на одной таблице неподъемно?

0

@invm

В качестве бредовой идеи - темпоральная таблица

1

@PaulWist

Вопросы:

1. Кем/как опрашиваются приборы, предполагаю, что это SCADA-система или всё сразу валится в MSSQL прям с датчиков??

2. Если есть прослойка в виде SCADA, то какими порциями и по сколько датчиков, как часто она выдаёт на сервер БД ??

0

@uaggster

Ну, отдельный слой конечно. Точнее - десятке два различных приложения, которые опрашивают датчики, буферизируют ответ и формируют пакет для "захоронения" на MSSQL. По собственному расписанию.
Это не реалтайм система, там мгновенная доступность данных не нужна.
Не совсем SCADA, но близко.

0

@PaulWist

Ага.

Значит если для каждого приложения своя таблица, то из 5млрд получаем 250 млн записей, уже легче

Нууу и вторая таблица/view нужна, куда триггером складывать "последние" значения.

PS а как сейчас организован сбор и хранение - всё в одну таблицу??

0

@uaggster

Неа.
Но, в принципе, можно сегментировать по признаку опросчика или региона.

Да.
В результате отклик системы за гранью добра и зла.

0

@katamoto

А скинь мячик планы )

0

@Jack Famous

uaggster, я бы делил по какому-нибудь признаку в отдельные таблицы.
Меньше таблица = выше скорость работы с ней.

0

@uaggster

Пробуем покатать на тестах.

В нынешних нет ничего интересного. Clustered columnstore scan + compute scalar + sort.

Запросы примерно такого вида:

0

@invm

1. Добавить столбец-признак - последняя добавленная строка или нет
2. Секционировать по этому столбцу
3. Пари вставке потребуется снимать признак у текущей "последней"

0

@Ludwig Valentin

Добавить столбец с датой записи.
Секционировать по этому столбцу.

0

@uaggster

Нет, так не пойдет. Верхняя запись может появиться 2 минуты назад, а может и полгода как уже.

0

@Ludwig Valentin

Говорилось о миллионах записей.
Даже в худшем случае по-вашему архив за полгода будет пустой.

0

@uaggster

Да, тоже думал об этом.
Но:
1. Добавлять записи придется через merge, а не insert. Ну, или upsert какой-то городить.
2. Возникнут накладные расходы при перемещении записей между секциями.

Собственно, а не будет ли выгоднее просто нарисовать индекс по полю "верхняя строка" + ИД, может даже и фильтрованный?
Хотя с секциями "верхняя" - "исторические", на первый взгляд, поинтереснее. Если сделать кластерный индекс по ИД +признак верхняя строка, и секционировать по признаку верхней строки, то кей-лукапов, в отличие от случая фильтрованного некластерного индекса не будет.
Да и сжать секции можно по-разному, насколько я понимаю. Верхнюю секцию оставить rowstore, историческую сделать columnstore (так, вроде, можно).

Буду думать.

Добавлено через 52 секунды
Ну не полгода, ну два дня назад. Что это меняет?

0

@Ludwig Valentin

вы определитесь с анализом.
вы архивируете все текущие значения (insert)
или вам нужны уникальные значения состояния системы (merge)?

0

@uaggster

1. Я архивирую все текущие
2. Для 80% случаев применения, нужно именно текущее, верхнее состояние.
3. Однако для 20% случаев - нужны все состояния прибора, либо N последних, либо не позднее какого-то момента времени.

Поэтому и была мысль хранить всё в двух таблицах. В первой - только верхнее состояние, во второй - все состояния.
Проблема в том, что это тоже не спасает от upsert'a
В верхнюю таблицу нужно будет merge, в общую - можно insert.
Но тут еще всякие негативные моменты с целостностью вылезут.

Мысль по поводу секционирования - мне нравится больше. Не спасает от upsert'a, но хоть экземпляр данных один, и не придется согласовывать данные.
Таблица с системным управлением версиями (темпоральная), тоже кажется интересным решением, но опыта работы с ними нет.
И в пользу секционирования плюсом идет возможность по-разному сжать секции, чего хотелось бы.

Добавлено через 2 минуты
Чтобы выделить верхнюю запись признаком "текущая", нужно для всех остальных записей этого прибора этот признак сбросить.
Это upsert. Можно merge, можно на коленке.

0