Performance/Behavior Issue: Querying OpenTSDB to build a table of hosts & metrics

[Bean Brother]

I am currently working on a project where we are using OpenTSDB. Things are working well except for query performance. What I am trying to do is get the most recent data points of all metrics for all hosts.

For example, I need to create a table that resembles the following:

testhost1:
df.1kblock.total
df.1kblocks.free
proc.meminfo.total
proc.meminfo.free
proc.loadavg.1min
proc.loadavg.5min
proc.loadavg.15min
...

testhost2:
df.1kblock.total
df.1kblocks.free
proc.meminfo.total
proc.meminfo.free
proc.loadavg.1min
proc.loadavg.5min
proc.loadavg.15min
...

As you can see from this example, I need to do a query for each metric for a host and then repeat for each host. Each query would be something like:

"/q?start=1m-ago&m=sum:proc.loadavg.1min{host=testhost1}&ascii"

This returns way more than 1 min of data....it's really like several thousand data points and is variable depending on how much data has been collected. I know the explanation of the behavior is documented. I basically just use the very last data point returned and ignore the remainder (which is several thousands of data points).

So lets say there are 200 metrics and 100 hosts. This equates to 20,000 queries. Each query returns lets say for example 4,000 data points. This equates to 80,000,000 (80 million) data points. I could reduce the number of queries by querying one metric and specifying the tag host=*:

"/q?start=1m-ago&m=sum:proc.loadavg.1min{host=*}&ascii"

Now this would reduce the number of queries by a factor of 100...down to 200 (from 20,000) but each query will return data for each host...so each query will return 100 times more data. The amount of data returned will still be the same ~80 million points. The real problem is that each query is returning way too much data.

The real way to improve performance in this scenario would be to return 1 point for each query...so 20,000 data points and not 80 million.

Does anyone have ideas on how I could add functionality to support a new query to OpenTSDB. I understand this may require adding/modifying code on OpenTSDB to handle another type of query to basically return the most recent data point. Could anyone point me in the right direction or has anyone made query code changes?

I'm also open to other ideas on handling this scenario. Thanks for any help!

[ManOLamancha]

That's definitely the way to go, issue one query per metric and split the output up by host instead of querying by metric AND host.
 

Now this would reduce the number of queries by a factor of 100...down to 200 (from 20,000) but each query will return data for each host...so each query will return 100 times more data. The amount of data returned will still be the same ~80 million points. The real problem is that each query is returning way too much data.

The real way to improve performance in this scenario would be to return 1 point for each query...so 20,000 data points and not 80 million.

The reason for returning many points is that for graphing and rate calculations we need at least one point before the requested start time and ideally one after. Since the data is stored in hourly rows within HBase, the query code grabs 2 hours of data before the start time requested and up to 30 minutes of data after. This is because we don't know where the earliest data point will be.

Also, since the data is stored in hourly buckets, if we tweaked the queries to return *just* the start and end time requested (e.g. 1m-ago as per your example) we would still fetch all of the data points from that row (due to the nature of HBase and Scanners). That said, you will probably want to use the 2.0.0 RC1 release and the /api/query endpoint. It will filter out all of the data points you don't want and return only the data within the time frame requested (e.g. all the data points in the last minute per your example).
 

Does anyone have ideas on how I could add functionality to support a new query to OpenTSDB. I understand this may require adding/modifying code on OpenTSDB to handle another type of query to basically return the most recent data point. Could anyone point me in the right direction or has anyone made query code changes?

A member of my team actually has the same requirement, e.g. the ability to quickly get the last data point, so I've some code I'm cooking up that should be ready later this month for testing that will provide an optimization. It'll be based off the 2.0 branch though.

[Carsten Saathoff]

Am Freitag, 9. August 2013 23:10:34 UTC+2 schrieb ManOLamancha:

Does anyone have ideas on how I could add functionality to support a new query to OpenTSDB. I understand this may require adding/modifying code on OpenTSDB to handle another type of query to basically return the most recent data point. Could anyone point me in the right direction or has anyone made query code changes?

A member of my team actually has the same requirement, e.g. the ability to quickly get the last data point, so I've some code I'm cooking up that should be ready later this month for testing that will provide an optimization. It'll be based off the 2.0 branch though.

Will this be configurable at query time, e.g., to return the last 10 points? We have a similar requirement to be able to retrieve the recent history of a time series, and this would be very handy :) 

[ManOLamancha]

A "last n" query would be much more difficult since we don't track the interval between writes and OpenTSDB doesn't assume that points are written with a uniform interval. Once we have a dirty flag table it would be easier to process last n, but for now the the best we can do is to fetch the data for the last 5m or some interval specified in the query.

[Bean Brother]

A member of my team actually has the same requirement, e.g. the ability to quickly get the last data point, so I've some code I'm cooking up that should be ready later this month for testing that will provide an optimization. It'll be based off the 2.0 branch though.

This would be really awesome and exactly what I'm looking for. Is there any way you could port your code over to version 1.1.0? This would really make a tremendous difference in performance for us, and make things a lot cleaner than some other workaround solutions!

Bean

[ManOLamancha]

On Tuesday, August 13, 2013 4:17:06 PM UTC-4, Bean Brother wrote:

A member of my team actually has the same requirement, e.g. the ability to quickly get the last data point, so I've some code I'm cooking up that should be ready later this month for testing that will provide an optimization. It'll be based off the 2.0 branch though.

This would be really awesome and exactly what I'm looking for. Is there any way you could port your code over to version 1.1.0? This would really make a tremendous difference in performance for us, and make things a lot cleaner than some other workaround solutions!

Unfortunately I can't because it depends on the TSUID incrementing fields. Is there anything preventing you from moving to 2.0? It's fully compatible.

[Bean Brother]

Which packages will need to be updated besides OpenTSDB? Any new package dependencies that weren't there before? Any changes on sending data to OpenTSDB...will TCollector need to be updated? Are queries to get data the same?

Sorry for all the questions. I haven't had a chance to look into what's in 2.0 and what's required for the upgrade. Sorry for the questions.

Bean

[Bean Brother]

A member of my team actually has the same requirement, e.g. the ability to quickly get the last data point, so I've some code I'm cooking up that should be ready later this month for testing that will provide an optimization. It'll be based off the 2.0 branch though.

Just to check, would this allow getting the entire last data point for a particular metric along with associated timestamp and tags? If so great! Can't wait.

[ManOLamancha]

On Tuesday, August 13, 2013 6:18:42 PM UTC-4, Bean Brother wrote:

Which packages will need to be updated besides OpenTSDB? Any new package dependencies that weren't there before? Any changes on sending data to OpenTSDB...will TCollector need to be updated? Are queries to get data the same?

Sorry for all the questions. I haven't had a chance to look into what's in 2.0 and what's required for the upgrade. Sorry for the questions.

No problem, it's almost literally a plug and play operation. You don't have to update HBase or TCollector or any tools that you've already integrated. Just compile the newest code, add the "tsdb-tree" and "tsdb-meta" tables if you want to use the newest features, create a config file, and turn it up in place of your old TSDs (after testing of course). The only new dependency is Jackson but it's couple of JARs that will be downloaded when you compile. No data modifications necessary or anything like that. There is a new HTTP API so we recommend folks start migrating tools to use it instead of the old style API since it's been deprecated, but you can do that at your leisure.

[ManOLamancha]

On Tuesday, August 13, 2013 6:32:42 PM UTC-4, Bean Brother wrote:

A member of my team actually has the same requirement, e.g. the ability to quickly get the last data point, so I've some code I'm cooking up that should be ready later this month for testing that will provide an optimization. It'll be based off the 2.0 branch though.

Just to check, would this allow getting the entire last data point for a particular metric along with associated timestamp and tags? If so great! Can't wait.

Well I could make it do aggregation for all time series belonging to a metric if you like. I was just going to have a query like "/api/query/last?tsuids=010101,020202,030303"

[Bean Brother]

Wouldn't the query itself define the aggregation? For example if you did a query for a metric with sum and no host tag...would mean to aggregate all the data from the different hosts.

I'm not sure I understand why the "last?tsuids=..." is needed?

Thanks for your support!

[ManOLamancha]

On Thursday, August 15, 2013 4:48:52 PM UTC-4, Bean Brother wrote:

Well I could make it do aggregation for all time series belonging to a metric if you like. I was just going to have a query like "/api/query/last?tsuids=010101,020202,030303"

Wouldn't the query itself define the aggregation? For example if you did a query for a metric with sum and no host tag...would mean to aggregate all the data from the different hosts.

I'm not sure I understand why the "last?tsuids=..." is needed?

Well for this case I'm not sure that aggregation actually makes any sense. The reason for passing in TSUIDs is that it you would generally only want the last data point for a specific time series (or multiple specific time series). There are two problems with aggregating across time series:

1) The last data point for any given series can be anywhere in time and not aligned. E.g. for the same metric, time series A may have it's last point within the current hour but time series B's last point may be 2 days ago. It doesn't make much sense to aggregate these together.
2) The last data point query should only fetch the last data point. If we want to aggregate, we would need at least two points in order to perform interpolation which would mean we lose the optimization of only fetching data for a single row. Given that the points may be splayed widely in time, we would have to scan for much more data. And how do we pick a time to return?

So the output of the query would be something like:

[{
  tsuidA
  metricA
  tagsA
  time
  value
},{
  tsuidB
  metricB
  tagsB
  time
  value
}...]

It would still be possible to fetch all last values for a given metric or tag combination, but it will only be able to return individual points for each series.

[Bean Brother]

Ok this would do what I need. I don't really need the aggregation, I was just asking to learn about the enhancement you are making. Why would be need the TSUIDS? A normal query doesn't require that.


I also have a question about the additional jars that will be needed:
1. In our environment, we don't have access to internet. We will need to package those jars and provide them as part of installation. Is there an offline installation method to do this?
2. The license of the jars is of concern. We can't redistribute jars if the license doesn't allow.

Please let us know when this will be available as it will be much needed! Thank you.

[ManOLamancha]

On Wednesday, August 21, 2013 11:18:27 AM UTC-4, Bean Brother wrote:

Ok this would do what I need. I don't really need the aggregation, I was just asking to learn about the enhancement you are making. Why would be need the TSUIDS? A normal query doesn't require that.

Turns out it was actually easy to implement without specifying the TSUIDs (though that's still an option). Check out https://github.com/manolama/opentsdb/tree/next_lastdp for working code. I'll post documentation shortly and clean up the code a bit but you can get the last points by accessing /api/query/last?timeseries=<metric>[{tagname=value[,tagname2=value2...]}]

I also have a question about the additional jars that will be needed:
1. In our environment, we don't have access to internet. We will need to package those jars and provide them as part of installation. Is there an offline installation method to do this?

If you're running Debian you can build a package on a machine with Internet access via "sh build.sh debian" and distribute that. Or you can run "sh build.sh dist" to create a tarball that will include the dependencies and you can distribute that to your machines.
 

2. The license of the jars is of concern. We can't redistribute jars if the license doesn't allow.

I'm not sure what all the licenses are, but since we're hosting copies on the Google code site I bet they're all compatible. Tsuna would have a better idea.

[Bean Brother]

That's great news! Will you do a patch or release for this fix? If so when will that be available? In our environment, it is preferred to work with some form of stable release package.

[ManOLamancha]

On Wednesday, August 21, 2013 2:32:25 PM UTC-4, Bean Brother wrote:

That's great news! Will you do a patch or release for this fix? If so when will that be available? In our environment, it is preferred to work with some form of stable release package.

Yep, I'll clean it up and push it to another branch in the main repo. "next" is still for 2.0 and since this is a new feature it'll be slated for 2.1. We'll likely migrate "next" to "master" in October as an official release, then move this into "next" for 2.1.

[Bean Brother]

I am at the point that I want to use this functionality. Did this function make it into 2.0 or is this a patch that I can apply to RC2 and build? Could you provide instructions.

Thanks!

[ManOLamancha]

On Friday, January 17, 2014 10:56:13 AM UTC-5, Bean Brother wrote:

I am at the point that I want to use this functionality. Did this function make it into 2.0 or is this a patch that I can apply to RC2 and build? Could you provide instructions.

Not quite yet, sorry, I'm getting there though :) 

[Bean Brother]

On Wednesday, August 21, 2013 12:36:42 PM UTC-4, ManOLamancha wrote:

Turns out it was actually easy to implement without specifying the TSUIDs (though that's still an option). Check out https://github.com/manolama/opentsdb/tree/next_lastdp for working code. I'll post documentation shortly and clean up the code a bit but you can get the last points by accessing /api/query/last?timeseries=<metric>[{tagname=value[,tagname2=value2...]}]

You had posted a link to the code. If I wanted to use this now, do I just use this version instead of the RC2 version? Or can I apply a patch to RC2?

Sorry to bug you but we're at a critical point and were hoping to use this functionality to retrieve last data point.

[Bean Brother]

ManOLamancha,
Is there any way that you could post a diff or patch of the code that was changed to make this functionality work? I will go ahead and try to apply it to the latest RC2. We need this functionality ;) Thanks.

[ManOLamancha]

On Wednesday, January 22, 2014 11:35:13 AM UTC-5, Bean Brother wrote:

ManOLamancha,
Is there any way that you could post a diff or patch of the code that was changed to make this functionality work? I will go ahead and try to apply it to the latest RC2. We need this functionality ;) Thanks.

Hiya Bean, sorry I thought you were referring to the TSUID map. The last data point code is actually in the main repo now and it's already synced with all of the latest 2.0 patches. Just fetch the next_lastdp branch from the main repo. 

[ManOLamancha]

On Wednesday, January 22, 2014 12:01:23 PM UTC-5, ManOLamancha wrote:

Hiya Bean, sorry I thought you were referring to the TSUID map. The last data point code is actually in the main repo now and it's already synced with all of the latest 2.0 patches. Just fetch the next_lastdp branch from the main repo. 

And docs are at: http://opentsdb.net/docs/build/html/api_http/query/last.html 

[nwhitehead]

(Reposted since image was pasted as raw base64..... grrr....)

I have developed an OpenTSDB Search Plugin that maintains a SQL based catalog of metrics (and annotations). It will use H2 (memory or disk based), Oracle or Postgres to store the catalog.  H2 is the most up to date, but Oracle and Postgres are not far behind. Implementing new DB types is fairly straightforward as long as it's a more-or-less standard SQL syntax. There are also some stored procedure requirements, but they're only required if you enable reverse-meta-synch, which records changes you make in the DB (such as descriptions, notes, custom values, units etc.) are synched back to HBase. 
This is a slightly out of date image of the catalog structure: (TSD_FQN is now called TSD_TSUID to make it consistent)

So for a list of all metric names and TSUIDs for all hosts, you could query:

    SELECT TSUID, FQN FROM TSD_FQN F WHERE EXISTS (
      SELECT * FROM  TSD_FQN_TAGPAIR FT, TSD_TAGPAIR P, TSD_TAGK K
      WHERE F.FQNID = FT.FQNID
      AND   FT.UID = P.UID
      AND P.TAGK = K.UID
      AND K.NAME = 'host'
   )

Based on the number of metric names and hosts you have, I think you will get pretty decent response time. The plugin itself dispatches all callbacks from core OpenTSDB through a disruptor ring-buffer so the penalty in calling the plugin is fairly small, although to keep the catalog up to date  it would require you have real-time-meta enabled.Alternatively, there is a JMX Op exposed to trigger a flush of all TSUIDs if you wanted to periodically refresh the catalog. 

If you have any interest, and you could give me a more detailed idea of  the number of metrics you're talking about, I can run a test to get the general elapsed times for these operations. 

The code is here:  https://github.com/nickman/tsdb-plugins

This plugin would require tsdb-plugins-core and tsdb-sqlcatalog.

Cheers.

//Nicholas

[Bean Brother]

Thanks ManOLamancha. I tried it using the next_lastdp branch and I can't seem to get it working. Here is my examples:

This query:
https://127.0.0.1:4242/api/query?start=1m-ago&m=sum:proc.loadavg.1min{host=pmserver}

returns json message:
[{"metric":"proc.loadavg.1min","tags":{"host":"pmserver"},"aggregateTags":[],"dps":{"1390514504":0.019999999552965164}}]

----------------
The last data point query looks like:
https://127.0.0.1:4242/api/query/last?timeseries=proc.loadavg.1min{host=pmserver}

and returns blank json message:
[]

What am I doing wrong? I also noticed the documentation and it states that metadata must be created. Is this not created by default? Also there are 4 options, which one needs to be used?
tsd.core.meta.enable_realtime_uid
tsd.core.meta.enable_tsuid_tracking
tsd.core.meta.enable_tsuid_incrementing
tsd.core.meta.enable_realtime_ts

Bean

On Wednesday, January 22, 2014 12:01:23 PM UTC-5, ManOLamancha wrote:

[ManOLamancha]

On Thursday, January 23, 2014 5:15:36 PM UTC-5, Bean Brother wrote:

Thanks ManOLamancha. I tried it using the next_lastdp branch and I can't seem to get it working. Here is my examples:

This query:
https://127.0.0.1:4242/api/query?start=1m-ago&m=sum:proc.loadavg.1min{host=pmserver}

returns json message:
[{"metric":"proc.loadavg.1min","tags":{"host":"pmserver"},"aggregateTags":[],"dps":{"1390514504":0.019999999552965164}}]

----------------
The last data point query looks like:
https://127.0.0.1:4242/api/query/last?timeseries=proc.loadavg.1min{host=pmserver}

Try it with "&back_scan=1" 

and returns blank json message:
[]

What am I doing wrong? I also noticed the documentation and it states that metadata must be created. Is this not created by default? Also there are 4 options, which one needs to be used?
tsd.core.meta.enable_realtime_uid
tsd.core.meta.enable_tsuid_tracking
tsd.core.meta.enable_tsuid_incrementing
tsd.core.meta.enable_realtime_ts

Try  "tsd.core.meta.enable_tsuid_incrementing" and "tsd.core.meta.enable_realtime_ts" first. That will enable the TSUID tracking.