connection pool

thepichi

Hi, i'm thinking about what's the best way to make db connections in a web application.

It's clear that persistent connections have better performance and improve the time of responses, because you don't have to make a new connection every time that a script runs.

But i see a big problem here, if a have a site with a lot of concurrently visits, all those scripts use the same connection, then maybe will produce many collisions, that's a problem.

I've seen in other languages, an interesting solution, a connection pool. Anybody knows if exist any implementation of a connection pool for PHP?

ahundiak

mysql does support persistant connections which is basically a connection pool. A given connection can only be used by one script at a time so collisions are not a problem.

However, it's possible for a script to do something very anti-social such as failing to unlock a table before existing. The table would remained locked for as long as the connection exists. Which is usually a bad thing. Another bad thing is that a database typically has a limit on the number of open connections that can be maintained. Using persistant connections tends to use up available connections.

Furthermore, the speed advantage offered by persistant connections turns out to be trivial and seldom makes a real difference in the overall executation time of a script.

The bottom line is: don't use persistant connections.

There is much more information on this subject in the manual.
Well worth reading.

superwormy

Unless your website has an extrordinary number of hits all at once, you're using a connection for each query ( bad practice ) or you're accessing a remote database which is very slow to establish connections, you shouldn't worry about it.

You'll rarely ever need to worry about this, and in practice, persistent connections rarely help except in very specific situations.

Kefka

Another bad thing is that a database typically has a limit on the number of open connections that can be maintained. Using persistant connections tends to use up available connections.

Just wondering....
What conditions DOES a persistant connection close under? Must it be closed manually, or will pcons remain open indefinately once opened (hence causing the problem with reaching the connection limit)

Also, I'm a little confused at what causes a new connection to be opened or an existing one to be reused...

Say I have a website which has a database. All the visitors of the website have the same access to the database, with no logging in... eg browsing a library catalogue.

After reading the info on p_connect, I get the impression that persistant connections WOULD be useful here, as one connection could serve all visitors (only creating a new one when two visitors try to use it at the same time).

After a while of operation, there may be 10 or so connections open, due to people trying to query at the same time.... The chances of more than 10 people querying at the same time and hence causing more connections to be needed is pretty low...
So in effect, this has become a connection pool.

...or have I made a big logic error somewhere?

~Kefka

superwormy

You'd think it would help, but all the tests I've done and all the benchmarking I've done show a slim to no benefit in a scenario just like that.

Establishing a connection to the database is way fast, and it normally doesn't make any difference whatsoever whether you establish a new connection on each hit or use one that's already been opened.

Try it and see.

Kefka

How do you determine the difference?
I've been wondering where to find a program that can test and report stats and information (speeds, timing, connections, hits etc...).

Any idea where to get soemthing like that for MySQL or Apache etc?

superwormy

Look around for web server load testers, search Google, theres tons of them.