Hey team :wave: we are trying to optimize pinot's ...
# troubleshooting
z
Hey team πŸ‘‹ we are trying to optimize pinot's index on the time series data: we currently have a column request_ts with timestamp in seconds, so we define it as
Copy code
{
		"name": "request_ts",
		"dataType": "TIMESTAMP",
		"format": "1:SECONDS:EPOCH",
		"granularity": "1:SECONDS"
	  },
to improve the index, we also doing 1. "timeColumnName": "request_ts", 2. "rangeIndexColumns": ["request_ts"], 3. "routing": {"segmentPrunerTypes": ["time"]} 4. add request_ts to timestampConfig with granularities like ["DAY","WEEK","MONTH"] Could you please share if the operation is helpful or some of them does not improve the performance too much πŸ™‡ cc: @Neeraja Sridharan @Sai Tarun Tadakamalla
m
Yes, this makes sense. What granularity do you query in?
n
Sample query:
Copy code
SELECT
  x,
  DISTINCTCOUNTMV(ids1) FILTER(WHERE ids1_count > 0) as num_unique_ids1,
  DISTINCTCOUNTMV(ids2) FILTER(WHERE ids2_count > 0) as num_unique_ids2
FROM real_time_pinot_metrics_table
WHERE
    x in ${IDs}
    AND team = {$TEAM_ID}
    AND request_ts >= {$REQUEST_TS_START_RANGE}
	AND request_ts <= {$REQUEST_TS_END_RANGE}
GROUP BY x
Currently, request_ts query range is last 24 hours. We might have any future use-cases querying for a day, as an example. Does it make sense to have all the 3 listed below? 1. Range index 2. Timestamp index 3. Time based segment pruning If so, any insights into how each one of them would benefit this scenario? πŸ™‡β€β™€οΈ
m
That makes sense. On top of that you can also do: β€’ If there's a equality predicate on a high cardinality item (say team), then sort (and optionally partition) on that. β€’ If not partitioned on another column, you can also partition on ids, that will help with distinctcount optimizations. β€’ Inv index on medium/low cardinality columns with predicates.
n
Thanks @Mayank! We have set up inverted index currently, but have not enabled sorted column index and partition-based segment pruning for our real-time Pinot table. This is because, the corresponding upstream Kafka data needs to be sorted. Similarly, for partitions to take effect, the upstream data needs to follow the same partition scheme - correct? Kafka -> Real-time Pinot: There is no easy way to set this up in our Kafka upstream source currently, unless we build some custom layer to enable sorting & partitioning before ingesting to Pinot. How do sorting & partitioning work for real-time data?
m
No, upstream data does not need to be sorted.
It does need to be partitioned though (in open source). In closed source there are re-partitioning tasks
thanks 2
n
Ah I see, Good to know! Then we can just configure the team_id to be the sorted column index without any pre-ingestion sorting set up - is that correct? We'll skip partition based segment pruning (for now) though, as that has a pre-requisite.
πŸ‘ 1
m
Yes correct
Is it high cardinality column? You don't want to use a low cardinality column for sorting
πŸ‘ 1
n
Just curious:
SEGMENTPARTITIONEDDISTINCTCOUNT
that you shared above - does it work for multi-value columns as well?
m
I think it doesn't
πŸ‘ 1
n
Hey @Mayank πŸ‘‹ As a follow-up to this discussion: We're trying to set up
Timestamp index
for a
request_ts
column, where the data being ingested for this column is in epoch seconds format:
Copy code
{
  "name": "request_ts",
  "dataType": "LONG",
  "format": "1:SECONDS:EPOCH",
  "granularity": "1:SECONDS"
}
Based on this document for
Timestamp index
:
Supported data type
A
TIMESTAMP
index can only be created on the
TIMESTAMP
data type.
Is it mandatory to have the
TIMESTAMP
data type for
request_ts
in the Schema config? If so, appreciate any help in providing a recommended approach with the set up needed (schema & table config) for declaring timestamp index on this column πŸ™‡β€β™€οΈ
Copy code
"fieldConfigList": [
      {
        "name": "request_ts",
        "indexTypes": ["TIMESTAMP"],
        "timestampConfig": {
          "granularities": ["DAY", "WEEK", "MONTH"]
        }
      }
    ]
cc: @Sai Tarun Tadakamalla
m
You already pasted the config above. Given you are doing 24hrs, you can use HOUR granularity.
thanks 1
n
Sure, I can add HOUR to ""granularities": ["DAY", "WEEK", "MONTH"]" . But do I have to declare the below schema as TIMESTAMP data type instead of LONG EPOCH format?
Copy code
{
  "name": "request_ts",
  "dataType": "LONG",
  "format": "1:SECONDS:EPOCH",
  "granularity": "1:SECONDS"
}
Based on this document for
Timestamp index
:
Supported data type
A
TIMESTAMP
index can only be created on the
TIMESTAMP
data type.
m
"dataType": "TIMESTAMP",
you need to change the data type
n
Would this work on the fly given that the incoming data is in epoch seconds or do we have to do a custom transform?
Copy code
{
  "name": "request_ts",
  "dataType": "TIMESTAMP",
  "format": "1:SECONDS:EPOCH",
  "granularity": "1:SECONDS"
}
m
timestamp is internally stored as long
so i think it should work, you can try
n
Sure, will give it a try. Does the below look reasonable to enable timestamp index on request_ts (primary time column)? Schema config:
Copy code
{
  "name": "request_ts",
  "dataType": "TIMESTAMP",
  "format": "1:SECONDS:EPOCH",
  "granularity": "1:SECONDS"
}
Table config:
Copy code
"fieldConfigList": [
      {
        "name": "request_ts",
        "indexTypes": ["TIMESTAMP"],
        "timestampConfig": {
          "granularities": ["HOUR", "DAY", "WEEK", "MONTH"]
        }
      }
    ]
🌟 1
@Mayank Above ^^ trial failed i.e.
request_ts
could not be converted to timestamp and we had ingestion failures while consuming the real-time data. I can try
MILLISECONDS
instead of
SECONDS
, but wanted to check if you could help unblock us with any other recommended solution. Just trying to see if we can avoid creating another transform column to be able to declare
TIMESTAMP
datatype and thereby
TIMESTAMP index
on it. It'd be great if there is a way to create TS index on request_ts, where the incoming data is in epoch seconds format πŸ™‡β€β™€οΈ
I was reviewing this article on Timestamp index from @Mark Needham (related repo) - looks like a custom transform column is needed to be able to declare the
dataType
as
TIMESTAMP
in the schema, and subsequently declare Timestamp index on it (though this is not documented). Could you please confirm?
Copy code
"fieldConfigList": [
      {
        "name": "DateEpoch",
        "encodingType": "DICTIONARY",
        "indexTypes": ["TIMESTAMP"],
        "timestampConfig": {
          "granularities": [
            "DAY",
            "WEEK",
            "MONTH"
          ]
        }
      }
    ]
Copy code
"transformConfigs": [
        {"columnName": "DateEpoch", "transformFunction": "FromDateTime(\"Date\", 'MM/dd/yyyy hh:mm:ss a')" }
      ]
Copy code
"dateTimeFieldSpecs": [
      {
        "name": "DateEpoch",
        "dataType": "TIMESTAMP",
        "format" : "1:MILLISECONDS:EPOCH",
        "granularity": "1:MILLISECONDS"
      }
    ]
Difference in my case being that the corresponding date column's incoming data is Epoch Seconds, while the example in the recipe is Floating Timestamp. So, wanted to check if Epoch Seconds is naturally compatible with
TIMESTAMP
dataType without any conversion, but we're seeing ingestion failures as attached in my earlier post ☝️. I suppose a transform like the below might be needed in my case - after which I can declare with timestamp dataType and thereby, TS index?
Copy code
"transformConfigs": [ 
    { 
     "columnName": "request_ts", 
     "transformFunction": "fromEpochSeconds(request_ts)" 
    }
]
If there's a equality predicate on a high cardinality item (say team), then sort (and optionally partition) on that.
re: this ^^ note shared earlier, I see the reverse in Pinot docs i.e. "_The most frequently used predicate column with low cardinality should be used for applying sorted inverted index ._" So, hope declaring
Timestamp index
on the
request_ts
column and
Sorted index
on a different low cardinality
enterprise_id
column still works (on top of range index and other inverted index) πŸ™‡β€β™€οΈ
cc: @Sai Tarun Tadakamalla @ZEBIN KANG @Jessica Stewart
m
Yes, in your case, try the transform to convert from seconds. The docs seem incorrect, can you share the link. Sorted index should be on high cardinality column which has equality predicate on majority of queries.
n
Thanks @Mayank! Doc: https://startree.ai/resources/best-practices-for-designing-tables-in-apache-pinot Under
Indexes
section:
The most frequently used predicate column with low cardinality should be used for applying sorted inverted index .
Also noticed in a few other Pinot-related discussions as well.
Please let us know your thoughts πŸ™
m
Please go by what I am saying for sorted column. I’ll follow up on docs
πŸ‘ 1
n
@Mayank Currently,
enterprise_id
column is not a high-cardinality column for our real-time Pinot table, but wondering if we can still set it up as
sortedColumn
based on the discussion here. cc: @Sai Tarun Tadakamalla
m
What's the cardinality? Any other columns?
s
The cardinality of
enterprise_id
is pretty low. Only about 0.001% of unique values per day.
m
What’s the read qps and latency. And will this become high cardinality in future
n
@Mayank I'm not yet sure about the read qps. We should be able to return results in less than 10 seconds for our user-facing dashboard. I don't expect enterprise_id to become high cardinality in the near future. cc: @Sai Tarun Tadakamalla
πŸ‘ 1
Also, currently we have
inverted index
set up for ~6 columns and planning to add ~5 more columns to inverted index. Is there a restriction or recommendation on the number of columns that can be used for this index? Wanted to see if enterprise_id (which is always an equality predicate/filter) can be added to
sorted index
. Here is the sample query:
Copy code
SELECT
  x,
  DISTINCTCOUNTMV(ids1) FILTER(WHERE ids1_count > 0) as num_unique_ids1,
  DISTINCTCOUNTMV(ids2) FILTER(WHERE ids2_count > 0) as num_unique_ids2
FROM real_time_pinot_metrics_table
WHERE
    x in ${XIDs}
    AND enterprise_id = {$ENTERPRISE_ID}
    AND request_timestamp >= CAST(toEpochSeconds(now()) * 1000 - 86400000 AS LONG) -- Last 24 hours UTC
GROUP BY x
m
Avoid transform in filter, index cannot be used in transform
Create derived column for time so you don’t have to do the transform
n
request_timestamp
is a transform column to enable Timestamp index that we discussed earlier in this thread. Did you mean creating another derived column for
CAST(toEpochSeconds(now()) * 1000 - 86400000 AS LONG)
to get the
Last 24 hours UTC
?
m
Oh sorry, that is the right hand side, that will get compiled to a literal
n
Yes, if RHS gets compiled to a literal, Timestamp index on
request_timestamp
column would still work as expected - is that correct?
m
Yes
n
Could you please help clarify regarding the sorted index and inverted index as well πŸ™‡β€β™€οΈ
m
What’s the max number rows for an enterprise for 24 hrs
You have distinct count which will be the bigger problem than indexing
n
Agree! Based on the query that I shared above, we have inverted index set up on: β€’ x β€’ ids1 β€’ ids2 β€’ ids1_count β€’ ids2_count Sorted index on: enterprise_id Timestamp index, range index & time-based segment pruning on: request_timestamp Distinct count is our business requirement and we have MV columns, hence DISTINCTCOUNTMV
May be we could consider something like DISTINCTCOUNTHLLMV if we don't achieve the required latency i.e < 10 seconds.
m
Yes
Partition on id might help distinct
n
Yes, but there is no easy way to set up partitioning in our Kafka upstream source currently, unless we build some custom layer to enable partitioning before ingesting to Pinot. So, trying out the appropriate indexes is our best bet for now.
What’s the max number rows for an enterprise for 24 hrs
@Mayank This ^^ is ~25 million rows for an enterprise for last 24 hrs (chose an enterprise with max rows). Are we still good to set up
enterprise_id
as
sortedColumn
, or would you rather recommend leaving it as
invertedIndex
? Here is the sample query:
Copy code
SELECT
  x,
  DISTINCTCOUNTMV(ids1) FILTER(WHERE ids1_count > 0) as num_unique_ids1,
  DISTINCTCOUNTMV(ids2) FILTER(WHERE ids2_count > 0) as num_unique_ids2
FROM real_time_pinot_metrics_table
WHERE
    x in ${XIDs}
    AND enterprise_id = {$ENTERPRISE_ID}
    AND request_timestamp >= CAST(toEpochSeconds(now()) * 1000 - 86400000 AS LONG) -- Last 24 hours UTC
GROUP BY x
m
Yeah, you can. However I feel the bottleneck will be on distinct count, you should focus on optimizing that
πŸ‘ 1
n
@Mayank Update: We're enabling sorted index on
request_timestamp
column which is our primary time column (on top of Timestamp & Range index), and moved
enterprise_id
to inverted index [TBD: performance evaluation]. DISTINCTCOUNTMV related optimizations to follow, in case of poor performance with the query sample shared above.
m
Generally speaking, it makes more sense to sort on a dimension , and have range index on time. It certainly does not make sense to have both sorted and range index on time, or more than one index on a column.
n
Oh yes, we already removed range index on request_timestamp and only have sorted & timestamp index on it.
It'd be a higher cardinality than enterprise_id, and all our queries anchor filters on request_timestamp
m
Sort on enterprise id, not in time
Sorting on time doesn't have much value, as you will have range filter on time in all queries, not equality.
n
I see! Is it okay to sort on enterprise_id even if low cardinality/based on all the details shared so far?
m
I mean sorting on time adds no value
πŸ‘ 1
n
Thanks @Mayank! We'll revisit our sorted column. Also, given that we're doing
DISTINCTCOUNTMV
on 2 array columns
ids1
&
ids2
- does it make sense to implement
bloom filters
and
no-dictionary columns
for these multi-value fields.
DISTINCTCOUNTHLLMV
is also having server timeout. May be add Star-Tree indexes for aggregation optimization? but hesitant about the additional storage it'll consume.
Copy code
{
  "noDictionaryColumns": [
    "ids1",
    "ids2"
  ],
  "bloomFilterColumns": [
    "ids1",
    "ids2"
  ],
  "starTreeIndexConfigs": [
    {
      "dimensionsSplitOrder": [
        "enterprise_id",
        "app_id"
      ],
      "functionColumnPairs": [
        "DISTINCTCOUNTMV__ids1",
        "DISTINCTCOUNTMV__ids2"
      ]
    }
  ]
}