...

View Full Version : reducing sql server load



arne2
08-26-2006, 10:28 AM
Hi,
Sometimes my website just stops running and only displays errors (see below).
Is there a way i can fix the problems?
Should it help if i use mysql_close() anywere?
Also : how to use unset() and mysql_free_result (which,when) to reduce sql server load?


[24-Aug-2006 18:30:01] PHP Warning: mysql_connect(): Too many connections in /home/mruler/public_html/game_setup.php on line 13
[24-Aug-2006 21:51:13] PHP Warning: mysql_connect(): Lost connection to MySQL server during query in /home/mruler/public_html/game_setup.php on line 13
[24-Aug-2006 21:51:15] PHP Warning: mysql_connect(): Lost connection to MySQL server during query in /home/mruler/public_html/game_setup.php on line 13
[24-Aug-2006 21:55:01] PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/mruler/public_html/game_setup.php on line 13

Anthony2oo4
08-26-2006, 02:28 PM
I take it you wrote your own script.

It would be a good idea to only open and cloe the database connections when you need them and not leave them open all the time.

An easyw ay to do this is useing a function, that how I do mine.


function db($action) {

// Settings
$dbUsername = ""; // Database Username
$dbPassword = ""; // Database Password
$dbName = ""; // Database Name
$dbHost = "localhost"; // Database Host

// DONT EDIT ANYTHING BELOW HERE !!

if ($action == "open") {
// Connect to th database
mysql_connect($dbHost,$dbUsername,$dbPassword);
mysql_select_db($dbName) or die( "Unable to select database");
} elseif ($action == "close") {
mysql_close();
} else {
echo "Not a valid db handler";
}
}

I then do somthing like:


db("open");
$query = "blah"
db("close");

marek_mar
08-26-2006, 04:08 PM
That's actually not true.

lansing
08-26-2006, 05:03 PM
That's actually not true.Which part isn't true?

marek_mar
08-26-2006, 05:45 PM
The part about connecting and disconnecting for every query.

Anthony2oo4
08-26-2006, 06:26 PM
Why isnt it? That is what i read when I was learning. Im not going to argue the point because im not an expert but thought i would make my point known.

I quote:

Before I show you how to work with the database, I will show you one more command:

mysql_close();

This is a very important command as it closes the connection to the database server. Your script will still run if you do not include this command but too many open MySQL connections can cause problems for a web host. It is good practice to always include this line once you have issued all your commands to the database, to keep the server running well.

http://www.freewebmasterhelp.com/tutorials/phpmysql/3

NancyJ
08-26-2006, 06:41 PM
opening and closing for every query is bad, the best thing to do would be just increase the max number of allowed connections - if you can (if you're on shared hosting probably not)
adding mysql_close() at the end of any page using db connections might help a little - will reduce the amount of time a connection is left open after you're done with it.
But if you have high traffic, its likely that you just have more visitors that your host allows. Are your queries taking a long time to run? How fast do your pages load? If you're using phpmyadmin, try examining your server variables. For example, just had a look at my shared hosting and the max user connections is 25, so if I had more than 25 people on my site at any one time - then the errors would start.
where the problem lies depends very much on your site and your sql setup

NancyJ
08-26-2006, 06:44 PM
Why isnt it? That is what i read when I was learning. Im not going to argue the point because im not an expert but thought i would make my point known.

I quote:


http://www.freewebmasterhelp.com/tutorials/phpmysql/3
Its important to note here that he says after you've issues all your commands - not after each one.
Your mysql connection will expire at the end of your page - but its not instant, mysql_close is useful to a certain degree, ofcourse if your page goes on to do a lot of lengthy php processing then yes, definately close it as soon as you're done. But otherwise the difference between closing it straight after your last query and closing it at the end of the page is microseconds

Fumigator
08-26-2006, 06:48 PM
I don't see where Anthony advised opening and closing on every query. Maybe his coding example implied that, but I didn't interpret it that way.

marek_mar
08-26-2006, 06:54 PM
For example, just had a look at my shared hosting and the max user connections is 25, so if I had more than 25 people on my site at any one time - then the errors would start.
Some users would get the too many connections error if more than 25 users were requesting sometghing at once. It's actually not that easy to do.

Brandoe85
08-26-2006, 08:40 PM
Maybe he's thinking of connection pooling? But i'm just thinking of .NET Sql Server now....carry on. :)

arne2
08-27-2006, 11:18 AM
My sql max was already at 150. I increased it but isn't that just a temporarly solution, till your site get's bigger and then it will appear again?
I only get the errors at evenings (high traffic then). I'll add mysql_close to the footers, maybe it helps.
What about unset & mysql_free_result ? Are they usefull? How?

Anthony2oo4
08-27-2006, 12:52 PM
unset Unsets one or more variables.

example

<?
$song = array (
'name' => 'Another Second to Be',
'band' => 'Accept',
'album' => 'Russian Roulette',
'year' => 1986
);

print "<tt>$song</tt> before calling unset:\n";
print_r ($song);

// Individual array elements can be unset
print "\n<tt>\$song</tt> after calling <tt>unset (\$song['year']):</tt>\n";
unset ($song['year']);
print_r ($song);

print "\n<tt>\$song</tt> after calling <tt>unset (\$song):</tt>\n";
unset ($song);
print_r ($song);
?>

I dont see how this could help you in this matter.

mysql_free_result Frees memory taken up by a result handle.


mysql_free_result ($mysql_result);

Again i dont see how this would help with connections.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum