Drawbacks of Persistent db connection?

Anon

I'm wondering what the drawbacks are of ALWAYS using a persistent connection rather than the regular mysql_connect().

More load on the server I'm guessing?

Anon

Actually LESS "load on the server" if you are making multiple selects from the same table from the same script. There is no gain/loss if it's just a on-time deal. Somebody please correct me if I'm wrong.

--ph

Anon

In the manual it says:

"Persistent connections are SQL links that do not close when the execution of your script ends."

Then this means that the connection stays "open" even after the script ends. How can this be good if you are just doing simple SQL statements (eg. visitors visiting your webpage and viewing it).

Anon

If you are going to use a persitstent connection, you should use the mysql_close() function at the end of your script.

--ph

Anon

Suppose your web server has keep alive turned on. If you write an app that creates and uses a persistant connection, and the user just quits without "logging out" of the app (which is where you'd close the persistant connection) then the connection stays open to the db as long as your keep alive time out is set for, typically 10 minutes.

Most databases use about 512k to 1M memory per backend, and often having a lot of backends for any database (not just mysql) can cause the database's overall performance to suffer.

This is especially true if you don't have enough ram for all the backends and your server starts swapping ram to fit the backends into memory.

If apache is set for max children of 200, and you've only got 64 Megs of ram, then you'll run out of ram long before you get to 200 httpd processes running.

It's a good idea to figure out how much each apache/php and postgres child weighs in at.

While running an access through the web server, use top to capture the numbers for an average httpd and database master daemon.

For my postgres box:

postgres Size: 2340 Share: 1896
httpd Size: 1776 Share: 1352

Numbers should be similar for most databases.

So the first process of each costs us a fair bit, but each child only costs the difference of the size and shared memory. I.e. postgres costs us 2.3M+(2.3M-1.9M)n-1 or 2.3M+400k(n-1), while apache costs us about 1.7M+400k*(n-1).

so, each child costs about the same. Now the trick is to limit the apache server so that before we run out of memory and kill our box, people start getting no response due to lack of available children. Better the box go slow than down.

We can fudge the number to about 1 Meg per child (fudge is good, better to guess high on this) and just say that we can handle about 1 child per meg on the box. so, for a machine with 128 Megs ram, assuming the OS can live on 16Megs or so, and a little spill into the swap file, we could handle 100 or so clients max. so, set apache or whatever web server you use to that number of max children and you'll be ok.

If you hit a dbase once on the front page, once on the middle, and maybe two more times, you probably aren't going to gain alot unless your database eats up a lot of time to make connections. On postgres, it's well under 5% of the total time to execute smaller transactions, and gets smaller with larger numbers of queries in the same script, so it's no big deal. I don't know the numbers for other dbases and connection time.

Anon

No peter, read the manual. :-)

Anon

The idea of pconnect is that the connection does stay open.
ie: php does not have to open a new connection every time it wants to use the database.

This does NOT mean that every time you connect to the database a new connection is made that stays open.
Old connections are simply re-used.

This gives a small performance increase because you don't have to wait for your connection to be built.

Scott has given some valuable info about how many process you should run if you have X memory, but that is also true for non-persistant connections.

The only benefit of pconnect is that there is (hardly) no delay in opening the connection.

Anon

An open connection doesn't cost you much. Opening new connections is the big hit in performance.

AFA using mysql_close() that function does not close persistent connections. (if it did that would defeat the whole purpose).

Think of the php module connection to mysql as a shell. You don't have to log on to mysql for each query. IOW, if you have a mysql_(p)connect() call PHP first checks to see if there is an already existing connection to the same mysql host using the same username and password. If so, then it uses that connection. But with pconnect the chances of that connection already being there are much higher, reducing the overhead of the connect. Again, it doesn't take much for the server to maintain that connection.

Regarding Scott's comments:

I'll stipulate the numbers he provides for arguments sake but they are irrelevent. The total number of connections is dependent on unique host,user combinations, not the number of calls for a connection. If a connection already exists (either thru a plain _connect() by a simultaneously running script or by a persistent connection by a recently run script) for the same host,user pair than that connection is used and no new connection is created (note: on some systems a generic connect() has been reported to open a new connection when it is not necessary -- another reason to use pconnect() -- though I've not experienced this).

Scott's numbers only come into serious consideration if the number of unique connections (again, determined by unique host-user pair, NOT connection requests) is high, which is usually NOT the case.

Anon

Regarding Rod's comments:

"You don't have to log on to mysql for each query. IOW, if you have a mysql_(p)connect() call PHP first checks to see if there is an already existing connection to the same mysql host using the same username and password. If so, then it uses that connection."

From the PHP manual it says:

"In particular, they do not give you an ability to open 'user sessions' on the same SQL link, they do not give you an ability to build up a transaction efficently, and they don't do a whole lot of other things."

Does this mean that only a single user can reuse the persistent connection and not multiple users running the same script?

Anon

The only user that "runs scripts" on your web server is usually 'nobody'.

So, no matter how many different people are visiting your website, it is still the same user as far as PHP/MySQL are concerned.

Anon

Regarding my statements, note that I stipulated that IF you were running apache with persistant connections, you would have issues with simos.

With keep alive on, each web server "child" process pretty much belongs to the single client on the other end that opened it, and stays open for it for up to the limit of the time out on the keep alive.

I don't know what kind of testing you've done, but if I set up either mysql or postgres apps to user sim connections, go to ten different web browsers on ten machines access the same page with a persistant connect on it, I will get ten apache children, plus the min-spares, plus I will get ten database backends created.

If I go to ten more machines, and open ten more sessions before the time limit on keep alive, it doubles the numbers of both web servers and backends.

Note that most apache installations have keep alive turned on by default, which will cause this quick starvation as a bunch of clients access the machine.

My experience has shown that even a lightly worked web server with a lot of small bandwidth concurrent connections can memory start quickly with persistant connections, and if you haven't tested it with a lot of clients.

Anon

WEll for each vistor the script is going to have to open a new connection to the database, can take upwards of a couple of secs to do. If you use persistent connections, all the visitors will use the same connection to the database and save you the overhead of those couple of seconds (which in web time is almost like a couple of minutes).

Anon

So how do you close the connection, when does it get closed?
I guess if you only have one it doesn't matter if the pconnect call ensures that's always the case?

Ian.

smarlowe

You don't close persistant connections, the web server / database handles that. Basically, if you have persistant connects on, you either need to limit the max number of them for the dbms access family you're using (odbc,pgsql,mysql,oracle, etc.) or the max number of web server children (maxchildren in apache).

The application only opens connections, the environment then manages the connections and decides when to connect or disconnect.