Google Professional Cloud Database Engineer Question 131
Select 4Google Cloud PlatformYou are managing a Cloud SQL instance running PostgreSQL, and your application is experiencing slow query performance. Upon investigation, you notice high CPU utilization and long query execution times. Which actions should you take to investigate and resolve potential database resource contention issues?
- A
Analyze the 'pg_stat_activity' view to identify long-running queries and their states.
- B
Increase the size of the instance's CPU and memory resources immediately to mitigate the issue.
- C
Use Query Insights in the Google Cloud Console to identify resource-intensive queries and examine their execution plans.
- D
Check for table or index bloat using PostgreSQL maintenance tools like 'pg_repack' and address it if necessary.
- E
Investigate the database's connection pool configuration to ensure it is optimized for the workload.
Show answer and explanation
Correct answers: A, C, D, E
Explanation
When investigating database resource contention, it's important to identify the root cause rather than immediately scaling up resources, which may only provide a temporary solution. Key actions include analyzing the 'pg_stat_activity' view for long-running or blocking queries, using Query Insights to identify and optimize slow or resource-intensive queries, addressing table or index bloat, and ensuring the connection pool configuration aligns with the workload. These actions collectively help diagnose and resolve contention effectively.
- A. Correct.
The 'pg_stat_activity' view is a key PostgreSQL tool that helps identify long-running queries, their current states, and potential blocking issues, which are common causes of resource contention.
- B. Incorrect.
While increasing CPU and memory resources can temporarily mitigate performance issues, it does not address the root cause of resource contention and should not be the first action taken.
- C. Correct.
Query Insights is a powerful feature in Google Cloud that provides detailed analytics for query performance, including execution plans and resource usage, making it essential for identifying problematic queries.
- D. Correct.
Table or index bloat can lead to inefficient resource utilization and slow query performance. Tools like 'pg_repack' can help mitigate these issues by reorganizing bloated tables or indexes.
- E. Correct.
Optimizing the database connection pool ensures that the application is not overloading the database with too many concurrent connections, which can lead to contention and degraded performance.