The slow SQL analysis feature calculates, samples, and aggregates slow SQL statements from three dimensions: instance, mongod node, and mongos node. It aggregates slow SQL statistics based on command template and command space, including the number of executions, average execution time, resource consumption (such as CPU), sizes of scan, and result sets. It also supports viewing slow SQL details to help users quickly locate and resolve slow SQL issues.
2. In the left sidebar, choose Performance Optimization, then select the Slow SQL Analysis tab.
3. At the top of the page, select the MongoDB database type and the instance ID to be analyzed.
4. Select the Statistics tab.
5. Select the view dimension.
Instance dimension: Click Instance to view the slow log statistics trend chart, segmented time consumption statistics of slow logs, and slow log list for the instance dimension.
Mongod node dimension: Click Mongod Node, select Full Node Analysis or single node name to view the slow log statistics trend chart, segmented time consumption statistics of slow logs, and slow log list for the mongod node dimension.
Mongos node dimension: Click Mongos Node, select Full Node Analysis or single node name to view the slow log statistics trend chart, segmented time consumption statistics of slow logs, and slow log list for the mongos node dimension.
6. Select a time range. You can select the current day, last 5 minutes, last 10 minutes, last 1 hour, last 3 hours, last 24 hours, last 3 days, or a custom period (up to 30 days ago and with a maximum span of 3 days).
7. View the slow log statistics, segmented time consumption statistics of slow logs, and slow log list.
Slow log statistics trend chart
Slow log statistics focus on the number of slow queries and the cluster's maximum CPU utilization. It can quickly identify CPU usage during periods with a large number of slow queries in the selected time range. This avoids high CPU utilization caused by excessively slow queries, preventing computer lag or no response.
In the trend chart, click the time period with slow logs (which means the histogram), and the view will display the time points and number of slow SQLs generated.
Click a certain time period with slow logs or drag the mouse pointer to select multiple time periods. The segmented time consumption statistics and slow log list will display data information for the selected period in a linked display.
Segmented time consumption statistics of slow logs
It displays the overall time consumption distribution of slow logs in the selected period.
Slow log list
It displays the slow logs in the selected time period based on command space aggregation.
7.1.1 Filter slow logs by namespace.
It supports inclusion or exclusion. After selecting, you can enter one or more namespaces. If you select Include, the relationship between multiple namespaces is OR, and if you select Exclude, the relationship is AND.
7.1.2 View the slow log list. By default, it is sorted in descending order by overall time consumption.
Click a specific aggregated slow log to view the log statistics and details in the pop-up panel on the right.
Statistics tab: It displays the total time consumption, the proportion of scanned lines, the number of scanned lines and the time distribution of this type statement in the selected period.
Details tab: It displays the detailed SQL execution information. For specific operations, see View Slow SQL Details.
Additionally, the slow log list supports the following operations:
Copy or view command templates: Hover over the command template row, and click Copy or View.
Click the list header to sort parameters in ascending or descending order: It includes execution count, total time, average execution time (s), average scan rows, maximum scan rows, average index scan rows, maximum index scan rows, average return rows, and maximum return rows.
7.1.3 Export slow log list.
In the upper right corner of the slow log list, click
to export all the slow log statistics displayed in the current list. The export format is .csv.
2. In the left sidebar, choose Performance Optimization, then select the Slow SQL Analysis tab.
3. At the top of the page, select the MongoDB database type and the instance ID to be analyzed.
4. Select the Details tab.
5. Select a time range. You can select the current day, last 5 minutes, last 10 minutes, last 1 hour, last 3 hours, last 24 hours, last 3 days, or a custom period (up to 30 days ago and with a maximum span of 3 days).
6. (Optional) Filter slow SQL statements by time consumption or namespace.
Time Consumed: It can be greater or less than a specific execution time, or execution time within a particular interval.
Namespace: It can be selected to include or exclude one or more namespaces. If you select Include, the relationship between multiple namespaces is OR, and if you select Exclude, the relationship is AND.
7. View slow SQL details. By default, the order is sorted in descending order by execution time.
Additionally, the slow SQL list supports the following operations:
Copy or view SQL statements: Hover over the SQL statements, and click Copy or View.
Click the list header to sort parameters in ascending or descending order: It includes execution time, scanned indexes, returned rows, and scanned rows.
8. Export slow SQL detail data.
Above the slow SQL list, click
to export all slow SQL details of the current list. The export format is .csv.
Related Operations
If you need to view monitoring metrics details on the Slow SQL Analysis page or compare the monitoring metrics of two time periods, you can click Monitoring Details at the top right of the page.
Select monitoring metrics and the time range, and view the monitoring metric trend chart and list information.
