[MySQL, PostgreSQL] QAN support for parsing query comments

Description

User story:
As a Developer who uses PMM to monitor DB used in my application, I want to have some insights into the source of queries executed against DB. I'm using frameworks and different parts of the App that can connect and execute queries.
Having more details on the query will let me see parts of my App making the most significant impact on the DB

I'm already using some OpemTelemetry/Tracing tools.

UI/UX:
TBD:

  • Do we need to specify include or exclude tags from the comments?

  • Probably: exclude known labels with unique values like traceparent, tracestate

Acceptance criteria
TBD

  • User able to enable/disable feature (defaults TBD)

  • User able to see Comments in key-value or key only format in QAN

  • parsed Comments are filters in QAN and have % and agility to filter by them

  • TBD

Out of scope: PO/Team on grooming/pre-grooming

Suggested implementation:
TBD

How to test:
TBD

Details:

  • PG: pg_stat_monitor is planning to have a feature to extract comments from queries
     

Original report: 

It would be awesome if QAN could support something like pt-query-digest's embedded-attributes:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html#cmdoption-pt-query-digest-embedded-attributes

to correlate queries with application logic

 

Related links:

How to test

1. Add MySQL and PG to monitoring (2x times both, so perfschema, slowlog, PGSM and PGSS)
2. Run some queries with comment contains key=value form in both of them (like /* controller='xxx', os='yyy' */)
3. Wait for at least minute
4. Search in left bar in QAN for filter group "controller" and there should be value xxx to choose. Another group should be "os" with value yyy.
5. Run more queries with comments key=value inside comment and see new groups in QAN filter.

How to document

None

Attachments

4

Smart Checklist

Activity

Show:

Jiří Čtvrtka June 13, 2023 at 8:31 AM

Technical preview

Jiří Čtvrtka June 5, 2023 at 9:08 AM

PGSM comments with "pgsm_extract_comments=yes" on PG side worked for me. Lets wait on Nailya confirmation.

Jiří Čtvrtka June 5, 2023 at 7:40 AM

I am not sure if Igor had "pgsm_extract_comments=yes" setup on PGSM side. I forgot to mention it to him. I am going to check PGSM now.

Roma Novikov June 5, 2023 at 7:37 AM

For PG_stat_statements, we need to review the Comments section (https://docs.percona.com/pg-stat-monitor/reference.html#postgresql-15) usage and expect the comments to be parsed in advance. 

Ihor Cherkasov June 2, 2023 at 5:46 PM

Tested on FB: https://github.com/Percona-Lab/pmm-submodules/pull/3235#issuecomment-1571942481

Tested pg_stat_statements and pg_stat_monitor.

  • pg_stat_statements:

    • PD for PostgreSQL 13 - OK

    • PostgreSQL 12, 13, 14, 15 - OK

  • pg_stat_monitor:

    • PD for PostrgreSQL 15.2 + PGSM 2.0.1 - comments are not parsed

    • PostgreSQL 15 + PGSM 2.0.1 -  comments are not parsed

Done

Details

Assignee

Reporter

Priority

Components

Needs QA

Yes

Needs Doc

Yes

Planned Version/s

Fix versions

Story Points

Smart Checklist

Created August 23, 2018 at 11:49 AM
Updated November 13, 2024 at 8:30 AM
Resolved June 13, 2023 at 8:31 AM