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.

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