How can I download the full SQL text from Performance Insight for my Aurora PostgreSQL-Compatible instance?

Short description

Aurora PostgreSQL-Compatible handles text in Performance Insights differently from other engine types, like Aurora MySQL-Compatible. By default, each row under the Top SQL tab on the Performance Insights dashboard shows 500 bytes of SQL text for each SQL statement. When a SQL statement exceeds 500 bytes, you can view more text in the SQL text section that's below the Top SQL table. The maximum length for the text displayed in the SQL text section is 4 KB. If the SQL statement exceeds 4096 characters, then the truncated version is displayed on the SQL text section. But, you can download the full SQL text from the SQL text section of the TOP SQL tab.

The track_activity_query_size DB parameter specifies the amount of memory that's reserved to store the text of the currently running command for each active session. This determines the maximum query length to display in the pg_stat_activity query column. To set the text limit size for SQL statements and store that limit on the database, modify the track_activity_query_size parameter. You can modify this parameter at the instance or cluster parameter group level. See the minimum and maximum allowed values for the text limit size for SQL statements:

Resolution

You can download the full SQL text from Performance Insights using the Amazon Relational Database Service (Amazon RDS) console. If the full SQL text size exceeds the value of track_activity_query_size, then increase the value of track_activity_query_size before you download the SQL text. The track_activity_query_size parameter is static, so you must reboot the cluster after you've changed its value.

For example, the SQL text size might be set to 1 MB, and track_activity_query_size is set to the default value of 4096 bytes. In this case, the full SQL can't be downloaded. When the engine runs the SQL text to Performance Insights, the Amazon RDS console displays only the first 4 KB. Increase the value of track_activity_query_size to 1 MB or larger, and then download the full query. In this case, viewing and downloading the SQL text return a different number of bytes.

In the Performance Insights dashboard, you can view or download the full SQL text by following these steps:

1.    Open the Amazon RDS console.

2.    In the navigation pane, choose Performance Insights.

3.    Choose the DB instance that you want to view Performance Insights for.

4.    From the Top SQL tab, choose the SQL statement that you want to view.

5.    Under the SQL text tab, you can view up to 4,096 bytes for each SQL statement. If the SQL statement falls within this limit, then choose Copy to copy the SQL.

6.    If the SQL statement is larger than 4,096, then it's truncated in this view. Choose Download to download the full SQL.

Note: Be sure that the track_activity_query_size parameter is set to a larger value than the SQL statement that you want to download.

Related information

Viewing Aurora PostgreSQL DB cluster and DB parameters

Rebooting an Aurora cluster (Aurora PostgreSQL and Aurora MySQL before version 2.10)