Data Warehousing on AWS Training Monitoring and Troubleshooting 2 — Questions and Answers
Question 1: How can you identify which user is running a long-running query that is blocking other queries in Redshift?
- Check the AWS IAM audit log
- Query STV_INFLIGHT joined with SVV_TRANSACTIONS to find the blocking session and user (Correct answer)
- Review the CloudWatch CPUUtilization metric
- Check the VPC Flow Logs for the cluster
Correct answer: Query STV_INFLIGHT joined with SVV_TRANSACTIONS to find the blocking session and user
Joining STV_INFLIGHT with SVV_TRANSACTIONS shows active queries, their lock status, and the associated user and session ID.
Question 2: Which Redshift command terminates a currently running query without disconnecting the user session?
- KILL SESSION
- CANCEL query_id (Correct answer)
- TERMINATE query_id
- ABORT query_id
Correct answer: CANCEL query_id
The CANCEL command stops a specific running query identified by its query ID while leaving the user session active.
Question 3: What does the SVV_TABLE_INFO view in Redshift provide that is useful for troubleshooting storage issues?
- Real-time query execution plans
- Table-level statistics including size, skew, and the percent of unsorted rows (Correct answer)
- Column-level data type information
- Index usage statistics per table
Correct answer: Table-level statistics including size, skew, and the percent of unsorted rows
SVV_TABLE_INFO shows per-table metadata including size, distribution skew ratio, and the percentage of rows that are unsorted.
Question 4: Which Amazon Redshift metric should alert you to a potential skewed distribution key problem?
- DatabaseConnections per node
- NodeSliceDiskSpaceUsed variance across nodes (Correct answer)
- CPUUtilization average
- NetworkReceiveThroughput total
Correct answer: NodeSliceDiskSpaceUsed variance across nodes
Uneven NodeSliceDiskSpaceUsed across cluster nodes indicates data skew where some nodes hold disproportionately more data.
Question 5: What is the purpose of the pg_cancel_backend() function in Amazon Redshift?
- Cancels a pending cluster resize
- Terminates a specific backend process (query) by process ID (Correct answer)
- Revokes a user's connection privileges
- Rolls back an active transaction
Correct answer: Terminates a specific backend process (query) by process ID
pg_cancel_backend(pid) sends a cancellation signal to the specified backend process ID, terminating its current query.
Question 6: How does Amazon Redshift audit logging help with troubleshooting and compliance?
- It automatically rotates IAM credentials
- It records connection attempts, user activity, and SQL queries to S3 for review (Correct answer)
- It triggers Lambda functions on suspicious queries
- It encrypts query results before writing to disk
Correct answer: It records connection attempts, user activity, and SQL queries to S3 for review
Audit logging writes connection log, user activity log, and user log records to S3, providing a full history for security and troubleshooting.
Data Warehousing on AWS Training Monitoring and Troubleshooting 2
How can you identify which user is running a long-running query that is blocking other queries in Redshift?