ApsaraDB for MongoDB:Performance issues from excessive databases and collections

Remove unnecessary collections

Identify collections in the database that can be deleted, such as those that are expired or are no longer in use. Use the dropCollection command to remove them. For more information, see dropCollection().

Use the following commands to view database and collection information:

• Run the following command to view the number of collections in a database.

  db.getSiblingDB(<dbName>).getCollectionNames().length

• Run the following command to view detailed information about a database, including the number of collections, indexes, and documents, as well as the total data size.

  // View statistics for a specific database.
  db.getSiblingDB(<dbName>).stats()

• Run the following command to view detailed information about a specific collection.

  // View statistics for a specific collection.
  db.getSiblingDB(<dbName>).<collectionName>.stats()

Remove unnecessary indexes

Reducing the number of indexes also decreases the number of disk files and corresponding dhandle structures maintained by the WiredTiger storage engine, which helps mitigate this issue.

Follow these basic principles for index optimization:

• Avoid unused indexes

  If a query never accesses a specific field, any index on that field will not be used. Such indexes are considered unused and can be removed.

• Follow index prefix rules

  For example, if you have both {a:1} and {a:1,b:1} indexes, the first index is a redundant prefix of the second and can be removed.

• Consider index field order for equality queries

  For equality matches, the order of fields in a composite index does not matter. For example, for queries on fields a and b, the indexes {a:1,b:1} and {b:1,a:1} are functionally equivalent. You can remove the one that is used less frequently.

• Use the ESR rule for range queries

  To build an optimal composite index for your queries, arrange the fields in the order of Equality, Sort, Range. For more information, see The ESR (Equality, Sort, Range) Rule.

• Review indexes with low hit counts

  An index with a low hit count often overlaps with a more efficient index. Analyze all relevant query patterns to determine if you can safely remove it.

You can use MongoDB's $indexStats aggregation stage to view statistics for all indexes in a collection. Ensure that you have the necessary permissions before running the following command.

// View index statistics for a specific collection.
db.getSiblingDB(<dbName>).<collectionName>.aggregate({"$indexStats":{}})

The command returns output similar to the following example.

{
   "name" : "item_1_quantity_1",
   "key" : { "item" : 1, "quantity" : 1 },
   "host" : "examplehost.local:27018",
   "accesses" : {
      "ops" : NumberLong(1),
      "since" : ISODate("2020-02-10T21:11:23.059Z")
   }
}

The following table describes the parameters in the returned information.

If you observe that an index has a very low hit count, for example, if accesses.ops is 1, it is likely a redundant or unused index that you can consider deleting. If your MongoDB instance is version 4.4 or later, you can use the hideIndex command to hide the index before dropping it. This allows you to confirm that there is no negative impact on your application for a period of time, reducing the risk of index deletion.

// players collection
{
  "_id": "ObjectId(123)",
  "first_name": "John",
  "last_name": "Doe",
  "coins": 11,
  "stars": 2
}

Consolidate data from multiple collections

Consolidate data from multiple collections into a single collection to reduce the total collection count.

For example, a database named temperatures stores temperature data from sensors. A sensor operates from 10:00 AM to 10:00 PM, reading and storing temperature data every half hour. It stores the temperature data for each day in a separate collection named after the date.

The following snippets show partial data from two collections, temperatures.march-09-2020 and temperatures.march-10-2020.

• Collection temperatures.march-09-2020

  {
    "_id": 1,
    "timestamp": "2020-03-09T010:00:00Z",
    "temperature": 29
  }
  {
    "_id": 2,
    "timestamp": "2020-03-09T010:30:00Z",
    "temperature": 30
  }
  ...
  {
    "_id": 25,
    "timestamp": "2020-03-09T022:00:00Z",
    "temperature": 26
  }

• Collection temperatures.march-10-2020

  {
    "_id": 1,
    "timestamp": "2020-03-10T010:00:00Z",
    "temperature": 30
  }
  {
    "_id": 2,
    "timestamp": "2020-03-10T010:30:00Z",
    "temperature": 32
  }
  ...
  {
    "_id": 25,
    "timestamp": "2020-03-10T022:00:00Z",
    "temperature": 28
  }
  

Over time, the number of collections in the database increases. Because MongoDB does not enforce a hard limit on the number of collections and this model lacks a clear data lifecycle policy, the number of collections and their corresponding indexes grows indefinitely.

Besides the problem of an ever-growing number of collections, this data model makes it difficult to perform queries that span multiple days. To query data across several days to analyze long-term temperature trends, you would need to use $lookup queries, which are less performant than querying within a single collection.

A better data model is to store all temperature readings in a single collection, with each day's data stored in a single document. This approach is an example of the Bucket Pattern. The following example shows the optimized model.

// temperatures.readings
{
  "_id": ISODate("2020-03-09"),
  "readings": [
    {
      "timestamp": "2020-03-09T010:00:00Z",
      "temperature": 29
    },
    {
      "timestamp": "2020-03-09T010:30:00Z",
      "temperature": 30
    },
    ...
    {
      "timestamp": "2020-03-09T022:00:00Z",
      "temperature": 26
    }
  ]
}
{
  "_id": ISODate("2020-03-10"),
  "readings": [
    {
      "timestamp": "2020-03-10T010:00:00Z",
      "temperature": 30
    },
    {
      "timestamp": "2020-03-10T010:30:00Z",
      "temperature": 32
    },
    ...
    {
      "timestamp": "2020-03-10T022:00:00Z",
      "temperature": 28
    }
  ]
}

The optimized model consumes far fewer resources than the original model. You no longer need to create indexes based on the time of day, and the default _id index on the collection facilitates queries by date. This also resolves the issue of an unbounded number of collections.

Split the instance

If you cannot reduce the total number of databases and collections within a single MongoDB instance, consider a logical split of the database instance along with corresponding changes to your application.

You can approach this in two scenarios:

Migrate to a sharded cluster with shard tags

If all your collections are in a single database and you want to manage them as a single logical instance, consider migrating your data to a sharded cluster architecture and using shard tags for management. The shard tag management method is slightly more complex and requires extra operational steps (using sh.addShardTag and sh.addTagRange). However, a single MongoDB instance still manages all collections, requiring minimal changes to your application. You only need to replace the connection string with that of the new sharded cluster instance.

For example, if your instance has 100,000 active collections, you can purchase a new 10-shard sharded cluster instance. Follow the process below to configure the instance and migrate data, which will result in 10,000 active collections per shard. The procedure is as follows:

1. Purchase a new sharded cluster instance. This example uses a 2-shard instance. For instructions on how to create a sharded cluster, see Create a sharded cluster instance.

2. Connect to the mongos node of the sharded cluster instance. For more information, see Connect to an ApsaraDB for MongoDB sharded cluster instance by using the mongo shell.

3. Run the following commands to add a shard tag to each shard.

   sh.addShardTag("d-xxxxxxxxx1", "shard_tag1")
   sh.addShardTag("d-xxxxxxxxx2", "shard_tag2")

   Note

   • Before you run these commands, ensure the account you are using has the required permissions.

   • DMS (Data Management) does not currently support the sh.addShardTag command. We recommend connecting to the instance with the mongo shell or mongosh to run these commands.

4. Pre-configure the tag-based range distribution rules for all sharded collections.

   use <dbName>
   sh.enableSharding("<dbName>")
   sh.addTagRange("<dbName>.test", {"_id":MinKey}, {"_id":MaxKey}, "shard_tag1")
   sh.addTagRange("<dbName>.test1", {"_id":MinKey}, {"_id":MaxKey}, "shard_tag2")

   This example uses _id as the shard key. Choose a shard key that suits your workload, and ensure that all query operations include the shard key field. The shard key must be consistent with the field used in the next step. You must also use the [MinKey,MaxKey] boundaries to ensure that all data for a single collection resides on a single shard.

5. Run the shardCollection operation for all collections to be migrated.

   sh.shardCollection("<dbName>.test", {"_id":1})
   sh.shardCollection("<dbName>.test1", {"_id":1})

6. Run the sh.status() command to confirm that the rules are in effect.

   zhongli.test
           shard key: { "_id" : 1 }
           unique: false
           balancing: true
           chunks:
                   d-xxx	1
           { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : d-xxx Timestamp(1, 0)
            tag: shard_tag1  { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } }
   zhongli.test1
           shard key: { "_id" : 1 }
           unique: false
           balancing: true
           chunks:
                   d-xxx	1
           { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : d-xxx Timestamp(1, 0)
            tag: shard_tag2  { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } }

7. Migrate data from a replica set instance to a sharded cluster instance.

   Note

   Because you have already pre-sharded the collections on the destination instance, all database and collection information already exists. Therefore, you must set the Processing Mode of Conflicting Tables to Ignore Errors and Proceed in your DTS task.

8. After verifying data consistency, switch your applications to access the new sharded cluster instance.

Migrate to a sharded cluster with zones

This method is similar to using shard tags, but it uses MongoDB's Zones feature instead. It requires extra operational steps, specifically sh.addShardToZone() and sh.updateZoneKeyRange().

The procedure is as follows:

1. Purchase a new sharded cluster instance. This example uses a 2-shard instance. For instructions on how to create a sharded cluster, see Create a sharded cluster instance.

2. Connect to the mongos node of the sharded cluster instance. For more information, see Connect to an ApsaraDB for MongoDB sharded cluster instance by using the mongo shell.

3. Run the following commands to assign each shard to a zone.

   sh.addShardToZone("d-xxxxxxxxx1", "ZoneA")
   sh.addShardToZone("d-xxxxxxxxx2", "ZoneB")

   Note

   • Before you run these commands, ensure the account you are using has the required permissions.

   • DMS does not currently support the sh.addShardToZone command. We recommend connecting to the instance with the mongo shell or mongosh to run these commands.

4. Pre-configure the zone-based range distribution rules for all collections.

   use <dbName>
   sh.enableSharding("<dbName>")
   sh.updateZoneKeyRange("<dbName>.test", { "_id": MinKey }, { "_id": MaxKey }, "ZoneA")
   sh.updateZoneKeyRange("<dbName>.test1", { "_id": MinKey }, { "_id": MaxKey }, "ZoneB")

   This example uses _id as the shard key. Choose a shard key that suits your workload, and ensure that all query operations include the shard key field. The shard key must be consistent with the field used in the next step. You must also use the [MinKey,MaxKey] boundaries to ensure that all data for a single collection resides on a single shard.

5. Run the shardCollection operation for all collections to be migrated.

   sh.shardCollection("<dbName>.test", { "_id": 1 })
   sh.shardCollection("<dbName>.test1", { "_id": 1 })

6. Run the sh.status() command to view the sharding distribution and confirm that the zone configuration is in effect. The sample output is as follows:

   {  "_id" : "shardDistributionDB",  "primary" : "d-xxx24",  "partitioned" : false,  "version" : {  "uuid" : UUID("1dc635xxx"),  "lastMod" : 1 } }
           shardDistributionDB.test
                   shard key: { "_id" : "hashed" }
                   unique: false
                   balancing: true
                   chunks:
                           d-2ze9797089ef3704      1
                   { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : d-xxx04 Timestamp(1, 0)
                    tag: ZoneA  { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } }
           shardDistributionDB.test1
                   shard key: { "_id" : "hashed" }
                   unique: false
                   balancing: true
                   chunks:
                           d-2zed2e752c35af24      1
                   { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : d-xxx24 Timestamp(1, 0)
                    tag: ZoneB  { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } }

7. Migrate data from a replica set instance to a sharded cluster instance.

   Note

   Because you have already pre-sharded the collections on the destination instance, all database and collection information already exists. Therefore, you must set the Processing Mode of Conflicting Tables to Ignore Errors and Proceed in your DTS task.

8. After verifying data consistency, switch your applications to access the new sharded cluster instance.