PDA

View Full Version : Optimizing Forumhome


Pages : [1] 2

Xenon
20 Dec 2002, 22:34
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....

NTLDR
20 Dec 2002, 22:37
Thanks for releasing Xenon, I'm going to be sure to test this one :D

Xenon
20 Dec 2002, 22:38
:)
i hope i'll get good results by you NTLDR :)

Dean C
20 Dec 2002, 22:44
Yea i'll test this one out - sounds awesome :P

Chris M
20 Dec 2002, 22:57
Sounds good:)

Will install:)

Satan

JulianD
20 Dec 2002, 23:09
I'm going to test it for my new site! Thanks Xenon!

Xenon
20 Dec 2002, 23:17
i have to thank everyone who test it to bring back some feedback to me :)

if it works as it should i'll create other hack like that (have some ideas :))

@All: my PM-solution worked wrong (damn) the file is updated with the well known pm-query, you should use this instead :)

Lanigironu
20 Dec 2002, 23:47
This is great!

My queries used to be at 48, and now dropped to 38.

I know this is extremely high, so are there any ways to lower it any more?

Boofo
21 Dec 2002, 00:14
Xenon, I did a re-fresh with 21 queries, it jumped to 23 and on another refresh, it went down to 17. I figure it was the cache kicking it. Its there a way to add this:

$totalviews=number_format($countthreads['views']);

to the query you have to make one less query if we are using this?

Also, the code for this:

$numbersmembers=$DB_site->query_first('SELECT COUNT(*) AS users,MAX(userid) AS max FROM user');
$numbermembers=number_format($numbersmembers['users']);

// get total posts
$countposts=$DB_site->query_first('SELECT COUNT(*) AS posts FROM post');
$totalposts=number_format($countposts['posts']);

$countthreads=$DB_site->query_first('SELECT COUNT(*) AS threads FROM thread');

must be a little different in 2.2.9. :)

Xenon
21 Dec 2002, 00:31
Uploaded new version with small bugfix
be sure you also make the table change to have lastupdate in it :)


Boofo: Well, yes it can be changed, if you use the threadtable instead of the forum-table, but the forumtable is faster, and on an unhacked vb229 i've used the optimized version :)

once it's out of the beta i can make it for you or post it here ok?

must be a little different in 2.2.9
hmm, no i've compared it with an unhacked vb229 file, maybe your fle is hacked...

Xenon, I did a re-fresh with 21 queries, it jumped to 23 and on another refresh, it went down to 17. I figure it was the cache kicking it.
Yes there are 3 extra queries compared to an normal forumhome call when the cache is updated, but at least 3 less to a normal one if read out from the cache.
But the additional queries are very fast from what i know compared to the others :)

Boofo
21 Dec 2002, 00:34
I don't want it changed if it can be incorporate it into what you already have. It's just that i don't have any threadviews anymore. :)

I haven't changed anything there (that I can recall). Must be an extra space or something in my file. :)

Xenon
21 Dec 2002, 00:40
of course, because the threadviewpart was a hack wasn't it? :)

instead of this code:$counters=$DB_site->query_first('SELECT SUM(replycount) AS posts, SUM(threadcount) AS threads FROM forum WHERE parentid=-1');
$totalposts=number_format($counters['posts']);
$totalthreads=number_format($counters['threads']);

use this one:
$counters=$DB_site->query_first('SELECT SUM(replycount) AS posts, COUNT(*) AS threads, SUM(views) AS views FROM thread');
$totalposts=number_format($counters['posts']+$counters['threads']);
$totalthreads=number_format($counters['threads']);
$totalviews=number_format($counters['views']);


so it should work and you have your views again :)

Boofo
21 Dec 2002, 00:49
Thank you, sir. And I'm still at 17 queries. You are the Man! :)

I need to ask, though, is it normal to have this hack add 2 queries until the refresh? I mean, it is supposed to do that, right?

By the way, the threadviews was more of a line of code I got from Chen than a hack. :)

Xenon
21 Dec 2002, 01:00
:)

yes it is normal :)
as said above the two extra queries are fast one's
but i can't exactly say the performance power, that's why it's here in beta :)

:)

Boofo
21 Dec 2002, 01:04
Ok, well, I have noticed the page loads a lot quicker and smoother. So, it must be doing something. But, then, my board doesn't have very many users either, so I can't tell on the server load part of it. Although my server load stats in Microstats are showing 0.09, 0.05, 0.09 right now, if that means anything at all. :)

NuclioN
21 Dec 2002, 01:57
Page loads indeed quicker and it reduced the index queries by ten..that's significant! I hope that you can do this for the showthread. ;) Well done Xenon.

Dolby
21 Dec 2002, 03:11
I went from 18queries to 13queries. Very nice.

Aaron1
21 Dec 2002, 05:25
Yes it works faster, but the downside is i just lost TECKS hack: Latest XX Threads on Forumhome Page. Is there a way to work arround it?

http://www.vbulletin.org/forum/showthread.php?s=&threadid=43310

Xenon
21 Dec 2002, 08:44
@Aaron1: i'll look into it when i finished the beta, i'm still working on optimizing :)

@Boofo: Queries which are inside the makeforumbit function will be reduced always, so if someone has installed the show subforums hack, this hack can save an enourmous ammount of queries.

it always depends on how strong your index.php is hacked :)

well it's just the field lastupdate which has been included into the new version, it prevents some bugs...
you don't have to upgrade immediatelly, because im sure i'll change the structure a bit soon ;)

Wahh, sortingorder here is completly.......
good it's just beta :)

Dean C
21 Dec 2002, 08:58
So installing the show sub-forums hack would now only add one query instead of all those queries in the query loop?

- miSt

Xenon
21 Dec 2002, 09:35
well, whenever the cache would be updated all queries of the subforumhack will be there again, but if the script reads out from the cache NO more quereis are added for the subforums :)

Aaron1
21 Dec 2002, 09:39
Originally posted by Xenon
@Aaron1: i'll look into it when i finished the beta, i'm still working on optimizing :)

Well, that works good, because after installing the micro stats, the queries has dropped from 26 to 19! on my forumhome. Now it loads at lightspeed. But the reason could also be that TECK latest XX topics stopped working :)

Btw, a small note: the Latest XX topics Hack, sometimes disapears & appears again. Very strange...

Xenon
21 Dec 2002, 14:52
I see: the lates XX topics will appear everytime the cache is updated.

so you have to cut out the hack lines by Teck and put em after the } // if $cacheupdate line so it should work

Aaron1
21 Dec 2002, 15:01
Great i will try right away! And it works!
Page generated in 1.36290896 seconds (40.29% PHP - 59.71% MySQL) with 20 queries.

Now i notice that Latest XX Threads is just 1 querie, which isn't bad i guess...
Thanks Xenon. Great stuff

Xenon
21 Dec 2002, 15:06
good to hear :)

Dean C
21 Dec 2002, 16:35
Ok so any extra hacks we have installed which add lines which are db queries we need to put after the cache line?

Aaron1
21 Dec 2002, 16:52
Oops i got a database error, that looks like something to do with the cache forum home template...

Here's the beginning of the error message:

Invalid SQL: INSERT INTO cache_forumhome (userid,lastpost,lastupdate,postcount,styleid,
forumbits) VALUES (1251,1040492181,1040492253,5550,0,"</table>
</td></tr></table><br>

Here's the end:
mysql error: Duplicate entry '1251' for key 1
mysql error number: 1062

Hmm, ok i attached the complete message as a txt file...
Hope this helps :)

I also checked in phpmyadmin the cache_forumhome table is about 1 mb large, and it grows. For your interest: i got a pretty busy board (almost 1000 visitors a day)

Aaron1
21 Dec 2002, 17:05
I also checked in phpmyadmin the cache_forumhome table is about 1 mb large, and it grows. For your interest: i got a pretty busy board (almost 1000 visitors a day)

I think i understand: 1251 is a member on the board.
Sorry for this, i am just starting to learn php/mysql a little bit. And i have the trouble of thinking out loud :)

Velocd
21 Dec 2002, 17:06
I recieved the same error message as Aaron1 above also, not sure what the problem is though but I'm guessing as well to do with the cache... :ermm:

Xenon
21 Dec 2002, 17:20
hmm weird this should not happen and logically cant hapen, because all old caches are deleted before the new one is inserted......

maybe it's an mysql problem
i try to solve it thanks for letting me know :)
it seems it just appears when much users are online.....

@mist: every hack which adds queries and is not saved in $forumbits must be below the cahcing if-clause :)

Xenon
21 Dec 2002, 17:28
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
21 Dec 2002, 18:05
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
21 Dec 2002, 18:09
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
21 Dec 2002, 18:13
Hehe, ok thanks! I will give it a try! :)
I will keep you informed...

Aaron1
21 Dec 2002, 18:19
Just for incase of disaster, you can take a look at my phpmyadmin screendump. Let me know it it looks alright :)

Xenon
21 Dec 2002, 18:21
looks as it should now :)

Xenon
21 Dec 2002, 18:38
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
21 Dec 2002, 19:09
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
21 Dec 2002, 19:14
:)
then i'm sure it'll reduce the space used in DB a lot :)

Aaron1
21 Dec 2002, 19:28
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
21 Dec 2002, 19:35
Working well so far Xenon :D

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

Aaron1
21 Dec 2002, 19:39
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:

sCGZBCϤS0~pJ5ey^ѪGf.o^,3
nq|,^j<F3>b!g|/A{ě_?cU|(d=
0V4090VI]Kk)/sh%ҺBQhyiH???@%:
'*'VQ.w\!}G=PS`qxTeF6
k*XoT[<QSRh' ]`bMccbMcL/Z[\ 9 :_:
@fvoZ

:classic:

NTLDR
21 Dec 2002, 19:44
*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
21 Dec 2002, 19:49
@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
21 Dec 2002, 19:58
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"? :)

Boofo
21 Dec 2002, 19:59
Originally posted by 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

Can you share the script, please?

Aaron1
21 Dec 2002, 20:06
Oops :)

Invalid SQL: SELECT COUNT(title) AS starts FROM thread WHERE postusername='d' jam-man' AND open!='10' mysql error: You have an error in your SQL syntax near 'jam-man' AND open!='10'' at line 1

mysql error number: 1064

Date: Saturday 21st of December 2002 09:57:26 PM
Script: http://www.housequake.com/forum/forum/member.php?s=&action=getinfo&userid=1645
Referer: http://www.housequake.com/

Whats that? Weird?

Aaron1
21 Dec 2002, 20:09
Originally posted by Boofo


Can you share the script, please?

No problem its very simple

Open notepad. Paste this inside:

<?php
phpinfo();
?>

Save as: whateveryoulike.php
Upload & voila!

Boofo
21 Dec 2002, 20:11
Ok, that's almost like the one I am using now. Only mine is phpinfo(32). I guess I will have to look closer at it. Thanks! :)

Aaron1
21 Dec 2002, 20:17
@Boofo: you're welcome, i only wished all coding was that simple :)

@Xenon: I don't think my problem is related to year hack, because it has something to do with illegal user names; The quote marks seem non allowed? But why do i get a database error for that... But its the first time i get this error.

----

One more thing, is that extra querie i have (yep i never got that marvelous 20 queries back again) due to that Zlib addition?

Boofo
21 Dec 2002, 21:28
Can someone please check their forumjump and tell me if all of their forums are showing up? Mine is blank on the forums and I need to see if it is this hack or Velocd's Similar threads hack doing it. Thanks! :)

Aaron1
21 Dec 2002, 21:35
I checked your site Boofo, and your forumjump looks good, and seem to have all the forums from your board. I can't check my forum, because i disabled it...

Xenon
21 Dec 2002, 22:13
@boofo:
you have a forumjump on forumhome?
hmm, it's a hack, so you have to look where the creating line is :)
if it's inside of the if-clause you have to take it out from it, same thing as with the latest xx threads....

also the extra threadviews are no problem of my hack boofo, it just reads em out of the DB, so if the ammount is incorect it is saved wrong in thread table..

@Aaron: the extra querie cannot be produced by the zlib inclusion :)
Also your error, has nothing to do with my hack, but i can say whats the problem: somewhere you have $bbuserinfo[username] or something like, but you have to use addslashes($bbuserinfo[username])

Boofo
21 Dec 2002, 22:29
Sorry about that. It turned out it wasn't your hack causing the problem. It is Velocd's Similar Threads Hack causing it. And the totalviews problem is still there after un-installing your hack (just for testing purposes). I will re-install it once I figure out the totalviews problem. Again, sorry for my over-zealousness (the wife told me that word. She said it means "studness". :))

Boofo
21 Dec 2002, 22:39
Originally posted by Aaron1
I checked your site Boofo, and your forumjump looks good, and seem to have all the forums from your board. I can't check my forum, because i disabled it...

Did you check it while viewing a thread or just a forum? It works good in the forums, it's while viewing a thread (posts) that the forum titles are gone from the forumjump drop down listing. :)

nuno
21 Dec 2002, 22:56
Damn, why the hell is my browser redirecting me to Boofo's avatar when i click attachment link? :surprised:

Xenon
21 Dec 2002, 22:59
lool, no idea it works perfect here ;)

Herman
21 Dec 2002, 23:27
It works beautifully!

/me clicks Install

Lanigironu
22 Dec 2002, 00:32
How would I go about optimizing the forum display? Is there any way to optimize thread view?

Aaron1
22 Dec 2002, 09:26
Originally posted by Xenon
somewhere you have $bbuserinfo[username] or something like, but you have to use addslashes($bbuserinfo[username])

OK thanks, but hey, thats looking for a needle in a haystack! :-)
My homesite gave me 52 results on a search pattern! But which one needs the addslashes?

Forumhome works great Xenon! Now the rest of the forum pages :)

Aaron1
22 Dec 2002, 09:27
Originally posted by Boofo


Did you check it while viewing a thread or just a forum? It works good in the forums, it's while viewing a thread (posts) that the forum titles are gone from the forumjump drop down listing. :)

Yes it looked all good! Btw you got a fast webserver. Everything runs smoothly. I wish my site was like that!

Herman
22 Dec 2002, 14:52
Hmm I'll try my own on forumdisplay, it shouldn't be too awful hard....

Parker Clack
22 Dec 2002, 15:19
Xenon:

Great hack. I look forward to seeing what you can do to optimize showthread.

Parker

Thehandyplanet
23 Dec 2002, 07:26
Yes, great Hack!

Dvd
23 Dec 2002, 21:09
You have an error in your SQL syntax near '\'0\',
lastpost int(10) unsigned NOT NULL default \'0\',
lastupdate int(10) un' at line 2


Any ideas?

Xenon
23 Dec 2002, 21:51
@DVD: a bit more information would be nice.....
Where do you get this error, which file, line and so on...

MartynJ
24 Dec 2002, 00:27
Am I missing something? I installed the latest version of the hack, and my index page looks indentical, doesn't load any quicker, and doesn't use any more or any less queries. Also, how do I know if I'm using the cached version, and how do I force a cache update? :confused:

Xenon
24 Dec 2002, 00:48
Well, you won't see any difference, visually ;)
it just reduces the querie ammount if it reads from the cache.

The cache would be updated, whenever a new post is made, that's also how you force an update ;)

so if you've installed the newest version, go to forumhome, you'll have about 19 queries (normally) then just do a reload, without a new post has been made, and the qurey ammount should be about 14.
if you don't have the effect, you have probably made a mistake while installing

Aaron1
24 Dec 2002, 13:38
I have a problem, it could be related to this hack. But i am not sure. After trying to optimize the forumhomepage, i was advised to move 'The Latest XX Threads Hack' after the '} // if $cacheupdate' line. But now the hack is not working properly anymore, it does show, but the problem now is, that it also shows topics from a private forum.

What do you think of this? Is this a cache related problem? Or just the fact because i moved it below the specified line?

Xenon
24 Dec 2002, 14:47
yes, that's a problem. These two hacks don't work together properly. It's because the viewingpermissions are just read out from the db when the cache is updated, but it won't if the script reads from the cache. That is one of the queries which are reduced when reading from cache :)

i don't know the hack of latest XX but maybe, you can add the threadinfo into the cache also, so it should be working :)

as said, i can't code such workarounds for other hacks whil beta-phase but maybe i find a way after the full release :)

nuno
24 Dec 2002, 15:09
By adding a new mod to the forum it will not be displayed on forumhome Xenon :confused:

MartynJ
24 Dec 2002, 15:21
I installed it slowly as per the instructions. Though I noted a faster load, the queries were always at 23 for logged in users, and 16 for logged out users. Is it possible that the template recording the number of queries made is also cached?

Xenon
24 Dec 2002, 15:35
@nuno: Yes you are right, just make a new post that there is a new mod and you'll see next time on forumhome he will be displayed ;)

it just won't display him as long as the script reads out from the cache :)

@MartynJ: Hmm, maybe a bug with a hack you've installed.
The newest version works perfect on all my test boards (hacked and unhacked)
can you create a test account so i can see it on your board ;)

nuno
24 Dec 2002, 16:58
Done and fixed :)

Aaron1
25 Dec 2002, 08:57
Originally posted by Xenon
i don't know the hack of latest XX but maybe, you can add the threadinfo into the cache also, so it should be working :)


Hmm, sounds very simple but how do i do that? :)
Well i guess, there will be more hacks that might have some trouble with your optimize(cache) hack?

Well for now i have uninstalled the latest XX threads hack, because i was forced to choose between speed and functionality. Yes, i have chosen speed, but hey! I got 19 queries now! :)

DrkFusion
25 Dec 2002, 17:03
Seems not to work :(

DrkFusion
25 Dec 2002, 17:05
Sometimes it says 13 queries, and sometimes it sasy 34, sometimes it says 32, and sometimes when i refresh t says 13. Whats wrong?

nuno
25 Dec 2002, 18:12
That's just the cache doing it's job, caching ;)
Oddly enough i get 15 queries for logged out users and 12 for logged in, what's the catch? :confused:

Xenon
25 Dec 2002, 22:10
@Drk: if your qureies change so strong, the hack is working perfectly ;)
as nuno said, that's caching :)

@nuno: hmm, the caching is optimized for logged in users, it should also work for most of the logged out ones, but, i have to optimize that part i think ;)

@Aaron: yes it's easy i think, i'll help you when i've more time so i can look into TECKs hack ;)
I think speed was the better chose ;)

DrkFusion
26 Dec 2002, 01:07
This is great :)
Great job Xenon

jeffj
28 Dec 2002, 12:29
most excellent... I had it up and running on my test site in about 5 minutes...and it worked great!

then I moved index.php to my production site... and ....too bad I forgot to add the cache table to my production site....but that only took about 10 seconds and I beat everyone before they saw the mysql error that I did...

it works great and I love it!

- jeff

Xenon
28 Dec 2002, 21:21
thanks for the kind words :)

i found out it works nearly perfect, just a small thing i had to change before it will be moved into full releases :)

i think it will be there before next year ;)

freeshares1
28 Dec 2002, 22:08
:clicks on install:

It works very well and i have noticed that my forum home loads a lot quicker.

DrkFusion
29 Dec 2002, 04:29
One for forumdispay and showthread would be really nice:)
This is the next best thing to microstats :p

Thanks to Xenon my forumhome:
Page generated in 0.09714496 seconds (75.75% PHP - 24.25% MySQL) with 13 queries.

fury
29 Dec 2002, 04:33
Great hack! It = teh winnar. I now get 0.2-0.3 second forumhome generation times, with 18 queries if cached, 25 if not.

Since I have the persistent mark forums read hack by ethank installed I had to modify part of that in member.php to be able to get forum lights to turn off, but other than that, it looks to be working perfectly.

fury
29 Dec 2002, 04:37
One for showthread would be HUGE :eek:

fury
30 Dec 2002, 00:24
I noticed after installing this hack, total post count is off by 10... there are 111979 post records in the database but the sum of the replycount column is 111989.. there are 10 extra posts somehow (10 is how many forums I have which have posts in them, but I don't know why this would have any effect)

Running the counters again didn't help.

X-Fan
30 Dec 2002, 01:54
Just installed this, and it works great! My forum home page went from around 32 queries down to 19!

I'm wondering if there's any way to add this to vbHome for those (like me) who use that as their site's portal page?

DrkFusion
30 Dec 2002, 03:20
Vote Xenon for vB Dev :-p

This is a great method, I would love this for the all of vb

Xenon
30 Dec 2002, 11:17
*ggg* thx Arunan :)

@X-Fan: When i have more time and completly finished this beta, i will work with TECK on an inclusion into vBhom (lite) :)

@fury: There are two possibilities:
1. your counters are wrong (update your counters, be sure you'll do in the right order (first threads then forums)
2. you have orphan posts which don't appear in any threads...

Xenon
30 Dec 2002, 12:33
Ok, uploaded a little modified file now
Moved the thread to Full Releases

thanks to all who helped me in the betaphase :)

NuclioN
30 Dec 2002, 12:45
i get a gzinflate parse error at the top of the forum index after replacing the old code with the new. (?)

Dean C
30 Dec 2002, 13:45
Awesome i'll try this on my localhost tonite :p

Xenon
30 Dec 2002, 14:43
@NuclioN: How old was your last version?
the last update justs changes a small if-clause nothing with the gzinflate function...

as a tip, just empty the whole forumcache table and it should work again

Dean C
30 Dec 2002, 22:16
Wow this works wonders Xenon..

Reduced my testboard from 19queries to 14queries when dont on a fresh install :)

- miSt

Smoothie
31 Dec 2002, 02:34
How well does this work with hacks that are installed?

Boofo
31 Dec 2002, 02:43
There's some that won't work very well (or at all) with it, that is why I had to un-install it. Great idea, though. :)

Smoothie
31 Dec 2002, 04:01
thats a shame.

Dean C
31 Dec 2002, 10:05
Yep well i'm sure there's a way around it and Xenon will guid you all the way :p

Xenon
31 Dec 2002, 13:53
@Smoothie: it depends on the hack, subforums on forumhome for example works perfect and has a huge effect on reducing queries.

if you are a little experience you can nearly make every hack to work with it.

but it doesn't work with this hack:
# of users browsing on Forumhome

Chris M
31 Dec 2002, 14:15
Hmm...

I can't remember if I have that one installed:(

*damned script kiddies attacking my Hosts server:mad:*

I will test it out on DB when it gets back up:)

Satan

Xenon
31 Dec 2002, 14:24
you'll like it chris :)

Chris M
31 Dec 2002, 14:35
Hehe no doubt;) It will probably reduce my Forumhome queries considerably - At last check, I think I had 24 or 26:)

Satan

Smoothie
31 Dec 2002, 17:09
Originally posted by Xenon
@Smoothie: it depends on the hack, subforums on forumhome for example works perfect and has a huge effect on reducing queries.

if you are a little experience you can nearly make every hack to work with it.

but it doesn't work with this hack:
# of users browsing on Forumhome Don't have that one installed, but I do have a hack for Photo Post installed.

fury
01 Jan 2003, 14:27
Originally posted by Xenon
*ggg* thx Arunan :)

@X-Fan: When i have more time and completly finished this beta, i will work with TECK on an inclusion into vBhom (lite) :)

@fury: There are two possibilities:
1. your counters are wrong (update your counters, be sure you'll do in the right order (first threads then forums)
2. you have orphan posts which don't appear in any threads... My counters were already updated like a billion times. Doing the orphan posts option didn't find anything either. :(

nuno
01 Jan 2003, 17:51
Originally posted by nuno
By adding a new mod to the forum it will not be displayed on forumhome Xenon :confused:

Originally posted by Xenon
@nuno: Yes you are right, just make a new post that there is a new mod and you'll see next time on forumhome he will be displayed ;)
Xenon, it shouldn't work that way IMO.
It would be tons cleaner if it was only updated when a new mod is added.
Current version waits for a new post/new thread for being made and then updates the moderator db table.
Not so useful for busy boards. ;)

Xenon
01 Jan 2003, 18:46
@Smoothie: Just test it out if it works :)

@fury: As i know the orphan post function just finds posts where threadid is 0.
but there can also be some posts where threadid > 0 but the thread they belong to is deleted.
my hack shows not the ammount of posts that are saved in the db, just the ammount of posts which can be viewed by at least the admin. So i'd say my method is better, normally the ammount should be the same, but if you've had a little problem some time ago you can have this difference :)

@nuno: It's an optimization of the loading time of the forumhome.
When you have a busy board the updated way is really enough, because you'll surely have a new post shortly after adding a new moderator.
just on not so busy boards the cache can be too old for you :)
For the hackinstructions i prefer the easier way, but of course you can add one query to the add moderator function to always update the cahce afterwards: DELETE FROM cache_forumhome
As i say it's not needed, you have to modify other files, too, but you can do, without any negative aspects except of little more work ;)

Martin CX
03 Jan 2003, 13:20
It worked fine for some hours, then this:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 4081664 bytes) in .../index.php on line 408

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 45 bytes) in .../admin/db_mysql.php on line 77

Any ideas?

Xenon
03 Jan 2003, 21:36
hmm, you don't have the zlib installed on your host have you?
if you have not, the whole html is saved uncompressed in the db, which will blow the table very fast. especiall when there are much users online.

try to contact your host to install zlib and allow more memory :)

i think that's the problem....

Martin CX
04 Jan 2003, 01:45
Originally posted by Xenon
hmm, you don't have the zlib installed on your host have you?I do! (Or I think I do - I'm obviously no expert. :D) This is what phpinfo tells me:

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

Xenon
04 Jan 2003, 10:50
hmm, then the error seems a bit confusing to me...

maybe you can try this:
instead of$DB_site->query('DELETE FROM cache_forumhome WHERE userid=' . $bbuserinfo[userid] . iif($numbersmembers['lastpost']>(time()-3600*5), ' OR lastpost<'.(time()-3600*5),''));

use smaller values as the timespan:$DB_site->query('DELETE FROM cache_forumhome WHERE userid=' . $bbuserinfo[userid] . iif($numbersmembers['lastpost']>(time()-3600*3), ' OR lastpost<'.(time()-3600*3),''));

nuno
04 Jan 2003, 10:52
heh Xenon, could it be some kind of caching proggie, Martin's problem?

Xenon
04 Jan 2003, 11:04
i really have no idea what the problem could be...
Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 4081664 bytes)
4081664 < 8388608, so it shouldn't be an error....

You mean a caching proggie on the server right?
Well i haven't seen any till now, so i don't know how they work exactly, i wouldn't say this would be the problem, but i can't say for sure :)

nuno
04 Jan 2003, 11:31
Yup, something like php cache, apc used to do that, the memory errors.
Wild guess i think.

Xenon
04 Jan 2003, 16:38
hmm, maybe you're right, i don't know these scripts.
but it could be, the hack is ok....
also if this is the bug with those cachng software, i can't do anything :)

Martin CX
04 Jan 2003, 17:32
Is there some way of finding out if this is the problem?

Xenon
04 Jan 2003, 17:37
hmm, disable the software ;)

Martin CX
04 Jan 2003, 17:42
Originally posted by Xenon
hmm, disable the software ;) But WHAT software? :confused: What software is it that might cause these problems, and how can I check whether it is installed? (Sorry for all my whining ;))

nuno
04 Jan 2003, 18:29
Is it a dedicated or shared server?
FreeBSD, Linux, Windoze?
Upload this code into your forums folder

<?
phpinfo();
?>

Save it as phpinfo.php
You should see something like

This program makes use of the Zend scripting language engine:
Zend Engine v1.2.0, Copyright (c) 1998-2002 Zend Technologies
with Your accelerator software credits go here

Martin CX
04 Jan 2003, 19:18
Nope. It just says This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.1.1, Copyright (c) 1998-2001 Zend TechnologiesAnd it's Linux, BTW.

nuno
04 Jan 2003, 19:54
Try to increase memory_limit value in php.ini.

Martin CX
04 Jan 2003, 20:00
Shouldn't 8 megs be enough?

nuno
04 Jan 2003, 20:19
Originally posted by Martin CX
It worked fine for some hours, then this:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 4081664 bytes) in .../index.php on line 408

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 45 bytes) in .../admin/db_mysql.php on line 77

Any ideas?
Increase it to 16M

pawel
06 Jan 2003, 20:28
Amazing hack! My page loaded very slow at peak times but now that problem is gone.
Thanks,
Paul

Xenon
06 Jan 2003, 20:44
:)
i enjoy to hear that :)

N9ne
19 Jan 2003, 12:23
So in this hack, data is being stored in the DB...by how much is my DB size going to increase?

Xenon
19 Jan 2003, 12:29
it's just stored in the DB temporairly, so not real increase of space is there...

Also it depends if you have zlib or not..

N9ne
19 Jan 2003, 12:41
How can I check if I have Zlib? Phpinfo?

edit: Just checked phpinfo and I have this:

--with-zlib'

So what does zlib do?

Xenon
19 Jan 2003, 13:32
with the zlib you should be able to use the compressing function gzdeflate and so on, which has a strong effect on the temp. db size which is needed by my hack :)

N9ne
19 Jan 2003, 20:29
And what is this about hacks not working with this one? I have a few hacks on forumhome, and one of them is # of users browsing in each forum :(

Erwin
20 Jan 2003, 11:02
I just saw this hack... Nice work, Stefan as always. :)

Tony G
20 Jan 2003, 11:38
I heard about this one, good job too! :D

Xenon
20 Jan 2003, 15:32
thx both of you :)

@N9ne: well you have to decide what is more important to you, some (not needed in my opinion) hacks or performance.
If you have a lot of users online same time, you should decide for performance, but, if you have just a few users, you can use your hacks.
Also most of the hacks can be combined with this hack if you have good experience in hacking, but some cannot like the # online...

N9ne
20 Jan 2003, 16:58
Well the hacks are very important to me/my members...

At peak times I have about 40-50 users online so I should be alright...I was just looking forward to seeing the query count drop 5 :D but unfortunately I cannot sacrifice hacks...

Xenon
20 Jan 2003, 20:28
well, your decicion :)

you can at least install the other querie opts like pm-query and thread ammount thing..

N9ne
20 Jan 2003, 22:09
I already did Xenon ;). PM Query mod I did about 4 months ago, and the thread/post counter I PMed you about and you helped me out on that too...

I have another board though, and it only has one, yes ONE! hack on forumhome, the change style drop down menu, so what do I need to do to get this to work with your mega-query reducer?

Xenon
21 Jan 2003, 14:18
well, the styledropdown hack should work with my hack without any problems as far as i know the hack :)

Schorsch
24 Jan 2003, 23:29
Hi Xenon,

I got a very rare error, it suddenly appears and it suddenly disappears (after pressing F5 in IE6):

Buffer Error (http://www.fetter-esel.com/images/buffer_error.gif)

In line 487 is your piece of code:

if($cacheupdate) {
$forumbits=makeforumbit(intval($forumid), 1, $permissions);
if ($bbuserinfo['userid'] > 0 or ($ourtimenow - $bbuserinfo['lastvisit']) < $cookietimeout) {
// remove old cache from user and those which are older than 5 hours
$DB_site->query('DELETE FROM cache_forumhome WHERE userid=' . $bbuserinfo . iif($numbersmembers['lastpost']>(time()-3600*5), ' OR lastpost<'.(time()-3600*5),''));
$DB_site->query('INSERT INTO cache_forumhome (userid,lastpost,lastupdate,postcount,styleid,forumbits) VALUES (' . $bbuserinfo[userid] . ',' . $numbersmembers[lastpost] . ',' . time() . ',' . $counters[posts] . ',' . $bbuserinfo[styleid] . ',"' . addslashes(gzdeflate($forumbits,9)) . '")');
}
} else {
$forumbits=gzinflate($forumhomecache['forumbits']); // [u]<<<< LINE 487
}

I have 2 members who reported me this error, and 5 minutes ago I got the same error (for the first time) for about 1 minute.

Do you have any advice ?

thanks,
Schorsch

squawell
25 Jan 2003, 04:51
HI Xenon......

when i installed this but i have a problem

i cant logout.....and just only userid=1 cant....why??

so i replace original index.php file and it work.....

so Xenon what cause that??

Xenon
25 Jan 2003, 12:01
@Schorsch: well, it's a problem with your server, maybe the buffer is to small or something like that.

it's nothing i could repair ...

@Squawell: hmm, couldn't be normally the login/out feature are not touched by my hack.
Also it works perfect for all.
maybe you've changed the index on a piece of code where you shouldn't..

collidation with another hack you've installed propably?

squawell
25 Jan 2003, 12:52
Originally posted by Xenon
@Squawell: hmm, couldn't be normally the login/out feature are not touched by my hack.
Also it works perfect for all.
maybe you've changed the index on a piece of code where you shouldn't..

collidation with another hack you've installed propably?
in fact...it not just effect the login function.. when i add or edit the new forum the main page also didnt show up too(the change part).....so strange...it only happen to userid=1 user.....i try another user account its normal and work perfect.....so that make me confuse what cause that??

by the way i dont change anything in index.php i use the original index.php......

Xenon
25 Jan 2003, 13:05
maybe your cookie is corrupted.
i know it can't happen because the part of code is not affected by my hack, at least not if you've done everything correct.

the last thing is normal, whenever you add or edit a forum on forumhome or add a moderator or something like that, it will just change after the cache has been updated, so whenever one new post has been made.

N9ne
25 Jan 2003, 14:03
I had a problem with this hack, when we were editing some templates for forumhome, they weren't updating on forumhome, so you can't see the changes, and when someone could see the changes, someone else couldn't...all in all I couldn't risk any problems and I just uninstalled the caching part of the hack..

Xenon
25 Jan 2003, 17:42
@N9ne: It's not a problem.
all you have to do after editing a template on forumhome is to make a new post and then all caches would be updatet...

you could also edit templates.php to manually delete all cached versions whenever you make a templateedit.

As said, it's not a problem, it's just how caching works without to rewrite hundreds of files ;)

pwr_sneak
02 Feb 2003, 15:50
i just read your instructions to see if this hack would work for my forum.
but this query is much slower then the original ones:
SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers
you replaced three fast queries which reads the values from index by one slow query which crawls through the whole datafile. i did a quick test on my privatemessage table (1.2M messages), your query took 2.2 seconds to complete, the original ones took 0.2 seconds all together

but the rest of this cache looks good :)

Boofo
02 Feb 2003, 16:38
Is there any way to make the one query faster?

pwr_sneak
02 Feb 2003, 17:00
I've updated my last post due to an cut'n'paste error...

Originally posted by Boofo
Is there any way to make the one query faster?
you have to run the original 3 queries to make it faster.
it's a good idea trying to decrease the number of queries, but it won't help anything if you replace some small and fast queries with one big and slow query.

nuno
02 Feb 2003, 17:23
Originally posted by pwr_sneak
I've updated my last post due to an cut'n'paste error...


you have to run the original 3 queries to make it faster.
it's a good idea trying to decrease the number of queries, but it won't help anything if you replace some small and fast queries with one big and slow query.
Read this thread
http://www.vbulletin.org/forum/showthread.php?s=&threadid=38471
Also read this post made by Chen
http://www.vbulletin.org/forum/showthread.php?postid=254638#post254638
Also read Matt's post <- This guy is a db Master BTW. :D
http://www.vbulletin.org/forum/showthread.php?postid=254690#post254690

pwr_sneak
02 Feb 2003, 18:25
Originally posted by nuno

Read this thread
http://www.vbulletin.org/forum/showthread.php?s=&threadid=38471
Also read this post made by Chen
http://www.vbulletin.org/forum/showthread.php?postid=254638#post254638
Also read Matt's post <- This guy is a db Master BTW. :D
http://www.vbulletin.org/forum/showthread.php?postid=254690#post254690
Matt runs Sybase instead of MySQL, you can't compare this.
MySQL optimizes count(*) queries because it keeps an extra counter in its index-files when using MyISAM Tables. The Index should be read from RAM on heavy-used tables. This kind of queries as well as MAX(indexed-column) can be answered without I/O which makes it really fast.

this sum(...,1,0) trick has to read every row from the table or just every entry in its index if its condition matches from index. so this is always slower than reading three single values with three single queries.

nuno
02 Feb 2003, 18:42
Originally posted by pwr_sneak

Matt runs Sybase instead of MySQL, you can't compare this.
MySQL optimizes count(*) queries because it keeps an extra counter in its index-files when using MyISAM Tables. The Index should be read from RAM on heavy-used tables. This kind of queries as well as MAX(indexed-column) can be answered without I/O which makes it really fast.

this sum(...,1,0) trick has to read every row from the table or just every entry in its index if its condition matches from index. so this is always slower than reading three single values with three single queries.

Originally posted by FireFly
I did that Matt. :)

$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");

$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm'];
Well I copied it from somewhere, don't really remember right now. But the problem is the query is slower for users with lots and lots of PM's (like myself), but considering most people don't I thought it would be wiser to just use that one query.
Chen already said one query is slower, however, this only occurs when you have a considerable amount of PM's stored.
So, i think your assumption is not correct.
It is faster for users with few pm's.

pwr_sneak
02 Feb 2003, 19:25
Originally posted by nuno

Chen already said one query is slower, however, this only occurs when you have a considerable amount of PM's stored.
So, i think your assumption is not correct.
It is faster for users with few pm's.
where's the threshold of this considerable amount?
I do like those well scaling solutions more than those with unneccesary performance breaks.

I just wanted to say that it's not always good to run less queries than others do.

Maybe someone could figure out how many PMs are needed to make this one query slower than the original three.
My Database stores 1.3 million PMs and as I said before, the three queries are faster by factor 6 - 8

nuno
02 Feb 2003, 20:07
Originally posted by pwr_sneak

where's the threshold of this considerable amount?
I do like those well scaling solutions more than those with unneccesary performance breaks.

I just wanted to say that it's not always good to run less queries than others do.

Maybe someone could figure out how many PMs are needed to make this one query slower than the original three.
My Database stores 1.3 million PMs and as I said before, the three queries are faster by factor 6 - 8
Exactly. :)
I would like to know this as well.:)

Xenon
02 Feb 2003, 20:11
you are right here, it would be intresing to find out how much pm's are needed to slow down...

in all my tests the one query was faster, but i never had 1,3 million pm's which i think most of the admins won't have....
If you have such a great db and board the optimization is to diasble pm-queries at all...

pwr_sneak
02 Feb 2003, 20:58
Originally posted by Xenon
If you have such a great db and board the optimization is to diasble pm-queries at all...
the 3 pm-queries on index.php run 0.0068 seconds on my servers. no matter how huge the privatemessage table is since mysql doesn't have to read anything from disk or crawl through big B-Trees.

Seby
05 Feb 2003, 15:45
Vieln Dank Xenon!! ;)

Great Hack, now I only have 17 queries on my main page, nice!!

mcyates
06 Feb 2003, 10:55
my queries where 63, they are now 19

Xenon
06 Feb 2003, 18:52
:)
it's a good feeling and also a good motivation to get so much feedback, mainly positiv :)

i'm currently planing v2 of this hack, which will strongly optimize it, i hope it will be finished in two weeks

Buddha
15 Feb 2003, 17:14
33 to 23 querries.

thank you very much.

Seby
17 Feb 2003, 14:06
Xenon, seems that this hack doesnt work with version 2.3.0 :(
I installed it and then the forums homepage doesnt load, please have a look at it, the hack is great and I want to use it on my 2.3.0 board ;)

PS: Cheers again for helpng me Saturday! :D

Xenon
17 Feb 2003, 18:07
You're welcome seby :)

Hmm unfortunately, i don't have much free time, so i think i can't work on a new version of the hack.
Haven't taken a look at 230 yet.....

But i'll try to do what i can :)

ImportPassion
21 Feb 2003, 14:25
well, the post count and thread count is WAY off for me.

optimize hack = 608,313 posts
original vbb = 559,113 (which is the # of rows in that table)

not to mention the thread count is off too.
Think i will keep the count(*) for now, but keep the rest of the optimize hack.

D

Xenon
21 Feb 2003, 15:31
you should update your counters if there is really that difference....

also not moderated posts don't count towards the stats..

Xenon
26 Feb 2003, 00:21
Ok, i have released version 2 of the hack.

I've nearly completely recoded it up from ground, so you do better in completly uninstall version1 and install version2

the new version doesn't use the table cache_forumhome anymore, so you could DROP it from your database.

at least, no single caching is used anymore (except from moderatorlist caching)
The hack is now just an optimization of the forumhome queries.
on more frequented boards this will help much better than the old version.
also the pm query section has been optimized, so it would work much faster on large DB's than the old one..

sHORTYWZ
26 Feb 2003, 00:31
could you post the old instructions for v1 so that I can uninstall? I didn't keep them =(

Boofo
26 Feb 2003, 00:37
Will the users browsing forums hack work now with this new version? :)

Xenon
26 Feb 2003, 00:38
oops, sorry, have overwritten them, too.

maybe someon other has kept them :)

if you have no other hacks installed on index.php you can just use an unhacked index.php and start reinstalling.
just deltet the one table from the db.

Xenon
26 Feb 2003, 00:39
Originally posted by Boofo
Will the users browsing forums hack work now with this new version? :)

yes :)

Boofo
26 Feb 2003, 00:42
/me slam-dunks the install button!

nuno
26 Feb 2003, 01:05
Don't know why, but the mods column is now gone.

sHORTYWZ
26 Feb 2003, 01:10
I have a ton of hacks on my index =(

Anyone have the old install txt lying around? Please! =)

Erwin
26 Feb 2003, 01:17
Originally posted by Xenon
the new version doesn't use the table cache_forumhome anymore, so you could DROP it from your database.


The text file in your first post still refers to this though. It has queries to add the table, and other queries to look it up.

Erwin
26 Feb 2003, 01:19
Ignore that - I had to refresh my browser cache. :)

Looks good. I have already optimized my index.php like that, but by removing the extra queries rather than combining them, so this hack won't help me, but it's fantastic nonetheless.

Dan_UPC
26 Feb 2003, 11:37
I've attached the version 1 install file

Aaron1
26 Feb 2003, 12:12
Damn i got a parse error, in the index.php

Parse error: parse error in /forum/index.php on line 433
(thats the last line with the ?> mark) Did i miss an accolade somewhere or do more people have that problem?

Btw: Any ideas where i should look for?
Accolades?

lifesourcerec
26 Feb 2003, 12:14
I had to do this line by line because of other hacks, but this is amazing. Was as high as 49 queries. Removed a hack which dropped it down to 43. Just installed this hack, now I'm down to 37. :) Forum runs a little smoother.

version2
26 Feb 2003, 14:10
Hmm. Where is the new version?

Aaron1
26 Feb 2003, 14:16
I solved the Parse error, because i seem to have left some old code in there. :) Dunno if things work out faster yet, although my board has an average of a 1000 pageviews a day. So let's hope users will find it a bit faster....

I didn's see any drop of queries though (28)

Xenon
26 Feb 2003, 17:57
@Aaron1: if you install everything correct, it should reduce the queries by 4 to an unhacked, not to version 1 which was different :)

Xenon
26 Feb 2003, 17:58
@boofo: that part hasn't changed from version one, so you can use the code i posted in the thread here :)

nuno
26 Feb 2003, 18:24
Originally posted by nuno
Don't know why, but the mods column is now gone.
bump

Xenon
26 Feb 2003, 18:53
sorry, have overread it nuno.

have you done the last step and edited a forum (save without any changes?)

nuno
26 Feb 2003, 19:09
Yes.
Just go to ACP and save any forum right?
Did that, all mods are gone.
Guess i'll just have to redo the whole install from the start.

Xenon
26 Feb 2003, 22:01
yes, it should work have tested it on two boards right now, and everything works ;)

the jumping point for the mods part is the modified forum.php if you have forgotten just one line your bug could appear :)

just check in the db if the fields are filles ;)

nuno
26 Feb 2003, 22:35
Originally posted by Xenon
yes, it should work have tested it on two boards right now, and everything works ;)

the jumping point for the mods part is the modified forum.php if you have forgotten just one line your bug could appear :)

just check in the db if the fields are filles ;)
Thank you Xenon, that did it. ;)

Seby
26 Feb 2003, 23:35
I have the "user online today" hack installed and that cause not being able to install the hack. even when I remove it I cant do teh find/replace stuff cause my lines are different, I'll be bugging you on icq xenon, maybe you can help me :D

Xenon
26 Feb 2003, 23:47
i haven't time for private requests!

remove the hack and apply my part or just leave that part out..

@nuno: you're welcome :)

Xenon
28 Feb 2003, 13:42
little upgrade.
the pm queries had a bug, just replace the old pm code with the new one :)

Xyphen
02 Mar 2003, 00:17
Did this on vB2.3.0 and worked fine, dropped about 3 queries I belive or maybe more.

My forum is hacked badly -_-.

Result: Page generated in 0.25047302 seconds (53.65% PHP - 46.35% MySQL) with 55 queries.

Result Before: Page generated in 0.25559199 seconds (53.88% PHP - 46.12% MySQL) with 58 queries.

Xenon
02 Mar 2003, 00:53
wow, good server if it's that fast with 55 queries ;)

but i think you should optimize index.php more than just with my hack ;)

erdem
03 Mar 2003, 14:34
hi xenon ...

i just want to remove moderator querys from index ...
i have to do these
remove:
$imodcache = array();
$mod = array();
$forummoderators=$DB_site->query('SELECT user.userid,user.username,moderator.forumid
FROM moderator
LEFT JOIN user
ON (moderator.userid=user.userid)
ORDER BY user.username');
while ($moderator=$DB_site->fetch_array($forummoderators)) {
$imodcache["$moderator[forumid]"][] = $moderator;
$mod["$moderator[userid]"] = 1;
}
$DB_site->free_result($forummoderators);
unset($moderator); find:
$listexploded=explode(",", $forum['parentlist']);
while ( list($mkey1,$mval1)=each($listexploded) ) {
if ( !isset($imodcache["$mval1"]) ) {
continue;
}
reset($imodcache["$mval1"]);
while ( list($mkey2,$moderator)=each($imodcache["$mval1"]) ) {
if ( !isset($forum['moderators']) ) {
eval("\$forum['moderators'] = \"".gettemplate('forumhome_moderator')."\";");
} else {
eval("\$forum['moderators'] .= \", ".gettemplate('forumhome_moderator')."\";");
}
}
}

if ( !isset($forum['moderators']) ) {
$forum['moderators'] = '&nbsp;';
}

replace with:
eval("\$forum[moderators] = \"".addslashes($forum[moderators])."\";");
right ?

also this wont cause any problem right ? like update problem or so ?
Thanks

Xenon
03 Mar 2003, 17:01
yes right

it won't hurt anymore then :)
you shouldn't have any upgrade problems, AFAIK Erwin has removed that col long time ago already and didn't get in trouble..

nevertheless if you just remove the mod query and don't install the mod thing into forum.php (of my file) and also don't improve the whos' online on forumhome queries with my hacking advices, mods wouldn't be highlighted anymore (just supermods and admins would..)

erdem
03 Mar 2003, 17:24
is that means after removing moderator querys from index.php , i have to apply other querys to other files ?

a little confused ...

;)

Xenon
03 Mar 2003, 21:16
no, no other files are affected :)

hmm, maybe i've posted a bit complicated.

There is the online now feature on forumhome right?
and if you have enabled the moderatorhighlightin, all your online mods show up bold.
if you remove the mod query above and do not change the part which generates the current online users, all normal mods wouldn't be bold anymore :)

Xyphen
03 Mar 2003, 21:44
Originally posted by Xenon
wow, good server if it's that fast with 55 queries ;)

but i think you should optimize index.php more than just with my hack ;)

Now the result is 'Page generated in 0.26656604 seconds (43.08% PHP - 56.92% MySQL) with 50 queries.' Since I turned off the vBportal left colum display.

One question, how can I optimize it even more? :knockedout:

Xenon
03 Mar 2003, 21:49
well i don't know without knowing your index.php ;)

you can look into my way to combine some queries and try if you can do so yourself on other queries :)

Xyphen
04 Mar 2003, 20:09
Thanks for the tip, but I am not quite good at php as you are.

Xenon
04 Mar 2003, 20:20
well, also i started from a not so experienced level ;)
with time you'll learn how to do these things :)

i'd help you, but i really don't have any time left these days.

VeoMorphine
04 Mar 2003, 20:47
whoa..... Dramatic change for me. Very Very Very good hack indeed.

Roderik
04 Mar 2003, 23:23
Before:
Page generated in 0.50040603 seconds (27.94% PHP - 72.06% MySQL) with 27 queries.
[Debug Mode OFF] [PHP v4.1.2] [GZIP enabled - level 9] [Server Load: 0.08 0.17 : 0.22]

After:
Page generated in 0.51396000 seconds (26.39% PHP - 73.61% MySQL) with 23 queries.
[Debug Mode OFF] [PHP v4.1.2] [GZIP enabled - level 9] [Server Load: 0.22 0.13 : 0.16]

a full 4 queries! a great improvement for a few changed lines!
MORE MORE! :D

Xenon
05 Mar 2003, 07:53
thx, i do what i can to find some more ;)

btw. you should reduce your gzip level from 9 to 1.
the 9 isn't so much better in compressing, but the load of a server increases.

Kars10
21 Mar 2003, 17:02
First of all this hack works great for me!!
Thanks Xenon.
But i have a problem with my $forum[moderators]. This variable is not working for me anymore.

I have deleted this code:

$imodcache = array();
$mod = array();
$forummoderators=$DB_site->query('SELECT user.userid,user.username,moderator.forumid
FROM moderator
LEFT JOIN user
ON (moderator.userid=user.userid)
ORDER BY user.username');
while ($moderator=$DB_site->fetch_array($forummoderators)) {
$imodcache["$moderator[forumid]"][] = $moderator;
$mod["$moderator[userid]"] = 1;
}
$DB_site->free_result($forummoderators);
unset($moderator);

When i put the moderator-code back in my index.php i have my moderators showing up, but all with a >> , << comma bevor there names (examble: , xxx, xxx).
What can i do to make the moderators work.

Thanks in advance.

Xenon
21 Mar 2003, 17:05
have you run the last step and modified a forum from your acp to initialise the Mod cache?

Kars10
21 Mar 2003, 17:33
Yes i have, and my mods are cached in the DB :)

Xenon
21 Mar 2003, 17:37
then they should also be displayed!

the var is filled, that's why all other mods are added with a , in front to it.

maybe you have an disturbing hack installed which doesn't like mine ^^
i know your complicated index.php :p

Kars10
21 Mar 2003, 17:42
No way buddy...my index works!! ;)
But what could it be that this variable dont work anymore??
I promise, when i put the orginal mod-code back in the file, it works like a charm (but than i get that wierd comma)... :(

Xenon
21 Mar 2003, 17:56
well i know the hack works, and as it used the variable correct, it should show up :)

so it must be your index.php, and no i don't have time to take a closer look at your file ;)

AlexanderT
22 Mar 2003, 16:46
I don't really know, but most of you guys only speak of reducing queries, but not total page generation time (see here: http://www.vbulletin.org/forum/showthread.php?postid=361243#post361243)

I installed the hack and got the following timings:

WITHOUT Hack:
Page generated in 0.12671995 seconds (70.49% PHP - 29.51% MySQL) with 30 queries.
[Debug Mode OFF] [PHP v4.3.1] [GZIP enabled - level 1] [Server Load: 0.33 0.55 : 0.87]


WITH Hack:
Page generated in 0.23996496 seconds (37.26% PHP - 62.74% MySQL) with 26 queries.
[Debug Mode OFF] [PHP v4.3.1] [GZIP enabled - level 1] [Server Load: 0.33 0.55 : 0.87]


... So yes, queries were reduced by a lot, but total page generation took almost twice as long. So I assume the hack doesn't really do it for me.

Xenon
23 Mar 2003, 17:30
i hope you have run those test a bit more often than just once did ya?

i've tested it several times and the gen time was in average 80% of the old gen time.

of course if you just look on the time once it could happen that the time differences so much.

AlexanderT
23 Mar 2003, 17:41
yup I did... one entire evening testing. Query time was always below non-hack, but final page generation was above. I think the replacement of the pm query routine took most additional time.

Xenon
23 Mar 2003, 18:01
hmm, could i have some stats of you board?
post ammount, thread ammount, total pm ammount(and the ammount of pm's you have had in your account while testing)...

especially the pm ammount would be important, in version1 i know it was slower when there are a lot of pm's of a user that for this user the generation was slower, but for the average users who doesn't have much pm's it was faster.

normally all other queries should be faster than the original ones.

VampireMan
29 Mar 2003, 00:46
Worked like dream , reduced over 4 querys on my system.

My system = vb 2.3.0 + vbportal + a few homepage mods

Page generated in 0.19411397 seconds (70.19% PHP - 29.81% MySQL) with 40 queries.
[Debug Mode OFF] [PHP v4.3.1] [GZIP enabled - level 1] [Server Load: 0.07 0.06 : 0.01]

The_Wanderer
18 Apr 2003, 00:34
very nice... this is a MUST for me, seeing how i have 37 queries on forumhome :o

Doubledoom
21 Apr 2003, 13:04
I also have that comma before the mods on the index page. Not sure how or why its there but it isnt before the hack is installed. I havent done any investigations yet as to why but i will be checking against conflicts with other hacks and will post back.

iwearnosox
21 Apr 2003, 13:27
Went from 29 to 25 queries, loads noticably faster too.

Thanks!

iwearnosox
21 Apr 2003, 15:43
My bad.

Xenon
21 Apr 2003, 15:51
maybe just your cookie was corrupted.

my hack doesn't change anything which belongs to login procedure.

iwearnosox
21 Apr 2003, 21:34
Today at 03:51 PM Xenon said this in Post #216 (http://www.vbulletin.org/forum/showthread.php?postid=385152#post385152)
maybe just your cookie was corrupted.

my hack doesn't change anything which belongs to login procedure.
I stand corrected, it is not this hack. I also added a seemingly harmless chat room hack which, as it turns out, is the culprit. It is pooping a cookie out on it's own that interferes with the vbulletin cookie.

Thanks for your effort on this hack again, sorry about that. Feel free to delete these last three if it will help from a confusion perspective...

drumsy
23 Apr 2003, 18:07
How do I undo this query:

ALTER TABLE `forum` ADD `moderators` MEDIUMTEXT NOT NULL

iwearnosox
23 Apr 2003, 23:46
Today at 06:07 PM drumsy said this in Post #218 (http://www.vbulletin.org/forum/showthread.php?postid=386004#post386004)
How do I undo this query:

ALTER TABLE `forum` ADD `moderators` MEDIUMTEXT NOT NULL Just drop it.

drumsy
24 Apr 2003, 01:29
Today at 06:46 PM iwearnosox said this in Post #219 (http://www.vbulletin.org/forum/showthread.php?postid=386153#post386153)
Just drop it.


ALTER TABLE `forum` DROP `moderators`?

solent
24 Apr 2003, 13:40
perfect thanks for that. will let you know once my board is in production.

iwearnosox
24 Apr 2003, 14:01
Today at 01:40 PM solent said this in Post #221 (http://www.vbulletin.org/forum/showthread.php?postid=386413#post386413)
perfect thanks for that. will let you know once my board is in production.
solent if you're unfamiliar with basic mysql syntax you might want to see if your host has phpmyadmin, it's got a basic gui front end that might help you.

solent
24 Apr 2003, 14:05
Today at 04:01 PM iwearnosox said this in Post #222 (http://www.vbulletin.org/forum/showthread.php?postid=386425#post386425)
solent if you're unfamiliar with basic mysql syntax you might want to see if your host has phpmyadmin, it's got a basic gui front end that might help you.


well i have no host i build and maintane my own server i am trying my best and upto now i had no problems. anyway this hack has be installed and works just fine i am at the vbmicrostats hack now and hopefully i will set that up to.

Neopiper
29 Apr 2003, 18:22
thx a lot xenon, i got now Page generated in 0.1170980 seconds (17.90% PHP - 82.10% MySQL) with 17 queries.

http://www.animeonline.net/forums/

mute
01 May 2003, 13:21
Page generated in 0.47 seconds (16.79% PHP - 83.21%MySQL) with 11 queries.

Yay! :)

forumdude
18 May 2003, 03:20
When users don't have any new pms since their last visit this is what the Private Message box says:

You have new message(s) since your last visit.

where it should be:

You have 0 new message(s) since your last visit.


Anyone else notice this or have the same problem?


Also, it lists fine if there are 1 or more new pms.

Xenon
18 May 2003, 10:37
that problem normally can't occur if you have this lines in your code:
$unreadpm['messages'] = intval($unreadpm['messages']);
$newpm['messages'] = intval($newpm['messages']);


edit: oops, the last line wasn't in the uploaded file, sorry ;)

just add it to your code and it'll work

forumdude
18 May 2003, 16:07
Thank you so much.

:)

DeeperImage
22 May 2003, 22:14
I have installed it and almost imediately the server loads dropped down to half. Is there a way for me to check how many queries are being requested by typing in some kind of command from phpmyadmin? I dont have the GZIP, Queries hack installed yet. Thanks.

xs1
23 May 2003, 07:06
anyone tested in 2.3.0 ?

Xenon
23 May 2003, 11:52
@Deeper: you should install teck's microstatshack :)

@xs1: yes, it's used here on vb.org :)

Courage
23 May 2003, 15:57
I use this on 2.3.0 is a very good hack, but i still ahve a big problem. I need to optimize showtheread page. It has to many querries

Any ideea ?

Xenon
23 May 2003, 16:54
*gg*
yeah, rewrite the whole file ^^


i didn't found a easy way to optimize showthread.php without rewriting it a lot...

Courage
23 May 2003, 16:57
this is bad. I looked to my online visitors behavior.
I have very few on Forum Home...

Y2KevSE
28 May 2003, 19:24
Works nicely on 2.3.0! https://www.vbulletin.org/forum/external/2003/05/1.gif https://www.vbulletin.org/forum/external/2003/05/2.gif

Alien
29 May 2003, 00:17
I have this hack installed, now since I don't show the moderator column on forum home and removed it's display from the templates, is there some code here that I can get rid of to reduce a query? :D

-Jason

Erwin
29 May 2003, 08:17
Yes - just edit index.php and comment out or remove the query that searches for the moderators. That will save you 1 query too. :)

Xenon
29 May 2003, 10:55
Erwin, he has my hack installed!

@Alien: nope, when you have installed my hack the query for the mods is gone already and a cache is used, so you cannot save anything more with removing the mod col

Erwin
29 May 2003, 12:37
Oops, sorry! :) I don't know anything about your hack, Xenon. LOL!

Xenon
29 May 2003, 13:31
you should look into vb.org index.php to see most of my hack ;)

Alien
09 Aug 2003, 18:48
Definatly one of the best things you can do for your site, this deserves a major bumpity-bump after 3 months! :D

Xenon
10 Aug 2003, 16:25
arrigato ;)

Danielo
11 Aug 2003, 11:22
let's try for it....
i hope my actual db query can be lesser :(

julius
21 Aug 2003, 20:25
Installed.
Before 20 queries, now 16 queries.

Thanks Xenon.

StrykerContact
08 Sep 2003, 05:08
I get this error when I edit a forum and save it (not editing anything)

Database error in vBulletin Control Panel 2.3.2:

Invalid SQL: UPDATE forum SET moderators='&nbsp;'
mysql error: Unknown column 'moderators' in 'field list'

mysql error number: 1054

==============================================
Help please, I don't think I made an error I did this twice and nothing changed, mayby it's somthing I had installed already that messed up a line of code.

Edit: I fixed it, I don't know why but the step:

find:
$DB_site->query("DELETE FROM forumpermission WHERE forumid=$forumid AND ((usergroupid>=1 AND usergroupid<=4) OR usergroupid>6)");
}
}

updateparentlists();


change to:
$DB_site->query("DELETE FROM forumpermission WHERE forumid=$forumid AND ((usergroupid>=1 AND usergroupid<=4) OR usergroupid>6)");
}
}

updateparentlists();
updatemoderators();

Causes a Mysql error, all I had to do was remove 'updatemoderators();' from under updateparentlists();

It could just be me but I dunno. Great hack.

Xenon
08 Sep 2003, 17:30
easy answer is you have installed the hack incorrectly.

you have to add a column moderators at forum table.
it's the first step you have to do, described in install file

StrykerContact
09 Sep 2003, 22:47
Wow I'm sorry for missing that, I have never missed a step in a hack before without realising it later. Mayby I just missed it because I started right where I saw open index.php thinking everything above it wasn't part of the hacking. Thanks for pointing that out. You don't need to take my suggestion, but organise your instructions a little more, put some indicators and make them easy to identify like:

########################################
Start Install of Hack
########################################

=========================
RUN QUERY
=========================
ALTER TABLE `forum` ADD `moderators` MEDIUMTEXT NOT NULL

=========================
Open Index.php
=========================
Etc etc. Your a much more experienced hacker then me so I'm not going to tell you to do anything, but some people are as dumb as me and screw up like that. Thanks for the great hack.

Xenon
09 Sep 2003, 22:52
:)

i've already planned to organize my hack instructions more when i start vb3 hack releases ;)

Blizzb
12 Sep 2003, 01:40
You make the best hacks Xenon :)

Xenon
12 Sep 2003, 01:50
:)

thank you very much :)