How to clean up orphaned documents in a sharded MongoDB cluster - Data Transmission Service

Check for orphaned documents

Connect to the ApsaraDB for MongoDB sharded cluster instance by using Mongo Shell.
For information about how to connect to ApsaraDB for MongoDB, see Connect to a sharded cluster instance by using the mongo shell.
Run the following command to switch to the destination database.
```
use <db_name>
```
Run the following command to view information about orphaned documents.
```
db.<coll_name>.find().explain("executionStats")
```
Note
For each shard, check the executionStats output. If the SHARDING_FILTER stage chunkSkips field is not 0, orphaned documents exist on the corresponding shard.
The following example return shows that the SHARDING_FILTER stage is preceded by the FETCH stage, which returned 102 documents ("nReturned" : 102). Then, in the SHARDING_FILTER stage, 2 orphaned documents were filtered out ("chunkSkips" : 2), and 100 documents were returned in the end ("nReturned" : 100).
```
"stage" : "SHARDING_FILTER",
"nReturned" : 100,
......
"chunkSkips" : 2,
"inputStage" : {
    "stage" : "FETCH",
    "nReturned" : 102,
```
For more information about the SHARDING_FILTER stage, see MongoDB Manual.

Clean up orphaned documents

Important

If you have multiple databases, clean up orphaned documents in each database.

ApsaraDB for MongoDB

Note

Running the cleanup script on an instance with a major version earlier than MongoDB 4.2 or a minor version earlier than 4.0.6 causes an error. To view the current version of your instance, see MongoDB minor versions. To upgrade the major or minor version, see Upgrade the major version of a database and Upgrade the minor version of a database.

Use the cleanupOrphaned command to clean up orphaned documents. The procedure differs slightly for MongoDB 4.4 and later and for MongoDB 4.2 and earlier.

MongoDB 4.4 and later

On a server that can connect to your sharded cluster instance, create a JavaScript (JS) script named cleanupOrphaned.js.

Note

This script cleans up orphaned documents from all collections in multiple databases across multiple shards. If you need to clean up orphaned documents from a specific collection, you can modify the JS script.

// List of shard names
var shardNames = ["shardName1", "shardName2"];
// List of databases
var databasesToProcess = ["database1", "database2", "database3"];
shardNames.forEach(function(shardName) {
    // Iterate over the specified list of databases
    databasesToProcess.forEach(function(dbName) {
        var dbInstance = db.getSiblingDB(dbName);
        // Get the names of all collections in the database instance
        var collectionNames = dbInstance.getCollectionNames();
        // Iterate over each collection
        collectionNames.forEach(function(collectionName) {
            // The full collection name
            var fullCollectionName = dbName + "." + collectionName;
            // Build the cleanupOrphaned command
            var command = {
                runCommandOnShard: shardName,
                command: { cleanupOrphaned: fullCollectionName }
            };
            // Execute the command
            var result = db.adminCommand(command); 
            if (result.ok) {
                print("Cleaned up orphaned documents for collection " + fullCollectionName + " on shard " + shardName);
                printjson(result);
            } else {
                print("Failed to clean up orphaned documents for collection " + fullCollectionName + " on shard " + shardName);
            }
        });
    });
});

In the script, modify the values of the shardNames and databasesToProcess parameters:

shardNames: An array of IDs for the shards from which to clean up orphaned documents. You can obtain the shard IDs from the Shard List on the Basic Information page of the instance. For example, d-bp15a3796d3a****.
databasesToProcess: An array of database names from which to clean up orphaned documents.

In the directory where the cleanupOrphaned.js script is located, run the following command to clean up orphaned documents.

mongo --host <Mongoshost> --port <Primaryport>  --authenticationDatabase <database> -u <username> -p <password> cleanupOrphaned.js > output.txt

Parameter	Description
`<Mongoshost>`	The connection address of the mongos node of the sharded cluster instance. Example: `s-bp14423a2a51****.mongodb.rds.aliyuncs.com`.
`<Primaryport>`	The port number of the mongos node of the sharded cluster instance. The default value is 3717.
`<database>`	The name of the authentication database. This is the database to which the database account belongs.
`<username>`	The database account.
`<password>`	The password for the database account.
`output.txt`	The output file for the execution results.

MongoDB 4.2 and earlier

On a server that can connect to your sharded cluster instance, create a JS script named cleanupOrphaned.js.

Note

This script cleans up orphaned documents from a specified collection in a specified database across multiple shards. To clean up orphaned documents from multiple collections, you can modify the fullCollectionName parameter and run the script multiple times, or modify the script to iterate through the collections.

function cleanupOrphanedOnShard(shardName, fullCollectionName) {
    var nextKey = { };
    var result;
    while ( nextKey != null ) {
        var command = {
            runCommandOnShard: shardName,
            command: { cleanupOrphaned: fullCollectionName, startingFromKey: nextKey }
        };
        result = db.adminCommand(command);
        printjson(result);
        if (result.ok != 1 || !(result.results.hasOwnProperty(shardName)) || result.results[shardName].ok != 1 ) {
            print("Unable to complete at this time: failure or timeout.")
            break
        }
        nextKey = result.results[shardName].stoppedAtKey;
    }
    print("cleanupOrphaned done for coll: " + fullCollectionName + " on shard: " + shardName)
}
var shardNames = ["shardName1", "shardName2", "shardName3"]
var fullCollectionName = "database.collection"
shardNames.forEach(function(shardName) {
    cleanupOrphanedOnShard(shardName, fullCollectionName);
});

In the script, modify the values of the shardNames and fullCollectionName parameters:

shardNames: An array of IDs for the shards from which to clean up orphaned documents. You can obtain the shard IDs from the Shard List on the Basic Information page of the instance. For example, d-bp15a3796d3a****.
fullCollectionName: The name of the collection from which to clean up orphaned documents, in the format database.collection.

In the directory where the cleanupOrphaned.js script is located, run the following command to clean up orphaned documents.

mongo --host <Mongoshost> --port <Primaryport>  --authenticationDatabase <database> -u <username> -p <password> cleanupOrphaned.js > output.txt

Parameter	Description
`<Mongoshost>`	The connection address of the mongos node of the sharded cluster instance. Example: `s-bp14423a2a51****.mongodb.rds.aliyuncs.com`.
`<Primaryport>`	The port number of the mongos node of the sharded cluster instance. The default value is 3717.
`<database>`	The name of the authentication database. This is the database to which the database account belongs.
`<username>`	The database account.
`<password>`	The password for the database account.
`output.txt`	The output file for the execution results.

Self-managed MongoDB

On a server that can connect to your self-managed MongoDB database, download the cleanupOrphaned.js script file.

wget "https://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/120562/cn_zh/1564451237979/cleanupOrphaned.js"

Modify the cleanupOrphaned.js script file. Replace test with the name of the database from which you want to clean up orphaned documents.

Important

If you have multiple databases, you must repeat Step 2 and Step 3 for each database.

function cleanupOrphaned(coll) {
  var nextKey = { };
  var result;
  while ( nextKey != null ) {
    result = db.adminCommand( { cleanupOrphaned: coll, startingFromKey: nextKey } );
    if (result.ok != 1)
       print("Unable to complete at this time: failure or timeout.")
    printjson(result);
    nextKey = result.stoppedAtKey;
  }
}
var dbName = "test"
db = db.getSiblingDB(dbName)
db.getCollectionNames().forEach(function(collName) {
        cleanupOrphaned(dbName + "." + collName);
});

Run the following command to clean up orphaned documents from all collections in the specified database on a shard.

Note
You must repeat this step for each shard.
```
mongo --host <Shardhost> --port <Primaryport>  --authenticationDatabase <database> -u <username> -p <password> cleanupOrphaned.js
```
Note
- <Shardhost>: The IP address of the shard.
- <Primaryport>: The service port of the primary node in the shard.
- <database>: The name of the authentication database. This is the database to which the database account belongs.
- <username>: The database account.
- <password>: The password for the account.
Example:

In this example, the self-managed MongoDB database has three shards. You must run the command for each shard to clean up the orphaned documents.
```
mongo --host 172.16.1.10 --port 27018  --authenticationDatabase admin -u dtstest -p 'Test123456' cleanupOrphaned.js
```
```
mongo --host 172.16.1.11 --port 27021 --authenticationDatabase admin -u dtstest -p 'Test123456' cleanupOrphaned.js
```
```
mongo --host 172.16.1.12 --port 27024  --authenticationDatabase admin -u dtstest -p 'Test123456' cleanupOrphaned.js
```

Troubleshooting

If idleCursors exist on the namespace of the orphaned documents, the cleanup process may fail to complete. In this case, the mongod log for the orphaned documents contains the following information:

Deletion of DATABASE.COLLECTION range [{ KEY: VALUE1 }, { KEY: VALUE2 }) will be scheduled after all possibly dependent queries finish

You can connect to mongod by using Mongo Shell and run the following command to check whether idleCursors exist on the current shard. If idleCursors exist, use the restart mongod or killCursors command to clean up all idleCursors, and then clean up the orphaned documents again. For more information, see JIRA ticket.

db.getSiblingDB("admin").aggregate( [{ $currentOp : { allUsers: true, idleCursors: true } },{ $match : { type: "idleCursor" } }] )