Updated on 2022-09-16 GMT+08:00

Slow Request Locating

In the same service scenario, the query performance depends on the design of the architecture, databases, collections, and indexes. A good design can improve the query performance. On the contrary, a large number of slow queries (statements that take a long time to execute) may occur, which deteriorates system performance.

This document describes the causes and solutions of slow queries.

Fault Locating

DDS allows you to view slow query logs on the console. You can start from the slowest operation recorded in the log and optimize the operations one by one.

  • If a query takes longer than 1s, the corresponding operation may be abnormal. You need to analyze the problem based on the actual situation.
  • If a query takes longer than 10s, the operation needs to be optimized.

    If an aggregate operation takes more than 10s, it is normal.

Analysis Method

  1. Connect to the database.

  2. Run the following command to check the execution plan of a slow query:

    explain()

    Example:

    db.test.find({"data_id" : "ae4b5769-896f-465c-9fbd-3fd2f3357637"}).explain();
    db.test.find({"data_id" : "775f57c2-b63e-45d7-b581-3822dba231b4"}).explain("executionStats");

    A covered query does not need to read a document, but directly returns a result from an index, which is very efficient. You can use covering indexes as much as possible. If the output of explain() shows that indexOnly is true, the query is covered by an index.

  3. Parse the execution plan.

    1. Check the execution time.

      The smaller the values of the following parameters, the better the performance: executionStats.executionStages.executionTimeMillisEstimate and executionStats.executionStages.inputStage. executionTimeMillisEstimate

      Table 1 Parameter description

      Parameter

      Description

      executionStats.executionTimeMillis

      Execution plan selection and execution time

      executionStats.executionStages.executionTimeMillisEstimate

      Completion time of the optimal execution plan

      executionStats.executionStages.inputStage. executionTimeMillisEstimate

      Execution completion time of the sub-phase of the optimal execution plan

    2. Check the number of scanned records.

      If the three items in Table 2 have the same value, the query performance is the best.

      Table 2 Parameter description

      Parameter

      Description

      executionStats. nReturned

      Number of documents matching the search criteria

      executionStats .totalKeysExamined

      Number of rows scanned through indexes

      executionStats .totalDocsExamined

      Number of scanned documents

    3. Check the stage status.
      The combinations of stage statuses with better performance are as follows:
      • Fetch+IDHACK
      • Fetch+ixscan,
      • Limit+ (Fetch+ixscan)
      • PROJECTION+ixscan
      Table 3 Status description

      Status Name

      Description

      COLLSCAN

      Full table scan

      SORT

      In-memory sorting

      IDHACK

      _id-based query

      TEXT

      Full-text index

      COUNTSCAN

      Number of unused indexes

      FETCH

      Index scanning

      LIMIT

      Using Limit to limit the number of returned records

      SUBPLA

      $or query stage without using an index

      PROJECTION

      Number of used indexes

      COUNT_SCAN

      Number of used indexes

Optimization Plan

  • For queries without indexes, create indexes based on the search criteria.
  • Hash indexes can be created for point queries.
  • Create composite indexes for multi-field queries where a single field is highly repeated.
  • Create an ascending or descending index for range lookups with ordered result sets.
  • Compound indexes are those indexes sort query results by prefix, so the sequence of query conditions must be the same as that of index fields.
  • For partitioned collections (tables) and large collections (with more than 100,000 records), do not use fuzzy query (or do not use LIKE) for tables with a large amount of data. As a result, a large number of records are scanned. You can query data based on the index field, filter out small collections, and then perform fuzzy queries.
  • Do not use $not. MongoDB does not index missing data. The $not query requires that all records be scanned in a single result collection. If $not is the only query condition, a full table scan will be performed on the collection.
  • If you use $and, put the conditions with the fewest matches before other conditions. If you use $or, put the conditions with the more matches first.
  • Check the performance baseline of instance specifications and analyze whether the current service requirements can be met. If the performance bottleneck of the current instance is reached, upgrade the instance specifications in a timely manner.