vb.org Archive
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vBulletin 3.0 Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=33)
-   -   New Posts / Get Daily Optimization (https://vborg.vbsupport.ru/showthread.php?t=66578)

Freddie Bingham 06-26-2004 10:00 PM
New Posts / Get Daily Optimization
 
Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

edit search.php, find:

PHP Code:
ORDER BY lastpost DESC 
Above this, place:

PHP Code:
AND sticky IN(0,1) 
For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.

Boofo 06-27-2004 01:13 AM
Will this work on RC3 for now? Once 3.0.2 gets released, I plan on upgrading (that is if 3.0.3 isn't just around the corner). ;)

Freddie Bingham 06-27-2004 01:20 AM
Quote:
Originally Posted by Boofo
Will this work on RC3 for now? Once 3.0.2 gets released, I plan on upgrading (that is if 3.0.3 isn't just around the corner). ;)
It will work on any version of 3.0 that you have access to.

SnowBot 06-27-2004 01:21 AM
This is nice of you to post :)

I think vBulletin should tell the users now what mods they have made to the files as most people have hacked the boards and dont want to rehack everything once an upgrade comes out. There cant be that many things to do and most people who have hacked there boards can make the file edits with the new 3.0.2 corrections.

food for thought :)

Freddie Bingham 06-27-2004 01:36 AM
Quote:
Originally Posted by SnowBot
This is nice of you to post :)

I think vBulletin should tell the users now what mods they have made to the files as most people have hacked the boards and dont want to rehack everything once an upgrade comes out. There cant be that many things to do and most people who have hacked there boards can make the file edits with the new 3.0.2 corrections.

food for thought :)
Our policies are not for discussion here.

Boofo 06-27-2004 01:54 AM
Quote:
Originally Posted by Freddie Bingham
It will work on any version of 3.0 that you have access to.
Thank you, Freddie. ;)

This is already added to 3.0.2, right?

P.Jackson 06-27-2004 11:02 AM
no doubt :)

the Sandman 06-27-2004 11:31 AM
Thanks Freddie!

colicab-d 06-27-2004 11:59 AM
is 3.0.2 out? Ive not seen it

the Sandman 06-27-2004 12:01 PM
It's not out... yet...

sabret00the 06-27-2004 01:13 PM
nice work, that's a major optimisation right there :)

nuno 06-27-2004 01:25 PM
Quote:
Originally Posted by Freddie Bingham
Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

edit search.php, find:

PHP Code:
ORDER BY lastpost DESC 
Above this, place:

PHP Code:
AND sticky IN(0,1) 
For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.
God bless you, dear!

MindTrix 06-27-2004 01:30 PM
Nice little addition thanks

Vega 06-27-2004 01:35 PM
Nice one! :)

Thx.

Cold Steel 06-27-2004 02:15 PM
Installed - thanks!

Freddie Bingham 06-27-2004 02:37 PM
Quote:
Originally Posted by colicab-d
is 3.0.2 out? Ive not seen it
3.0.2 is not out yet but I assumed that this little change would be helpful now.

SnowBot 06-27-2004 02:46 PM
Quote:
Originally Posted by Freddie Bingham
Our policies are not for discussion here.
I didnt say i wanted to :P I just stated my views.

PET 06-27-2004 03:04 PM
uhh...i just upgrade to 3.0.1 and now i hear 3.0.2 :(

Oblivion Knight 06-27-2004 03:12 PM
Thanks Freddie.. :)

tamarian 06-27-2004 03:58 PM
My "check daily" went from about 1 second to about 0.1 seconds!!

Well done, installed. :)

mtha 06-27-2004 06:15 PM
Quote:
Originally Posted by Freddie Bingham
Here is a small 3.0.2 optimization to search.php?do=getnew / search.php?do=getdaily that you can make a noticeable impact on your load.

edit search.php, find:

PHP Code:
ORDER BY lastpost DESC 
Above this, place:

PHP Code:
AND sticky IN(0,1) 
For example on vb.com, we went from doing a scan of 107000 thread rows on every getnew/getdaily (that wasn't cached in search.php) to a scan of 188 rows.
sounds good, but ... could you explain for me how this supposed to work? all threads has sticky value of (0,1), isnt it?

I dont see the exclussion in this condition :(

neocorteqz 06-27-2004 06:26 PM
always the small things that make a big impact. :)

Thanks.

tamarian 06-27-2004 06:37 PM
Quote:
Originally Posted by mtha
sounds good, but ... could you explain for me how this supposed to work? all threads has sticky value of (0,1), isnt it?

I dont see the exclussion in this condition :(
I'm not sure either, but I suspect that it has to do with some MySQL indexing row-scan quirk, that makes this trick possible.

But I'd love to hear more from Freddie on this :)

MugenSi00 06-27-2004 11:52 PM
thanks

thakikka 06-28-2004 04:36 AM
vb3 rc4:
"Parse error: parse error, unexpected $ in /www/htdocs/********/search.php on line 1658"

this is line 1654 to 1668:
PHP Code:
    // remove all ids from $orderedids that do not exist in $remaining
    $orderedids = array_intersect($orderedids, $remaining);
    unset($remaining);

    // rebuild the $orderedids array so keys go from 0 to n with no gaps
    $orderedids = array_merge($orderedids, array());

    // count the number of items
    $numitems = sizeof($orderedids);

    // do we still have some results?
    if ($numitems == 0)
    {
        eval(print_standard_error('searchnoresults', 1, 0));
    } 

Erwin 06-28-2004 05:55 AM
Interesting - my method to limit the load was to modify the query so that it is forced to only scan the past 100 posts posted only. According to the mysql logs, that only made the query scan 100 rows. But I will try this out for sure. :)

Andreas 06-28-2004 05:57 AM
Quote:
Originally Posted by mtha
I dont see the exclussion in this condition :(
You are right that the condition itself doesn't make sense als all threads are stiicky or not ;)

But: Table thread has an index called forumid which is built from the colums forumid, visible, sticky and lastpost.

Three of them (forumid, visible and lastpost) are already conditions of that particular query, so if a condition for column sticky is added mySQL uses this index which dramatically reduces the time to execute the query.

@thakikka
The code excerpt you posted seems just fine, so the error must be somewhere above.

Erwin 06-28-2004 07:50 AM
It definitely sped things up. Thanks, Freddie!

mtha 06-28-2004 12:18 PM
Quote:
Originally Posted by KirbyDE
You are right that the condition itself doesn't make sense als all threads are stiicky or not ;)

But: Table thread has an index called forumid which is built from the colums forumid, visible, sticky and lastpost.

Three of them (forumid, visible and lastpost) are already conditions of that particular query, so if a condition for column sticky is added mySQL uses this index which dramatically reduces the time to execute the query.

@thakikka
The code excerpt you posted seems just fine, so the error must be somewhere above.
Thats cool, thanks

noppid 06-28-2004 05:29 PM
This should be listed as a bug if it's bringing servers to their knees. Nice try trying to hide it and offer it as a hack. It should have been announced at .com.

tamarian 06-28-2004 06:18 PM
Quote:
Originally Posted by noppid
This should be listed as a bug if it's bringing servers to their knees.
What is bringing your server to it's knees? I'm using it on 2 million+ forum with no problems.

Freddie Bingham 06-28-2004 07:37 PM
Quote:
Originally Posted by noppid
This should be listed as a bug if it's bringing servers to their knees. Nice try trying to hide it and offer it as a hack. It should have been announced at .com.
If you are going to have that attitude then I don't need to bother posting things like this but if it makes you happy I can post it in the bug tracker. I'm sorry that you feel I'm trying to "hide" something. Perhaps if that was my goal then I would have just updated the code and not posted here at all? Doesn't that seem to make more sense?

P.Jackson 06-28-2004 08:10 PM
well whatever ;) thanks for releasing it here freddie im sure alot of people appriciate it including me :)

Xenon 06-28-2004 08:20 PM
nice one Freddie :)

that shows the little hints can boost MySQL :)

the Sandman 06-28-2004 08:23 PM
IMO Freddie's first post was accurate and straightforward - it's an optimization for vB3 from the upcoming 3.0.2 release. Since adding it involves editing a file, and it's not a bug, this seems to be the appropriate place to release it. Of course, there are other possible ways to look at it, but the majority here seem to appreciate it as do I. Thanks again Freddie!

Any other tidbits you can throw our way? :D

WoodiE 06-28-2004 09:08 PM
Funny - my speeds increased with this hack.

getnew went from .02 to .03

getdaily went from .05 to .07

and my get unanswered threads went from .30 to .44

-Michael

Freddie Bingham 06-29-2004 06:11 PM
Quote:
Originally Posted by WoodiE
Funny - my speeds increased with this hack.

getnew went from .02 to .03

getdaily went from .05 to .07

and my get unanswered threads went from .30 to .44



-Michael
WoodiE that isn't possible unless you ran an unscientific test or you are missing the index on the thread table.

Alien 06-29-2004 06:36 PM
Quote:
Originally Posted by Freddie Bingham
WoodiE that isn't possible unless you ran an unscientific test or you are missing the index on the thread table.
Kick ass. Thanks a LOT, Freddie! Doing nicely...

vbmechanic 06-29-2004 08:17 PM
Ignore the naysayers... THANK YOU for releasing this early; makes a huge difference on sites with 1 mil+ posts.

Princeton 06-29-2004 10:07 PM
thanks Freddie ...

optimization hacks/tip = :D:):up:


All times are GMT. The time now is 04:35 PM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

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.01326 seconds
  • Memory Usage 1,825KB
  • 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
  • (7)bbcode_php_printable
  • (14)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)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