analyze_wait_events
Identify PostgreSQL performance bottlenecks by analyzing wait events for client backend processes, helping detect I/O issues, lock contention, and resource saturation in application queries.
Instructions
Analyze PostgreSQL wait events to identify bottlenecks.
Note: This tool focuses on client backend processes and excludes system background processes to help identify bottlenecks in your application queries.
Wait events indicate what processes are waiting for:
Lock: Waiting for locks on tables/rows
IO: Waiting for disk I/O
CPU: Waiting for CPU time
Client: Waiting for client communication
Extension: Waiting in extension code
This helps identify:
I/O bottlenecks
Lock contention patterns
Resource saturation
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| active_only | No | Only include active (running) queries |
Implementation Reference
- The run_tool method in WaitEventsToolHandler class that implements the core logic of the 'analyze_wait_events' tool. It queries pg_stat_activity for current wait events on client backends, groups them by type and event, analyzes for issues like lock contention or I/O waits, and returns structured JSON output with recommendations.async def run_tool(self, arguments: dict[str, Any]) -> Sequence[TextContent]: try: active_only = arguments.get("active_only", True) # Get user filter for excluding specific user IDs user_filter = get_user_filter() activity_filter = user_filter.get_activity_filter() state_filter = "AND state = 'active'" if active_only else "" # Current wait events query = f""" SELECT wait_event_type, wait_event, COUNT(*) as count, array_agg(DISTINCT pid) as pids FROM pg_stat_activity WHERE wait_event IS NOT NULL AND backend_type = 'client backend' {state_filter} {activity_filter} GROUP BY wait_event_type, wait_event ORDER BY count DESC """ result = await self.sql_driver.execute_query(query) # Detailed breakdown by wait type type_query = f""" SELECT wait_event_type, COUNT(*) as count FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND backend_type = 'client backend' {state_filter} {activity_filter} GROUP BY wait_event_type ORDER BY count DESC """ type_result = await self.sql_driver.execute_query(type_query) analysis = { "issues": [], "recommendations": [] } # Analyze wait types if type_result: for row in type_result: wait_type = row.get("wait_event_type") count = row.get("count", 0) if wait_type == "Lock" and count > 5: analysis["issues"].append(f"{count} processes waiting on locks") analysis["recommendations"].append( "Investigate lock contention using pg_locks and pg_blocking_pids()" ) elif wait_type == "IO" and count > 10: analysis["issues"].append(f"{count} processes waiting on I/O") analysis["recommendations"].append( "Consider tuning I/O settings or increasing shared_buffers" ) elif wait_type == "BufferPin" and count > 0: analysis["issues"].append(f"{count} processes waiting on buffer pins") analysis["recommendations"].append( "This may indicate contention on frequently accessed pages" ) output = { "wait_events": result, "by_type": type_result, "analysis": analysis } return self.format_json_result(output) except Exception as e: return self.format_error(e)
- The get_tool_definition method defining the tool schema, including name 'analyze_wait_events', description, input schema with 'active_only' parameter, and annotations.def get_tool_definition(self) -> Tool: return Tool( name=self.name, description=self.description, inputSchema={ "type": "object", "properties": { "active_only": { "type": "boolean", "description": "Only include active (running) queries", "default": True } }, "required": [] }, annotations=self.get_annotations() )
- The complete WaitEventsToolHandler class that defines and implements the 'analyze_wait_events' tool, including name, description, schema, and execution logic.class WaitEventsToolHandler(ToolHandler): """Tool handler for analyzing wait events.""" name = "analyze_wait_events" title = "Wait Events Analyzer" read_only_hint = True destructive_hint = False idempotent_hint = True open_world_hint = False description = """Analyze PostgreSQL wait events to identify bottlenecks. Note: This tool focuses on client backend processes and excludes system background processes to help identify bottlenecks in your application queries. Wait events indicate what processes are waiting for: - Lock: Waiting for locks on tables/rows - IO: Waiting for disk I/O - CPU: Waiting for CPU time - Client: Waiting for client communication - Extension: Waiting in extension code This helps identify: - I/O bottlenecks - Lock contention patterns - Resource saturation""" def __init__(self, sql_driver: SqlDriver): self.sql_driver = sql_driver def get_tool_definition(self) -> Tool: return Tool( name=self.name, description=self.description, inputSchema={ "type": "object", "properties": { "active_only": { "type": "boolean", "description": "Only include active (running) queries", "default": True } }, "required": [] }, annotations=self.get_annotations() ) async def run_tool(self, arguments: dict[str, Any]) -> Sequence[TextContent]: try: active_only = arguments.get("active_only", True) # Get user filter for excluding specific user IDs user_filter = get_user_filter() activity_filter = user_filter.get_activity_filter() state_filter = "AND state = 'active'" if active_only else "" # Current wait events query = f""" SELECT wait_event_type, wait_event, COUNT(*) as count, array_agg(DISTINCT pid) as pids FROM pg_stat_activity WHERE wait_event IS NOT NULL AND backend_type = 'client backend' {state_filter} {activity_filter} GROUP BY wait_event_type, wait_event ORDER BY count DESC """ result = await self.sql_driver.execute_query(query) # Detailed breakdown by wait type type_query = f""" SELECT wait_event_type, COUNT(*) as count FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND backend_type = 'client backend' {state_filter} {activity_filter} GROUP BY wait_event_type ORDER BY count DESC """ type_result = await self.sql_driver.execute_query(type_query) analysis = { "issues": [], "recommendations": [] } # Analyze wait types if type_result: for row in type_result: wait_type = row.get("wait_event_type") count = row.get("count", 0) if wait_type == "Lock" and count > 5: analysis["issues"].append(f"{count} processes waiting on locks") analysis["recommendations"].append( "Investigate lock contention using pg_locks and pg_blocking_pids()" ) elif wait_type == "IO" and count > 10: analysis["issues"].append(f"{count} processes waiting on I/O") analysis["recommendations"].append( "Consider tuning I/O settings or increasing shared_buffers" ) elif wait_type == "BufferPin" and count > 0: analysis["issues"].append(f"{count} processes waiting on buffer pins") analysis["recommendations"].append( "This may indicate contention on frequently accessed pages" ) output = { "wait_events": result, "by_type": type_result, "analysis": analysis } return self.format_json_result(output) except Exception as e: return self.format_error(e)
- src/pgtuner_mcp/server.py:136-168 (registration)The register_all_tools function where WaitEventsToolHandler is instantiated with the SQL driver and registered via add_tool_handler at line 161. This central registry adds the tool to the MCP server's tool_handlers dict.def register_all_tools() -> None: """ Register all available tool handlers. This function serves as the central registry for all tools. New tool handlers should be added here for automatic registration. """ driver = get_sql_driver() hypopg_service = HypoPGService(driver) index_advisor = IndexAdvisor(driver) # Performance analysis tools add_tool_handler(GetSlowQueriesToolHandler(driver)) add_tool_handler(AnalyzeQueryToolHandler(driver)) add_tool_handler(TableStatsToolHandler(driver)) # Index tuning tools add_tool_handler(IndexAdvisorToolHandler(index_advisor)) add_tool_handler(ExplainQueryToolHandler(driver, hypopg_service)) add_tool_handler(HypoPGToolHandler(hypopg_service)) add_tool_handler(UnusedIndexesToolHandler(driver)) # Database health tools add_tool_handler(DatabaseHealthToolHandler(driver)) add_tool_handler(ActiveQueriesToolHandler(driver)) add_tool_handler(WaitEventsToolHandler(driver)) add_tool_handler(DatabaseSettingsToolHandler(driver)) # Bloat detection tools (using pgstattuple extension) add_tool_handler(TableBloatToolHandler(driver)) add_tool_handler(IndexBloatToolHandler(driver)) add_tool_handler(DatabaseBloatSummaryToolHandler(driver))