Unable to fetch information about flushing

[Gorinther]

Hi,

I am new to PMM and I am trying to troubleshoot my database redo log usage, checkpoint age and InnoDB flushing.

I have installed PMM Server 2 in a docker container and connected it to a remote MySQL server, running MariaDB 10.5 on another container, where I created the required user with the permissions as per documentation states.

Most of MySQL metrics are fetched without issues, however for the ones relating to flushing, I only get “No data”, as on the following screenshot:

I am not an expert with PMM but tried to explore that chart and see it uses “mysql_info_schema_innodb_metrics_buffer_buffer_flush_adaptive_total_pages”. By the name I suppose it should be getting data from information_schema.INNODB_METRICS, by name “buffer_flush_adaptive_total_pages”, which I can fetch from my DB:

[code]MariaDB [information_schema]> select * from innodb_metrics WHERE name=‘buffer_flush_sync_total_pages’ \G;

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

  NAME: buffer_flush_sync_total_pages

SUBSYSTEM: buffer

 COUNT: 1763445

MAX_COUNT: 1763445

MIN_COUNT: NULL

AVG_COUNT: 2988

COUNT_RESET: 1763445

MAX_COUNT_RESET: 1763445

MIN_COUNT_RESET: NULL

AVG_COUNT_RESET: 2988

TIME_ENABLED: 2020-09-02 21:47:25

TIME_DISABLED: NULL

TIME_ELAPSED: 6539

TIME_RESET: NULL

ENABLED: 1

  TYPE: set_owner

COMMENT: Total pages flushed as part of sync batches

1 row in set (0.001 sec)[/code]

I do not know how to proceed with troubleshooting it, could someone please advise?

Thanks!

[Peter]

Hi,

My suggestion to troubleshoot data capture would be to select graph and press X or navigate to Explore Mode

This will show you what formula is being graphed and when you can see in Advanced Data Exploration Dashboard whenever this metric is being captured

That can help to see if it is a capture problem or display problem.

You have the data in the database it is still a question whenever this data is being captured from exporter and stored in the PMM database - sometimes timeouts can cause data capture problems - This can be checked with Prometheus Exporter Status dashboard

Re your particular MariaDB problem - I’d check if increasing your innodb_io_capacity and innodb_io_capacity_max will improve the situation

[Gorinther]

Hi,

Thanks for your reply. That “explore” window I already tried going before, that’s where I got the “mysql_info_schema_innodb_metrics_buffer_buffer_flush_adaptive_total_pages” name. However if I try to create a new query and start typing “mysql_info_schema_innodb_”, after this I only have 10 suggestions as on the screenshot below:

And none of them starts with “_metrics” - as soon as I type “_m” there is no suggestion anymore.

I also tried going for that “Advanced Data Exploration” dashboard however there is no “innodb_metrics” at all:

My Prometheus Exporter Status looks good aswell, I could even find execution times for “collect.info_schema.innodb_metrics” which I think should be what I am looking for:

Any other ideas on how to proceed?

Thanks!

[adivinho]

Hello Gorinther,

Sad to say but the current mysqld_exporter can’t correctly fetch information_schema.innodb_metrics in MariaDB 10.4 and later due to the table format changes.

https://github.com/prometheus/mysqld_exporter/issues/494

[Gorinther]

Thanks! This was exactly the issue, I tried changing for MySQL 8.0.21 and it has worked straight away:

It’s so bad to see the change from MySQL codebase causing those issues.

Thanks again, I learned a lot with it

[Peter]

Thanks @adivinho for finding the root cause.

I wish MariaDB would have kept compatibility… though this problem should not be hard to fix.