Db Performance Watchlist

What it does

The Db Performance Watchlist skill monitors key performance metrics for databases, specifically PostgreSQL. It identifies slow-running queries and tracks connection pool usage to help prevent regressions and proactively optimize database performance. The tool provides insights into query duration, resource utilization (CPU, memory, disk), cache hit rates, index usage, and connection pool statistics.

When to use it

• You suspect your database is experiencing performance slowdowns or regressions.
• You want to identify queries that are taking an unusually long time to execute.
• You need to monitor the health and utilization of your database's connection pool.
• You’re proactively seeking ways to optimize database resource usage.
• You want to track cache performance and identify potential index improvements.

Key capabilities

• Slow Query Detection: Identifies queries exceeding a configurable threshold (default 100ms) using PostgreSQL's pg_stat_statements.
• Connection Pool Monitoring: Tracks active, idle, total connections, and maximum connection limits within the database.
• Key Performance Metric Tracking: Monitors metrics including query performance, resource usage (CPU, memory, disk I/O), query statistics (selects, inserts, updates, deletes per second), cache hit rate, and index usage.
• Reporting: Provides detailed reports on slow queries, including duration, call count, average duration, maximum duration, and percentage of total time spent.

Example prompts

• "Run the database performance watchlist to check for slow queries."
• "Show me the current connection pool statistics for the database."
• "What are the top 20 slowest queries running in the last hour?"
• “Check if there are any unused indexes.”

Tips & gotchas

• This skill is designed specifically for PostgreSQL databases.
• The slow query detection feature requires log_min_duration_statement to be configured within the database (the script will attempt this).
• Ensure the agent has appropriate permissions to query system views like pg_stat_statements and pg_settings.