MongoDB auto increment field

This article for those who just jump into MongoDB ocean from RDBMS world. First of all, I can tell you do not start finding stuff which usually support by RDBMS into MongoDB. When you decided to start learning MongoDB then you should be consider yourself as a novice, then you can probable start learning it quickly. Just like A blank disk can efficiently store data than rewritable disk.

Today, I am talking about a scenario or case study about making ID field. Usually in RDBMS we can make tables id field unique and Auto Incremented. In order to make that kind of autoincrement in MongoDB, I found some thing horrible from developer. They made a different collection and using $inc operator to increment one fields value and use this value as an auto increment _id field to other document.

Unoptimized way of making auto increment, it will hurt badly the db server.

Developers had made the solution of the requirements and gone. Now when the load came, target collection was the payment collection and the load was 90-100 Operation / Second (OPS), following scenario observed.

  1. Payments queries are taking too much time despite of all queries are abide by the index and shading concept.
  2. From mloginfo –queries if you analyzed, this operation is most busiest operation.
  3. From the mongostat we checked many Queued operation and when we check the log file of the particular query numYields values are so high.

Query Analysis Results

Point # 1. Reason of queries taking time, because every time when payments api hits, it is first wait for the new autoincremented id value then it will execute the query.

Point # 2. This is the busiest because operations are so high.

Point # 3. Now, this point is confirmatory test. Actually when 90 -100 operations are trying to update a single document one field incremental($inc) value, the other operations are being Queued, it will be done one after another, not 90 operations executed simultaneously on the sever.
numYields value also so high, because the query is interrupted many times when it tries to be executed but Queued. It is also affects other query performance, making full db server overwhelmed by this queued operation.

Solution of Auto Increment

First of all you need to know few things.

  • By default MongoDB will produce a _id field for each document, either you specified a value or it will create a ObjectId for you.
  • _id field also by default unique indexed. Not autoincremented like RDBMS.
  • You can put string, number or other data type here at _id, condition is the value should be unique.

In our scenario, What we did, I mentioned it in following ways.

  1. First of all we dropped the sequencing/auto increment value collection.
  2. Then ask developers to generate unique number from the application end when the query will execute 90 OPS frequencies.
  3. We made the transaction_id field as an unique index, means if application unknowingly send some duplicate data the operation will trigger E11000 Duplicate key error. Prevents duplicate incretion.

Conclusion

MongoDB does not support auto incrementation of a field by default. Trying to achieve this kind of functionality in MongoDB you may end up with this kind of unoptimized things, which can tremendously badly affects your overall database performance. Hence requesting you, do not compare or try to execute something which you did in you past for tabular databases. Try to approach it very newly and try to adhere its core behavior.