Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2001
    Location
    USA
    Posts
    830
    Rep Power
    18

    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.
  2. #2
  3. Advisor
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    Stockholm, Sweden
    Posts
    446
    Rep Power
    19
    you close a permanent mysql connection (mysql_pconnect())with
    mysql_close()
    - [ If I cant fix it, ask MacGyver... ] -
  4. #3
  5. phpkid ~~~~~~ :o)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Nov 2000
    Location
    NJ, USA
    Posts
    2,534
    Rep Power
    20
    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
    _____________________________
    d.k.jariwala (JD)
    ~ simple thought, simple act ~
    I blog @ http://jdk.phpkid.org
  6. #4
  7. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    30
    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
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Posts
    37
    Rep Power
    17
    $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...
  10. #6
  11. phpkid ~~~~~~ :o)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Nov 2000
    Location
    NJ, USA
    Posts
    2,534
    Rep Power
    20
    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
    _____________________________
    d.k.jariwala (JD)
    ~ simple thought, simple act ~
    I blog @ http://jdk.phpkid.org
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Posts
    37
    Rep Power
    17

    Question jdk


    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 ?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Posts
    37
    Rep Power
    17

    Question


    What would be the use to stay connected to mysql?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2001
    Location
    USA
    Posts
    830
    Rep Power
    18
    johan,

    No you don't.

    jdk,

    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.
    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

    Code:
    mysql_query("close");
    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.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    Tauranga, NZ
    Posts
    349
    Rep Power
    18
    http://www.php.net/manual/en/feature...onnections.php

    The first method is to use PHP as a CGI "wrapper". When run this way, an instance of the PHP interpreter is
    created and destroyed for every page request (for a PHP page) to your web server. Because it is destroyed after
    every request, any resources that it acquires (such as a link to an SQL database server) are closed when it is
    destroyed. In this case, you do not gain anything from trying to use persistent connections -- they simply don't
    persist.

    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 each 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 serviced by a different child process than the first
    time. What a persistent connection does for you in this case it make it so each child process only needs to connect
    to your SQL server the first time that it serves a page that makes us of such a connection. When another page then
    requires a connection to the SQL server, it can reuse the connection that child established earlier.
  20. #11
  21. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    121
    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.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2001
    Location
    USA
    Posts
    830
    Rep Power
    18
    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.
  24. #13
  25. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    121
    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!
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2001
    Location
    USA
    Posts
    830
    Rep Power
    18
    "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.
  28. #15
  29. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    121
    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?
    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 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?
    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.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo