Re: [PHP] MySQL close connection, what's the purpose?
tedd wrote: chris said: Just out of interest, could you re-run the test using persistent connections? change mysql_connect to mysql_pconnect.. [snip] Thanks -- does the "persistent connection" thing hold the server up until released? How does that work? MySQL is threaded so will not be "held up" by any connection. Obviously the more connections the more of certain resources it will be using, but it doesn't block for every connection. The exception, of course, being if you have something going on at the MySQL protocol level such as LOCK TABLES or a transaction. That will hold up certain other queries. But that's at a whole other layer. -- Jasper Bryant-Greene General Manager Album Limited http://www.album.co.nz/ 0800 4 ALBUM [EMAIL PROTECTED] 021 708 334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On Mon, April 3, 2006 1:33 pm, tedd wrote: >>Just out of interest, could you re-run the test using persistent >> connections? >> >>change mysql_connect to mysql_pconnect.. > > In doing so, the overall results dropped from a tenth of a second > difference between both methods to three-one-hundredths of a second > difference. In other words, using mysql_pconnect is three times > faster for multiple open/closes. > > However, this only improved the connect/get-data/close sequence. The > open once and keep open showed no improvement. > > Thanks -- does the "persistent connection" thing hold the server up > until released? How does that work? What "persistent connection" REALLY means is: Ask MySQL to keep the connection laying around handy for re-use. So it's not really "persistent" from PHP's point-of-view -- It's more like "re-usable" from PHP's point-of-view. The actual persistence happens on the MySQL side of things, mostly. Note, however, that for security reasons, the re-use can only be done if the SAME MySQL user is asking for a connection. You wouldn't want to risk the data left over from a 'power' user to leak out to a 'normal' user, if you have multiple MySQL users with multiple levels of access, and the connection to be re-used was previously a 'power' user one, but now a 'normal' user is asking to use it. There are more constraints on this re-use which I forget... What they boil down to, though, is that EACH Apache child process will end up having a connection allocated to itself, for each username that you use in your application. In /etc/httpd.conf (or wherever your httpd.conf file is) you have something like MaxServers to define the maximum number of Apache children that can exist. So you need to edit /etc/my.cnf (or wherever yours is installed, or your MySQL expects it to be installed, based on compile-time settings) and make sure you have that number of connections PLUS A FEW EXTRA ONES. The few extra ones are needed in case of a server melt-down, so that even if every Apache child is running, sucking down a MySQL connection, you can STILL connect from the shell as 'root' and do something useful, like shutdown MySQL safely. If your MySQL # of connections are ALL taken up by Apache children thrashing away, then you CANNOT connect using mysql_admin to shut down MySQL and you're in big trouble. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
chris said: Just out of interest, could you re-run the test using persistent connections? change mysql_connect to mysql_pconnect.. In doing so, the overall results dropped from a tenth of a second difference between both methods to three-one-hundredths of a second difference. In other words, using mysql_pconnect is three times faster for multiple open/closes. However, this only improved the connect/get-data/close sequence. The open once and keep open showed no improvement. Thanks -- does the "persistent connection" thing hold the server up until released? How does that work? tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On 4/3/06, tedd <[EMAIL PROTECTED]> wrote: > chris said: > > >Time. Opening a db connection is time consuming. There are many levels > >involved (making the connection, authentication, etc).. Even worse if > >the connection is over tcp/ip because that overhead comes in on top as > >well. > > I replied: > > >Have you timed it? > > > >Maybe I'll do that tomorrow. > > Okay, I performed a test that made 100 queries, and found that it > took about four times as long to open a dB connection each time and > get the data as it did to open it once. In other words, if your > script pulls data from a dB 100 times, then making a connection to > the dB once, pulling the data, and then closing the dB is four times > as fast as opening the dB, pulling the data, and closing the dB in > sequence one hundred times. > > Now, that sounds like you should open your dB at the start of your > script and just leave it open until the your script quits (as the > original poster suggested), but there's a few thing to consider here. > > 1. The total time difference shown in the above test is a little over > a tenth of a second. > > 2. By keeping the dB open, you're actually tying up the resources of > the server more than necessary. After all, your script will always > run longer than the query, right? > > 3. Most scripts (I'm guess here) don't access the dB 100 times or > more -- so doing is less, means less of a difference. > > 4. And lastly, by closing the dB in your script when you're done with > it, allows the server to use it's resources to "clean-up" quicker and > have more time to be responsive to other queries. > > So, not that you implied otherwise, I still don't see any convincing > argument as to why one should open a dB at the start of a script and > not close it as the original poster asked. Furthermore, not that I'm > anyone special, but if I was overlooking someone's code who did that, > I wouldn't think favorably of the technique -- it would appear as > sloppy programming to me. Just out of interest, could you re-run the test using persistent connections? change mysql_connect to mysql_pconnect.. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
chris said: Time. Opening a db connection is time consuming. There are many levels involved (making the connection, authentication, etc).. Even worse if the connection is over tcp/ip because that overhead comes in on top as well. I replied: Have you timed it? Maybe I'll do that tomorrow. Okay, I performed a test that made 100 queries, and found that it took about four times as long to open a dB connection each time and get the data as it did to open it once. In other words, if your script pulls data from a dB 100 times, then making a connection to the dB once, pulling the data, and then closing the dB is four times as fast as opening the dB, pulling the data, and closing the dB in sequence one hundred times. Now, that sounds like you should open your dB at the start of your script and just leave it open until the your script quits (as the original poster suggested), but there's a few thing to consider here. 1. The total time difference shown in the above test is a little over a tenth of a second. 2. By keeping the dB open, you're actually tying up the resources of the server more than necessary. After all, your script will always run longer than the query, right? 3. Most scripts (I'm guess here) don't access the dB 100 times or more -- so doing is less, means less of a difference. 4. And lastly, by closing the dB in your script when you're done with it, allows the server to use it's resources to "clean-up" quicker and have more time to be responsive to other queries. So, not that you implied otherwise, I still don't see any convincing argument as to why one should open a dB at the start of a script and not close it as the original poster asked. Furthermore, not that I'm anyone special, but if I was overlooking someone's code who did that, I wouldn't think favorably of the technique -- it would appear as sloppy programming to me. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
John Nichel wrote: Jasper Bryant-Greene wrote: I never close connections; PHP does that for me and has never caused any problems doing that. I don't see it as sloppy programming, it is a documented feature that PHP closes resources such as database connections at the end of the script. It's extremely sloppy programming. You're assuming that a) PHP will continue to be a forgiving language when it comes to items like this and b) your script is going to exit normally. The reason this is a 'documented feature' is because PHP is trying to make up for sloppy programming. You shouldn't rely on the language to clean up your toys for you. If the script exits abnormally the connection is still closed. Test it. I'm happy to gamble on a) as because I have said in earlier posts I am very confident this behaviour will not change in the forseeable future. -- Jasper Bryant-Greene General Manager Album Limited http://www.album.co.nz/ 0800 4 ALBUM [EMAIL PROTECTED] 021 708 334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
Jasper Bryant-Greene wrote: I never close connections; PHP does that for me and has never caused any problems doing that. I don't see it as sloppy programming, it is a documented feature that PHP closes resources such as database connections at the end of the script. It's extremely sloppy programming. You're assuming that a) PHP will continue to be a forgiving language when it comes to items like this and b) your script is going to exit normally. The reason this is a 'documented feature' is because PHP is trying to make up for sloppy programming. You shouldn't rely on the language to clean up your toys for you. -- By-Tor.com ...it's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On Sat, 2006-04-01 at 21:57, Jasper Bryant-Greene wrote: > Robert Cummings wrote: > > >> Of course, it wouldn't exactly be a rewrite to make it close the > >> connection at the end of every script before PHP did, if I'm proven > >> wrong and it one day is necessary. I'd only need to change the database > >> objects layer. > > > > Wrong, you would just be doing the same thing PHP does... closing the > > connection at the end of the script. What happens if you need to open 20 > > connections to 20 different databases... are you going to keep them all > > open? I guess you would since it sounds like you don't have a facility > > to close them. I don't think what you're doing is incredibly obscene, I > > mean 90% of PHP developers are doing the same thing. 90% of the coding > > population can't be wrong... but one that same line of thought... when > > you open an image file or text file for reading or writing... do you > > close it? Or just leave it open for PHP to close at the end? I mean PHP > > will magically close all resources for you, there's obviously no need to > > close it... or maybe there are valid times when you need to close a > > resource yourself, I dunno, I feel like I'm out on a limb here ;) > > Yeah, I can see your point. Simple answer though: my framework isn't > designed for connecting to 20 different databases :) It's designed for > normal database-driven websites -- where there usually a maximum of two > connections (master and slave), and often only one connection, open. Now getting away from you particular framework and back to the original question: "MySQL close connection, what's the purpose?", we now have an answer :) > I guess I'm just gambling the time-saving benefits of not having to call > $db->close() or whatever all the time, against the slim possibility that > I might one day have to write a new framework to deal with apps that do > 20+ DB connections at once. The framework is fairly light anyway as it's > built on top of PDO, so a rewrite is not a huge deal. I agree with you in general that the likelihood of PHP stopping it's support for auto closing of connections is highly unlikely, but this question wasn't about your framework, but rather about automatic closing of MySQL connections in general... and so, somebody out there probably does have an environment where closing the connections makes a LOT of sense. BTW, who used register globals and magic quotes in the past and relied on the argument that PHP would never get rid of them due to the number of scripts that would break -- show of hands please! *grin* Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
Robert Cummings wrote: On Sat, 2006-04-01 at 21:39, Jasper Bryant-Greene wrote: Robert Cummings wrote: There's smart lazy programming, and sloppy lazy programming. I don't trust anything magical in PHP. Most of us are familiar with the magic quotes and global vars fiascos *LOL*. But hey, if you can squeeze a rewrite of an application out of a client for relying on dirty techniques, who am I to critique your forward thinking manipulative methods -- not to say that's your intent -- but I'd sure question your motives and judgement if it comes around ;) I very much doubt PHP will ever enforce the closing of resources such as database connections at the end of every script. That would be a needless BC break. I'm sure that was the thought on magic quotes and register globals also. If PHP didn't close connections at the end of scripts we'd either have just about every script in the world throwing errors when they finished, or lots of memory leaks. Neither is particularly favourable, so I don't think it will happen any time soon... Also, I do it this way because some projects that use my framework want persistent connections. If my framework closed connections automatically then that wouldn't be possible. Your database layer should handle whether a connection is really freed. Just because the developer calls the close() method on your DB object, doesn't mean you need to close the connection. But if they don't call a close() method, then in the future if you do need that functionality... it's not there. Of course, it wouldn't exactly be a rewrite to make it close the connection at the end of every script before PHP did, if I'm proven wrong and it one day is necessary. I'd only need to change the database objects layer. Wrong, you would just be doing the same thing PHP does... closing the connection at the end of the script. What happens if you need to open 20 connections to 20 different databases... are you going to keep them all open? I guess you would since it sounds like you don't have a facility to close them. I don't think what you're doing is incredibly obscene, I mean 90% of PHP developers are doing the same thing. 90% of the coding population can't be wrong... but one that same line of thought... when you open an image file or text file for reading or writing... do you close it? Or just leave it open for PHP to close at the end? I mean PHP will magically close all resources for you, there's obviously no need to close it... or maybe there are valid times when you need to close a resource yourself, I dunno, I feel like I'm out on a limb here ;) Yeah, I can see your point. Simple answer though: my framework isn't designed for connecting to 20 different databases :) It's designed for normal database-driven websites -- where there usually a maximum of two connections (master and slave), and often only one connection, open. I guess I'm just gambling the time-saving benefits of not having to call $db->close() or whatever all the time, against the slim possibility that I might one day have to write a new framework to deal with apps that do 20+ DB connections at once. The framework is fairly light anyway as it's built on top of PDO, so a rewrite is not a huge deal. -- Jasper Bryant-Greene General Manager Album Limited http://www.album.co.nz/ 0800 4 ALBUM [EMAIL PROTECTED] 021 708 334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On Sat, 2006-04-01 at 21:39, Jasper Bryant-Greene wrote: > Robert Cummings wrote: > > > There's smart lazy programming, and sloppy lazy programming. I don't > > trust anything magical in PHP. Most of us are familiar with the magic > > quotes and global vars fiascos *LOL*. But hey, if you can squeeze a > > rewrite of an application out of a client for relying on dirty > > techniques, who am I to critique your forward thinking manipulative > > methods -- not to say that's your intent -- but I'd sure question your > > motives and judgement if it comes around ;) > > I very much doubt PHP will ever enforce the closing of resources such as > database connections at the end of every script. That would be a > needless BC break. I'm sure that was the thought on magic quotes and register globals also. > Also, I do it this way because some projects that use my framework want > persistent connections. If my framework closed connections automatically > then that wouldn't be possible. Your database layer should handle whether a connection is really freed. Just because the developer calls the close() method on your DB object, doesn't mean you need to close the connection. But if they don't call a close() method, then in the future if you do need that functionality... it's not there. > Of course, it wouldn't exactly be a rewrite to make it close the > connection at the end of every script before PHP did, if I'm proven > wrong and it one day is necessary. I'd only need to change the database > objects layer. Wrong, you would just be doing the same thing PHP does... closing the connection at the end of the script. What happens if you need to open 20 connections to 20 different databases... are you going to keep them all open? I guess you would since it sounds like you don't have a facility to close them. I don't think what you're doing is incredibly obscene, I mean 90% of PHP developers are doing the same thing. 90% of the coding population can't be wrong... but one that same line of thought... when you open an image file or text file for reading or writing... do you close it? Or just leave it open for PHP to close at the end? I mean PHP will magically close all resources for you, there's obviously no need to close it... or maybe there are valid times when you need to close a resource yourself, I dunno, I feel like I'm out on a limb here ;) Cheers, Rob -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
Robert Cummings wrote: On Sat, 2006-04-01 at 20:48, Jasper Bryant-Greene wrote: Yeah, e.g. I have a database objects layer that means I only write SQL in classes, everything else is just calling object methods. I create the database object at the start of every script but that doesn't necessarily open the database connection. The database connection is opened when I make my first query. That way if a page does no queries (I use APC caching so it is fairly common for a page to do no queries) then no database connection is opened. I never close connections; PHP does that for me and has never caused any problems doing that. I don't see it as sloppy programming, it is a documented feature that PHP closes resources such as database connections at the end of the script. But, as has been said, each to their own. There's smart lazy programming, and sloppy lazy programming. I don't trust anything magical in PHP. Most of us are familiar with the magic quotes and global vars fiascos *LOL*. But hey, if you can squeeze a rewrite of an application out of a client for relying on dirty techniques, who am I to critique your forward thinking manipulative methods -- not to say that's your intent -- but I'd sure question your motives and judgement if it comes around ;) I very much doubt PHP will ever enforce the closing of resources such as database connections at the end of every script. That would be a needless BC break. Also, I do it this way because some projects that use my framework want persistent connections. If my framework closed connections automatically then that wouldn't be possible. Of course, it wouldn't exactly be a rewrite to make it close the connection at the end of every script before PHP did, if I'm proven wrong and it one day is necessary. I'd only need to change the database objects layer. -- Jasper Bryant-Greene General Manager Album Limited http://www.album.co.nz/ 0800 4 ALBUM [EMAIL PROTECTED] 021 708 334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On Sat, 2006-04-01 at 20:48, Jasper Bryant-Greene wrote: > > Yeah, e.g. I have a database objects layer that means I only write SQL > in classes, everything else is just calling object methods. I create the > database object at the start of every script but that doesn't > necessarily open the database connection. The database connection is > opened when I make my first query. > > That way if a page does no queries (I use APC caching so it is fairly > common for a page to do no queries) then no database connection is opened. > > I never close connections; PHP does that for me and has never caused any > problems doing that. I don't see it as sloppy programming, it is a > documented feature that PHP closes resources such as database > connections at the end of the script. > > But, as has been said, each to their own. There's smart lazy programming, and sloppy lazy programming. I don't trust anything magical in PHP. Most of us are familiar with the magic quotes and global vars fiascos *LOL*. But hey, if you can squeeze a rewrite of an application out of a client for relying on dirty techniques, who am I to critique your forward thinking manipulative methods -- not to say that's your intent -- but I'd sure question your motives and judgement if it comes around ;) Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
Robert Cummings wrote: On Sat, 2006-04-01 at 20:15, tedd wrote: It would be interesting to actually run a script that opens, retrieves, and inserts data -- let's say 50k times. What's the time difference between one open, 50k retrieves/inserts, and one close-- as compared 50k opens retrieve/insert closes? [snip] Everyone has their own way. I'm not going to advocate either style since both have their merits depending on where and what you are doing. My input is to advocate a database wrapper layer such that the database connection semantics are remove from general development. In this way you might have the following: [snip] Yeah, e.g. I have a database objects layer that means I only write SQL in classes, everything else is just calling object methods. I create the database object at the start of every script but that doesn't necessarily open the database connection. The database connection is opened when I make my first query. That way if a page does no queries (I use APC caching so it is fairly common for a page to do no queries) then no database connection is opened. I never close connections; PHP does that for me and has never caused any problems doing that. I don't see it as sloppy programming, it is a documented feature that PHP closes resources such as database connections at the end of the script. But, as has been said, each to their own. -- Jasper Bryant-Greene General Manager Album Limited http://www.album.co.nz/ 0800 4 ALBUM [EMAIL PROTECTED] 021 708 334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On Sat, 2006-04-01 at 20:15, tedd wrote: > >Time. Opening a db connection is time consuming. There are many levels > >involved (making the connection, authentication, etc).. Even worse if > >the connection is over tcp/ip because that overhead comes in on top as > >well. > > Have you timed it? > > It would be interesting to actually run a script that opens, > retrieves, and inserts data -- let's say 50k times. What's the time > difference between one open, 50k retrieves/inserts, and one close-- > as compared 50k opens retrieve/insert closes? > > Maybe I'll do that tomorrow. > > > > You see, my habit stems from doing a lot of communication programming > >> -- it was my experience that you open a communication port > >> (file/channel/port/whatever); establish a link; exchange data; and > >> close it. There's no need to leave it open. > > > >Just because you can close it doesn't mean you have to. > > Everyone has their own way. I'm not going to advocate either style since both have their merits depending on where and what you are doing. My input is to advocate a database wrapper layer such that the database connection semantics are remove from general development. In this way you might have the following: query( 'INSERT INTO blah blah blah' ); $conn->free(); // this may or may not close the connection. ?> This way, if your mileage varies with either technique you can modify the connection layer to get another semantic which better suits your application/environment. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
Time. Opening a db connection is time consuming. There are many levels involved (making the connection, authentication, etc).. Even worse if the connection is over tcp/ip because that overhead comes in on top as well. Have you timed it? It would be interesting to actually run a script that opens, retrieves, and inserts data -- let's say 50k times. What's the time difference between one open, 50k retrieves/inserts, and one close-- as compared 50k opens retrieve/insert closes? Maybe I'll do that tomorrow. > You see, my habit stems from doing a lot of communication programming -- it was my experience that you open a communication port (file/channel/port/whatever); establish a link; exchange data; and close it. There's no need to leave it open. Just because you can close it doesn't mean you have to. Everyone has their own way. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
At 11:07 AM +1200 4/2/06, Jasper Bryant-Greene wrote: tedd wrote: > > > I always close the connection right after my > > query -- force of habit. It's like leaving the > > toilet seat up, it's only going to get you into > > trouble. > > So you close it after every query and then re-open it later for the > next query? I don't see that as a good idea. > > > No, you leave it open until you're done with the database. Reading Ted's post didn't give this impression. I wanted to make sure he wasn't doing it that way. Chris et al: Actually I am. When I need something from the dB, I open it, get the information and close it. It's like opening a drawer, getting what you need, and then closing the drawer. Where's the problem? Uh, what if you want to do more than one query in a single request? You aren't seriously suggesting you would connect and disconnect from the same database multiple times within the same request? No, I'm not. What I am suggesting is that if you are done with your query, and are not immediately asking another, then hang up. If another segment of your code wants to access the dB again, then open it as you want. The practice makes for more modular code and keeps the right connections active when they are needed. In my experience, connecting to the database takes up more than half of the execution time of the average database-driven PHP script (I said *average*, there are exceptions). You don't want to be doing it multiple times if you don't have to. In my experience, it's better, in many ways, to put things away when you're done using them. This tread started out with "What's the purpose of closing a connection? After all, when the script finishes, doesn't the dB automatically close?" While that's true, I personally think it's a bad habit that could lead to problems. Your mileage may vary. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On 4/2/06, tedd <[EMAIL PROTECTED]> wrote: > > > > > I always close the connection right after my > >> > > query -- force of habit. It's like leaving the > >> > > toilet seat up, it's only going to get you into > >> > > trouble. > >> > > >> > So you close it after every query and then re-open it later for the > >> > next query? I don't see that as a good idea. > > > > > > > No, you leave it open until you're done with the database. > > > >Reading Ted's post didn't give this impression. I wanted to make sure > >he wasn't doing it that way. Firstly sorry for calling you Ted, not Tedd :) > Actually I am. When I need something from the dB, I open it, get the > information and close it. It's like opening a drawer, getting what > you need, and then closing the drawer. Where's the problem? > > I don't see any reason whatsoever for me to open the dB at the start > of my session and then close it at the end. Is there some > overwhelming reason for anyone to do so? Time. Opening a db connection is time consuming. There are many levels involved (making the connection, authentication, etc).. Even worse if the connection is over tcp/ip because that overhead comes in on top as well. > You see, my habit stems from doing a lot of communication programming > -- it was my experience that you open a communication port > (file/channel/port/whatever); establish a link; exchange data; and > close it. There's no need to leave it open. Just because you can close it doesn't mean you have to. You can send 10 (or 100) messages through an smtp server before closing it... It depends on what you are doing. I'm sure there are tons of cases where you should open/action/close. Db isn't one of those. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
tedd wrote: > > > I always close the connection right after my > > query -- force of habit. It's like leaving the > > toilet seat up, it's only going to get you into > > trouble. > > So you close it after every query and then re-open it later for the > next query? I don't see that as a good idea. > > > No, you leave it open until you're done with the database. Reading Ted's post didn't give this impression. I wanted to make sure he wasn't doing it that way. Chris et al: Actually I am. When I need something from the dB, I open it, get the information and close it. It's like opening a drawer, getting what you need, and then closing the drawer. Where's the problem? Uh, what if you want to do more than one query in a single request? You aren't seriously suggesting you would connect and disconnect from the same database multiple times within the same request? In my experience, connecting to the database takes up more than half of the execution time of the average database-driven PHP script (I said *average*, there are exceptions). You don't want to be doing it multiple times if you don't have to. -- Jasper Bryant-Greene General Manager Album Limited http://www.album.co.nz/ 0800 4 ALBUM [EMAIL PROTECTED] 021 708 334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
> > > I always close the connection right after my > > query -- force of habit. It's like leaving the > > toilet seat up, it's only going to get you into > > trouble. > > So you close it after every query and then re-open it later for the > next query? I don't see that as a good idea. > > > No, you leave it open until you're done with the database. Reading Ted's post didn't give this impression. I wanted to make sure he wasn't doing it that way. Chris et al: Actually I am. When I need something from the dB, I open it, get the information and close it. It's like opening a drawer, getting what you need, and then closing the drawer. Where's the problem? I don't see any reason whatsoever for me to open the dB at the start of my session and then close it at the end. Is there some overwhelming reason for anyone to do so? You see, my habit stems from doing a lot of communication programming -- it was my experience that you open a communication port (file/channel/port/whatever); establish a link; exchange data; and close it. There's no need to leave it open. Additionally, isn't that they way the net is set up? When your browser accesses a web site, the browser sends a request for information, the web site responds, and then both of you attend to your own business. There is no maintaining an open communication link, other than a session or cookie. Plus, if you are talking to several devices (including dB's) at once, then how do you know for sure which device you're talking to IF you leave all communication ports open? Furthermore, from a code perspective, I always comment my open and close dB-includes as a pair -- like braces in a function or in an "if statement". That way, I always know where in my code are the dB segments. Additionally, I also number the error statements in the die portion -- so if something goes wrong, I know exactly where it went wrong. This does not mean that I place an open/extract-insert/close dB within a loop. If I need to get something that way, then I open the dB before the loop and close the dB after the loop. However, I much prefer having MySQL do the looping for me. It's my understanding that opening a dB isn't problematic and isn't something that I should worry about, right? It's also my understanding that opening a dB is very fast, and it can manage a large number of commands, and different connections very quickly, right? Then what's the problem with opening it several times during a session instead just once? If someone cares to enlighten me with a convincing argument to do otherwise, I'll certainly consider and adapt. But my guess is, that opening a dB the way I do it presents no significant downside. I've never had a problem with showing my ignorance before, so prove me wrong if you can. I can learn. Thanks. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On 4/1/06, Anthony Ettinger <[EMAIL PROTECTED]> wrote: > On 3/31/06, chris smith <[EMAIL PROTECTED]> wrote: > > On 4/1/06, tedd <[EMAIL PROTECTED]> wrote: > > > At 10:30 PM +0200 3/31/06, Martin Zvarík wrote: > > > >Hi, > > > >I was wondering why is it necessary to use > > > >mysql_close() at the end of your script. > > > >If you don't do it, it works anyways, doesn't it? > > > > > > > >MZ > > > > > > MZ: > > > > > > I always close the connection right after my > > > query -- force of habit. It's like leaving the > > > toilet seat up, it's only going to get you into > > > trouble. > > > > So you close it after every query and then re-open it later for the > > next query? I don't see that as a good idea. > > > > No, you leave it open until you're done with the database. Reading Ted's post didn't give this impression. I wanted to make sure he wasn't doing it that way. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On 3/31/06, chris smith <[EMAIL PROTECTED]> wrote: > On 4/1/06, tedd <[EMAIL PROTECTED]> wrote: > > At 10:30 PM +0200 3/31/06, Martin Zvarík wrote: > > >Hi, > > >I was wondering why is it necessary to use > > >mysql_close() at the end of your script. > > >If you don't do it, it works anyways, doesn't it? > > > > > >MZ > > > > MZ: > > > > I always close the connection right after my > > query -- force of habit. It's like leaving the > > toilet seat up, it's only going to get you into > > trouble. > > So you close it after every query and then re-open it later for the > next query? I don't see that as a good idea. > No, you leave it open until you're done with the database. If you pee and poo in one sitting, you don't get up and flush between occurrences. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On 4/1/06, tedd <[EMAIL PROTECTED]> wrote: > At 10:30 PM +0200 3/31/06, Martin Zvarík wrote: > >Hi, > >I was wondering why is it necessary to use > >mysql_close() at the end of your script. > >If you don't do it, it works anyways, doesn't it? > > > >MZ > > MZ: > > I always close the connection right after my > query -- force of habit. It's like leaving the > toilet seat up, it's only going to get you into > trouble. So you close it after every query and then re-open it later for the next query? I don't see that as a good idea. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
At 10:30 PM +0200 3/31/06, Martin Zvarík wrote: Hi, I was wondering why is it necessary to use mysql_close() at the end of your script. If you don't do it, it works anyways, doesn't it? MZ MZ: I always close the connection right after my query -- force of habit. It's like leaving the toilet seat up, it's only going to get you into trouble. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On 3/31/06, Martin Zvarík <[EMAIL PROTECTED]> wrote: > Richard Lynch wrote: > > >On Fri, March 31, 2006 2:30 pm, Martin Zvarík wrote: > > > > > >>I was wondering why is it necessary to use mysql_close() at the > >>end > >>of your script. > >>If you don't do it, it works anyways, doesn't it? > >> > >> > > > >Yes, but... > > > >Suppose you write a script to read data from one MySQL server, and > >then insert it into 200 other MySQL servers, as a sort of home-brew > >replication (which would be really dumb to do, mind you)... > > > >In that case, you REALLY don't want the overhead of all 200 > >connections open, so after you finish each one, you would close it. > > > >There are also cases where you finish your MySQL work, but have a TON > >of other stuff to do in the script, which will not require MySQL. > > > >Close the connection to free up the resource, like a good little boy. :-) > > > >There also COULD be cases where your PHP script is not ending > >properly, and you'd be better off to mysql_close() yourself. > > > > > > > So, does the connection close automatically at the end of the script ? > > My situation is following: > I have a e-shop with a ridiculously small amount of max approved > connections, so it gives an error to about 10% of my visitors a day, > that the mysql connections were exceeded. > > Now, if I will delete the "mysql_close()" line, will that help me or > not? My webhosting does not allow perminent connections either. > > Thanks, > MZ deleting the mysql_close() line would keep the connection open until the script ends. by closing it earlier when you're done with the database for the "event", your script continues on, ie - parsing/displaying of db query results, template rendering, etc. yet the connection was closed earlier so other processes can use mysql (assuming your hitting your limit this way with too many simultaneous connections). -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
Richard Lynch wrote: On Fri, March 31, 2006 2:30 pm, Martin Zvarík wrote: I was wondering why is it necessary to use mysql_close() at the end of your script. If you don't do it, it works anyways, doesn't it? Yes, but... Suppose you write a script to read data from one MySQL server, and then insert it into 200 other MySQL servers, as a sort of home-brew replication (which would be really dumb to do, mind you)... In that case, you REALLY don't want the overhead of all 200 connections open, so after you finish each one, you would close it. There are also cases where you finish your MySQL work, but have a TON of other stuff to do in the script, which will not require MySQL. Close the connection to free up the resource, like a good little boy. :-) There also COULD be cases where your PHP script is not ending properly, and you'd be better off to mysql_close() yourself. So, does the connection close automatically at the end of the script ? My situation is following: I have a e-shop with a ridiculously small amount of max approved connections, so it gives an error to about 10% of my visitors a day, that the mysql connections were exceeded. Now, if I will delete the "mysql_close()" line, will that help me or not? My webhosting does not allow perminent connections either. Thanks, MZ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL close connection, what's the purpose?
On Fri, March 31, 2006 2:30 pm, Martin Zvarík wrote: > I was wondering why is it necessary to use mysql_close() at the > end > of your script. > If you don't do it, it works anyways, doesn't it? Yes, but... Suppose you write a script to read data from one MySQL server, and then insert it into 200 other MySQL servers, as a sort of home-brew replication (which would be really dumb to do, mind you)... In that case, you REALLY don't want the overhead of all 200 connections open, so after you finish each one, you would close it. There are also cases where you finish your MySQL work, but have a TON of other stuff to do in the script, which will not require MySQL. Close the connection to free up the resource, like a good little boy. :-) There also COULD be cases where your PHP script is not ending properly, and you'd be better off to mysql_close() yourself. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php