Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    May 2007
    Posts
    93
    Thanks
    4
    Thanked 0 Times in 0 Posts

    php script crashing my server

    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>

  • #2
    Regular Coder Custard7A's Avatar
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    286
    Thanks
    32
    Thanked 33 Times in 33 Posts
    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.

  • #3
    New Coder
    Join Date
    May 2007
    Posts
    93
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Regular Coder Redcoder's Avatar
    Join Date
    May 2012
    Location
    /dev/null
    Posts
    332
    Thanks
    2
    Thanked 47 Times in 46 Posts
    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"
    Last edited by Redcoder; 12-04-2012 at 04:37 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •