Hey, i’m testing pinot with trino on the last few days and i see something weird in the metrics (i’m running pinot 0.9.1 and trino 366 trino configured with

pinot.max-rows-per-split-for-segment-queries=1000000
pinot.request-timeout=1m

) as you can see, “broker jvm used” graph continues to rise and fall even when there are no requests to the server at all, and this did not stop until I deleted the pods The same thing happened to the server In addition, Trino is almost unusable Simple query like

select * from bi_test_table where page_url like '%google%' and epoch_ts >= 1639440000
  AND epoch_ts < 1640044800

get a timeout after a minute and sometimes crash the servers Where am I wrong?

Hi @Stav Gayer looks like a slow query, what's your table config? Which column is sorted, and do you have a range index?

also, try this query directly on Pinot and see if there is any difference in latency

my table configured like this at the moment:

{
  "OFFLINE": {
    "tableName": "bi_test_table_OFFLINE",
    "tableType": "OFFLINE",
    "segmentsConfig": {
      "timeType": "MILLISECONDS",
      "schemaName": "bi_test_table",
      "retentionTimeUnit": "DAYS",
      "retentionTimeValue": "365",
      "segmentPushType": "REFRESH",
      "replication": "1",
      "timeColumnName": "epoch_ts",
      "allowNullTimeValue": false
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant"
    },
    "tableIndexConfig": {
      "rangeIndexVersion": 1,
      "jsonIndexColumns": [
        "json_array_column"
      ],
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "loadMode": "MMAP",
      "invertedIndexColumns": [
        "event_type_id"
      ],
      "noDictionaryColumns": [
        "json_array_column"
      ],
      "enableDefaultStarTree": false,
      "enableDynamicStarTreeCreation": false,
      "aggregateMetrics": false,
      "nullHandlingEnabled": false
    },
    "metadata": {
      "customConfigs": {}
    },
    "ingestionConfig": {
      "batchIngestionConfig": {
        "segmentIngestionType": "REFRESH",
        "segmentIngestionFrequency": "HOURLY"
      },
      "transformConfigs": [
        {
          "columnName": "json_array_column",
          "transformFunction": "jsonFormat(\"array_column\")"
        }
      ]
    },
    "isDimTable": false
  }
}

try adding

"rangeIndexVersion":2,
"rangeIndexColumns": ["epoch_ts"]

to tableIndexConfig

i set the epoch_ts as sortedColumn and ran this query directly on Pinot

select count(*) from consolidated where REGEXP_LIKE(page_url ,'/google/') and epoch_ts >= 1639440000
  AND epoch_ts < 1640044800

and got this error

[
  {
    "message": "2 servers [10.20.155.230_O, 10.20.141.241_O] not responded",
    "errorCode": 427
  }
]

( i got 4 pinot servers)

You'll need to add an FST index because the like predicate will be doing a scan

ok I’ll try, I did not see it in the docs

it's very new

@Atri Sharma ^^

The default will be Lucene

if it doesn't we can dig into it more, look at your setup and data a bit

Just specify "type" : native in that box if you want native

still got the same error, same result with simple “like”

select count(*) from bi_test_table where page_url LIKE '%google%' and epoch_ts >= 1639440000
  AND epoch_ts < 1640044800

But it’s not just in this query, I get timeouts all the time with all kinds of queries

ok, what's your hardware setup (cpus, available RAM, number of VMs) and JVM args?

@Stav Gayer are you sure the index got created?

server:

replicaCount: 4
resources:
  requests:
    cpu: 3
    memory: 28G
  limits:
    cpu: 3
    memory: 28G
jvmOpts: "-Xms1G -Xmx14G"

running on r5.xlarge

Can you paste the config you used?

Yeah, curl -X PUT “http://localhost:9001/tableConfigs/bi_test_table?reload=true” -H “accept: application/json” -H “Content-Type: application/json” -d with this json(I hid some of the columns)

{
  "tableName": "bi_test_table",
  "schema": {
    "schemaName": "bi_test_table",
    "dimensionFieldSpecs": [
      // More fields
      {
        "name": "page_url",
        "dataType": "STRING"
      }
      // More fields
    ],
    "metricFieldSpecs": [
      // More fields
    ],
    "dateTimeFieldSpecs": [
      {
        "name": "epoch_ts",
        "dataType": "LONG",
        "format": "1:SECONDS:EPOCH",
        "granularity": "1:MINUTES"
      }
    ]
  },
  "offline": {
    "tableName": "bi_test_table_OFFLINE",
    "tableType": "OFFLINE",
    "segmentsConfig": {
      "timeType": "MILLISECONDS",
      "schemaName": "consolidated",
      "retentionTimeUnit": "DAYS",
      "retentionTimeValue": "365",
      "segmentPushType": "REFRESH",
      "replication": "1",
      "timeColumnName": "epoch_ts",
      "allowNullTimeValue": false
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant"
    },
    "tableIndexConfig": {
      "rangeIndexVersion": 2,
      "jsonIndexColumns": [
        "json_array_column"
      ],
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "sortedColumn": [
        "epoch_ts"
      ],
      "loadMode": "MMAP",
      "invertedIndexColumns": [
        "event_type_id"
      ],
      "noDictionaryColumns": [
        "json_array_column"
      ],
      "fstIndexColumns" : ["page_url"],
      "enableDefaultStarTree": false,
      "enableDynamicStarTreeCreation": false,
      "aggregateMetrics": false,
      "nullHandlingEnabled": false
    },
    "metadata": {
      "customConfigs": {}
    },
    "ingestionConfig": {
      "batchIngestionConfig": {
        "segmentIngestionType": "REFRESH",
        "segmentIngestionFrequency": "HOURLY"
      },
      "transformConfigs": [
         {
          "columnName": "json_array_column",
          "transformFunction": "jsonFormat(\"array_column\")"
        }
      ]
    },
    "isDimTable": false
  }
}

after making config changes did you reload the data? Old segments won't automatically get updates with an FST index.

Yes btw, currently i’m creating segment for each hour and it means that i have a lot of small segments (between 5mb to 150mb) i’m working on switching to daily segments, and every hour ill just update the segment, this way I’ll have fewer segments I wonder if this is why a lot of the queries get timeouts? because currently I already have 1104 segments for almost 2 months of data (my final goal is to be able to query 1year of data quickly)

possibly, the query will use a thread per segment until the query threadpool is saturated

Isn't the segment size too small? Have we tried a larger segment here?

yes, the segments are too small and rolling them up should help

@Atri Sharma Im working on switching to larger segments, but yes according to the docs it might be an issue that still does not explain the strange metrics and the server crashes, I probably missing something with the current setup

were you running queries during the profile? All I can see is the prometheus agent doing stuff

Server crashes? I missed that part

you have 26GB heap available, use 14 of it for heap, the JVM will use another 14 for direct bytebuffers, then the JVM needs some space for native memory (G1 RSets etc.) and then the OS needs some memory too, so I'd say the box is a little small for a 14GB heap

@Stav Gayer A couple of questions:

1. What's the smallest granularity you will query the data in? If something like hours, perhaps that's how you want to store the time (will reduce cardinality).
2. What's the broker response and metadata response for the query: select count(*) from bi_test_table where epoch_ts between 1639440000 AND  1640044800
3. Typically, you don't want to sort on time column, it is naturally in order (if events are chronological), you may pick another column for sorting (won't help this query, but might with others).