MongoDb vs MySql - Fight !!

Simplicity is the ultimate sophistication
- Leonardo Da Vinci -

Relational databases, and to a degree MySQL, have become the backbone of commercial organisations. Programmers and database developers have been engaged in a tug-of-war, deciding the best place to store and execute the business logic.

Recent attempts to objectize the Database (Hibernate, JPA, iBATIS) have had cult-like following. But for performance purists (not to mention DBAs) Object Relational Mapping (ORM) solutions are often plagued with performance limitations and gotcha's, such as caching issues, type casting problems, transaction problems etc.

So when a new breed of JSON 'document style' storage servers arrived we approached them with some sceptcism.

A quick, personal (non rigourous) comparison

The following highlights a typpical user story of one of our customers:

Insert 10000 records Table
Update 1000 records based on a non-key'd update
Update 1000 records based on a key'd query

on my small development server I get :

Mysql

Loading test data …

  • Execution time of loadTestData was 16327 ms. for 10000 entries
  • Execution time of updateRandomRecord was 31151 ms. for 1000 records
  • Execution time of updateRandomRecordKeyd was 1592 ms. for 1000 records

Now lets compare this to the new breed of NoSQL databases. I used MongoDB in this case because it is C++, thread safe and deployable to Windows, Mac OS X and Linux (including our ISP), plus it supports Java, .Net, PHP connectors.

MongoDb

  • Execution time of loadTestData was 1912 ms. for 10000 records
  • Execution time of updateRandomRecord was 6484 ms. for 1000 records
  • Execution time of updateRandomRecordKeyd was 1097 ms. for 1000 records

The Quick Takeway

  • Key'd updates are comparable
  • Inserts and non-key'd updated on MongoDB are significantly faster.

Some interesting other factors regarding the B-Tree database

  • memory usage is fraction of MySQL, meaning it's much easier to spin up multiple servers
  • being schemeless, it is much easier to create and modify collections (equivalent of tables) which will be required by our customers
  • development was also magnatudes of order faster on the document storage database. This will be further increased if we used the native JSON format on the application front end.

The main down side I have seen so far:

  • With a NoSQL database you have the option to perform a batch update but batch operations
  • SQL-type queries and datamining becomes more difficult or at least requires different solutions (Map/Reduce)
  • MongoDB does not current support transactions (but it does support autosharding).