This query does not scale beyond 240 qps
{"sql":"s...
# pinot-perf-tuningp
Prakhar Pande
11/07/2022, 8:16 PMThis query does not scale beyond 240 qps
{"sql":"select event_time , sum(count) as event_counts from nrt_app_open where year=year(now()) and month=month(now()) and day>=(day(now())-1) and FromDateTime(event_time, 'yyyy-MM-dd HHmmss') > cast((now() - 86400000) as long) group by 1 limit 1000000000","trace":false,"queryOptions":""}
Any ideas on how I can optimise this. Cpu and memory of pinot-servers are also under utilized.
Thanks in advance.
Prakhar Pande
11/08/2022, 1:55 PMwas able to scale it up to 420 qps by using TIMESTAMP indexing, I am using 10 servers but each of them are using less than 15 percent cpu and 20 percent memory of the given resources. How can I scale further, basically we want to test how much further we can go from here? similarly broker and controller are also under-utilised.
m
Mayank
11/08/2022, 2:39 PM
Can you check IO
p
Prakhar Pande
11/08/2022, 3:24 PMCould you elaborate ?
m
Mayank
11/08/2022, 4:32 PM
If cpu is under utilized, and you are not able to scale beyond, then other bottlenecks are disk reads, network IO, memory read/writes. The next thing I’d check is what’s the disk IO happening, and if it is a lot, then moving to faster/local disk, playing with jvm args (heap, gc) might help
Mayank
11/08/2022, 4:33 PM
You can also check how much time is being spent in
FromDateTime(event_time, 'yyyy-MM-dd HH:mm:ss') > cast((now() - 86400000) as long)p
Prakhar Pande
11/09/2022, 10:41 AMThe query itself is taking only around 7-8 ms in the pinot-ui and it is now changed to
select event_time_epoch , sum(count) as event_counts from nrt_app_open_1 where event_time_epoch > cast((now() - 86400000) as long) group by 1 limit 1000000000Prakhar Pande
11/09/2022, 12:46 PMMeanwhile also, I have observed that when we fire load on pinot, the ui becomes very slow at times.