Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We have an application which is C++ trying to access MySQL database.

There are several (about 5 or so) threads in the application (with Boost library for threading) and in each thread has a few objects, each of which is trying to access Database for its' own purpose. It has a simple ORM kind of model but that really is not an important factor here.

There are three potential access patterns i can think of:

  1. There could be single connection object per application or thread and is shared between all (or group). The object needs to be thread safe and there will be contentions but MySQL will not be fired with too many connections.

  2. Every object could initiate connection on its own. The database needs to take care of concurrency (which i think MySQL can) and the design could be much simpler. There could be two possibilities here. a. either object keeps a persistent connection for its life OR b. object initiate connection as and when needed.

  3. To simplify the contention as in case of 1 and not to create too many sockets as in case of 2, we can have group/set based connections. So there could be there could be more than one connection (say N), each of this connection could be shared connection across M objects.

Naturally, each of the pattern has different resource cost and would work under different constraints and objectives.

What criteria should i use to choose the pattern of this for my own application?

What are some of the advantages and disadvantages of each of these pattern over the other?

Are there any other pattern which is better?


PS: I have been through these questions:
mysql, one connection vs multiple and
MySQL with mutiple threads and processes
But they don't quite answer exactly what i am trying to ask.

share|improve this question
add comment

3 Answers 3

  1. There could be single connection object per application or thread and is shared between all (or group). The object needs to be thread safe and there will be contentions but MySQL will not be fired with too many connections.

This is essentially doing what the server is already doing, on client side. Unless the client side may fire a crazy number of connections and you don't have control on it, better just create connections and let server handles the synchronization.

Your second option does not necessarily incur a lot of connections. Take a look at Dynamic Scoping. It's easy to implement dynamic scoping in C++ so you basic have one connection per thread.

The 3rd option is more or less as opposite to option 1 - if you stick with one connection per thread approach, why not simply restrict the number of threads?

share|improve this answer
add comment

First of all I must say that per-thread connections (if its not persistence connections) is absolutely unsuccessful way of making system work faster unless it's a king of CGI application. Connection initialization could cost too much in the way of high-load. So if your question is

What criteria should i use to choose the pattern of this for my own application?

my answer is: we should always try to simplify our solutions, thus if your code simple and not suppose to be a high-load application - you can use simple in-place connection initialization operations; but in case of the high load applications persistence connections are preferred.

Are there any other pattern which is better?

Here is my receipt for "how to resolve the question". For one of my web application I used some king of your 3rd variant mixed with 2nd one in the way of connections were living in the system. So the strategy was quiet simple: on application init stage you create an Object, lets say a Database Connectors Pool (DCP) that is shared through threads and could serve up to N pre-created persistent connections. It could spawn as much connectors as needed according to application in-time behaviour. Unused connectors could be killed at run-time if needed.

Thus we have self-controlling connectors queue that is pretty fast for any high-load application.

Here is a simple example of how the pattern described myself above is realised in Python http://youdev.co/python-web-development-database-persistent-connections-how-to/

share|improve this answer
add comment

I would never try and share database connections across threads. Split it up and just have 1 connection per thread.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.