skip SPACE_TYPE column for MariaDB >=10.5 by businessbean · Pull Request #724 · prometheus/mysqld_exporter

businessbean · 2023-04-15T18:47:31Z

this PR skips the SPACE_TYPE column for MariaDB server instances with version 10.5 or newer.

to be able to scrape the information_schema.innodb_sys_tablespaces tables
MariaDB has cleaned up the INFORMATION_SCHEMA.INNODB_ tables from version 10.5 on
unit tests added
fixes
- SPACE_TYPE Error while querying innodb_tablespaces #528
- MariaDB 10.6.* - 10.8.* - Unknown column 'SPACE_TYPE' #662

build environment

macOS 13.3.1 Intel
golang 1.20.2
make build

unit test results

go test -timeout 30s -run ^TestScrapeInfoSchemaInnodbTablespaces$ github.com/prometheus/mysqld_exporter/collector
ok  	github.com/prometheus/mysqld_exporter/collector	0.391s

go test -timeout 30s -run ^TestScrapeInfoSchemaInnodbTablespacesWithoutSpaceType$ github.com/prometheus/mysqld_exporter/collector
ok  	github.com/prometheus/mysqld_exporter/collector	0.202s

go test -timeout 30s -run ^TestSemanticVersionCheck$ github.com/prometheus/mysqld_exporter/collector
ok  	github.com/prometheus/mysqld_exporter/collector	0.174s

go test -timeout 30s github.com/prometheus/mysqld_exporter/collector -short
ok  	github.com/prometheus/mysqld_exporter/collector	0.468s

MariaDB test results

10.4.28

# TYPE mysql_info_schema_innodb_tablespace_allocated_size_bytes gauge
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="innodb_temporary"} 1.2582912e+07
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/gtid_slave_pos"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/innodb_index_stats"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/innodb_table_stats"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/transaction_registry"} 114688
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/wsrep_cluster"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/wsrep_cluster_members"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/wsrep_streaming_log"} 65536
# HELP mysql_info_schema_innodb_tablespace_file_size_bytes The apparent size of the file, which represents the maximum size of the file, uncompressed.
# TYPE mysql_info_schema_innodb_tablespace_file_size_bytes gauge
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="innodb_temporary"} 1.2582912e+07
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/gtid_slave_pos"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/innodb_index_stats"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/innodb_table_stats"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/transaction_registry"} 114688
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/wsrep_cluster"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/wsrep_cluster_members"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/wsrep_streaming_log"} 65536
# HELP mysql_info_schema_innodb_tablespace_space_info The Tablespace information and Space ID.
# TYPE mysql_info_schema_innodb_tablespace_space_info gauge
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/gtid_slave_pos"} 4
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/innodb_index_stats"} 2
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/innodb_table_stats"} 1
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/transaction_registry"} 3
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/wsrep_cluster"} 5
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/wsrep_cluster_members"} 6
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="Dynamic",space_type="Single",tablespace_name="mysql/wsrep_streaming_log"} 7
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="System",tablespace_name="innodb_temporary"} 4.294967294e+09

10.5.18

# TYPE mysql_info_schema_innodb_tablespace_allocated_size_bytes gauge
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="innodb_temporary"} 1.2582912e+07
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/gtid_slave_pos"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/innodb_index_stats"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/innodb_table_stats"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/transaction_registry"} 114688
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/wsrep_cluster"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/wsrep_cluster_members"} 65536
mysql_info_schema_innodb_tablespace_allocated_size_bytes{tablespace_name="mysql/wsrep_streaming_log"} 65536
# HELP mysql_info_schema_innodb_tablespace_file_size_bytes The apparent size of the file, which represents the maximum size of the file, uncompressed.
# TYPE mysql_info_schema_innodb_tablespace_file_size_bytes gauge
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="innodb_temporary"} 1.2582912e+07
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/gtid_slave_pos"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/innodb_index_stats"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/innodb_table_stats"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/transaction_registry"} 114688
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/wsrep_cluster"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/wsrep_cluster_members"} 65536
mysql_info_schema_innodb_tablespace_file_size_bytes{tablespace_name="mysql/wsrep_streaming_log"} 65536
# HELP mysql_info_schema_innodb_tablespace_space_info The Tablespace information and Space ID.
# TYPE mysql_info_schema_innodb_tablespace_space_info gauge
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="innodb_temporary"} 4.294967294e+09
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/gtid_slave_pos"} 4
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/innodb_index_stats"} 2
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/innodb_table_stats"} 1
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/transaction_registry"} 3
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/wsrep_cluster"} 5
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/wsrep_cluster_members"} 6
mysql_info_schema_innodb_tablespace_space_info{file_format="NONE",row_format="NONE",space_type="",tablespace_name="mysql/wsrep_streaming_log"} 7

- to be able to scrape information_schema.innodb_sys_tablespaces - https://jira.mariadb.org/browse/MDEV-19940 Signed-off-by: Birk Bohne <birk.bohne@sap.com>

- to avoid panic errors with invalid version numbers - unit test added Signed-off-by: Birk Bohne <birk.bohne@sap.com>

- to be able to validate code with and without SPACE_TYPE column - version query skipped if running inside test Signed-off-by: Birk Bohne <birk.bohne@sap.com>

firecow · 2023-07-15T18:45:55Z

@businessbean Can I do anything to help get this into main?
I'm getting annoyed filtering these logs in our Kibana 😄

businessbean · 2023-07-17T08:04:07Z

@businessbean Can I do anything to help get this into main? I'm getting annoyed filtering these logs in our Kibana 😄

Hi @firecow , you should filter these logs already on Logstash level 😄 I will update the PR today...

firecow · 2023-07-17T08:18:25Z

@businessbean Nice, thanks man..

SuperQ · 2023-07-17T08:25:10Z

collector/binlog.go


 // Scrape collects data from database connection and sends it over channel as prometheus metric.
-func (ScrapeBinlogSize) Scrape(ctx context.Context, db *sql.DB, ch chan<- prometheus.Metric, logger log.Logger) error {
+func (ScrapeBinlogSize) Scrape(ctx context.Context, db *sql.DB, ch chan<- prometheus.Metric, logger log.Logger, semanticVersionIsNewer bool) error {


This semanticVersionIsNewer is not a good way to handle this. It's too specific to one collector, and hard-codes it as a flag for every collector.

What we probably want is to do something like we did in the postgres_expoter, which is to make the db instance include both a database connection and a semver that each collector can access.

Do you have a link to the related code?

Here's the PR: prometheus-community/postgres_exporter#785

I would prefer to do that refactoring as a separate PR.

I will look into this, but it will take some time...

I created a PR with that refactoring #859

Signed-off-by: Birk Bohne <birk.bohne@sap.com>

Pebin · 2024-03-13T09:48:53Z

is there any chance to merge it? 🙂 or is it waiting for the refactor? can I help with some testing, rebasing or coding? It's not exactly clear to me what blocked the merge 🙂

Pebin · 2024-06-06T09:46:02Z

hello @SuperQ are you still working on this repo? It would be useful for us to merge it. I can do some changes if needed. We are currently making custom build for this library because of this

firecow · 2024-07-02T11:41:01Z

@SuperQ Yeah, we are running a custom build as well.
Is there anything we can do to get this into main somehow?

SuperQ · 2024-08-12T13:55:06Z

Thanks to @s10 for doing the refactoring!

Sorry for the lack of activity here, I don't have a ton of time to maintain MySQL stuff since I haven't really worked on MySQL databases since 2017.

It would be great if we could get more community involvement here.

SuperQ · 2024-08-29T06:36:43Z

Fixed in #860

businessbean added 2 commits April 15, 2023 20:56

skip SPACE_TYPE colums for MariaDB >=10.5

7681885

- to be able to scrape information_schema.innodb_sys_tablespaces - https://jira.mariadb.org/browse/MDEV-19940 Signed-off-by: Birk Bohne <birk.bohne@sap.com>

go-version instead of go-semver

07aa31d

- to avoid panic errors with invalid version numbers - unit test added Signed-off-by: Birk Bohne <birk.bohne@sap.com>

businessbean force-pushed the main branch from b062fca to 07aa31d Compare April 15, 2023 18:56

businessbean changed the title ~~skip SPACE_TYPE colum for MariaDB >=10.5~~ skip SPACE_TYPE column for MariaDB >=10.5 Apr 15, 2023

dedicated test for old and new table structures

057063a

- to be able to validate code with and without SPACE_TYPE column - version query skipped if running inside test Signed-off-by: Birk Bohne <birk.bohne@sap.com>

businessbean force-pushed the main branch from 245ca20 to 057063a Compare April 20, 2023 12:14

SuperQ reviewed Jul 17, 2023

View reviewed changes

businessbean added 2 commits July 17, 2023 12:06

Merge remote-tracking branch 'upstream/main'

3a0d12c

go-version mod info added

705b9dc

Signed-off-by: Birk Bohne <birk.bohne@sap.com>

businessbean force-pushed the main branch from ba68879 to 705b9dc Compare July 17, 2023 10:13

s10 mentioned this pull request Aug 12, 2024

Skip SPACE_TYPE column for MariaDB >=10.5 #860

Merged

SuperQ closed this Aug 29, 2024

Conversation

businessbean commented Apr 15, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

build environment

unit test results

MariaDB test results

10.4.28

10.5.18

Uh oh!

firecow commented Jul 15, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

businessbean commented Jul 17, 2023

Uh oh!

firecow commented Jul 17, 2023

Uh oh!

SuperQ Jul 17, 2023

Choose a reason for hiding this comment

Uh oh!

businessbean Jul 17, 2023

Choose a reason for hiding this comment

Uh oh!

SuperQ Jul 17, 2023

Choose a reason for hiding this comment

Uh oh!

SuperQ Jul 17, 2023

Choose a reason for hiding this comment

Uh oh!

businessbean Jul 17, 2023

Choose a reason for hiding this comment

Uh oh!

s10 Aug 12, 2024

Choose a reason for hiding this comment

Uh oh!

Pebin commented Mar 13, 2024

Uh oh!

Pebin commented Jun 6, 2024

Uh oh!

firecow commented Jul 2, 2024

Uh oh!

SuperQ commented Aug 12, 2024

Uh oh!

SuperQ commented Aug 29, 2024

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

businessbean commented Apr 15, 2023 •

edited

Loading

firecow commented Jul 15, 2023 •

edited

Loading