HUGE Load on My hosts server. Need some help!

[lajkonik86]

Heeyz.

I need a bit of help. I'm sort of new to mysql and php. After a lot of learning I got together a highscore list for my flashgame. But with some variables it gives my host a load of 47% on his cpu. My host is using a double Xeon 3.0ghz.
So i need to change my php/mysql a bit to reduce the load.

Could you guys help me find the problem?

Here is the code:

[PHP]<?php require_once('hsconfig.php');?>
<?php
$HSDB = mysql_pconnect($hostname_HSDB, $username_HSDB, $password_HSDB) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_HSDB, $HSDB);
$order = "DESC";
$i=0;
if (isset($_GET['red'])) {
$red = $_GET['red'];
} else {
$red=1;
}
if (isset($_GET['searchname']) ¦¦ isset($_GET['searchscore'])){

if (isset($_GET['searchname'])) {
$searchname = $_GET['searchname'];
}
if (isset($_GET['searchscore'])) {
$searchscore = $_GET['searchscore'];
}

if (isset($_GET['gameID'])){
$gameID = $_GET['gameID'];
} else {
$gameID = "thegame";
}
$query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order;
$Recordset1 = mysql_query($query_Recordset1, $HSDB) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$topplayers = array();
while ($results = mysql_fetch_array($Recordset1))
{
if (! $topplayers[$results['playername']])
{
$topplayers[$results['playername']] = $results['score'];
}
if (count($topplayers) == "18000") { break; }
}

$cnt = 1;
$blue = 1;

if ($totalRows_Recordset1 > 0) mysql_data_seek($Recordset1, 0);

while ($results = mysql_fetch_array($Recordset1))
{
$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];
if ($thenames[$blue] == $searchname && $thescores[$blue] == $searchscore){
$red = $blue;
break;
}

$blue++;
$cnt++;
if ($cnt > 18000) break;
}
}

$holder = $red+32;
if (! preg_match("/^[0-9]+$/", $holder)) { $holder = $red+32; }

$limit = $holder;
if (isset($_GET['gameID'])){
$gameID = $_GET['gameID'];
} else {
$gameID = "thegame";
}

$query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order;

$Recordset1 = mysql_query($query_Recordset1, $HSDB) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$topplayers = array();

while ($results = mysql_fetch_array($Recordset1))
{

if (! $topplayers[$results['playername']])
{
$topplayers[$results['playername']] = $results['score'];
}

if (count($topplayers) == $limit) { break; }

}

$cnt = 1;
$blue = 1;

if ($totalRows_Recordset1 > 0) mysql_data_seek($Recordset1, 0);

while ($results = mysql_fetch_array($Recordset1))
{
$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];

$blue++;
$cnt++;
if ($cnt > $limit) break;
}
if (isset($_GET['searchname']) ¦¦ isset($_GET['searchscore'])){
$thenames[$red] = "<b>$thenames[$red]</b>";
$thescores[$red] = "<b>$thescores[$red]</b>";
}
?>

<removed a ton of straight html code - jatar_k>

// actual php display code starts - jatar_k

<?php

echo "<table><tr><td width=\"30px\"><div id=\"up\">#</td><td width=\"80px\"><div id=\"up\">Name</td><td><div id=\"up\">Score</td></tr>";

while($i<=7)
{
echo "<tr><td><div id=\"thescore\">$red</td><td width=\"110px\"><div id=\"thescore\">$thenames[$red]</td><td><div id=\"thescore\">$thescores[$red]</td></tr>";
$red++;
$i++;
}

echo "</table>";

echo "</div></div>
<div id=\"bthebox\"><center>
<div id=\"gamershell\">";
echo $red;
echo " -- ";
echo $red+7;
echo "</div></center>
<div class=\"mostdownloaded\">
<table><tr><td width=\"30px\"><div id=\"up\">#</td><td width=\"80px\"><div id=\"up\">Name</td><td><div id=\"up\">Score</td></tr>";
$i=0;
while($i<=7)
{
echo "<tr><td><div id=\"thescore\">$red</td><td width=\"110px\"><div id=\"thescore\">$thenames[$red]</td><td><div id=\"thescore\">$thescores[$red]</td></tr>";
$red++;
$i++;
}

echo "</table>
</div></div>
<div id=\"cthebox\">
<div id=\"filefront\"><center>";
echo $red;
echo " -- ";
echo $red+7;
echo "</center>
</div>
<div class=\"mostdownloaded\">
<table><tr><td width=\"30px\"><div id=\"up\">#</td><td width=\"80px\"><div id=\"up\">Name</td><td><div id=\"up\">Score</td></tr>";
$i=0;
while($i<=7)
{
echo "<tr><td><div id=\"thescore\">$red</td><td width=\"110px\"><div id=\"thescore\">$thenames[$red]</td><td><div id=\"thescore\">$thescores[$red]</td></tr>";
$red++;
$i++;
}

echo "</table>
</div></div>
<div id=\"gamespot\">
<br>
<br>";
echo $red;
echo "<br>
&nbsp;&nbsp;¦<br>
&nbsp;&nbsp;¦<br>";
echo $red+7;
echo "<br>
<br>
<br>
</div>
<div id=\"dthebox\">
<div class=\"mostdownloadedauto\">
<table><tr><td width=\"30px\"><div id=\"up\">#</td><td width=\"80px\"><div id=\"up\">Name</td><td><div id=\"up\">Score</td></tr>";

$i=0;
while($i<=7)
{
echo "<tr><td><div id=\"thescore\">$red</td><td width=\"110px\"><div id=\"thescore\">$thenames[$red]</td><td><div id=\"thescore\">$thescores[$red]</td></tr>";
$red++;
$i++;
}

echo "</table></div></div>";

?>

// actual php display code starts - jatar_k
[/PHP]

<removed a ton of straight html code - jatar_k>

[edited by: jatar_k at 8:36 pm (utc) on Nov. 2, 2004]
[edit reason] removed specifics and trimmed code [/edit]

[jatar_k]

Welcome to WebmasterWorld lajkonik86,

>> $query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order;

better limting on your result set might help, you wouldn't be pulling and looping through, so much data

this scares me, WOW

>> if ($cnt > 18000) break;

you also use a while loop twice to go through the results

line 30 and line 45, relative to your code posted into my editor

I count 8 while loops total, if each of those is possibly going through 18000 lines, that's a serious problem

2 individual queries that look like they do the same thing - line 26 and 70

a lot of code seems to be duplicated

then there are 3 while loops inside individual divs in the page code near the bottom, could take some time depending on how many records they loop through each

suggestion

you're working with the arrays too many times and looping through the results from mysql too many times.

all of this code aside, can you describe to me what exactly you want to do?

how long does this page actually take to finish, it looks like it might take a few minutes.

[edited by: jatar_k at 8:42 pm (utc) on Nov. 4, 2004]

[lajkonik86]

Thx for the response.

No the page actually loads within several seconds, barely notice it's database drivin.

When using the searchroutines (most loads comes from that) it still doesnt take that long.

Hope the site gives you a better idea of what i'm trying to do.

[edited by: jatar_k at 10:53 pm (utc) on Nov. 3, 2004]
[edit reason] no personal urls thanks [/edit]

[jatar_k]

I understand what the end product should be, it's a high scores list, nothing complicated in that

what I was referring to was I need this data from here and combine it with this data from there and then I need to do this with it

your script looks mostly like it is suffering from being cobbled together, it just needs streamlining. Mostly the things that I referred to in my original response.

[lajkonik86]

not following...

[jatar_k]

look through my initial response, there are a large number of issues there that could be addressed.

[lajkonik86]

Did a lot of changing. When you'r not that good in PHP doing this can be a real puzzle.

Could you take a look at the changes and tell me if you think the load will be less.

Greetz Thierry

[code]

<?php require_once('hsconfig.php');?>
<?php
$HSDB = mysql_pconnect($hostname_HSDB, $username_HSDB, $password_HSDB) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_HSDB, $HSDB);
$order = "DESC";
$i=0;
$gameID = "thegame";
$condit=0;

if (isset($_GET['red'])) {
$red = $_GET['red'];
} else {
$red=1;
}
$query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order;

$Recordset1 = mysql_query($query_Recordset1, $HSDB) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$topplayers = array();

if (isset($_GET['searchname']) ¦¦ isset($_GET['searchscore'])){

if (isset($_GET['searchname'])) {
$searchname = $_GET['searchname'];
}
if (isset($_GET['searchscore'])) {
$searchscore = $_GET['searchscore'];
}

while ($results = mysql_fetch_array($Recordset1))
{
if (! $topplayers[$results['playername']])
{
$topplayers[$results['playername']] = $results['score'];
}
if (count($topplayers) == "18000") { break; }
}

$cnt = 1;
$blue = 1;

if ($totalRows_Recordset1 > 0) mysql_data_seek($Recordset1, 0);

while ($results = mysql_fetch_array($Recordset1))
{

$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];
if ($thenames[$blue] == $searchname && $thescores[$blue] == $searchscore){
$red = $blue;
$temp = $blue+32;
$condit=1;
}

$blue++;
$cnt++;
if ($condit == 1) {
if ($cnt > $temp) break;
}
if ($cnt > 18000) break;
}
$thenames[$red] = "<b>$thenames[$red]</b>";
$thescores[$red] = "<b>$thescores[$red]</b>";
}

else {

$holder = $red+32;
if (! preg_match("/^[0-9]+$/", $holder)) { $holder = $red+32; }
$limit = $holder;
$cnt = 1;
$blue = 1;
if ($totalRows_Recordset1 > 0) mysql_data_seek($Recordset1, 0);

while ($results = mysql_fetch_array($Recordset1))
{

$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];


$blue++;
$cnt++;
if ($cnt > $limit) break;
}
}

?>

<html>
[end]

[jatar_k]

big changes, very good work

one thing that still gets me is that you are still using

while ($results = mysql_fetch_array($Recordset1))

3 seperate times, which means you will be talking to mysql 3 times about the same thing, one option would be to read it into an array the first time and then just using the array each loop after that

or doing all the processing in a single loop would be the best bet

[rlkanter]

One way to generally cut down processing time/server load is to make sure you're using indexes if you can. You might want to try adding indexes to any column you use in the where section of the sql query. Also make sure the datatypes for the columns are correct (don't be using varchar where smallint would work.)

Anything thing you could do is setup static pages for most common pages and refresh them at some interval (1 minute / 5 mins / etc.)

[lajkonik86]

Because my site loads in several seconds.
The only way for a php script to create a big load would be to stay running on the server while the page is already loaded. Plz correct me if i'm wrong.

I'm mostly worried about searches with no result.
I think that will cause the most power because it actually has to check the entire database.

Anyhows my site is loading a dual xeon 3.0 to 47% sometimes. There must be something very wrong...

Cheers,
Thierry

[lajkonik86]

Changed more stuff

---------

<?php require_once('hsconfig.php');?>
<?php
$HSDB = mysql_pconnect($hostname_HSDB, $username_HSDB, $password_HSDB) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_HSDB, $HSDB);
$order = "DESC";
$i=0;
$gameID = "thegame";
$cnt = 1;
$blue = 1;
$found = 0;

if (isset($_GET['red'])) {
$red = $_GET['red'];
} else {
$red=1;
}
$query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order;
$Recordset1 = mysql_query($query_Recordset1, $HSDB) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$topplayers = array();
if ($totalRows_Recordset1 > 0) mysql_data_seek($Recordset1, 0);

if (isset($_GET['searchname']) ¦¦ isset($_GET['searchscore'])){

if (isset($_GET['searchname'])) {
$searchname = $_GET['searchname'];
}
if (isset($_GET['searchscore'])) {
$searchscore = $_GET['searchscore'];
}

while ($results = mysql_fetch_array($Recordset1))
{
$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];
if ($thenames[$blue] == $searchname && $thescores[$blue] == $searchscore){
$red = $blue;
$temp = $blue+32;
$found=1;
}

$blue++;
$cnt++;
if ($found == 1) {
if ($cnt > $temp) break;
$thenames[$red] = "<b>$thenames[$red]</b>";
$thescores[$red] = "<b>$thescores[$red]</b>";
}

if ($cnt == mysql_num_rows($Recordset1)) break;
}

} else {

$holder = $red+32;
if (! preg_match("/^[0-9]+$/", $holder)) { $holder = $red+32; }
$limit = $holder;

while ($results = mysql_fetch_array($Recordset1))
{

$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];


$blue++;
$cnt++;
if ($cnt > $limit) break;
}
}

?>

---------------

Still there must be a leak somwhere

[Timotheos]

I'd be interested to know if mysql_pconnect is the problem. It's a shot in the dark but you should really use mysql_connect unless you have a good reason.

See the user comments at [php.net...]

[jatar_k]

good thought Timotheos, I missed that

[jatar_k]

another thing is you are only displaying 32 results at any given time, why not LIMIT your mysql query?

[dev.mysql.com...]

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

so something like

$query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order . " LIMIT " . $startrow . ",32";

$startrow would be 0 for the top 32 and then you could pass a different value for later pages

[lajkonik86]

i'll try that.
Thanks a lot

[lajkonik86]

Got this now
------------
<?php require_once('hsconfig.php');?>
<?php
$HSDB = mysql_connect($hostname_HSDB, $username_HSDB, $password_HSDB) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_HSDB, $HSDB);
$order = "DESC";
$i=0;
$gameID = "thegame";
$cnt = 1;
$blue = 1;
$found = 0;

if (isset($_GET['red'])) {
$red = $_GET['red'];
} else {
$red=1;
}
$query_Recordset1 = "SELECT playername, score FROM " . $table_HSDB . " WHERE gameid = '" . $gameID . "' ORDER BY score " . $order;
$Recordset1 = mysql_query($query_Recordset1, $HSDB) or die(mysql_error());
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$topplayers = array();
if ($totalRows_Recordset1 > 0) mysql_data_seek($Recordset1, 0);

if (isset($_GET['searchname']) ¦¦ isset($_GET['searchscore'])){

if (isset($_GET['searchname'])) {
$searchname = $_GET['searchname'];
}
if (isset($_GET['searchscore'])) {
$searchscore = $_GET['searchscore'];
}

while ($results = mysql_fetch_array($Recordset1))
{
$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];
if ($thenames[$blue] == $searchname && $thescores[$blue] == $searchscore){
$red = $blue;
$temp = $blue+32;
$found=1;
}

$blue++;
$cnt++;
if ($found == 1) {
if ($cnt > $temp) break;
$thenames[$red] = "<b>$thenames[$red]</b>";
$thescores[$red] = "<b>$thescores[$red]</b>";
}

if ($cnt == mysql_num_rows($Recordset1)) break;
}

} else {

$holder = $red+32;
if (! preg_match("/^[0-9]+$/", $holder)) { $holder = $red+32; }
$limit = $holder;

while ($results = mysql_fetch_array($Recordset1))
{

$thenames[$blue] = $results[playername];
$thescores[$blue] = $results[score];


$blue++;
$cnt++;
if ($cnt > $limit) break;
}
}

mysql_close($HSDB);
?>
--------
Think it improved a lot.
About the limit you sugested.
I could only use that for the normal displaying. But not when searching the highscores.

You all. Thank you a lot.
I suspect the problem to be solved.