in the logs i’m observing

2021-11-09 12:53:00	
Slow query: request handler processing time: 441, send response latency: 1, total time to handle request: 442
2021-11-09 12:53:00	
Processed requestId=1975257,table=etsyads_metrics_REALTIME,segments(queried/processed/matched/consuming)=46/46/46/1,schedulerWaitMs=0,reqDeserMs=0,totalExecMs=441,resSerMs=0,totalTimeMs=441,minConsumingFreshnessMs=1636480380211,broker=Broker_pinot-broker-1.pinot-broker-headless.pinot.svc.cluster.local_8099,numDocsScanned=20584,scanInFilter=0,scanPostFilter=123504,sched=fcfs,threadCpuTimeNs=0

i was able to then find the request id in the broker and got some more info:

requestId=1976569,table=ads_metrics_REALTIME,timeMs=234,docs=17731/290711208,entries=0/106386,segments(queried/processed/matched/consuming/unavailable):46/46/46/1/0,consumingFreshnessTimeMs=1636480906334,servers=1/1,groupLimitReached=false,brokerReduceTimeMs=0,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-1_R=0,233,7479,0,-1,offlineThreadCpuTimeNs=0,realtimeThreadCpuTimeNs=0,query=SELECT product_id, SUM(click_count), SUM(impression_count), SUM(cost), SUM(order_count), SUM(revenue) FROM ads_metrics WHERE user_id = 13133627 AND serve_time BETWEEN 1633924800 AND 1636520399 GROUP BY product_id LIMIT 6000

is there any way i could tell from these logs why this is being slow (?) only thing I can see is the

scanPostFilter=123504

which may happen because of the group by i believe we currently do not have any indexes into that product_id column, would adding one speed up things in any way?

could you get a profile from the server process while querying it? e.g.

jcmd <pid> JFR.start duration=60s filename=server.jfr

then copy the jfr file off the box? or if you have async-profiler installed already that would be better

can i profile with jvisualvm?

preferably not, it's super high overhead

i do have jcmd i think

jps should identify the server process

oh yea i think we have somethin

you can load it in JMC

image.png

yes it gives rule based advice

🙏 thank you so much will send shortly