[Resolved] MYSQL pconnect vs connect

bluesharp

Many of the php/mysql texts I've read on the subject usually tout mysql_pconnect() as the way to go due to the efficiency of the persistent connection.

After scanning this forum, it appears that in practice one is better off using the non-persistent version. I assume that having control over the opening/closing of the connection is worth more than the cost of the overhead incurred.

I would appreciate if anyone could comment on this, particularly if you have experience from a production perspective. Also, does anyone know if there is a breakpoint at which the number of users/connections begins to really adversely affect performance?

Thanks very much.

drawmack

According to the manual the two distinct differences are that pconnect will attempt to return a link to an already open connection first and that the link does not close when the script terminates.

So on a high volume site you're definately better off to use pconnect as using connect would open multiple connetions and therefor hog system resources.

However, on a low volume site, or simply for explaination purposes (such as the forums) it doesn't make much difference either way.

Roger_Ramjet

The one advantage that persistent connections definately have is when you want to use a temp table. Temp tables are per connection and so a persistent connection will create a 'persistent' temp table, ie it will still be there when you come back.

drawmack

Originally posted by Roger Ramjet
The one advantage that persistent connections definately have is when you want to use a temp table. Temp tables are per connection and so a persistent connection will create a 'persistent' temp table, ie it will still be there when you come back.

You are technically correct however a look through the manual gave me this little nugget

An important summary. Persistent connections were designed to have one-to-one mapping to regular connections. That means that you should always be able to replace persistent connections with non-persistent connections, and it won't change the way your script behaves. It may (and probably will) change the efficiency of the script, but not its behavior! SIZE=1[/SIZE]

While persistent connections do not end with the script ends they may, and probably do, eventually end. When this happens the temp table goes away. So you should not count on this in your scripts.

bluesharp

Drawmack, Thanks for responding

However I am seeing the opposite as you have explained it. pconnect() will open connections and will not close them until the server decides, whereas connect() will close on explicit command or once the script completes. So you shouldn't have a lot of open, unused connections with connect().

Also, pconnect() will only reconnect you if there is an open link that matches your host,user,pw arguments. On a shared host, another site could leave many open connections but you cannot grab them because they do not match your host/user/pw criteria.

So I see the tradeoffs as follows:

pconnect() pros: Connection recycling

pconnect() cons: No control over managing connections
connect() pros: Connection management

connect() cons: Overhead of creating connections

So the question is, which con is a bigger issue?

Roger_Ramjet

See this http://www.mysql.com/news-and-events/newsletter/2002-11/a0000000086.html for a discussion of relative merits. As far as MySQL goes there are few advantages, especially if it closely linked to your web server on a fast network or on the same box. Persistent connections are only really usefull if you want to preserve a temp table from one query to another in quick succession. It just saves you having to create per user tables with unique names and delete them afterwards. DBMS config will determine how long unused connections are allowed to persist.

bluesharp

Thanks for the link, it certainly helped make my mind up on how I want to proceed (mysql_connect). Anyone who's on the fence about which connection process to use should read the article.

This is a great community! Thanks.