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.

[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...&postid=300688

[dwh]

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

[Raz]

Originally Posted by dwh

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.