1 Mysql Connection Vs Many Small Mysql Connections

[leZourite]

Let's discuss, we know that when a page is generated it's faster to have the connection already opened, so far so good.

But in the long run what is better, 1 connection or multiple smaller ones ?

I used to do the first a lot before (1 connection to rule them all), now for my project i have switched to multiple small connections, but  i can easily switch to the other behavior if needed.

Example : say your generated content is cut in parts : 1 header, a dynamic content body, 1 footer, a very basic template system.

In the long run what is better ?

1header => connection to sql DB (via a db abstraction layer)
Inside the dynamic body, a bunch of queries are executed batch style
1footer => close the connection, destroy the object

OR

1header => Nothing there
Multiple small connections when queries are needed : note that some included content may be very very query intensive : (lots of updates/inserts/deletes), closed and destroyed as soon as the queries are executed~
1footer => Nothing there

I tend to think the first may be more efficient, because of the batch nature of query executions, the second may be efficient when small sets of queries are executed, but if there is a loop somewhere it multiplies the number of connections pretty drastically

[Zeggy]

My guess would be the first is more efficient, but I really have no knowledge about that
I always just use one connection.

[leZourite]

I have just finished my market/auction system, and the load of delete/updates/inserts is overwhelming to say the least (well mostly because i chose the auction system route), i tried to be on the small size regarding the numbers of request, but nonetheless  with the log system added it's a lot, so i think i am going to switch to the 1 connexion per page model...

[knuts]

 I think all you have to do to get a single connection is remove all your "mysql_close" statments - repeat calls to mysql_connect with the same identifier will reuse the open link, and the connection is closed automatically at the end of the script . . . there is always at least some new connection overhead,  supposedly it is low on MySql but it depends on network setup etc.

If you have a lot of users hitting at once and you think connection overhead is making a difference (and have access to the server setup) you could use persistent connections http://us.php.net/manual/en/features.persistent-connections.php

Good Luck !

[leZourite]

hi !

Yes this is what i have done first (for my big user class) removing the instance to closing the object, but even, as i am calling the database class many time it could be a problem, there is a simple way though (as i showed on the first example).

For the persistent connection it might not be a good idea at first, because the hosting is not yet dedicated... may be later depending on the load, but looking closely to the matter it won't always help.

[mobeamer]

In my humble opinion there are two things you have to be aware of when opening a connection:

1. You are consuming a server resource, the connection will stay open till you close it or the page dies. This consumes memory.
2. Every time you open a connection it takes a few CPU cycles thus impacting the speed of your code.

Every time you open a connection you pay these cost, so in general the less connections you open the better.

I'd be interested in seeing a test of this. Create a page which opens and closes 1,000 connections with a simple sql and one that opens one connection and runs 1000 sqls. Then compare the time results.

[leZourite]

I did a lot of researches on the matter unfortunately i didn't find anything that relevant, only that an opened connection = Faster queries executions.

Under load the first scenario will only open 1 connection*X people on the site and that's all, but in the second scenario it will be n connections*X people and thus ending up killing the whole thing.

What i did once though was a test in a loop that does 4 intensive sets of queries 1 select, 1 update, 1 insert, 1 delete (i was playing with the idea of transferring items between users and there was some logic to apply having to deal with inventory space, logging etc etc)  up too 1000 send iterations the server didn't really lagged, when i tried to go up to 10 000 though the server went where the sun doesn't shine )

i then changed the way it works and limited the amount of transaction, though if i get 10 000 requests at once the server will most likely to start to fail... This was in a loop of connections/disconnections.

And frankly i think that Memorry Vs Cpu Cycles, i would gladly go for the first, memory is quite cheap it's far easier to have better memory. More over if things go that badly server clustering comes to the rescue !

[greendots]

Creating and closing connections are large overheads*, because this type of question only really gets asked when there are many queries to be excecuted in a complicated application scenario.

There is nothing wrong with reusing connections as long as they are released correctly and promptly when no longer needed.   pconnect is an option also.

Basically, do what you like.

If you make 1 connection, you have to be prompt in releasing it when no longer required.
If you make many, you have to profile your app to make sure you are not spending too much time creating/closing connections.


*in relation to the speed at which queries are executed.

[Political Monster]

The only issue with one long one is other users have to wait. By splitting connections up a little bit (not much), you allow a good cue system to form, however in reality, the differences are very little, unless your grabbing a huge amount of data. I probably have about 2 connections per page.