mysql_pconnect()

When using mysql_pconnect(), when and how is the connection terminated or destroyed, or whatever the correct term is? I know that it is not done by any explicit action on my part.

you close a permanent mysql connection (mysql_pconnect())with
mysql_close()

as you know, for a normal mysql connection, when your script execution finishes the connection would be closed automatically.
for persistent connection remains open eventhough your scrip finishes. now obviously you can close it using mysql_pclose i couldnt find
anything which says when this would be closed my mysql automatically. i mean if you open persistent connection and dont close it, how long it would remain open ?? i coudlnt find any info. though i am looking for it.


jd

Not sure if this is true for using mysql_pconnect(), but the manual states that mysql closes inactive connections after 8 hours by default. http://www.mysql.com/doc/G/o/Gone_away.html

/NoXcuz

$connection = mysql_connect("host", "$foo", "$bar") or die("couldnt connect");
$db = @mysql_select_db($db_name, $connection) or die("couldnt select database");
Put this in config.php or connection.php

You can always call it : required(connnection.php) in each mysql or sql statement.

Some host have a limit to how many connection are allowed. You can see your max connection in ini.php. Mine is 100 max!


HERE IS HOW I CODE AND CLOSE MYSQL, after each connection.

<?
//required connection
required("connection.php");

//insert into users table
$sql ="insert into users(name, email, regdate, username, passwd, user_ip, user_host, sess_id)VALUES('$name', '$email', '$regdate', '$username', passwd('$passwd'), '$user_ip', '$user_host', '$sess_id')";
$results = @mysql_query($sql, $connection)or die("mysql error");
mysql_query("close");

//insert into pages table
$sql ="insert into pages(tittle, website, keyword, description, cat_order) VALUES('$tittle', '$website', '$keyword', '$description', '$cat_order')";
$results = @mysql_query($sql, $connection)or die("mysql error");
mysql_query("close");

?>

@ is only because I used it when I connected to database...

micro,
what you are trying to say ?
he is not asking how you can connect to mysql , but he is asking about closing of persistent connection.
seems like you have worked for long hours and you better take rest.

jd

persistent connection,

If I didn't show in that statement ... his answer, then I am confused my self. This does show that 'persistent connection' is being closed.

After each connection to mysql> mysql_query("close");

Disagree ?

What would be the use to stay connected to mysql?

johan,

No you don't.

jdk,



Really? It wasn't obvious to me. Can you provide me with a copy of this magical function, mysql_pclose(), which I take is of your invention?? That's what I want to know, how long it will remain open. But what I really want to know is what causes it to close, and when, as it is obviously not open for eternity.

NoXcuz,

Thanks for the link. Is the connection that this page refers to the same as the MySQL connection in the PHP context? Sorry for my ignorance, but I don't know much about what goes on behind the scenes between PHP and MySQL.

micro,

I've seen your genius at work in previous posts, but I think this may be your finest work yet. Ok, we're having just a couple major problems here. a) I asked about a persistent connection and you're showing me stuff about a regular connection. b) what exactly do you think



does?

"then I am confused my self"
You're absolutely right.

"This does show that 'persistent connection' is being closed."
You are niether opening a persistent connection, nor closing it, nor closing the regular connection that you have opened.

http://www.php.net/manual/en/featur...connections.php

JMM, I believe persistant connections won't die until they time out, most likely mysql decides when that is. AFAIK, a connection is a connection to mysql, so ANY connection, be it from apache(php) or an individual would time out after the alloted time value with no transactions taking place. AFAIK, this is standard for many applications that allow socket connections to keep an errant user from using up all resources.

bumperbox,

Thank you for the information. I wasn't really completely aware of that situation. I should have specified in my original post that I am asking in terms of the module version. That information seems to be saying that the persistent connection will only be used during succesive requests from the same client serviced by the same child process, but it says that the same client may be serviced by different child processes on successive requests. Am I reading that right? If so, then how often is the persistent connection actually used?

Nemi,

Thanks. So what that boils down to is, I just don't have to worry about it, right? MySQL will just close the connection on it's own terms.

The way I read that, apache makes 'children' (threads) for each request it gets for pages. When you make another request, you may get serviced by the same thread, or you may get serviced by a different thread.

When a thread you are using accesses mysql with pconnect, it does not close that connection when the script terminates like it normally would, it keeps it open. If another request is given to that thread, and it needs to access mysql again, it already has a connection open, thus making accesses faster than normal.

The downside is that you can potentially have many open connections to mysql that are not being used. You have to decide which way is best for your site. If you have a site that gets many hits, but typically the user doesn't move around alot and there are few accesses to mysql , regular connect would probably suffice. If you have a site with a lower hit count but users tend to access many pages that all have many accesses to mysql , then pconnect would probably be better.

Lastly, what one can infer from that description is that apache does not terminate it's 'children' immediately after a request has taken place. It keeps them around for an unspecified amount of time, probably for the very same reason you would use pconnect - there is overhead invloved with these transactions, so if you think you will be doing the same thing many times, why not reuse resources?

What is not explicitly said is whether the pconnection is terminated when apache finally axes its child (kills the thread), or if mysql has responsibility for that. My guess is that the connection terminates once either apache kills the owning thread or mysql closes the connection after it has been open for a given amount of time without any transactions.

Either way, it is probably safe to assume that someone is cleaning up the connection at some point. You just need to decide if that point is best for your sites dynamics.

Hope that helps!

"Hope that helps!"

Well... yes and no. So far, everything I have heard about persistent connections is too abstract to grant me understanding of its operation. Here are the problems I am having:

Being on a shared server as I am, that is, a virtually hosted website, what happens when I open a persistent connection, that opens on a given thread, and another user of the hosting service accesses MySQL and gets that thread?

I know little about Apache and *nix; how often will a given user agent be serviced by the same thread in a sequence of requests that constitutes their visit to my site?

"The downside is that you can potentially have many open connections to mysql that are not being used"

What makes that a downside? What is the affect or consequence of having multiple open connections? As a web developer programming a site that has pages that access a MySQL database, what does that mean to me in practical terms?


I realize you may not be able to answer these questions, but those are my questions. I'm surprised that the operation of persistent connections is not understood by the PHP community by now.

Thanks Nemi.

From how I understand it, even if the thread that has your open connection is used to access someone elses site, it will only reuse the connection to mysql if it is the exact same connection (same database, same user, same password). In other words, if it gets a request to open a connection to mysql, it checks to see if an open connection exists to the exact DB, if it does, it uses it. If it doesn't, it opens a new one. It should not be a security risk.

The downside is that there generally can only be so many open connections open to mysql (I heard the figure of 100 once) at one time. If they are all open on threads that no one is using, you will get an access error. That is technically known as a 'Bad Thing'.

So like I said, if you get many hits to your site, but users tend to hit one page and then leave, or there is little DB access anyway, it is probably not good to use pconnections as they will likely just go until they time out, creating alot of wasted resources.

If you have a site where there are lots of accesses to mysql on many pages and/or users tend to go through many pages that all try and access mysql, then it can be benificial because they won't have to open a new connection to the DB every page.

It is just a cost vs benefits thing. Let me make an analogy.

Say you have a small business. You work on widgets. Your shop is down in the basement. You get called upstairs when someone brings in a widget for repair. When that happens, lets say you must stop what you are doing, remove a protective coverall, go upstairs and help the customer. After the customer leaves, you have to walk back downstairs, put your coverall back on and pick up where you left off.

The time it takes to remove/put on your coverall and traverse the stairs is overhead. You don't want to be doing that if a customer is coming in every 5 minutes. You could work on widgets upstairs, but it is not as efficient, lets say. If customers are few and far between, you make the most money staying downstairs where you can get lots of work done, even though you have to come up occasionally. If customers are bringing in new widgets over their lunch hour a lot, it might be a good idea to work on one upstairs, even though it is less efficient, because you are benefiting from not having to lose that time to overhead.

Now, every aspect of this analogy does not directly correlate to the present discussion 100% accurately, so I dont wish to argue the details. However, hopefully it was close enough that you can get a better idea of overall situation.