vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vBulletin 2.x Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=4)
-   -   Thread view optimizations (https://vborg.vbsupport.ru/showthread.php?t=55715)

Zzed 07-28-2003 10:00 PM
Thread view optimizations
 
This is a little hack that I did to improve the speed at which threads are
loading in my forums. I am experiencing very high server loads due to the
limited hardware on the server and due to the high post counts in my forums.
This hack has tremendously sped up showthread.php.

In order to view an entire thread, showthread.php makes 3 queries into the
post table. I have managed to divert one of these queries to the thread table,
and have managed to combine the remaining 2 queries into a single query.

The thread table is usually 10%-15% of the post table in terms of count, and
it is about 5% of the post table in terms of its physical size. Using the
thread table for one of the queries will definitely improve things.

This hack requires no template or table changes. It is just 3 simple code
changes made to the source code.

Hobbes 07-29-2003 08:57 PM
looks nice :D will instlal

[high]* Hobbes clicks instlal when he finishes[/high]

Velocd 07-29-2003 09:25 PM
How about some benchmark statistics, by curiousity? ;)

What was the loading time before, and what is it now?
(that is, if you have Teck's microstats installed)

mx3 07-29-2003 09:31 PM
I'll install this...speed is good. :)

*mx3 clicks install

jdmuniverse 07-29-2003 09:31 PM
I'd like to see some stats too :)

mx3 07-29-2003 09:38 PM
Loading a bit faster for me :)

Pikok 07-30-2003 12:09 AM
Quote:
Yesterday at 11:25 PM Velocd said this in Post #3
How about some benchmark statistics, by curiousity? ;)

What was the loading time before, and what is it now?
(that is, if you have Teck's microstats installed)
Before:
  • Page generated in 1.02129197 seconds (86.35% PHP - 13.65% MySQL) with 28 queries. [GZIP enabled - level 1]
After:
  • Page generated in 1.00562406 seconds (86.31% PHP - 13.69% MySQL) with 27 queries. [GZIP enabled - level 1]
I would think that the savings would be more noticeable for larger threads with numerous replies. The above stats are for a thread with only 2 replies (3 posts total). I think the larger the thread, the more noticeable the increase will be. But, even with a small thread I noticed an increase in speed. It does work! ;) Thanks Zzed!

Perhaps someone with somewhat larger threads could post some stats..

sonic3d 07-30-2003 01:06 AM
Page generated in 0.61381495 seconds (81.33% PHP - 18.67% MySQL) with 25 queries.
Page generated in 0.41559005 seconds (80.16% PHP - 19.84% MySQL) with 24 queries.

it loads fast i suppose.

l8er
sonic

Oblivion Knight 07-30-2003 02:28 AM
Always nice to drop a query when installing a mod instead of gain one..

Nice work :)

Dolby 07-30-2003 05:36 AM
So far its working nice. Why didnt vBulletin do this? Is there some sort of draw back?

Darkwaltz 07-30-2003 06:46 AM
Nice, easy to install and speeds up my pages a lot

*install*

MrNase 07-30-2003 06:59 AM
:bunny: wow! loads a lot faster, thx :)

clicking install....

pwr_sneak 07-30-2003 10:04 AM
Quote:
Today at 08:36 AM Dolby said this in Post #10
So far its working nice. Why didnt vBulletin do this? Is there some sort of draw back?
they didn't implement this because it would not work for forums which moderate new posts.
but you could use $forum['moderatenew'] to decide which version you wanted to use.

if ($forum['moderatenew']) {
//use old queries
}
else {
//use faster queries
}

ed2k_2 07-30-2003 02:19 PM
Installed without any problem. Threads load more faster!
Thanks Zzed!

Dean C 07-30-2003 03:21 PM
Same here :)!

corsacrazy 07-30-2003 03:37 PM
Quote:
Today at 05:21 PM Mist said this in Post #15
Same here :)!

where to ? :ermm:

Dean C 07-30-2003 04:19 PM
My forums are private :)

oldgamesnu 07-30-2003 07:48 PM
Before: in 0.24841595
After: 0.20439696

fury 08-03-2003 04:18 PM
Quote:
07-30-03 at 07:04 AM pwr_sneak said this in Post #13
they didn't implement this because it would not work for forums which moderate new posts.
but you could use $forum['moderatenew'] to decide which version you wanted to use.

if ($forum['moderatenew']) {
//use old queries
}
else {
//use faster queries
}

Why not just change this line...

Code:
$postids.= "post.threadid='$threadid'";
to this:

Code:
$postids.= "post.threadid='$threadid' AND post.visible='1'";
It might slow it down a tiny bit more but it works with moderated forums...

fury 08-03-2003 04:26 PM
BTW, this (after you've applied the mod)
Code:
$postscount=$DB_site->query_first("SELECT replycount AS posts FROM thread WHERE threadid='$threadid'");
$postscount[posts]++;
$totalposts=$postscount[posts];
can be replaced, to save yet another query, with this:
Code:
$totalposts = $thread['replycount'] + 1;
since the thread info is already fetched earlier in the file.

pwr_sneak 08-03-2003 04:44 PM
Quote:
Today at 07:18 PM fury said this in Post #19
Why not just change this line...

Code:
$postids.= "post.threadid='$threadid'";
to this:

Code:
$postids.= "post.threadid='$threadid' AND post.visible='1'";
It might slow it down a tiny bit more but it works with moderated forums...
for moderated forums you'll need the other query ($postscount = $DB_site->query_first("SELECT COUNT(*)...) too, to get the right pagecount and page navigation.
this hack speeds thread-display because it will not have to crawl the whole post-table to check the "visible" column, it just uses the indexed "thread" column which is alot faster. this is not "a tiny bit" this is MUCH faster on big forums whith huge threads.
I've installed it on my 2 million post forum and it runs like a charm now.

fury 08-03-2003 06:29 PM
Unfortunately I don't have a forum with 2 million posts to check it on. http://smilies.xibase.com/shrug.gif Why not index the visible column then? Since you have a multi-GB post table, what's an extra few dozen megs gonna hurt?

As for the post count, $thread['replycount'] is not incremented for posts that are placed in the moderation queue. it is only updated when they are taken out of the queue. That's the whole point of the reply count field, if there were more replies showing in the count on forumdisplay than were visible in the thread, people would start to wonder.

cirisme 08-03-2003 08:21 PM
I like this hack and it definately improved the speed, but we have a moderated users hack and this causes those posts to show up anyway, so I had to uninstall it.

Definately a great concept though :)

romeshomey 08-03-2003 09:14 PM
Nice.. Thanks

Alien 08-04-2003 03:24 PM
fury's suggestion for reducing yet another query, has this been independently confirmed as well before I play with it. :D

Oblivion Knight 08-04-2003 05:47 PM
Quote:
Today at 05:24 PM Alien said this in Post #25
fury's suggestion for reducing yet another query, has this been independently confirmed as well before I play with it. :D
I haven't tried it yet.. Waiting for confirmation, the same as you ;)

Alien 08-04-2003 11:54 PM
Can this optimization work with this hack below?

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

These lines (that you ask me to replace) are different in my showthread.php:

PHP Code:
$postscount=$DB_site->query_first("SELECT COUNT(*) AS posts FROM post WHERE post.threadid='$threadid' AND post.visible=1$attachment_clause"); 
&

PHP Code:
$getpostids=$DB_site->query("
    SELECT post.postid FROM post
    WHERE post.threadid='$threadid' AND post.visible=1$attachment_clause
    ORDER BY dateline $postorder LIMIT ".($limitlower-1).",$perpage
"); 
How would I make this compatible? :D

sabret00the 08-10-2003 02:31 PM
great hack, noticed it straight away

Alien 08-10-2003 04:12 PM
Any solution on my question above anyone? :)

David Bott 08-13-2003 04:17 PM
Mow installed on AVS Forum (http://www.avsforum.com) and it does help with this large active site.

Anything to make the DB server happier. :)

weewilly 08-14-2003 04:17 PM
I installed this hack and it seemed to work OK.

Until.... you entered a forum and then clicked on a thread to view it. Then, an error was displayed. I had to upload the original file to correct it.

I am using version 2.3.2

Anyone have any idea why this is not working for me? I am an extreme novice and feel silly even posting this.

*********************

Edited to add:

I have it working now. I was editing the file in dreamweaver. When I switched to notepad I was able to modify it and get it working.

Alien 08-17-2003 08:27 PM
Wow, people are really sleeping the past few months...

Help! :D

Alien 08-18-2003 02:29 AM
Ok I've decided to temporarily remove the other hack while I work it out. In the meantime I've installed this one, and the additional query removal from fury and all is working properly so far.. I'll let you know if it breaks anything. :D

Nice work you guys!

julius 08-18-2003 12:52 PM
Installed on vB 2.3.2.

Thanks
:)

legendarysk8er 09-17-2003 11:56 PM
Working fine ob vB 2.3.2.

/me clicks install.

zsmom 10-25-2003 10:20 PM
Works great on 2.3.2. Thanks!!

* zsmom clicks install.

luke2kz71 01-11-2004 10:33 PM
installed on 2.3.4 and working great! :D


All times are GMT. The time now is 02:45 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01314 seconds
  • Memory Usage 1,811KB
  • 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
  • (6)bbcode_code_printable
  • (2)bbcode_php_printable
  • (6)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (37)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
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete