Performance issues since installing vbSEO

Hi,
Hoping somebody can shed some light on performance issues I have on my site since installing vbSEO.

My site is running on two dedicated servers - 1 web server, 1 database server.

Up until the summer the site was on a single server and starting throwing database errors around the 1500 user mark. After moving to two servers, it was able to handle upwards of 2000 simultaneous users with load nice and low on both boxes, so there was plenty of breathing room to break 2000 users and beyond.

I installed vbSEO just before Christmas and since then when traffic has peaked the database has been backed up with slow queries (3000 database errors in the space of about 20 minutes yesterday). The load has jumped dramatically on the database sever and site performance is noticeable slower at around the 1200+ user mark.

The majority of the slow queries are like the following:

Code:

# Time: 071229 19:29:49
# Query_time: 11  Lock_time: 0  Rows_sent: 1  Rows_examined: 36651
SELECT t.forumid, t.threadid, t.title, t.replycount, t.lastposter, 1 lastpostid, t.lastpost
FROM thread AS t
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = t.threadid AND type = 'thread')
WHERE forumid = 6 AND lastpost < 1198956743 AND visible = 1 AND open <> 10
AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 1;

Any advice on where the problem could be?

What kind and how many extra database queries does vbSEO make for your typical vbulletin page?

I may well have to uninstall vbSEO permanently if I can't find a solution to this, so any help would be appreciated.

I don't actively maintain the server hardware/software, I handle things on the vbulletin end, but if you need specific information to help diagnose the problem, I can get it.

Thanks in advance.

Hello,

make sure that you have disabled "Replace 'Previous/Next Thread' redirect links?" option in vbseocp to disable the mentioned queries.

Yes indeed. This option should be kept disabled for bigger boards as it adds two queries that can be expensive.

Go to:
vBSEO CP > SEO Functions >Direct Links > Replace 'Previous/Next Thread' redirect links?Set to 'no'

Additionally, I see you are a big board admin. I've given you access to the big-board forums where you will find other server-setup discussions that might be useful.

Thanks, I've disabled those two options so will see what difference that makes. Are there any other less essential vbSEO features that could be disabled to reduce database queries?

Do you know if those prev/next topic queries are related at all to the SQL query in my original post?

Thanks for access to the Big Board forum.

Looking at the query, it looks like you soft delete a lot of posts....id suggest running this and possibly think about cleaning it up if the returned results are huge.

Code:

    SELECT  count(*), as `mycount`, primaryid
FROM deletionlog
group by primaryid

Originally Posted by Sidane

Thanks, I've disabled those two options so will see what difference that makes. Are there any other less essential vbSEO features that could be disabled to reduce database queries?

Once you disable this option, vBSEO will require 0, 0, 1, 2 and 0 queries for forumhome, forumdisplay, showthread, memberprofile and memberlist respectively. So I wouldn't expect significant reductions from disabling any other vBSEO feature.

Do you know if those prev/next topic queries are related at all to the SQL query in my original post?

Yes they are.

Thanks for access to the Big Board forum.

Glad to have you join the group.

Originally Posted by Juan Muriente

vBSEO will require 0, 0, 1, 2 and 0 queries for forumhome, forumdisplay, showthread, memberprofile and memberlist respectively.

Correction, that is 1 query instead of 2 for memberprofiles as you are not running vBBlogs, vBBlogs requires an additional query to extract the blog data.

Originally Posted by briansol

Looking at the query, it looks like you soft delete a lot of posts....id suggest running this and possibly think about cleaning it up if the returned results are huge.

Code:

    SELECT  count(*), as `mycount`, primaryid
FROM deletionlog
group by primaryid

Thanks Brian,
I ran that query and it returns 23000 rows. Should I delete most/all of these? Is there any negative impact of doing so?

The larger the table, the more resources mysql will need to make the join.

IMO, hard delete everything that's garbage, spam, etc from the table. It might be a long manual process though. Teaching your staff is another thing altogether.... they have the option to soft delete most likely.
It goes a step further too- if you allow members to delete their own posts, it soft deletes them.

The only things i tell my staff to soft delete are posts that may contain info that's wrong, or something like that. anything else, i have them hard delete.

running the same query i posted, i have 1200 records out of 900,000 posts. i don't know how many posts you have, but perhaps its proportional.


Another thing to consider is WHO can see these soft deleted posts? can regular members see them? or just staff?

assuming you have a limited number of staff ( <50 ) i can't see how the delete log would prove to be a huge problem....

Originally Posted by Oleg Ignatiuk

make sure that you have disabled "Replace 'Previous/Next Thread' redirect links?" option in vbseocp to disable the mentioned queries.

Originally Posted by Juan Muriente

Yes indeed. This option should be kept disabled for bigger boards as it adds two queries that can be expensive.

Oleg, Juan:

Any other tips/suggestions/tweaks recommended for large sites?

Originally Posted by KW802

Oleg, Juan:

Any other tips/suggestions/tweaks recommended for large sites?

Moving rewrite rules to httpd.conf appears to have the biggest performance improvement since .htaccess files are processed on each request while httpd.conf is processed only at server startup.

As I mention above, vBSEO adds 0 to 1 query for most pages and I do not recommend disabling the direct links option (which adds 2 queries for threads) unless these queries are impacting your performance. You might want to monitor slower queries in your instance, in most cases it won't have a noticeable impact, and the SEO benefits of direct links are desirable.

Btw, the next vBSEO version will feature direct links for threads from the 'goto last post' type links in forumdisplay (that's a whole lot of direct links to your forum's deeper content!!). This with none, zero, nada queries added.

Performance has noticeably improved with Direct Links disabled, database performance is more or less back to normal with 1800 simultaneous users.

So thanks for the tip!