https://pinot.apache.org/ logo
Join Slack
Powered by
has anyone seen the client become the bottleneck f...
# troubleshooting
x
has anyone seen the client become the bottleneck for their queries before? i’m seeing 
timeMs=~1000ms
on my broker, but it takes 2/3 seconds on python/java clients on that single line to execute the query e.g.
Copy code
# python
curs.execute(sql_query)

# java
ResultSetGroup pinotResultSetGroup = pinotConnection.execute(pinotClientRequest);
m
What's the size of the response?
x
this is for a query that fetches 400k ints
is the response size in the broker logs?
oh yeah it is, 
ResponseSize=6000433
m
I am guessing there's some JSON rendering/conversion happening that might be taking time cc: @Jackie
x
I can’t use idset and in_subquery directly to have all the work happen in the cluster so I need to split it into 2 queries on the client side
Would be nice if idset could work with queries that have a GROUP BY + HAVING clause
for the python client, the slowness definitely comes from the client:
Copy code
def execute_query(query):
     start = timer()
     curs.execute(query)
     end = timer()
     print(f"query took {end-start}")
     return curs.fetchall()
Copy code
python3 -m cProfile query.py

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
    208/1    0.015    0.000   48.651   48.651 {built-in method builtins.exec}
 811465/5    0.907    0.000   47.301    9.460 db.py:41(g)
 405731/2    0.519    0.000   42.400   21.200 db.py:52(g)
        2    0.368    0.184   42.400   21.200 db.py:369(fetchall)
   405729    0.396    0.000   41.531    0.000 db.py:393(__next__)
   405729    0.348    0.000   40.211    0.000 db.py:345(fetchone)
    405731   39.863    0.000   39.863    0.000 {method 'pop' of 'list' objects}
        2    0.000    0.000    4.900    2.450 db.py:263(execute)
@Xiang Fu
x
hmm, it may comes from the results json parsing
x
does broker return JSON only? is there a more efficient way to retrieve data?
m
The broker currently returns JSON, and I have also seen JSON parsing being slow for really large response sizes
k
We also noticed JSON parsing being the bottleneck for large responses. It would be useful to have a binary/serialized response format, similar to how Solr supports either xml or binary.
Though this definitely can create compatibility issues, when you want to extend the response format. At the time this was implemented for Solr, Avro didn’t exist but would have been a better option than their own custom format.
m
Yeah, it should be possible to send back serialized response that client side library can provide abstractions to iterate on etc. And yeah, I can see how it opens up the door for compatibility issues. Historically speaking though, the Pinot response has not changed that frequently
x
constructing an 
IdSet
is also slower than using raw ints in the IN clause @Jackie, from 10k ints onwards:
Copy code
query_str length: 27525
time taken with idset: 2079

query_str length: 78436
time taken with raw ids: 140
j
2s vs 140ms doesn’t seem reasonable. Could you please post more query stats? Also could it because of the cold start?
x
it cant be because of the cold start, i can replicate these times with subsequent queries. what kind of stats would you like to know?
Copy code
# raw ids
requestId=106,table=events-2021-03_OFFLINE,timeMs=66,docs=14870/449912692,entries=1103854/29740,segments(queried/processed/matched/consuming/unavailable):10/10/8/0/0,consumingFreshnessTimeMs=0,servers=2/2,groupLimitReached=false,brokerReduceTimeMs=5,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-1_O=5,30,33260,0,-1;pinot-server-0_O=2,24,28467,0,1,offlineThreadCpuTimeNs=22897030,realtimeThreadCpuTimeNs=0
Copy code
# idset
requestId=105,table=events-2021-03_OFFLINE,timeMs=1981,docs=2040075/449912692,entries=841265291/4080150,segments(queried/processed/matched/consuming/unavailable):10/10/10/0/0,consumingFreshnessTimeMs=0,servers=2/2,groupLimitReached=false,brokerReduceTimeMs=59,exceptions=0,serverStats=(Server=SubmitDelayMs,ResponseDelayMs,ResponseSize,DeserializationTimeMs,RequestSentDelayMs);pinot-server-1_O=0,1901,4049628,1,1;pinot-server-0_O=0,1918,4112944,1,-1,offlineThreadCpuTimeNs=3803269492,realtimeThreadCpuTimeNs=0
users = a really really long string of ids concatenated together , or an idset
Copy code
# query
"select time, count(distinct(user)) as count from {table} where user in ({users}) and time between {start} and {end} and location between 300 and 350 group by time limit 10000000"
j
Hmm, there are much more documents matches the id set (2040075) than raw ids (14870)
Can you please check if the id set created represent the same ids?
Open in Slack
PreviousNext
Powered by