Definition
Database commands that take longer than 100 ms to execute are recorded in the slow query log. These commands can include query commands, data update commands, or create index commands. Long-running queries are the most common type of slow query log entry. This typically occurs when a query performs a full table scan because an index is not configured correctly. This document refers to these commands as slow queries. Slow queries are a primary cause of poor database performance. When a slow query is logged, you should promptly analyze its details and configure an appropriate index.
If many slow queries occur in a short period, your service will be rate-limited to protect other tenants.
The database kernel tracks the execution duration of commands to identify slow queries. This can result in performance glitches. For example, a query on a table with a small amount of data might be recorded as a slow query. You can safely ignore these types of slow query logs. They will not trigger rate limiting. Focus on optimizing slow queries for tables that contain hundreds of thousands of records or more.
Slow log trends
In the console, you can query slow log trends for a specific time range. You can view statistics for the past week.
Slow Query Log Count curve: Shows the trend in the number of slow query logs per minute.
Scanned Document Count curve: Shows the trend in the total number of documents scanned by slow queries per minute.
Slow log details
In the console, you can query slow query log details. You can view details for the past week. You can filter the logs by collection name. You can also sort the logs by running time, number of documents scanned, or number of index entries scanned.
A slow query log record typically includes the following fields:
Field |
Description |
Collection Name |
The name of the collection on which the command was executed. |
Start Time |
The time when the command started to execute. |
Execution Duration (ms) |
The execution duration of the command. |
Rows Scanned |
The number of data rows that the command scanned in the database.
Note
For a slow query command, if the index is configured correctly, this value should be as close as possible to the number of index entries scanned and the number of rows returned. |
Index Entries Scanned |
The number of index entries that the command scanned in the database.
Note
For a slow query command, if this value is much larger than the number of rows returned, your index is ineffective. |
Rows Returned |
The number of data rows that matched and were returned after the command was executed. |
Slow Log Statement |
The detailed command template. You can use a JSON formatting tool to make it easier to analyze.
Note
For a slow query command, pay special attention to the filter field. For example, ...{"filter":{"userId":"xxx"}}... indicates a query where the condition is userId="xxx". In this case, check whether an index has been created for the userId field. |