2 Concurrent Database Connections Per User Versus ...

Establishing a database connection is one of the most expensive database operations. It requires authentication, opening files on a disk, creating memory caches, etc.

This might make you think that opening two persistent connections is the way to go, but it really depends on how your site is used. If the vast majority of your users are only performing SELECT operations, then opening that second persistent connection to the master database will eat up memory and slow the loading of the ever-important landing page.

As with many design problems, the correct solution requires understanding how your users will be using your application. Once you understand that, you can do some benchmarking and make the correct decisions about how to optimize your application.

Is it possible for you to intelligently connect users to the correct database (e.g., connect a user to the master database only after they have logged in as an "editor" or some similar role)?

Thanks for the quick response!

I'm very well aware of how taxing establishing a connection to a database server is when compared to other operations, but this is one of those "When the DBA says JUMP..." moments.

I'm just trying to figure out the most elegant and practical way to accomplish this, if there is one. I honestly don't know why they don't just do something as simple as having a small dedicated server farm that sits behind a load balancer; these guys definitely have the resources for it.

Honestly, this is a very popular teen dating site with 4 million users that dishes out 100s of thousands of hits a day. There are a LOT of heavy queries involved and I've done my best at making them as streamlined as possible, but the DBA's think separating the fetching queries from the manipulation queries like this will help performance issues.

As for your last question, the answer would be no. All users who log in have the same abilities as everyone else.

Thanks, though. It's actually a pretty interesting topic I've never really took the time to sit down and think hard about.

The fact that users have to login means that, at the very least, you can probably wait until they have done so to establish a connection to the master database.

You may want to ask the DBA why he thinks splitting the queries across two databases will improve performance. Ask him where the bottleneck is in the current system, how his proposal addresses that bottleneck, and what he thinks the next bottleneck will be after this system is implemented. Ask him how his solution compares to a load-balanced server farm (which, I agree, seems like most obvious solution if the resources are available). Most DBAs are comfortable discussing things in these terms. It may be the case that you make him think about something he hadn't yet considered. It's also possible that the DBA understands a subtlety of the system that wasn't articulated "when the DBA said jump..."

True enough! I'll discuss this with our DBA then. :)

Thanks for all the help. I'm liking this site more and more.