pinot seems to be truncating the number of groups returned b Apache Pinot #troubleshooting

pinot seems to be truncating the number of groups ...

xtrntr

01/26/2022, 6:22 AM

pinot seems to be truncating the number of groups returned by my group by query, but im not seeing

groupLimitReached=true

in my queries. and this is also with the option,

pinot.server.query.executor.num.groups.limit=N

where N is 10M and the number of groups i’m expecting to see is between 5-8M but it’s around 1M instead

xtrntr

01/26/2022, 8:51 AM

one way im trying to work around this is that i know that N ranges from 1 to 8M

xtrntr

01/26/2022, 8:52 AM

so i can split the groupby into multiple queries e.g.

Copy code

SELECT * WHERE COLUMN_N BETWEEN 1 AND 1,000,000 GROUP BY COLUMN_N

SELECT * WHERE COLUMN_N BETWEEN 1,000,001 AND 2,000,000 GROUP BY COLUMN_N

...

SELECT * WHERE COLUMN_N BETWEEN 8,000,001 AND 9,000,000 GROUP BY COLUMN_N

xtrntr

01/26/2022, 8:53 AM

but i still get truncated groups. i see this even if the

BETWEEN

clause is limited to 200k values of

COLUMN_N

Mayank

01/26/2022, 3:40 PM

@Jackie ^^

Jackie

01/26/2022, 4:23 PM

Can you please check the server log and see what's the actual query it got? Broker might rewrite the query limit if configured

xtrntr

01/26/2022, 5:50 PM

server logs look something like this:

Copy code

Processed requestId=144,table=eventsv1202109_OFFLINE,segments(queried/processed/matched/consuming)=18/3/3/-1,schedulerWaitMs=0,reqDeserMs=0,totalExecMs=834,resSerMs=2,totalTimeMs=836,minConsumingFreshnessMs=-1,broker=Broker_pinot-broker-0.pinot-broker-headless.pinot.svc.cluster.local_8099,numDocsScanned=71985558,scanInFilter=0,scanPostFilter=71985558,sched=fcfs,threadCpuTimeNs=0

how do i see the actual query it got?

xtrntr

01/26/2022, 8:49 PM

Can let me know what debug logs you require

xtrntr

01/27/2022, 1:45 AM

@Jackie in case you missed this

Jackie

01/27/2022, 6:21 AM

Seems the queries are not logged on the server

xtrntr

01/27/2022, 6:22 AM

is there an option to enable that?

Jackie

01/27/2022, 6:22 AM

How did you apply the

num.groups.limit

option?

Jackie

01/27/2022, 6:23 AM

groupLimitReached

is not triggered, it should return all the groups

Jackie

01/27/2022, 6:23 AM

Can you please share the query and the response stats?

Jackie

01/27/2022, 6:26 AM

There are 2 broker configs which can enable the query limit rewrite:

pinot.broker.enable.query.limit.override

and

pinot.broker.query.response.limit

, you might also want to check if they are set in your environment

xtrntr

01/27/2022, 7:57 AM

How did you apply the
num.groups.limit
option?

in the server config

xtrntr

01/27/2022, 7:59 AM

There are 2 broker configs which can enable the query limit rewrite:
pinot.broker.enable.query.limit.override
and
pinot.broker.query.response.limit
, you might also want to check if they are set in your environment

these are not set in my environment

xtrntr

01/27/2022, 4:32 PM

@Jackie

Copy code

# query
SELECT userid_int, count(*) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000 GROUP BY userid_int HAVING COUNT(userid_int) >= 1 LIMIT 1000000000

xtrntr

01/27/2022, 4:33 PM

you should see 1,000,000 groups, but instead i get 883,574 groups

xtrntr

01/27/2022, 4:34 PM

Copy code

requestId=145,table=eventsv1202109_OFFLINE,timeMs=2947,docs=394858352/3192209696,entries=0/394858352,segments(queried/processed/matched/consuming/unavailable):70/14/14/0/0,consumingFreshnessTimeMs=0,servers=4/4,groupLimitReached=false,brokerReduceTimeMs=1826,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-2_O=0,822,2488975,1,-1;pinot-server-0_O=0,1119,10222748,3,1;pinot-server-1_O=0,866,2492275,0,-1;pinot-server-3_O=0,842,2602293,1,1,offlineThreadCpuTimeNs=0,realtimeThreadCpuTimeNs=0,query=SELECT userid_int, count(*) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000 GROUP BY userid_int HAVING COUNT(userid_int) >= 1 LIMIT 1000000000

xtrntr

01/27/2022, 4:39 PM

if i change the BETWEEN from

7000001 AND 8000000

7000001 AND 7200000

, i get 193,760 values

xtrntr

01/27/2022, 4:40 PM

if i split

7000001 AND 7200000

into 2 queries,

7000001 AND 7100000

and

7100001 AND 7200000

, i get 200k values

xtrntr

01/27/2022, 4:40 PM

pinot.broker.enable.query.limit.override

and

pinot.broker.query.response.limit

so there is some truncating here, and i’ve already checked that these 2 configs are not set

Jackie

01/27/2022, 5:42 PM

Interesting.. Can you please try removing the

HAVING

clause and see if the results are still less than expected?

xtrntr

01/27/2022, 6:35 PM

https://apache-pinot.slack.com/archives/C011C9JHN7R/p1643301183220500?thread_ts=1643178123.179100&cid=C011C9JHN7R without the HAVING clause, it’s also 883,574 groups

xtrntr

01/27/2022, 6:35 PM

for this same query

Jackie

01/27/2022, 6:40 PM

Can you try

SELECT DISTINCTCOUNT(userid_int) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000

? Also, since there is no entry scan in filter, I assume you have range index on

userid_int

. I don't think the problem is that groups being truncated, but probably caused by range index

xtrntr

01/27/2022, 7:38 PM

Copy code

distinctcount(userid_int)
1000000

xtrntr

01/27/2022, 7:38 PM

i don’t have range index on userid_int

xtrntr

01/27/2022, 7:39 PM

Copy code

"tableIndexConfig": {
      "invertedIndexColumns": [
        "userid_int",
        "time_rounded",
        "cell_id"
      ],

xtrntr

01/27/2022, 7:48 PM

just these indexes

Jackie

01/27/2022, 8:01 PM

I see. Since there is no scan in the filter processing, I suspect the issue is within the. segment pruner. Which version of pinot are you running?

Jackie

01/27/2022, 8:04 PM

Actually I took it back.. That cannot explain why

distinctcount

query gives back the correct result..

Jackie

01/27/2022, 8:05 PM

@xtrntr If you have time, can we do a zoom session to debug the issue?

xtrntr

01/27/2022, 8:08 PM

when do you want?

Jackie

01/27/2022, 8:10 PM

I can do it now if you are available

xtrntr

01/27/2022, 8:11 PM

sorry it's 4 am singapore time.. can we schedule it at a better period?

Jackie

01/27/2022, 8:14 PM

I can do it today from 3-5PM or tomorrow before 11AM or after 1PM PST

xtrntr

01/27/2022, 8:15 PM

what is the earliest time before 11am that you are available?

Jackie

01/27/2022, 8:31 PM

I can do 9:30

xtrntr

01/27/2022, 8:36 PM

https://www.worldtimebuddy.com/?qm=1&lid=208,1880252&h=208&date=2022-1-28&sln=9.5-10&hf=1

xtrntr

01/27/2022, 8:36 PM

can i confirm this timing?

xtrntr

01/27/2022, 8:37 PM

i will be there

xtrntr

01/27/2022, 8:37 PM

thank you for offering to help debug

Jackie

01/27/2022, 8:38 PM

Sounds good. Let's chat tomorrow

xtrntr

01/28/2022, 5:28 PM

hi @Jackie, i’m ready

Jackie

01/28/2022, 5:31 PM

@xtrntr https://startreedata.zoom.us/my/startree.jackie

xtrntr

01/28/2022, 5:35 PM

Copy code

requestId=156,table=eventsv1202109_OFFLINE,timeMs=870,docs=394858352/3192209696,entries=0/394858352,segments(queried/processed/matched/consuming/unavailable):70/14/14/0/0,consumingFreshnessTimeMs=0,servers=4/4,groupLimitReached=false,brokerReduceTimeMs=110,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-2_O=0,692,3311176,0,1;pinot-server-0_O=0,667,3407864,1,1;pinot-server-1_O=0,759,3362920,0,1;pinot-server-3_O=0,695,3507770,1,1,offlineThreadCpuTimeNs=0,realtimeThreadCpuTimeNs=0,query=SELECT DISTINCTCOUNT(userid_int) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000

xtrntr

01/28/2022, 5:35 PM

Copy code

distinctcount(userid_int)
1000000

xtrntr

01/28/2022, 5:37 PM

earlier you were saying, entries = 0 means ?

Jackie

01/28/2022, 5:37 PM

Copy code

entries=0/394858352

Jackie

01/28/2022, 5:37 PM

Means no entry scanned during the filtering phase

xtrntr

01/28/2022, 5:38 PM

let me check version

xtrntr

01/28/2022, 5:39 PM

server is

v0.9.3

xtrntr

01/28/2022, 5:39 PM

ingestion job is

v0.8.0

xtrntr

01/28/2022, 5:39 PM

should they be same version?

xtrntr

01/28/2022, 5:41 PM

Copy code

segment.padding.character = \u0000
segment.name = eventsv1202109_OFFLINE_2021-10-25_9
segment.table.name = eventsv1202109
segment.dimension.column.names = cell_id,userid_int
segment.metric.column.names =
segment.datetime.column.names = time_rounded
segment.total.docs = 48582356
038757633914.c000.snappy.parquet

column.userid_int.cardinality = 669575
column.userid_int.totalDocs = 48582356
column.userid_int.dataType = INT
column.userid_int.bitsPerElement = 20
column.userid_int.lengthOfEachEntry = 0
column.userid_int.columnType = DIMENSION
column.userid_int.isSorted = true
column.userid_int.hasNullValue = false
column.userid_int.hasDictionary = true
column.userid_int.textIndexType = NONE
column.userid_int.hasInvertedIndex = true
column.userid_int.hasFSTIndex = false
column.userid_int.hasJsonIndex = false
column.userid_int.isSingleValues = true
column.userid_int.maxNumberOfMultiValues = 0
column.userid_int.totalNumberOfEntries = 48582356
column.userid_int.isAutoGenerated = false
column.userid_int.minValue = 7296465
column.userid_int.maxValue = 8119811
column.userid_int.defaultNullValue = -2147483648

column.cell_id.cardinality = 2459
column.cell_id.totalDocs = 48582356
column.cell_id.dataType = INT
column.cell_id.bitsPerElement = 12
column.cell_id.lengthOfEachEntry = 0
column.cell_id.columnType = DIMENSION
column.cell_id.isSorted = false
column.cell_id.hasNullValue = false
column.cell_id.hasDictionary = true
column.cell_id.textIndexType = NONE
column.cell_id.hasInvertedIndex = true
column.cell_id.hasFSTIndex = false
column.cell_id.hasJsonIndex = false
column.cell_id.isSingleValues = true
column.cell_id.maxNumberOfMultiValues = 0
column.cell_id.totalNumberOfEntries = 48582356
column.cell_id.isAutoGenerated = false
column.cell_id.minValue = -1
column.cell_id.maxValue = 2458
column.cell_id.defaultNullValue = -2147483648

column.time_rounded.cardinality = 96
column.time_rounded.totalDocs = 48582356
column.time_rounded.dataType = LONG
column.time_rounded.bitsPerElement = 7
column.time_rounded.lengthOfEachEntry = 0
column.time_rounded.columnType = DATE_TIME
column.time_rounded.isSorted = false
column.time_rounded.hasNullValue = false
column.time_rounded.hasDictionary = true
column.time_rounded.textIndexType = NONE
column.time_rounded.hasInvertedIndex = true
column.time_rounded.hasFSTIndex = false
column.time_rounded.hasJsonIndex = false
column.time_rounded.isSingleValues = true
column.time_rounded.maxNumberOfMultiValues = 0
column.time_rounded.totalNumberOfEntries = 48582356
column.time_rounded.isAutoGenerated = false
column.time_rounded.datetimeFormat = 1:HOURS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss
column.time_rounded.datetimeGranularity = 15:MINUTES
column.time_rounded.minValue = 1635091200000
column.time_rounded.maxValue = 1635176700000
column.time_rounded.defaultNullValue = -9223372036854775808
segment.index.version = v3

Jackie

01/28/2022, 5:42 PM

index_map

xtrntr

01/28/2022, 5:42 PM

Copy code

userid_int.dictionary.startOffset = 0
userid_int.dictionary.size = 2678308
userid_int.forward_index.startOffset = 2678308
userid_int.forward_index.size = 5356608

cell_id.dictionary.startOffset = 50544982
cell_id.dictionary.size = 9844
cell_id.forward_index.startOffset = 50554826
cell_id.forward_index.size = 72873542

time_rounded.dictionary.startOffset = 123428368
time_rounded.dictionary.size = 776
time_rounded.forward_index.startOffset = 123429144
time_rounded.forward_index.size = 42509570

Jackie

01/28/2022, 5:43 PM

It this everything in the

index_map

Jackie

01/28/2022, 5:43 PM

I don't see the inverted index here

Jackie

01/28/2022, 5:44 PM

Copy code

column.userid_int.isSorted = true

Jackie

01/28/2022, 5:44 PM

That explains why there is no scan

Jackie

01/28/2022, 5:47 PM

Can you try

SELECT userid_int, count(*) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000 GROUP BY userid_int LIMIT 10000000 OPTION(minServerGroupTrimSize=-1)

Jackie

01/28/2022, 5:47 PM

Also

SELECT userid_int, count(*) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000 GROUP BY userid_int ORDER BY userid_int LIMIT 10000000

xtrntr

01/28/2022, 5:47 PM

SELECT userid_int, count(*) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000 GROUP BY userid_int LIMIT 10000000 OPTION(minServerGroupTrimSize=-1)

1-10 of 883574

xtrntr

01/28/2022, 5:47 PM

still truncated

xtrntr

01/28/2022, 5:48 PM

SELECT userid_int, count(*) FROM eventsv1202109 WHERE userid_int BETWEEN 7000001 AND 8000000 GROUP BY userid_int ORDER BY userid_int LIMIT 10000000

1-10 of 883574

also truncated

xtrntr

01/28/2022, 5:51 PM

thanks for taking the time today @Jackie, will file a github issue

xtrntr

01/28/2022, 6:09 PM

@Jackie https://github.com/apache/pinot/issues/8089

Jackie

01/28/2022, 6:09 PM

Thanks! Will post my findings there

xtrntr

01/28/2022, 6:09 PM

data is sensitive so i can’t share data but i will try to generate some similar data that can replicate the issue if there is time

Open in Slack

Previous Next