Комментарии 23
Я вам открою большой секрет — если использовать триггеры и агрегатные таблицы, то можно будет читать суммы практически мгновено. Суть в том, что нагрузка по суммированию размазывается по времени, а чтение просто значения практически ничего не стоит.
Случай с суммированием всей таблицы рассмотрен лишь для простоты. В реальности суммирование производится после группировки.
Разумеется, у нас есть и заранее агрегированные таблицы. Но если в таблице пара десятков id-шников, то хранить агрегированные данные для всех возможных группировок несколько накладно. Также не стоит забывать, что условие на эти поля и на диапазон дат может быть разным.
Разумеется, у нас есть и заранее агрегированные таблицы. Но если в таблице пара десятков id-шников, то хранить агрегированные данные для всех возможных группировок несколько накладно. Также не стоит забывать, что условие на эти поля и на диапазон дат может быть разным.
А можно про «агрегатные таблицы» поподробнее? Что-то гугл про них ничего сказать не смог.
Это не особая технология, а скорее подход к агрегации. Агрегатная таблица — это такая таблица из счётчиков, данные в которой обновляются триггерами после каждого изменения источника. Подходит в тех случаях, когда требуется наличие оперативных агрегатных данных по предварительно заданным алгоритмам и интервалам агрегации. Соответственно, их изменение обычно приводит к полному переучёту, со всеми последствиями.
Надеюсь, я не открою большой секрет, обратив внимание на то, что если выполнять преаггрегацию при изменении/обновлении, надо очень, очень внимательно отнестись к стратегии блокирования, ибо все может прочно встать колом на дедлоках при многопоточной вставке/модификации исходных данных. И даже если удастся грамотно раскидать блокировки, параллельный процесс модификации данных вполне может деградировать в последовательный.
Преаггрегация возможна скорее в частном случае, нежели в общем, и к ней должны быть определенные предпосылки.
Преаггрегация возможна скорее в частном случае, нежели в общем, и к ней должны быть определенные предпосылки.
PG версионник, насколько мне известно и как вставка может надолго кого-то заблокировать — я себе слабо представляю. Обновления — да, тут без разницы архитектура — всегда есть шанс нарваться на блокировку. У себя в проектах я храню агрегатные таблицы для быстрой оценки нужных параметров — там потеря нескольких инкрементов сильно картины не изменит. А вот отчёты уже считаются по-честному, там можно и подождать немного :)
PG версионник — да. Он не блокирует запись чтением и чтение записью… Но запись ведь блокирует запись все равно — так? Или как вы себе это представляете?
как вставка может надолго кого-то заблокировать — я себе слабо представляю
я вам расскажу.
Возьмем гипертрофированный пример — тот самый, который использует автор статьи. Он считает сумму по ВСЕЙ таблице. Т.е. предложенная вами таблица преаггрегации будет содержать только одну строчку. Соответственно все инсерты в целевую таблицу посредством предложенного вами триггера, будут апдейтить одну эту самую строчку, соответственно все эти апдейты аккуратненько выстроятся в очредь по блокировке на записи. Весь параллелизм упорядочится. А если с целевой таблицей работают пользователи и приложение используют длинные транзакции, все прочие вставки в эту таблицу будут ожидать пока МарИванна допьет свой кофей.
как вставка может надолго кого-то заблокировать — я себе слабо представляю
я вам расскажу.
Возьмем гипертрофированный пример — тот самый, который использует автор статьи. Он считает сумму по ВСЕЙ таблице. Т.е. предложенная вами таблица преаггрегации будет содержать только одну строчку. Соответственно все инсерты в целевую таблицу посредством предложенного вами триггера, будут апдейтить одну эту самую строчку, соответственно все эти апдейты аккуратненько выстроятся в очредь по блокировке на записи. Весь параллелизм упорядочится. А если с целевой таблицей работают пользователи и приложение используют длинные транзакции, все прочие вставки в эту таблицу будут ожидать пока МарИванна допьет свой кофей.
Ну в вашем примере — всё понятно, но на практике агрегатные таблицы состоят более, чем из одной строчки. А за длинные пишущие транзакции пальчики надо кусачками… или молоточком…
но на практике агрегатные таблицы состоят более, чем из одной строчки
Пусть и более чем из одной, как бы то ни было, гранулярность блокировки при преаггрегации возрастает и возрастает во столько раз, сколько строк мы схлопываем в одну группу. Это могут быть и порядки и порядки порядков.
А за длинные пишущие транзакции пальчики надо кусачками… или молоточком…
Зря вы столь категоричны. Очень зря )
Пусть и более чем из одной, как бы то ни было, гранулярность блокировки при преаггрегации возрастает и возрастает во столько раз, сколько строк мы схлопываем в одну группу. Это могут быть и порядки и порядки порядков.
А за длинные пишущие транзакции пальчики надо кусачками… или молоточком…
Зря вы столь категоричны. Очень зря )
Эээ? И всё? Как-то резко заметка закончилась.
Темасисекsum'а не раскрыта.
Тема
Надобно на mssql провести подобное тестирование.
А не пробовали написать расширение, со своей реализацией sum, возвращающей bigint (ну и, разумеется, с риском переполнения)? Было бы интересно посмотреть насколько будет быстрее.
В СУБД Caché для ускорения таких запросов предусмотрен специальный тип индекса: BITSLICE
Сделал небольшой тест (для ускорения заполнения таблицы использовал не SQL, а прямой доступ). Время везде приводится в секундах.
Результаты на моей машине (i5-2400, ST3500413AS):
Сделал небольшой тест (для ускорения заполнения таблицы использовал не SQL, а прямой доступ). Время везде приводится в секундах.
Код класса (он же таблица)
Class demo.test Extends (%Persistent, %IndexBuilder)
{
Index idxClicks On clicks [ Type = bitslice ];
Property d As %Date;
Property browserid As %Integer [ Required, SqlFieldName = browser_id ];
Property bannerid As %Integer [ Required, SqlFieldName = banner_id ];
Property views As %Integer;
Property clicks As %Integer;
ClassMethod Fill()
{
set time=$zhorolog
set succ(0)=0
set succ(1)=0
set succ(2)=50
set succ(3)=500
set insucc(0)=0
set insucc(1)=0
set insucc(2)=400
set insucc(3)=400000
do DISABLE^%NOJRN
do ..%KillExtent()
set i=0
for d=$horolog-99:1:$horolog {
for browserid=0:1:99 {
for bannerid=0:1:999 {
set succ=succ($random(4))
set insucc=insucc($random(4))
set i=i+1
set ^demo.testD(i)=$listbuild("",d,browserid,bannerid,succ+insucc,succ)
}
}
}
set ^demo.testD=i
do ENABLE^%NOJRN
write "Время заполнения таблицы = ",$zhorolog-time,!
do ..%ConstructIndicesParallel(,,,1,,0,0)
}
}
{
Index idxClicks On clicks [ Type = bitslice ];
Property d As %Date;
Property browserid As %Integer [ Required, SqlFieldName = browser_id ];
Property bannerid As %Integer [ Required, SqlFieldName = banner_id ];
Property views As %Integer;
Property clicks As %Integer;
ClassMethod Fill()
{
set time=$zhorolog
set succ(0)=0
set succ(1)=0
set succ(2)=50
set succ(3)=500
set insucc(0)=0
set insucc(1)=0
set insucc(2)=400
set insucc(3)=400000
do DISABLE^%NOJRN
do ..%KillExtent()
set i=0
for d=$horolog-99:1:$horolog {
for browserid=0:1:99 {
for bannerid=0:1:999 {
set succ=succ($random(4))
set insucc=insucc($random(4))
set i=i+1
set ^demo.testD(i)=$listbuild("",d,browserid,bannerid,succ+insucc,succ)
}
}
}
set ^demo.testD=i
do ENABLE^%NOJRN
write "Время заполнения таблицы = ",$zhorolog-time,!
do ..%ConstructIndicesParallel(,,,1,,0,0)
}
}
Результаты на моей машине (i5-2400, ST3500413AS):
- Сгенерируем даные:
TEST>do ##class(demo.test).Fill()
Время заполнения таблицы = 5.327899
Время построения индекса, используя 4 процесса = 15.495457
- Выполним запрос:
select sum(clicks) from demo.test -- время = 0.031
А если нужно делать произвольные группировки и фильтры?
А если нужно делать произвольные группировки и фильтры?Нужно смотреть планы запросов и подбирать выгодные подсказки оптимизатору и индексы: отдельно для агрегации, отдельно для фильтрации/группировки.
Для фильтрации и группировки обычно лучше использовать BITMAP-индексы.
Но если запросы очень сложные — сложные агрегации/фильтрации, большие объёмы данных, — стоит использовать технологию DeepSee, позволяющую строить кубы на оперативных данных (на русском про DeepSee можно почитать здесь):
- не нужно делать отдельное хранилище
- помимо SQL можно воспользоваться ещё и MDX
- готовый интерфейс для конечных пользователей
Где-нибудь толково написано про алгоритмическую базу, стоящую за DeepSee? Просто ведь неспроста под аналитику, как правило, отводят отдельную базу: соответствующие индексы плохо переносят обновления.
Самому разбираться в том что реально стоит за маркетинговыми заявлениями и красивыми формочками не хочется: к продуктам InterSystems уже сложилось недоверие.
Самому разбираться в том что реально стоит за маркетинговыми заявлениями и красивыми формочками не хочется: к продуктам InterSystems уже сложилось недоверие.
Где-нибудь толково написано про алгоритмическую базу, стоящую за DeepSee?
- How the DeepSee Query Engine Works
- How DeepSee Builds and Uses Fact Tables
- Keeping the Cubes Current
DeepSee основывается в своей работе на тех же bitmap/bitslice индексах, а если совсем уж глубоко, то на глобалах — так же как и хранимые классы и таблицы. На основе модели куба строится таблица фактов с вполне определённой структурой, данные которой поддерживаются в актуальном состоянии при изменении исходных данных. Доступ к этой таблице фактов можно получить напрямую через глобалы, через SQL или MDX. Или визуально играться в анализаторе.
Просто ведь неспроста под аналитику, как правило, отводят отдельную базу: соответствующие индексы плохо переносят обновления.Нужно уточнить: отводят, как правило, в реляционных СУБД, но Caché — это не реляционная СУБД. В её основе лежит TMDM™ (transactional multidimensional data model), поэтому и реализация bitmap-индексов в ней отличается от реализации в других типах СУБД.
В Caché bitmap-индексы специально заточены под OLTP, а не только под OLAP:
Bitmap indices, typically used in large data warehousing and OLAP systems, offer the ability to perform high-speed searches based on complex combinations of conditions. Such bitmap indices cannot be updated in real-time, however and are typically updated as a batch process. Caché SQL supports bitmap indices that offer high-performance searching power combined with no loss in insert/update performance. This gives transaction processing applications the ability to perform data warehouse-style queries and gives data warehouse applications the ability to perform real-time updates.
источник
Но и здесь ничто не мешает разнести отдельные части данных куба по разным БД/машинам:
Здесь не хватает ещё тестов на то, как индекс повлиял на скорость обновлений в таблице.
Сделал в рамках одной транзакции 100000 обновлений полей views и clicks случайными значениями (аналогично заполнению). Записи для обновления выбирались по случайному ID [1:10 млн.]
Результаты:
1) без индекса, напрямую: ~ 0.6 (сек.)
2) без индекса, через SQL: ~ 6 (сек.)
3) c индексом, через SQL: ~ 12 (сек.)
Результаты:
1) без индекса, напрямую: ~ 0.6 (сек.)
2) без индекса, через SQL: ~ 6 (сек.)
3) c индексом, через SQL: ~ 12 (сек.)
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Оптимизация sum в PostgreSQL