Optimizing Forumhome

Ok, what this Hack does is it reduces the ammount of queries on forumhome by caching the moderatorlist and by optimizing most of the queries used on index.php


I'd really appreciate getting feedback how it reduces serverload on greater boards

On an unhacked vb index.php (pm enabled accesmask disabled) it reduces the ammount of queries from 19 to 15
my experiments have shown, that the queries are faster than the normal ones, too....

[Xenon]

go to phpmyadmin drop the index from the table and then run this one:
ALTER TABLE `cache_forumhome` ADD INDEX ( `userid` )

@Aaron: yes, the table will grow depending on your ammount of online users at the same time, but it should change dynamically, so it would also decrease after 5 hours again (you can try to change this value in the hack if you want )

[Aaron1]

Originally posted by Xenon
go to phpmyadmin drop the index from the table and then run this one:
ALTER TABLE `cache_forumhome` ADD INDEX ( `userid` )

I am not sure if this is addressed to all of us, therefore: is this something we need to do?
Ps, i have no experience in dropping indexes, but dropping tables i have done before! Is that the same thing perhaps?

[Xenon]

it was for all of you

i think the index makes the problem you posted before

no, droping indexes isn't the same as droping the table

but you can drop the whol table and recreate it again with the new install file, then it should work also

[Aaron1]

Hehe, ok thanks! I will give it a try!
I will keep you informed...

[Aaron1]

Just for incase of disaster, you can take a look at my phpmyadmin screendump. Let me know it it looks alright

[Xenon]

looks as it should now

[Xenon]

oh, sadistically as i am i've uploaded another new version now *gg*

it's just a small cahnge in the caching php-code but it can save a lot of db-space if you have instelled the zlib on your php-module

if you install the new version be sure your empty the table before, if not the old format will be coruppted till the next cache update

[Aaron1]

I just checked with a simple phpinfo script:

ZLib Support enabled
'zlib:' fopen wrapper enabled
Compiled Version 1.1.3
Linked Version 1.1.3

I seem to have it installed
Thanks

[Xenon]

then i'm sure it'll reduce the space used in DB a lot

[Aaron1]

Installed! Looks ok by me, but i seem to use 1 querie more somehow. But that could 'i hope' fluctuate...

Page loaded in 1.86 seconds - 28.95% Php - 71.05% MySql with 21 queries.

[NTLDR]

Working well so far Xenon

With Cache:
Page generated in 0.400 seconds (21.03% PHP - 78.97% MySQL) with 19 queries.

Updating Cache:
Page generated in 0.536 seconds (31.74% PHP - 68.26% MySQL) with 25 queries.

Seems to be loading well all round IMO

[Aaron1]

A little bit off-topic, but its funny to see when your looking at the forumbits from cache_forumhome table before and after a Zlib compression

Before:

<table bgcolor="#0A293E" cellpadding="3" cellspacing="1" border="0" width="100%">
<tr align="center">
<td bgcolor="#1D6AA0"><smallfont> </smallfont></td>
<td bgcolor="#1D6AA0" width="80%" align="left"><smallfont color="#EEEEFF"><b>Forum</b>
</smallfont></td><td bgcolor="#1D6AA0"><smallfont color="#EEEEFF"><b>Posts</b>
</smallfont></td><td bgcolor="#1D6AA0" width="160" nowrap><smallfont color="#EEEEFF">
<b>Last Post</b></smallfont></td>
<td bgcolor="#1D6AA0" width="20%"><smallfont color="#EEEEFF"><b>New Post</b>
</smallfont></td>

Turning into something like this:
After:

sCãGZBçC֣ϤS0~p¼êJ5eµÃy¯£^üËÆ ñѪØGfÃ.oËÉ^,ÿ3ÊÈ
nð±¬ñqÏñ±|ôø¸,^j„<„¬äFÈ3>b!ëg÷|ò ¯¥¦/A¯œ{”Ä›_?–ëæ‘àc¥¶U|¬(d®=Þ
0V40î90VŽ—„I]K¡k)î/–þsh%ÐõÒºŽB×Qhª¿yªïä¦úiHºŽ°??� �?ãì¹@áø%ú:÷„ðâ‘
Í'Â*²–øç'úVµQÞ.Ñw”û½—\êŸ!©ÝÐ}G� �ý=§ûÎÑÓý¹P©S`‡ƒ‹µµqñôâxÍì×� �TŠe¤‡F6
Ÿü²k‰æ*˜XoT¶[<QS®Rh' µ]`bMcâžcbMcâL˜ÔÙ/ýZ’ýº[\ ¸9 :ñ¥_:ñ¥ þ
ƾû£ï@êfvoÖZ

:classic:

[NTLDR]

*hehe* ^^ I agree Its a pain in phpMyAdmin as it causes and error if you click on the edit or delete link. However this is a small price to pay IMO if the compression saves a worthwhile amount of space.

[Xenon]

@Aaron: 1 querie fluctuations are normal, whenever you login into your board after the cookietimeout of your last visit

@boofo: well that one can't work with the hack, because you'd combine dynamical content with cached content.....
to get it back on working correctly you'll lose the cacheing features of my hack

@NTDLR: ahh, good to hear

@Aaron: of course, the first version saves the forumbits in plain html into the db, the new one uses a compression function before savein into db

[Boofo]

Decisions...decisions...ok, I can forgo that hack for now (at least until I can figure a way around it). How about the extra threadviews problem, "buddy"?