Go Back   vBulletin.org Forum / vBulletin Modifications / Archive / vB.org Archives / vBulletin 3.0 / vBulletin 3.0 Full Releases
Reload this Page

Improve performance on thread view updating

Register Members List Search Today's Posts Mark Forums Read

Reply
Page 1 of 4 1 234
 
Mod Options
Improve performance on thread view updating Details »
Improve performance on thread view updating
Mod Information
The Developer
Last Online: Dec 2010
 
Mod Version: 1.00, by dwh (Member) dwh is offline
Developer Last Online: Dec 2010 I like it Show Printable Version Email this Page
This modification is in the archives.
vB Version: 3.0.7 Rating: (1 vote - 5.00 average) Installs: 16
Released: 16 Mar 2005 Last Update: Never Downloads: 0
Not Supported  

Per Doc Erwin:
http://www.vbulletin.com/forum/showp...8&postcount=18
I'm releasing this here.

I have a suggestion to improve performance although it is for mysql 4.04 and up (I assume at some point a future version of vb will require it).

Requirements: MySQL 4.04

If you have a large forum, run mysql 4.04 and up and want to try this, please report back on your results.

The way updating threads views works right now, (even though putting it in cron improved things,) you are still going through many queries to the thread table, potentially locking it up while updating views.The current code from threadviews.php is:

PHP Code:
Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.



With 4.04 you can do a multitable update.
FIRST, create the table ONCE ONLY
PHP Code:
Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

and in the threadviews.php code change it to

PHP Code:
Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.


That will reduce the number of queries by however many threads get updated in the cron interval. Additionally, you might be able to skip creating the extra table using subqueries. I haven't gotten around to writing that code yet. If someone wants to add that,mention it in this thread and I'll update the code.

Download Now

Only licensed members can download files, Click Here for more information.

Show Your Support

  • To receive notifications regarding updates -> Click to Mark as Installed.
  • This modification may not be copied, reproduced or published elsewhere without author's permission.
Comments
  #2  
Old 17 Mar 2005, 00:35
nexialys
Guest
  
put your codes in [ PHP ] tags please...

also, can you point a real MySQL doc where the example is explained and rated ?!... because i can see your point, as a mysql developper, but many others will discuss it...
Last edited by nexialys; 17 Mar 2005 at 00:37.
Reply With Quote
  #3  
Old 17 Mar 2005, 00:54
dwh's Avatar
dwh dwh is offline
Member
  
Join Date: Feb 2002
I copied it from vb.com and on preview the php code displayed. I edited it.

On the mysql:
http://dev.mysql.com/doc/mysql/en/update.html

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.priceWHERE items.id=month.id;The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.

Before MySQL 4.0.18, you need the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified.

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Currently, you cannot update a table and select from the same table in a subquery.
Reply With Quote
  #4  
Old 17 Mar 2005, 02:39
nexialys
Guest
  
ok, no problem btw...

but i would usggest you to release it in the BETA forum instead, because it's in development, not really releaseable on production sites...

but maybe you can modify it to have a if statement depending on PHP version we have on our server...
Reply With Quote
  #5  
Old 17 Mar 2005, 05:32
kmike kmike is offline
Member
  
Join Date: Oct 2002
Originally Posted by nexialys
but i would usggest you to release it in the BETA forum instead, because it's in development, not really releaseable on production sites...

but maybe you can modify it to have a if statement depending on PHP version we have on our server...
It's actually pretty much of production quality, I'm running this with small changes now. According to dwh's thread at vb.com, he has this hack applied and running, too.
This hack doesn't depend on PHP version, only on MySQL version - anything greater than 4.0 works.

My changes are:
1. This string has a typo, there should be a space after "FROM"

Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

2. After applying hack, move this line immediately after the line with INSERT SQL statement:

Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

Reason for this is explained here:
http://www.vbulletin.com/forum/showp...4&postcount=16
Reply With Quote
  #6  
Old 17 Mar 2005, 07:37
Boofo's Avatar
Boofo Boofo is offline
R.I.P.
  
Join Date: Mar 2002
Real name: Rob
Originally Posted by kmike
It's actually pretty much of production quality, I'm running this with small changes now. According to dwh's thread at vb.com, he has this hack applied and running, too.
This hack doesn't depend on PHP version, only on MySQL version - anything greater than 4.0 works.

My changes are:
1. This string has a typo, there should be a space after "FROM"

Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

2. After applying hack, move this line immediately after the line with INSERT SQL statement:

Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

Reason for this is explained here:
http://www.vbulletin.com/forum/showp...4&postcount=16
So what should the full code look like then?
Reply With Quote
  #7  
Old 17 Mar 2005, 09:34
kmike kmike is offline
Member
  
Join Date: Oct 2002
Originally Posted by Boofo
So what should the full code look like then?
I see it's easier to paste whole file, just without header/footer:

Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

Reply With Quote
  #8  
Old 17 Mar 2005, 22:12
Erwin's Avatar
Erwin Erwin is offline
Member
  
Join Date: Jan 2002
I encouraged dwh to post here. It's fine to be in this forum, no need to be in beta considering it's working on some large sites.

I may add this to my forum with over 14mil posts... I am always wary of database corruption.
Reply With Quote
  #9  
Old 18 Mar 2005, 02:02
dwh's Avatar
dwh dwh is offline
Member
  
Join Date: Feb 2002
Originally Posted by Erwin
I encouraged dwh to post here. It's fine to be in this forum, no need to be in beta considering it's working on some large sites.

I may add this to my forum with over 14mil posts... I am always wary of database corruption.
14 million posts???? No ****!
You deserve a beer.
Can you PM me your url? Would love to take a look.
Reply With Quote
  #10  
Old 18 Mar 2005, 08:26
Brad Brad is offline
Contributor
  
Join Date: Nov 2001
Erwin's forum is very large

I have this running on my home box with no problems, probbly use this on my production site soon to.
Reply With Quote
  #11  
Old 22 Mar 2005, 14:18
mute mute is offline
Member
  
Join Date: Dec 2002
This does look very promising, has jelsoft thought about integrating it into the product?
Reply With Quote
  #12  
Old 22 Mar 2005, 14:31
dwh's Avatar
dwh dwh is offline
Member
  
Join Date: Feb 2002
Yes they are.

I haven't had a chance to develop it yet but using the same principle Ibelieve there are a couple of other places this concept can be used.
Reply With Quote
  #13  
Old 22 Mar 2005, 14:33
mute mute is offline
Member
  
Join Date: Dec 2002
Thats great news. Now if I can only work up the nerve to upgrade to 3.1.0 when it comes out
Reply With Quote
  #14  
Old 23 Mar 2005, 17:42
deathemperor's Avatar
deathemperor deathemperor is offline
Member
  
Join Date: Jul 2003
Real name: Lucius Hunk
Originally Posted by Erwin
I may add this to my forum with over 14mil posts... I am always wary of database corruption.
WTF ! YEAH I mean the number, 14mil posts is really far off my hands ! OMG !
__________________
Need Vbulletin 3.5 styles ?

http://download.holvn.org
Reply With Quote
  #15  
Old 16 Apr 2005, 21:01
detalhe's Avatar
detalhe detalhe is offline
Member
  
Join Date: Jan 2005
Installed this and got this error after sometime:


Block Disabled:      (Update License Status)   
Suspended or Unlicensed Members Cannot View Code.

Will uninstall :disappointed:
Reply With Quote
Reply
Page 1 of 4 1 234

« Previous Mod | Next Mod »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Mod Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 21:37.

Layout Options | Width: Wide Color:
- vBulletin Main Site
Powered by vBulletin® Version 3.8.14
Copyright © 2023, MH Sub I, LLC dba vBulletin. All Rights Reserved. vBulletin® is a registered trademark of MH Sub I, LLC
Design by Princeton
Copyright ©2001 - , vbulletin.org. All rights reserved.
vBulletin® is a registered trademark.