Database Workshop
Database Workshop
Document
- Documentation
Database WorkshopUser guide (veDB for MySQL)Operation and maintenance managementObservation and diagnosisLog managementSlow log
Copy page
Download PDFLog management
Slow log
Copy pageDownload PDFSlow log
This topic describes how to view the analysis results of slow logs and download target slow logs on the Database Workshop (DBW) console.
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
- When you access the Slow log analysis tab through Operation observability on the left navigation bar, slow log information about the first instance in the Instance list is viewed by default.
- Default displays slow log information from the past 10 minutes, such as the number of slow SQL, CPU usage, and Slow SQL list.
- You can view slow log data for up to the past 30 days, with a maximum query time span of 7 days.
- When filtering to view slow SQL queries within a certain period, the query end time must be later than the start time.
- When you filter by time to view the number of slow SQL queries and CPU usage of primary node or read-only node within a specified time, the slow SQL list pane displays the database, executing user, IP source, average execution time (s), maximum lock wait time (s), average rows returned, and first wait time for eachSQL template within the specified time.
- Slow log files downloaded from the system are compressed in .gz (Gzip) format. For macOS users, please extract the downloaded file using the steps below:
- Decompress using the built-in gzip or gunzip command-line tools:Open the Terminal and run the gzip or gunzip commands provided by the system to extract the files. The command format is gzip -d your_filename.gz or gunzip your_filename.gz.
- To decompress the file using the tar tool: Download and install the tar tool to decompress the file. The command format is tar -xz --force-local -f file_name.gz.
- When downloading slow logs, note the following:
- Up to 1 million slow log entries can be downloaded in a single request.
- Slow log download tasks are retained for 1 day by default. Expired tasks will be automatically deleted.
- When you select to download slow log details, you will get the slow SQL that can be executed directly.
- You can view and export slow log download files in Export history.
Query a slow log
- Log on to the DBW console.
- On the top menu bar, select the region.
- On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.
- In the upper-right corner of the Observation and diagnosis page, switch the database Database veDBfor MySQL instance type.
- By default, the information about the first instance in the Instance list page is displayed. Click the drop-down icon in the upper-left corner of the page to select the target instance.
- On the Observation and diagnosis page, click Log management.
- On the Slow log tab, filter the time frame as needed. Select the target node (such as the primary node or a read-only node) from the Node ID drop-down list to view the slow log trends, statistics summary, and details of that node, as shown below.
tip
- If you do not select a node ID, the slow log information of all nodes is displayed by default.
- In the Node ID dropdown, each node shows the number of slow logs within the selected time frame:
- 0 article: Indicates no slow logs during this period, with normal system response, and performance bottlenecks can be ruled out.
- Greater than 0 article: Indicates potential performance issues. It is recommended to further analyze the specific slow SQL.
- When selecting a time range, the query end time must be later than the start time.
- View slow log trends: In the left area of the Slow log details tab, you can view the slow log trends within a certain period. You can also click a specific time point to view the Slow SQL number and CPU usage at that moment.
- View slow log statistics: On the right side of the Slow log tab, you can view the Top 1-day slow SQL locations and Top 7-day slow SQL locations for a certain period.
- View slow log details: In the Slow SQL List section, filter slow SQL templates as needed and view detailed information of slow SQL within the templates. Specific parameters are shown in the table below.
tip
Before viewing slow SQL details on the slow SQL list pane, you need to click the bar graph of a certain point in time in the slow log development trend graph.
-
- After enabling Shard compatible, in the sharding scenario, SQL templates with the same logical database and table names will be aggregated together, and the aggregated SQL template will be displayed.
- After disabling Shard compatible, all SQL templates are displayed in the sharding scenario.
tipIn general, the number of SQL templates after shard compatibility is enabled is less than the number of SQL templates after shard compatibility is disabled.Click Advanced filtering. In the expanded filter area, configure the following parameters as needed, and then click Query.- Aggregation method: Select whether to Aggregate ignore execution User, Aggregate ignore IP source, or Aggregate ignore DB as needed. Multiple options can be selected simultaneously.
By default, aggregation ignores the execution User and IP source. To view by User and IP dimensions, deselect the corresponding aggregation method options here.- Database: Select the target database from the drop-down list. Multiple selections are supported.
- User: Select the target user from the drop-down list. Multiple selections are supported.
- IP: Check the target IPs in the drop-down list. Multiple selections are supported.
- Keywords: Enter the keywords to be queried in the input box, such as select, from, and drop.
- Execution time range: Set the execution time range in the input box. The minimum time range must be less than or equal to the maximum time range.
On the Slow SQL list pane, you can perform the following operations:- View information for each SQL template, including the database name, executing user, IP source, execution times, total time (s), maximum execution time (s), average execution time (s), maximum lock wait time (s), average lock wait time (s), maximum rows returned, average rows returned, maximum rows scaned, average rows scanned, maximum number of affected rows, average rows affected, first appearance time, last appearance time, and table name of each SQL template. You can also click Advanced filtering to view the SQL template that meets the target.
tip- The SQL template column can display up to 3 lines of SQL text.
- The Slow SQL list area displays slow logs from the past 10 minutes by default.
- All SQL commands that meet the template are aggregated within each SQL template.
- Total time (s) refers to the total time of all execution times of the SQL statement in the SQL template, in seconds.
- Maximum execution time (s) and average execution time (s) refer to the maximum execution time and average execution time of a single execution of the SQL statement in the SQL template, in seconds.
- View SQL index optimization suggestions:
- Click SQL optimization in the Actions column of the target SQL template. In the Allow authorization dialog box, click Authorize.
- The SQL 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 enabled, you do not need to perform this operation again.
- It takes approximately 10 to 20 seconds to view SQL optimization suggestions.
- In the SQL optimization suggestions dialog box, view SQL optimization suggestions, including the executed SQL statement, Execution plan, and Optimization suggestions, including Index optimization and SQL rewrite.
tipIf the Automatic index creation function has been enabled in AutoOps, DBW automatically creates indexes for the current slow SQL based on the SQL optimization suggestions. For more information, refer to Automatic index creation management.Click the target SQL template under the SQL template column. In the SQL details panel, you can view relevant parameter information under different tabs:- Execution details: On the Execution details tab, you can view the Start Time, SQL text, Database, Executing user, IP source, Execution time (s), Lock wait time (s), Rows scanned, and Rows returned of each SQL statement. At the same time, you can click the copy icon to copy the SQL text, and click the filter icon after Execution user or IP source to filter User or IP.
- 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 the executed SQL statement, Execution plan, and Optimization suggestions, including Index optimization and SQL rewrite.
tipIf the Automatic index creation function has been enabled in AutoOps, DBW automatically creates indexes for the current slow SQL based on the SQL optimization suggestions. For more information, refer to Automatic index creation management.- 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.
Download a slow log
tip
- Up to 1 million slow log entries can be downloaded in a single request.
- Slow log download tasks are retained for 1 day by default. Expired tasks will be automatically deleted.
- View and export the download file of slow log in the export history.
- Slow log files downloaded from the system are compressed in .gz (Gzip) format. For macOS users, please extract the downloaded file using the steps below:
- Decompress using the built-in gzip or gunzip command-line tools:Open the Terminal and run the gzip or gunzip commands provided by the system to extract the files. The command format is gzip -d your_filename.gz or gunzip your_filename.gz.
- To decompress the file using the tar tool: Download and install the tar tool to decompress the file. The command format is tar -xz --force-local -f file_name.gz.
- Log on to the DBW console.
- On the top menu bar, select the region.
- On the left navigation bar, select Operation and maintenance management > Observation and diagnosis.
- In the upper-right corner of the Observation and diagnosis page, switch the database Database veDBfor MySQL instance type.
- By default, the information about the first instance in the Instance list page is displayed. Click the drop-down icon in the upper-left corner of the page to select the target instance.
- On the Observation and diagnosis page, click Log management.
- In the slow SQL statistics area, select the time period you want to view.
- In the Slow log tab, in the Slow SQL list area, click Advanced filtering to filter the SQL list as needed, and then click the download icon.
- Select the range of slow logs to download as needed. Currently, Export slow log details and Export current page are supported. The specific configurations are as follows:
-
- The exported slow log details include slow SQL that can be executed directly.
- Click Export slow log details. In the Export slow log details dialog box, configure the following parameters.
-
- Click OK.
- On the Slow log details tab, click the download icon, and then click Export history.
- In the Export history dialog box, select the target task whose Status is Generated, and click Download file in the Actions column.
- Click Export current page. In the Export slow logs of the current page dialog box, select the information included in the downloaded slow logs as needed.
- The current download can include SQL template, Database name, Executing user, IP source, Execution times, Total time (s), Maximum execution time (s), Average execution time (s), Maximum lock wait time (s), Average lock wait time (s), Maximum rows returned, average rows returned, maximum rows scaned, average rows scanned, maximum number of affected rows, average rows affected, first appearance time, last appearance time, and Table name.
- Click OK.
-
Last updated: 2026.09.01 16:57:39
All rights reserved © Beijing Volcano Engine Technology Co., Ltd2024
