When some kind of resource has great quantity, the sql will be rather slow

[panpan0000]

What happened?

the k8s api-server event amount is very large(> 40000). it cause the table content is big.

then the normal sql which required owner refernece is very slow.

What did you expect to happen?

some better architecture to refine this.

How can we reproduce it (as minimally and precisely as possible)?

in kpanda (DCE5.0 code), clusterpedia managers openshift (with large events), then the sql to find pods list which belongs to a deployment, will be very slow.

Anything else we need to know?

DCE5 issue tracker( private network): https://gitlab.daocloud.cn/ndx/engineering/kpanda/-/issues/1880

Clusterpedia Version

latest

Host Kubernetes version

1.24

[clusterpedia-bot]

Hi @panpan0000,
Thanks for opening an issue!
We will look into it as soon as possible.

Details

Instructions for interacting with me using comments are available here.
If you have questions or suggestions related to my behavior, please file an issue against the gh-ci-bot repository.

[panpan0000]

per lizhongjun , previous ,in large event database ,
the sql select which parses json structure is slow.
but below sql without unstruct data query is also slow :

SELECT `object` FROM `resources` WHERE `group` = '' AND `resource` = 'pods' AND `version` = 'v1' AND cluster = 'on-premise' AND namespace = 'default' AND owner_uid IN (SELECT `uid` FROM `resources` WHERE `cluster` = 'on-premise' AND owner_uid IN (SELECT `uid` FROM `resources` WHERE `cluster` = 'on-premise' AND namespace IN ('default','') AND name = 'nginx-002')) ORDER BY created_at DESC LIMIT 10