Hello everyone is there anyway to make such a query which in Apache Pinot #troubleshooting

Hello everyone, is there anyway to make such a que...

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)

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?

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)

Ali Atıl

04/07/2022, 6:29 AM

i will try range and sorted index 👍

Ali Atıl

04/07/2022, 8:31 AM

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)

Ali Atıl

04/07/2022, 8:52 AM

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 ? :)

Ali Atıl

04/08/2022, 8:53 AM

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)

Open in Slack

Previous Next