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)
-   -   Improve performance on thread view updating (https://vborg.vbsupport.ru/showthread.php?t=78232)

dwh 03-16-2005 10:00 PM
Improve performance on thread view updating
 
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:
PHP Code:
$threads = $DB_site->query("SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
 
while ($thread = $DB_site->fetch_array($threads))
{
$DB_site->query(
"UPDATE " . TABLE_PREFIX . "thread
SET views = views + " . intval($thread['views']) . "
WHERE threadid = " . intval($thread['threadid'])
);
} 


With 4.04 you can do a multitable update.
FIRST, create the table ONCE ONLY
PHP Code:
PHP Code:
CREATE TABLE `threadviewsaggregate` (
`threadid` int(10) NOT NULL default '0',
`views` int(10) NOT NULL default '0',
PRIMARY KEY (`threadid`)
) TYPE=MyISAM; 
and in the threadviews.php code change it to

PHP Code:
PHP Code:
$DB_site->query("INSERT INTO ".TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 
 
$DB_site->query(
"UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate 
SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views
WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid"); 
 
$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate"); 

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.

nexialys 03-16-2005 11:35 PM
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...

dwh 03-16-2005 11:54 PM
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

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

nexialys 03-17-2005 01:39 AM
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...

kmike 03-17-2005 04:32 AM
Quote:
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"
PHP Code:
$DB_site->query("DELETE FROM". TABLE_PREFIX ."threadviewsaggregate"); 
2. After applying hack, move this line immediately after the line with INSERT SQL statement:
PHP Code:
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 
Reason for this is explained here:
http://www.vbulletin.com/forum/showp...4&postcount=16

Boofo 03-17-2005 06:37 AM
Quote:
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"
PHP Code:
$DB_site->query("DELETE FROM". TABLE_PREFIX ."threadviewsaggregate"); 
2. After applying hack, move this line immediately after the line with INSERT SQL statement:
PHP Code:
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 
Reason for this is explained here:
http://www.vbulletin.com/forum/showp...4&postcount=16
So what should the full code look like then? ;)

kmike 03-17-2005 08:34 AM
Quote:
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:
PHP Code:
error_reporting(E_ALL & ~E_NOTICE);

if (!is_object($DB_site))
{
        exit;
}

$DB_site->query("INSERT INTO ". TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews");

$DB_site->query(
        "UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate
        SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views
        WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid");
$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate");

log_cron_action('Thread Views Updated', $nextitem); 

Erwin 03-17-2005 09:12 PM
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. :)

dwh 03-18-2005 01:02 AM
Quote:
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.

Brad 03-18-2005 07:26 AM
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. :)

mute 03-22-2005 01:18 PM
This does look very promising, has jelsoft thought about integrating it into the product?

dwh 03-22-2005 01:31 PM
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.

mute 03-22-2005 01:33 PM
Thats great news. Now if I can only work up the nerve to upgrade to 3.1.0 when it comes out :)

deathemperor 03-23-2005 04:42 PM
Quote:
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 !

detalhe 04-16-2005 08:01 PM
Installed this and got this error after sometime:

PHP Code:
Database error in vBulletin 3.0.6:
 
Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid
mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviews
 
mysql error number: 1064 
Will uninstall :disappointed:

dwh 04-16-2005 08:03 PM
Is the lack of spaces only coming from your post or is it in your code as well? And what version of mysql do you have installed?

T3MEDIA 04-16-2005 11:39 PM
good questions.

red_baron2000 04-16-2005 11:49 PM
will certainly give it a go ..:)

detalhe 04-17-2005 09:51 AM
Quote:
Originally Posted by dwh
Is the lack of spaces only coming from your post or is it in your code as well? And what version of mysql do you have installed?
Now that you mention it, I think you should correct your code above, I think that lack of spaces comes from it

PHP Code:
$DB_site->query("INSERTINTO".TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews"); 

$DB_site->query(
         "UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate 
        SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views
        WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid"); 

$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate"); 
Will try it ;)

Dean C 04-19-2005 03:34 PM
Indeed you need to space your query out - that's why it's not working :)

dwh 04-19-2005 03:50 PM
For some reason, lately when I post here, the spaces get pulled out and words are stuck together. I have no idea why.

WebMasterAJ 04-20-2005 12:23 AM
I just installed this... so far so good :cool:

tp214 04-23-2005 07:25 AM
did the edits keep getting this

Code:
Database error in vBulletin 3.0.7:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid
mysql error: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Saturday 23rd of April 2005 03:25:03 AM
Script: http://www.v3gamer.com/forums/admincp/cronadmin.php?do=runcron&cronid=2
Referer: http://www.v3gamer.com/forums/admincp/index.php?
Username: admin
IP Address: xx.xx.xx.xxx

tp214 04-23-2005 07:27 AM
nevermind!

this is the correct code...

Code:
error_reporting(E_ALL & ~E_NOTICE);

if (!is_object($DB_site))
{
        exit;
}

$DB_site->query("INSERT INTO ". TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews");

$DB_site->query(
        "UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate
        SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views
        WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid");
$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate");

log_cron_action('Thread Views Updated', $nextitem);

LambHyjoo 04-28-2005 12:43 PM
Hi, and first of all thank you for this release.

I was just wondering if it is normal that the "threadviewsaggregate" table remains empty ?

Link14716 05-01-2005 01:52 PM
Quote:
Originally Posted by LambHyjoo
Hi, and first of all thank you for this release.

I was just wondering if it is normal that the "threadviewsaggregate" table remains empty ?
Yes, because it is only used when threadviews is being updated.

LambHyjoo 05-02-2005 07:05 AM
Ok, thanks for the answear ;)

RichieBoy67 05-03-2005 01:36 AM
How about a downloadable text file with edits @ instructions???

djwins 05-07-2005 08:25 PM
So, is the best code in the first post, or am I supposed to use one of the other users contributions?

Also, I am running mySQL 4.0.24. Will this work with my version?

Thanks!

dsboyce8624 05-08-2005 05:15 AM
Okay, I'd like to try this, but AdminCP says I don't have permission to execute queries. I tried pasting the create table stuff into the MySQL command box, but it said I needed to specify a datavase. I don't know the sytax so I couldn't.

A little help here please.

Marco van Herwaarden 05-08-2005 09:03 AM
To run a query in your ACP, you must add your userid to the canrunquery variable in your config.php

dsboyce8624 05-08-2005 01:31 PM
And this is the correct code?

Quote:
Originally Posted by tp214
nevermind!

this is the correct code...

Code:
error_reporting(E_ALL & ~E_NOTICE);

if (!is_object($DB_site))
{
        exit;
}

$DB_site->query("INSERT INTO ". TABLE_PREFIX ."threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM " . TABLE_PREFIX . "threadviews GROUP BY threadid");
$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews");

$DB_site->query(
        "UPDATE " . TABLE_PREFIX . "thread,". TABLE_PREFIX ."threadviewsaggregate
        SET ". TABLE_PREFIX ."thread.views = ". TABLE_PREFIX ."thread.views + ".TABLE_PREFIX."threadviewsaggregate.views
        WHERE ". TABLE_PREFIX ."thread.threadid = ". TABLE_PREFIX ."threadviewsaggregate.threadid");
$DB_site->query("DELETE FROM ". TABLE_PREFIX ."threadviewsaggregate");

log_cron_action('Thread Views Updated', $nextitem);

dsboyce8624 05-08-2005 01:39 PM
Okay, I put that code in and turned on logging. It says it ran fine.

Thanks for the help.

GlitterKill 05-21-2005 04:02 PM
Quote:
Originally Posted by detalhe
Installed this and got this error after sometime:

PHP Code:
Database error in vBulletin 3.0.6:
 
Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid
mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviews
 
mysql error number: 1064 
Will uninstall :disappointed:
I have used the most recent posted code that is supposed to be good but I get this type of error every hour:

Database error in vBulletin 3.0.6:

Invalid SQL: INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASviewsFROM threadviews GROUP BY threadid mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERTINTOthreadviewsaggregate SELECT threadid , COUNT(*)ASview

mysql error number: 1064

Date: Saturday 21st of May 2005 10:56:17 AM
Script: http://forums.pimprig.com/cron.php?&rand=634959
Referer: http://gallery.pimprig.com/showphoto...o=2553&cat=501
Username: Unregistered
IP Address: 86.133.1.23

Marco van Herwaarden 05-23-2005 09:14 AM
Insert spaces where needed. The code in this thread is somehow missing some spaces.

Edit: Edited the original code now with spaces inserted.

kmike 05-23-2005 10:25 AM
Took long enough...
But not all occurences are updated:
Code:
query("SELECTthreadid ,

Marco van Herwaarden 05-23-2005 11:11 AM
Quote:
Originally Posted by kmike
Took long enough...
There was no reason at all for that remark, especially since the corrections are already posted in this thread (and it is not a 50 page thread).

Quote:
Originally Posted by kmike
But not all occurences are updated:
Thanks fixed.

kmike 05-23-2005 12:57 PM
Sorry, I thought you're author of this hack but I see my mistake now.

David Bott 05-25-2005 07:03 PM
Installed on AVS Forum.

WotC_Mel 06-17-2005 05:26 PM
I see this has been installed successfully a few places. Anyone willing to comment on performance improvements they've notice?

-Mel


All times are GMT. The time now is 02:46 AM.
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.01413 seconds
  • Memory Usage 1,881KB
  • 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
  • (4)bbcode_code_printable
  • (11)bbcode_php_printable
  • (12)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