Re: Keep Mysql Connection Open?

[Gareth Watson]

Jonathan,

The correct answer to this would be keep the connection open when there are periods of high activity and close it when the activity dies down. This really is stating the obvious and I am sure the reason you have asked the question is that your application is not in a position to predict the activity level.

No worries. I can tell you what decision we took, rightly or wrongly :)

We open and close a connection with every query! We find that we are turning most of our queries round in about 5ms! So the connection setup cost does not seem to be that high.

However, where we do have multiple dependent queries we run these inside a transaction (these always involve some sort of update, insert, delete and then a select) therefore in these instances we have the same connection open to run the commands against.

The ratio between explicit transactional commands and standalone commands (I realise there is an implicit transaction here but I am trying to say we run a single command at per connection) is low. In that there are many more single command connections than multi-command transactional ones.

We are running a pretty busy service and as yet have had no performance problems from the database layer (now I need a piece of wood to touch :).

I hope this helps you or at least gives you the confidence to proceed in one direction or another.

Finally, if anyone thinks what we have done is a bad idea I would welcome your input. Any performance gains that can be made are always beneficial.

Cheers,

Gareth