Повторное открытие дашборда заново считывает значения размерностей из БД, можно ли избежать

dpanov · 01.Март.2021 13:37:37

При каждом открытии аналитического отчета он открывается 13 минут (открывал 3 раза подряд)

При этом в БД 13 минут выполняется запрос получения значений верхнего уровня размерности.

select "query1503240873"."osmId" as "c0", "query1503240873"."osmName" as "c1", "query1503240873"."osmName" as "c2" from 
(select sub_ind."Id" as "Id",sub_ind."Id" as id_key,  * from sub_ind) as "query-132957879",
"fer_indicator_view" as "fer_indicator_view_1", 
(select per."Id" as id_key, * from dimPeriodDay per) as "query-692886388",
(select * from fer_pmsp) as "query1032016490",
(select sdim."Id" as id_key , sdim."Id"::text as id_key_text, * from fer_person sdim) as "query1503240873"
 where "fer_indicator_view_1"."osmId" = "query-132957879"."id_key" 
   and "query-132957879"."id_sub" in (144764, 147166, 140337, 112819, 83184, 81997, 72197, 77665, 115106, 72181, 2099218, 102269, 337422, 1574364, 147167, 145730, 85617, 145454, 109879, 103906, 81995, 151233, 109878, 144763, 115100, 85963, 108082, 190090, 140290, 72223, 176095, 72169, 151228, 51490, 2099216, 274048, 72195, 89331, 140294, 140292, 77669, 72224, 0, 72182, 115135, 151225, 155262, 253256, 145194, 77677, 145729, 109876, 253252, 108083, 393980, 115136, 72639, 115114, 72196, 151234, 110032, 79374, 145195, 190911, 85606, 71950, 72194, 72193, 394235, 79379, 81996, 108081, 72180, 2095259, 140295, 81993, 140291, 115134, 72192, 151223, 140296, 77687, 109877, 80513, 151231, 191706, 81994)
   and "fer_indicator_view_1"."onDate" = "query-692886388"."id_key"
   and "query-692886388"."period" in ('20210201', '20210202', '20210203', '20210204', '20210205', '20210206', '20210207', '20210208', '20210209', '20210210', '20210211', '20210212', '20210213', '20210214', '20210215', '20210216', '20210217', '20210218', '20210219', '20210220', '20210221', '20210222', '20210223', '20210224', '20210225', '20210226', '20210227', '20210228')
    and "fer_indicator_view_1"."isPMSP" = "query1032016490"."is_pmsp"
	and "query1032016490"."is_pmsp" = 1.0
	and "fer_indicator_view_1"."specDimId_text" = "query1503240873"."id_key_text"
group by "query1503240873"."osmId", "query1503240873"."osmName"
order by "query1503240873"."osmName" ASC NULLS LAST

Размерность содержит 2 млн врачей, верхний уровень - 85 субъектов РФ.

ВОПРОС 1. Можно ли сделать так, чтобы использовался кэш OLAP?
ВОПРОС 2. Можно ли повлиять на построение запроса. чтобы хотя бы использовал индекс по дате? (строчки 19-20 в плане)

r.khaliullin · 01.Март.2021 13:49:42

Такое поведение может говорить о том, что в Mondrian отключено кэширование
Приложите, пожалуйста, файл mondrian.properties из OLAP-сервера, его можно найти по пути:
/opt/tomcat/webapps/mondrian/WEB-INF/classes/mondrian.properties

kalyanov · 01.Март.2021 14:16:08

Скорее всего, он и используется.
В ЕГИСЗ, кажется, используется внутренняя кластеризация мондриана, когда на одном сервере находится несколько независимых кэшей (это необходимо для способности OLAP-сервера выдерживать нагрузку от пользователей). Соответственно, запросы могут приходить на них, и каждый из них будет запрашивать данные из БД. Можно выполнить OLAP-запрос несколько раз (кажется, в ЕГИСЗ это нужно сделать как минимум 6 раз; точнее могут подсказать администраторы или разработчики, кто проводил эту настройку - либо в файле mondrian.properties посмотреть на значение multiserver.count), чтобы прогреть все кэши.

kalyanov · 01.Март.2021 14:18:23

(Просьба текст вставлять как текст, а не как картинку)

kalyanov · 01.Март.2021 14:39:43

Сложно сказать, не видя план запроса (неискаженный: на этом скриншоте нет лидирующих пробелов в строках, которые является суперважными для понимания структуры плана; из консоли разработчика в браузере во вкладке Network можно достать json с необрезанными пробелами). Я бы для начала порекомендовал выполнить этот запрос с explain analyze verbose: 1) посмотреть, на что тратится время; 2) сравнить ожидаемые селективности с фактическими.
Но есть несколько подозрительных мест:

"Id"::text as id_key_text в тексте подзапроса
все ли фактические внешние ключ объявлены в базе как таковые?
давно ли производился vacuum в таблицах?
судя по оценке селективности, фильтрация fer_indicator_view по набору дат должна вернуть 40М строк (что, видимо, является существенной долей какой-то из таблиц) - такое количество строк дешевле прочесть один раз и хэшировать, чем собирать по индексу по дате.
текст fer_indicator_view наверняка скрывает за собой что-нибудь

dpanov · 01.Март.2021 14:58:11

{“success”:true,“Data”:{“1”:[{“QUERY PLAN”:“Group (cost=3024842.53…3026942.12 rows=7396 width=80)”},{“QUERY PLAN”:" Group Key: sdim.“osmName”, sdim.“osmId”"},{“QUERY PLAN”:" -> Gather Merge (cost=3024842.53…3026868.16 rows=14792 width=44)"},{“QUERY PLAN”:" Workers Planned: 2"},{“QUERY PLAN”:" -> Group (cost=3023842.50…3024160.77 rows=7396 width=44)"},{“QUERY PLAN”:" Group Key: sdim.“osmName”, sdim.“osmId”"},{“QUERY PLAN”:" -> Sort (cost=3023842.50…3023948.59 rows=42436 width=44)"},{“QUERY PLAN”:" Sort Key: sdim.“osmName”, sdim.“osmId”"},{“QUERY PLAN”:" -> Parallel Hash Join (cost=2487130.82…3020580.66 rows=42436 width=44)"},{“QUERY PLAN”:" Hash Cond: ((sdim.“Id”)::text = fer_indicator_view_1.“specDimId_text”)"},{“QUERY PLAN”:" -> Parallel Seq Scan on fer_person sdim (cost=0.00…527090.32 rows=989232 width=52)"},{“QUERY PLAN”:" -> Parallel Hash (cost=2487052.97…2487052.97 rows=6228 width=11)"},{“QUERY PLAN”:" -> Hash Join (cost=621.25…2487052.97 rows=6228 width=11)"},{“QUERY PLAN”:" Hash Cond: (fer_indicator_view_1.“osmId” = sub_ind.“Id”)"},{“QUERY PLAN”:" -> Hash Join (cost=603.83…2487018.41 rows=6228 width=19)"},{“QUERY PLAN”:" Hash Cond: (fer_indicator_view_1.“isPMSP” = fer_pmsp.is_pmsp)"},{“QUERY PLAN”:" -> Hash Join (cost=565.97…2486399.24 rows=138407 width=23)"},{“QUERY PLAN”:" Hash Cond: (fer_indicator_view_1.“onDate” = per.“Id”)"},{“QUERY PLAN”:" -> Parallel Seq Scan on fer_indicator_view fer_indicator_view_1 (cost=0.00…2381505.94 rows=39722794 width=31)"},{“QUERY PLAN”:" -> Hash (cost=565.62…565.62 rows=28 width=8)"},{“QUERY PLAN”:" -> Seq Scan on dimperiodday per (cost=0.00…565.62 rows=28 width=8)"},{“QUERY PLAN”:" Filter: (period = ANY (’{20210201,20210202,20210203,20210204,20210205,20210206,20210207,20210208,20210209,20210210,20210211,20210212,20210213,20210214,20210215,20210216,20210217,20210218,20210219,20210220,20210221,20210222,20210223,20210224,20210225,20210226,20210227,20210228}’::text[]))"},{“QUERY PLAN”:" -> Hash (cost=37.75…37.75 rows=9 width=8)"},{“QUERY PLAN”:" -> Seq Scan on fer_pmsp (cost=0.00…37.75 rows=9 width=8)"},{“QUERY PLAN”:" Filter: ((is_pmsp)::numeric = 1.0)"}]},“Meta”:[{“Path”:“QUERY PLAN”,“Options”:{},“Code”:“QUERY PLAN”,“Caption”:“QUERY PLAN”,“FieldType”:0,“Type”:0,“AvailableValues”:[],“DefaultValue”:null}]}

dpanov · 01.Март.2021 14:58:31

"Id"::text as id_key_text в тексте подзапроса -
это вынужденная мера была, но в данном случае это наверное неважно, так как вернётся больше четверти записей fer_person
все ли фактические внешние ключ объявлены в базе как таковые?
По размерности врачей fer_person нет внешнего ключа. По дате - есть.
давно ли производился vacuum в таблицах?
давно((
судя по оценке селективности, фильтрация fer_indicator_view по набору дат должна вернуть 40М строк (что, видимо, является существенной долей какой-то из таблиц) - такое количество строк дешевле прочесть один раз и хэшировать, чем собирать по индексу по дате.

за месяц (в запросе набор 28 дат февраля) должна вернуть 4М. Всего в таблице сейчас около 35 месяцев, то есть селективность грубо 3%

текст fer_indicator_view наверняка скрывает за собой что-нибудь
это просто mat.view

kalyanov · 01.Март.2021 15:01:07

Не совсем - кавычки заменились: нужно использовать вставку блока неотформатированного текста (как на скриншоте )

dpanov · 02.Март.2021 16:20:32

Вопрос 1 с кэшем остаётся.
Вопрос 2 редуцировал до запроса ниже, будем разбираться с дба:
explain
select dimPeriodDay.period
from dimPeriodDay, fer_indicator_view

where
fer_indicator_view.“onDate” = dimPeriodDay.“Id”
and dimPeriodDay.“period” in (‘20210201’, ‘20210202’, ‘20210203’, ‘20210204’, ‘20210205’, ‘20210206’, ‘20210207’, ‘20210208’, ‘20210209’, ‘20210210’, ‘20210211’, ‘20210212’, ‘20210213’, ‘20210214’, ‘20210215’, ‘20210216’, ‘20210217’, ‘20210218’, ‘20210219’, ‘20210220’, ‘20210221’, ‘20210222’, ‘20210223’, ‘20210224’, ‘20210225’, ‘20210226’, ‘20210227’, ‘20210228’)

group by 1

kalyanov · 05.Март.2021 09:56:44

Есть ли возможность сделать экспорт этого отчета со всеми зависимостями (БO, кубы, сам отчет и т.п.)?

dpanov · 05.Март.2021 10:33:05

отправил в личку

kildushova · 05.Март.2021 10:40:28

По вопросу использования кэша:
В общем случае кэш должен использоваться.
Для диагностики проблемы необходимо:

Содержимое mondrian.properties
Схема многомерной модели. Получить ее можно:
2.1. Из БД - таблица runtime.mondrian_schema
или
2.2. Аутентифицироваться в альфе под пользователем admin, открыть урл:
http://host:port/appName/dwh/Schema?dataSource=кодИсточникаДанных
Mdx запрос, который при повторном выполнении предположительно не использует кэш иерархий

Готова подключиться к анализу.

kildushova · 10.Март.2021 15:08:41

ВОПРОС 1. Итак, по результатам анализа по имеющимся (не полным) вводным.

Кэш элементов иерархии действительно не используется.
Приведенный выше sql-запрос не просто “запрос получения значений верхнего уровня размерности”, как было сказано выше.
Этот запрос обеспечивает получение элементов иерархии:
2.1. только связанных с записями таблицы фактов,
2.2. с учетом фильтров по другим размерностям, определенных на оси фильтров mdx запроса.
В настройках olap сервера - в файле mondrian.properties - включено “нативное” выполнение NonEmpty:
#If enabled some NON EMPTY set operations like member.children, level.members and member descendants will be computed in SQL.
mondrian.native.nonempty.enable=true
mdx запрос, который выполняется без применения кэша элементов иерархий, имеет вид:

MDX запрос

WITH
MEMBER [Measures].[Всего (юридических лиц)] AS IIF(IsEmpty([Measures].[moIdNot]), null, [Measures].[moIdNot])

MEMBER [Measures].[В которые осуществлена запись через ЕПГУ] AS IIF(IsEmpty([Measures].[moId]), null, [Measures].[moId])
MEMBER [Measures].[Процент] AS IIF(
IsEmpty([Measures].[В которые осуществлена запись через ЕПГУ])
AND IsEmpty([Measures].[Всего (юридических лиц)])

,null
,IIF( [Measures].[В которые осуществлена запись через ЕПГУ] > 0
,[Measures].[В которые осуществлена запись через ЕПГУ]
,0
)
/
IIF( [Measures].[Всего (юридических лиц)]>0
,[Measures].[Всего (юридических лиц)]
,1)
), FORMAT_STRING="#0%"
SET [default_fer_mo_indicator_dim_mat_fer_mo_indicator_dim] AS {[fer_mo_indicator_dim_mat].[fer_mo_indicator_dim].DefaultMember}
SET [default_dimPeriodDay_mat_period_day_hier] AS FILTER([dimPeriodDay_mat].[period_day_hier].Levels(1).Members, IIF([dimPeriodDay_mat].[period_day_hier].CurrentMember.properties(“member_type”) = 2, 1 < 0, ((DateValue([dimPeriodDay_mat].[period_day_hier].CurrentMember.properties(“datefrom”)) IS NULL OR DateValue([dimPeriodDay_mat].[period_day_hier].CurrentMember.properties(“datefrom”)) > null) AND (DateValue([dimPeriodDay_mat].[period_day_hier].CurrentMember.properties(“datefrom”)) IS NULL OR DateValue([dimPeriodDay_mat].[period_day_hier].CurrentMember.properties(“datefrom”)) < null))))
SET [default_fer_pmsp_mat_is_pmsp] AS {[fer_pmsp_mat].[is_pmsp].[1]}
SET [default_fer_sub_ind_region_mat_fer_sub_ind_region] AS FILTER([fer_sub_ind_region_mat].[fer_sub_ind_region].children, IIF([fer_sub_ind_region_mat].[fer_sub_ind_region].CurrentMember.properties(“member_type”) = 2, 1 < 0, ([fer_sub_ind_region_mat].[fer_sub_ind_region].CurrentMember.properties(“fnsiRegionName”) = “” OR ([fer_sub_ind_region_mat].[fer_sub_ind_region].Parent.Properties(“MEMBER_CAPTION”) = “Российская Федерация” AND InStr([fer_sub_ind_region_mat].[fer_sub_ind_region].CurrentMember.properties(“fnsiRegionName”), “”) > 0))))

SELECT
{
{[Measures].[Всего (юридических лиц)],[Measures].[В которые осуществлена запись через ЕПГУ],[Measures].[Процент]}
} DIMENSION PROPERTIES [CHILDREN_CARDINALITY],[MEMBER_TYPE] ON COLUMNS,
NON EMPTY {
HIERARCHIZE(union([default_fer_mo_indicator_dim_mat_fer_mo_indicator_dim],
{{[fer_mo_indicator_dim_mat].[fer_mo_indicator_dim].children}}))
} DIMENSION PROPERTIES [CHILDREN_CARDINALITY],[MEMBER_TYPE] ON ROWS

from [fer_medical_org]
WHERE FILTER([default_dimPeriodDay_mat_period_day_hier], COUNT(INTERSECT([default_dimPeriodDay_mat_period_day_hier], DESCENDANTS([dimPeriodDay_mat].[period_day_hier].CurrentMember))) = 1)
*[default_fer_pmsp_mat_is_pmsp]*FILTER([default_fer_sub_ind_region_mat_fer_sub_ind_region], COUNT(INTERSECT([default_fer_sub_ind_region_mat_fer_sub_ind_region], DESCENDANTS([fer_sub_ind_region_mat].[fer_sub_ind_region].CurrentMember))) = 1)
CELL PROPERTIES VALUE,FORMATTED_VALUE,FORMAT_STRING

Важно, что применяется режим NON EMPTY на оси и выражение, содержащее .children.

Таким образом, совокупность факторов п.3 и п.4. обеспечивает “нативное” выполнение .children - п.2, что исключает применение кэша - п.1.

Вернемся к ВОПРОСу 1. Можно ли сделать так, чтобы использовался кэш OLAP?
ОТВЕТ: Можно. Варианты:

ВАРИАНТ 1.

В mondrian.properties выключить “нативное” выполнение NonEmpty:
mondrian.native.nonempty.enable=false

Важно: механизм “нативного” выполнения NonEmpty является инструментом оптимизации расхода ресурсов (время, память) OLAP сервером при применении режима NON EMPTY на оси.
Суть оптимизации заключается в том, чтобы минимизировать количество кортежей на оси строк/столбцов (за счет получения только связанных с фактами с учетом фильтрации), по которым будут запрашиваться и кэшироваться агрегаты.

Риски: изменение этого свойства повлияет на выполнение всех mdx запросов - в некоторых случаях это может привести к увеличению времени выполнения запроса.

ВАРИАНТ 2.

Конкретно для исследуемого “проблемного” mdx запроса применить вместо режима NON EMPTY на оси функцию NonEmpty, определив формулу в настройках иерархии на оси (готова с этим помочь при необходимости).

Применение этого варианта:

исключит “нативное” выполнение выражения .children и обеспечит применение кэша элементов соответствующей иерархии при выполнении заданного mdx запроса,
позволит оценить разницу во времени выполнения для режима NON EMPTY и функции NonEmpty в конкретном случае,
является более безопасным, т.к. не влияет на выполнение остальных mdx запросов.

a.volodina · 12.Март.2021 12:41:55

Выбрали вариант 2.
Добавили формулу в иерархию на оси строк
(NonEmpty(Drilldownlevel([fer_mo_indicator_dim_mat].[fer_mo_indicator_dim].Levels(0).Members),
{[Measures].[В которые осуществлена запись через ЕПГУ], [Measures].[Всего (юридических лиц)],[Measures].[Процент]})).

Теперь при повторном запросе кэширование применяется, повторное получение результата - несколько секунд.

a.volodina · 19.Март.2021 07:28:11

По вопросу 2 были получены и выполнены следующие рекомендации:

“Стоимость использования индекса зависит в том числе и от correlation индекса в pg_stats. Это значение было увеличено упорядочиванием физического расположения строк в соответствии с индексом по колонке onDate с помощью выполнения Cluster и повторным выполнением Analyze по таблице. Значение correlation было увеличено с ~0,14 до 1 (максимального), что позволило уменьшить стоимость использования индекса. По мере добавления строк в таблицу, значение correlation может уменьшаться, поэтому необходимо наблюдать за его изменением и периодически выполнять Cluster по индексу.”

После выполнения рекомендаций индекс стал использоваться.