Merge MySQL metrics inside 1 metric with label

[dupondje]

Feature Request

Proposal:

It might be a good idea to consolidate some MySQL metrics inside 1 metric with labels.

Current behavior:

For example the mysql_com_xxx metric is returned 158 times with different values.
mysql_com_alter_db
mysql_com_alter_db_upgrade
mysql_com_alter_event
mysql_com_alter_function
mysql_com_alter_instance
mysql_com_alter_procedure
mysql_com_alter_server
mysql_com_alter_table
mysql_com_alter_tablespace
mysql_com_alter_user
mysql_com_analyze
mysql_com_assign_to_keycache
....

Desired behavior:

It might be better to change the metric to:
mysql_com{command="alter_db"}
mysql_com{command="analyze"}
etc.

Use case:

If you want to use those metrics inside Grafana with a Prometheus backend, you need to write 158 queries! This is not very flexible.
Also things like topk etc won't work.

I could create a pull request for this if we would like to get this implemented :)
Might be nice to have this optional via config variable? Then people can choose to keep existing output.

[danielnelson]

I guess it depends on what query you want to run, with InfluxQL your proposed layout wouldn't be great for math across fields, or Kapacitor alerts that need multiple field values. In the case where you are using Flux you can do your query today. I'll take your word that it can't be done in PromQL, since I don't know it very well, and also I agree it can't work with topk to rank commands.

However, I'm quite reluctant to change these metrics right now due to the disruption is causes. We have an idea to provide processors that can do this transformation in general though, so I wrote them up today and opened #5991. Could you take a look and let me know what you think, could this work for you?

[dupondje]

That pivot would do the trick indeed.
But it might be nice to add regex to that. Cause there are for example 158 mysql_com_xx metrics. (And possibly there will be others in other versions of MySQL), so it would be way more flexible if you could apply some regex.

[[processors.pivot]]
tag_key = "mysql_com_(.*)"
value_key = "{$1}"

For example :)

[dupondje]

Also I don't know how big the customerbase is that uses Prometheus.
But we could add combine_metrics = true/false option for example in the config. So its flexible for users to enable this if they are using prometheus.
This would make all users use the same style, and dashboard sharing etc is more straightforward.

Anyway, your call :)

[danielnelson]

In your case you will want to use the unpivot variation. It will unpivot every field that is added to the processor, I don't think you will need a regex but you can combine this with the metric filtering.

Since the line protocol is like this:

mysql,server=debian-stretch-mariadb.virt:3306 com_alter_server=0i,com_alter_table=0i,com_alter_tablespace=0i,com_analyze=0i,com_assign_to_keycache=0i,com_begin=0i,com_binlog=0i,com_call_procedure=0i,com_change_db=0i,com_change_master=0i,com_check=0i,com_checksum=0i,com_commit=0i,com_compound_sql=0i,com_create_db=0i,com_create_event=0i,com_create_function=0i,com_create_index=0i,com_create_procedure=0i,com_create_role=0i 1560499336000000000

You should be able to do:

[[processors.unpivot]]
  namepass = ["mysql"]
  fieldpass = ["com_*"]
  tag_key = "command"
  value_key = "value"

The output will be like this:

mysql,server=debian-stretch-mariadb.virt:3306,command="com_alter_server" value=0i
mysql,server=debian-stretch-mariadb.virt:3306,command="com_alter_table" value=0i
mysql,server=debian-stretch-mariadb.virt:3306,command="com_alter_tablespace" value=0i

Which I believe converts to this prometheus format:

mysql{server="debian-stretch-mariadb.virt:3306",command="com_alter_server"} 0
mysql{server="debian-stretch-mariadb.virt:3306",command="com_alter_table"} 0
mysql{server="debian-stretch-mariadb.virt:3306",command="com_alter_tablespace"} 0

But we could add combine_metrics = true/false option for example in the config. So its flexible for users to enable this if they are using prometheus.

I'll think about this, but I don't think you would want this across the board and I also think we should be careful not to have too many formats. We already have something in motion on the prometheus translation too in #4415.

[dupondje]

Just to bump this a bit, as it seems to now cause issues with Grafana due to the huge amount of queries in 1 graph because of this.

Is it still the idea to use unpivot? Or might we want to improve the exporter for Prometheus?

[powersj]

@dupondje,

I know it has been a while, but it appears that the unpivot function helps to resolve the issue you were seeing. We already have two different prometheus formats as well that users can use. If this is still an issue, were these options successful?

Thanks

[telegraf-tiger]

Hello! I am closing this issue due to inactivity. I hope you were able to resolve your problem, if not please try posting this question in our Community Slack or Community Page. Thank you!