[RESOLVED] conflict claims: mysql_pconnect vs. mysql_connect, which one use more connections?

blackhorse

The hosing company I rent the server from told me that mysql_pconnect saves the connections, so I can use mysql_pconnect to solve the connections limits problem. I have found many posts in many forums said the msyql_pconnect use less connections.

But the mysql manual said TOTALLY IN THE OPPOSITE: "Warning
Using persistent connections can require a bit of tuning of your Apache and MySQL configurations to ensure that you do not exceed the number of connections allowed by MySQL. "

Due to the mysql_connect will auto close at the end of the script and when I go to another page, it will open a new connection, still use one connection. I don't see why msyql_pconnect saved the connections numbers.

1) So who is right? msyql_pconnect saves connections numbers or msyql_connect saves connections numbers? Could someone confirm it once for all?

I turned to believe that mysql manual is right.

But also on the msyql manual there was a comment
"Drowning under a sea of connections using mysql_connect (not pconnect) I found that php connections did NOT go away at the end of a page load. Only by setting the directive mysql.allow_persistent = Off made the connections go away. So unless I've missed something else or don't understand the meaning of 'script' the following note on this page is incorrect:

Note: The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().

Using PHP 4.4.4 & MySQL 4.0.26 "

2) Is he right? do I have to set up the mysql.allow_persistent = Off so mysql connections could be auto closed? otherwise, the mysql connections would not be auto closed as the manual claimed?

3) How can I check to see if a connection is auto closed or not at the end of the script? So I can test it mysql that who is right and who is wrong in the above claims?

With the opposit view points of different experts that either mysql_pconnect or mysql_connect uses less connections, I need big helps to clearfy this. Thanks!

blackhorse

From this article, http://www.php.net/manual/en/features.persistent-connections.php

"The second, and most popular, method is to run PHP as a module in a multiprocess web server, which currently only includes Apache. A multiprocess server typically has one process (the parent) which coordinates a set of processes (its children) who actually do the work of serving up web pages. When a request comes in from a client, it is handed off to one of the children that is not already serving another client. This means that when the same client makes a second request to the server, it may be served by a different child process than the first time. When opening a persistent connection, every following page requesting SQL services can reuse the same established connection to the SQL server. "

So persistent connections not reduce the connection numebrs at all, it is only for the efficiency. Meaning the child process will not have to connect to the server if a persistent connection is already open. Meaning, actually, in the contrary, it may used more connections (because persistent connections were not closed) in exchange for efficiency benefits.

Am I right here? So thinking using mysql_pconnect will solve the exceeding the mysql connection numbers problem is dead wrong?

shu

Chances are 99.99% of the time, persistence connection is bad for most users. Unless you have some very specific application and there exists a heavy over head for establish new db connection, I recommend just using the normal connect and spare yourself of the headaches.

Especially when the number of child process Apache is allowed to spawn is fairly small. When this happened, the Apache process expires quickly due to rapid incoming requests, and the database connection is kept open. It wouldn't take long for the number of database connection get max out, even if you have it set a high value.

MarkR

Basically, you won't want to use persistent connections, ever.

Unless, that is, you've considered it very carefully in your particular hosting environment, load-tested it, and are absolutely sure that it is a POSITIVELY BENEFICIAL thing to do.

Under no circumstances use persistent connections if you have more than one web app sharing a server.

Mark

Sxooter

I use pconnect (with pgsql, but it's the same issue) only on vary rare occasions.

If you use pconnect, you MUST have your apache server(s) set to create fewer children in total than you have your database set to handle.

I.e. I have my apache daemon set to create 50 children max, and my pgsql db set to handle 100 connections.

I use pconnect on this little widget program I wrote that provides airport pairs for a jmeter in a load testing setup. I get 30-50 ms response with plain connect, and 1-2 ms responses with pconnect.

But generally, if you don't have an explicit need for pconnect, avoid it. if you must use it, see the first part of my post again.

blackhorse

Thanks! I will just use mysql_connect for now just to avoid the troubles.