Performance Insight is a Database Autonomy Service (DAS) feature that analyzes SQL execution data from MySQL's performance_schema to help you pinpoint the root cause of performance issues and take corrective action. It also tracks tables and indexes with no traffic so you can clean up unused objects and reclaim storage.
Prerequisites
Before you begin, ensure that you have:
A database instance of one of the following types:
ApsaraDB RDS for MySQL (Basic Edition and version 5.5 are not supported)
MyBase for MySQL
PolarDB for MySQL
A database instance with 8 GB or more of memory
performance_schemaenabled on the instance. If it is not enabled, DAS prompts you to configure it when you enable Performance Insight. For setup instructions, see Enable Performance Schema.Enabling
performance_schemaconsumes some instance resources. For details, see the Performance Schema stress test report.A database instance in one of the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), or Indonesia (Jakarta)
Billing
Performance Insight is a value-added feature of DAS Economy Edition and Enterprise Edition V3. Charges apply based on the edition you activate.
For supported databases and regions, see Databases and regions supported by different editions.
For pricing details, see Billing details.
In regions where Economy Edition and Enterprise Edition V3 are not yet available, Performance Insight is in a free public preview. The free public preview ends when these editions become available in that region.
Key concepts
Understanding the four core metrics helps you interpret the data in Performance Insight:
| Metric | What it measures |
|---|---|
| Resource Usage | The share of database resources consumed by each SQL statement — your primary indicator for identifying high-impact queries |
| Executions | How many times a SQL statement ran in the selected time range |
| Scanned Rows | The number of rows MySQL examined to produce the result — a high value relative to returned rows often indicates a missing or inefficient index |
| Execution Duration | The total or average time the statement spent executing, including lock waits and I/O |
The Display by Time Range tab exposes these four metrics per SQL statement. The database-level trend graphs add deeper dimensions: Logic Read, Physical Reads, Average Execution Duration, Number of Created Temporary Tables, Number of Sorted Rows, Select_Scan/Select_Range/Full Join/Full Range Join/Sort_Scan/Sort_Range, and Top 5 SQL Trends.
How it works
Performance Insight reads aggregated SQL data from MySQL's performance_schema. Each row in the SQL list represents a unique SQL pattern (identified by SQL ID), aggregated across all executions within the time range you select. Click a SQL ID to drill down into trends and individual samples.
DAS also collects table and index usage data in the early morning every day to determine which objects have had no traffic. After 7 days of data collection, the Tables/Indexes tab shows objects that have been idle.
Enable Performance Insight
Log on to the DAS console.
In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.
Find the target instance and click the instance ID to open the instance details page.
In the left navigation pane, click Performance Insight.
If you have not activated DAS O&M Service, click Enable O&M Service. On the Activate O&M Service page, select a Service Duration and click Activate.
On the Performance Insight tab, click Enable Performance Insight. If
performance_schemais not enabled, the Parameter Settings dialog box appears. Click Configure and follow the instructions in Enable Performance Schema. When Performance Insight is enabled, the feature that identifies tables and indexes with no traffic is also enabled automatically.WarningModifying
performance_schemaparameters requires a database instance restart. Perform this operation during off-peak hours and make sure your application has a reconnection mechanism.ImportantAs of April 11, 2023, the no-traffic identification feature is enabled automatically when you enable Performance Insight. For instances where Performance Insight was enabled before April 11, 2023, manually enable Identification for Tables and Indexes with No Traffic in Service Settings.
Investigate performance issues
Once Performance Insight is enabled, use the three tabs on the Performance Insight page to investigate issues.
Display by Time Range
Use this tab to identify which SQL statements are driving database load in a specific time window.
Typical diagnostic workflow:
In Quick View, select a metric (Resource Usage, Executions, Scanned Rows, Execution Duration, or Average Active Sessions) and set the time range. The trend graph shows how that metric changed over time.
The end time must be later than the start time. You can query any seven-day window within the last month.
Click Show to expand the database-level metric trend graphs. To choose which metrics appear, click Settings and select metrics in the Metrics area.
In the SQL list below the graphs, identify the SQL statements with the highest values for your chosen metric. To customize which columns appear, click Settings and select metrics in the Column Settings area.
Click a SQL ID to view its trend graphs for Resource Usage, Executions, Scanned Rows, and Execution Duration, along with SQL sample data.
To view individual execution samples for a statement, click Sample in the Actions column.
To reduce the impact of a high-load SQL statement immediately, click Throttling in the Actions column. Configure the throttling parameters on the SQL Throttling page. For details, see SQL throttling.
To get an optimization suggestion for a SQL statement, click Optimize in the Actions column. DAS analyzes the statement based on its complexity, the data volume of the underlying table, and current database load — this may take more than 20 seconds. The SQL Diagnostic Optimization dialog box shows the diagnostic result, the optimization suggestion, and the expected improvement. To apply the suggestion, click Copy, then paste the optimized SQL into a database client or DMS and run it. You can also click Expert Service to purchase the Database Expert Service, which provides professional database support including emergency response, health diagnostics, performance tuning, dedicated support, and data migration.
To save the SQL list to a local file, click Export.
Display by Comparison
Use this tab to compare SQL performance across two different time periods — for example, before and after a schema change or deployment.
Select the two time points to compare. The SQL list shows side-by-side values for Resource Usage, Executions, Scanned Rows, and Execution Duration. To customize which columns appear, click Settings.
Tables/Indexes
Use this tab to find tables and indexes that have had no traffic. Set filter conditions to query idle tables and indexes.
DAS collects usage data every morning. After you enable Performance Insight, at least 7 days must pass before data appears on this tab.
New version vs. old version
| Performance Insight (New Version) | Performance Insight (Old Version) | |
|---|---|---|
| Metric dimensions | Per-SQL: Resource Usage, Executions, Scanned Rows, Execution Duration. Per-database: Logic Read, Physical Reads, Average Execution Duration, Number of Created Temporary Tables, Number of Sorted Rows, Select_Scan/Select_Range/Full Join/Full Range Join/Sort_Scan/Sort_Range, Top 5 SQL Trends | Per-database: Memory Usage/CPU Utilization, Session, Traffic Throughput, IOPS. Per-SQL: average active sessions trend and multi-dimensional load |
| Viewing methods | Time range view, comparison view, no-traffic tables and indexes | Time range view only |
| Management | SQL throttling and SQL optimization | SQL optimization only |
| Supported engines | ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL | ApsaraDB RDS for MySQL, ApsaraDB RDS for PostgreSQL, PolarDB for MySQL, PolarDB for PostgreSQL, MyBase for MySQL, PolarDB for PostgreSQL (Compatible with Oracle) |
Disable Performance Insight
Log on to the DAS console.
In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.
Find the target instance and click the instance ID to open the instance details page.
In the left navigation pane, click Performance Insight.
On the Performance Insight tab, click Service Settings. Turn off Feature Setting and click OK. In the confirmation dialog box, click OK.
Disabling Performance Insight also disables no-traffic identification. To disable only no-traffic identification without turning off Performance Insight, turn off Identification for Tables and Indexes with No Traffic in Service Settings. Disabling this feature deletes the data for tables and indexes with no traffic, along with their usage data.
After you disable Performance Insight, DAS stops data ingestion and deletes all historical data, including no-traffic table and index data.
What's next
Enable DAS autonomy features to handle performance issues automatically: