Fetch lock time and cpu time from performance schema by cristiangreco · Pull Request #862 · prometheus/mysqld_exporter

cristiangreco · 2024-08-26T14:11:02Z

Extend the query on events_statements_summary_by_digest to fetch SUM_LOCK_TIME and SUM_CPU_TIME and return them as counters. These are useful to report time spent running on the cpu (e.g. to sort results or run computations) vs waiting on locks (e.g. to acquire write lock on a table or row), by query digest.

SuperQ

I think we originally intentionally left these out for cardinality reasons. Would you mind documenting how they they are additionally useful in the PR description? That way we can have better history of "why" these are added by default.

cristiangreco · 2024-08-26T15:11:49Z

I think we originally intentionally left these out for cardinality reasons. Would you mind documenting how they they are additionally useful in the PR description? That way we can have better history of "why" these are added by default.

Hey @SuperQ thanks for having a look! These metrics would have the same cardinality (in terms of labels values) as the other sibling metrics created out of the query events_statements_summary_by_digest. Or are we worried about adding new metrics in general? (I wanted to submit a followup PR to additionally expose QUANTILE_{95,99,999} so it'd be good to understand what is the approach to take)

SuperQ · 2024-08-26T15:23:41Z

It's about adding new metrics in general. If you can explain what the metrics are useful for, that's all I'm asking about.

Exposing the quantiles is less useful, as they can't be aggregated between MySQL servers.

cristiangreco · 2024-08-26T16:04:53Z

I've added a short note on the PR description, could you please check that?

SuperQ · 2024-08-27T09:25:54Z

Needs a rebase.

Extend the query on `events_statements_summary_by_digest` to fetch `SUM_LOCK_TIME` and `SUM_CPU_TIME` and return them as counters. Signed-off-by: Cristian Greco <cristian@regolo.cc>

cristiangreco · 2024-08-27T09:46:26Z

Needs a rebase.

Thanks so much! Just rebased.

SuperQ · 2024-08-27T09:47:26Z

I had a think about the history of this. I wish we had written down the queries we experimented with in production at the time.

I think we tried adding CPU time originally, but we didn't really find it useful compared to total query time.

Lock time is probably a good one to have tho.

If cpu time = total time - lock time, maybe we don't need cpu time.

cristiangreco · 2024-08-27T15:17:14Z

I think we tried adding CPU time originally, but we didn't really find it useful compared to total query time.

@SuperQ fair enough, if you prefer I can close this PR and open a new one adding just lock time?

SuperQ · 2024-08-27T16:43:58Z

I'm mostly looking for up-to-date expertise in MySQL here. It's been a while since I looked deeply at this data. I assume you've got data on the uselessness of these metrics.

cristiangreco · 2024-08-28T08:49:29Z

I'm mostly looking for up-to-date expertise in MySQL here. It's been a while since I looked deeply at this data. I assume you've got data on the uselessness of these metrics.

So I'm not sure that cpu time = total time - lock time is true but can't disprove it either. I thought it was cheap enough that we could export both metrics anyway.

SuperQ · 2024-08-28T09:07:36Z

Ok, I guess this is fine as-is. If people complain about the additional cardinality we can look it over again.

Changes: * [FEATURE] Support for prometheus scrape timeout in probe endpoint prometheus#828 * [ENHANCEMENT] Support MySQL 8.4 replicas syntax prometheus#837 * [ENHANCEMENT] Fetch lock time and cpu time from performance schema prometheus#862 * [ENHANCEMENT] Add the instance struct to handle connections prometheus#859 * [ENHANCEMENT] Optimize code by using built-in constants in the standard lib prometheus#844 * [BUGFIX] Fix fetching tmpTables vs tmpDiskTables from performance_schema prometheus#853 * [BUGFIX] Skip SPACE_TYPE column for MariaDB >=10.5 prometheus#860 * [BUGFIX] Fixed parsing of timestamps with non-zero padded days prometheus#841 * [BUGFIX] Fix auto_increment metric collection errors caused by using collation in INFORMATION_SCHEMA searches prometheus#833 * [BUGFIX] Fix race condition in ReloadConfig prometheus#760 * [BUGFIX] Change processlist query to support ONLY_FULL_GROUP_BY sql_mode prometheus#684 * [BUGFIX] replication_applier_status_by_worker requires mysql 8.0 prometheus#683 * [BUGFIX] Update docker registry link in README.md prometheus#813 * [BUGFIX] Fix Docker run command and update documentation for cnf file handling prometheus#843

Changes: * [FEATURE] Support for prometheus scrape timeout in probe endpoint prometheus#828 * [ENHANCEMENT] Support MySQL 8.4 replicas syntax prometheus#837 * [ENHANCEMENT] Fetch lock time and cpu time from performance schema prometheus#862 * [ENHANCEMENT] Add the instance struct to handle connections prometheus#859 * [ENHANCEMENT] Optimize code by using built-in constants in the standard lib prometheus#844 * [BUGFIX] Fix fetching tmpTables vs tmpDiskTables from performance_schema prometheus#853 * [BUGFIX] Skip SPACE_TYPE column for MariaDB >=10.5 prometheus#860 * [BUGFIX] Fixed parsing of timestamps with non-zero padded days prometheus#841 * [BUGFIX] Fix auto_increment metric collection errors caused by using collation in INFORMATION_SCHEMA searches prometheus#833 * [BUGFIX] Fix race condition in ReloadConfig prometheus#760 * [BUGFIX] Change processlist query to support ONLY_FULL_GROUP_BY sql_mode prometheus#684 * [BUGFIX] replication_applier_status_by_worker requires mysql 8.0 prometheus#683 * [BUGFIX] Update docker registry link in README.md prometheus#813 * [BUGFIX] Fix Docker run command and update documentation for cnf file handling prometheus#843 Signed-off-by: Cristian Greco <cristian@regolo.cc>

Changes: * [CHANGE] Replace logging library go-kit/log with slog prometheus#885 * [FEATURE] Support for prometheus scrape timeout in probe endpoint prometheus#828 * [ENHANCEMENT] Support MySQL 8.4 replicas syntax prometheus#837 * [ENHANCEMENT] Fetch lock time and cpu time from performance schema prometheus#862 * [ENHANCEMENT] Add the instance struct to handle connections prometheus#859 * [ENHANCEMENT] Optimize code by using built-in constants in the standard lib prometheus#844 * [BUGFIX] Fix fetching tmpTables vs tmpDiskTables from performance_schema prometheus#853 * [BUGFIX] Skip SPACE_TYPE column for MariaDB >=10.5 prometheus#860 * [BUGFIX] Fixed parsing of timestamps with non-zero padded days prometheus#841 * [BUGFIX] Fix auto_increment metric collection errors caused by using collation in INFORMATION_SCHEMA searches prometheus#833 * [BUGFIX] Fix race condition in ReloadConfig prometheus#760 * [BUGFIX] Change processlist query to support ONLY_FULL_GROUP_BY sql_mode prometheus#684 * [BUGFIX] replication_applier_status_by_worker requires mysql 8.0 prometheus#683 * [BUGFIX] Update docker registry link in README.md prometheus#813 * [BUGFIX] Fix Docker run command and update documentation for cnf file handling prometheus#843 * [BUGFIX] info_schema_tables: do not collect the sys schema prometheus#879 Signed-off-by: Cristian Greco <cristian@regolo.cc>

Changes: * [CHANGE] Replace logging library go-kit/log with slog prometheus#875 * [FEATURE] Support for prometheus scrape timeout in probe endpoint prometheus#828 * [ENHANCEMENT] Support MySQL 8.4 replicas syntax prometheus#837 * [ENHANCEMENT] Fetch lock time and cpu time from performance schema prometheus#862 * [ENHANCEMENT] Add the instance struct to handle connections prometheus#859 * [ENHANCEMENT] Optimize code by using built-in constants in the standard lib prometheus#844 * [BUGFIX] Fix fetching tmpTables vs tmpDiskTables from performance_schema prometheus#853 * [BUGFIX] Skip SPACE_TYPE column for MariaDB >=10.5 prometheus#860 * [BUGFIX] Fixed parsing of timestamps with non-zero padded days prometheus#841 * [BUGFIX] Fix auto_increment metric collection errors caused by using collation in INFORMATION_SCHEMA searches prometheus#833 * [BUGFIX] Fix race condition in ReloadConfig prometheus#760 * [BUGFIX] Change processlist query to support ONLY_FULL_GROUP_BY sql_mode prometheus#684 * [BUGFIX] replication_applier_status_by_worker requires mysql 8.0 prometheus#683 * [BUGFIX] Update docker registry link in README.md prometheus#813 * [BUGFIX] Fix Docker run command and update documentation for cnf file handling prometheus#843 * [BUGFIX] info_schema_tables: do not collect the sys schema prometheus#879 Signed-off-by: Cristian Greco <cristian@regolo.cc>

Changes: * [CHANGE] Replace logging library go-kit/log with slog #875 * [FEATURE] Support for prometheus scrape timeout in probe endpoint #828 * [ENHANCEMENT] Support MySQL 8.4 replicas syntax #837 * [ENHANCEMENT] Fetch lock time and cpu time from performance schema #862 * [ENHANCEMENT] Add the instance struct to handle connections #859 * [ENHANCEMENT] Optimize code by using built-in constants in the standard lib #844 * [BUGFIX] Fix fetching tmpTables vs tmpDiskTables from performance_schema #853 * [BUGFIX] Skip SPACE_TYPE column for MariaDB >=10.5 #860 * [BUGFIX] Fixed parsing of timestamps with non-zero padded days #841 * [BUGFIX] Fix auto_increment metric collection errors caused by using collation in INFORMATION_SCHEMA searches #833 * [BUGFIX] Fix race condition in ReloadConfig #760 * [BUGFIX] Change processlist query to support ONLY_FULL_GROUP_BY sql_mode #684 * [BUGFIX] replication_applier_status_by_worker requires mysql 8.0 #683 * [BUGFIX] Update docker registry link in README.md #813 * [BUGFIX] Fix Docker run command and update documentation for cnf file handling #843 * [BUGFIX] info_schema_tables: do not collect the sys schema #879 Signed-off-by: Cristian Greco <cristian@regolo.cc>

* [CHANGE] Replace logging library go-kit/log with slog #875 * [FEATURE] Support for prometheus scrape timeout in probe endpoint #828 * [ENHANCEMENT] Support MySQL 8.4 replicas syntax #837 * [ENHANCEMENT] Fetch lock time and cpu time from performance schema #862 * [ENHANCEMENT] Add the instance struct to handle connections #859 * [ENHANCEMENT] Optimize code by using built-in constants in the standard lib #844 * [BUGFIX] Fix fetching tmpTables vs tmpDiskTables from performance_schema #853 * [BUGFIX] Skip SPACE_TYPE column for MariaDB >=10.5 #860 * [BUGFIX] Fixed parsing of timestamps with non-zero padded days #841 * [BUGFIX] Fix auto_increment metric collection errors caused by using collation in INFORMATION_SCHEMA searches #833 * [BUGFIX] Fix race condition in ReloadConfig #760 * [BUGFIX] Change processlist query to support ONLY_FULL_GROUP_BY sql_mode #684 * [BUGFIX] replication_applier_status_by_worker requires mysql 8.0 #683 * [BUGFIX] Update docker registry link in README.md #813 * [BUGFIX] Fix Docker run command and update documentation for cnf file handling #843 * [BUGFIX] info_schema_tables: do not collect the sys schema #879 Signed-off-by: SuperQ <superq@gmail.com>

dbakit · 2025-02-10T08:10:52Z

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html
----
As indicated in the release notes, SUM_LOCK_TIME and SUM_CPU_TIME are only supported starting from version 8.0.28. These fields do not exist in versions 5.7 and below or in versions prior to 8.0.28. It is recommended to differentiate the scrap SQL based on the mysql_version.
This PR case #905

…8.0.28 Fix issue introduced in prometheus#862 Only query SUM_LOCK_TIME and SUM_CPU_TIME when using MySQL >= 8.0.28.

…8.0.28 Fix issue introduced in prometheus#862 Only query SUM_LOCK_TIME and SUM_CPU_TIME when using MySQL >= 8.0.28. Signed-off-by: Cristian Greco <cristian@regolo.cc>

…8.0.28 Fix issue introduced in prometheus#862 Only query SUM_LOCK_TIME and SUM_CPU_TIME when using MySQL >= 8.0.28. Fixes prometheus#905 Signed-off-by: Cristian Greco <cristian@regolo.cc>

cristiangreco force-pushed the cristian/add-lock-cpu-time branch from 9841b7b to 4212787 Compare August 26, 2024 14:11

SuperQ reviewed Aug 26, 2024

View reviewed changes

SuperQ approved these changes Aug 27, 2024

View reviewed changes

Fetch lock time and cpu time from performance schema

3225ffa

Extend the query on `events_statements_summary_by_digest` to fetch `SUM_LOCK_TIME` and `SUM_CPU_TIME` and return them as counters. Signed-off-by: Cristian Greco <cristian@regolo.cc>

cristiangreco force-pushed the cristian/add-lock-cpu-time branch from 4212787 to 3225ffa Compare August 27, 2024 09:42

SuperQ merged commit d65c5e6 into prometheus:main Aug 28, 2024

SuperQ mentioned this pull request Aug 29, 2024

Monitor query lock time #827

Closed

cristiangreco mentioned this pull request Nov 8, 2024

Release 0.16.0 #889

Merged

SuperQ mentioned this pull request Nov 8, 2024

Release v0.16.0 #891

Merged

matthewnolf mentioned this pull request Nov 25, 2024

Add perf_schema quantile columns to collector #897

Merged

dbakit mentioned this pull request Feb 10, 2025

Unknown column 'SUM_CPU_TIME' in 'SELECT'" #905

Closed

cristiangreco deleted the cristian/add-lock-cpu-time branch February 10, 2025 20:44

cristiangreco mentioned this pull request Feb 10, 2025

Fix #905: use SUM_LOCK_TIME and SUM_CPU_TIME with mysql >= 8.0.28 #916

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fetch lock time and cpu time from performance schema#862

Fetch lock time and cpu time from performance schema#862
SuperQ merged 1 commit intoprometheus:mainfrom
cristiangreco:cristian/add-lock-cpu-time

cristiangreco commented Aug 26, 2024 •

edited

Loading

Uh oh!

SuperQ left a comment

Uh oh!

cristiangreco commented Aug 26, 2024

Uh oh!

SuperQ commented Aug 26, 2024 •

edited

Loading

Uh oh!

cristiangreco commented Aug 26, 2024 •

edited

Loading

Uh oh!

SuperQ commented Aug 27, 2024

Uh oh!

cristiangreco commented Aug 27, 2024

Uh oh!

SuperQ commented Aug 27, 2024 •

edited

Loading

Uh oh!

cristiangreco commented Aug 27, 2024

Uh oh!

SuperQ commented Aug 27, 2024

Uh oh!

cristiangreco commented Aug 28, 2024

Uh oh!

SuperQ commented Aug 28, 2024

Uh oh!

dbakit commented Feb 10, 2025 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

cristiangreco commented Aug 26, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

SuperQ left a comment

Choose a reason for hiding this comment

Uh oh!

cristiangreco commented Aug 26, 2024

Uh oh!

SuperQ commented Aug 26, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cristiangreco commented Aug 26, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

SuperQ commented Aug 27, 2024

Uh oh!

cristiangreco commented Aug 27, 2024

Uh oh!

SuperQ commented Aug 27, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

cristiangreco commented Aug 27, 2024

Uh oh!

SuperQ commented Aug 27, 2024

Uh oh!

cristiangreco commented Aug 28, 2024

Uh oh!

SuperQ commented Aug 28, 2024

Uh oh!

dbakit commented Feb 10, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

cristiangreco commented Aug 26, 2024 •

edited

Loading

SuperQ commented Aug 26, 2024 •

edited

Loading

cristiangreco commented Aug 26, 2024 •

edited

Loading

SuperQ commented Aug 27, 2024 •

edited

Loading

dbakit commented Feb 10, 2025 •

edited

Loading