Page 1 of 1

Good way to open and close connections

Posted: Thu Nov 02, 2006 3:59 pm
by amir
Hi,

We've currently had a problem with our VPS server. We used too many resources.
I guess that the connection we have to read/write our database is the reason of this problem.

This is how we work:

Code: Select all

//Connection to localhost:
@ $handledb1 = mysql_connect("localhost","user","password");
mysql_select_db("namedb1", $handledb1);

//Connection to database on other server:
@ $handledb2 = mysql_connect("IP","user","password");
mysql_select_db("namedb2", $handledb2);

$query1 = "SELECT * FROM table WHERE field = '$value'";
$sql1 = mysql_query($query1, $handledb1);

$query2 = "SELECT * FROM table WHERE field = '$value'";
$sql2 = mysql_query($query2, $handledb2);
sometimes up to 6-7 queries in one php-file

This all works great. But is it holding resources or not?
I was thinking that after the script is done, all connections will be closed automatically and the used resources are free again? Or am I thinking wrong. I'm not sure anymore


Is there another way to read the database? SQL is very time consuming on large database, even with a good index?


Thanks,

Posted: Thu Nov 02, 2006 4:06 pm
by Chris Corbyn
Assuming you aren't opening new connections for each individual query I doubt that's where resources are being used. What sort of things are you processing/computing?

Posted: Thu Nov 02, 2006 4:09 pm
by feyd
Each query generally be their own resources (if the query is unique among the other queries.) What is often suggested is to use mysql_free_result() immediately following the finished use of a query. Calling mysql_close() immediately after you're finished with the database can also help some. Looping a query can often be a bad code smell, so take great care in their usage.

Posted: Thu Nov 02, 2006 4:10 pm
by Chris Corbyn
You may also want to look at mysql_unbuffered_query() although that has some implications if you lose connectivity.

Posted: Thu Nov 02, 2006 4:15 pm
by RobertGonzalez
If you can (like feyd suggested) call mysql_free_result() as soon as you are done with the result set. mysql_close() is an automatic call at the end of the script execution as long as you are not using persistent connections to the db server (mysql_pconnect() instead of mysql_connect()).

Posted: Thu Nov 02, 2006 4:50 pm
by amir
Thanks a lot!