PMM QAN for prepared statements with AWS RDS mysql

Percona Monitoring and Management (PMM)
1

I use aws rds mysql and successfully set everything up with using performance_schema as the source.

Unfortunately I had to notice that QAN does not show any of my application queries and I found out that this is because it does not collect data for prepared statements (which is the de facto standard for any application to send queries). Does this mean QAN only really works when using the slow query log as the source?

If so, is there a way to use slow query logs as the source for PMM when connecting to aws rds mysql?

2

Query analytics do support perf schema as a query source though ( AWS - Percona Monitoring and Management ).

Did you enable the “Use Performance Schema” checkbox during setup?

3

@Michael_Okoko yes. but only queries that did not use prepared statements are showing up in QAN. I think this has been a known issue/limitation when using performance_schema as the source:

Weird that there is not more pressure to drive this forwards, since pretty much everyone uses prepared statements. So the only option to get useful QAN seems to be to use the slow query logs and I am not sure how I can do this with aws rds or if it is even possible

4

In my 20+ years as a MySQL DBA, working for companies with only 2-3 MySQL servers, all the way up to 10,000+ servers handling millions of QPS, I can emphatically state that prepared statements are NOT the de facto standard in any way. They may be standard for you, but in general, they are not used.

The main reason they are not used is because prepared statements don’t gain you anything other than overhead. If you prepare, then bind, then execute a single SELECT statement only once, you’ve gained nothing, and added a proportionality large amount of bloat, compared to simply executing the SELECT directly.

If you were to execute that same SELECT 10,000+ times in a row with varying WHERE predicate changes, then yes, you might squeak out a bit of efficiency in terms of network data/round-trip.

Prepared statements don’t change the way the optimizer works. Turn on optimizer tracing, then EXECUTE a prepared statement. View the trace. EXECUTE again with different statement parameters, view this trace, and you’ll see the optimizer potentially choosing a different execution path.

Prepared statements also do not protect 100% against SQL injection. In some cases, force-casting strings to integer can help, but when you bind strings, you can still technically inject malicious SQL. We have several slides in our MySQL Developers training course where we show this.

Prepared statements are deallocated when the connection closes. Most applications connect to mysql, execute queries, then disconnect, as this is considered best-practice. Thus, by using prepared statements, you’re just wasting memory/cpu/network over, and over by re-preparing the same statement on each connection.

I’m not telling you to stop using them; you do you. What I am saying is that they are absolutely not standard by any means, and rarely offer a performance benefit.

Regarding PMM QAN, the problem is on the MySQL side. PMM QAN scrapes performance_schema.events_statements_history for metrics. Prepared statements are not added to this table by MySQL. PMM is doing all that it can, but if MySQL does not provide the data, there’s nothing we can do about it. I found two open, and verified bugs on this issue dating back to 5.6.

5

@matthewb thank you for the detailed insights. My statement about prepared statements came from a mix up of the terms parameterized and prepared statements on my part. What I actually meant was that parameterized queries are used by everyone (at least in my field: web applications) since most of the frameworks use ORMs that mostly default to using parameterized queries.

Should parameterized queries show up in QAN? If so, then I will need to continue investigating for the cause why I dont see them.

6

parameterized and prepared statements are the same thing. You prepare a parameterized statement, then execute it. In MySQL directly, the parameter is ‘?’, where in PHP or other languages can use “:pram1” or similar.

mysql> PREPARE popworld FROM 'SELECT * FROM city WHERE population > ?';
mysql> SET @a=1000000;
mysql> EXECUTE popworld USING @a;
...
237 rows in set (0.01 sec)
8

No, they won’t because of the 2 MySQL bugs I noted above. It’s not an issue with QAN, it’s a bug in MySQL that prepared statements do not appear in the P_S tables. The 2 solutions are 1) use slow log (which you can’t because RDS), or 2) switch to direct SQL instead of prepared.

1 Like