0

I am not a programmer so please be gentle :)

Following query on our website loads a lot of data to server's var/temp folder and creates server load and all sorts of troubles.

{php}
global $db;


$res = $db->get_results("select * from ".table_links." , pligg_files where      link_status='queued' and file_link_id = link_id and  file_size = '85x85' ORDER BY   `link_date` DESC LIMIT  5");

echo "<ul class='upcomstory'>";
foreach($res as $rslink)
{

$rslink->link_title = utf8_substr($rslink->link_title, 0, 40) . '...';

$cat = $db->get_var("select category_name from ".table_categories." where category__auto_id='".$rslink->link_category."'");
$catvar = $db->get_var("select category_safe_name from ".table_categories." where category__auto_id='".$rslink->link_category."'");
//echo "<li><div class='stcon'><div class='stpic'><img class='stimg' alt='".$rslink->link_title."' src='".my_base_url.my_pligg_base."/modules/upload/attachments/thumbs/".$rslink->file_name."' /></div><a href='".my_base_url.my_pligg_base."/story.php?id=".$rslink->link_id."'>".$rslink->link_title."</a><br /><br /> <span style='color:#044B9B;font-weight:bold;'>".$rslink->link_votes."</span> Vote -In: <span style='font-weight:bold;color:#044B9B;'>".$cat."</span></div> </li>";
echo '<li><div class="stcon"><div class="stpic"><img class="stimg" alt="'.$rslink->link_title.'" src="'.my_base_url.my_pligg_base.'/modules/upload/attachments/thumbs/'.$rslink->file_name.'" /></div><a href="'.my_base_url.my_pligg_base.'/story.php?id='.$rslink->link_id.'">'.$rslink->link_title.'</a><br /><br /> <span style="color:#044B9B;font-weight:bold;">'.$rslink->link_votes.'</span> Vote(s) </div> </li>';

}

echo "</ul>";
{/php}

Is there a way to 'clear' the output automatically as part of this query once every few minutes?

Thanks

KBS
  • 70
  • 1
  • 11
  • When you say 'lots of data', how big are the files we're talking about? The top query only returns 5 results so I can't imagine something like this causing a heap of data. How many users hit this code? – mal-wan Sep 26 '11 at 03:24
  • Hi, I think this query doesn't cache anything and runs for every user who accesses the website and hence creating the load. There are around 5000 users who access the site everyday. – KBS Sep 26 '11 at 03:44
  • You'd be a lot better off looking how to turn on some decent caching or looking at some other optimizations. Have you tried asking over at the Pligg forums ( http://forums.pligg.com/ ) because this definitely looks like a framework specific problem. They may have a fix. – mal-wan Sep 26 '11 at 03:49
  • thanks mate ..this is outside the framework - just a hack to display images on the sidebar. I am sure there is a way to do it within framework but programmer we hired was too lazy to do it the right way. – KBS Sep 26 '11 at 03:51
  • Then at least get him to cache it so it doesn't hammer your database. Honestly, if I were you I'd get him to fix the problem - deleting the temp files is a super dodgy workaround that could lead to some wacky stuff happening for users (timeouts, non-returned queries etc). Caching will help. – mal-wan Sep 26 '11 at 03:55

1 Answers1

0

It's not really clear how this could be filling your directory. But, you could use a cron job to periodically clean out that folder. This will delete any files that are older than 60 minues.

@hourly find /var/temp/ -mmin +60 -exec rm {} \;

Chris Henry
  • 11,914
  • 3
  • 30
  • 31
  • @KBS: You should **REALLY** know (and understand) what's going into your `/var/temp/` directory before just deleting things every hour. This could cause more problems than it solves. – mal-wan Sep 26 '11 at 03:34
  • @mwan Agreed. Only reason I suggested such an agressive approach is that I've never seen a standard `/var/temp` and made the assumption that someone put the directory specifically to store temporary files. – Chris Henry Sep 30 '11 at 06:06