tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for MongoDB
    Documentation
    Download PDF

    Cause Analysis and Solutions for Increased Slow Queries and Elevated Latency

    Last updated: 2024-05-07 16:15:02
    Download PDF

      Phenomenon Description

      A slow query log is used to record queries that surpass a predefined execution time threshold. When there is a large volume of slow queries in the system, it can lead to decreased system performance, longer response times, and may even cause a system crash. Therefore, it is necessary to optimize slow queries, reduce their quantity, and improve system performance.
      Log in to TencentDB for MongoDB console. Click Instance ID to enter the Instance Details page, select the System Monitoring tab, and examine the instance's monitoring data. It has been observed that latency-related monitoring metrics have notably increased. The latency monitoring metrics mainly reflect the duration from when a request arrives at the access layer until it completes processing and is returned to the client. For specific monitoring items, see Monitoring Overview.

      Possible Causes

      Query through the $lookup operator without using an index or using an unsupported index. It requires traversing the entire database for a full scan, leading to low retrieval efficiency.
      Documents within certain collections contain numerous large array fields that are extensively searched and indexed, leading to excessively large datasets being queried and indexed, consequently causing high system loads.

      Analyzing Slow Queries

      Slow SQL Analysis based on TencentDB for DBbrain (DBbrain) to Troubleshoot Slow Queries (Recommended)

      Launched by Tencent Cloud, DBbrain is a cloud-based database management service for database performance optimization, security, and management. Specifically, it offers slow SQL analysis for MongoDB, aiming at analyzing slow logs generated during MongoDB operations. The diagnostic data is intuitive and easy to find, as shown in the following figure. For more information, see Slow SQL Analysis.
      

      Analyzing Slow Queries Using Slow Logs through the MongoDB Console

      Before integrating MongoDB with DBbrain, you can retrieve slow logs in the MongoDB console and analyze key fields one by one to identify the reasons for slow queries.

      Retrieveing Slow Logs

      1. Log in to the MongoDB console.
      2. In the drop-down list of MongoDB in the left sidebar, select either Replica Set Instance or Sharded Instance. The operations for both types of 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 tab, and then select the Slow Log Query tab.
      7. On the Slow Log Query tab, analyze the slow logs. The system will record operations executed over 100 milliseconds. Slow logs are retained for 7 days. And downloading slow log files is supported. For detailed operations, see Manage Slow Logs.
      Abstract query: After the query conditions have undergone fuzzy processing, this presents statistical data on slow queries sorted by their average execution time. It is recommended to initially focus on optimizing the top 5 requests.
      Detailed query: Records the full details of user-executed requests, including: the execution plan, the number of rows scanned, the execution duration, as well as certain lock waiting and related information.
      

      Analyzing Slow Log Key Fields

      View key fields in the slow logs. For the meanings of common key fields, see the table below. For more field descriptions, see MongoDB official website.
      Key Fields
      Description
      command
      Indicates the request operation recorded in the slow log.
      COLLSCAN
      Indicates that a full table scan was performed for the query. If the number of scanned rows is below 100, the speed of the full table scan will also be fast.
      IXSCAN
      Indicates that an index scan was performed. The specific index used will be listed after this field. A table may have multiple indexes. When the index here does not meet expectations, it should be considered to optimize the index or restructure the query statement with hint().
      keysExamined
      Indicates the scanning of index entries. "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
      Indicates the summary information of query execution. Each MongoDB query will generate an execution plan, which contains detailed information about the query, such as the used index, the number of documents scanned, the execution time of the query, etc. For example, "planSummary": "IXSCAN { a: 1, _id: -1 }" indicates a MongoDB query plan using index scan (IXSCAN). Specifically, it uses the index named a and the default "_id" index, scanning the a index in ascending order (1). This is a common query plan, indicating the query used the index to retrieve the required data.
      numYield
      Indicates the number of times an operation yields locks during its execution. When an operation needs to wait for certain resources (such as disk I/O or locks), it may relinquish CPU control, allowing other operations to continue. This process is referred to as "yielding". The higher the value of numYield, the heavier the system load is generally indicated, as operations take more time to complete. Typically, operations involving document searches (like querying, updating, and deleting) can yield locks. An operation can only yield its lock when other operations are queued waiting for the lock it holds. By optimizing the number of yields in the system, concurrency performance and throughput can be improved, and lock contention reduced, thereby enhancing system stability and reliability.
      nreturned
      Indicates the number of documents returned by a query request. The larger this value, the more rows are returned. If the keysExamined value is large but nreturned returns few documents, it indicates that the index may need optimization.
      millis
      Indicates the time it takes for a MongoDB operation to complete from start to finish. The larger this value, the slower the execution.
      IDHACK
      Used to accelerate query 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 technology to speed up the operation. Specifically, IDHACK technology can take advantage of the unique properties of the _id field to convert query or update operations into more efficient ones. 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 needing to scan the entire collection.
      FETCH
      Indicates the number of documents MongoDB reads from disk during the execution of a query operation. During a query operation, MongoDB reads matching documents from 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. Because MongoDB can make full use of technologies like index to reduce the number of times documents are read from disk, thereby improving query performance.

      Solutions

      Cleaning Up Slow Queries

      1. Select the Database Management > Slow Query Management tab. The list will display the current instance's executing requests (including those from secondary nodes). You can click Batch Kill to kill unwanted slow query requests. For detailed operations, see Slow Log Management.
      2. For unexpected requests, you can directly perform a kill operation in the Real-Time Session page of the Diagnostic Optimization in DBbrain (TencentDB for DBbrain, DBbrain) to clean them up. For detailed operations, see Real-Time Session.

      SQL Throttling

      For TencentDB for MongoDB 4.0, in the SQL Throttling page of Diagnostic Optimization in the DBbrain, you can create an SQL throttling task to autonomously set SQL types, maximum concurrency, throttling duration, and SQL keywords to control the database's request volume and SQL concurrency, thereby achieving service availability. For detailed operations and application cases, see SQL Throttling.

      Using Index

      For slow SQL analysis based on the DBbrain, pay attention to the number of rows scanned in the slow log list. A large number may indicate requests with large scans or long-running requests.
      An increase in slow queries due to full table scans can be reduced by creating indexes to lessen collection scans, memory sorting, etc. For index creation, see the official MongoDB Indexes.
      If an index is used, and the number of index scans is 0, while the number of actual rows scanned is greater than 0, this indicates that the index needs optimization. Through the Index Recommendation of DBbrain, choose the optimal index. Index recommendations are generated by automatic analysis through real-time log slow query data collection. They offer the globally optimal indexes and rank them by the impact on performance. A larger recommendation value indicates more significant performance improvements after implementation.
      
      For analysis based on slow logs, handle according to the following situations.
      keysExamined = 0, while docsExamined > 0, and planSummary is COLLSCAN, indicating a full table scan that significantly delays queries, as shown below. For index creation, see the official MongoDB Indexes.
      keysExamined > 0, while docsExamined > 0, and planSummary is IXSCAN, indicating that some query conditions or returned fields are not included in the index, necessitating index optimization. Please use the Index Recommendation of DBbrain to choose the optimal index.
      For key field keysExamined > 0, while docsExamined = 0 and planSummary is IXSCAN, indicating that the query conditions or returned fields are already covered by the index. If keysExamined value is high, consider optimizing the order of fields in the index or adding a more suitable index for filtering. For more information, see Index Optimization to Solve Read/Write Performance Bottleneck.
      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" } originatingCom%!s(<nil>)mand: { 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 you are using a MongoDB version earlier than 4.2 and have confirmed there is no issue with the index used for business queries, check whether an index was created in foreground mode during peak business hours. Switch to background mode.
      Note:
      Index creation in foreground mode: Before MongoDB version 4.2, the default mode for index creation in a collection was foreground. It sets the background option's value to false. This operation blocks all other operations until the index creation in the foreground is completed.
      Index creation in background mode: Set the background option's value to true. It allows MongoDB to continue providing read-write operation services during the index creation process. However, creating an index in the background may extend the time it takes to create the index. For specific methods of creating an index, visit TencentDB for MongoDB's official website.
      To create an index in background mode, use the currentOp command to view the current progress of the index creation. The specific command is as shown below.
      db.adminCommand(
    {
      currentOp: true,
      $or: [
        { op: "command", "command.createIndexes": { $exists: true }  },
        { op: "none", "msg" : /^Index Build/ }
      ]
    }
)
      Return as shown in the following figure. The msg field indicates the current progress of the index creation. The locks field indicates the lock type for the operation. For more information on locks, visit TencentDB for MongoDB's official website.
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy