mysql_connect vs pconnect()

dd3123

I know the foremost opens a connection, and pconnect opens a persistant one, but why would I want to use pconnect instead of connect (or visa versa)?

undertow

Well, as it turns out, pconnect doesn't do what (probably) most people want it to do, which is have one connection to the DB server that all hits to the PHP scripts will use. However, this isn't the case. What happens is that since the persistant connections never close, you will eventually hit some maximum number of connections on the server and you'll have to restart it.

I'd say you're better off using the regular mysql_connect(). Probably you should use the mysql_close() function as well; I don't know whether you'll get the same errors if you don't close a regular connection.

dd3123

I've always used mysql_connect, I just started to think about pconnect and I wasn't really sure why anyone would ever use it.

dannys

Originally posted by undertow
Well, as it turns out, pconnect doesn't do what (probably) most people want it to do, which is have one connection to the DB server that all hits to the PHP scripts will use. However, this isn't the case. What happens is that since the persistant connections never close, you will eventually hit some maximum number of connections on the server and you'll have to restart it.

That's not entirely true - if you're using a modular version of PHP (i.e. compiled as an Apache module) then PHP will be able re-use connections left open by pconnect. If you're using the cgi version then it can't.

In short - if you're using the CGI/Standalone version of PHP then steer clear of persistent connections. If you're using a modular build and the limit of simultaneous connections isn't very low then give them a try as they can boost performance - especially if the db connection overhead is high.

dd3123

So if I had a high number of connections allowed, I woudl start up the pconnect at the start of the page, run a bunch of sql queries (or whatever) and then close it (pconnect) at the end of the page for a performance gain? I'm assuming this gain is then because multiple sql statements inside the page do not need to connect each time?

undertow

Strange, it doesn't seem to work for me. I have PHP compiled as a DSO for Apache. Persistant connections are enabled on both the MySQL server and in php.ini. But when I use something like phpMyAdmin or my own scripts (I just brought up phpMyAdmin because it must use a heck of a lot more connections since that's what it's designed for) with pconnect, I always gets this "too many connections error. Oh well, what works for me, works for me I guess :p.

Originally posted by dannys

That's not entirely true - if you're using a modular version of PHP (i.e. compiled as an Apache module) then PHP will be able re-use connections left open by pconnect. If you're using the cgi version then it can't.

In short - if you're using the CGI/Standalone version of PHP then steer clear of persistent connections. If you're using a modular build and the limit of simultaneous connections isn't very low then give them a try as they can boost performance - especially if the db connection overhead is high.

Radio

You only need to make one call to the mysql_connect() function in a script. There shouldn't even be a need for mysql_close() since PHP will close the connection automatically at the end of execution. mysql_pconnect() however leaves the connection open after the execution of script. Reduce the database overhead generated by subsequent executions of the script.

This assumes that persistant connections are allowed however. If that's not the case, then the connection is closed regardless of what function you call.

dd3123

No, maybe I didn't make myself clear. I only use 1 mysql_connect per page. Oh well, in any case, why would you leave the connection open (with pconnect)? It seems that it would be a good way to have a connection stay open when it doesnt need to (such as if the user closed the brower before getting to a mysql_close)

dannys

Using pconnect allows multiple calls to the script (and any script which uses a identical connect params) to use the same db connection instead of going to the trouble of actually opening a new connection each time.

Say, for example, that your site has a total of 100 pageviews during the space of a few mins.
Using connect will open and close db 100 times.

Using pconnect will open the db once and keep that connection open until it's gone unused for a specified time, only then is it closed.

Looking at it this way, I'm sure you can see where the potential benefit it is, especially if the db is slow to actually open connections.

dannys

Originally posted by undertow
Strange, it doesn't seem to work for me. I have PHP compiled as a DSO for Apache. Persistant connections are enabled on both the MySQL server and in php.ini. But when I use something like phpMyAdmin or my own scripts (I just brought up phpMyAdmin because it must use a heck of a lot more connections since that's what it's designed for) with pconnect, I always gets this "too many connections error. Oh well, what works for me, works for me I guess :p.
[/B]

Interesting, though you don't say what version of PHP or Apache you're using.

undertow

PHP: It's either 4.0.6 or 4.2.1
Apache: 1.3.22 with the patch from RedHat to 1.3.26
MySQL: Not sure exactly, it's a 3.23 though
phpMyAdmin: 2.2.6

Yes I know everything except my Apache and MySQL are out of date, but I'm upgrading everything when I get back from vacation :p.

Radio

Originally posted by dd3123
No, maybe I didn't make myself clear. I only use 1 mysql_connect per page. Oh well, in any case, why would you leave the connection open (with pconnect)? It seems that it would be a good way to have a connection stay open when it doesnt need to (such as if the user closed the brower before getting to a mysql_close)

I suggest you look at the PHP and MySQL documention some more. Leaving the connection open with pconnect means that resources are spent on connecting only once while the server is running. Sub-sequent calls to the script would make use of that same connection made in the previous execution.

When PHP's running as a SAPI module, it's running with the server as in it starts and closes when the server starts up and shuts down. Whereas a cgi-bin install of PHP is opened only when a script is requested. This is slower mainly due to the constant opening and closing of PHP which makes it impossible for it to hold a persistant connection with MySQL.

mrmufin

For this example we'll assume PHP is installed as a DSO, we're running Apache 1.3.x (since 2.0x may behave quite differently), and using MySQL 3.23.whatever. Consider the request/response process when index.php is requested (the first time):

Apache handles the request by by doing one of two things: passing the request to an available child process or spawning a new child process. This process has many governing factors, but the big one here is a setting (in the httpd.conf file) called 'KeepAliveTimeout'. Depending on how much you've 'fiddled with the knobs', that value (which is a value in seconds) may be anywhere from 5 up to 30, or lower or higher. Most installations of Apache that I've done set a default value of 15, but this value is quite configgerable.
The child Apache process recognizes the file as PHP and shoves it through the PHP interpreter. PHP does its thing, including making any requested database connections. When the Apache child process finishes, the Apache parent process keeps that child process alive for the 'KeepAliveTimeout' value for additional requests from the same host.

If you make a subsequent request to the Apache server before the parent process kills the child process that handled your previous request, and you used pconnect() to attach to the database, you'll save some connection overhead. Otherwise, the benefit of pconnect() is lost.

Bottom line: the right answer depends on your sites traffic, how long Apache keeps child processes laying around for, and a bunch of other stuff (like the MinSpareServers and MaxSpareServers settings in the httpd.conf file), and the number of concurrent connections your database engine will support.

IMHO (and O's are like A's, we've all got 'em), I think it's best to not use pconnect() in most situations, unless you have the ability to fiddle with the knobs on your server config file, your PHP.ini file and your database configuration. I think it's just good programming practice to clean up yer rousources when you're finished with 'em, so the next guy has some system resources to use. Your mileage may vary...

SKennedy

On servers where the database and webserver are on the same machine, there is little point to pconnect over connect, I know, I've benched them.

The real advantage, I think, is when the database server is somewhere else. Then the connection doesn't have to deal with the latency of the initial connect request, it can straight ask for data.

You folks who don't close your connections, shame. It's only polite to clean up after yourself.