https://linen.dev logo
Join Slack
Powered by
Hi Team, We are using GreptimeDB for the first tim...
# general
j
Hi Team, We are using GreptimeDB for the first time to store Prometheus metrics via remote write, and would appreciate guidance on table design and performance tuning. Our environment GreptimeDB v1.2.1 on Kubernetes v1.36.5 3 frontends, 3 datanodes, and 1 metasrv Each datanode: 32-core CPU quota, 96 GiB memory limit, and a 2,000 GiB local SSD-backed PVC XFS on LVM for the data filesystem Automatically created table and data distribution We let GreptimeDB create the physical metric table automatically when data arrived. Below is the relevant DDL, with additional label columns and their primary-key entries omitted for brevity:
Copy code
CREATE TABLE IF NOT EXISTS "greptime_physical_table" (
  "greptime_timestamp" TIMESTAMP(3) NOT NULL,
  "greptime_value" DOUBLE NULL,
  "address" STRING NULL,
  "hostname" STRING NULL,
  "instance" STRING NULL,
  "job" STRING NULL,
  -- Additional label columns omitted
  TIME INDEX ("greptime_timestamp"),
  PRIMARY KEY (
    "address", "hostname", "instance", "job"
    -- Additional label columns omitted
  )
)
ENGINE=metric
WITH (
  'comment' = 'Created on insertion',
  physical_metric_table = 'true'
);
There is no explicit partition clause or TTL in the generated table DDL. Although we have three datanodes, this table has only one data region, located on datanode-0: TypeRegion IDPlacementData region19774029430784datanode-0Internal metric metadata region19774046208000datanode-0COUNT(*) performance We tested:
Copy code
SELECT COUNT(*) FROM prometheus.greptime_physical_table;
It returned approximately 18.2 million rows in more than 16 seconds. With ingestion continuing, a subsequent EXPLAIN ANALYZE showed:
Copy code
MergeScanExec:
  peers=[19774029430784(4604, 0)]
  output_rows: 1
  finish_time: 17.8s
  ready_time: 8.1ms

AggregateExec: mode=Final
  elapsed_compute: 50us

AggregateExec: mode=Partial
  output_rows: 32
  elapsed_compute: 3.9ms

SeqScan:
  partition_count: {
    count: 1,
    mem_ranges: 12,
    files: 10,
    file_ranges: 10
  }
  output_rows: 24464122
  output_bytes: 186.6MiB
  elapsed_poll: 17.6s
  scan_cost: 17.8s
The aggregation is inexpensive; most time is spent scanning on the datanode. For additional context, a later region-statistics snapshot showed 28,485,421 rows, 4 SST files, approximately 48.2 MiB of SST data, and 411.5 MiB of memtable data. These snapshots were taken at different times while ingestion continued. Our questions 1. Is this exact-count performance expected for a metric-engine table? Does it require scanning and merging rows rather than using SST metadata? 2. For our three-datanode cluster, should we pre-create a partitioned physical table instead of relying on automatic creation? What initial DDL and partitioning strategy would you recommend? 3. How can a first-time user choose a reasonable partition design before collecting detailed workload statistics? Can the table be repartitioned or its regions split later? 4. Based on this example, what should we optimize first to improve query performance and make better use of the available CPU, memory, SSDs, and multiple datanodes? We understand that full-table COUNT(*) is not representative of every Prometheus query; we are using this example to understand the execution model and how to design the tables correctly from the start.
p
So a couple things to help that I learned while doing a PoC for a very large telemetry project that we are now moving to production. 1. The ideal is to run 2:1 datanode to frontend. 2. 32c/96g is way to much for what your targeting if you partition correctly. 3. Partitioning is key to large scale data (not saying yours is large scale, but I would do it ahead of time). 4. Identify something unique with a small cardinality that you can split on for partitions. 5. Precreate the 
greptime_physical_table
before you ever start sending metrics. Here is an example of how to split it, in this we have a field we inject via the OTel Collector called shard which is the integer representation of an ip address modulo 16 and stored in the table as a string to allow for 0 padding for uniqueness.
Copy code
CREATE TABLE 'greptime_physical_table' (
    greptime_timestamp TIMESTAMP(9) NOT NULL,
    greptime_value DOUBLE NULL,
    shard STRING NULL,
    TIME INDEX (greptime_timestamp),
    PRIMARY KEY (shard)
  )
    PARTITION ON COLUMNS ("shard") (
    shard <  '016',
    shard >= '016' AND shard <  '032',
    shard >= '032' AND shard <  '048',
    shard >= '048' AND shard <  '064',
    shard >= '064' AND shard <  '080',
    shard >= '080' AND shard <  '096',
    shard >= '096' AND shard <  '112',
    shard >= '112' AND shard <  '128',
    shard >= '128' AND shard <  '144',
    shard >= '144' AND shard <  '160',
    shard >= '160' AND shard <  '176',
    shard >= '176' AND shard <  '192',
    shard >= '192' AND shard <  '208',
    shard >= '208' AND shard <  '224',
    shard >= '224' AND shard <  '240',
    shard >= '240'
  )
  ENGINE = metric
  WITH ("physical_metric_table" = "");
As long as you do this before any data comes in, every table generated off of this for metrics that line up to a single prometheus metric will also get the partitions allowing the spread of data. To note on cluster sizing, this is my current cluster with about 30% of our metrics, logs, and traces sent over the past 4 weeks. Currently Frontend and Datanode pods are 8c/16g, and Metaserver are 4c/8gi.
The G in row count in Grafana stands for billion, so that is 73.8 billion rows for 3.71Tb of compressed data.
Just make sure you also have the greptime monitoring stack running and keep an eye on it as a whole. We learned based on our usage even at this load, we can run our full storage of telemetry with about half the cores per pod, but scaling the number of pods out horizontally to spread traffic, but at the same time learned that less then 8g of ram on frontend and datanodes under heavy load can be problematic at scale.
e
Is this exact-count performance expected for a metric-engine table? Does it require scanning and merging rows rather than using SST metadata?
You can collect a full 
EXPLAIN ANALYZE VERBOSE
result to check the time of each stage in scan and optimize based on that. It needs to merge and deduplicate rows to ensure the count is correct. If you want a metadata count, you can use the statistics in 
information_schema.tables
. docs.greptime.com/user-guide/deployments-administration/performance-tuning/design-table#… You can check the document above for more details. docs.greptime.com/user-guide/deployments-administration/manage-data/repartition And we have document about repartition and some prerequisites to use that. (e.g. MUST enable gc). For partition columns, a custom hashed label from instance would be a choice for beginners. If it isn’t available, you can try other labels. And we’re also working on more flexible ways to write partition rules. You can check our documents and try our skills when you have any questions. docs.greptime.com/faq-and-others/vibecoding#…
j
Thanks @Philip Schwartz @evenyag , I have partitioned physical table by using IP address, and now I encounter poor performance on querying GreptimeDB dictionary, it takes about 3.8s, that's normal?
Copy code
SELECT table_schema, table_name, table_id
FROM information_schema.tables
WHERE table_schema IN ('prometheus', 'prometheus_risingwave')
  AND table_name = 'greptime_physical_table'
ORDER BY table_schema;
explain query
Copy code
EXPLAIN ANALYZE VERBOSE
SELECT *
FROM information_schema.partitions
WHERE table_schema = 'prometheus_risingwave'
  AND table_name = 'greptime_physical_table'
ORDER BY partition_ordinal_position;
Copy code
SortPreservingMergeExec: [partition_ordinal_position@5 ASC NULLS LAST] metrics=[output_rows: 15, elapsed_compute: 93us, output_bytes: 4.4KiB, output_batches: 1, ]
  SortExec: expr=[partition_ordinal_position@5 ASC NULLS LAST], preserve_partitioning=[true] metrics=[output_rows: 15, elapsed_compute: 12us, output_bytes: 4.4KiB, output_batches: 1, spill_count: 0, spilled_bytes: 0B, spilled_rows: 0, ]
    FilterExec: table_schema@1 = prometheus_risingwave AND table_name@2 = greptime_physical_table metrics=[output_rows: 15, elapsed_compute: 127us, output_bytes: 847.7KiB, output_batches: 1, selectivity: 0, ]
      RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1 metrics=[output_rows: 15, elapsed_compute: 6.8us, output_bytes: 847.7KiB, output_batches: 1, spill_count: 0, spilled_bytes: 0B, spilled_rows: 0, fetch_time: 9.50s, repartition_time: 1ns, send_time: 88us, ]
        StreamScanAdapter metrics=[]
e
Hi, You can use the 
greptimedb-performance-diagnosis
 skill. Since we don’t have enough context for your question, you can try that skill with agents like codex. In particular, we don’t have a dictionary to let user query. So I’m not sure what do you mean. Is it a label values query? If your query is a promql, you can use 
TQL ANALYZE VERBOSE
, which we haven’t mentioned in the skill. But you can give this hint to your agent. github.com/GreptimeTeam/docs/…/skills docs.greptime.com/reference/sql/tql#…
j
@evenyag I’ve reposted the question in the channel and also used the GreptimeDB skills. https://greptimedbcommunity.slack.com/archives/C03TY50Q682/p1791190245473639
Open in Slack
PreviousNext
Powered by