polardb-daily-check

# PolarDB Daily Check Agent This skill guides the agent in conducting a thorough daily health check of a PolarDB for PostgreSQL database instance. It extends the PostgreSQL daily check capabilities with PolarDB-specific checks for its unique architecture (Shared-Storage, LogIndex, HTAP/MPP). ## Purpose The primary goal of this skill is to empower the agent to proactively monitor the health and performance of PolarDB for PostgreSQL, leveraging its unique features like storage-compute separation, LogIndex, and HTAP capabilities. It performs routine inspections efficiently with PolarDB-specific insights. ## Core Capabilities The agent performs checks across several key areas: * **PolarDB Core Health**: Node type verification, LogIndex status, PFS storage usage * **Availability & Health**: Standard PostgreSQL health checks (invalid indexes, XID wraparound, blocking locks) * **Performance & Activity**: Active sessions, long-running queries, cache efficiency, MPP parallel query performance * **HTAP & MPP**: Parallel query statistics, worker status, coordinator coordination * **Storage & I/O**: Shared storage performance, PolarFS usage, buffer pool status * **Replication & Consistency**: Primary-readonly node sync status, LogIndex replay lag ## PolarDB-Specific Checks ### LogIndex Architecture PolarDB uses **LogIndex** to maintain page replay history for read-only nodes, solving the "past page" problem. Key checks include: - LogIndex replay status and latency - WAL metadata consistency between primary and read-only nodes - Lazy and Parallel replay efficiency ### HTAP & MPP Architecture PolarDB supports HTAP via distributed MPP execution engine: - **PxScan** and **Shuffle** operators for parallel execution - Coordinator-Worker coordination for skew elimination - Serverless elastic scaling of compute nodes - `polar_enable_px` parameter controls MPP functionality ### Storage-Compute Separation In this architecture: - **Storage**: Shared PolarFS, scales independently - **Compute**: Multiple read-write and read-only nodes, stateless - **Benefits**: No data replication during scaling, cost-effective read replicas ## Workflow When activated, this skill executes a predefined sequence of checks: 1. First runs PolarDB-specific checks (node type, LogIndex, PFS) 2. Then runs standard PostgreSQL compatibility checks 3. Generates a comprehensive Markdown report with PolarDB-specific recommendations ## Available Skills Each item below represents a callable skill, returning structured JSON output. --- ## 1. PolarDB Core Health ### Skill: `get_polar_node_type` - **Description**: Checks the current PolarDB node type and role using `polar_node_type()` function. - **Usage**: `./run_polardb_check.sh get_polar_node_type` - **Expected Output**: ```json { "skill": "get_polar_node_type", "status": "success", "data": [ { "node_type": "Primary", "is_writable": true, "polar_version": "2.0" } ] } ``` - **Analysis**: Reports the node role (Primary/ReadOnly) and write capability. ### Skill: `get_logindex_status` - **Description**: Monitors LogIndex replay status and lag between primary and read-only nodes. - **Usage**: `./run_polardb_check.sh get_logindex_status` - **Expected Output**: ```json { "skill": "get_logindex_status", "status": "success", "data": [ { "node_role": "ReadOnly", "replay_lag_mb": 15, "replay_lag_seconds": 2, "pending_wal_count": 50 } ] } ``` - **Analysis**: WARNING if replay lag >100MB or >10 seconds. CRITICAL if lag >1GB. ### Skill: `get_pfs_usage` - **Description**: Reports Polar File System (PFS) storage usage using `pfs_du_with_depth()` and `pfs_info()`. - **Usage**: `./run_polardb_check.sh get_pfs_usage` - **Expected Output**: ```json { "skill": "get_pfs_usage", "status": "success", "data": [ { "total_size_gb": 500, "used_size_gb": 350, "used_percentage": 70, "file_count": 15000 } ] } ``` - **Analysis**: WARNING if usage >80%, CRITICAL if >90%. ### Skill: `get_polar_process_status` - **Description**: Detailed process information using `polar_stat_process()` - PID, wait events, I/O stats, CPU, RSS. - **Usage**: `./run_polardb_check.sh get_polar_process_status` - **Expected Output**: ```json { "skill": "get_polar_process_status", "status": "success", "data": [ { "pid": 1234, "state": "active", "wait_event": "ClientRead", "cpu_user": 5.2, "cpu_system": 1.5, "rss_mb": 2048, "shared_storage_read_iops": 150, "shared_storage_read_throughput_mbps": 50, "shared_storage_read_latency_ms": 0.5 } ] } ``` - **Analysis**: Reports detailed resource utilization per process. ### Skill: `get_polar_activity` - **Description**: Enhanced activity view combining `pg_stat_activity` and `polar_stat_process()`. - **Usage**: `./run_polardb_check.sh get_polar_activity` - **Expected Output**: ```json { "skill": "get_polar_activity", "status": "success", "data": [ { "pid": 1234, "usename": "app_user", "state": "active", "query": "SELECT * FROM orders WHERE...", "duration": "00:00:05", "wait_event": "IO polarfs", "shared_io": true } ] } ``` - **Analysis**: Enhanced monitoring with PolarDB-specific wait events. --- ## 2. HTAP & MPP Checks ### Skill: `get_px_workers_status` - **Description**: Checks MPP parallel query worker status and configuration. - **Usage**: `./run_polardb_check.sh get_px_workers_status` - **Expected Output**: ```json { "skill": "get_px_workers_status", "status": "success", "data": [ { "polar_enable_px": true, "polar_px_max_workers_number": 64, "polar_px_dop_per_node": 8, "active_px_queries": 3, "total_px_workers": 24 } ] } ``` - **Analysis**: Reports MPP configuration and active parallel queries. ### Skill: `get_px_query_stats` - **Description**: Statistics on MPP parallel query execution and performance. - **Usage**: `./run_polardb_check.sh get_px_query_stats` - **Expected Output**: ```json { "skill": "get_px_query_stats", "status": "success", "data": [ { "query_type": "PxScan", "execution_count": 1500, "avg_execution_time_ms": 45, "total_rows_scanned": 5000000000 } ] } ``` - **Analysis**: Monitors HTAP workload performance. ### Skill: `get_px_nodes` - **Description**: Lists nodes participating in MPP execution via `polar_px_nodes`. - **Usage**: `./run_polardb_check.sh get_px_nodes` - **Expected Output**: ```json { "skill": "get_px_nodes", "status": "success", "data": [ {"node_id": 1, "node_name": "primary", "is_coordinator": true}, {"node_id": 2, "node_name": "readonly1", "is_worker": true}, {"node_id": 3, "node_name": "readonly2", "is_worker": true} ] } ``` - **Analysis**: Shows MPP cluster topology. ### Skill: `get_buffer_pool_affinity` - **Description**: Checks Buffer Pool affinity settings and effectiveness for MPP operations. - **Usage**: `./run_polardb_check.sh get_buffer_pool_affinity` - **Expected Output**: ```json { "skill": "get_buffer_pool_affinity", "status": "success", "data": [ { "buffer_hit_ratio": 99.5, "local_buffer_usage": 80, "shared_buffer_usage": 75 } ] } ``` - **Analysis**: Reports buffer efficiency for shared storage access. --- ## 3. Storage & I/O Performance ### Skill: `get_shared_storage_stats` - **Description**: Shared storage I/O performance metrics (IOPS, throughput, latency). - **Usage**: `./run_polardb_check.sh get_shared_storage_stats` - **Expected Output**: ```json { "skill": "get_shared_storage_stats", "status": "success", "data": [ { "read_iops": 5000, "write_iops": 2000, "read_throughput_mbps": 200, "write_throughput_mbps": 100, "read_latency_ms": 0.3, "write_latency_ms": 0.5 } ] } ``` - **Analysis**: WARNING if latency >5ms, CRITICAL if >10ms. ### Skill: `get_polar_io_stats` - **Description**: Detailed I/O statistics from PolarDB-specific monitoring. - **Usage**: `./run_polardb_check.sh get_polar_io_stats` - **Expected Output**: ```json { "skill": "get_polar_io_stats", "status": "success", "data": [ { "polarfs_read_count": 1000000, "polarfs_write_count": 500000, "polarfs_read_bytes": "50 GB", "polarfs_write_bytes": "25 GB", "polarfs_iops": 8000, "polarfs_throughput_mbps": 350 } ] } ``` - **Analysis**: Detailed PolarFS I/O patterns. ### Skill: `get_dirty_page_status` - **Description**: Dirty page flush status and coordination with read-only nodes. - **Usage**: `./run_polardb_check.sh get_dirty_page_status` - **Expected Output**: ```json { "skill": "get_dirty_page_status", "status": "success", "data": [ { "dirty_pages_count": 5000, "dirty_bytes_mb": 200, "flush_rate_pages_per_sec": 1000, "oldest_modified_age": 120 } ] } ``` - **Analysis**: Monitors primary node dirty page flushing coordination. --- ## 4. High Availability & Consistency ### Skill: `get_primary_readonly_sync` - **Description**: Synchronization status between primary and read-only nodes. - **Usage**: `./run_polardb_check.sh get_primary_readonly_sync` - **Expected Output**: ```json { "skill": "get_primary_readonly_sync", "status": "success", "data": [ { "primary_node": "node_primary", "readonly_nodes": [ { "node_name": "node_readonly1", "sync_status": "streaming", "lag_bytes": 5242880,