vb.org Archive - Optimizing Forumhome

Page 1 of 7

Last »

Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vBulletin 2.x Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=4)

- - Optimizing Forumhome (https://vborg.vbsupport.ru/showthread.php?t=46848)

Xenon

12-19-2002 10:00 PM

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

NTLDR

12-20-2002 09:37 PM

Thanks for releasing Xenon, I'm going to be sure to test this one :D

Xenon

12-20-2002 09:38 PM

:)
i hope i'll get good results by you NTLDR :)

Dean C

12-20-2002 09:44 PM

Yea i'll test this one out - sounds awesome :P

Chris M

12-20-2002 09:57 PM

Sounds good:)

Will install:)

Satan

JulianD

12-20-2002 10:09 PM

I'm going to test it for my new site! Thanks Xenon!

Xenon

12-20-2002 10:17 PM

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

12-20-2002 10:47 PM

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

12-20-2002 11:14 PM

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:

Code:

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

Code:

$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

12-20-2002 11:31 PM

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?

Quote:

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

Quote:

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

12-20-2002 11:34 PM

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

12-20-2002 11:40 PM

of course, because the threadviewpart was a hack wasn't it? :)

instead of this code:

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

PHP Code:


		
			
$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

12-20-2002 11:49 PM

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

12-21-2002 12:00 AM

:)

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

12-21-2002 12:04 AM

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

12-21-2002 12:57 AM

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

12-21-2002 02:11 AM

I went from 18queries to 13queries. Very nice.

Aaron1

12-21-2002 04:25 AM

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?

https://vborg.vbsupport.ru/showthrea...threadid=43310

Xenon

12-21-2002 07:44 AM

@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

12-21-2002 07:58 AM

So installing the show sub-forums hack would now only add one query instead of all those queries in the query loop?

- miSt

Xenon

12-21-2002 08:35 AM

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

12-21-2002 08:39 AM

Quote:

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

12-21-2002 01:52 PM

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

12-21-2002 02:01 PM

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

12-21-2002 02:06 PM

good to hear :)

Dean C

12-21-2002 03:35 PM

Ok so any extra hacks we have installed which add lines which are db queries we need to put after the cache line?

Aaron1

12-21-2002 03:52 PM

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

12-21-2002 04:05 PM

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

12-21-2002 04:06 PM

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

12-21-2002 04:20 PM

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

12-21-2002 04:28 PM

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

12-21-2002 05:05 PM

Quote:

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

12-21-2002 05:09 PM

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

12-21-2002 05:13 PM

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

Aaron1

12-21-2002 05:19 PM

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

Xenon

12-21-2002 05:21 PM

looks as it should now :)

Xenon

12-21-2002 05:38 PM

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

12-21-2002 06:09 PM

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

12-21-2002 06:14 PM

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

Aaron1

12-21-2002 06:28 PM

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.

All times are GMT. The time now is 06:44 AM.

Page 1 of 7

Last »

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01436 seconds Memory Usage 1,820KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (2)bbcode_php_printable (4)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (40)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: