MongoDB – slow remove query

mongodbperformancequery-performance

I have a collection in MongoDB with 800M+ records. I wanted to do some cleanup, but the remove operation is really really (unusably) slow – even deleting 1000 records can take 10+ minutes.

The structure of one record in the collection is something like this:

{
     'stID' : '00000001',
     'recordID' : '28-4',
     'date' : ISODate('2017-06-06 20:00:00'),
     ......
}

There is a compound index on {'stID' : 1, 'recordID' : 1, 'date' : 1} and my delete query is like this: db.coll.remove({ 'stID' : '00000001', 'recordID' : '28-4' }, { multi: true });

The database is part of a replicaSet, with one secondary and one arbiter, and I do understand that the delete operation has to fill the oplog with the _ids of each record to delete, but still I find it hard to understand why this could be so slow, especially since:

  • there is no considerable system or IO activity on the primary or the secondary
  • the secondary is not lagging behind the primary

We have never experienced problems with find or write/update operations. Can you suggest what other parameters/commands/tools should I use to see what is going on with the query and the servers.

The storage engine we use is WiredTiger. The server has 128GB of RAM and fast SSD drives in a RAID 1 configuration.

Best Answer

I've had improved performance by using bulkWrite:

db.ig_posts.bulkWrite([
   { deleteMany: { "filter": { /* your query here */ } }
]);