Performance Issues with Aurora 5.7.mysql_aurora.2.11.5

Question

I have seen intermittent issues with the Performance of the Select Queries where the same query randomly takes 8-10s. I have enabled Slow query logs and randomly some queries that are simple and use proper indexing even take very long 1 out of 10 times. I have enabled Performance Insights on Aurora Cluster and i can see that topmost query that's causing the load is as below

SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM,  KCU.REFERENCED_TABLE_NAME PKTABLE_NAME, KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM,  KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ, CASE update_rule    WHEN 'RESTRICT' THEN 1   WHEN 'NO ACTION' THEN 3   WHEN 'CASCADE' THEN 0   WHEN 'SET NULL' THEN 2   WHEN 'SET DEFAULT' THEN 4 END UPDATE_RULE, CASE DELETE_RULE  WHEN 'RESTRICT' THEN 1  WHEN 'NO ACTION' THEN 3  WHEN 'CASCADE' THEN 0  WHEN 'SET NULL' THEN 2  WHEN 'SET DEFAULT' THEN 4 END DELETE_RULE, RC.CONSTRAINT_NAME FK_NAME, RC.UNIQUE_CONSTRAINT_NAME PK_NAME,7 DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE KCU.TABLE_SCHEMA = database() AND KCU.TABLE_NAME = 'tbl_user_branch_mapping' ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ

I am sure our application is not running this at all but can't understand how is this being executed and why it is in the Top Sql always.

Aduet_D · Answer

Hello,

After going through the post, I understand that you noticed intermittent performance issues with Select Queries where the same simple query randomly takes a longer time to execute. Slow query logs, and Performance Insights are enabled on Amazon Aurora cluster with proper indexing on the database tables. 
You want to understand how the query, that is not application generated and which is responsible for high database load, is being executed and why is it always in the Top SQL metric.
Please correct me if I misunderstood your query.

Keeping the above concern in mind, below are my observations:

It’s important to note that the performance issues in Amazon Aurora databases aren’t limited to just SELECT statements. While SELECT queries often become the focal point due to their direct impact on read performance, the underlying issues can be more complex. SELECT statements that involve retrieve of large datasets or multiple joins, can slow down database performance.

To know more on this please refer below link:

[+] How do I troubleshoot a slowly running SELECT query in my Amazon Aurora MySQL DB cluster?
https://repost.aws/questions/QUwHfAGKHQQ_KIiihTAGTVaw

In many cases, intermittent issues require deeper troubleshooting, particularly around overall database load, resource allocation and resource contention. High concurrency, long-running transactions and excessive I/O operations can create bottlenecks that affect the database performance.

Please refer to the following links for deeper understanding:

[+] How do I troubleshoot and resolve high CPU utilization on my Amazon RDS for MySQL or Amazon Aurora MySQL instance? 
https://repost.aws/knowledge-center/rds-instance-high-cpu

[+] Troubleshooting workload issues for Aurora MySQL databases
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-troubleshooting-workload.html

Given that you’ve already enabled slow query logs, a crucial next step is to configure and enable audit logs for your Amazon Aurora cluster. While slow query logs allows you to view queries that are running longer than a specified duration, it doesn’t provide any insights on where the query originated from or who is executing those queries.

Enabling Audit logs will allow you to track who is running specific queries and when, which can be crucial for identifying the sources that contribute to a performance degradation. Monitoring these audit logs regularly can provide insights into problematic query behavior that might be impacting performance.

Please refer to the below article for configuring audit logs for Amazon Aurora.

[+] Configuring an audit log to capture database activities for Amazon Aurora with MySQL compatibility
https://aws.amazon.com/blogs/database/configuring-an-audit-log-to-capture-database-activities-for-amazon-rds-for-mysql-and-amazon-aurora-with-mysql-compatibility/

The query in question, is not a system-generated query rather it is a user-generated query. One key indication is that AWS system-generated queries typically don’t use aliases and the table “tbl_user_branch_mapping” referenced in the query is not one of the system tables that RDS/Aurora uses internally.
 
I did some research on the query and the tables involved as such the query retrieves information about foreign key constraints from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS tables defined on the database table “tbl_user_branch_mapping”.
[+] https://dev.mysql.com/doc/refman/8.4/en/information-schema-key-column-usage-table.html
[+] https://dev.mysql.com/doc/refman/8.4/en/information-schema-referential-constraints-table.html

NOTE - It is recommended to review query logs and audit logs to trace the origin of the query and verify who initiated it. This will provide more context and insight into why the query is affecting performance and help in taking corrective actions, like optimizing the query and adjusting resource allocation.

[+] Troubleshooting query performance for Aurora MySQL databases
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-troubleshooting-query.html

I hope you will find this information useful. If you have any further inputs or concerns you may raise a case with AWS support as support will have access to the resources involved and will be able to troubleshoot it better.

Performance Issues with Aurora 5.7.mysql_aurora.2.11.5

Relevant content