Viewing more SQL text in the Performance Insights dashboard for MySQL

0

"Viewing more SQL text in the Performance Insights dashboard" has instructions for PostgreSQL DB instances under the "Setting the SQL text limit for Amazon RDS for PostgreSQL DB instances" section, but none for MySQL. Does this mean that viewing more sql text is not supported for MySQL, or is it that the documentation just doesn't contain those instructions.

If it's the latter, what is the right way to view more sql text? Is it to increase the size max_digest_length and performance_schame_max_digest_length?

asked 3 years ago2214 views
1 Answer
1

Dear Customer,

Thanks for posting in AWS re:Post. I apologize for the delay in response to this post, I saw it just now and started to dig deep on it.

To-start with, I would like to inform you that by default, each row in the Top Load Items table shows 500 bytes of SQL text for each SQL statement in the Performance Insights dashboard. [1] When a SQL statement is larger than 500 bytes, you can view more of the SQL statement by opening the statement in the ‘Download full SQL’. But, RDS for MySQL has a limits to displays text up to 1,024 bytes and it’s mean for RDS MySQL engine the maximum sql text length is of 1,024 bytes. Thus, even if you downloaded the full SQL statement, it won't have more text than the defined engine limits. Refer->https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.UsingDashboard.SQLTextSize.html

Further, Performance Insights in RDS for MySQL uses Performance Schema. The length of SQL queries in Performance Schema is limited by the variable "performance_schema_max_digest_length". This variable value shows the maximum number of bytes of memory reserved per statement for computation of normalized statement digest values in the Performance Schema.Refer->https://dev.mysql.com/doc/refman/5.7/en/performance-schema-system-variables.html#sysvar_performance_schema_max_digest_length

Although this parameter can be increased up to “1,048,576" bytes, but this will be limited by the parameter "max_digest_length", which is set to 1,024 bytes. Unfortunately, this "max_digest_length" is not currently modifiable in RDS for MySQL and it has a default value of 1024 bytes.

mysql> show variables like '%max_digest_length%'; +-------------------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------------------+-------+ | max_digest_length | 1024 | | performance_schema_max_digest_length| 1024 | +-------------------------------------------------------+-------+

Corresponding to it, any statement above 1,024 bytes will be truncated when logging it into performance schema, and hence, will be shown truncated in Performance Insights. It is the reason why when you after downloading the full SQL from the Performance Insight, the SQL was truncated by 1,204 characters.

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

Please accept my sincere apologies on behalf of AWS for the inconvenience this is causing. Thank you and have a nice day!

AWS
EXPERT
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