Capture Prepared statements data in Query Analytics with datasource PERFORMANCE_SCHEMA

[taraskozub-percona]

Description

Goal: Create a basic implementation of collecting prepared statement for PS 8.0.**

The problem: Queries executed using prepared statements are not captured by QAN. This cause the problem = Not everything happening in the  DB server  - visible in the QAN and it's easy to do false conclusions about a Server 

User Story: As a PMM user I need to be able to see queries executed with prepared statements so that I can see the real number of queries executed by the DB server 

Use case: As a developer, I'm using prepared statements to increase the security of my APP and DB. I can prepare a statement in my code and later execute it.  This type of query generates a majority of the load on the DB server.

Suggested solution

Implement getPreparedStatements similar to https://github.com/percona/pmm-agent/blob/master/agents/mysql/perfschema/summaries.go Implement a model for prepared_statements_instances as in https://github.com/percona/pmm-agent/blob/master/agents/mysql/perfschema/models.go Collect metrics of prepared statements: https://github.com/percona/pmm-agent/blob/master/agents/mysql/perfschema/perfschema.go#L126

Additional context

Out of scope: Any other MySQL version/distribution than PS 8.0

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[daniel-shuy]

@taraskozub-percona which MySQL driver are you using?

I tried to test this with the JDBC driver (requires setting useServerPrepStmts to true). Indeed MySQL doesn't log executions of prepared statements to performance_schema.events_statements_summary_by_digest, but with the JDBC driver it doesn't log them to performance_schema.prepared_statements_instances as well (the table was empty for me). I tested this with both percona:5.7 and percona:8.0.

[BupycHuk]

Hi @daniel-shuy, have you tried to make queries using MySQL cli client?

[daniel-shuy]

Yes, when I use the PREPARE statement (the text protocol) it works, but not when I use the binary protocol (drivers). I also tested with the PHP MySQLi driver (which supports MySQL's server-side prepared statements), and the same issue happens (prepared statements are neither logged to performance_schema.events_statements_summary_by_digest nor performance_schema.prepared_statements_instances).

Also if I use the JDBC driver with useServerPrepStmts set to false (client-side prepared statements), statements are logged to performance_schema.events_statements_summary_by_digest (like normal statements), but not performance_schema.prepared_statements_instances. So this is not an issue with the JDBC driver, but MySQL/percona itself.

Even though the performance_schema.prepared_statements_instances documentation claims to support both binary and text protocols, it seems like only the text protocol is supported (unless there is some server option to enable capture for binary protocols that I'm not aware of).

[Retssaze]

So whether somebody is doing at this?

[Retssaze]

Guys, I'd like to stake out this

[artemgavrilov]

@Retssaze Hi, go ahead!

[Retssaze]

Sure

[Retssaze]

Can you assign one to me?

[Retssaze]

Thank you

[Retssaze]

I have started from compiling the code and here is the issue:

env CGO_ENABLED=1 go build -v -ldflags "-extldflags '-static' -X 'github.com/percona/pmm/version.ProjectName=pmm-agent' -X 'github.com/percona/pmm/version.Version=2.28.0-2402-g576142adc-dirty' -X 'github.com/percona/pmm/version.PMMVersion=2.28.0-2402-g576142adc-dirty' -X 'github.com/percona/pmm/version.Timestamp=1694931585' -X 'github.com/percona/pmm/version.FullCommit=576142adcd752462868575a238bd97804b22449a' -X 'github.com/percona/pmm/version.Branch=main'" -tags 'osusergo netgo static_build' -o ../bin/pmm-agent agentlocal/agent_local.go:53:2: no required module provides package github.com/percona/pmm/api/agentlocalpb; to add it: go get github.com/percona/pmm/api/agentlocalpb commands/clients.go:37:2: no required module provides package github.com/percona/pmm/api/agentlocalpb/json/client; to add it: go get github.com/percona/pmm/api/agentlocalpb/json/client commands/setup.go:29:2: no required module provides package github.com/percona/pmm/api/agentlocalpb/json/client/agent_local; to add it: go get github.com/percona/pmm/api/agentlocalpb/json/client/agent_local runner/jobs/mongodb_backup_job.go:29:2: no required module provides package github.com/percona/pmm/api/managementpb/backup; to add it: go get github.com/percona/pmm/api/managementpb/backup commands/clients.go:38:2: no required module provides package github.com/percona/pmm/api/managementpb/json/client; to add it: go get github.com/percona/pmm/api/managementpb/json/client commands/clients.go:39:2: no required module provides package github.com/percona/pmm/api/managementpb/json/client/node; to add it: go get github.com/percona/pmm/api/managementpb/json/client/node ../utils/errors/errors.go:35:2: no required module provides package github.com/percona/pmm/api/serverpb; to add it: go get github.com/percona/pmm/api/serverpb make[1]: *** [Makefile:28: release] Error 1

go get github.com/percona/pmm/api/agentlocalpb/json/client/agent_local: no matching versions for query "upgrade" go get github.com/percona/pmm/tree/main/api/agentlocalpb/json/client/agent_local: no matching versions for query "upgrade"

Can you please advice something?

[Retssaze]

Already solved this

[Retssaze]

make clean deletes more than it should

[Retssaze]

Can someone please advise me, how should I create docker images for Server and for Agent from binaries after successfully compiling the product?

[Retssaze]

Found something in CONTRIBUTING.md

[BupycHuk]

@Retssaze could you please create PRs in this repo and in percona-lab/pmm-submodules? PR in percona-lab/pmm-submodules should build a docker image for you.

[Retssaze]

Do you mean Pool Request?

[BupycHuk]

Yes, Pull Request

[Retssaze]

Should I also create a visual interface for the collected data?