Hello during query performance testing we found ou...
# troubleshooting
g
Hello during query performance testing we found out that most of our queries spend a lot of time in steps that doesn't really make sense, can you shred some light into if it is possible to optimize those steps or it is normal behavior? Our queries were using the V1 engine (without multistage) and are simple sum, avg, min, max aggregations per customer_id (we have partitioning for it, so pinot scans relatively small amounts of data) and returning between 1 and 10 rows per query, where most of the queries also had a simple group by 1 column without ordering after that. If you need more info let me know
Those were results for a table with 6.7B documents where we have 1 broker and 21 servers (8cpu + 64ram)
m
What times are these? Max or some percentile?
g
image.png
all of them follow this pattern and the name of the metric is the query name
m
How many segments do you have? Can you also share entriesScannedInFilter metrics? This seems likely highly unoptimized numbers
g
image.png,image.png
there is only 1 table, so those metrics are accurate
i can send you the schema + realtime table as well if needed
m
Thanks, let’s start with what I requested above
g
3073 segments without replication and 9219 with replication and for entries scanned in filter you can see them in the second picture
Broker Entries Scanned Rate and Server Entries Scanned Rate are the graph names
m
Entries scanned in filter should be zero. Non zero means indexing is not set in desired way
g
can i send you example query and table indexes to help me understand what is wrong ?
m
If queries select few rows, then these numbers should be in milliseconds range
Yes please send
Also what disks are you using for severs
g
realtime-table-config.json
Copy code
SELECT bets_trading_bet_type,
       MIN(creation_date) as FirstBetDate, 
       SUM(bets_selections_stake) as TotalHandle, 
       SUM(bets_selections_gain) as TotalGain, 
       max(bets_stake) as MaxBetStake, 
       min(bets_stake) as MinBetStake, 
       count(distinct bets_id) as BetsCount,
       count(distinct bets_selections_sport_id) as UniqueSportsCount,
       count(distinct bets_selections_league_id) as UniqueLeaguesCount,
       avg(bets_selections_count) as SelectionsCount
FROM purchases
WHERE customer_id = {customerId}
group by bets_trading_bet_type
google's hyperdisks each server has 2TB storage set
we run like 10 different such queries but with different group by columns
m
If all queries will be for one customer you should sort and partition on customerID.
g
the kafka topic is partitioned by customer id
m
Also, I’d remove the no-dict columns if they are not very high cardinality
g
they are money values without end ranges and the 3 id columns are autoincrement ids, where there are no duplicates
how would i do the sort and partition on customerID is this a config in pinot or something else?
m
You need to sort as well (add sorted column). Also match partition function in Pinot (murmur2) along with partition routing config to ensure query fans out to 1 server
If a query is fanning out to more than one server or processing too many segments then optimizations are not configured correctly
g
they are going to 1 server only
here is example response from the controller: { "resultTable": { "dataSchema": { "columnNames": [ "bets_trading_bet_type", "FirstBetDate", "TotalHandle", "TotalGain", "MaxBetStake", "MinBetStake", "BetsCount", "UniqueSportsCount", "UniqueLeaguesCount", "SelectionsCount" ], "columnDataTypes": [ "INT", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "INT", "INT", "INT", "DOUBLE" ] }, "rows": [ ] }, "numRowsResultSet": 5, "partialResult": false, "exceptions": [], "numGroupsLimitReached": false, "timeUsedMs": 340, "requestId": "153356555003882565", "brokerId": "Broker_pinot-broker-0.pinot-broker.pinot.svc.cluster.local_8099", "numDocsScanned": 3780, "totalDocs": 318534606, "numEntriesScannedInFilter": 0, "numEntriesScannedPostFilter": 34020, "numServersQueried": 1, "numServersResponded": 1, "numSegmentsQueried": 147, "numSegmentsProcessed": 146, "numSegmentsMatched": 101, "numConsumingSegmentsQueried": 1, "numConsumingSegmentsProcessed": 0, "numConsumingSegmentsMatched": 0, "minConsumingFreshnessTimeMs": 1750087570113, "numSegmentsPrunedByBroker": 2926, "numSegmentsPrunedByServer": 1, "numSegmentsPrunedInvalid": 0, "numSegmentsPrunedByLimit": 0, "numSegmentsPrunedByValue": 0, "brokerReduceTimeMs": 0, "offlineThreadCpuTimeNs": 0, "realtimeThreadCpuTimeNs": 0, "offlineSystemActivitiesCpuTimeNs": 0, "realtimeSystemActivitiesCpuTimeNs": 0, "offlineResponseSerializationCpuTimeNs": 0, "realtimeResponseSerializationCpuTimeNs": 0, "offlineTotalCpuTimeNs": 0, "realtimeTotalCpuTimeNs": 0, "explainPlanNumEmptyFilterSegments": 0, "explainPlanNumMatchAllFilterSegments": 0, "traceInfo": {}, "tablesQueried": [ "purchases" ] }
the partitioning with murmur2 hashing function i have done and tested it working, as i had a bug there and queries didn't return results, as for the sort column can you send me docs about it
m
No there isn’t a bug in partitioning, it is very widely used
Just add sorted columns in table config (search doc)
g
what i meant is that i was producing to kafka using different algorithm then the one specified inside the realtime table
m
I see, the default in Kafka actually matches Pinot, so that is also surprising.
g
in java yes, in .net it uses different algorithm
m
Ok, partitioning is not a must have
Sorting itself will take you long way
Also look at replica groups in docs
Also I see you are upserting on time, that is a bit odd? Are you trying to dedup?
g
yes, we get snapshots of the purchase state
so the newer ones have more information set and newer UpdateDate