Hello. We are using shared Data mode in Starrocks. It was working well about 10 days ago but suddenly we are seeing high CPU consumption for basic join queries. We tried changing s3 services Incase that could have caused but issue still persist. There has been no change in the cluster side as well as query side. We were using dbt-starrocks and dbt transformation is taking ages to complete. The same pipeline that would have completed under 2 minutes is now taking 15-20 minutes. Please let me know how do I deal with this.

set enable profile and get a profile for the query, need the profile to analysis what's going on.

explain doesn't help. profile will have detailed costs of the execution.

1. looks like it creates a full scan of the table

transaction_info

, which has 144608 tablets, all of these tablets are touched during the query.

- TabletCount: 144.608K (144608)
     - __MAX_OF_TabletCount: 29.240K (29240)
     - __MIN_OF_TabletCount: 28.754K (28754)

2. disk cache is not taking effect.

- IOCountLocalDisk: 0
       - IOCountRemote: 424
         - __MAX_OF_IOCountRemote: 53
         - __MIN_OF_IOCountRemote: 0
       - IOCountRequest: 1.396K (1396)
         - __MAX_OF_IOCountRequest: 176
         - __MIN_OF_IOCountRequest: 0
       - IOCountTotal: 424
         - __MAX_OF_IOCountTotal: 53
         - __MIN_OF_IOCountTotal: 0
       - IOTimeLocalDisk: 0ns

3. compaction may not be complete, this gives each tablet about 7-8 rowsets.

- RowsetsReadCount: 1.135M (1134919)
         - __MAX_OF_RowsetsReadCount: 1.728K (1728)
         - __MIN_OF_RowsetsReadCount: 1.168K (1168)
       - SegmentsReadCount: 1.037M (1036754)
         - __MAX_OF_SegmentsReadCount: 1.590K (1590)
         - __MIN_OF_SegmentsReadCount: 1.060K (1060)

The first one shall be the most significant one. need to consider how to rewrite the sql in an efficient way.

hmm, what's your recommendation?

https://ai-agent.starrocks.com/ try this one, may provide you some suggestion. Also https://docs.starrocks.io/docs/category/best-practices/ , knowing how to tune the table/partition to best fit for the query pattern and ingestion.