Optimizing telegraf with timescaledb

TimescaleDB and PostgreSQL
1

I use telegraf with timescaledb and it works great. I use this schema – telegraf/plugins/outputs/postgresql at v1.30.1 · influxdata/telegraf · GitHub

I was wondering if there are any optimizations that are being overlooked when creating this sort of schema. And also, is it possible to automatically create materialized views for downsampling data. The data will be visualized by Grafana.

2

very good question @Rita_Morgan , and yes, it seems you have room for optimization.

Compression:

ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id')'

It’s not making compress_orderby which is very useful for queries to lookup and scan from the right direction.

ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id', timescaledb.compress_orderby='time desc')'

There’are also other params that are experimental but maybe will improve the speed.

Also, not related to performance but you can use the new interface for create_hypertable

 SELECT create_hypertable({{ .table|quoteLiteral }}, 'time', chunk_time_interval => INTERVAL '7d')

to

SELECT create_hypertable({{ .table|quoteLiteral }},  by_range('time', INTERVAL '7d'))
3

For further timescaledb adoption and especially in the TICK stack, these sort of posts/blogs are needed. This would be a great blob post idea. Considering that telegraf now supports timescaledb natively, I am sure we can optimize it much more. thanks again for your response.

1 Like
4

Thanks for the feedback @Rita_Morgan! We’re going to consider it for sure. I’ll see what we can offer as code/templates and what is good for content. We’re also very open to receive contributions :hugs:

@anatavares would be very happy to coordinate your participation as a guest and we love when community members bring production scenarios :slight_smile:

1 Like
5

I think we can also submit a PR to telegraf, let me know the best options for segmentby/orderby compression settings. I can submit the PR if necessary :slight_smile:

6

They have something like this,

I am not sure why. But this causes a lot of pain if a global_tag is introduced in telegraf.

7

Do you have any explain analyze output that we can take a look of what is hurting the performance?

Maybe we can help and suggest some indices or config tweaks.