Debug slow AI job querying database

Scenario

When a PAI training or inference job shows high io_wait or prolonged GPU idle time, the bottleneck typically stems from data ingestion rather than compute. This workflow correlates PAI resource telemetry with OceanBase query diagnostics to isolate slow SQL, apply targeted optimizations, and restore pipeline throughput.

Integration steps

1. Retrieve PAI job metrics: Use the PAI CLI to pull compute and I/O stats for your TrainingJobId.

``bash pai-cli training-job describe --job-id <TrainingJobId> --metrics cpu,gpu,io_wait ` Flag jobs where io_wait > 40%` during the data-loading epoch.

2. Extract the offending SQL: Fetch job logs via the PAI API endpoint /api/v1/jobs/{TrainingJobId}/logs. Filter for JDBC execution traces and copy the exact query string.
3. Analyze execution plan in OceanBase: Connect to your cluster and run:

``sql EXPLAIN SELECT * FROM training_data WHERE feature_ts BETWEEN '2024-01-01' AND '2024-01-02'; ` Look for table_scan or high cost` in the plan output.

4. Verify runtime performance: Query OceanBase’s audit view to confirm scan type and latency:

``sql SELECT query_sql, elapsed_time, scan_type FROM oceanbase.GV$OB_SQL_AUDIT WHERE query_sql LIKE '%training_data%'; ``

5. Apply optimization: Add a covering index aligned with filter predicates, then clear stale plans:

``sql CREATE INDEX idx_feature_ts ON training_data(feature_ts, label_id); ALTER SYSTEM FLUSH PLAN CACHE; ``

6. Validate: Restart the PAI job. Re-run step 1 and confirm io_wait drops below 15% and GPU utilization stabilizes.

Architecture

PAI orchestrates compute workloads and pulls training batches via JDBC/ODBC. OceanBase executes SQL queries, manages indexes, and streams result sets back to PAI containers. Monitoring flows bidirectionally: PAI emits node-level metrics to CloudMonitor, while OceanBase exposes query execution plans and audit trails. The integration bridges PAI’s I/O telemetry with OceanBase’s SQL diagnostics to isolate data-fetch latency.

Prerequisites

• Active PAI workspace with CloudMonitor metrics enabled
• OceanBase cluster (v4.0+) with oceanbase system DB accessible
• IAM roles: AliyunPAIFullAccess + AliyunOceanBaseDBAccess
• JDBC driver pre-installed in PAI container image
• pai-cli and mysql-client available in your debug environment

Common pitfalls

• Network vs. Query Latency: High PAI io_wait may originate from VPC routing, not slow SQL. Always cross-check OceanBase elapsed_time before rewriting queries.
• Plan Cache Staleness: Index creation doesn’t auto-invalidate cached plans. Skipping FLUSH PLAN CACHE forces the optimizer to reuse the original full-scan path.
• Implicit Type Conversion: Comparing mismatched types (e.g., VARCHAR vs INT) bypasses indexes. Ensure PAI query parameters exactly match OceanBase column definitions.
• Connection Pool Exhaustion: PAI workers can spawn thousands of concurrent DB sessions, triggering OceanBase thread contention. Implement connection pooling and tune max_connections.

Typical questions

• AI job slow querying database
• PAI job reading from OceanBase is slow
• training job bottleneck database
• debug AI job slow SQL
• GPU idle waiting on database
• AI训练任务查询数据库慢
• PAI任务读取OceanBase慢
• 训练任务SQL性能问题