@User - I am almost positive we do not need all of the inverted indexes.

@User do you remember the soft reference issue that our perf time guy found during the initial days? (if so, do you recall the fix?)

Oh nice, you have a perf person? 😀 Can the join also?

we should avoid fine tuning jvm parameters on a case by case basis

@User Are you saying that changing the

mmap

to

direct

for realtime off-heap allocation improved the GC?

Where are creating these?

Or does everyone use 1 cluster?

Hello, I deployed Pinot in k8s cluster and can see the memory is reported incorrectly actually v high. top command inside the node and docker stats are showing correct usage ~ 2GB. However K8s and prom metrics all are reporting high usage(25G).

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

I’ve got maxLeftRecords set to 10K, I thought that was default - should I increase?

And I don’t see anything about when to use onHeapDictionaryColumns, does that matter at all?

We noticed that offline tables with a lot of segments require a lot of DirectR buffer references - would this indicate that we need to scale up the number of servers? What % of the heap should DirectR buffer references consume before it is recommended to scale up?

I’d heard that the server process has a set (10?) number of threads to handle query requests. Assuming we have very low qps, and want to minimize latency, is there a way to set the number of threads == the number of cores? And are there any other places in the system where similar constraints exist?

Asking because if I add a where clause (that filters out nothing) using a dimension NOT in my dimensionsSplitOrder list, I though that the star tree wouldn’t be used - and the query time is the same for the case without that where clause.

I added two more servers to my cluster, and performance has dropped. One theory is that one or both of these new servers is slower than the previous servers, and thus causing the drop in performance. How can I confirm or refute that theory? Are there Pinot metrics I should be examining?

Is there a way to have an inverted index for a column, but not store the column data? So a pure filter-only field?

Hey good morning. I read some articles about Pinot, and feel Pinot can be a great tool for our real time analytics platform. we currently use snowflake and redshift. I tried it with a simple usecase (63 million records with percentileest, avg aggration) on a single ec2 instance and the performance is amazing. I want to pursue further and have a few questions related with building star tree index for the aggregators. mainly we want to make sure building the star tree indexes takes much shorter than the full cubing.  hope you can help me out: 1. for our percentile aggregation, we only care the values for 10, 25, 50, 75 and 90 percent. is there any way to do the aggregation only for those percentiles? 2. How do i know if a star tree index is built? from the UI “Reload Status” screen, I don’t see anything related with the star tree index 3. currently we are doing very intensive monthly cubing to support realtime analytics (percentile on 12 columns, avg on 12 columns,   approx_cont_distinct on 5 columns). at the end of each month, we are batch feeding about 70 million records. is it possible to build the star tree index in a couple of hours? if so what are the recommended ways to speed up the index building process? some context for our table: 1. 40 dimension columns, 1 time column and 15 metric column 2. we have monthly feed about 70 million records 3. we need  monthly, quarterly and yearly analytics Thanks in advance

I was referring to pre aggregate during indexing in offline

@User perhaps you are looking for a solution being worked on in this issue: https://github.com/apache/pinot/issues/7229

Hey guys, Regarding Realtime Table Memory usage . i had posted a thread on troubleshoot group. https://apache-pinot.slack.com/archives/C011C9JHN7R/p1656747644771759 Can anyone advice on the same?

Hey everyone, I'm trying to run below query but it is erroring out with

upstream request timeout

, when using single month data in filter then it is working. Not sure why error message isn't giving valid reason of failure. Can you guys suggest the right and necessary table config

SELECT
    DISTINCTCOUNT( CASE WHEN data_source = 'web' THEN master_id ELSE 'a' END) as activity_account_count,
	DISTINCTCOUNT( CASE WHEN data_source in ('b2bn', 'b2bn_excluded') THEN master_id ELSE 'a' END) as kw_account_count,
    DISTINCTCOUNT( CASE WHEN data_source = 'fpm' THEN master_id ELSE 'a' END) as fpm_account_count,
    DISTINCTCOUNT( CASE WHEN data_source = 'tpm' THEN master_id ELSE 'a' END) as tpm_account_count,
    DISTINCTCOUNT( CASE WHEN data_source = 'crm' THEN master_id ELSE 'a' END) as crm_account_count,
    DISTINCTCOUNT( CASE WHEN data_source = 'map' THEN master_id ELSE 'a' END) as map_account_count,
    DISTINCTCOUNT( master_id) as all_account_count
FROM
    six_sense_dapm
WHERE
    dt BETWEEN '2022-01-01' AND '2022-08-30'
    AND data_source IN ('web', 'b2bn', 'b2bn_excluded', 'fpm', 'tpm', 'crm', 'map')
    AND product='__all__'

table config :

{
  "OFFLINE": {
    "tableName": "sumologic_dapm_OFFLINE",
    "tableType": "OFFLINE",
    "segmentsConfig": {
      "timeType": "DAYS",
      "schemaName": "sumologic_dapm",
      "replication": "2",
      "segmentPushType": "APPEND",
      "timeColumnName": "dt",
      "allowNullTimeValue": false
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant"
    },
    "tableIndexConfig": {
      "invertedIndexColumns": [
        "data_source",
        "product",
        "source_activity_name"
      ],
      "noDictionaryColumns": [
        "external_id",
        "master_id",
        "secondary_id",
        "source_activity_desc",
        "source_activity_url",
        "source_activity_referrer_url",
        "source_activity_desc",
        "source_activity_url_r",
        "metric_value",
        "source_id"
      ],
      "rangeIndexColumns": [
        "dt"
      ],
      "optimizeDictionaryForMetrics": true,
      "enableDefaultStarTree": true,
      "enableDynamicStarTreeCreation": true,
      "aggregateMetrics": true,
      "nullHandlingEnabled": true,
      "rangeIndexVersion": 2,
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "sortedColumn": [
        "data_source",
        "master_id"
      ],
      "loadMode": "MMAP"
    },
    "metadata": {},
    "isDimTable": false
  }
}

This query does not scale beyond 240 qps {"sql":"select event_time , sum(count) as event_counts from nrt_app_open where year=year(now()) and month=month(now()) and day>=(day(now())-1) and FromDateTime(event_time, 'yyyy-MM-dd HHmmss') > cast((now() - 86400000) as long) group by 1 limit 1000000000","trace":false,"queryOptions":""} Any ideas on how I can optimise this. Cpu and memory of pinot-servers are also under utilized. Thanks in advance.

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?