php script crashing my server

anarchoi · 12-04-2012, 05:47 AM

I have a VPS at Hostgator. Since 2 months i am having problems with high server load, slowness and constant crashing. After investigation it seems that the problems are related to a script i made.

The script is for a phpBB3 forum. It's a side menu that will query MySQL and display:
- total number of posts
- total number of posts in specific forums
- last topics on the forums
- last replies on the forums
- last topics in a specific forum

I can't figure what is causing the high server load on a VPS. Could anyone help me ?

Here is the script:

Code:

<?php



include("config.php");


$query = "SELECT topic_id FROM phpbb_topics WHERE forum_id = 27 OR forum_id = 12 OR forum_id = 40 OR forum_id = 11 OR forum_id = 12 OR forum_id = 40";

$res = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($res)) {
$num_rows = mysql_num_rows($res);
}
echo "&bull; <a href=\"http://www.anarcho-punk.net/viewforum.php?f=11\">Anarchopunk albums: <b>$num_rows</b></a>";

echo "<br>";



$xquery = "SELECT topic_id FROM phpbb_topics WHERE forum_id = 46 OR forum_id = 53 OR forum_id = 54 OR forum_id = 50 OR forum_id = 49 OR forum_id = 45 OR forum_id = 47 OR forum_id = 48 OR forum_id = 51 OR forum_id = 52 OR forum_id = 64 OR forum_id = 60";

$xres = mysql_query($xquery) or die(mysql_error());

while($row = mysql_fetch_array($xres)) {
$xnum_rows = mysql_num_rows($xres);
}
echo "&bull; <a href=\"http://www.anarcho-punk.net/viewforum.php?f=27\">International albums: <b>$xnum_rows</b></a>";
echo "<br>";
$znum_rows = $xnum_rows+$num_rows;
echo "&bull; <a href=\"http://downloads.anarcho-punk.net\"><font color=\"#FF0000\">Total downloads: <b>$znum_rows</b></font></a>";

?>
<br>
<br>



<table width="160" align="left"><tr><td align="left">
<table width="100%" cellpadding="4" cellspacing="2"><tr><td bgcolor="#0F0F0F">


<table width="100%" style="border: 1px dashed #c0c0c0;" cellpadding="4" cellspacing="2"><tr><td bgcolor="#000000">
<b> &nbsp;> New Topics</b><br>
</td></tr></table>



<?php
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES utf8");


$query = "SELECT topic_id, forum_id, topic_title, topic_first_poster_name, topic_first_post_id, topic_replies, topic_last_poster_name, topic_time, topic_last_post_time from phpbb_topics WHERE forum_id not IN($dlid,38,63) ORDER BY topic_id DESC LIMIT 10";



$res = mysql_query($query) or die(mysql_error());



while($row = mysql_fetch_array($res)) {

$title = substr($row["topic_title"], 0, 25);
$fulltitle = $row["topic_title"];
$zztitle = $row["topic_title"];
$topic_id = $row["topic_id"];
$fpost = $row["topic_first_post_id"];
$poster = $row["topic_first_poster_name"];
$lastposter = $row["topic_last_poster_name"];
$forumid = $row["forum_id"];
$topictime = date("d-m-Y", $row["topic_time"]);
$topiclasttime = date("d-m-Y H:i", $row["topic_last_post_time"]);

$replies = $row["topic_replies"];
echo "&nbsp;<a href=\"viewtopic.php?f=$forumid&t=$topic_id\" title=\"$ztitle\" rel=\"htmltooltip\"><font size=\"1\">";
echo $title;
echo "..</font></a><br>";


echo "<!-- Matching tooltip with class=\"htmltooltip\" -->";
echo "<div class=\"htmltooltip\"><b><a href=\"viewtopic.php?f=$forumid&t=$topic_id\">$fulltitle</a></b> [<i><font size=\"1\">by $poster @ $topictime</font>]</i><br><br>";


$xquery = "SELECT post_text FROM phpbb_posts WHERE post_id = '$fpost'";
$xres = mysql_query($xquery) or die(mysql_error());
while($row = mysql_fetch_array($xres)) {



$xfulltext = $row["post_text"];


$posttext = substr($xfulltext, 0, 230);

 echo htmlentities(strip_tags($posttext));
}
echo " (...) <br><br><i><b>$replies ";

if ($replies <= 1) {
echo "reply</b>";
} else {
echo "replies</b>";
}
echo " - last post by $lastposter @ $topiclasttime</i></div>";



}


?>
</font>
</td></tr></table>
<div></div>
</td></tr><tr><td>
<br>


<div class="container">
<table width="96%"><tr><td bgcolor="#0F0F0F">
<font size="1">


<table width="100%" style="border: 1px dashed #c0c0c0;" cellpadding="4" cellspacing="2"><tr><td bgcolor="#000000">
<font size="2">
<b> &nbsp;> Latest Replies</b><br>
</font>
</td></tr></table>








<ilayer name="nscontainer" width=160 height=315 clip="0,0,160,215">
<layer name="nscontent" width=160 height=315 visibility=hidden>

<!--INSERT CONTENT HERE-->


<?php


$query = "SELECT topic_replies, post_subject, posts.topic_id, posts.forum_id, post_id from phpbb_posts posts, phpbb_topics topics WHERE posts.topic_id = topics.topic_id AND posts.forum_id != 38 AND topic_replies > 0 ORDER BY post_id DESC LIMIT 50";

$res = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($res)) {
$ztitle = $row["post_subject"];
$title = substr($row["post_subject"], 0, 100);
$fulltitle = $row["post_subject"];
$topic_id = $row["topic_id"];
$post_id = $row["post_id"];
$forumid = $row["forum_id"];
$replies = $row["topic_replies"];
$zuname = $row["post_username"];
$poster = $row["topic_first_poster_name"];
$lastposter = $row["topic_last_poster_name"];

echo "&nbsp;<a href=\"http://www.anarcho-punk.net/viewtopic.php?f=$forumid&p=$post_id#p$post_id\" target=\"_top\">";

echo $title;
echo "</a><br>";

$posttime = date("d-m-Y H:i", $row["post_time"]);
$topictime = date("d-m-Y", $row["topic_time"]);







echo "<!-- Matching tooltip with class=\"htmltooltip\" -->";
echo "<div class=\"htmltooltip\"><b>$fulltitle</b>
<br><i><font size=\"1\">Last reply by $lastposter @ $posttime</i>
<br><br>
<b>$replies ";

if ($replies <= 1) {
echo "reply</b>";
} else {
echo "total replies</b>";
}

echo "</b><br>
Topic started by $poster ($topictime)
</font><br>";

echo "</div>";


}




?>


<!--END CONTENT-->

</layer>
</ilayer>


<table width="160px"><td><div align="center">
<a href="#" onMouseover="moveup()" onMouseout="clearTimeout(moveupvar)"><img src="scroll2.gif" border=0></a>&nbsp; <a href="#" onMouseover="movedown()" onMouseout="clearTimeout(movedownvar)"><img src="scroll1.gif" border=0></a></div></td>
</table>


</td></tr></table>





</td>
</tr><tr><td>
<br>






<br>







<table width="100%"><tr><td bgcolor="#0F0F0F">
<font size="1">


<table width="100%" style="border: 1px dashed #c0c0c0;" cellpadding="4" cellspacing="2"><tr><td bgcolor="#000000">
<font size="2">
<b> &nbsp;> <a href="http://downloads.anarcho-punk.net">New Downloads</a></b><br>
</font>
</td></tr></table>

<?php


// Query the Database
$query = "SELECT topic_id, forum_id, topic_title, topic_poster from phpbb_topics WHERE forum_id IN($dlid) ORDER BY topic_id DESC LIMIT 10";

$res = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($res)) {
$title = substr($row["topic_title"], 0, 45);
$topic_id = $row["topic_id"];

echo "&nbsp;<a href=\"http://www.anarcho-punk.net/viewtopic.php?f=$forumid&t=$topic_id\">";
echo $title;
echo "</a>..<br>";
}



mysql_close($dblink);
?>
</td></tr></table>






</td>
</tr>
</table><br>

Custard7A · 12-04-2012, 08:44 AM

You mean besides querying about a billion things?

Code:

$query = "SELECT topic_id FROM phpbb_topics WHERE forum_id = 27 OR forum_id = 12 OR forum_id = 40 OR forum_id = 11 OR forum_id = 12 OR forum_id = 40";

12 and 40 are repeated. Not likely your magic solution — or much at all, actually — but it seems weird.

anarchoi · 12-04-2012, 09:01 AM

Quote:

You mean besides querying about a billion things?

My website doesn't have extreme high traffic so it shouldn't be a problem. I thought maybe my queries were not formulated right

But yeah you're right, there was a problem with the query repeating 12 and 40. I didn't notice it.

Redcoder · 12-04-2012, 04:34 PM

You should try benchmarking your script and finding where the bottleneck is in the script.
The bottleneck is ussually in the database so you should have super optimized queries. Use MySQLi(MySQL Improved) or PDO which is much faster and prepared statements for security and caching of queries (http://php.net/manual/en/mysqlnd-qc....rt.caching.php).

What will help you a lot in benchmarking is the microtime() or XDebug tool(http://xdebug.org/docs/profiler).

PHP Code:


			
$start_time = microtime(TRUE);
 
//The Query you want to test

$end_time = microtime(TRUE);

echo "Query 1 took : ". $end_time - $start_time."microseconds";