0

We are having huge collection which we can not partition ( we need to get the records by last updated time which can not be known ). To decrease the storage we have enabled TTL index based on last updated time.

However we saw that TTL index is not capable to catch up and delete records on time. Therefore we started to investigate other options including manual deletes. When we separated disks we have found that most of work during delete period is on indexes disks and the amount of the reads and writes from disks is much much higher then the number of deletes and writes + reads op.

Therefore we suspect that the problem is that mongo is constantly rebuilds indexes which in turns slows down deletes. The rebuild is caused because indexes are the B-Trees which gets unbalanced all the time ( at least thats our assumption ) Transaction log and data disks are having really low traffic ( 15-20% of the overall capacity of the disks at any time ), and only indexes disks are the problem. In general we have 4 collections on this sharded cluster. 3 are the partitioned data which we write to only ( and read of course ) , the collections are getting dropped from time to time causing minimal oplog operations and minimal amount of disk ops. And the last one is the problematic collection which we are trying to manage properly. We are using mongodb4.4.

The number of writes that are expected to be are ~1 billion records per day, currently we have 4 shards, and each records is approximately 0.5KB. Effectively before we started to make the changes in disks and adding shards, we where having 2 shards with ~4TB storage on each shard for this collection, while we expect to have ~250GB of disk used on each shard by data, and ~300-400GB of disk used by indexes.

Are there any fine tune parameters for indexes maintenance, or at least the documentation on how multikey indexes are creating the tree. Any optimization hints would help.

We need to have indexes on id, lastUpdatedId (ObjectId ), keys ( array of up to 5 values which are the key elements ) and recordType Currently due to different queries we have following indexes:

_id

_id_hashed ( for sharding )

keys_id

keys_lastUpdatedId

sessionType_id

sessionType_lastUpdatedId

keys_sessionType_id

Keys_sessionType_lastUpdatedId

lastUpdatedTime ( this was ttl index, currently its regular index based on last updated time - date field , which we use to execute manual deletes )

Thanks and best regards

Yulian Oifa

1
  • For a sharded collection, even with a regular index (e.g., lastUpdatedTime) the multiple deletes will be directed to multiple shards. Optimized operations happen when the document is accessed using the shard key (or the prefix of a shard key). Also, indexes are efficient if they are in the RAM. See Write Operations on Sharded Clusters
    – prasad_
    Commented May 9 at 6:52

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.