Deferred Threadviews v1.00 (to reduce server load)

[Jujubee]

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.

[Velocd]

*still trying to make sense of it all*

[Admin]

Basically it doesn't update the thread table every time a thread is viewed (to incremement the thread views), instead the views are kept in a different table (a HEAP table, much faster than MyISAM tables), and then once in a while updates the real thread table.

It doesn't add any visual features.

[eva2000]

i like :)

i know a few boards that would love this :D

[MattR]

Won't the count/update of my_threadviews cause almost as much locking problems as the current thread table?

I would say simply keep inserting a threadID into the table, then have the job update with a count( * ), threadid group by threadid to get the threadIDs.

[Jujubee]

Originally posted by MattR
Won't the count/update of my_threadviews cause almost as much locking problems as the current thread table?


Not nearly as much, IMHO. :) The main problem with the orginal code is that the thread table is read locked (i.e. no reads are allowed) everytime showthread.php is called (or more precisely, every time thread views are updated). Reading of the thread table is one of the most common occurences in vb: it occurs on the home page, on forum display, on showthread, on search, and probably a few others, so the locking "collisions" occur very frequently.

With this hack, the my_threadviews table is only read in showthread, so the read "collisions" are kept to a minimum.

UPDATE: with your suggestion, the table is only written to so there are almost not table-locking issues. The only time the table is read is when the views are about to be committed to the thread table.


I would say simply keep inserting a threadID into the table, then have the job update with a count( * ), threadid group by threadid to get the threadIDs.

That's an excellent idea! It would remove the need for the select query on every pageview. I'll go try it out. :)

[Jujubee]

Ok, I've updated it using Mattr's suggestion.

No more checking the temp table before writing to it. Temp table has changed format, so you have to recreate the table if you've started using the first version.

[Smoothie]

will this make showthreads, and forumdisplay faster to load?

[Jujubee]

Originally posted by Smoothie
will this make showthreads, and forumdisplay faster to load?

Well, it does nothing to speed up those pages directly, but by deferring writing to thread table, the thread table is "read locked" less of the time, which in turn speeds up any page where the thread table is read (forumdisplay, showthread, index, search results).

So this hack helps for boards with many simultaneous users. If you've got 20 people browsing, you're not going to see any difference. :)

[Smoothie]

What would be the limit of simultaneous users before you would notice any difference?

[Jujubee]

Originally posted by Smoothie
What would be the limit of simultaneous users before you would notice any difference?


It's hard to say -- depends on what their doing, and if you're on a shared server, it depends on how busy the server is. If you're load is below 2 all the time, you shouldn't worry about this hack too much. :)

[1stTrade]

does this hack reduce also the server-traffic ?

I don´t care about the serverload, but traffic is a important thing to me.

[CJi]

Originally posted by Jujubee

So this hack helps for boards with many simultaneous users. If you've got 20 people browsing, you're not going to see any difference. :)

How about 130+ ? I'm trying to decide if the issue of a server crash is worth the speed increase. (e.g. if the server dies prior to this hack actually writing to a 'real' table, the counts will be lost?).

[Jujubee]

Originally posted by 1stTrade
does this hack reduce also the server-traffic ?

I don?t care about the serverload, but traffic is a important thing to me.

No, the traffic isn't affected in any way.


How about 130+ ? I'm trying to decide if the issue of a server crash is worth the speed increase. (e.g. if the server dies prior to this hack actually writing to a 'real' table, the counts will be lost?).

I'm pretty sure it'll make a big difference at 130+. :) But, yes, if the server dies, you'll lose the threadviews since the last update. The way I see it, if the server is crashing, you've got bigger problems to worry about than thread views. :p

[alexi]

We are loping along at 400 users on line and it seems to be working great, the updates are happening every 1-2 minutes with a savings of from 7-40%...I am not sure what ideal is

[Jujubee]

Originally posted by alexi
We are loping along at 400 users on line and it seems to be working great, the updates are happening every 1-2 minutes with a savings of from 7-40%...I am not sure what ideal is

Since you're on a dedicated server you can afford to have more bulk updates. I'd suggest cranking up the second number (default is 600) in the mt_rand function so that updates take place less often. 40% is ok, but 80% would be better. :)

[alexi]

Ok let's try a 1000 and see what happens :)

[alexi]

I think a 1000 might be a spec high!

4431 Total Views (original number of write queries)
1310 Threads (new number of write queries)

3121
Writes Avoided (70% avoided)

[Jujubee]

Originally posted by alexi
I think a 1000 might be a spec high!

4431 Total Views (original number of write queries)
1310 Threads (new number of write queries)

3121
Writes Avoided (70% avoided)

Yowza! 1300 queries... :paranoid:

Keep tweaking :)

[CJi]

I'll install this tomorrow and give it a whirl :)

[Okiewan]

Worth the effort (in general) for a baord with 100- 150 online?

[Jujubee]

Originally posted by Okiewan
Worth the effort (in general) for a baord with 100- 150 online?

This hack is worth the effort if you need to reduce your load. If you've got 150 users but no load problems, then it's not really necessary. If you're load is above 2, then this hack should help a little. ;)

[CJi]

I get edgy when our load goes above 1.2, so I'm going to put this hack in tomorrow and see how we do. :)

[Jujubee]

Well, after about 18 hours of high load today (shared server), I got this email:

5566 Total Views (original number of write queries)
278 Threads (new number of write queries)
5288 Writes Avoided (95% avoided)


5500 MySQL queries distilled into 280 -- now that's what I call efficiency. :bunny:

[VirtueTech]

Originally posted by Jujubee
Well, after about 18 hours of high load today (shared server), I got this email:


5500 MySQL queries distilled into 280 -- now that's what I call efficiency. :bunny:

What are your settings all around?

[Jujubee]

Originally posted by VirtueTech


What are your settings all around?

My settings? Which ones?

[VirtueTech]

Originally posted by Jujubee


My settings? Which ones?

The 3 settings for the script.

[Jujubee]

Originally posted by VirtueTech


The 3 settings for the script.

I'm using the default settings. :)

[CJi]

After a little bit of tweaking with the settings, I'd say this works rather well :)

1149 Total Views (original number of write queries)
308 Threads (new number of write queries)

841 Writes Avoided (73% avoided)

Congratulations on a fantastic hack!

[ColinP]

Fantastic hack :)

[The Piper]

This seems really fantastic, and I have installed it already, but so far I've gotten no e-mails whatsoever. Since my whole site is built around vBulletin 1000 or so threadviews should happen pretty quick. I get around 10,000-12,000 page views every day! Any clue of why I didn't get any e-mails with the stats? Thanks a lot!

[Jujubee]

Originally posted by The Piper
This seems really fantastic, and I have installed it already, but so far I've gotten no e-mails whatsoever. Since my whole site is built around vBulletin 1000 or so threadviews should happen pretty quick. I get around 10,000-12,000 page views every day! Any clue of why I didn't get any e-mails with the stats? Thanks a lot!

The only reason you wouldn't get the emails is if your server load is consistently above 2.0. Login to the Admin CP and check your current load.


Also, check the my_threadviews table via PHPmyadmin to make sure it's being written to.

[The Piper]

Originally posted by Jujubee


The only reason you wouldn't get the emails is if your server load is consistently above 2.0. Login to the Admin CP and check your current load.


Also, check the my_threadviews table via PHPmyadmin to make sure it's being written to.

Ok, I just got several e-mails today, so this is definately working. Been getting anywhere from 40-80% avoided queries. Most of them around the 70% mark. Definately an AWESOME hack, this should be in vB 3.x!

Thanks a lot for such a great hack and for your help!

[Jujubee]

Glad to be of help. :)


BTW, the server my machine is on has been running at >5 load avg all day, and the temp table's got 14000 rows in it.... that's gonna be one crazy update. Will post when I get the email. :paranoid:

[Regs]

Jujubee,

What has been the response at VO? Are they taking notice of the extraordinary steps you have taken to lessen the load?

Cheers,

~Regs.

[Jujubee]

Originally posted by Regs
Jujubee,

What has been the response at VO? Are they taking notice of the extraordinary steps you have taken to lessen the load?

Cheers,

~Regs.

PMed you -- don't want to throw off this thread. :)

[Jujubee]

Latest email from 24-hour period:

16924 Total Views (original number of write queries)
434 Threads (new number of write queries)

16490 Writes Avoided (97% avoided)


For the statisticians out there, this is a 39:1 ratio of original queries versus consolidated queries. Which in english means there were originally 39 times the number of queries the were actually written to the disk.

[Mark Hewitt]

This sounds like a good hack but is there a way to decouple it from sever load and make it just write periodically?

For whatever reason the server load monitoring doesn't work on my server. However I would like to be able to reduce the load.

[Jujubee]

Originally posted by Mark Hewitt
This sounds like a good hack but is there a way to decouple it from sever load and make it just write periodically?

For whatever reason the server load monitoring doesn't work on my server. However I would like to be able to reduce the load.

Easy:

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

and remove " $loadavg[0]<2 && "

[Raz]

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

But if your load is so high, why not just disable views? They're not all that important if you're having server load problems.

[Jujubee]

Originally posted by Raz
But if your load is so high, why not just disable views? They're not all that important if you're having server load problems.

I consider views important to spur participation. Zeroes all over makes the board seem dead. :)


BTW, latest email after about 3 days without an update:


19063 Total Views (original number of write queries)
400 Threads (new number of write queries)

18663 Writes Avoided (98% avoided)
48:1 Original Writes:New Writes

[Erwin]

Cool. I've installed this. Will let you know how it goes for me.

[Erwin]

Okay, my first email:


4018 Total Views (original number of write queries)
535 Threads (new number of write queries)

3483 Writes Avoided (87% avoided)


Not bad at all... :)

Thanks for a great hack!

[Jujubee]

Ok, I've updated the hack a little. I've added reporting of the ratio of Original Writes vs. Consolidated Writes.

Email will look 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



I use the ratio to tweak the frequency of the updates. A low ratio means updates are occuring too often. There is no "correct" ratio as it depends on the number of threads being updated -- you don't want too many threads being updated at the same time.

IMHO, ratios of less than 5 probably means that updates are too frequent.


(for those using a previous version, only the code has changed -- the db table remains unchanged)

[Erwin]

Possible bug:

The thread views are not being recorded properly. I have a few threads with many replies where the thread views are persistently at 0 and not updated.

Do these threadviews get updated when the db write occurs?

[Jujubee]

Originally posted by Erwin
Possible bug:

The thread views are not being recorded properly. I have a few threads with many replies where the thread views are persistently at 0 and not updated.

Do these threadviews get updated when the db write occurs?

The threadview count is updated only when the db write occurs. So depending on the frequency of your updates, the count will remain at 0 for new threads for a while.

Is the db being written to daily? (i.e. how often do you get an email?)

[Martin CX]

This looks like a marvelous hack!

I would install it, but since vB is the auxillary forum on my site (waiting for vB3) I don't have the need for it just yet. I hope, though, vB3 has a similar feature (or perhaps you'll be able to port it real quick? ;)) since I most often have around at 2-300 online users/guests simultaneously during the day hours.

[Erwin]

Originally posted by Jujubee


The threadview count is updated only when the db write occurs. So depending on the frequency of your updates, the count will remain at 0 for new threads for a while.

Is the db being written to daily? (i.e. how often do you get an email?)

I figured it out. :) Thanks!

Once a day or once every 2 days. It's working fine, and there is a noticeable difference.

[stilger]

Hello.. Im thinking about installing this hack on a site that I support that averages about 400 users logged in at a time. Sometimes peaking to around 2000 (well vbulletin reports the most ever as being 2410 I don't know how much you can trust this though). We currently spread our load over 2 servers using Round Robin DNS and have a separate server for all database functions. Will this hack work on multiple server site? I am thinking because of how this hack works there should be no problem but I wanted to make sure. It sounds like this hack would help with the occasional To Many Connections messages we get..... Thanks for the info.

[Tim Wheatley]

Installing tonight: 12:40am UK time 8-AUG.

Will post my first e-mail. Looks like it'll really help me. We get a lot of guests visiting and not posting (just viewing) as well as 200-300 logged in users.

[Tim Wheatley]

I've decided to keep this hack installed. But have not decided to keep the original settings.

I have removed the line which tells it to work under a certain server usage. And have just got it running so it moves them to the database periodically.

I've attached a text file which is a downloaded post from my admin forum on my forums, this contains over 50 e-mails sent by this hack.

I have now commented out the e-mail lines because I'm happy with it's performance at these settings.

[FFMania]

Have some problem......when I find this 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...

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?

[Courage]

I'm the only one with a problem.. :)

After I installed this hack I got 200 Email errors in ~ 5 min :o


Database error in vBulletin :

Link-ID == false, connect failed
mysql error: Too many connections

mysql error number: 1040


I'm using Vb 2.2.2 witht A LOT of hacks :o

[Tim Wheatley]

Update! This is going to be uninstalled from my boards. Last nights busy period was very busy and all went well. Then it got to about 1am when the visitors start to go down and BOOM - the board was killed for 5mins. I kept trying to enter and when I eventually did the server stats said that usage had been:

10.40 7.00

This caused massive problems and also (I believe) all the view counts were lost.

It seems that when it comes to updating the stats it's such a massive undertaking that it kills the server pc.

[Tim Wheatley]

I have reinstalled this.

Now have it functioning correctly.

My 'normal users online' is around 100-150 at once. Peaking maybe at 250 at times. A lot of guests visit and a lot of people read threads and don't post, let me know if anyone wants to see my settings for it...

[Capt PPRuNe]

Errr... can someone shed some light on the reason every time I try to download this hack i get v0.91 instead of v0.92?

having installed it I am getting the emails but without the ratio bit added to v0.92.

Looks like it will be a great hack as i have over 50,000 registered users and at least twice that many guests with an average of 250-350 users on at any one time peaking to about 550 every day.

Dedicated server with average load at around 1.5-2.5 but can peak to about 6 or 7 and ocassionally even more.

[Jujubee]

Originally posted by stilger
Hello.. Im thinking about installing this hack on a site that I support that averages about 400 users logged in at a time. Sometimes peaking to around 2000 (well vbulletin reports the most ever as being 2410 I don't know how much you can trust this though). We currently spread our load over 2 servers using Round Robin DNS and have a separate server for all database functions. Will this hack work on multiple server site? I am thinking because of how this hack works there should be no problem but I wanted to make sure. It sounds like this hack would help with the occasional To Many Connections messages we get..... Thanks for the info.

Doh! I stopped getting notification emails... sorry for the long delay. :rolleyes:

I don't see why this wouldn't work with multiple web servers, but I can't be 100% without intimate knowledge of your setup. (that's me covering my ass :p )

The hack makes cumulative updates to the db, so while the two webservers would update the db at different times, it should still work. You'll probably have to tweak the update frequency to get it to your liking, but otherwise, I don't forsee a problem.

[Jujubee]

Originally posted by FFMania
Have some problem......when I find this 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...
<snip>
Any idea what I should do?

I'm not familiar with the Lesane hack, but it looks way more complicated than this hack. Maybe you could ask them...

[stilger]

I just installed this. Tweaking now.

I would like to see your settings....

Originally posted by Tim Wheatley
I have reinstalled this.

Now have it functioning correctly.

My 'normal users online' is around 100-150 at once. Peaking maybe at 250 at times. A lot of guests visit and a lot of people read threads and don't post, let me know if anyone wants to see my settings for it...

[Darth Cow]

Hmmm... thank you. Just installed and should do wonders for our forums :cool:.

[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 :cool:.

[Ritsui]

How about scaling dynamically with load? Something like:
$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:
$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....

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...

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:

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

[eva2000]

Originally posted by PPN
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 interesting idea... anything else needed to be done (edit files etc ? )

or just this 2 steps before this cronjob ?

[1) ADMIN CP CHANGES:

Admin CP -> vB Options -> *NIX Load Limit -> set to any "non-zero number"

NOTES: I use 10 as the "non-zero number", but you can set it to anything you want,
You can view your current load on the main page of the Admin CP.
Remember that your board will be inaccessable when the server load goes
above this number, so don't set it too low! The "correct" number is a matter of debate. :)



2) CREATE MYSQL TABLE

CREATE TABLE my_threadviews (
threadid mediumint(9) NOT NULL default '0'
) TYPE=HEAP;

NOTES: Via PHPmyAdmin: paste the above into the box labeled "Run SQL query/queries on database ...."

[Martin64]

Sounds like a great hack for big size boards, I would install it if I hadn't moved my forums to a seperate server already (had some troubles earlier).

Good job! :)

[Kaelon]

I've just installed this hack on v2.2.9, and I'll report on any improvements. My forums regularly have between 60 and 80 users and I've been looking to pin-point and reduce weird server spikes that raise my load (which is ordinarily between 1 and 2) up to 3 and sometimes 4 or 5.

Kaelon

[Kaelon]

Some preliminary results on my board:

273 Total Views (original number of write queries)
131 Threads (new number of write queries)

52% of Writes Avoided [142 avoided]
2:1 Original Writes vs. Consolidated Writes

This was in a 17 minute period. So, good deal thus far.

Kaelon

[Mijae]

Last time I tried to use this hack it reset my Who's Online record to 0, but I will install it again now that I have upgraded :)

[Mijae]

I get safe mode errors when I set *NIX Load Limit to anything but 0. Damn.

[mini2]

Installed first time no problem, thanks!

Paul

[Overgrow]

Excellent! I just stumbled on this while searching for something else. Should be mandatory for big boards sticking with 2.x for a while.

[Overgrow]

I'm rarely getting more than 50% writes avoided.. which may be expected when you have many different threads that users are opening. I've turned the delay setting up to 1000 since I don't want to wait too long for the updates even if it would mean a higher hit ratio.

Also for those of you running separate DB servers, the load checker will do nothing as the built in load check is only on your web server. fastforward released a remote db-server load checker that works fine here, I'll find it if anyone needs.

[scotty]

hm - I installed the hack and everything worked fine...

...except the "mark read" feature of thread, that the icon of the thread is changed.

anyone else reporting this problem?

[Jujubee]

Today at 06:35 AM scotty said this in Post #80 (https://vborg.vbsupport.ru/showthread.php?postid=392127#post392127)
hm - I installed the hack and everything worked fine...

...except the "mark read" feature of thread, that the icon of the thread is changed.

anyone else reporting this problem?

This hack doesn't touch icons or dates so I don't know where those changes are coming from.

BTW, I've been letting this run for 24 hours between updates:

3884 Total Views (original number of write queries)
212 Threads (new number of write queries)

95% of Writes Avoided [3672 avoided]
18:1 Original Writes vs. Consolidated Writes

[scotty]

Today at 08:01 PM Jujubee said this in Post #81 (https://vborg.vbsupport.ru/showthread.php?postid=392334#post392334)
[B]This hack doesn't touch icons or dates so I don't know where those changes are coming from.
I can't explain it too - I've looked over the code and the changed code doesn't touch the cookie settings... strange

[Kaelon]

Just wanted to report that since installing this hack, my load has rarely risen over 1, and I have over 3,000,000 views a month.

Great work, Jujubee!

Kaelon

[Odysseus]

Jepp, this works just great for me, too.

This stupid threadview-update caused countless "deadlocks" on my machine which resulted in serverloads of 20.0 or even bigger throughout the day ... after installing this hack, the load has never reached a value above 2.50 again.

Great! Thanks! :banana:

[Sabrina]

nm figured it out :)

[Cal Poly Forum]

BUmp.

So would this work on 2.3.2 without any problem??

[Cal Poly Forum]

Bump.

[julius]

I installed on 2.3.2 without any problem.

It adds a query: before installing this hack I had 21 queries on showtread, now I have 22 queries.

[Q8vbhacks]

users online 300-400 server load over 7
there is setting

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

NIX Load Limit 0 <--- i don't want to be server busy to our users

877 Total Views (original number of write queries)
540 Threads (new number of write queries)

38% of Writes Avoided [337 avoided]
2:1 Original Writes vs. Consolidated Writes

[AKosygin]

First, I would like to thank you a billion, this has saved us and we see MASSIVE performance gains. We dropped 20 points on the NIX load and no more lagging. We also tested this to work with vBulletin v2.3.2 and 2.2.x. We get 300 to 400 visitors and it was lagging until the database server was overloaded, now we are zooming by most of the time.

I get safe mode errors when I set *NIX Load Limit to anything but 0. Damn.

We had similar problems, it means that the load average command belongs to root. With PHP's safe mode on, a file belonging to you can not access a file belonging to anyone else including files belonging to root. You will have to ask your server admin to turn of PHP's safe mode.

If you can not turn off safe mode, another solution is to have it read how many processes your SQL login is processing. We had to hack this hack to check that. Actually, we really hacked this hack with a bunch of functions. Maybe I will post the giant hack of this hack later when I have time to refine it a bit.

[pgowder]

Has anyone done this hack with the store hack??

Can you post your code?

[pgowder]

Hello??

Anyone??

[pgowder]

I would really like to run this can anyone help?

[dwh]

For vb3.0 wouldn't it much more efficient to have a separate table for pageviews and make that a heap table in the first place?

[dwh]

Actually, scratch that, heap tables are deleted when machine is rebooted?

[Venom2890]

I get problems with the search index when using this hack

Database error in vBulletin 2.2.8:

Invalid SQL: DELETE FROM searchindex WHERE wordid IN (0,4218,1488,235788,4322,21662,452,1577,4216,96491 ,52152,1028,2705,176,121,1092,2865,2854,5861) AND postid=300688
mysql error: Can't open file: 'searchindex.MYI'. (errno: 145)

mysql error number: 1016

Date: Friday 16th of January 2004 11:11:17 AM
Script: http://www.sgnonline.com/vb/vb/editpost.php
Referer: http://www.sgnonline.com/vb/editpost.php?s=&action=editpost&postid=300688

[dwh]

Could be you ran out of space in your /tmp directory.

[Raz]

Could be you ran out of space in your /tmp directory.
Looks more like table corruption. Run myisamchk

[dwh]

A better place for a similar type hack might be attachment.php. Those counters are probably updated much more often than threadviews if you have a lot of attachments in a page.