TencentDB for MongoDB Cause Analysis and Solutions for High Latency Due to Increased Slow Queries-Practical Tutorial-Help & Documentation-Tencent Cloud

Phenomenon Description
Slow logs record queries that exceed the threshold query time. When there are many slow queries in the system, it can lead to decreased system performance, longer response times, and even system crashes. Therefore, it is necessary to optimize slow queries, reduce their number, 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. You will find that the database latency monitoring metrics have significantly increased. Latency monitoring metrics mainly reflect the time from when a request reaches the access layer to when the request is processed and returned to the client. For specific monitoring metrics, refer to the Monitoring Overview.
Possible Reasons
Using the $lookup operator query without an index or with an index that does not support the query requires a complete scan of the entire database, resulting in very low retrieval efficiency.
Some documents in your collection have many large array fields that are time-consuming to search and index, causing a high system load.
Analyzing Slow Queries
Analyzing Slow SQL with TencentDB for DBbrain to Troubleshoot Slow Queries (Recommended)
TencentDB for DBbrain (DBbrain) is a cloud database autonomy service provided by Tencent Cloud for database performance optimization, security, and management. The Slow SQL Analysis for MongoDB is specifically used to analyze slow logs generated during MongoDB operations. The diagnostic data is intuitive and easy to find, as shown in the figure below. For more information, see Slow SQL Analysis.
﻿
Analyzing Slow Queries Based on MongoDB Console's Slow Log
Before MongoDB is connected to TencentDB for DBbrain (DBbrain), you can obtain slow logs from the MongoDB console and analyze key fields one by one to troubleshoot the causes of slow queries.
Retrieving slow logs
1. Log in to the TencentDB for MongoDB console.
2. In the left navigation bar, select MongoDB from the drop-down list, then choose Replica Set Instance or Sharded Instance. The operations for replica set instances and sharded instances are similar.
3. Select Region at the top of the Instance List page on the right.
4. In the Instance List, find the target instance.
5. Click the Target Instance ID to enter Instance Detailspage.
6. Select the Database Management tab, then select the Slow Log Query tab.
7. In the Slow Log Query tab, analyze slow logs. The system logs operations executed for more than 100 milliseconds and retains slow logs for 7 days. It also supports downloading slow log files. For specific operations, see Manage slow logs.
Abstract query: Statistical value after fuzzy processing of query conditions. Here you can see slow query statistics sorted by average execution duration. We recommend optimizing the Top 5 requests first.
Specific query: Records complete user execution requests, including: execution plan, number of scanned rows, execution duration, and some lock wait information.
﻿
Analyzing key fields in slow logs
View key fields in slow logs. For the meaning of common key fields, see the table below. For more field descriptions, see MongoDB Official Website.
Key Fields
Field Description
command
Indicates the operation request recorded in the slow log.
COLLSCAN
Indicates that the query performed a full table scan. If the number of scanned rows is less than 100, the speed of full table scan will also be fast.
IXSCAN 
Indicates an index scan. The specific index used will be output after this field. A table may have multiple indexes, and if the index here does not meet expectations, consider optimizing the index or modifying the query statement using hint().
keysExamined 
Refers to the number of index entries scanned. "keysExamined" : 0, # The number of index keys scanned by MongoDB for the operation is 0.
docsExamined 
Indicates the number of documents scanned in the collection.
planSummary 
Used to describe the summary information of query execution. Each MongoDB query generates an execution plan that contains detailed information about the query, such as the index used, scanned document count, query execution time, etc. For example: "planSummary" : "IXSCAN { a: 1, _id: -1 }" indicates that MongoDB used an index scan (IXSCAN) query plan. Specifically, it used the index named "a" and the default "_id" index, scanning the "a" index in ascending order (1). This is a common query plan, indicating that the query used an index to retrieve the required data.
numYield
This field indicates the number of times the lock is yielded during the operation. When an operation needs to wait for certain resources (e.g., disk I/O or locks), it may relinquish CPU control so that other operations can continue execution. This process is called "yielding." A higher numYield value usually indicates a higher system load, as operations require more time to complete. Typically, document search operations (query, update, and delete) can yield locks. They only yield their locks if other operations are queued waiting for the locks held by this operation. By optimizing the number of yields in the system, you can improve system concurrency and throughput, minimize lock competition, and enhance system stability and reliability.
nreturned
Refers to the number of documents returned by the query request. The larger this value, the more rows are returned. If the keysExamined value is large and nreturned returns few documents, it indicates that the index needs optimization.
millis
The time consumed from the start to the end of the MongoDB operation. The larger this value, the slower the execution.
IDHACK 
Used to accelerate queries or update operations. In MongoDB, each document has an _id field, which is a unique identifier. In some cases, if the query or update operation includes the _id field, MongoDB can use the IDHACK technique to speed up the operation. Specifically, the IDHACK technique can leverage the special nature of the _id field to convert the query or update operation into a more efficient one. 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 
This field indicates the number of documents read from the disk by MongoDB when executing a query operation. When executing a query operation, MongoDB reads matching documents from the disk based on query conditions and index information. The FETCH field records the number of documents read during this process. Generally, the smaller the value of the FETCH field, the better the query performance. This is because MongoDB can utilize indexes and other techniques to minimize the number of documents read from the disk, thereby improving query performance.
Solution:
Cleaning Slow Queries
1. Select Database Management > Slow Query Management tab, 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 specific operations, see Manage Slow Logs.
2. For unexpected requests, you can directly perform the Kill operation in DBbrain (TencentDB for DBbrain, DBbrain) under Diagnosis and Optimization on the Real-Time Session page for cleanup. For specific operations, see Real-Time Session.
SQL Throttling
For TencentDB for MongoDB 4.0, on the DBbrain (TencentDB for DBbrain, DBbrain) Diagnosis and Optimization SQL Throttling page, create an SQL throttling task, set SQL type, maximum concurrency, throttling duration, and SQL keywords to control the request volume and SQL concurrency of the database, ensuring service availability. For specific operations and application cases, see SQL Throttling.
Using Indexes
If based on DBbrain (TencentDB for DBbrain, DBbrain) slow SQL analysis, check if the scan rows are large in the slow log list, indicating large scan requests or long-running requests.
If slow queries caused by full table scans increase, create indexes to reduce collection scans and memory sorting. For creating indexes, see MongoDB official Indexes.
If an index is used but the index scan rows are 0 while the scan rows are greater than 0, the index needs optimization. Use the Index Recommendation feature of DBbrain (TencentDB for DBbrain, DBbrain) to select the optimal index. Index Recommendation collects real-time slow log information for automatic analysis, proposes the globally optimal index, and ranks them by performance impact. The higher the recommendation value, the more significant the performance improvement after the operation.
﻿
If based on slow log analysis, handle it according to the following situations.
keysExamined = 0, while docsExamined > 0, and planSummary is COLLSCAN, indicating a full table scan was performed, causing significant query latency, as shown below. For creating indexes, see MongoDB official Indexes.
If keysExamined > 0 and docsExamined > 0, with planSummary being IXSCAN, it indicates that some query conditions or returned fields are not included in the index, requiring index optimization. Please use the Index Recommendation feature of TencentDB for DBbrain (DBbrain) to select the optimal index.
For key fields where keysExamined > 0 and docsExamined = 0 with planSummary as IXSCAN, it means the query conditions or returned fields are already covered by the index. If the value of keysExamined is large, it is recommended to optimize the order of index fields or add more appropriate indexes for filtering. For more information, see Optimizing Indexes to Break Through Read/Write Performance Bottlenecks.
Thu Mar 24 01:03:01.099 I COMMAND [conn8976420] command tcoverage.ogid_mapping_info command: getMore { getMore: 107924518157, collection: "ogid_mapping_info", $db: "tcoverage" } originatingCommand: { find: "ogid_mapping_info", skip: 0, $readPreference: { mode: "secondaryPreferred" }, $db: "tcoverage" } planSummary: COLLSCAN cursorid:107924518157 keysExamined:0 docsExamined:179370 numYields:1401 nreturned:179370 reslen:16777323 locks:{ Global: { acquireCount: { r: 1402 } }, Database: { acquireCount: { r: 1402 } }, Collection: { acquireCount: { r: 1402 } } } protocol:op_query 102ms
If MongoDB is version 4.2 or earlier and there is no problem with the index used for business queries, check whether an index is created in the foreground during peak business hours and change it to the background mode.
Notes:
Foreground index creation: In MongoDB versions before 4.2, the default method for creating an index on a collection is the foreground method, which sets the background option parameter to false. This operation will block all other operations until the index creation is completed in the foreground.
Creating indexes in the background: Setting the background option to true allows MongoDB to continue providing read-write operation service during index creation. However, creating indexes in the background may lead to longer index creation times. For specific methods of creating indexes, please refer to the MongoDB Official Website.
Create an index in the background, and use the currentOp command to check the current index creation progress. The specific command is as follows.
db.adminCommand(
    {
      currentOp: true,
      $or: [
        { op: "command", "command.createIndexes": { $exists: true }  },
        { op: "none", "msg" : /^Index Build/ }
      ]
    }
)
The return is shown below, the msg field indicates the current index creation progress, and the locks field represents the lock type of the operation. For more information on locks, refer to the MongoDB Official Website. 
﻿
﻿
Restarting an Instance
If there are no slow operations 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 these cases, you can restart mongos. For specific operations, refer to Restarting an Instance.
Notes:
All instance connections will be interrupted at the moment of restarting mongos, but the business can be directly reconnected.
Expanding Specifications
If the problem still cannot be resolved, the instance load will exceed normal processing capacity. Pay attention to the system monitoring of read and write requests and latency metrics, and compare with the stress test data in Performance Data. If the deviation is too large, it is recommended to upgrade the configuration immediately to avoid affecting normal business.
Mongod node: Upgrade the CPU and memory configuration of mongod. For more information, see Adjust the mongod node specification.
Mongos node: If mongos reaches a bottleneck, upgrade the mongos node specification or increase mongos quantity. For specific operations, see Adjust the mongos node specification and Adding Mongos Node.
If slow logs accumulate too quickly and the problem cannot be resolved, please contact after-sales support or Submit a Ticket.
﻿

Key Fields	Field Description
command	Indicates the operation request recorded in the slow log.
COLLSCAN	Indicates that the query performed a full table scan. If the number of scanned rows is less than 100, the speed of full table scan will also be fast.
IXSCAN	Indicates an index scan. The specific index used will be output after this field. A table may have multiple indexes, and if the index here does not meet expectations, consider optimizing the index or modifying the query statement using hint().
keysExamined	Refers to the number of index entries scanned. "keysExamined" : 0, # The number of index keys scanned by MongoDB for the operation is 0.
docsExamined	Indicates the number of documents scanned in the collection.
planSummary	Used to describe the summary information of query execution. Each MongoDB query generates an execution plan that contains detailed information about the query, such as the index used, scanned document count, query execution time, etc. For example: "planSummary" : "IXSCAN { a: 1, _id: -1 }" indicates that MongoDB used an index scan (IXSCAN) query plan. Specifically, it used the index named "a" and the default "_id" index, scanning the "a" index in ascending order (1). This is a common query plan, indicating that the query used an index to retrieve the required data.
numYield	This field indicates the number of times the lock is yielded during the operation. When an operation needs to wait for certain resources (e.g., disk I/O or locks), it may relinquish CPU control so that other operations can continue execution. This process is called "yielding." A higher numYield value usually indicates a higher system load, as operations require more time to complete. Typically, document search operations (query, update, and delete) can yield locks. They only yield their locks if other operations are queued waiting for the locks held by this operation. By optimizing the number of yields in the system, you can improve system concurrency and throughput, minimize lock competition, and enhance system stability and reliability.
nreturned	Refers to the number of documents returned by the query request. The larger this value, the more rows are returned. If the keysExamined value is large and nreturned returns few documents, it indicates that the index needs optimization.
millis	The time consumed from the start to the end of the MongoDB operation. The larger this value, the slower the execution.
IDHACK	Used to accelerate queries or update operations. In MongoDB, each document has an _id field, which is a unique identifier. In some cases, if the query or update operation includes the _id field, MongoDB can use the IDHACK technique to speed up the operation. Specifically, the IDHACK technique can leverage the special nature of the _id field to convert the query or update operation into a more efficient one. 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	This field indicates the number of documents read from the disk by MongoDB when executing a query operation. When executing a query operation, MongoDB reads matching documents from the disk based on query conditions and index information. The FETCH field records the number of documents read during this process. Generally, the smaller the value of the FETCH field, the better the query performance. This is because MongoDB can utilize indexes and other techniques to minimize the number of documents read from the disk, thereby improving query performance.

Cause Analysis and Solutions for High Latency Due to Increased Slow Queries

On this page:

Phenomenon Description

Possible Reasons

Analyzing Slow Queries

Analyzing Slow SQL with TencentDB for DBbrain to Troubleshoot Slow Queries (Recommended)

Analyzing Slow Queries Based on MongoDB Console's Slow Log

Retrieving slow logs

Analyzing key fields in slow logs

Solution:

Cleaning Slow Queries

SQL Throttling

Using Indexes

Restarting an Instance

Expanding Specifications