MongoDB Bucket upsert performance

TL;DR: My upserts for the bucket model are 10x slower than an insert of the same document in a more granular format.

Background: We store IoT data inside a PSS MongoDB replica set. Currently, we have a small model that saves each sensor value in one document in the following format:

{
  _id: <ObjectId>,
  Time: <DateTime>,
  Value: <Object>,
  InternalId: <string>
}

With our current server (512GB RAM, 64 threads, NVMe disks), we are observing 200k+ reads/writes per second, combined. Documents are on average 120 bytes in size, totaling 8 billion documents for 7 days of data, which consume roughly 1TB in storage.

We are now moving towards a bucket model to improve storage efficiency. Currently, I've am using the suggested model of:

{
  _id: <ObjectId>,
  BlockStartTime: <DateTime>,
  BlockEndTime: <DateTime>,
  InternalId: <String>,
  LastModified: <DateTime>,
  Telemetry: [
    Key: <DateTime>,
    Value: <Object>,
    ...
  ]
}

This model is very efficient in RAM usage and storage. However, all writes are very slow. I use the following to insert a telemetry record:

db.Telemetry.BulkWrite([
    {
        updateOne: {
            filter: { InternalId: <Guid>, BlockStartTime: <DateTime>},
            update: {
                $currentDate: { LastModified: true },
                $setOnInsert: { BlockEndTime: <DateTime>},
                $push: { Telemetry: { $each: [{ Key: <DateTime>, Value: <Object>}] }}
            },
            upsert: true
        }
]

The above BulkWrite typically contains 25 - 775 queries, almost always containing one key value pair to insert into "Telemetry".

Performance has dropped from 100K+ writes/s, to 5k/s - 10k/s updates.

I've tried the following:

Removing the $push, thereby only setting the LastModified date. Writes improved to 15k/s. This was to identify whether the upserts were the bottleneck
Altering batch sizes from 25 updates, to 775 updates, to 1500 updates, with no luck
Optimizing the query to use indexes fully. I can confirm that .explain() explains that indexes are hit only once, and only one document is returned/fetched
Server CPU (less than 20%) and write tickets are normal (always more than 120, with a max of 127)
Inserting a bucket for each telemetry value as a test of insert vs upsert. Performance increased 3x from upserts
Testing on a 4GB RAM server. Oddly enough, performance was marginally lower on the small server
Removing the possibility of update conflicts by using a simulator that sends telemetry every 15 seconds, spread over 400K unique InternalIds, sequentially

My question: Is upsert the way to go for data ingestion? I can understand a 50% drop in performance (upsert needs to do find, then update or insert), but is a 10x drop normal? Am I missing something with my ingestion strategy? Should I perhaps try to only store in the bucket format as a roll-up strategy, maybe one hour after ingestion? We need to use MongoDB for this client

MongoDB Bucket upsert performance

0 Answers