Docs

Database Workshop

Log management

Slow log diagnosis

Database Workshop (DBW) provides a slow log diagnosis feature, which analyzes the Top 200 slow logs generated on the previous day and offers diagnostic results and optimization suggestions to help improve database performance and stability.

This topic describes how to enable the slow log diagnosis feature and view the diagnostic results and optimization suggestions on the DBW console.

Function introduction

In slow log analysis, DBW collects slow log information from the database every day. The slow log diagnosis feature analyzes the Top 200 slow log information collected on the previous day around 8:00 A.M. every day and provides diagnostic results.

In the diagnostic results, DBW provides suggestions for index creation and SQL rewriting, as well as recommendations.

tip

The total time consumption ratio is the percentage of the current SQL query time to the total SQL query time in all slow logs collected on the previous day.

Estimated performance improvement times after optimizing a single SQL template = Execution time before optimizing the current SQL template/Execution time after optimizing the current SQL template.

Total time consumption ratio income = Estimated performance improvement times after optimizing a single SQL template * Total time consumption ratio before optimizing the current SQL template.

Recommendation level	Description
Extremely high	If the estimated total time consumption income after optimization is 15% or more, the recommendation level is extremely high.
High	If the estimated total time consumption income after optimization is 10% or more, the recommendation level is high.
Medium	If the estimated total time consumption income after optimization is 5% or more, the recommendation level is medium.
Low	If the estimated total time consumption after optimization is less than 5%, the recommendation level is low.

You can refer to the recommendation level and the total time consumption ratio in the SQL list to make decisions on whether to create indexes and rewrite SQL statements.

Optimization suggestion	Reference factor
Index creation suggestion	Recommendation level, estimated average execution time after optimization(s), estimated performance improvement times after optimization, and total time consumption ratio.
SQL rewrite suggestions	Recommendation level and total time consumption ratio

Fee

The slow log diagnosis feature is currently in free public beta. You will be notified one month before formal charges are applied.

Prerequisites

A veDB for MySQL instance and account have been created. For detailed steps, refer to Creating an instance and Creating an account.

If the target instance has security control enabled, you need to apply for operation and maintenance read-only or read/write permissions for the current account. For more information, see Enable veDB for MySQL instance security control and Apply for veDB for MySQL instance related operation permissions.

Precautions

The slow log diagnosis feature can not analyze slow log information generated on the current day. Therefore, after you enable the slow log diagnosis feature, you can view the diagnosis results after 8:00 A.M. on the next day.

Slow log diagnosis analyzes and diagnoses only the Top 200 slow logs (including SELECT, UPDATE, and DELETE SQL statements) generated by the current instance on a specific day.

The slow log diagnosis feature needs to use SQL information in the database. Therefore, if you enable the slow log diagnosis feature, you are agreeing to collect SQL information in the database by default.

Enable slow log diagnosis

You can enable slow log diagnosis for a single instance on the slow log diagnosis page, or batch enable slow log diagnosis for instances of the same type in the Instance list.

Enable slow log diagnosis for a single instance

Batch enable slow log diagnosis

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the drop-down list in the upper-right corner of the page, select the target database type: veDB for MySQL.

In the upper-left corner of the Operation observability page, click the drop-down icon and select the target instance.

tip

You can switch to the target instance by searching for the instance ID.

On the target instance page, click Log management.

On the Log management tab, click Slow log diagnosis.

If slow log diagnosis is not enabled for the current instance, the page prompts you to enable it immediately.

Click Enable now.

In the Slow log diagnosis form dialog box, set the following parameters.

Parameter	Description
Enable slow log diagnosis	Click Enable to enable slow log diagnosis.
Diagnosis frequency	Once a day and can not be modified. After enabling slow log diagnosis, DBW diagnoses the Top 200 slow logs generated on the previous day around 8 o'clock every day.
Diagnosis scope	All slow logs from the previous day. DBW automatically diagnoses the Top 200 slow logs of the previous day. For example, on June 25, 2025, DBW automatically diagnoses the Top 200 slow logs generated on June 24, 2025, and provides diagnosis results and optimization suggestions.
Diagnostic history retention period	The retention period of the slow log diagnosis result is 30 days by default, and can not be modified.
Retention period for performance tracking	After optimizing SQL based on optimization suggestions in the slow log diagnosis result, DBW continues to track the performance changes before and after SQL optimization. The performance change data before and after SQL optimization is retained for 30 days by default and can not be modified.

Click OK.

Set slow log diagnosis

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the drop-down list in the upper-right corner of the page, select the target database type: veDB for MySQL.

In the upper-right corner of the page, locate the drop-down list and select the target instance.

On the target instance page, click Log management.

On the Log management tab, click Slow log diagnosis.

On the Operation observability tab, click Service settings.

In the Service settings dialog box, you can perform the following operations:

Parameter	Description
Disable slow log diagnosis	Click Disable to disable the slow log diagnosis.
Diagnosis frequency	Once a day and can not be modified. That is, after enabling slow log diagnosis, DBW performs a daily diagnosis of the Top 200 slow logs generated the previous day.
Diagnosis scope	All slow logs from the previous day. DBW automatically diagnoses the Top 200 slow logs of the previous day. For example, on June 25, 2025, DBW automatically diagnoses the Top 200 slow logs generated on June 24, 2025, and provides diagnosis results and optimization suggestions.
Diagnostic history retention period	The retention period of the slow log diagnosis result is 30 days by default, and can not be modified.
Retention period for performance tracking	After optimizing SQL based on optimization suggestions in the slow log diagnosis result, DBW continues to track the performance changes before and after SQL optimization. The performance change data before and after SQL optimization is retained for 30 days by default and can not be modified.

Click OK.

View slow log diagnosis results

Slow log diagnosis analyzes previous slow logs and provides SQL optimization suggestions to help you quickly improve database performance.

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the drop-down list in the upper-right corner of the page, select the target database type: veDB for MySQL.

In the upper-right corner of the page, locate the drop-down list and select the target instance.

On the target instance page, click Log management.

On the Log management tab, click Slow log diagnosis.

The slow log diagnosis page displays the slow log diagnosis results of the previous day by default. You can also set the slow log diagnosis range to view the slow log diagnosis results of a certain day.

Target	Description
Set the slow log diagnosis range	DBW displays slow log diagnostic results from the previous day by default. You can filter slow log diagnosis data for a specific date as needed.
View the overview of diagnostic results	After setting the slow log diagnosis scope, you can view the diagnostic results of slow logs for the specified date, including the Total number of slow SQL diagnoss, Number of SQL statements recommended for optimization, Number of SQL recommended for index creation , Number of SQL with rewrite suggestions, and Number of SQL with no optimization suggestions. tip Total number of slow SQL diagnoses: Only the Top 200 slow log records in the instance are diagnosed. Total number of slow SQL diagnoses = Number of SQL statements recommended for optimization + Number of SQL with no optimization suggestions. Number of SQL statements recommended for optimization = Number of SQL recommended for index creation + Number of SQL with rewrite suggestions.
View the index creation suggestion list	View slow log information by template, including SQL template, Optimization recommendation level, Database, Table name, IP source, Execution user, Execution times, Average rows scanned, Average rows returned, Recommended index SQL text, Average execution time(s), Estimated average execution time after optimization(s), Estimated performance improvement times after optimization, and Total time consumption ratio. View slow log information by index, including Recommended index SQL text, SQL template, Optimization recommendation level, Database, Table name, IP source, Execution user, Execution times, Average rows scanned, Average rows returned, Average execution time(s), and Total time consumption ratio.
View the list of SQL rewrite suggestions	Includes SQL template, Optimization recommendation level, Database, Table name, IP source, Execution user, Execution times, Average rows scanned, Average rows returned, SQL rewrite suggestions, Average execution time(s), and Total time consumption ratio.
View the list of SQL without optimization suggestions	Includes SQL template, Optimization recommendation level, Database, Table name, IP source, Execution user, Execution times, Average rows scanned, Average rows returned, Average execution time(s), Total time consumption ratio, and Suggestions.
View SQL details	In the SQL list, click the name of the SQL template to open the SQL details panel. In the SQL details panel, you can view the parameter information of different tabs: Execution details: On the Execution details tab, you can preform the following operations: View the information of each SQL statement, including the SQL statement's Start time, SQL text, Database, Execution user, IP source, Execution time(s), Lock wait time(s), Rows scanned, and Rows returned. View SQL optimization suggestions: Click SQL optimization in the Actions column of the target SQL text. In the Allow authorization dialog box, click Authorize. The index optimization suggestion function needs to sample and analyze the content of the database and table. Therefore, when you view the index optimization suggestion of SQL for the first time, you need to authorize DBW to read the database and table. If it is already enabled, you do not need to perform this operation again. It takes about 10 to 20 seconds to view SQL index optimization suggestions. In the SQL optimization suggestions dialog box, view SQL optimization suggestions including database name and SQL statement, execution plan, and optimization suggestions, with optimization suggestions including index optimization and SQL rewrite. Execution statistics: On the Execution statistics tab, you can view all SQL text information that meets the filtering criteria in the SQL template. Total time consumption ratio: The proportion of the total time consumption of the current SQL template to the total time consumption of all SQL lists within a certain period of time. Total lock wait time ratio: The ratio of the total lock wait time of the current SQL template to the total lock wait time of all SQL lists within a certain period of time. Percentage of total rows scanned: The ratio of the total number of scanned rows in the current SQL template to the total time consumption of all SQL lists within a certain period of time. Percentage of total rows returned: The ratio of the total number of returned rows in the current SQL template to the total number of returned rows in all SQL lists. SQL execution times: view the trend of slow SQL within the filtered time period. Execution profiling: Displays the execution status of SQL statements in each stage to help you analyze and locate problems. SQL analysis: on the SQL analysis tab, you can view the following information. SQL text: includes all SQL text information within the SQL template; click the copy icon to copy the SQL text. Execution plan: Includes select_type information interpretation, type information interpretation, and extra information interpretation. Optimization suggestions: Includes optimization suggestions for SQL statements. You can refer to these suggestions to optimize your SQL statements.

View optimized performance tracking data

After the slow log diagnosis report is generated, DBW continues to track performance changes of slow logs within 7 days. If you optimize SQL following the optimization suggestions in the slow log diagnosis result, you can view the performance improvement after SQL optimization. The SQL optimize performance tracking data is retained for 30 days by default and cannot be modified.

Log on to the DBW console.

On the top menu bar, select the project and region.

On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.

In the drop-down list in the upper-right corner of the page, select the target database type: veDB for MySQL.

In the upper-right corner of the page, locate the drop-down list and select the target instance.

On the Operation observability page, click Log management.

On the Log management tab, click Slow log diagnosis. The slow log diagnosis page displays the slow log diagnosis results of the previous day by default. You can also set the slow log diagnosis range to view the slow log diagnosis results of a certain day.

Actions	Description
Set the slow log diagnosis range	DBW displays slow log diagnostic results from the previous day by default. You can filter slow log diagnosis data for a specific date as needed.
View optimized performance tracking data	After the slow log diagnosis report is generated, DBW continues to track performance changes of slow logs within 7 days. If you optimize SQL following the optimization suggestions in the slow log diagnosis result, you can view the performance improvement after SQL optimization. You can view inspection execution time, Instance ID, SQL template, suggestions, optimize execution status, whether to create an index, whether slow SQL still exists, actual performance improvement multiple after optimization, difference in execution times before and after optimization, CPU difference (%) before and after optimization, and difference in average execution time before and after optimization in the optimize performance tracking list. tip Actual performance improvement multiple after optimization greater than 1 indicates positive gain after optimization. Difference in execution times before and after optimization being negative indicates that the number of SQL statements counted as slow logs has decreased after optimization. CPU difference before and after optimization being negative means that the instance’s average CPU usage over 24 hours has decreased after optimization. Difference in average execution time before and after optimization being negative indicates that the average SQL execution time has decreased after optimization.

Related operation

To create an index, refer to Creating a lock-free SQL change ticket.

Related APIs

API	Description
ListSlowQueryAdviceApi	Call the ListSlowQueryAdviceApi interface to get detailed slow log diagnosis information.
SlowQueryAdviceTaskHistoryApi	Call the SlowQueryAdviceTaskHistoryApi API to obtain historical slow log diagnosis information.

Last updated: 2025.12.18 11:14:41