We use MySQL 5.7 as our default DB at work through Amazon's Relational Database Service. RDS has a "Performance Insights" dashboard, which is described as:
a database performance tuning and monitoring feature that helps you quickly assess the load on your database, and determine when and where to take action. - https://aws.amazon.com/rds/performance-insights
The performance insights dashboard shows the top 10 queries being run against the DB at a specific time interval, an extremeley useful tool for assessing the most expensive queries and can highlight where optimization should happen. Unfortunately, we are only able to see 1024 characters from the SQL query. These 1024 characters are quickly consumed when you have a query selecting many things across mutliple tables. Especially when queries can look extremely similar.
AWS has instructions for how to increase the length of the ouputted SQL for PostgreSQL, but not for MySQL. What makes it really frustrating is that they don't mention whether this omittion is because increasing the SQL length is not supported for MySQL or because nobody bothered with the documentation.
Because of this discrepancy, I did a little bit of digging. MySQL has a max_digest_length parameter, along with others, that it used to set the maximum bytes of memory reserved for the computation of a normalized SQL statement.
I decided to test this out, and created a new parameter group with the following settings:
- max_digest_length -> 4096
- performance_schema_max_digest_length -> 4096
- performance_schema_max_sql_text_length -> 4096
I applied the new parameter group to our development DB and reset the server. This is where the surprise occured. No change happened to our Performance Insights dashboard for the dev DB. The parameter group still showed that these settings were set to 4096, however, when I looked in the DB, this was the result:
mysql-dev-readonly> show variables like '%max_digest_length%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| max_digest_length | 4096 |
| performance_schema_max_digest_length | 1024 |
+--------------------------------------+-------+
No clue why! I guess parameter groups are just a suggestion.