Updated on 2024-05-30 GMT+08:00

Database Performance

  • Full table scan is not allowed.
  • During the query, select only the fields that need to be returned. In this way, the network and thread processing loads are reduced. If you need to modify data, modify only the fields that need to be modified. Do not directly modify the entire object.
  • Do not use $not. GeminiDB Mongo 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.
  • In a single instance, the total number of databases cannot exceed 200, and the total number of collections cannot exceed 500. If the number of collections is too large, the memory may be overloaded. In addition, the performance may deteriorate when a device restarts or an active/standby switchover is triggered, which affects the high availability performance in emergencies.
  • Before bringing a service online, perform a load test to measure the performance of the database in peak hours.
  • Do not execute a large number of concurrent transactions at the same time or leave a transaction uncommitted for a long time.
  • Before rolling out services, check the performance of all query types through the execution of query plans.

    During the development process, each execution on a collection must be checked using explain() to view its execution plan.

    Example:

    db.T_DeviceData.find({"deviceId" : "ae4b5769-896f"}).explain();

    db.T_DeviceData.find({"deviceId" : "77557c2-31b4"}).explain("executionStats");

    A covered query does not have to read a document and returns a result from an index, so using a covered query can greatly improve query efficiency. If the output of explain() shows that indexOnly is true, the query is covered by an index.

    Execution plan parsing:

    • Check the execution time. The smaller the values of the following parameters, the better the performance: executionStats.executionStages.executionTimeMillisEstimate and executionStats.executionStages.inputStage. executionTimeMillisEstimate
      • executionStats.executionTimeMillis specifies how much time the database took to both select and execute the winning plan.
      • executionStats.executionStages.executionTimeMillisEstimate indicates the execution completion time of the winning plan.
      • executionStats.executionStages.inputStage. executionTimeMillisEstimate indicates the execution completion time of the child stage of the winning plan.
    • Check the number of scanned records. If the three items are the same, the index is best used.
      • executionStats. nReturned indicates the number of documents that match the query condition.
      • executionStats .totalKeysExamined indicates the number of scanned index entries.
      • executionStats .totalDocsExamined indicates the number of scanned document entries.
    • Check the stage status (combination of stages with good performance)
      • Fetch+IDHACK
      • Fetch+ixscan
      • Limit+ (Fetch+ixscan)
      • PROJECTION+ixscan
      Table 1 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