vb.org Archive

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` (
`
threadidint(10NOT NULL default '0',
`
viewsint(10NOT 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. :)


All times are GMT. The time now is 01:36 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.01161 seconds
  • Memory Usage 1,779KB
  • 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
  • (8)bbcode_php_printable
  • (5)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
  • (10)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