How Prometheus collect MySQL QPS please?

[jwang...@gmail.com]

From which MySQL system table it get the information please?

Thanks a lot in advance

[Ben Kochie]

The mysqld_exporter exposes several metrics from different MySQL subsystems.

The basic server QPS data is collected from SHOW GLOBAL STAUS.

Take a look at the mysql_global_status_commands_total metric.

There are also detailed per query digest metrics in performance schema.  These are disabled by default, as they are only available in MySQL 5.6 and newer.

On Sep 29, 2016 12:10, <jwang...@gmail.com> wrote:

From which MySQL system table it get the information please?

Thanks a lot in advance

--
You received this message because you are subscribed to the Google Groups "Prometheus Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-developers+unsub...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jwang...@gmail.com]

show global status like '%quer%';

Queries | 463542292

seams only the total queries since server started.
also, can prometheus show what are these queries please? e.g. inserts/updates/deletes/selects et al

Thanks

[Ben Kochie]

MySQL calls these commands, hence the metric I mentioned.  They are broken down by command type.

[jwang...@gmail.com]

Please forgive my blunder-ness, how do i get the mysql_global_status_commands_total metric please?

Please elaborate a bit more.

Thanks a lot in advance

[Julius Volz]

Check out the README for setting up the mysqld_exporter:

https://github.com/prometheus/mysqld_exporter

It's an exporter which Prometheus can scrape and in the background (during the scrape), it fetches lots of useful metrics from a MySQL daemon.

[george....@kkbox.com]

Like others have suggested, use mysql_exporter and node_exporter and also percona_plugin for grafana

The links below will the whole setup

https://blog.100tb.com/how-to-set-up-prometheus-to-monitor-your-ubuntu-server
https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/
https://grafana.net/plugins/percona-percona-app

[george....@kkbox.com]

your prometheus.yml file should look like this

global:
scrape_interval: 5s
evaluation_interval: 5s
scrape_configs:
- job_name: prometheus
static_configs:
- targets: ['prometheus_ip:9090']
- job_name: grafana-1-linux
static_configs:
- targets: ['prometheus_ip:9100']
labels:
alias: prometheus-server
- job_name: db-linux
static_configs:
- targets: ['db_ip:9100']
labels:
alias: db-name
- job_name: db-mysql
static_configs:
- targets: ['db_ip:9104']
labels:
alias: db-name

[jwang...@gmail.com]

Thanks a lot.

However, form my experience there is no way to collect QPS from "GLOBAL STATUS" - you may get how many rows affected.

Thanks

[Ben Kochie]

Yes, it is totally possible. Like I said originally, the mysqld_exporter exposes the GLOBAL STAUS Com_% counters as a metric.

Prometheus collects this and allows you to apply a rate function.

For example, rate(mysql_global_status_commands_total[5m])

This returns the per-second rate with a 5 minute rolling average.

--
You received this message because you are subscribed to the Google Groups "Prometheus Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-developers+unsub...@googlegroups.com.

To post to this group, send email to prometheus-developers@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-developers/cc08b6be-22c6-4fed-ae65-eed4c8f66abd%40googlegroups.com.

[jwang...@gmail.com]

I remember it wrong. Com_* is queries not row affected.
http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Com_xxx

Thanks a lot, All

[Ben Kochie]

Yup. You can get good rows affected/examined/sent from performance schema events statements.  One of the more useful things Prometheus can handle that many monitoring systems can't. ;-)

--
You received this message because you are subscribed to the Google Groups "Prometheus Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-developers+unsub...@googlegroups.com.
To post to this group, send email to prometheus-developers@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-developers/3f4cc975-4ee7-4905-963d-dc6357cd6862%40googlegroups.com.