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

• 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