Триггер с вычислениями или выборка из достаточно большой таблицы?

← →
Иксик © (2011-04-14 19:19) [0]

Вот такая ситуация:
Имеется N (<5000) измерительных устройств/программ, которые посылают данные на центральный сервер с интервалом, скажем, в 5 минут. На сервере стоит MS SQL Server, в который это все пишется

В базе, среди прочего, есть две таблицы: "устройства" и "измерения".
В "устройствах" помимо проего хранится результат ПОСЛЕДНЕГО измерения, полученного с этого устройства.
В "измерениях" - полный история измерений за последние 2-3 месяца.

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

Проблема:

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

Теперь действия этой службы зависят от 10 последних измерений (для каждого устройства), т.е. логику нужно менять.

Я видел два быстрых решения:
1) После каждого нового измерения тупо делать выборку 10 предыдущих измерений для данного устройства
2) Писать строку состояния в таблицу "устройства" и обновлять ее после каждого нового измерения

Я боялся, что тысячи выборок из миллионов записей загрузят сервер по самое не хочу и выбрал вариант 2)

Как я это сделал: про поступлении новой записи в таблицу "измерения" срабатывает триггер и обновляет строку состояния в "устройствах", что-то типа:

SET @States = @States * 2
SET @States = @States | (SELECT INSERTED.[State] FROM INSERTED)

В принципе все работает, но SQL Server таки частенько ест более 70% CPU, а железо не самое слабое. Мне кажется я где-то таки напортачил.

Вот собственно вопрос: как сделать все это дело эффективнее? Может ли быть, что вариант 1) будет быстрее?

P.S. Сам знаю, что многа букофф

← →
Иксик © (2011-04-14 19:21) [1]

полный история = полная история

10 = 20 :)

← →
Jan (2011-04-14 20:17) [2]

а не проще сделать индекс на основной таблице <код устройства, дата выборки> и по нему выбирать данные select top 10 <поля> from <таблица измерений> where <код устройства> = <код> order by <дата выборки> desc
должно быть быстро

← →
Jan (2011-04-14 20:19) [3]

дополнительно указать еще использовать нужный индекс оптимизатору (не помню как это сделать в mssql)

← →
sniknik © (2011-04-14 20:54) [4]

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

простой выбор десяти последних получше, но только если между моментами выбора все 10 успевают смениться. если нет, то лучше выбирать 10 последних с id > чем максимальный предыдущий. придет меньше... зашибись, локально добавить "сверху" предыдущего списка, а "низ" подрезать до тех же 10... "круговорот воды в природе" в общем сделать.

← →
oldman © (2011-04-14 20:55) [5]

выкинуть из первой таблицы результат последнего измерения
завести треть таблицу с 10 последними измерениями
при поступлении нового измерения данные в таблице3 сдвигать
мониторинг вести по таблице3, необходимую статистику по таблице2

← →
Leon-Z © (2011-04-14 21:29) [6]

Нужно еще одну таблицу завести и там хранить 10 последних измерений и времен.
Так будет правилней с точки зрения расширяемости.
Представь, что завтра тебя попросят использовать 20 измерений.
Что тогда ???

← →
Leon-Z © (2011-04-14 21:34) [7]

> В "устройствах" помимо проего хранится результат ПОСЛЕДНЕГО
> измерения, полученного с этого устройства.

А вот это НЕВЕРНО !!!
Так делают (ПОСЛЕДНЕЕ ИЗМЕРЕНИЕ) в программах, которые работают с микроконтролерами или в микроконтролерах и используют плоские файлы для
хранения данных.
А у Вас SQL Server !!!

← →
Иксик © (2011-04-14 21:40) [8]

> Jan (14.04.11 20:17) [2]
>
> а не проще сделать индекс на основной таблице <код устройства,
> дата выборки> и по нему выбирать данные select top 10 <поля>
> from <таблица измерений> where <код устройства> = <код>
> order by <дата выборки> desc
> должно быть быстро
>

Я туплю или это то, что я описал в 1) способе?

> sniknik © (14.04.11 20:54) [4]

> локально добавить "сверху" предыдущего списка, а "низ" подрезать
> до тех же 10.

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

> oldman © (14.04.11 20:55) [5]

Это мысль! Так вместо выборки из миллионов, будет выборка из пары максимум сотни тысяч.

Sniknik, как ты думаешь, вариант oldman-a намного быстрее будет?

← →
Иксик © (2011-04-14 21:46) [9]

> Leon-Z © (14.04.11 21:29) [6]
>
> Нужно еще одну таблицу завести и там хранить 10 последних
> измерений и времен.
> Так будет правилней с точки зрения расширяемости.
> Представь, что завтра тебя попросят использовать 20 измерений.
>
> Что тогда ???
>

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

> Leon-Z © (14.04.11 21:34) [7]
>
>
> > В "устройствах" помимо проего хранится результат ПОСЛЕДНЕГО
> > измерения, полученного с этого устройства.
>
> А вот это НЕВЕРНО !!!
> Так делают (ПОСЛЕДНЕЕ ИЗМЕРЕНИЕ) в программах, которые работают
> с микроконтролерами или в микроконтролерах и используют
> плоские файлы для
> хранения данных.
> А у Вас SQL Server !!!

Это исторически обусловленно, раньше только последнее изменение имело значение, остальное - просто архив.

← →
Иксик © (2011-04-14 21:50) [10]

из пары максимум сотни тысяч. = из максимум сотни тысяч.

← →
sniknik © (2011-04-14 21:50) [11]

> вариант oldman-a намного быстрее будет?
немного медленнее моего... идея та же, только у него таблица, а я предлагал это в памяти. 10/20/50... пофигу, решается константой.

← →
sniknik © (2011-04-14 21:54) [12]

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

← →
Leon-Z © (2011-04-14 22:15) [13]

Ну тогда храните 10 последних измерений в RAM.

Например: одно измерение = 1 Кб данных.
5000 (датчиков) * 10 (измерений) * 1 Кб = 50 Мб. Не так уж много.
Организуйте что-то вроде кольцевого буффера.

Новые данные сначала в него записывайте, а затем
передавайте потоку, который запишет эти данные в БД.

Работа с RAM повысит эффективность по-максимуму.

← →
Игорь Шевченко © (2011-04-14 22:59) [14]

> 1) После каждого нового измерения тупо делать выборку 10
> предыдущих измерений для данного устройства

В оракле я бы выбрал именно этот путь. Как в оракле сделать - знаю, как в MS SQL - нет.

Но тут возникает вопрос - а зачем тебе делать выборку из миллионов записей ? Наверное ее не надо делать.

← →
Иксик © (2011-04-14 23:31) [15]

> Игорь Шевченко © (14.04.11 22:59) [14]

> Как в оракле сделать - знаю, как в MS SQL - нет.

Т.е. не стандартным SQL а как-то иначе?

> Но тут возникает вопрос - а зачем тебе делать выборку из
> миллионов записей ? Наверное ее не надо делать.

Игорь, объясни плиз. Как не надо, если нужны данные из этой огромной таблицы?

> sniknik © (14.04.11 21:54) [12]
>
> > промежуточная таблица даст.
> таблица не дат выигрыша... выигрыш будет при отказе от лишних
> операций/пере запросов, заполнений/поддерживаний заранее.
>
> Leon-Z © (14.04.11 22:15) [13]

Я вот пытаюсь решить, что есть большее зло - добавлять, тестировать, поддерживать внутренние структуры данных для работы с RAM или таки делать выборку из этого огромного архива.
Sniknik, а можно спросить почему ты думаешь, что выборки будут быстрее, чем эти мои триггеры? Я не ставлю под сомнение, просто мне было бы полезно понять, чтобы дальше считать.

← →
Игорь Шевченко © (2011-04-14 23:43) [16]

> Т.е. не стандартным SQL а как-то иначе?

Стандартным. Я бы ввел еще одно поле в таблицу "нужно/не нужно", у нужного в этом поле вводил бы NOT NULL значение (например, время поступления в базу), у ненужного, NULL, построил бы индекс по этому полю и выбирал бы нужные строки по этому индексу.

> Игорь, объясни плиз. Как не надо, если нужны данные из этой
> огромной таблицы?

ты же вроде сказал, что нужные последние N ?

← →
Иксик © (2011-04-14 23:53) [17]

> Игорь Шевченко © (14.04.11 23:43) [16]
>
>
> > Т.е. не стандартным SQL а как-то иначе?
>
>
> Стандартным. Я бы ввел еще одно поле в таблицу "нужно/не
> нужно", у нужного в этом поле вводил бы NOT NULL значение
> (например, время поступления в базу), у ненужного, NULL,
> построил бы индекс по этому полю и выбирал бы нужные строки
> по этому индексу.
>

Ну они в принципе все нужны, сразу после их появления. Т.е. последние 20 нужны, а потом они "становятся" архивом. Я думал запоминать последний обработанный datetime (это поле индексированно) и выбирать все, что больше него.

> Игорь Шевченко © (14.04.11 23:43) [16]
>
>
> ты же вроде сказал, что нужные последние N ?

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

← →
sniknik © (2011-04-15 00:03) [18]

> что выборки будут быстрее, чем эти мои триггеры?
выборки всегда легче и быстрее чем запись, хоть в триггере хоть где. перестройка индексов, запись на диск (почитай спецификации дисков. они там всегда разделяют чтение/запись), кеширование, логирование, транзакции (они есть даже если их нет). все требует времени. читающее у всего меньше чем у записывающего.
это раз.

+
читать нужно только тогда когда данные реально понадобятся, т.е. включили монитор на пол часа значит 23.30ч свободно. а триггер "трудится" невзирая на нужность 24 часа в сутки.
это два.

← →
Игорь Шевченко © (2011-04-15 00:15) [19]

Иксик © (14.04.11 23:53) [17]

Я исходил из такой организации данных:

CREATE TABLE sensor_data (
sensor sensor_domain_type NOT NULL,
measure value_domain_type NOT NULL,
measure_time timestamp NOT NULL
)

и из того,что нужных измерений небольшой процент (скажем, сотая часть или меньше)

Я бы ее преобразовал в
CREATE TABLE sensor_data (
sensor sensor_domain_type NOT NULL,
measure value_domain_type NOT NULL,
measure_time timestamp NOT NULL,
needed_measure_time timestamp
)

по полю needed_measure_time построил бы индекс, по поступлении очередного значения измерения устанавливал бы needed_measure_time у самого старого в NULL

(это в качестве грубой прикидки, ни в коем случае не претендующей на решение, и, к тому же, для Oracle)

← →
b z (2011-04-15 00:36) [20]

70%... это много для обновления записи.
Вы точно вычислили это "узкое" место?
Ну а в остальном согласен с тем что доп. таблица излишня, .. и тригер.

← →
Иксик © (2011-04-15 00:49) [21]

> sniknik © (15.04.11 00:03) [18]
> Игорь Шевченко © (15.04.11 00:15) [19]

Спасибо!! Не, измерения нужны все и мониторинг работает постоянно (в нем в принципе вся суть).

Ясно... значит с триггером я ступил :) А думал, что хорошая идея :)

> b z (15.04.11 00:36) [20]
>
> 70%... это много для обновления записи.
> Вы точно вычислили это "узкое" место?

Кстати, вот я же еще это хотел узнать!! Наверное это вопросы к Сникнику: есть ли какой-то способ промониторить что именно жрет ресурсы процессора? Я пытался через windows-овский performance monitoring, но по-моему не нашел правильных маркеров.

← →
Игорь Шевченко © (2011-04-15 00:53) [22]

> Не, измерения нужны все и мониторинг работает постоянно
> (в нем в принципе вся суть).

Что значит - все ? Ты только что говорил, последние 10. А сколько их всего для каждого устройства ?

← →
Иксик © (2011-04-15 01:14) [23]

Вот в этой таблице measurements история всех измерений за последние 3 месяца. После каждго нового измерениям мне нужно взять 20 последних и на их основе что-то сделать. Т.е. если в базе всего 25 измерений для этого устройства, то берутся с 6-го по 25-ое, но как только появляется 26-е, надо снова взять с 7 по 26-ое. Постепенно их накапливаются миллионы и на каждом конкретном шаге нужны последние 20.

← →
sniknik © (2011-04-15 01:17) [24]

> Не, измерения нужны все и мониторинг работает постоянно
> (в нем в принципе вся суть).
что значит постоянно?

5000 устройств, с интервалом, в 5 минут. это 17 изменений/записей/срабатываний триггера в секунду... кино крутится с 25 кадров секунду. у тебя кино? ну и сколько кадров в нем последовательно различаешь/анализируешь?
кто среагирует если ты в мониторинге будешь 17 раз в секунду статусы менять?.

а если ты читать будешь хотя бы в 2 раза реже чем измеряешь... человек не заметит, а нагрузка тоже в 2 раза меньше станет.
+ отобразить все невозможно, в экран не влезет, значит и читать можно не все, всегда, а только то что требуется для отображения... это еще раз в 100 "облегчит участь сервера".

← →
sniknik © (2011-04-15 01:19) [25]

> и на каждом конкретном шаге нужны последние 20.
блин. почитай внимательно мой первый пост...

← →
Иксик © (2011-04-15 01:26) [26]

> sniknik © (15.04.11 01:17) [24]
>
> > Не, измерения нужны все и мониторинг работает постоянно
> > (в нем в принципе вся суть).
> что значит постоянно?
>
> 5000 устройств, с интервалом, в 5 минут. это 17 изменений/записей/срабатываний
> триггера в секунду... кино крутится с 25 кадров секунду.
> у тебя кино? ну и сколько кадров в нем последовательно
> различаешь/анализируешь?
> кто среагирует если ты в мониторинге будешь 17 раз в секунду
> статусы менять?.

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

> sniknik © (15.04.11 01:19) [25]
>
> > и на каждом конкретном шаге нужны последние 20.
> блин. почитай внимательно мой первый пост...

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

← →
Иксик © (2011-04-15 01:27) [27]

Кстати, посмотрел сейчас - пики процессора совпадают с пиками записи на диск (ты пророк!:)). На первом месте по загрузке диска стоит .ldf файл.

← →
Jan (2011-04-15 01:30) [28]

> Нужно еще одну таблицу завести и там хранить 10 последних
> измерений и времен

это точно не нужно. потому как нужно синхронизировать таблицы, что не правильно

> Так будет правилней с точки зрения расширяемости.
> Представь, что завтра тебя попросят использовать 20 измерений.

вот именно:) не вижу ничего страшного в выборе из основной таблицы по индексу (тем более это не всегда нужно)

← →
Jan (2011-04-15 01:36) [29]

> Я туплю или это то, что я описал в 1) способе?

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

> На первом месте по загрузке диска стоит .ldf файл.
вот кстати еще один путь снизить нагрузку, повысить производительность, админский - положить ldf на физически другой диск. т.е. не другой раздел, одной железки, а на другую железку.
тот же смысл, убираются лишние действия по синхронизации/позиционировании при практически одновременной записи в разные места одного устройства (значит неизбежны очереди/таймауты). т.е. там где одна "крутая" железка не справляется, 2 меньшей, даже вполовину мощности вполне могут...

я б попробовал
1. класть новые в отдельную таблу (OperMeasur)
2. джоб периодически из нее сливает в основную(ArchMeausr), с вычищением из OperMeasur
ArchM - заточена по индексам на select, OperM - на insert

> sniknik © (15.04.11 07:57) [30]

правда в условиях raidов всевозможных не понятно, есть ли смысл
+ еще TempDB перенести советуют на другой диск

> в условиях raidов всевозможных не понятно, есть ли смысл
тогда отдельным устройством будет сам рейд... т.е. суммарная мощность возрастет в 2 раза, и все еще не понятно?
смысла не будет тогда, когда не диски станут "слабым звеном", а другое, память например, и основные задержки ожидания операций переместятся в нее... т.е. еще не скоро.
или если нагрузка не такая, как описано, с постоянным потоком данных на запись, а периодическая, т.е. диск сам ждет операций и никаких таймаутов у него нет. а с периодическими "наплывами" справляется кеш/буфер в памяти, или в самом диске. но в таких случаях снижать нагрузку на диск и не требуется.

> sniknik © (15.04.11 09:45) [32]

> тогда отдельным устройством будет сам рейд... т.е. суммарная
> мощность возрастет в 2 раза, и все еще не понятно?

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

А я имею ввиду - разнос по дискам vs эти же диски в рейд смонтировать. Что круче?

> Что круче?

Фронт прямоугольного импульса

Из статей на sql.ru по поводу mssql говорили всякое. И так, и так.
Мои мысли такие: raid - штука универсальная. Она не может знать потребности MSSQL сервера, так как положено ему, серверу.
У меня была проблема - по всем тестам (предложенным там же, на sql.ru) выходило, что траблы в винте. А стоял raid на тот момент.
Я стал пинать сисадмина, что траблы в винте. Он, спеной у рта, стал доказывать, что raid - предел моих мечтаний, и ничего делать не надо. Он был круче, в глазах начальства, на том все и остановилось.
Достало это меня и моего начальника и мы ночью взяли и все переставили, помощнее выбрали из обычных ПК и поставили. По-уму, как советовали и как мне тогда представлялось (и сейчас, правда, есть такое мнение):

Ось - винт1, swap оси - винт2, mdf - винт3, ldf - винт4, tempDB - винт5, instance sql - винт6.

Заработало на больших выборках в 2 раза быстрее. На мелких медленнее, но чуть-чуть.

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

Сисадмин прифигел утром, и повез сервер на диагностику.
И! нашлось, что raid-контроллер мозгачит.

Потому вопрос остался лично для меня открыт. Так что же лучше? разнос по дискам vs эти же диски в рейд смонтировать
Однако мысль:
raid - штука универсальная. Она не может знать потребности MSSQL сервера, так как положено ему, серверу.
не дает покоя...

> эти же диски в рейд смонтировать.
зеркало? стрип? 5й -10й уровень?

если имеются только 2... выбор не большой зеркало или "стрип"
> Что круче?
зеркало надежнее, но самое медленное, стрип быстрый т.к. также "разносит" операции по разным, но не специализирован, т.е. не поделит нагрузку "ровно" положив части на разные, для него вообще файлы это абстракция... а сымым быстрым будет 2 диска с разнесенными рабочей, и базой транзакций.

ИМХО (и кстати "круче" ни один из вариантов не будет, только применительно к ситуации разные параметры у них будут)

> Фронт прямоугольного импульса
яйца вкрутую еще круче! это вообще крутейшая вещь известная человечеству. ;)

> Игорь Шевченко © (15.04.11 10:25) [34]

Игорь, меня тоже иногда достает, когда пишут плохо. Потому понимаю тебя.
Но вопрос формулировать долго, тем более, вроде, его все правильно должны понять. Минималистика и простота в словах, к которой стремлюсь, наверное, иногда бывает все же лишней.

Прошу извинений, на всякий случай, если кому-то еще не нравится стиль такой :)

> зеркало? стрип? 5й -10й уровень?

далек от этого :)
Насколько понимал админа:
Было две пары. Одна пара распараллеливает операции, вторая зеркалирует
И еще одна пара винтов, не знаю как была она. Там все остальное, к БД не относящееся

До чего дошёл прогресс.
На две таблицы -- рейд, шесть физических дисков...

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