Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com
Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

In part 1 and part 2 we looked at running concurrent workflows in SQL Server and MongoDB with different configurations of context, isolation and journaling.

MongoDB was by far the better performer, in all except Variant 4, where the client waited for the server to commit changes to the journal before acknowledging the response, at the default journal commit window of 100ms.

In a production system, we’d be looking at using variant 2 or variant 5 of Mongo. If we don’t need ultra-reliability, then we can go with variant 2 – which still uses journaling to ensure data integrity if the database goes down, but could give the client a false acknowledgement of their changes being committed in failure scenarios.

If we do need to be sure that the data really is safe when Mongo acknowledges it, then we need journaled acknowledgements, but if our workload is consistently high we would tune the commit window as in variant 5, to give us the extra safety without impacting performance.

With SQL, we would be using variant 2 if we can’t afford dirty reads, and variant 3 if we can. So comparing variations by reliability, we would be using Mongo v2 and SQL v3 if we could afford less reliability:

image

Or we would use Mongo v5 and SQL v2 if we needed maximum reliability:

image

 

Either way, Mongo outperforms SQL by a very significant margin – over 3 times better in the less reliable scenario, just under 3 times better in the reliable scenario.

Why

With high concurrency, you get high contention – multiple clients all trying to access the same resource at the same time. All databases use locks to protect those resources and ensure data consistency, but different locks have different costs.

Inserting data typically has the biggest cost, as the database needs to take a wide-ranging lock, either on the whole table/collection, or on the page where the data is stored, to make sure two inserts don’t run concurrently and try to append data to the same location.

The major performance disparity we see is largely due to the cost of those locks. In the SQL Server and Entity Framework model, every workflow inserts one row to the entities table and 15 rows to the entity events table, so every workflow has at least 16 expensive locks, which will cause other workflows to wait. The wait may only be for milliseconds, but at high concurrency and high load, small delays quickly add up.

In Mongo, we modelled events inside our entity document, so each workflow makes one insert into the collection, and 15 subsequent updates. An update lock is much cheaper, as the database only needs to lock a known set of data, and in our case no other workflows need that data, so they won’t be queued by the locks.

It’s not a fair test!

Yes it is. We’re using Mongo as it should be used, and SQL Server as it should be used. They’re fundamentally different technologies which need different modelling techniques.

In this scenario, the appropriate model in Mongo saves us a lot of expensive inserts, so our concurrent load spends more time processing and less time waiting for locks.

Of course, there are other differences in the technology. With EF the code uses LINQ and our queries get converted to SQL, which has a cost, as does populating the domain model from the data readers. With Mongo, we work with the data as it’s stored, so there’s no command translation and our mapping is just serialization and deserialization.

As always, your mileage will vary, but if you’re running with EF and SQL Server and hit a contention issue Mongo could be an option. With a domain-driven design it could be fairly straightforward to replace the EF infrastructure layer with Mongo and run a POC to see how much of a performance boost you get.

Get the code

It’s on github here: Sixeyed.LockComparison.Mongo. VS 2012 solution which uses NuGet package restore. If you want to run the tests, you’ll need SQL Server set up and MongoDB running.

For reference, I ran the tests in a Windows Server 2012 VM allocated 2 CPU cores (at 2.5GHz) and 8Gb RAM, running from a solid-state disk on a MacBook Pro. Sample result run included below:

image

Posted on Sunday, January 5, 2014 9:29 PM SQL Server , Mongo | Back to top


Comments on this post: The concurrency shoot-out: SQL Server versus MongoDB (part 3)

# re: The concurrency shoot-out: SQL Server versus MongoDB (part 3)
Requesting Gravatar...
It would be really useful to see the scripts you have for SQL if you don't mind. Also a trace of the typical query that gets passed to SQL would be extremely useful.

The reason I ask is that I suspect there is a fair chunk of time to gain on the SQL side (just a gut feel). This covers things like, indexes, query execution plans (inc plan re-use), DB file structure, queries used and so on.

I don't disagree that MongoDB is fast but I do wonder if you have an optimal SQL setup. Admittedly this likely means that in terms of a faster development turn around MongoDB is a quicker development tool, but if your concern is about handling concurrency then this is more about live estate issues than development.

I came across a test a while ago that claimed MongoDB was faster than SQL (the example was creating 500k unique identifiers and a basic document structure). I set this to my SQL team and every one of them improved on the times set by MongoDB in the example given. I assume that the MongoDB times could also have been improved on, but hey the original SQL work in that example was flawed.
Left by Ryan Offord on Jan 06, 2014 8:38 AM

# re: The concurrency shoot-out: SQL Server versus MongoDB (part 3)
Requesting Gravatar...
Good point, but in this case the DB is very simple - I don't think there's much room to optimise. Feel free to check it out: https://gist.github.com/sixeyed/8323968 and comment if there are any reasonable tweaks.
Left by Elton on Jan 08, 2014 8:27 PM

# re: The concurrency shoot-out: SQL Server versus MongoDB (part 3)
Requesting Gravatar...
I couldn't post sufficient detail in here. I've posted on my blog and you can see it here

http://underwaterryan.blogspot.co.uk/2014/03/mongo-concurrency-shoot-out-more.html
Left by Ryan Offord on Mar 31, 2014 9:14 AM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net