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:

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.

*still trying to make sense of it all*

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.

i like :)

i know a few boards that would love this :D

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.

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.

Quote:

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

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.

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

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

does this hack reduce also the server-traffic ?

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

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

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


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

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

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

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 :)

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

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

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

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?

My settings? Which ones?

The 3 settings for the script.

I'm using the default settings. :)

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!

Fantastic hack :)

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.

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!

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:

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

Latest email from 24-hour period:

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.

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 and remove " $loadavg[0]<2 && "

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.