How to optimize number of database connections?

Question

Hello everybody!

We have a Java (Spring) web application with Tomcat servlet container. We have a something like blog. But the blog must load its posts dynamically with Ajax. The client's ajax script checks for new posts every second. I.e. Ajax must ask the server for new posts every second and it will be very heavy for database.

But what if we have hundreds of thousands connects simultaneously? I think that we must retrieve all posts with cron every second and after that save it somewhere. But where? The main idea is to unload the database. Any ideas about architecture? Thanks in advance!

Answer 1

Hundreds of thousands of concurrent users all polling our site every second makes for a huge amount of traffic. If you truly expect this load you are going to have to design your platform accordingly, probably by clustering multiple web, application and DB servers.

Remember that with a database connection pool you don't need a DB connection for every user.

Answer 2

I'm not as familiar with Tomcat, but in WebSphere we can set up connection pools to prepare a certain number of connections.

Also, are you mainly worried about reads or the same number of writes?

Plus, you may also want to have the database "split" depending on region etc. This way there is no single heavy load across the entire database, but it can then be split and even load balanced.

There is also the "NoSQL" databases to look into as well. Maybe something to consider. Just ideas to help out.

Answer 3

There is other architecture for polling that could be more optimal, depending on the case:

Long polling

Long polling is a variation of the traditional polling technique and allows emulation of an information push from a server to a client. With long polling, the client requests information from the server in a similar way to a normal poll. However, if the server does not have any information available for the client, instead of sending an empty response, the server holds the request and waits for some information to be available. Once the information becomes available (or after a suitable timeout), a complete response is sent to the client. The client will normally then immediately re-request information from the server, so that the server will almost always have an available waiting request that it can use to deliver data in response to an event. In a web/AJAX context, long polling is also known as Comet programming.

Long Polling

Example of Implementations of this technology: Push Server

You could also use the observer pattern to register the requests, and notify them when an update is done.