https://linen.dev logo
Join Slack
Powered by
hi all, I'm trying to replace my influxdb 1.x setu...
# issues
s
hi all, I'm trying to replace my influxdb 1.x setup with greptimedb, but I'm getting consistently very slow queries when querying with promql and I can't figure it out. almost any query with promql takes pretty consistently 17-19 seconds to complete, while almost any sql query completes within some 100-300ms typically in my setup I'm storing mainly sensor data (eg. temperature, humidity, etc), inserting that to greptimedb using the influxdb api, and querying from grafana by having greptimedb added as a propetheus data source (as I'm most familiar with promql so I'd prefer using it for my dashboards). I have verified I'm getting pretty much exact same performance characteristics through grafana and through the greptimedb builtin dashboard UI, so it's not a grafana issue it seems to be more or less same with any kind of query, but for example using promql: 
ruuvi_measurements{__database__="ruuvi",__field__="temperature"}
takes rather consistently that 17-19 seconds if I query using a week long timeframe, one day, or even just that last 1 hour of data, and then for example this:
Copy code
SELECT
  "name",
  "dataFormat",
  "mac",
  AVG("temperature") AS avg_temperature
FROM ruuvi."ruuvi_measurements"
WHERE "greptime_timestamp" >= NOW() - INTERVAL '7 day'
GROUP BY "name", "dataFormat", "mac"
ORDER BY "name";
completes within that 100-300ms even with a week worth of data, and unsurprisingly it's pretty much equally fast with only last 1 hour of data. I have not tuned many settings in my config as I figured the defaults are probably quite good starting point. I am using s3 compatible object storage (backblaze b2) as the main storage backend if that makes any difference. I also tried enabling slow query logging if it would reveal any additional insights, but it seems not, as the queries appear in that log as expected is this kind of performance expected with promql? if not, what can I do to troubleshoot the issue further?
Looking at 
TQL ANALYZE VERBOSE (<tel:1777913224|1777913224>, 1777913824, '15s') ruuvi_measurements{__database__="ruuvi",__field__="temperature"}
I can see:
Copy code
PromInstantManipulateExec: range=[1777913224000..1777913824000], lookback=[300000], interval=[15000], time index=[greptime_timestamp] metrics=[output_rows: 779, elapsed_compute: 324us, output_bytes: 41.5KiB, output_batches: 19, num_series: 19, ]
  PromSeriesDivideExec: tags=["dataFormat", "mac", "name"] metrics=[output_rows: 3692, elapsed_compute: 229us, output_bytes: 178.2KiB, output_batches: 7, num_series: 19, ]
    SortExec: expr=[dataFormat@1 ASC, mac@2 ASC, name@3 ASC, greptime_timestamp@4 ASC], preserve_partitioning=[true] metrics=[output_rows: 3692, elapsed_compute: 42us, output_bytes: 0B, output_batches: 0, spill_count: 0, spilled_bytes: 0B, spilled_rows: 0, ]
      RepartitionExec: partitioning=Hash([dataFormat@1, mac@2, name@3], 8), input_partitions=8 metrics=[output_rows: 3692, elapsed_compute: 235us, output_bytes: 1016.5KiB, output_batches: 7, spill_count: 0, spilled_bytes: 0B, spilled_rows: 0, fetch_time: 45.5s, repartition_time: 254us, send_time: 28us, ]
        ProjectionExec: expr=[temperature@3 as temperature, dataFormat@0 as dataFormat, mac@1 as mac, name@2 as name, CAST(greptime_timestamp@4 AS Timestamp(ms)) as greptime_timestamp] metrics=[output_rows: 3692, elapsed_compute: 35us, output_bytes: 277.3KiB, output_batches: 1, expr_0_eval_time: 767ns, expr_1_eval_time: 287ns, expr_2_eval_time: 167ns, expr_3_eval_time: 167ns, expr_4_eval_time: 31us, ]
          FilterExec: __common_expr_3@0 >= 1777912924001 AND __common_expr_3@0 <= 1777913824000, projection=[dataFormat@1, mac@2, name@3, temperature@4, greptime_timestamp@5] metrics=[output_rows: 3692, elapsed_compute: 2.43s, output_bytes: 376.5KiB, output_batches: 1, selectivity: 0, ]
            ProjectionExec: expr=[CAST(greptime_timestamp@27 AS Timestamp(ms)) as __common_expr_3, dataFormat@0 as dataFormat, mac@1 as mac, name@2 as name, temperature@25 as temperature, greptime_timestamp@27 as greptime_timestamp] metrics=[output_rows: 169863709, elapsed_compute: 2.53s, output_bytes: 10.1GiB, output_batches: 167367, expr_0_eval_time: 2.18s, expr_1_eval_time: 21ms, expr_2_eval_time: 17ms, expr_3_eval_time: 15ms, expr_4_eval_time: 19ms, expr_5_eval_time: 18ms, ]
              MergeScanExec: peers=[4398046511104(1024, 0), ], metrics={"partition_0":{"regions":1,"total_poll_duration":"36.170129606s","total_do_get_cost":"20.681118ms","region_metrics":[{"region_id":"4398046511104(1024, 0)","poll_duration":"36.170129606s","do_get_cost":"20.681118ms","total_cost":"45.450498986s"}]}} metrics=[output_rows: 169863709, greptime_exec_read_cost: 0ns, finish_time: 45.5s, first_consume_time: 37ms, ready_time: 21ms, ]
which (if I'm reading this correctly) scans through 10GB of data (my 
write_cache_size
is 10GB, not sure if it's a coincidence here or not), which looks like it's not filtering by the timestamps even if it should be able to. I guess this explains the very consistent ~18s increase in the expected time
e
Yes, looks like a related issue. We’re working on it. You can use SQL as a workaround. For non-prometheus style metrics, SQL should work well and have better performance.
s
Thanks. Although I have no need for nanosecond resolution in the timestamp and I still have all of the original data so I could just recreate the table with millisecond precision, although re-importing all the data would take some 20 hours or so, but then again I am in no hurry with fully switching over. Though I was wondering if using millisecond precision instead of nanosecond would bring any measurable performance or storage size benefits?
e
Though I was wondering if using millisecond precision instead of nanosecond would bring any measurable performance or storage size benefits?
The storage size of the two resolutions should be close. The extra overhead of nanosecond is we need to cast timestamps to milliseconds because PromQL takes millisecond input. This usually isn’t the bottleneck, I think.
j
actually, in our use case, changing the data from microseconds to milliseconds improved some PromQL queries performance from ~2s to ~50ms
e
Thanks for sharing! Maybe this is also related to https://github.com/GreptimeTeam/greptimedb/issues/7913
Open in Slack
PreviousNext
Powered by