0

Problem Description

Our team is developing a small web application for an academic research project, which is currently hosted on Heroku and connects to a MongoDB Atlas M0 (Free Tier) database. The customerOrders collection has a schema defined in the TypeScript source code approximately as follows (see the models folder in the development branch of our project GitHub page for the full source code):

_id: {type: String, default: generateID}, // custom alphanumeric ID generator for URLs
pin: {type: Number, default: null}, // multiplayer game session PIN
createDate: {type: Date, default: Date.now},
lastModified: {type: Date, default: Date.now},
finishedTime: {type: Date, default: null},
status: {type: String, default: 'In Progress'},
stage: {type: String, default: 'Manufacturer'},
modelID: {type: Number, default: -1},
isCustomOrder: {type: Boolean, default: false},
orderDesc: {type: String, default: null},
imageData: {type: Buffer, default: null},
assembledModel: {type: String, default: null}

We have noticed extreme performance degradation when the imageData or assembledModel fields are read or written, which often causes requests to the app's API to time out; these fields typically hold ~100-500 KB of data each. Some timing data obtained from a localhost server connecting to the production database is as follows (the collection used in these tests holds only 3 documents with a total size of about 2.7 MB):

Both fields excluded:

Source code:

let startTime: number = Date.now();
console.log(`Querying database for orders (pin ${pin})...`);
let orderInfo = await this.orderModel.find({pin: pin}, {'imageData': 0, 'assembledModel': 0});
console.log(`Retrieved orders for pin ${pin} (${Date.now() - startTime} ms).`);

Results:

Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (74 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (64 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (66 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (62 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (62 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (65 ms).

imageData excluded:

Source code:

let startTime: number = Date.now();
console.log(`Querying database for orders (pin ${pin})...`);
let orderInfo = await this.orderModel.find({pin: pin}, {'imageData': 0});
console.log(`Retrieved orders for pin ${pin} (${Date.now() - startTime} ms).`);

Results:

Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (26224 ms).
Retrieved orders for pin 4306 (26154 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (26453 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (26498 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (26187 ms).
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (26135 ms).
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (53421 ms).
Retrieved orders for pin 4306 (50384 ms).
Retrieved orders for pin 4306 (42395 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (26155 ms).
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (48150 ms).
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...
Querying database for orders (pin 4306)...

assembledModel excluded:

Source code:

let startTime: number = Date.now();
console.log(`Querying database for orders (pin ${pin})...`);
let orderInfo = await this.orderModel.find({pin: pin}, {'assembledModel': 0});
console.log(`Retrieved orders for pin ${pin} (${Date.now() - startTime} ms).`);

Results:

Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2473 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2474 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2463 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2541 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2476 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2464 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2866 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2465 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2472 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2471 ms).
Querying database for orders (pin 4306)...
Retrieved orders for pin 4306 (2472 ms).

It is also worth noting that MongoDB Compass shows a connection timeout when attempting to retrieve this collection, but shows other collections without problems.

Solutions

If necessary, we could move the data in these fields or the entire database elsewhere (e.g. AWS), but any way to achieve reasonable performance on the existing database would be preferable.

  • The MongoDB Atlas free tier is intended for development or testing purposes. Performance is not guaranteed to be consistent (these clusters use shared host resources) and there are operational limitations that may affect your use case (eg maximum of 100 operations per second). However, one immediate tuning step would be to explain() your common queries and updates to confirm they are efficiently supported by indexes. – Stennie Jul 24 at 2:26
  • If you believe you already have an appropriate index (or need help understanding explain results) please edit your question to include the output of explain(true) for one of the slow queries. If indexing cannot be improved and you need more predictable performance, it is probably time to upgrade to a dedicated cluster or self-host. – Stennie Jul 24 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.