The slow query log is used to record the query log with query time exceeding the threshold. When there are a large number of slow queries in the system, the system performance will decrease, the response time will become longer, and the system may even crash. Therefore, slow queries need to be optimized to reduce occurrence and improve system performance.
Log in to the TencentDB for MongoDB console, click the instance ID to enter the Instance Details page, select the System Monitoring tab, and check the instance’s monitoring data. Then, you find that the database latency has increased dramatically. The latency monitoring metric measures the time it takes between when a request arrives at the access layer and when it is processed and returned to the client. For specific monitoring metrics, see Monitoring Feature.
Possible Causes
The query is performed with the $lookup operator but doesn't use indexes or uses indexes that don't support the query; therefore, it is necessary to traverse the entire database for a complete scan, which eventually leads to a very low search efficiency.
Some documents in your collection have many large array fields that are time-consuming to search and index, causing a high system load.
Slow Query Analysis
Slow SQL analysis and slow query troubleshooting based on DBbrain (recommended)
TencentDB for DBbrain (DBbrain) is a database self-management service provided by Tencent Cloud, helping you with database management and the optimization of performance and security. The slow SQL analysis for MongoDB mainly analyzes the slow logs generated by the operations in MongoDB. The specific diagnostic data is intuitive and easy to find. For more information, see Slow SQL Analysis.
Slow log analysis and slow query troubleshooting based on the TencentDB for MongoDB console
Before MongoDB is connected to DBbrain, you can obtain slow logs in the TencentDB for MongoDB console, and analyze key fields one by one to troubleshoot the cause of slow queries.
2. In the MongoDB drop-down list on the left sidebar, select Replica Set Instance or Sharded Cluster Instance. The directions for replica set instances and sharded cluster instances are similar.
3. Above the Instance List on the right, select the region.
4. In the instance list, find the target instance.
5. Click the target instance ID to enter the Instance Details page.
6. Select the Database Management > Slow Log Query tab.
7. Select the Slow Log Query tab to analyze slow logs. The system records operations executed for more than 100 milliseconds and retains slow logs for 7 days. You can also download the slow logs. For detailed directions, see Slow Log Management.
Query statistics: Statistical values after fuzzy processing of query conditions. Here you can see slow query statistics sorted by average execution time. We recommend that you optimize top 5 requests first.
Query details: The complete user execution requests, including execution plan, number of rows scanned, execution time, and lock waits.
Analyzing the key fields in slow logs
You can check the key fields in the slow log. For the meaning of common key fields, see the table below. For more field descriptions, see MongoDB official documentation.
Key Field
Field Description
command
Indicates an operation request recorded in a slow log.
COLLSCAN
Indicates that a full-collection scan is performed for the query. If the number of scanned rows is less than 100, the speed of full-collection scan will be very fast.
IXSCAN
Indicates that an index scan is performed. The index used will be output after this field. It is possible that a collection has multiple indexes. When the indexes here do not meet expectations, you should consider optimizing the indexes or modifying the query statement through hint().
keysExamined
Indicates the entries of the scanned indexes. "keysExined": 0, # MongoDB, meaning the number of index keys that MongoDB scanned to execute the operation is 0.
docsExamined
Indicates the number of documents in the scanned collection.
planSummary
Indicates the summary information used tp describe query execution. Every MongoDB query generates an execution plan that contains details about the query such as indexes used, number of documents scanned, and query execution time. For example: "planSummary" : "IXSCAN { a: 1, _id: -1 }" indicates that MongoDB uses an index scan (IXSCAN) query plan. Specifically, it uses the index named "a" and the default "_id" index, and scans the "a" index in ascending order (1). This is a common query plan, indicating that the query uses an index to obtain the required data.
numYield
Indicates the number of times the operation yielded the lock during execution. When an operation needs to wait for some resource (such as disk I/O or a lock), it may relinquish control of the CPU so that other operations can continue. This process is called "concession". Higher values for "numYield" generally indicate a greater load on the system because operations take more time to complete. Typically, operations that do document searches (queries, updates, and deletes) will yield the locks. It is only possible for an operation to yield its lock if other operations are queued waiting for the lock it holds. By optimizing the number of concessions in the system, the concurrent performance and throughput of the system can be improved, and lock competition can be reduced, thereby improving the stability and reliability of the system.
nreturned
Indicates the number of documents returned by the query request. The larger the value, the more rows are returned. If the "keysExamined" value is large, but "nreturned" returns few documents, indicating that the index needs to be optimized.
millis
Indicates the time it takes from the start to the end of a MongoDB operation. The larger the value, the slower the execution.
IDHACK
Used to speed up query or update operations. In MongoDB, each document has an "_id" field, which is a unique identifier. In some cases, if the "_id" field is included in the query or update operation, MongoDB can use IDHACK technology to speed up the operation. Specifically,
IDHACK technology can take advantage of the special properties of the "_id" field to convert query or update operations into more efficient operations. For example, if the query condition is an exact match of the "_id" value, IDHACK can directly use the "_id" index to find the document without scanning the entire collection.
FETCH
Indicates the number of documents MongoDB reads from disk when performing a query operation. When executing a query operation, MongoDB will read matching documents from disk based on information such as query conditions and indexes. The FETCH field records the number of documents read during this process. Usually, the smaller the value of the FETCH field, the better the query performance. As MongoDB may employ indexing and other methods to reduce the number of times documents are read from disk, query performance is thereby improved.
Solution
Cleaning up slow queries
1. Select Database Management > Slow Query Management, and the list will display the requests being executed by the current instance (including requests of secondary nodes). You can click Batch Kill to kill unnecessary slow query requests. For detailed directions, see Slow Log Management.
2. Use DBbrain'sperformance optimization feature to check unexpected requests, and kill such requests on the Real-Time Session page. For detailed directions, see Real-Time Session.
SQL throttling
For TencentDB for MongoDB 4.0, on the Performance Optimization page in the DBbrain console, select the SQL Throttling tab, and create SQL throttling tasks to control the database requests and SQL concurrency by setting the SQL Type, Max Concurrency, Throttling Duration, and SQL Keyword. For detailed directions and use cases, see SQL Throttling.
Using indexes
If the slow SQL analysis is based on DBbrain, pay attention to whether the number of scanned rows is large in the slow log list. If yes, there are large scanning requests or long-running requests.
For slow queries caused by full-collection scans, create indexes to reduce collection scans and in-memory sorting. For index creation, see MongoDB official documentation.
If an index is used, and the number of scanned rows of the index is 0, but the number of scanned rows is greater than 0, it means that the index needs to be optimized. You can use DBbrain's [index recommendation] (https://www .tencentcloud.com/document/product/1035/48613!768d085d4e01090e2c55bda0ef61bc43) feature to select the optimal index. Index recommendation collects real-time log and slow query information, conducts automatic analysis, recommends the global optimal index, and arranges them according to performance impact. The larger the recommended value, the more significant the performance improvement after the operation.
If it is based on slow log analysis, handle it based on the following situations.
keysExamined = 0, while docsExamined > 0, and planSummary is COLLSCAN, indicating that a full-collection scan has been performed, causing significant query delay. For index creation, see MongoDB official documentation.
keysExamined > 0, while docsExamined > 0, and planSummary is IXSCAN, indicating that some query conditions or returned fields are not included in the index, and index optimization is required. You can use DBbrain's index recommendation feature to select the optimal index.
keysExamined > 0, while docsExamined = 0, and planSummary is IXSCAN, indicating that the query conditions or returned fields are already included in the index. If the keysExamined value is large, it is recommended to optimize the field order of the index, or add a more appropriate index for filtering. For more information, see Optimizing Indexes to Break Through Read/Write Performance Bottlenecks.
For versions earlier than MongoDB 4.2, if there is no problem with the index used for business queries, check whether an index is created in the foreground during peak business hours. If yes, create it in the background.
Note:
Creating an index in the foreground: For versions earlier than MongoDB 4.2, an index is created in the foreground by default for a collection (with the background option being false), which will block all other operations until the index is created in the foreground.
Creating an index in the background: The value of the background option is set to true, MongoDB can still provide read/write services during the creation of the index. However, it takes more time to create the index in this way. For index creation, see MongoDB official documentation.
You can also run the currentOp command to check the progress of index creation. The specific command is as follows:
The returned result is shown as follows. The msg field indicates the progress of index creation. The locks field indicates the lock type of the operation. For more information on locks, see MongoDB official documentation.
Restarting an instance
If there are no slow queries on mongod, but the request latency is high, the problem may be caused by a high mongos load. There are many causes for this; for example, a large number of connections are established in a short time, or data in multiple shards needs to be aggregated. In this case, you can restart Mongos. For detailed directions, see Restarting Instances.
Note:
All instance connections will be interrupted at the moment of restarting mongos, but the business can be directly reconnected.
Upgrading instance specifications
If that doesn't fix the problem, the instance is overloaded beyond what it can normally handle. Pay attention to the read and write requests and latency metrics monitored by the system. Compared with the pressure test data in performance, if the deviation is too large, it is recommended to expand the configuration immediately to avoid affecting normal business.
Yes
No
Was this page helpful?