Bug Forum Performance Issues

[PSUser]

We have been experience site performance issues with vBulletin. I have inquired with our system administrator and this is what he said:

"I'm seeing a TON of slow queries on DB01 (master), and there are some queries running related to vbShop points logs which are using up alot of resources. It's pulling all the points logs, and there are tens of millions of rows. there seem to be maintenance functions that run periodically that just keep getting slower.

It isn't dealing with PSU's scale well."

Not sure how to handle this, but forum performance is becoming a major issue. All I have to go by is what the sys admin tells me. Would appreciate any help and/or guidance. Does the latest update deal with issues that he describes?

 

[Fillip H.]

Can you please ask your DBA to give me an example of a slow query? If so, I will be able to tell you exactly where it's from and advise from there

 

[PSUser]

I sent your question to the DBA. Waiting to hear back. Thanks.

 

[PSUser]

Still haven't heard back, but he had said this previously as well. Don't know if this helps.

" The one in particular that has given us headaches is the Points/VBShop plugins. The data tables it produces are massive and really slow, and there seem to be maintenance functions that run periodically that just keep getting slower. "

 

[PSUser]

Ok. Here is the response:

"So the PSU master database is slow as a crawl right now. This query has been running for 3 minutes, and is only one of many that get ran. Some script in the dbtech plugin seems to be fetching the entire table in 100K row batches. It should also not be running on Master since it's a SELECT.

SELECT *
FROM`forum4_dbtech_vbactivity_pointslog`
LIMIT 40500000 , 100000"

 

[Fillip H.]

You can turn off the "Enable Activity Feed" setting to reduce access to that table.

 

[PSUser]

Looks like it is already turned off.

 

[Fillip H.]

The query you posted actually does not exist anywhere in vBActivity, from what I can tell. There is no query that indiscriminately selects all columns from that table in that manner.