MySQL Exporter and Index IO Wait Seconds

[Peter Zaitsev]

Hi,

I'm trying to troubleshoot strange behavior  I observe with Prometheus so not filing it as a graph.

As I look in the information returned by mysqld_exporter I see:

# TYPE mysql_perf_schema_index_io_waits_seconds_total counter

mysql_perf_schema_index_io_waits_seconds_total{index="NONE",name="sbtest1",operation="delete",schema="sbinnodb"} 0

mysql_perf_schema_index_io_waits_seconds_total{index="NONE",name="sbtest1",operation="fetch",schema="sbinnodb"} 0

mysql_perf_schema_index_io_waits_seconds_total{index="NONE",name="sbtest1",operation="insert",schema="sbinnodb"} 760445.7222570692

mysql_perf_schema_index_io_waits_seconds_total{index="NONE",name="sbtest1",operation="update",schema="sbinnodb"} 0

mysql_perf_schema_index_io_waits_seconds_total{index="PRIMARY",name="sbtest1",operation="fetch",schema="sbinnodb"} 1.6027155371667786e+07

mysql_perf_schema_index_io_waits_seconds_total{index="PRIMARY",name="sys_config",operation="fetch",schema="sys"} 0.000815564068

mysql_perf_schema_index_io_waits_seconds_total{index="PRIMARY",name="testcomp",operation="fetch",schema="sbinnodb"} 0

mysql_perf_schema_index_io_waits_seconds_total{index="k_1",name="sbtest1",operation="fetch",schema="sbinnodb"} 12936.988598126449

mysql_perf_schema_index_io_waits_seconds_total{index="k_1",name="testcomp",operation="fetch",schema="sbinnodb"} 0

Note there is only one line for sbtest1  PRIMARY KEY 

However looking directly in Performance Schema I see:

mysql>  select * from   table_io_waits_summary_by_index_usage where object_name='sbtest1' and index_name='PRIMARY' \G

*************************** 1. row ***************************

     OBJECT_TYPE: TABLE

   OBJECT_SCHEMA: sbinnodb

     OBJECT_NAME: sbtest1

      INDEX_NAME: PRIMARY

      COUNT_STAR: 143675382835

  SUM_TIMER_WAIT: 2840779546248094781

  MIN_TIMER_WAIT: 1014120

  AVG_TIMER_WAIT: 148163871

  MAX_TIMER_WAIT: 1523073058466621

      COUNT_READ: 142095737997

  SUM_TIMER_READ: 16033309495643226090

  MIN_TIMER_READ: 1014120

  AVG_TIMER_READ: 112834309

  MAX_TIMER_READ: 1523073058466621

     COUNT_WRITE: 1579644838

 SUM_TIMER_WRITE: 5254214124314420307

 MIN_TIMER_WRITE: 3255826

 AVG_TIMER_WRITE: 3326199355

 MAX_TIMER_WRITE: 13818723500054

     COUNT_FETCH: 142095737997

 SUM_TIMER_FETCH: 16033309495643226090

 MIN_TIMER_FETCH: 1014120

 AVG_TIMER_FETCH: 112834309

 MAX_TIMER_FETCH: 1523073058466621

    COUNT_INSERT: 0

SUM_TIMER_INSERT: 0

MIN_TIMER_INSERT: 0

AVG_TIMER_INSERT: 0

MAX_TIMER_INSERT: 0

    COUNT_UPDATE: 1255306932

SUM_TIMER_UPDATE: 4386448736948725540

MIN_TIMER_UPDATE: 3255826

AVG_TIMER_UPDATE: 3494323549

MAX_TIMER_UPDATE: 13818723500054

    COUNT_DELETE: 324337906

SUM_TIMER_DELETE: 867765387365694767

MIN_TIMER_DELETE: 7581799

AVG_TIMER_DELETE: 2675497708

MAX_TIMER_DELETE: 3019949592189

1 row in set (0.00 sec)

any idea what could be the issue ? 

--

Peter Zaitsev, CEO, Percona
Tel: +1 888 401 3401 ext 7360   Skype:  peter_zaitsev

[Ben Kochie]

That is strange, I'm guessing this is a mysqd_exporter bug.

--
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-devel...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ben Kochie]

Ahh, I think this was intetional:

https://github.com/prometheus/mysqld_exporter/blob/master/mysqld_exporter.go#L1231-L1232

I remember something about explicitly leaving out write metrics for indexes that were not "NONE" as there was something about them that didn't make sense.

[Ben Kochie]

Yup:

https://github.com/prometheus/mysqld_exporter/commit/65e243d8a2975063fdff20da67e3745010d1adae

http://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html#table-io-waits-summary-by-index-usage-table

[Peter Zaitsev]

Hi Ben,

Reading your comment I'm not sure I understand what about them did not make sense ?

FETCH is helpful to understand which indexes are used but  I would think  WRITE data would correspond to seeing how much does it cost to maintain the index  or does not it compute properly ?

[Ben Kochie]

Ahh, is this MySQL 5.7?  Or could it possibly be a settings change?  In MySQL 5.6, you mostly don't see any of these metrics getting any values, especially on slaves.  Maybe that's why I did that originally, I most of my metrics testing on salves.

[Peter Zaitsev]

Ben,

Yes this is Percona Server 5.7, though it should be same in MySQL 5.7 

I think in some cases it makes sense not to export 0 values as they may never get any values  but more time series should mean more files and general hassle for Prometheus.

[Ben Kochie]

I've filed https://github.com/prometheus/mysqld_exporter/issues/96 to look into fixing it.  Thanks for reporting it.

[Peter Zaitsev]

Thanks Ben!

[Brian Brazil]

On 28 February 2016 at 17:09, Peter Zaitsev <p...@percona.com> wrote:

Ben,

Yes this is Percona Server 5.7, though it should be same in MySQL 5.7 

I think in some cases it makes sense not to export 0 values as they may never get any values  but more time series should mean more files and general hassle for Prometheus.

You should always export everything you know about that may become a label/timeseries, as dealing with timeseries that appear and disappear is quite difficult. Constant timeseries are quite cheap for Prometheus to deal with. http://prometheus.io/docs/practices/instrumentation/#avoid-missing-metrics

Brian

--

Brian Brazil

www.robustperception.io

[Ben Kochie]

On Sun, Feb 28, 2016 at 6:14 PM, Brian Brazil <brian....@robustperception.io> wrote:

On 28 February 2016 at 17:09, Peter Zaitsev <p...@percona.com> wrote:

Ben,

Yes this is Percona Server 5.7, though it should be same in MySQL 5.7 

I think in some cases it makes sense not to export 0 values as they may never get any values  but more time series should mean more files and general hassle for Prometheus.

You should always export everything you know about that may become a label/timeseries, as dealing with timeseries that appear and disappear is quite difficult. Constant timeseries are quite cheap for Prometheus to deal with. http://prometheus.io/docs/practices/instrumentation/#avoid-missing-metrics

Yea, I will probably change this to include all, the issue here is that I thought it would never be non-zero for these stats so I dropped them.  Zeros are cheap, and will probably get cheaper as soon as Björn implements gorilla-style compression format. :-)