Improve performance on thread view updating

[dwh]

Per Doc Erwin:
http://www.vbulletin.com/forum/showpost.php?p=834208&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:
$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:
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:
$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]

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]

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.

[nexialys]

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]

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"

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


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

$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews");

Reason for this is explained here:
http://www.vbulletin.com/forum/showpost.php?p=833844&postcount=16

[Boofo]

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"

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


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

$DB_site->query("DELETE FROM " . TABLE_PREFIX . "threadviews");

Reason for this is explained here:
http://www.vbulletin.com/forum/showpost.php?p=833844&postcount=16

So what should the full code look like then? ;)

[kmike]

So what should the full code look like then? ;)
I see it's easier to paste whole file, just without header/footer:

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]

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]

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]

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]

This does look very promising, has jelsoft thought about integrating it into the product?

[dwh]

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]

Thats great news. Now if I can only work up the nerve to upgrade to 3.1.0 when it comes out :)

[deathemperor]

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]

Installed this and got this error after sometime:


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]

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]

good questions.

[red_baron2000]

will certainly give it a go ..:)

[detalhe]

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

$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]

Indeed you need to space your query out - that's why it's not working :)

[dwh]

For some reason, lately when I post here, the spaces get pulled out and words are stuck together. I have no idea why.

[WebMasterAJ]

I just installed this... so far so good :cool:

[tp214]

did the edits keep getting this

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]

nevermind!

this is the correct 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]

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]

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]

Ok, thanks for the answear ;)

[RichieBoy67]

How about a downloadable text file with edits @ instructions???

[djwins]

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]

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]

To run a query in your ACP, you must add your userid to the canrunquery variable in your config.php

[dsboyce8624]

And this is the correct code?

nevermind!

this is the correct 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]

Okay, I put that code in and turned on logging. It says it ran fine.

Thanks for the help.

[GlitterKill]

Installed this and got this error after sometime:


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.php?photo=2553&cat=501
Username: Unregistered
IP Address: 86.133.1.23

[Marco van Herwaarden]

Insert spaces where needed. The code in this thread is somehow missing some spaces.

Edit: Edited the original code now with spaces inserted.

[kmike]

Took long enough...
But not all occurences are updated:

query("SELECTthreadid ,

[Marco van Herwaarden]

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).

But not all occurences are updated:Thanks fixed.

[kmike]

Sorry, I thought you're author of this hack but I see my mistake now.

[David Bott]

Installed on AVS Forum.

[WotC_Mel]

I see this has been installed successfully a few places. Anyone willing to comment on performance improvements they've notice?

-Mel

[mute]

Does anyone know if this made its way into 3.5.0?

[lazee]

I used this one smooth and help my board alot with no problem ,but today when i notice that the thead didn't count when people view , i ran the cron job theadview in schedules and i got this error :
Database error in vBulletin 3.0.7:

Invalid SQL: INSERT INTO threadviewsaggregate SELECT threadid , COUNT(*) AS views FROM threadviews GROUP BY threadid
mysql error: Duplicate entry '40' for key 1

mysql error number: 1062

Date: Tuesday 28th of June 2005 08:09:36 PM
Script: http://xx.com/forum/admincp/cronadmin.php?do=runcron&cronid=2
Referer: http://xx.com/forum/admincp/index.php?

anyone got any idea why i got this and how to fix ,thank in advance . I used to run the threadview before and nerver got this error . before this error, i didn't restore db or install anything .

[kmike]

You should empty threadviewsaggregate table. It is cleared at the end of cron script run, but it didn't happen at some point for some reason. Theoretically, it can happen when you shutdown http server in the middle of cron job run.

[lazee]

Thank Kmike .it working after i empty the threadview.. . I think i did restart the apache that was cause the problem like you said .

[michaelbenson]

Erm, where exactly is this threadviews.php file?
I have tried looking around and i am using 3.0.9 has it since been removed?

[Andreas]

includes/cron