Is decoupling web and database tiers via MSMQ necessary or overkill?

Question

I'm putting together a simple asp.net web control, that as the result of an ajax form post inserts a record into a MSQL database.

It's possible that the page containing this control will recieve many thousands of hits in a small space of time and I'm worried about the performance issue of opening a database connection, inserting the record and then closing the connection for each request.

A solution that has occured to me is to make the web control place a message into an MSMQ queue and have a Windows Service on the server periodically read the queue and do a batch insert.

Does that sound like a sensible architecture given that the web and database servers are running on the same machine. Does it sound necessary?

From what I've read on the database most of the benefits of using MSMQ are to do with resilience rather than performance, so I might be barking up the wrong tree.

Any advice would be very gratefully appreciated

Many thanks

Pete

Globeflower

Answer 1

Processing requests offline in this way is a common pattern for when you are dealing with a high volume of requests.

Whether you should or even can do this is based on a number of factors.

The main factor is: do your callers need to see the response to their requests synchronously?

What I mean is does the caller have to know, immediately and with absolute certainty, if the database successfully committed their data as part of the call response? If so, then taking request processing offline is not really an option.

However, if it is acceptable that callers can be sent a response saying that their request will be processed offline (and any required response also sent offline) then using a queue will definitely benefit your overall architecture.

The first thing that will be benefited is front-end availability issues.

If you are processing thousands of requests over a short period, and each request is serviced by a thread which then goes away and inserts data into a database, very likely you will run into the problem where no available dispatcher thread exists to service incoming requests.

However, by reducing the amount of back-end work IIS is doing (writing to a local queue is really cheap relative to a database call) you are also reducing the likelihood of availability-based failure.

Secondly by using a queue you will have a means of throttling back-end traffic, because you have control over the throughput on the back-end processing.

For example, you could have a a single-threaded queue reader process which de-queues a request and processes it into a database. If you find you have messages building up in the queue you can just scale out the queue reader service by hosting more instances of it.

What this means is that you are reducing the likelihood of getting database contention issues because you have more control over the number of accessing threads on the database at any one time.

So, by using queuing you suffer fewer failures and have lower management overhead, which are the hallmarks of well written applications. This especially when you are considering hosting your database on your web server!

Also, you should have a read up of an architectural pattern called CQRS, one of the central principals of which is to do your database writes offline.

Adorl

Cracking answer Hugh, thanks very much for that. Confirmed what I suspected and a whole lot more! – Globeflower

+ 1 for the great comparion and explanations of the options. Thank you very much. – Hussite

Answer 2

An asynchronous architecture has many benefits (as identified by Hugh) but definitely has a greater cost and complexity associated with it. This should be balanced carefully.From 2 components (web app and DB) you will now have to develop, maintain and monitor 4 components (web app, DB, batch service, MSMQ). You said that web server and DB server is same machine which is strange given your performance requirements. Tomorrow you may have to host the web app, batch service and DB is 3 separate machines. Now MSMQ involves the network as well. One more thing that can go wrong. Multithreaded batch processor is also not easy, what if you have to process each message in the sequence they were generated (at least for a given entity) logic can become complex.

If possible measure both solutions and decide.

Ripon

I take your point on monitoring overhead, yes, there are more things to monitor. However, totally reject that the approach is of greater complexity. A solution with a high degree of coupling is more complex to deploy and maintain. Losely coupled solutions are simpler even though there are more moving parts. Each part has a clear responsibility. Also, no multi-threaded batch service is needed, just a single threaded queue listener service. Agree that scale out can effect order of delivery but this is unavoidable. Also MSMQ is part of the Windows sub-system and does not need to be "developed". – Adorl