https://pinot.apache.org/ logo
Join Slack
Powered by
Hi Team. We are seeing high latency for the follow...
# troubleshooting
s
Hi Team. We are seeing high latency for the following query : SELECT cevalDec, vevalDec, DISTINCTCOUNT(subjectId) AS Count FROM ModelScoreVariableTable WHERE ekey = 'ee801a97-aae1-490d-ac7f-d5556060f758' GROUP BY cevalDec, vevalDec ORDER BY cevalDec, vevalDec LIMIT 1000 Can you let us know if we can optimize it? Attaching other information in thread.
Table Schema :
Copy code
{
  "schemaName": "ModelScoreVariableTable",
  "enableColumnBasedNullHandling": false,
  "dimensionFieldSpecs": [
    {
      "name": "subjectId",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "ekey",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "intentName",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "mscoreVar",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "cevalDec",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "vevalDec",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "histBucket",
      "dataType": "LONG",
      "fieldType": "DIMENSION"
    },
    {
      "name": "mscore",
      "dataType": "LONG",
      "fieldType": "DIMENSION"
    },
    {
      "name": "cmscoreVar",
      "dataType": "STRING",
      "fieldType": "DIMENSION"
    },
    {
      "name": "chistBucket",
      "dataType": "LONG",
      "fieldType": "DIMENSION"
    },
    {
      "name": "mScoreRaw",
      "dataType": "DOUBLE",
      "fieldType": "DIMENSION"
    },
    {
      "name": "cmScoreRaw",
      "dataType": "DOUBLE",
      "fieldType": "DIMENSION"
    }
  ],
  "metricFieldSpecs": [
    {
      "name": "count",
      "dataType": "LONG",
      "fieldType": "METRIC"
    }
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "bucket_1hour",
      "dataType": "LONG",
      "fieldType": "DATE_TIME",
      "format": "1:SECONDS:EPOCH",
      "granularity": "1:SECONDS"
    }
  ]
}
Table Config :
Copy code
{
  "REALTIME": {
    "tableName": "ModelScoreVariableTable_REALTIME",
    "tableType": "REALTIME",
    "segmentsConfig": {
      "timeColumnName": "bucket_1hour",
      "timeType": "MILLISECONDS",
      "retentionTimeUnit": "DAYS",
      "retentionTimeValue": "90",
      "minimizeDataMovement": false,
      "replication": "1"
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant"
    },
    "tableIndexConfig": {
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "loadMode": "MMAP",
      "noDictionarySizeRatioThreshold": 0.85,
      "rangeIndexVersion": 2,
      "jsonIndexColumns": [],
      "enableDefaultStarTree": false,
      "starTreeIndexConfigs": [
        {
          "dimensionsSplitOrder": [
            "ekey",
            "mscoreVar",
            "intentName",
            "vevalDec"
          ],
          "functionColumnPairs": [
            "SUM__count"
          ],
          "maxLeafRecords": 100000
        },
        {
          "dimensionsSplitOrder": [
            "ekey",
            "cevalDec",
            "vevalDec",
            "intentName"
          ],
          "functionColumnPairs": [
            "DISTINCTCOUNT__subjectId"
          ],
          "maxLeafRecords": 100000
        },
        {
          "dimensionsSplitOrder": [
            "ekey",
            "cmscoreVar"
          ],
          "functionColumnPairs": [
            "SUM__count"
          ],
          "maxLeafRecords": 100000
        }
      ],
      "enableDynamicStarTreeCreation": false,
      "aggregateMetrics": true,
      "nullHandlingEnabled": false,
      "columnMajorSegmentBuilderEnabled": true,
      "skipSegmentPreprocess": false,
      "optimizeNoDictStatsCollection": false,
      "optimizeDictionary": false,
      "optimizeDictionaryForMetrics": false,
      "optimizeDictionaryType": false,
      "invertedIndexColumns": [
        "ekey",
        "cevalDec",
        "vevalDec"
      ],
      "noDictionaryColumns": [
        "bucket_1hour",
        "count",
        "mScoreRaw",
        "cmScoreRaw"
      ],
      "sortedColumn": [
        "mscoreVar"
      ],
      "bloomFilterColumns": [
        "mscoreVar",
        "ekey"
      ],
      "onHeapDictionaryColumns": [],
      "rangeIndexColumns": [],
      "varLengthDictionaryColumns": [
        "mscoreVar",
        "ekey",
        "cevalDec",
        "cmscoreVar",
        "subjectId",
        "vevalDec",
        "intentName"
      ]
    },
    "metadata": {},
    "ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "bucket_1hour",
          "transformFunction": "toEpochSecondsRounded(comTim, 3600)"
        },
        {
          "columnName": "histBucket",
          "transformFunction": "FLOOR(MULT(mscore, 1000))"
        },
        {
          "columnName": "chistBucket",
          "transformFunction": "FLOOR(MULT(cmScore, 1000))"
        },
        {
          "columnName": "mScoreRaw",
          "transformFunction": "mscore"
        },
        {
          "columnName": "cmScoreRaw",
          "transformFunction": "cmScore"
        },
        {
          "columnName": "cmscoreVar",
          "transformFunction": "cmScoreVar"
        }
      ],
      "continueOnError": false,
      "retryOnSegmentBuildPrecheckFailure": false,
      "rowTimeValueCheck": false,
      "segmentTimeValueCheck": true,
      "ingestionExceptionLogRateLimitPerMin": 5,
      "maxConsecutiveRecordFetchFailuresAllowed": 0,
      "streamIngestionConfig": {
        "streamConfigMaps": [
          {
            "streamType": "kafka",
            "stream.kafka.topic.name": "ModelScoreVariableTopic",
            "stream.kafka.broker.list": "b2-piper-msk-prod-us-east-1.react.ctps.amazon.dev:9002,b4-piper-msk-prod-us-east-1.react.ctps.amazon.dev:9004,b3-piper-msk-prod-us-east-1.react.ctps.amazon.dev:9003",
            "stream.kafka.consumer.type": "lowlevel",
            "stream.kafka.consumer.numThreads": "4",
            "stream.kafka.consumer.prop.auto.offset.reset": "smallest",
            "stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
            "stream.kafka.decoder.class.name": "org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
            "realtime.segment.flush.threshold.time": "1h",
            "realtime.segment.flush.threshold.segment.size": "75M",
            "security.protocol": "SASL_SSL",
            "sasl.mechanism": "SCRAM-SHA-512",
            "group.id": "msk_consumer",
            "ssl.endpoint.identification.algorithm": "",
            "sasl.jaas.config": "org.apache.kafka.common.security.scram.ScramLoginModule required username=\"******\" password=\"*******\";"
          }
        ],
        "columnMajorSegmentBuilderEnabled": true,
        "trackFilteredMessageOffsets": false,
        "pauselessConsumptionEnabled": false,
        "enforceConsumptionInOrder": false,
        "useIdealStateToCalculatePreviousSegment": false,
        "disasterRecoveryMode": "DEFAULT",
        "dropRecordOnPartitionMismatch": false
      }
    },
    "isDimTable": false
  }
}
Does StarTree support native DISTINCTCOUNT aggregation?
x
startree index doesn't support DISTINCTCOUNT try dictinctCountHll
s
DISTINCTCOUNTHLL is not useful for our usecase as we need values with 100% precision. Do we have any other alternative?
x
what's the value range of your DISTINCTCOUNT you need
how many rows after the 
ekey
filtering , e.g.
Copy code
SELECT  COUNT(subjectId) AS Count
FROM ModelScoreVariableTable
WHERE ekey = 'ee801a97-aae1-490d-ac7f-d5556060f758'
s
Value for this can be very large. For the above query - 
225043706
x
what about the cardinality of 
cevalDec, vevalDec
s
This has low cardinality. For a particular ekey, it can have at most 20 distinct values. But for different ekey, the values can be different. Currently there are 32 distinct values.
x
i see, one thing here is if you can tolerate 1% error, 
DISTINCTCOUNTRAWHLLPLUS
may help
Copy code
DISTINCTCOUNTRAWHLLPLUS                 => ~0.81% default RSE
DISTINCTCOUNTRAWCPCSKETCH               => ~1% default-ish
DISTINCTCOUNTRAWTHETASKETCH             => ~1.56% default
DISTINCTCOUNTRAWHLL                     => ~6.5% default
if both 
cevalDec, vevalDec
are low cardinality then it's possible to use inverted index to compute the group by, but thats not in open source
2 Views
Open in Slack
PreviousNext
Powered by