MongoDB very slow deletes

Question

I've got a small replica set of three mongod servers (16GB RAM each, at least 4 CPU cores and real HDDs) and one dedicated arbiter. The replicated data has about 100,000,000 records currently. Nearly all of this data is in one collection with an index on _id (the auto-generated Mongo ID) and date, which is a native Mongo date field. Periodically I delete old records from this collection using the date index, something like this (from the mongo shell):

db.repo.remove({"date" : {"$lt" : new Date(1362096000000)}})

This does work, but it runs very, very slowly. One of my nodes has slower I/O than the other two, having just a single SATA drive. When this node is primary, the deletes run at about 5-10 documents/sec. By using rs.stepDown() I have demoted this slower primary and forced an election to get a primary with better I/O. On that server, I am getting about 100 docs/sec.

My main question is, should I be concerned? I don't have the numbers from before I introduced replication, but I know the delete was much faster. I'm wondering if the replica set sync is causing I/O wait, or if there is some other cause. I would be totally happy with temporarily disabling sync and index updates until the delete statement finishes, but I don't know of any way to do that currently. For some reason, when I disable two of the three nodes, leaving just one node and the arbiter, the remaining node is demoted and writes are impossible (isn't the arbiter supposed to solve that?).

To give you some indication of the general performance, if I drop and recreate the date index, it takes about 15 minutes to scan all 100M docs.

Answer 1

This is happening because even though

db.repo.remove({"date" : {"$lt" : new Date(1362096000000)}})

looks like a single command it's actually operating on many documents - as many as satisfy this query.

When you use replication, every change operation has to be written to a special collection in the local database called oplog.rs - oplog for short.

The oplog has to have an entry for each deleted document and every one of those entries needs to be applied to the oplog on each secondary before it can also delete the same record.

One thing I can suggest that you consider is TTL indexes - they will "automatically" delete documents based on expiration date/value you set - this way you won't have one massive delete and instead will be able to spread the load more over time.

Answer 2

Another suggestion that may not fit you, but it was optimal solution for me:

1. drop indeces from collection
2. iterate over all entries of collection and store id's of records to delete into memory array
3. each time array is big enough (for me it was 10K records), i removed these records by ids
4. rebuild indeces

It is the fastest way, but it requires stopping the system, which was suitable for me.