a) descending order of cardinality, time column at the end b) no c) maxLeaf record - default is 100k, stick to default for now.

only at very high throughput - like 10k+ queries per sec

Hi, we had some server crashes. I was able to take some histo's (couldn't get a heap dump though). Like last time we saw that DirectR references were taking up the most space in the heap (refs to the mmapped segments). We found that setting

-XX:SoftRefLRUPolicyMSPerMB=0

and combined with java11 (pr coming in the next 2 days) we are seeing that gc clears more soft references than before. Found this on the jvm mailing list: https://mail.openjdk.java.net/pipermail/hotspot-gc-dev/2019-April/025586.html

Any thoughts/experiences regarding this? I will let you know how it goes:)

are there any default perf test in pinot repo that I can use to measure the performance ? I am starting pinot-server with

-XX:ActiveProcessorCount

java opts, so that I can override number of available processors, and workaround the need to request high number of CPUs in k8 deployment. Wanted to do some perf test to reach optimal value.

/**
   * Use at most 10 or half of the processors threads for each query. If there are less than 2 processors, use 1 thread.
   * <p>NOTE: Runtime.getRuntime().availableProcessors() may return value < 2 in container based environment, e.g.
   *          Kubernetes.
   */
  public static final int MAX_NUM_THREADS_PER_QUERY =
      Math.max(1, Math.min(10, Runtime.getRuntime().availableProcessors() / 2));

https://github.com/apache/incubator-pinot/blob/master/pinot-core/src/main/java/org/apache/pinot/core/operator/combine/CombineOperatorUtils.java#[…]8

What’s the best way to confirm that a star tree index is being used for a query?

pr to use java11: https://github.com/apache/incubator-pinot/pull/6424 it's been working for us. We also use

-XX:SoftRefLRUPolicyMSPerMB=0

to fix the issue where soft references are not sufficiently cleaned up in gc.

Hi. We have the following query:

Select backend_id, backend_protocol, backend_name, COUNT(*) FROM backendEntityView WHERE tenant_id = '__default' AND ( backend_id IS NOT NULL AND start_time_millis >= 1626546008022 AND start_time_millis < 1626632408022 ) GROUP BY backend_id, backend_protocol, backend_name ORDER BY PERCENTILETDIGEST99(duration_millis) desc  limit 10000

We have a sorted index on

backend_id

and a range index on

start_time_millis

. What we have observed is that

PERCENTILETDIGEST99

increases the query time by a large amount (more than 300% easily). Is there some way we can optimise this using pre-aggregations (or some other way)?

You could pre-aggregate the percentile tdigest in a column and store it byte serialized

How many docs is the query selecting?

You could also try star tree index, however you might then want to also select the percentile in the aggregation so that star tree index is used to compute it

Hi Mayank,

numDocsScanned

is 658418392.

7611342

is the number of entries scanned in filter.

2633673568

is

numEntriesScannedPostFilter

“You could pre-aggregate the percentile tdigest in a column and store it byte serialized” - How can I do this?

I mean, the docs say that the only supported aggregation rn is SUM

@User has left the channel

Question about server disk size - do server nodes need enough disk space to store all segments? Or will segments get dropped from local disk and re-read from deep storage as needed if the disk gets full?

it needs enough disk space to store all the segments assigned to it

Thanks. So deep storage is just a backup. Is this use case tiered storage is meant to address? We have a AWS/EKS deployment and our cost is driven by server storage (EBS) - it would be ideal to have older data in S3

Tiered storage just moves some segments to a different set of servers, but those servers now need to have enough storage to host these.

Even in the issue that I mention, it is expected that the storage use temporarily bumps up on the servers, and then reclaimed when the segments "age". Pinot does not handle the case of serving data from segments that cannot be stored on servers.

@Priyank Bagrecha has left the channel

Hi Team, would like to recommendations for resource allocation. Right now we use the default configuration of allowing our segments to build off heap and i know that indexes are kept on heap. We want to get some advice on how much memory should we allocate to our severs/broker/controller ? Right now we are using i3.2xlarge. We want to fix our heap/nonheap allocation (currently we are using a % which is non ideal).

Hi, I am facing an issue with offline servers on running heavy queries via presto. I have 6 offline servers each with 32G RAM and I have configured my tables to have 2 RGs with 3 servers each. We have major chunk of our data (some tables of size 29G) in offline servers and that would keep increasing with time. When I run queries, most of the times the server goes down due to OOM or the query gets aborted due to some exception. Can I get some insights on the configuration for heap/non-heap allocation?

Is it NOT recommended to use a primary key that created by

SHA-256

algorithm for the upsert table from the performance perspective? I want to avoid collisions as much as possible.

What are the recommended routing configs (more specifically, the

segmentPrunerTypes

config) for an upsert table? The partition key (say

pk

) in my upsert table is the hashed value of two concatenated id fields (lets say

A

and

B

), and the most common pattern of queries against that table is filtering on column

A

instead of the

pk

.

What’s the recommended memory setup for broker? If a node has 16GB memory, how much memory request of the pod (assume single pod on the node), heap and off heap I should configure?

Hi Team, can i get some advise if our star tree index is configured correctly for this query ? Query:

SELECT start_timestamp_10mins, SUM(online_seconds)/3600.0 AS value FROM m_driver_supply_derived WHERE country_id = 6 AND city_id IN (84, 130, 20, 340, 369, 79, 360, 308, 98, 81, 230, 34, 349, 350, 370, 63, 372, 346, 231, 60, 86, 61, 218, 99, 43, 363, 222, 64, 367, 80, 15, 101, 348, 62, 361, 28, 75, 373, 356, 352, 69, 41, 362, 55, 347, 341, 374, 365, 219, 65, 275, 10, 66, 364, 40, 225, 366, 146, 35, 337, 354, 342, 344, 102, 345, 96, 132, 78, 18, 359, 36, 256, 77, 358, 343, 371, 100, 357, 368, 215, 26, 255, 44, 144, 351, 85, 353, 76) AND 
business_vertical = 'BUSINESS_VERTICAL_TYPE_TRANSPORT'  AND 
granularity = 'GRANULARITY_BUSINESS'  AND 
start_timestamp_10mins >= '2023-08-15 17:00:00.0' AND start_timestamp_10mins < '2023-08-25 17:00:00.0'  GROUP BY start_timestamp_10mins HAVING value >= 0 ORDER BY start_timestamp_10mins ASC LIMIT 10000000

Star Tree Index: We are configuring it in the order of highest cardinality to lowest.

"starTreeIndexConfigs": [
        {
          "dimensionsSplitOrder": [
            "vehicle_type_id",
            "city_id",
            "country_id",
            "vehicle_mode",
            "granularity",
            "start_timestamp_10mins",
            "start_timestamp_1hour",
            "start_timestamp_4hour"
          ],
          "skipStarNodeCreationForDimensions": [],
          "functionColumnPairs": [
            "SUM__online_seconds",
            "SUM__online_count",
            "SUM__in_transit_seconds"
          ],
          "maxLeafRecords": 10000
        }
      ],

@Elon When can we start using lookup function and other functions in passthrough queries?