PostgreSQL-Performance-Tuner-Mcp

Instructions

Implementation Reference

• src/pgtuner_mcp/tools/tools_health.py:650-731 (handler)

  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)

• src/pgtuner_mcp/tools/tools_health.py:632-648 (schema)

  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()
      )

• src/pgtuner_mcp/tools/tools_health.py:603-732 (handler)

  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))