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]

Today at 06:35 AM scotty said this in Post #80
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
[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.

Originally Posted by v0id

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.