---
Title: Optimize performance
URL Source: https://company-skill.com/p/rds/rds-optimize-performance
Language: en
Description: You want to identify and resolve performance bottlenecks in your ApsaraDB RDS database—such as slow SQL queries, high I/O usage, or inefficient execution plans—and apply optimizations like index…
---

# Optimize performance

Part of **ApsaraDB RDS**. Route queries via `POST https://company-skill.com/api/route`.

## What You Want to Do

You want to identify and resolve performance bottlenecks in your ApsaraDB RDS database—such as slow SQL queries, high I/O usage, or inefficient execution plans—and apply optimizations like index creation or query rewriting.

**Typical User Questions**:
- How do I optimize slow SQL queries?
- Can I enable automatic SQL optimization?
- How to handle long-running queries?

## Decision Tree

Pick the best path for your situation:

- **If** you need to build a custom performance dashboard or automate optimization workflows using code → Use API (go to *rds/rds-monitoring*)
- **If** you are experiencing specific issues like high I/O, inconsistent query times, or metadata lock contention → Use (go to *rds/rds-performance*)
- **If** you want interactive, no-code analysis with automatic suggestions like Automatic Index Creation and Deletion → Use SQL (go to *rds/rds-performance*)
- **Otherwise (default)** → Start with **SQL**, as it provides the most accessible entry point with integrated tools like Database Autonomy Service (DAS) and Performance Insight for general SQL tuning.

## Path Comparison

| Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill |
|------|----------|------------|---------------|------------|----------|-------------|
| SQL | SQL | medium | No | No | Includes Database Autonomy Service (DAS) with Automatic Index Creation and Deletion; Full SQL Statistics requires manual enablement | `rds/guide/rds-performance` |
| I/O | high | No | No | Healthy InnoDB buffer pool hit ratio must be ≥99%; PostgreSQL’s log_min_duration_statement defaults to 1000ms | `rds/troubleshooting/rds-performance` |
| API | high | Yes | Yes | Uses DescribeDBInstancePerformance and DescribeSlowLogs; billed per API request with QPS limit of 100/sec | `rds/api/rds-monitoring` |

## Path Details

### Path 1: SQL

**Best For**: SQL

**Brief Description**: This path leverages ApsaraDB RDS console features powered by Database Autonomy Service (DAS) and CloudDBA. It provides SQL Analysis and Optimization, Slow Query Logs, Performance Insight, Full SQL Statistics, Autonomy Center, and Query Governance—all accessible via UI without coding.

**Key technical facts**:
- Billing: SQLRDSDAS SQL

**When to Use**:
- Need interactive analysis of slow SQL with visual diagnostics
- Want automatic optimization suggestions without writing code
- Wish to apply index recommendations directly in the console
- Require historical SQL execution trends via Full SQL Statistics

**When NOT to Use**:
- Need to build custom performance analysis tools
- Facing specific issues like high I/O or inconsistent execution times
- Require programmatic access to performance data

**Known Limitations**:
- Basic Edition instances only support viewing/exporting Slow Query Logs—no SQL diagnosis or optimization
- Full SQL Statistics is disabled by default and must be manually enabled via SQL Explorer
- Automatic SQL optimization suggestions may take over 20 seconds to appear
- Query Governance is only available for High-availability Edition ApsaraDB RDS for PostgreSQL in mainland China, Hong Kong, and Singapore

### Path 2: Console / Dashboard
**Best For**: I/O

**Brief Description**: This path uses built-in diagnostic commands and console tabs like SQL Explorer and Audit, SHOW PROCESSLIST, and advanced EXPLAIN formats (EXPLAIN (ANALYZE, VERBOSE, BUFFERS) and EXPLAIN FORMAT=JSON) to investigate root causes of performance degradation. It also involves configuring parameters such as `long_query_time` (for MySQL) or `log_min_duration_statement` (for PostgreSQL) to capture slow queries.

**When to Use**:
- Diagnosing high CPU, high I/O, or long-running queries
- Analyzing execution plans to detect full table scans or missing indexes
- Resolving discrepancies between SQL Explorer and slow log timestamps
- Handling metadata lock waits or uncommitted transactions

**When NOT to Use**:
- Only seeking automatic optimization suggestions
- Needing to integrate performance data into external systems
- Avoiding deep SQL or system-level analysis

**Known Limitations**:
- Slow Query Logs are retained for only 7 days by default
- InnoDB buffer pool hit ratio below 99% indicates excessive disk reads
- PostgreSQL’s log_min_duration_statement defaults to 1000 milliseconds
- Large single-transaction UPDATE/DELETE operations generate heavy redo logs and dirty pages, risking I/O overload

### Path 3: API

**Brief Description**: This path uses ApsaraDB RDS Monitoring and Alerts APIs—including DescribeDBInstancePerformance, DescribeAvailableMetrics, DescribeSlowLogs, DescribeErrorLogs, and ModifySQLCollectorPolicy—to programmatically retrieve metrics, manage SQL auditing, and integrate RDS data into custom monitoring stacks.

**Key technical facts**:
- Billing: APIAPI510
- Auth method: Bearer TokenAuthorization: Bearer <your_api_key>
- Regions available: cn-hangzhou, cn-shanghai, cn-beijing

**When to Use**:
- Building custom performance dashboards or alerting systems
- Integrating RDS metrics into existing observability platforms
- Programmatically managing SQL audit policies and log retention
- Querying performance data across multiple RDS instances in bulk

**When NOT to Use**:
- Preferring visual, no-code analysis of slow queries
- Troubleshooting immediate issues like metadata locks or I/O spikes
- Lacking development resources or API familiarity

**Known Limitations**:
- Slow/error logs can only be queried over a max 31-day window
- SQL audit logs (via DescribeSQLLogRecords) limited to 15 days
- Monitoring API QPS capped at 100 requests/second per account
- DescribeSlowLogs max frequency: 10 requests/second
- ModifyDBInstanceMetrics supports up to 30 metric keys

## FAQ

Q: Which path should I start with?
A: Start with **SQL** if you’re unsure—it offers the broadest set of no-code diagnostics via Database Autonomy Service (DAS), Performance Insight, and Automatic Index Creation and Deletion.

Q: What if I need to monitor buffer pool hit ratio but used the API path without checking limitations?
A: You’ll successfully retrieve metrics via DescribeDBInstancePerformance, but if your region isn’t cn-hangzhou/cn-shanghai/cn-beijing, the API may not be available—always verify region support first.

Q: If I have a long-running query due to a metadata lock but chose SQL, what happens?
A: You won’t get actionable insights—the console’s SQL Analysis and Optimization focuses on query patterns, not lock contention. You’d miss critical diagnostics only available via SHOW PROCESSLIST and SQL Explorer and Audit in the troubleshooting path.

Q: If I try to build an automated index tuner using only the console path, will it work?
A: No—you cannot programmatically trigger or integrate Automatic Index Creation and Deletion. The console lacks automation hooks; you’d need DescribeSQLLogRecords and ModifySQLCollectorPolicy from the API path.

Q: Can I use Query Governance on a Basic Edition RDS instance?
A: No—Basic Edition only supports exporting Slow Query Logs. Query Governance requires High-availability Edition PostgreSQL in supported regions (mainland China, Hong Kong, Singapore).

Q: Why might my EXPLAIN (ANALYZE, VERBOSE, BUFFERS) output differ from Performance Insight?
A: Performance Insight shows aggregated trends, while EXPLAIN provides per-execution details. Use both: EXPLAIN for plan correctness, Performance Insight for load impact.

Q: 如果我需要排查因 long_query_time 设置过高而漏掉慢查询的问题，但选择了 SQL 路径，会怎样？
A: 控制台的慢日志功能依赖数据库参数配置。若未通过 SQL 路径中的 SQL Explorer 正确设置 long_query_time（MySQL）或 log_min_duration_statement（PostgreSQL），可能导致慢查询未被记录，从而无法在 DAS 中分析。

Q: 如果我想通过 API 获取可用监控指标列表但未调用 DescribeAvailableMetrics，会怎样？
A: 你将无法知道哪些指标可用于 DescribeDBInstancePerformance，可能导致请求无效指标而失败。DescribeAvailableMetrics 是程序化发现有效指标键的必要前置步骤。

## Related queries

optimize database performance, improve SQL query speed, slow query optimization, diagnose RDS performance, identify missing indexes, automatic SQL tuning, analyze execution plan, handle long-running queries, high I/O troubleshooting, enable auto index, SQL performance analysis, database tuning, quer

---
Part of [ApsaraDB RDS](https://company-skill.com/p/rds.md) · https://company-skill.com/llms.txt