https://pinot.apache.org/ logo
#troubleshooting
Title
# troubleshooting
a

Ali Atıl

04/06/2022, 1:41 PM
Hello everyone, is there anyway to make such a query which includes range filter, order by and high value limit,offset faster? I'd appreciate any help :) 'messageTime' is the dateTimeFieldSpecs column of the table.
Copy code
select fieldA, fieldB, fieldC, messageTime from mytable where messageTime >= 1648813002065 and  messageTime <= 1649245002065 order by messageTime limit 3000000, 1000000 option(timeoutMs=120000)
r

Rong R

04/06/2022, 4:18 PM
one config you can try is adding range index or even configure the sorted index to the messageTime field. but if you already use
messageTime
as your time column then the bottleneck is probably somewhere elase. could you share the query result metadata /stats returned from broker?
a

Ali Atıl

04/07/2022, 6:28 AM
requestId=283,table=mytable,timeMs=79562,docs=7005228/16607826,entries=0/28020912,segments(queried/processed/matched/consuming/unavailable):14/11/9/1/0,consumingFreshnessTimeMs=1649312557983,servers=1/1,groupLimitReached=false,brokerReduceTimeMs=25101,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-0_R=0,54457,128000542,383,1,offlineThreadCpuTimeNs(total/thread/sysActivity/resSer):0/0/0/0,realtimeThreadCpuTimeNs(total/thread/sysActivity/resSer):0/0/0/0,query=select fieldA, fieldB, fieldC, messageTime from mytable where messageTime >= 1648813002065 and  messageTime <= 1649245002065 order by messageTime limit 3000000, 1000000 option(timeoutMs=300000)
i will try range and sorted index 👍
i have set 'messageTime' as sortedColumn and also put rangeIndex on it. Increased cpu resource for pinot-server to 3(i am using kubernetes deployment). This is the broker response now:
requestId=6,table=mytable,timeMs=52146,docs=9676496/20612464,entries=0/38705984,segments(queried/processed/matched/consuming/unavailable):13/8/8/1/0,consumingFreshnessTimeMs=1649320041973,servers=3/3,groupLimitReached=false,brokerReduceTimeMs=25801,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-2_O=2,25,399,0,20;pinot-server-3_O=1,25,398,0,21;pinot-server-0_R=1,26335,128000539,215,20,offlineThreadCpuTimeNs(total/thread/sysActivity/resSer):0/0/0/0,realtimeThreadCpuTimeNs(total/thread/sysActivity/resSer):0/0/0/0,query=select fieldA, fieldB, fieldC, messageTime from mytable where messageTime >= 1648813002065 and  messageTime <= 1649245002065 order by messageTime limit 3000000, 1000000 option(timeoutMs=120000)
i think it got faster. This table is only served by single pinot-server. I don't know what it is but is there anything i could do to reduce that 'brokerReduceTimeMs'? Do you have any other suggestions ? :)
I have realized cpu resource for pinot-broker was too low. (200m which is 1/5 core). After increasing it my query got a lot faster.
requestId=60,table=mytable,timeMs=8879,docs=7032000/13074768,entries=42192/28128000,segments(queried/processed/matched/consuming/unavailable):13/10/10/1/0,consumingFreshnessTimeMs=1649407827428,servers=4/4,groupLimitReached=false,brokerReduceTimeMs=2565,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-3_O=1,3,400,0,1;pinot-server-0_O=1,6,400,0,2;pinot-server-2_O=1,5,400,0,2;pinot-server-0_R=1,6309,128000544,52,3,offlineThreadCpuTimeNs(total/thread/sysActivity/resSer):0/0/0/0,realtimeThreadCpuTimeNs(total/thread/sysActivity/resSer):0/0/0/0,query=select fieldA, fieldB, fieldC, messageTime from mytable where messageTime >= 1649312545000 and  messageTime <= 1649407767000 order by messageTime limit 3000000, 1000000 option(timeoutMs=120000)