Indexes are missing

[Айсина Роза Мунеровна <ro...@sbermarket.ru>]

Hola!

We observe very strange baviour in our Ignite cluster.
Indexes in the tables are deleted.

First we load data to cluster: create tables via SQL DDLs and then load data through Key-Value API.
It happens every day through automatic DAG.

Then after some time we see that all queries took too long to finish (with corresponding warnings in logs) -
EXPLAIN shows that full table scan is performed and INDEXES system view shows that there are no indexes at all.

Example of CREATE DDL:

CREATE TABLE IF NOT EXISTS PUBLIC.ProductFeatures
(
    product_id INT PRIMARY KEY,
    total_cnt_orders_with_sku INT
)
WITH "CACHE_NAME=PUBLIC_ProductFeatures, KEY_TYPE=io.sbmt.ProductFeaturesKey, VALUE_TYPE=io.sbmt.ProductFeaturesValue, AFFINITY_KEY=product_id, TEMPLATE=PARTITIONED, BACKUPS=1

See how INDEXES table looks like:

[unhealthy_cluster.png]

And in another cluster (where data is loaded once):

[healthy_cluster.png]

You can see that certain columns are included in primary key index. Also affinity keys are missing too.

In logs I couldn’t find any ERRORs or WARNINGs that can be related.
I also see that indexes are created for all 13 tables:

[2023-01-19T11:14:14,855][INFO ][schema-op-worker-607de2f5-93ae-4214-b8c7-ffdc4ad152b6-#904][GridQueryProcessor] Started indexes rebuilding for cache [name=PUBLIC_UserBrandMasterCategoryFeatures, grpName=null]

[2023-01-19T11:14:14,855][INFO ][schema-op-worker-607de2f5-93ae-4214-b8c7-ffdc4ad152b6-#904][GridQueryProcessor] Finished indexes rebuilding for cache [name=PUBLIC_UserBrandMasterCategoryFeatures, grpName=null]

Example of EXPLAIN query:

EXPLAIN
SELECT
*
FROM
UserBrandFeatures
WHERE
UserBrandFeatures.user_id = 1313547
AND UserBrandFeatures.brand_id = 8871
;

Result is full table scan:

SELECT
    __Z0.USER_ID AS __C0_0,
    __Z0.BRAND_ID AS __C0_1,
   ...
FROM PUBLIC.USERBRANDFEATURES __Z0
    /* PUBLIC.USERBRANDFEATURES.__SCAN_ */
WHERE (__Z0.USER_ID = 1313547)
    AND (__Z0.BRAND_ID = 8871)


The only strange thing that happens in logs is this message:
[2023-01-22T03:32:27,672][WARN ][push-metrics-exporter-#71][PoolProcessor] >>> Possible starvation in striped pool.


We have monitoring (Grafana dashboards) but I don’t know what metrics to analyse - everything seems fine.
Please help to investigate this problem, maybe some hint for next research. What can possibly cause such strange behaviour?

Thanks in advance!

--

Роза Айсина

Старший разработчик ПО

СберМаркет | Доставка из любимых магазинов



Email: roza.aysina@sbermarket.ru<ma...@sbermarket.ru>

Mob:

Web: sbermarket.ru<https://sbermarket.ru/>

App: iOS<https://apps.apple.com/ru/app/%D1%81%D0%B1%D0%B5%D1%80%D0%BC%D0%B0%D1%80%D0%BA%D0%B5%D1%82-%D0%B4%D0%BE%D1%81%D1%82%D0%B0%D0%B2%D0%BA%D0%B0-%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%82%D0%BE%D0%B2/id1166642457> и Android<https://play.google.com/store/apps/details?id=ru.instamart&hl=en&gl=ru>



УВЕДОМЛЕНИЕ О КОНФИДЕНЦИАЛЬНОСТИ: это электронное сообщение и любые документы, приложенные к нему, содержат конфиденциальную информацию. Настоящим уведомляем Вас о том, что, если это сообщение не предназначено Вам, использование, копирование, распространение информации, содержащейся в настоящем сообщении, а также осуществление любых действий на основе этой информации, строго запрещено. Если Вы получили это сообщение по ошибке, пожалуйста, сообщите об этом отправителю по электронной почте и удалите это сообщение.
CONFIDENTIALITY NOTICE: This email and any files attached to it are confidential. If you are not the intended recipient you are notified that using, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. If you have received this email in error please notify the sender and delete this email.