ApsaraDB for MongoDB:Excessive databases and tables cause instance stuttering or abnormal behavior

Diagnosing slow queries

When lock contention from too many collections causes slow queries, the slow query log contains entries similar to the following:

2024-03-07T15:59:16.856+0800 I  COMMAND  [conn4175155] command db.collections command: count { count: "xxxxxx", query: { A: 1, B: 1 },
$readPreference: { mode: "secondaryPreferred" }, $db: "db" } planSummary: COLLSCAN keysExamined:0 keysExaminedBySizeInBytes:0
docsExamined:1 docsExaminedBySizeInBytes:208 numYields:1 queryHash:916BD9E3 planCacheKey:916BD9E3 reslen:185
locks:{ ReplicationStateTransition: { acquireCount: { w: 2 } }, Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 2 } },
Collection: { acquireCount: { r: 2 } }, Mutex: { acquireCount: { r: 1 } } } storage:{ data: { bytesRead: 304, timeReadingMicros: 4 },
timeWaitingMicros: { handleLock: 40, schemaLock: 134101710 } } protocol:op_query 134268ms

In this example, a count operation on a single-document collection took 134,268 ms. The key field is timeWaitingMicros: { handleLock: 40, schemaLock: 134101710 }, which shows that the read request spent almost all its time waiting to acquire handle locks and schema locks in the underlying storage layer—not performing actual work.

The following table describes key fields in a slow query log entry:

Use the following commands to diagnose collection and index counts before taking action:

// Count collections in a database
db.getSiblingDB(<dbName>).getCollectionNames().length

// View database statistics (collection count, index count, document count, total size)
db.getSiblingDB(<dbName>).stats()

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

Remove unnecessary collections

Identify collections that are expired or no longer needed, then drop them using dropCollection. For more information, see dropCollection().

Remove unnecessary indexes

Each index creates a separate disk file in WiredTiger and adds a corresponding dhandle. Reducing index count directly reduces dhandle pressure.

Use the $indexStats aggregation stage to identify underused indexes. Run the following command before making any changes (requires appropriate permissions):

// View access statistics for all indexes in a collection
db.getSiblingDB(<dbName>).<collectionName>.aggregate({"$indexStats":{}})

Sample output:

{
   "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 key fields in the output:

Apply the following rules when deciding which indexes to remove:

• Invalid indexes: Remove indexes on fields that no queries access.

• Index prefix redundancy: If both {a:1} and {a:1,b:1} exist, {a:1} is redundant—{a:1,b:1} covers all queries that would use it.

• Equivalence query order: If both {a:1,b:1} and {b:1,a:1} exist, remove the one with fewer hits. For equivalence matching, field order does not affect results.

• ESR Rule for range queries: Build compound indexes in Equality, Sort, Range order. For more information, see The ESR (Equality, Sort, Range) Rule.

• Low-hit indexes: These are often duplicated by a higher-hit index. Evaluate against all query patterns before removing.

If your instance runs MongoDB 4.4 or later, use db.collection.hideIndex() to hide an index before dropping it. Monitor the instance for a period to confirm no queries depend on the index before permanently removing it. For more information, see db.collection.hideIndex().

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

Integrate data from multiple collections

When collections grow over time due to a time-based partitioning pattern, merging them into a single collection eliminates the accumulation problem.

Before optimization — the temperatures database stores daily readings in separate collections:

// temperatures.march-09-2020
{ "_id": 1, "timestamp": "2020-03-09T010:00:00Z", "temperature": 29 }
{ "_id": 2, "timestamp": "2020-03-09T010:30:00Z", "temperature": 30 }
// ... 25 readings total (sensor runs 10:00-22:00, every 30 minutes)
{ "_id": 25, "timestamp": "2020-03-09T022:00:00Z", "temperature": 26 }

// temperatures.march-10-2020
{ "_id": 1, "timestamp": "2020-03-10T010:00:00Z", "temperature": 30 }
// ...

Each new day creates a new collection and a new _id index. Cross-day queries require $lookup, which performs worse than queries on a single collection.

After optimization — a single temperatures.readings collection stores all data, one document per day. The default _id index supports date-based queries without additional indexes for each day's readings:

// 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 }
  ]
}

This eliminates the unbounded collection growth problem and removes the need to create indexes for each day.

For time-series workloads, consider using time series collections (MongoDB 5.0 and later). For more information, see Time Series.

Split the instance

If the total number of collections in an ApsaraDB for MongoDB standalone instance cannot be reduced, split the data across multiple instances.

Migrate to a sharded cluster using shard tags

If all collections must remain under a single logical instance and cannot be reduced, migrate them to a sharded cluster instance and use shard tags to pin each collection to a specific shard. This distributes the dhandle load across shards without requiring application changes—only the connection string changes.

For example, 100,000 active collections migrated to a 10-shard cluster results in approximately 10,000 collections per shard.

For more information, see sh.addShardTag() and sh.addTagRange().

Migrate to a sharded cluster using zones

Zones work the same way as shard tags but use the newer sh.addShardToZone() and sh.updateZoneKeyRange() commands. For more information, see Manage Shard Zones, sh.addShardToZone(), and sh.updateZoneKeyRange().

Avoid running dropDatabase on a database with many collections

When dropDatabase runs, WiredTiger asynchronously removes the metadata and physical files of all collections in the database. This can affect primary-to-secondary replication, causing replication lag to increase continuously. The MongoDB flow control mechanism activates during this process, and all writes with {writeConcern: majority} may be affected.

To avoid this issue, use one of the following approaches:

• Drop collections individually with a delay between each drop, then run dropDatabase after all collections are removed.

• Use DTS or another migration tool to migrate the databases and collections you want to keep to a new instance, then decommission the original instance after cutover.

Configure replication lag alerts on your instance. If this issue occurs, submit a ticket for technical support.submit a ticketsubmit a ticket