General Question

Wilhelm's avatar

2 Concurrent Database Connections Per User Versus ...

Asked by Wilhelm (66points) August 23rd, 2007

I'm a PHP developer at the company I work at and we're going through a major transition from Sybase to MySql Enterprise.

One of our requirements is to have an initial database connection to a server designated 'slave'. When our database abstraction layer encounters a command that is either an INSERT, UPDATE, DELETE or CALL to a stored procedure, we need to use a different server designated 'master'.

Now, my question is, what are the pros as cons as far as performance and overhead between closing connection from server A and opening a connection to server B OR just having 2 persistent connections to both servers at the same time and just switch between them accordingly?

Right now we're doing the former method and I'm a little worried about performance issues because this is a VERY HIGH VOLUME network of sites that use these servers.

Any insight would be appreciated!

Observing members: 0 Composing members: 0

4 Answers

sferik's avatar

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)?

Wilhelm's avatar

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.

sferik's avatar

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..."

Wilhelm's avatar

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

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

Answer this question




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther