Slow Request Locating

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.
   • To connect to a cluster instance, see Connecting to a Cluster Instance.
   • To connect to a replica set instance, see Connecting to a Replica Set Instance.
   • For details about how to connect to a single node instance, see Connecting to a Single Node Instance.

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	Execution completion time of the execution plan
      executionStats.executionStages.inputStage. executionTimeMillisEstimate	Execution completion time of the sub-phase of the 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.