Feature request: show full MySQL statements in Performance Insights

0

Currently, I've noticed there is no real option to view full MySQL statements in Performance Insights.

Download option gives file with the same truncated statement even though the UI shows "To view the full SQL statement, choose Download." (It also shows "If the SQL statement exceeds 4096 characters, it is truncated" - and in principal only 1024 characters are being shown)

I also enlarged digest max_digest_length / performance_schema_max_digest_length / performance_schema_max_sql_text_length to 50K but alas.

The only workaround is to query performance_schema directly with the SQL Digest ID shown in Performance Insights.

3 Answers
1

As pointed by you correctly, there is no option to view full MySQL statement i.e more than 1024 characters in Performance Insights.

Also, we cannot change the max_digest_length to a greater value, as it is not modifiable in Aurora MySQL. So, it is set to 1024 by default.

To confirm this behaviour, I ran a quick test in my lab environment and cannot find the parameter, also I tried to modify the parameter using the set command as follows and you can see that this parameter looks to be a read only parameter and can’t be modified

mysql> show variables like 'max_digest_length';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| max_digest_length | 1024 |
+-------------------+-------+
1 row in set (0.07 sec)

mysql> set max_digest_length = 2048; ERROR 1238 (HY000): Variable 'max_digest_length' is a read only variable

I will also like to confirm your understanding regarding these parameters, that once the MySQL engine collects the digest it is forwarded to performance schema, and it makes a copy of the digest value, using the performance_schema_max_digest_length.

For more details you can refer this link:

[+] https://dev.mysql.com/doc/refman/5.7/en/performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length

For example: With max_digest_length=1024, if you set performance_schema_max_digest_length value to larger value say 2048 the digest you get will still be 1024 size only, as max_digest_length is 1024.

Therefore, statement above 1024 bytes will be truncated when logging it into performance schema, and hence, will be shown truncated in Performance Insights.

Consequently, if performance_schema_max_digest_length is less than max_digest_length, digest values stored in the Performance Schema are truncated relative to the original digest values.

Furthermore, I would like to highlight one of the reasons why it not modifiable is because increasing this value over the default causes high memory consumption specially for workloads that involve large numbers of simultaneous sessions.

Also, this is a known issue and the AWS RDS / Aurora Engineering team is actively working to fix this issue. And whenever this feature is released, it will be notified on one of the following public channels:

[+] AWS Forums: https://forums.aws.amazon.com/forum.jspa?forumID=60
[+] RDS Release Notes - https://aws.amazon.com/releasenotes/Amazon-RDS
[+] RDS/DMS What's New - https://aws.amazon.com/new/

AWS
SUPPORT ENGINEER
answered 2 years ago
0

I'm using RDS for MySQL (but I assume Aurora for MySQL is no different) and I was able to change those max length parameters on a global level only in the DB param group (change requires a restart).

And as I wrote, I was able to get the full query from performance_schema (events_statements_* tables, SQL_TEXT column), so I expect Performance Insights to be able to as well and to allow me to access it conveniently through its UI.

answered 2 years ago
0

I completely understand your expectations, and as explained above the max length parameters present in parameter group helps engine to create copy of the digest value forwarded to performance_schema, and the MySQL statements shown in Performance Insight are truncated based on the parameter max_digest_length which is set to it’s default value i.e 1024.

And as pointed by you correctly for now till this feature request is implemented, as a workaround you can directly query the performance_scherma to get the full MySQL statements with the help of SQL Digest ID shown in Performance Insights.

I would like to mention that this is a known feature request and our internal team is working to fix this, but for now there is no visibility on ETA that when this feature will be released.

Also, I would ilke to inform you that the Internal Team and the development teams take time to integrate new functionalities and fix bugs when it comes to a service as vast as Aurora / RDS. It requires thorough testing and checks before being implemented.

AWS
SUPPORT ENGINEER
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions