You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@pinot.apache.org by Pinot Slack Email Digest <sn...@apache.org> on 2021/05/03 02:00:20 UTC
Apache Pinot Daily Email Digest (2021-05-02)
### _#general_
**@j.wise.hunter:** @j.wise.hunter has joined the channel
**@kautsshukla:** Hi All, I have a Realtime table consuming from kafka. As of
now it has 5 Billion records. I’m performing look up [predicate] on inverted
index keys [userid, eventcategory, eventlabel] with using “metricFieldSpecs”
column as timestampist for range condition. My Query is taking too much time
to finish > 10 seconds almost. How can i can configure it with best optimised
configuration. Query ```select userid,eventlabel,sessionid, MIN(timestampist)
as mint, MAX(timestampist) as maxt, (MAX(timestampist) - MIN(timestampist)) as
diff_time from default.click_stream where eventlabel !='null' and timestampist
between 1615833000000 and 1616225312000 group by
userid,eventlabel,sessionid```
**@mayanks:** How long does it take if you just do count(*) without group by
but using same where clause? And how many records does it select? My guess is
your query selects too many rows and so group by is slow In this case, may be
try star tree indexing
**@kautsshukla:** @mayanks: select count(*) from click_stream , average its
taking 250ms
**@kautsshukla:** @mayanks: It’s taking 5 seconds for ---- select
MIN(timestampist) as mint, MAX(timestampist) as maxt, (MAX(timestampist) -
MIN(timestampist)) as diff_time from click_stream where timestampist between
1612264456000 and 1619962345000. numDocsScanned : 442963466
**@kautsshukla:** ```select userid,eventlabel, MIN(timestampist) as mint,
MAX(timestampist) as maxt, (MAX(timestampist) - MIN(timestampist)) as
diff_time from click_stream where timestampist between 1612264456000 and
1619962345000 group by userid,eventlabel. This is taking 60 seconds
numDocsScanned 221866025```
**@mayanks:** Yeah, you are trying to group by 442M records. How many nodes,
what’s the jvm heap size? Your query is essentially very expensive
**@kautsshukla:** I have re created table with star tree : ```{ "tableName":
"click_stream_REALTIME", "tableType": "REALTIME", "segmentsConfig": {
"segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
"timeColumnName": "created_at", "retentionTimeUnit": "DAYS",
"retentionTimeValue": "90", "segmentPushType": "APPEND", "timeType": "DAYS",
"replication": "1", "replicasPerPartition": "1", "schemaName": "click_stream"
}, "tenants": { "broker": "DefaultTenant", "server": "DefaultTenant" },
"tableIndexConfig": { "autoGeneratedInvertedIndex": true, "loadMode": "MMAP",
"streamConfigs": { "streamType": "kafka", "stream.kafka.consumer.type":
"simple", "stream.kafka.topic.name": "vedantu.dp.click_stream.click_stream",
"stream.kafka.decoder.class.name":
"org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
"stream.kafka.consumer.factory.class.name":
"org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
"stream.kafka.hlc.zk.connect.string": "zk1:2181/kafka",
"stream.kafka.zk.broker.url": "zk1:2181/kafka", "stream.kafka.broker.list":
"kafka01:9092", "realtime.segment.flush.threshold.time": "3600000",
"realtime.segment.flush.threshold.size": "50000",
"stream.kafka.consumer.prop.auto.offset.reset": "smallest" },
"aggregateMetrics": false, "enableDefaultStarTree": false,
"enableDynamicStarTreeCreation": false, "nullHandlingEnabled": true,
"createInvertedIndexDuringSegmentGeneration": false, "starTreeIndexConfigs": [
{ "dimensionsSplitOrder": [ "eventcategory", "eventlabel", "userid",
"sessionid" ], "skipStarNodeCreationForDimensions": [], "functionColumnPairs":
[ "MIN__timestampist", "MAX__timestampist" ] } ], "rangeIndexColumns": [
"timestampist", "created_at" ] }, "metadata": { "customConfigs": {} },
"routing": { "instanceSelectorType": "replicaGroup" }, "isDimTable": false
}```
**@kautsshukla:** @mayanks but it doesn’t help here, what I have to follow, do
suggest me.
**@mayanks:** The query is really expensive.
**@mayanks:** How many servers and what’s the jvm heap
**@kautsshukla:** Yeah, there are 2 servers with 100GB heap space
**@mayanks:** What’s the use case? Is this going to be a real production
query? Or are you experimenting
**@kautsshukla:** This is going to be a prod query : ```select
userid,eventlabel,sessionid, MIN(timestampist) as mint, MAX(timestampist) as
maxt, (MAX(timestampist) - MIN(timestampist)) as diff_time from
default.click_stream where sessionid !='null' and timestampist between
1615833000000 and 1616225312000 group by userid,eventlabel,sessionid```
**@kautsshukla:** scanning 3 months ka data.
**@mayanks:** What’s your time column granularity
**@kautsshukla:** 1 minute
**@mayanks:** Hmm can you live with 1 day
**@mayanks:** Since you are going back 3 months?
**@mayanks:** And uses aggregate metrics. This will reduce number of rows and
improve latency
**@kautsshukla:** I’ll try now
**@kautsshukla:** ```"dateTimeFieldSpecs": [ { "name": "created_at",
"dataType": "LONG", "format": "1:MILLISECONDS:EPOCH", "granularity": "1:DAY" }
]```
**@kautsshukla:** @mayanks should i try this…
**@mayanks:** Aslo enable metrics aggregation
**@kautsshukla:** OK
**@kautsshukla:** “aggregateMetrics”: false --- > true
**@mayanks:**
**@mayanks:** Lets move to troubleshooting
**@kautsshukla:** @mayanks yes
**@kautsshukla:** @mayanks Only supported aggregation right now is `SUM` on
“aggregateMetrics”: true. Which aren’t using as a part of query. Now My
queries are not ever responding in 60 seconds with new table definition with
start tree index.
**@kautsshukla:** This is the schema ```{ "schemaName": "click_stream",
"dimensionFieldSpecs": [ { "name": "id", "dataType": "STRING" }, { "name":
"ipaddress", "dataType": "STRING" }, { "name": "city", "dataType": "STRING" },
{ "name": "region", "dataType": "STRING" }, { "name": "country", "dataType":
"STRING" }, { "name": "continent", "dataType": "STRING" }, { "name":
"postalcode", "dataType": "STRING" }, { "name": "latitude", "dataType":
"DOUBLE" }, { "name": "longitude", "dataType": "DOUBLE" }, { "name": "isp",
"dataType": "STRING" }, { "name": "useragent", "dataType": "STRING" }, {
"name": "browsername", "dataType": "STRING" }, { "name": "browserversion",
"dataType": "STRING" }, { "name": "osname", "dataType": "STRING" }, { "name":
"osversion", "dataType": "STRING" }, { "name": "devicename", "dataType":
"STRING" }, { "name": "deviceversion", "dataType": "STRING" }, { "name":
"clientid", "dataType": "STRING" }, { "name": "loggedin", "dataType": "STRING"
}, { "name": "grade", "dataType": "STRING" }, { "name": "target", "dataType":
"STRING" }, { "name": "board", "dataType": "STRING" }, { "name": "devicetype",
"dataType": "STRING" }, { "name": "referrer", "dataType": "STRING" }, {
"name": "utm_source", "dataType": "STRING" }, { "name": "utm_medium",
"dataType": "STRING" }, { "name": "utm_campaign", "dataType": "STRING" }, {
"name": "utm_term", "dataType": "STRING" }, { "name": "utm_content",
"dataType": "STRING" }, { "name": "channel", "dataType": "STRING" }, { "name":
"sessionid", "dataType": "STRING" }, { "name": "domain", "dataType": "STRING"
}, { "name": "fullurl", "dataType": "STRING" }, { "name": "pagepath",
"dataType": "STRING" }, { "name": "pagetitle", "dataType": "STRING" }, {
"name": "screenname", "dataType": "STRING" }, { "name": "type", "dataType":
"STRING" }, { "name": "appname", "dataType": "STRING" }, { "name":
"apptrackingid", "dataType": "STRING" }, { "name": "screenresolution",
"dataType": "STRING" }, { "name": "viewportsize", "dataType": "STRING" }, {
"name": "eventcategory", "dataType": "STRING" }, { "name": "eventaction",
"dataType": "STRING" }, { "name": "eventlabel", "dataType": "STRING" }, {
"name": "k1", "dataType": "STRING" }, { "name": "v1", "dataType": "STRING" },
{ "name": "k2", "dataType": "STRING" }, { "name": "v2", "dataType": "STRING"
}, { "name": "k3", "dataType": "STRING" }, { "name": "v3", "dataType":
"STRING" }, { "name": "k4", "dataType": "STRING" }, { "name": "v4",
"dataType": "STRING" }, { "name": "k5", "dataType": "STRING" }, { "name":
"v5", "dataType": "STRING" }, { "name": "k6", "dataType": "STRING" }, {
"name": "v6", "dataType": "STRING" }, { "name": "k7", "dataType": "STRING" },
{ "name": "v7", "dataType": "STRING" }, { "name": "k8", "dataType": "STRING"
}, { "name": "v8", "dataType": "STRING" }, { "name": "k9", "dataType":
"STRING" }, { "name": "v9", "dataType": "STRING" }, { "name": "k10",
"dataType": "STRING" }, { "name": "v10", "dataType": "STRING" }, { "name":
"useremail", "dataType": "STRING" }, { "name": "firstpagetitle", "dataType":
"STRING" }, { "name": "first_fullurl", "dataType": "STRING" }, { "name":
"deviceuniqueid", "dataType": "STRING" }, { "name": "deviceid", "dataType":
"STRING" }, { "name": "devicebrand", "dataType": "STRING" }, { "name":
"devicemanufacturer", "dataType": "STRING" }, { "name": "devicemodel",
"dataType": "STRING" }, { "name": "serialnumber", "dataType": "STRING" }, {
"name": "buildnumber", "dataType": "STRING" }, { "name": "carrier",
"dataType": "STRING" }, { "name": "batterylevel_new", "dataType": "STRING" },
{ "name": "firstinstalltime", "dataType": "INT" }, { "name": "isemulator",
"dataType": "STRING" }, { "name": "istablet", "dataType": "STRING" }, {
"name": "islandscape", "dataType": "STRING" }, { "name": "batterylevel",
"dataType": "INT" }, { "name": "firstinstalltimeist", "dataType": "INT" }, {
"name": "firstopened_datetime", "dataType": "LONG" }, { "name":
"daysfrom_firstopened", "dataType": "INT" }, { "name": "doubtactionablecount",
"dataType": "INT" }, { "name": "gapp_userstatus", "dataType": "STRING" }, {
"name": "gaid", "dataType": "STRING" }, { "name":
"last_attributed_touch_type", "dataType": "STRING" }, { "name":
"last_attributed_touch_timestamp", "dataType": "STRING" }, { "name":
"last_attributed_touch_timestamp_iso", "dataType": "LONG" }, { "name":
"last_attributed_touch_data_tilde_campaign_id", "dataType": "STRING" }, {
"name": "last_attributed_touch_data_tilde_branch_ad_format", "dataType":
"STRING" }, { "name": "last_attributed_touch_data_tilde_technology_partner",
"dataType": "STRING" }, { "name":
"last_attributed_touch_data_tilde_banner_dimensions", "dataType": "STRING" },
{ "name": "firsttransactiondate", "dataType": "LONG" }, { "name":
"gpslatitude", "dataType": "DOUBLE" }, { "name": "gpslongitude", "dataType":
"DOUBLE" }, { "name": "gpscity", "dataType": "STRING" }, { "name": "source",
"dataType": "STRING" }, { "name": "version", "dataType": "STRING" }, { "name":
"userid", "dataType": "STRING" }, { "name": "calc_userid", "dataType":
"STRING" } ], "metricFieldSpecs": [ { "name": "timestampist", "dataType":
"LONG" } ], "timeFieldSpec": { "incomingGranularitySpec": { "name":
"created_at", "dataType": "LONG", "timeType": "MILLISECONDS" } },
"primaryKeyColumns": [ "userid", "eventcategory", "eventlabel" ] }```
**@kautsshukla:** It’s not a upsert table. the table config is which I’m
using…. ```{ "REALTIME": { "tableName": "click_stream_REALTIME", "tableType":
"REALTIME", "segmentsConfig": { "segmentAssignmentStrategy":
"BalanceNumSegmentAssignmentStrategy", "timeColumnName": "created_at",
"retentionTimeUnit": "DAYS", "retentionTimeValue": "90", "segmentPushType":
"APPEND", "timeType": "DAYS", "replication": "1", "replicasPerPartition": "1",
"schemaName": "click_stream" }, "tenants": { "broker": "DefaultTenant",
"server": "DefaultTenant" }, "tableIndexConfig": { "rangeIndexColumns": [
"created_at", "timestampist" ], "autoGeneratedInvertedIndex": false,
"sortedColumn": [ "userid", "eventcategory", "eventlabel" ], "loadMode":
"MMAP", "streamConfigs": { "streamType": "kafka",
"stream.kafka.consumer.type": "simple", "stream.kafka.topic.name":
"click_stream", "stream.kafka.decoder.class.name":
"org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
"stream.kafka.consumer.factory.class.name":
"org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
"stream.kafka.hlc.zk.connect.string": "zk1:2181/kafka",
"stream.kafka.zk.broker.url": "zk1:2181/kafka", "stream.kafka.broker.list":
"kfk1:9092", "realtime.segment.flush.threshold.time": "3600000",
"realtime.segment.flush.threshold.size": "50000",
"stream.kafka.consumer.prop.auto.offset.reset": "smallest" },
"aggregateMetrics": true, "enableDefaultStarTree": true,
"enableDynamicStarTreeCreation": true, "nullHandlingEnabled": false,
"invertedIndexColumns": [ "userid", "eventcategory", "eventlabel" ],
"createInvertedIndexDuringSegmentGeneration": true }, "metadata": {
"customConfigs": {} }, "routing": { "instanceSelectorType": "replicaGroup" },
"isDimTable": false } }```
### _#random_
**@j.wise.hunter:** @j.wise.hunter has joined the channel
### _#troubleshooting_
**@j.wise.hunter:** @j.wise.hunter has joined the channel
**@mayanks:** @kautsshukla Moving to troubleshooting:
**@mayanks:** Given that the query is inherently expensive, perhaps you can
share a bit about the use case so we can see if the query itself can be
modified
**@kautsshukla:** @mayanks We have two types of queries ```select
userid,eventlabel,sessionid, MIN(timestampist) as mint, MAX(timestampist) as
maxt, (MAX(timestampist) - MIN(timestampist)) as diff_time from
default.click_stream where sessionid !='null' and timestampist between
1615833000000 and 1616225312000 group by userid,eventlabel,sessionid ```
**@kautsshukla:** select userid, v1, max(timestamp) as timestampist from
clickstream where eventlabel=‘app_classroom_video_seen’ and k1 = ‘video_id’
and timestampist between 1608057000000 and 1615965662009 group by userid, v1
**@kautsshukla:** These queries are taking too much time process every time..
Its taking more than 60 Sec and got timed out Total docs as of now is :
310767668
**@mayanks:** When I say use case I mean what is the end goal, is this a
dashboard, how it is going to be used etc
**@kautsshukla:** It’s going to be a dashboard and its like 500 around
concurrent users may use it in parallel. Every time a users opens this
dashboard it will fire these queries.
**@mayanks:** So if there are 500M records selected in query with 1minutr
granularity there will be millions of groups (at least)
**@mayanks:** Is the dashboard going to show all that?
**@kautsshukla:** As you suggested i made it to 1 day level granuality. As i
can live with 1 day granuality.
**@mayanks:** Can you do offline also?
**@mayanks:** For example real-time for 1 day and preaggregate in offline with
star tree for 29’days
**@kautsshukla:** How its gonna help here ?
**@mayanks:** Offline preaggregation with star tree will be very fast
**@kautsshukla:** I have to maintain 2 tables one is realtime with one day
granulaity and 1 with 29 days ?
**@mayanks:** Nope
**@kautsshukla:** offline with 29
**@mayanks:** Single table
**@kautsshukla:** Ok, what are the changes i should made it ?
**@mayanks:** Single table with offline and real-time component. Generate star
tree in offline and push
**@mayanks:** Do you have an estimate of how many unique sessions per day? And
how many unique groups you expect?
**@mayanks:** If we can somehow control the number of unique groups a query
has to process that would help
**@kautsshukla:** I’ll check and let you know
### _#announcements_
**@j.wise.hunter:** @j.wise.hunter has joined the channel
\--------------------------------------------------------------------- To
unsubscribe, e-mail: dev-unsubscribe@pinot.apache.org For additional commands,
e-mail: dev-help@pinot.apache.org