Deferred Threadviews v1.00 (to reduce server load)

Hack name: Deferred Threadviews
Version: 1.00
Purpose:
To reduce server load by not updating the view count on every page view but instead keeping the it in memory and writing to the db only occasionally.
Functions:
? Logs threadviews to a temporary table, and periodically commits data to the thread table.
? Frequency of writes controlled by server load and a random number.
? Includes optional email notification.
vB version tested on: 2.2.6
PHP version tested on: 4.2.0
Acknowledgements: Thanks to MattR for the "store threadviews as list of non-unique ids and use count(*)" suggestion that removed 1 query per pageview.



The updating of threadviews has been identified as a main cause of high server loads. This hack basically writes threadviews to a HEAP table (i.e. temporary table residing in memory) and will only update the thread count when the load is below a specified value and a random # value is hit.

(The impetus for this hack was an email from my host: They threatened to boot me off my server due to CPU usage "abuse", so as part of a larger effort, I decided to write this hack. *crossing fingers*)

SUMMARY of changes:
1) Set 1 Admin CP option
2) Create MySQL table
3) Insert code into SHOWTHREAD.PHP

The code will optionally send you an email every time the threadviews are committed to the thread table.

More details are in the text file.


This is what the email you'll get looks like:

2395 Total Views (original number of write queries)
170 Threads (new number of write queries)

93% of Writes Avoided [2225 avoided]
14:1 Original Writes vs. Consolidated Writes

Version History:

0.9 - Initial Release
0.91 - removed need for reading of temp table before writing to it (thanks to MattR). Note that if you've been using 0.9, you must re-create the my_threadviews table as it's structure has changed.
0.92 - added reporting of the ratio of Original Writes vs. Consolidated Writes. Use this to help in tweaking the frequency of updates. If the ratio is low, (e.g. 2:1, 5:1, etc) it probably means updates are occuring too often. (The ratio will also be low if you have many separate threads that are viewed a small number of times.)
1.00 - No code changes. Changed version to 1.00 since it's been stable for the past 2 months.

[Tim Wheatley]

if ($loadavg[0]<1 && mt_rand(1,5)=='44'){

[Darth Cow]

Originally posted by Raz
Jujubee, a great hack! Devs, should really consider making something similar as part of VB3.

I believe they already have . I read somewhere on the alpha forums that thread views are now only updated every hour .

[Ritsui]

How about scaling dynamically with load? Something like:

PHP Code:

$loadMult = (($loadavg[0] * 100) +5);    //change these values to tweak frequency of writes
if ( mt_rand(1,$loadMult)=='1' ) {
...snip... 


This would seem to work better at least with smaller forums that have occasional very heavy peaks where you still want views to be updated frequently, but less based proportionally on load.

[Jujubee]

Originally posted by Tim Wheatley

if ($loadavg[0]<1 && mt_rand(1,5)=='44'){

Tim, have you been getting any emails with the above settings? PHP will produce a random number between 1 and 5. This won't ever equal 44.

If you want more frequent updates, change the 44 to 1, then change the 5 to something higher. A limit of 5 would mean the views would be update once every 5 pageviews -- not much of a savings IMHO.

[Jujubee]

Originally posted by Ritsui
How about scaling dynamically with load? Something like:

PHP Code:

$loadMult = (($loadavg[0] * 100) +5);    //change these values to tweak frequency of writes
if ( mt_rand(1,$loadMult)=='1' ) {
...snip... 


This would seem to work better at least with smaller forums that have occasional very heavy peaks where you still want views to be updated frequently, but less based proportionally on load.

Sure, this would work. But personally, I don't want it doing any updates when the load is high. I don't mind losing views if the server crashes.

With your code, for example, the load could be 50 and it would still update (if the correct random number was produced). Hundreds of consecutive db writes when the load is at 50+ won't be a pretty sight.

[Mijae]

Originally posted by FFMania
Have some problem......when I find this code....

PHP Code:

    if ($noshutdownfunc) {
      $DB_site->query("UPDATE thread SET views=views+1 WHERE threadid='$threadid'");
    } else {
      $shutdownqueries[]="UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='$threadid'";
    } 


I can't fiind it since I have lesane Store hack installed..so the code becomes...

PHP Code:

if ($noshutdownfunc) {
  $DB_site->query("UPDATE thread SET views=views+1 WHERE threadid='$threadid'");
  
  // Store hack by Lesane

  $storeview=$DB_site->query_first("select views, postuserid from thread WHERE threadid='$threadid'");
  $views=$storeview[views];
  $postuserid=$storeview[postuserid];

  $storeadmin = $DB_site->query_first("SELECT * FROM storeadmin");
  $view1=$storeadmin[view1];
  $view2=$storeadmin[view2];
  $view3=$storeadmin[view3];
  $view4=$storeadmin[view4];
  $view5=$storeadmin[view5];
  $viewpoint1=$storeadmin[vpoint1];
  $viewpoint2=$storeadmin[vpoint2];
  $viewpoint3=$storeadmin[vpoint3];
  $viewpoint4=$storeadmin[vpoint4];
  $viewpoint5=$storeadmin[vpoint5];

  if ($views == $view1) 
  {
    $DB_site->query("UPDATE user SET storep=storep+$viewpoint1 where userid='$postuserid'");
  }
  elseif ($views == $view2) 
  {
    $DB_site->query("UPDATE user SET storep=storep+$viewpoint2 where userid='$postuserid'");
  }
  elseif ($views == $view3) 
  {
    $DB_site->query("UPDATE user SET storep=storep+$viewpoint3 where userid='$postuserid'");
  }
  elseif ($views == $view4) 
  {
    $DB_site->query("UPDATE user SET storep=storep+$viewpoint4 where userid='$postuserid'");
  }
  elseif ($views == $view5) 
  {
    $DB_site->query("UPDATE user SET storep=storep+$viewpoint5 where userid='$postuserid'");
  }
  
} else {
  $shutdownqueries[]="UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='$threadid'"; 


Any idea what I should do?

I have the same problem, I tried adding your hack anyways deleting some lines from Store Hack but I started getting php errors, so I have no idea what to do.

[Erwin]

The good news is that vB3 will have this built-in.

[eoc_Jason]

I like this hack but it has one quirk... late at night when not many people are on it doesn't update that often and people are asking why...

Would it be possible to code in a timer so that it would write every 5min or so? But also have the random number generation so that it will write properly during normal usage?

[ladyfyre]

How can i set this so that it updates every 500 threadviews, without any conditionals? The server load on the mysql server is not a factor...it is the php server load that is an issue and something in apc is not playing nicely with this hack. I would like to keep it, but would prefer being able to set a fixed # at which it will dump.

I have been trying to use this:

PHP Code:

if (mt_rand(1,500)=='44'){//change these values to tweak frequency of writes 


But it is not updating at 500 views.

[Scott MacVicar]

Here is a file to let you use a cron job to do it instead of in showthread, its more efficent in my opinion.

Open up the file and change the path to your config.php, upload now to your server, and then create a cron job.

Cpanel users can do this in the Advanced section

just type

php /home/path/to/deffered_views.php

and set it to run every 30mins