Compound Index Vs Multiple Single Field Index in MongoDB 3.x – Part II

Compound Index Vs Multiple Single Field Index in MongoDB 3.x – Part II

After the first blog post was published, it was suggested to me that I should run the test with w:1 j:true. It make sense since no one would or should be running workloads with w:1, j:false.

By definition:

With j: true, MongoDB returns only after the requested number of members, including the primary, have written to the journal. Previously j: true write concern in a replica set only requires the primary to write to the journal, regardless of the w: <value> write concern.

If you are using MMapv1, then you should be using j:true else you risk data loss. If you are using WiredTiger, you will only lose at most 60 seconds of data between the checkpoints.

Insert Performance

No Index

After 295 seconds, 5754368 new records inserted - collection has 5754368 in total
20017 inserts per second since last report 99.62 % in under 50 milliseconds
0 keyqueries per second since last report 100.00 % in under 50 milliseconds
0 updates per second since last report 100.00 % in under 50 milliseconds
0 rangequeries per second since last report 100.00 % in under 50 milliseconds

~19,763.2 inserts/second

Compound Index

After 295 seconds, 4497408 new records inserted - collection has 4497408 in total
14131 inserts per second since last report 98.63 % in under 50 milliseconds
0 keyqueries per second since last report 100.00 % in under 50 milliseconds
0 updates per second since last report 100.00 % in under 50 milliseconds
0 rangequeries per second since last report 100.00 % in under 50 milliseconds

~15,245.4 inserts/second (22% performance reduction)

Single-Field Index

After 295 seconds, 2438144 new records inserted - collection has 2438144 in total
6758 inserts per second since last report 35.55 % in under 50 milliseconds
0 keyqueries per second since last report 100.00 % in under 50 milliseconds
0 updates per second since last report 100.00 % in under 50 milliseconds
0 rangequeries per second since last report 100.00 % in under 50 milliseconds

~8,264.8 inserts/second (58% performance reduction)

Summary

The drop in performance ratio has reduced from when j:false. Previously it was about 40% performance drop with compound index and 78% performance drop with single-field indexes. Turns out, compound index isn’t too bad actually, taking into consideration that I created a 9 field compound index on the collection.

Regards,
Wei Shan

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s