How to query promscale data without using count(*)

[toymachiner62]

The timescaledb docs https://docs.timescale.com/latest/tutorials/tutorial-grafana-dashboards on using promscale + grafana all have examples of using count(*) which implies that every entry in the prom_metric tables in timescaledb corresponds to a single value being incremented.

In the real world this doesn't seem to work because the value column of a prom_metric view can have a value greater than 1 as in this example.

I could really use some help on how to write a query to populate a grafana graph where an entry in timescaledb via promscale contains a value that is greater than 1.

[cevian]

Sorry, do you mean using something like sum(value) instead of count(*)? Not sure I understand your question. A list of the available aggregate functions is available here: https://www.postgresql.org/docs/13/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE

[toymachiner62]

Well thinking about this further, I was originally thinking that promscale was reporting the current count to timescaledb every minute, but I might have been doing something else wrong. Let me check blow my data away and look into this again.

[toymachiner62]

Well thinking about this further, I was originally thinking that promscale was reporting the current count to timescaledb every minute, but I might have been doing something else wrong. Let me check blow my data away and look into this again.

Ok getting back to this it seems there is a record every ~15 seconds or so in my timescaledb even if I don't do anything to add to the metrics so a SUM won't work. What I really need is a MAX of every docker instance I have.

For more context here, I have 2 docker containers responding to requests in my backend app that log metrics. They seem to be differentiated by the series_id column Actually i'm not sure I understand what series_id actually is.

You can see that bobsteam has a total of 20 and tomsteam has a total of 16. You can also see that these same numbers are just repeated at different timestamps every ~15 seconds.

I temporarily opened up my promscale container to the internet on a dev env and this promQL query is returning what i'm trying to do. I'm just trying to replicate this by directly querying timescaledb so I don't have to expose my promscale container to the public internet.

sum by (teamName) (
  catalytic_tables_count
)

[Harkishen-Singh]

I was originally thinking that promscale was reporting the current count to timescaledb every minute

Promscale just converts the incoming Prometheus data into SQL queries as it receives the data and sends to Timescaledb. Sorry but I don't understand what Promscale will report.

[toymachiner62]

Do you have any idea what the converted query could be? Or how to replicate that simple promQL query into a correct timescaledb query? At this point the timescale docs https://docs.timescale.com/latest/tutorials/tutorial-grafana-dashboards are not helpful as they don't reflect reality since they always assume COUNT(*).

[Harkishen-Singh]

Sorry for the delay in response.

@toymachiner62 you can mention your promql query here and I can write the SQL form of it.

We can DM over slack if you want more SQL queries. That will be faster than communicating over GitHub issues.

[Harkishen-Singh]

Also, @toymachiner62 the docs link you mentioned are not reference for PromQL data. For PromQL, you need to look into docs of Promscale. This might be helpful. https://github.com/timescale/promscale/blob/master/docs/sql_schema.md

[toymachiner62]

Ok i'm getting back to this so here's what I did.

I completely destroyed by timescale instance and recreated it.

I then hit an endpoint of my app to log that I logged in (catalytic_logins_count). When I hit my promscale endpoint i can see that the value is 1

However, what is confusing to me is that timescale db seems to be inserting a new record every minute

Is this how it's supposed to work? Should a new record be inserted into timescaledb every minute for each of my metrics?

[toymachiner62]

If I follow this is example in your docs https://docs.timescale.com/timescaledb/latest/tutorials/grafana/create-dashboard-and-panel/#visualize-metrics-stored-in-timescaledb with my data which should be this query:

select
	date_trunc('day', time) AS day,
	count(*)
from catalytic_logins_count
group by day
order by day;

I get this result, which is obviously not right as I've only logged in once.

[Harkishen-Singh]

Is this how it's supposed to work? Should a new record be inserted into timescaledb every minute for each of my metrics?

Yes. Prometheus is sending those samples to Promscale and Promscale should ingest them as they are not duplicates. duplicate is one where the series_id and time, both are same. This is not the case here as each sample is different. If your exporter can expose timestamps, then this can be solved by asking prometheus to honor_timestamp: true.

I get this result, which is obviously not right as I've only logged in once.

Why is that not right? you have 16 different datapoints and hence, 16 is the result.

[toymachiner62]

Why is that not right? you have 16 different datapoints and hence, 16 is the result.

In total i've only had one user login so catalytic_logins_count should be 1, but with my query written as it is, the number is constantly going up.

[Harkishen-Singh]

@toymachiner62 that is because of different timestamps. Make sure your exporter exposes tinestamp (which it is not doing at this moment) in these cases as your usecase is time dependent. If your exporter does not expose timestamp, Prometheus attaches a tinestamp and treats it as a new sample and hence a new row is right in Prometheus language. You can see the same thing in Prometheus tsdb as well, since both worlds expect timestamp to be attached in this scenario.

[toymachiner62]

@toymachiner62 that is because of different timestamps. Make sure your exporter exposes tinestamp (which it is not doing at this moment) in these cases as your usecase is time dependent. If your exporter does not expose timestamp, Prometheus attaches a tinestamp and treats it as a new sample and hence a new row is right in Prometheus language. You can see the same thing in Prometheus tsdb as well, since both worlds expect timestamp to be attached in this scenario.

Looks like the default [scrape_config] (https://prometheus.io/docs/prometheus/latest/configuration/configuration/#scrape_config) is honor_timestamps: true. I'm not setting that in my prometheus.yml so timestamps should be honored I would imagine.

I'm just using prom-client to collect my metrics and the docs don't say anything about timestamps in there.

[Harkishen-Singh]

@toymachiner62 please refer to this PR to know how to add timestamp to Prometheus metric in Prometheus go client lib.

prometheus/influxdb_exporter#36

[toymachiner62]

@toymachiner62 please refer to this PR to know how to add timestamp to Prometheus metric in Prometheus go client lib.

prometheus/influxdb_exporter#36

Hmm well this must be a broader issue than just an issue for me if the prom-client doesn't have an option for adding timestamps. I'm not the owner of prom-client and it's a pretty widely used prometheus client written in node and not go.

[Harkishen-Singh]

Ah, I didnt pay attention that you are using node. In node, I can see this in the documentation.

To disable metric timestamps set timestamps to false (You can find the list of metrics that support this feature in test/defaultMetricsTest.js):

const client = require('prom-client');
 
const collectDefaultMetrics = client.collectDefaultMetrics;
 
// Probe every 5th second.
collectDefaultMetrics({ timestamps: false });

[Harkishen-Singh]

@toymachiner62 does that answer help?

[toymachiner62]

No actually it confuses me more.

You had mentioned:

Make sure your exporter exposes tinestamp (which it is not doing at this moment)

but then you also said mentioned to disable timestamps with

collectDefaultMetrics({ timestamps: false });

[Harkishen-Singh]

@toymachiner62, I meant to show you the exact copy. just change that to timestamps: true and all should be fine 😄

[toymachiner62]

Where did you find that example? There are no docs or examples that I saw that the timestamps is supported anymore including this PR siimon/prom-client#333

[Harkishen-Singh]

You can find that here: https://www.npmjs.com/package/prom-client/v/11.5.0

Search timestamps set timestamps to false on that webpage.

[toymachiner62]

You can find that here: https://www.npmjs.com/package/prom-client/v/11.5.0

Search timestamps set timestamps to false on that webpage.

That's an old version. the timestamps flag is no longer supported in newer versions from what I can tell.

[Harkishen-Singh]

Ah, then that's a tough choice. Try doing something like having a label that is updated in the client when you actually increment value. Then you can use sum by some grouping and get the result.

Still, there will be some way to expose timestamps otherwise, that library is not fully Prometheus compatible.

[ramonguiu]

@toymachiner62 sorry for the very late answer.

I think what you were looking for was something like this:

select
	time_bucket('1 day', time) AS day,
	delta(counter_agg(time, value))
from catalytic_logins_count
group by day
order by day;

This would return changes in the value on a daily basis and properly handling counter resets.

Closing this now but feel free to reopen.