Why do my queries run longer after Amazon Redshift maintenance?

Short description

During a version upgrade, Amazon Redshift clears its query cache and compilation cache. When you run a query for the first time after the upgrade, the compile time is longer. However, performance gradually improves as Amazon Redshift rebuilds its cache.

Amazon Redshift doesn't change cluster versions for all maintenance window actions. To identify version changes, check the redshift_version column in the SYS_QUERY_HISTORY table.

For more information about the types of cache in Amazon Redshift, see Result caching and Compiled code. For more information about query performance, see Factors affecting query performance.

Resolution

Analyze query performance before and after maintenance

Identify the queries that ran before and after maintenance. You can use the query monitoring console or the SYS_QUERY_HISTORY system view.

To find the last 20 user queries, run the following query:

SELECT * FROM sys_query_history
WHERE user_id>1
ORDER BY start_time desc
limit 20;

In the output, use the user_query_hash column to compare queries with the same query text. Or, use generic_query_hash to compare queries with a similar query text but different query literals.

To view each time that you ran a specific query in the last 7 days, run the following query:

SELECT * FROM sys_query_history
WHERE user_query_hash = 'ExampleText'
ORDER BY start_time desc;

Note: Replace ExampleText with the query hash value.

In the output, compare the compile_time value across different runs. The compile time of a query that you ran immediately after the maintenance might be higher.

To reduce compile times, schedule key queries to run after maintenance or warm up critical queries during non-peak hours.

Note: The query run engine compiles different code for the Java Database Connectivity (JDBC) and Microsoft Open Database Connectivity (ODBC) connection protocols. If you have two clients that use different protocols, then each client incurs the first-time cost to compile the code. However, clients that use the same protocol share the cached code. When you use JDBC to connect to the cluster and run a query, Amazon Redshift saves the cache only for JDBC connections. If you run the same query on a client that uses ODBC, then Amazon Redshift generates another cache for the ODBC connection.

Troubleshoot issues

Sometimes when query compilations show little difference before and after maintenance, an unrelated performance issue can occur. Use the Query and Database Monitoring console to monitor unusual spikes in metrics. Compare compile times to identify the source of the issue.

If you find no spikes but are still experiencing an issue, then create a support case.

Related information

SVL_COMPILE