PDA

View Full Version : Does multiple MySQL DB-connections increase performance?



Tottish
14th April 2011, 14:09
Hello!
I guess this is more of a MySQL question but since it involves how to interface to a DB-server from C++/Qt I would say it is appropriate to post here. =)

I'm currently developing an application that is connecting to a MySQL database server run on a multi-core PC.
I don't know much about MySQL but I'm starting to get hang of the basics.

I'm designing a class (derived from QThread) that is run on the client computer and I'm now thinking about possible differences in performance if I create multiple instances (and thereby connections to the server) of this class for each client.

I have no idea on what MySQL Servers policies on threading looks like. So I thought: IF the server only allows for one thread to start for each connection to it I will probably have a better performance if I have two (or more) connections.
The server will most likely never have more than say 10 clients connecting.

The reasoning is that the small 'simple-few-rows-SELECTs' should still be snappy even though a complex query (that might take a second or two) ordered by the same client is being executed at the same time.

This multi-connection approach feels kind of wrong but I still wanted to ask.
Your thoughts?

Thanks!
/Tottish

_Stefan
14th April 2011, 16:21
Hmm, I guess when you create a connection, the database creates a session for it.
Within that session, for as far I know, you can only execute one query at a time. A query is also a blocking thing, I don't see how else the database server can safely handle your transaction.

I thought there is something like connection pooling and I think Qt uses that, but that is for not opening and closing the connection all the time, but rather reuse the same connection to the database that is already open (hence, saving you the overhead of opening and closing the connection again).

So for that matter, if you have a time critical check (the simple-few-rows-SELECT's), that cannot wait a few seconds for complex queries to be executed, you even MUST use multiple connections.
I do not think this is a performance issue though, especially when you are talking about 10 connections max. After all, they are "just" TCP/IP connections which exchange some data.
Just the database has to do some stuff in parallel, but hey, that is what it was designed for, isn't it ;)

So in my opinion, multiple connections all the way ;)