Influxdb: SHOW TAG VALUES slower in 1.3

[e-dard]

One of the realities of refactoring the tsdb.Store to accommodate the TSI index in the 1.3 release is that we had to move away from a shared database index (shared across shards) and have a single index per shard.

Unfortunately, because we now have to inspect every shard's index to gather relevant values, the performance of the SHOW TAG VALUES meta query has slowed down quite a bit.

Rough benchmarks indicate it's around 3X slower, depending on the number of shards, measurements and tag values.

For example, with some basic test data where we have one measurement with ~250 tag values in three different tag keys, all across around 10 shards, I get the following:

// 1.2.4
⇒  time influx -database stress -execute 'SHOW TAG VALUES WITH KEY IN ("tag0", "tag1", "tag2")' > /dev/null
influx -database stress -execute   0.01s user 0.01s system 6% cpu 0.179 total


// 1.3.1
⇒  time influx -database stress -execute 'SHOW TAG VALUES WITH KEY IN ("tag0", "tag1", "tag2")'  > /dev/null
influx -database stress -execute   0.01s user 0.01s system 1% cpu 1.276 total

I'm going to see what I can do to optimise it.

[jwilder]

Is this slow with inmem too? Inmem is still a single shared index in 1.3.

[e-dard]

Yes it affects both. inmem acts like a shared index but it's not. It's an index per shard with a shim over them. Therefore with these meta queries we have to iterate over shards and merge results. Prior to this we used to be able to just iterate through the single in-memory index.

[pmagos00]

Guys, this issue is kicking my ass ... cause we use grafana templates ... and a lot of SHOW TAG sentences, please fix soon.

[e-dard]

@pmagos00 I have a general fix for this in #8660. We should be putting it into a 1.3.2 release at some point soon.

[ghost]

Same for me, since i have migrate to 1.3.6 instead 1.2.4.
SHOW TAG VALUES ... WHERE ... is very long : 30s for my measure instead 2 or 3s before.

[ghost]

Hi,
I resolved it by export, destroy and import data with influx_inspect tool.
The procedure to migrate from 1.2 to 1.3 in tsi1 is not good for me because i think backup keep old version of index engine.

is it right?

Best regard
Arnaud

[Arovix]

I'm having the same issue as @dosy07 with SHOW TAG VALUES ... WHERE taking minutes to return in v1.3.5. Like @pmagos00 I'm using this for Grafana templates and it breaks those dashboards when the query takes 2-3m to come back instead of seconds.

The templates that are having trouble are doing something like this:
$Team: SHOW TAG VALUES WITH KEY = "team"
$Subteam: SHOW TAG VALUES WITH KEY = "subteam" WHERE "subteam" =~ /$Team/

The first query returns almost instantly, but the second takes 2-3 minutes.

[e-dard]

@Arovix when you use the second approach, with the regex, every tag key's value for the key subteam has to be compared against the /$Team/ regex. If you have lots of values for that key, that could be expensive.

Could you run the following query to determine how many tag values you have for the subteam key?

$ influx -database mydb -execute 'SHOW TAG VALUES WITH KEY = "subteam"' | wc -l

The idea there is to count the number of lines returned to get a general idea of the number of values for the subteam key. Feel free to achieve that count a different way if it's more practical.

After that, would you be able to provide some profile information? It's pretty straightforward. First get the following command lined up:

curl -o profiles.tar.gz "http://localhost:8086/debug/pprof/all?cpu=true"

Execute the SHOW TAG VALUES command that take 2-3m. Wait a few seconds for it to get going and then enter the curl command above. The curl command will hang for 30s while it gathers the information, which will be saved to the profiles.tar.gz archive.

If you could attach that archive or email it to me (my first name @influxdb.com) that would be great.

[Arovix]

For comparison I have a dev server using InfluxDB v1.0.0 that is collecting the same information from a slightly different source. This is also used by our dev Grafana server, which uses these queries as well and doesn't have issues.

Query 1: SHOW TAG VALUES WITH KEY = "subteam"   
Query 2: SHOW TAG VALUES WITH KEY = "subteam" WHERE "subteam" =~ /teamname/

           v1.3.5      v1.0.0
Query 1
wc -l      191911     116142 
Time       0:01.94    0:00.90
Query 2
wc -l        812        707
Time       2:20.01    0:03.81

Just comparing to 1.0.0 because I already had it setup.

When Googling this issue I found this ticket, and a similar post here.

[Arovix]

The linked issue provided a workaround that worked for my use case, so I'm including it below in case Google brings you here. If you're trying to use this type of query for Grafana templates, and you can't upgrade the InfluxDB version for whatever reason, you might try this.

Ideally the query in my previous comment could be used to find matching key values across all metrics, but if you know that the values are the same for all the metrics then you only need to search one of them.

The regex comparison against multiple metrics seems to be the part that's broken in 1.3 and newer (In that it take multiple minutes to return). Using regex comparison against a single metric still has the same performance as older versions. e.g. 'SHOW TAG VALUES FROM "metricName" WITH KEY = "subteam" WHERE "subteam" =~ /teamname/' still returns almost instantly in 1.3.

Obviously comparing the regex against fewer things will always make it faster, but this took the query time from 4min+ to ~2sec on my data for the worst performing query. (The same 4min+ query takes 3-4sec in Influx 1.0)

Note that this seems to be less effective in 1.5+ (The linked issue #9683)