ApsaraDB for MongoDB:Best practices for TTL indexes in ApsaraDB for MongoDB

Basic mechanism

When a mongod process starts, it creates a background thread named TTLMonitor. By default, the thread starts a round of TTL cleanup every 60 seconds. Each round performs the following operations:

1. Collects all TTL indexes in the current database.

2. Generates an execution plan for each TTL index in turn and performs data cleanup.

3. Deletes the documents for which the indexed field value plus expireAfterSeconds is earlier than the current time.

The expiration threshold is calculated as follows: the date value of the indexed field plus the number of seconds specified by expireAfterSeconds. If the result is earlier than the current time, the document is considered expired.

Behavior in replica sets

In a replica set, the TTL background thread performs delete operations only on the Primary node. The TTL threads on Secondary nodes remain idle and synchronize the delete operations by replicating the oplog from the Primary node. This means TTL deletions generate additional oplog entries and may affect replica set replication lag.

Version differences: batched delete optimization (MongoDB 7.0+)

Starting with MongoDB 7.0 (a feature originally introduced in development version 6.1), TTL deletion uses a "fair delete" mechanism that allocates delete time to each TTL index in a time-slice manner. This prevents expired data in certain collections from being starved of cleanup. The main improvements are as follows:

• ttlMonitorBatchDeletes: Enables batched delete mode. Enabled by default. When enabled, TTL deletions are distributed more fairly across collections.

• ttlIndexDeleteTargetTimeMS: The upper time limit per round of deletion for each TTL index. Default value: 1000 ms.

• ttlIndexDeleteTargetDocs: The upper limit on the number of documents deleted per round for each TTL index. Default value: 50000.

• ttlMonitorSubPassTargetSecs: The upper time limit for a sub-pass that iterates through TTL indexes. Default value: 60 seconds.

These parameters appear as the BATCHED_DELETE stage in the execution plan and can be viewed by using the explain command.

Create a TTL index

Use the createIndex method to create a TTL index on a field of the Date type.

Example 1: Create a TTL index so that documents expire 3600 seconds after the value of the lastModifiedDate field.

db.eventlog.createIndex(
  { "lastModifiedDate": 1 },
  { expireAfterSeconds: 3600 }
)

Example 2: Expire at a specific point in time. Set expireAfterSeconds to 0 so that the field value alone determines the expiration time.

db.sessions.createIndex(
  { "expireAt": 1 },
  { expireAfterSeconds: 0 }
)

Modify the expiration time of a TTL index

Use the collMod command to change the expireAfterSeconds value of an existing TTL index without rebuilding the index.

db.runCommand({
  "collMod": "log_events",
  "index": {
    "keyPattern": { "createdAt": 1 },
    "expireAfterSeconds": 600
  }
})

Convert a regular index to a TTL index (MongoDB 6.0+)

Starting with MongoDB 6.0, you can use the collMod command to convert an existing regular single-field index into a TTL index, without first deleting and rebuilding it.

db.runCommand({
  "collMod": "tickets",
  "index": {
    "keyPattern": { "lastModifiedDate": 1 },
    "expireAfterSeconds": 100
  }
})

Monitor TTL status

You can monitor TTL operations by using the following commands.

// View the total number of documents deleted by TTL
db.serverStatus().metrics.ttl.deletedDocuments

// View the number of TTL thread passes
db.serverStatus().metrics.ttl.passes

// View the TTL scan interval (default: 60 seconds)
db.runCommand({ getParameter: 1, ttlMonitorSleepSecs: 1 })

// View the TTL delete operations that are currently running
db.currentOp()

Issue 1: Periodic CPU spikes caused by TTL deletions

Symptom

The instance CPU usage shows obvious periodic peaks at fixed intervals. By running db.currentOp(), you can observe that the TTL thread is performing a large number of delete operations.

Cause

When the application inserts a large amount of data at the same moment and the TTL field values are the same or close to each other, these documents expire in the same time window. The TTL thread needs to delete a large number of documents in a single scan, which puts significant pressure on CPU and I/O.

Solution

• Spread out expiration times: When inserting data, add a random offset to the TTL field to spread expiration times across a time window and avoid mass deletions. For example, for data that expires in 24 hours, add or subtract a random offset of several minutes to the expiration time.

• Upgrade to MongoDB 7.0 or later: Use the batched delete and fair delete mechanisms to smooth out deletion pressure.

• Choose an appropriate instance specification: Make sure that the instance has enough CPU and memory to handle the additional load from TTL deletions.

Example: Add a random expiration offset to inserted data.

// Add a random offset of 0 to 600 seconds on top of 24 hours
var randomOffset = Math.floor(Math.random() * 600);
var expireTime = new Date(Date.now() + (86400 + randomOffset) * 1000);
db.logs.insertOne({
  data: "log content",
  createdAt: expireTime
})

Issue 2: Data is not deleted after expiration

Symptom

Documents have passed their expected expiration time but still exist in the collection.

Troubleshooting steps

1. Verify the field type: Check whether the value of the TTL indexed field is of the BSON Date type. A common error is to use a UNIX timestamp integer instead of a Date object.

   // Check the field type
   db.collection.findOne({}, { createdAt: 1 })
   // Correct: ISODate("2024-01-01T00:00:00Z")
   // Wrong: 1704067200 (integer timestamp)

2. Verify that the field exists: Check whether documents actually contain the TTL indexed field. Documents that do not contain the field are not automatically deleted.

3. Check the index definition: Verify that the index includes the expireAfterSeconds property.

   db.collection.getIndexes()

4. Check the index direction: Make sure that the TTL index is in ascending order (the value is 1). A descending index may cause TTL to malfunction. If the index direction is incorrect, drop and recreate the index.

5. Check whether TTL Monitor is enabled: Verify that the ttlMonitorEnabled parameter is set to true.

   db.adminCommand({ getParameter: 1, ttlMonitorEnabled: 1 })

Issue 3: TTL deletion cannot keep up with the data insertion rate

Symptom

The amount of data in the collection keeps growing and the disk usage keeps increasing, even though a TTL index has been configured.

Cause

The TTL thread is single-threaded and runs every 60 seconds. When the data insertion rate far exceeds the TTL cleanup rate, expired data accumulates. In addition, if multiple TTL indexes exist on an instance, they are processed serially, which further reduces cleanup efficiency.

Solution

• Adjust the scan frequency: You can shorten the scan interval by setting the ttlMonitorSleepSecs parameter. Note that a higher frequency increases system load.

  // Set the scan interval to 10 seconds
  db.adminCommand({ setParameter: 1, ttlMonitorSleepSecs: 10 })

• Clean up on the application side: For scenarios with particularly high write volumes, implement a scheduled batch delete in your application instead of relying entirely on TTL indexes.

• Consider time-partitioned collections: For extremely high write scenarios, partition data into collections by time and drop entire expired collections. This is much more efficient than deleting documents one by one with a TTL index.

Issue 4: Disk space is not released after deletion

Symptom

TTL has deleted a large number of documents, but disk usage has not decreased significantly.

Solution

MongoDB uses the WiredTiger storage engine. When documents are deleted, the space is marked as reusable but is not immediately returned to the operating system. To reclaim disk space immediately, run the compact command or rebuild data files through an initial sync. We recommend that you run compact during off-peak hours.

1. Ensure correct data types

TTL indexes work only on fields of the BSON Date type. We recommend that you enforce the correct date type for TTL fields on the application side or by using the MongoDB Schema Validation feature.

db.createCollection("sessions", {
  validator: {
    $jsonSchema: {
      properties: {
        expireAt: { bsonType: "date", description: "The TTL field must be of the Date type" }
      },
      required: ["expireAt"]
    }
  }
})

2. Spread expiration times to avoid mass deletions

This is the most effective way to prevent CPU spikes caused by TTL deletions. When inserting data, add a random offset to the expiration time so that a batch of data expires across a time range. This way, the TTL thread deletes only a small number of documents per scan, which significantly reduces the impact on system performance.

3. Adjust expireAfterSeconds with caution

Lowering the expireAfterSeconds value of an existing TTL index causes many existing documents to expire immediately, which may trigger large-scale delete operations and seriously affect instance performance. We recommend the following:

1. Estimate how many documents will expire immediately after the adjustment.

2. Make the change during off-peak hours.

3. If the number of expired documents is large, first delete part of the data in batches by using a script, and then modify expireAfterSeconds.

4. Considerations for creating a new TTL index

Creating a TTL index on a collection that already contains a large number of documents that meet the expiration condition may trigger large-scale deletes immediately after the index is built. We recommend the following:

• Create the TTL index during off-peak hours.

• Clean up historical expired data first, and then create the TTL index.

• Build the index in the background to avoid affecting regular business.

5. Do not disable TTL Monitor for a long time

Although you can temporarily disable the TTL feature by setting the ttlMonitorEnabled parameter, we do not recommend keeping it disabled for a long time in a production environment. The large number of expired documents accumulated during the disabled period will be deleted in bulk when the feature is re-enabled, which may cause serious performance problems.

6. Evaluate the impact of TTL on the oplog

Each document deleted by TTL generates an oplog entry. When the volume of deletions is large, the oplog write volume increases significantly, which may affect replica set replication lag. We recommend that you evaluate the impact in the following ways:

• Monitor metrics.ttl.deletedDocuments to track the volume of TTL deletions.

• Monitor replica set replication lag to make sure that the Secondary nodes can handle the additional oplog generated by TTL.

• Increase the oplog size if necessary to prevent oplog overflow caused by TTL deletions.

7. Alternatives for high-write scenarios

TTL indexes are suitable for scenarios with moderate write volumes. For scenarios with extremely high write volumes, consider the following alternatives:

1. Time-partitioned collections: Partition the collection by a time dimension (such as by day or week) and drop the entire expired collection. This is the most efficient way to expire data.

2. Time series collections: MongoDB 5.0+ supports time series collections, which include built-in data expiration and delete data in bulk by bucket. This is much more efficient than deleting documents one by one in a regular collection.

3. Application-side scheduled cleanup: Use scheduled tasks to perform batch deletes during off-peak hours, which gives you more precise control over the deletion rate and timing.