Optimization suggestions?

[Morrus]

We've been suffering crippling slowdown since the wiki was activated. I was wondering if anyone had some tips for optimizaton (wiki settings and so forth)?

As an aside - I turned off all the autolinking, but it's still autolinking in the forums. I don't know if that's having any effect, but I imagine that would be server-intensive on a large forum.

[pegasus]

What site pages notice the most significant change? What does vBulletin's Debug Mode say about page generation time, queries, memory on these pages with both VaultWiki enabled and disabled?

[Lord Doys]

Ah, so i am not the only one thinking the overall performance has gone down.
forum and wiki pages sometimes take up to 10 secs before they are displayed

I am quite interested in what matters i can take to increase the forums speed again.

[pegasus]

@Lord Doys, first thing I would do is deal with the uncached templates that the Debug Mode is reporting. Next I would try to deal with your slowest query, which appears near the bottom of most forumdisplay pages:

SELECT ...
FROM vb3_thread
...
WHERE threadid IN (...)
ORDER BY sticky DESC, dateline DESC

This query typically takes over 0.1s to execute before it caches. I don't think this is a standard vB query, and I'm not 100% positive what portion of the query is causing issue but you could try adding an index to your thread table on (threadid, sticky, lastpost). If this doesn't help I would also try placing an index on thread.firstpostid. But sometimes MySQL is just stupid and chooses a slow index for no reason, but we mainly only have this problem with small data sets, and we can't really add FORCE_INDEX to vB's core files, can we?

I see that VaultWiki takes a similar amount of time to prepare the article list on each page, but I think this is just because it processes over 15,000 rows. This is one of the scalability issues we noticed with version 3 and we had a hard time finding solutions that wouldn't sacrifice functionality - like auto-links or quick look-ups to determine if pages already exist.

Our hope is that the new architecture in VaultWiki 4 reduces the footprint of the article list while we implement some real solutions to dealing with that many rows on a regular basis (such as not doing it).



@Morrus, I see you are using vBulletin 3, so you wouldn't be affected by some rogue VaultWiki plugins (now removed) that were known to cause issues with some versions of vBulletin 4. You only have around 100 pages in your wiki, so you shouldn't notice the many-articles issue I discussed above. However, you do have almost 6M posts in your database and over 100K users. Again, I would be really interested in seeing your Debug Mode, especially the Explain view (url?explain=1)

[Lord Doys]

nice! thanx a lot

vBulletin 4.1.7 Debug Information

Page Generation 9.01990 seconds
Memory Usage 34,746KB
Queries Executed 37 (?)


to

Bulletin 4.1.7 Debug Information

Page Generation 0.58055 seconds
Memory Usage 33,725KB
Queries Executed 26 (?)

[pegasus]

I don't know, the queries might just be cached or something else was cached. Do you have Xcache, Memcached, etc, set up on your forum?

Happily, I just got a nice long loading screen while on the explain view. I'll see what it says.

[Lord Doys]

I don't know, the queries might just be cached or something else was cached. Do you have Xcache, Memcached, etc, set up on your forum?

Happily, I just got a nice long loading screen while on the explain view. I'll see what it says.

not that i know of

[pegasus]

15 seconds, 8 MySQL 7 PHP

.24 seconds, threadid,sticky,lastpost - used PRIMARY threadid

seems the problem is somewhere before the end of global.php, which basically means before the current script has started executing.

A nice 5.9 second query. Might be making the tag cloud, there's a lot about tags and it's not hitting any indexes against your 24,274 row thread table. This isn't VaultWiki.

Another 2.3 seconds spent on a weird query to vault_revision, subquerying it, and returning all 32,000 rows before deciding on 0 of up to 10 of them. As I suggested in another thread, this query for your recent edits block is a problem.

If you're intent on returning every row, you should be getting only 15,000, instead of the total 43,000 the old query made.

Use this method:

FROM vb3_vault_article AS article
LEFT JOIN vb3_vault_revision AS revision ON (revision.threadid = article.threadid AND revision.dateline = article.lastmodified)
LEFT JOIN vb3_thread AS thread ... etc etc etc

Then you don't even need to subquery or GROUP BY.

[Lord Doys]

where the heck do you see those numbers? when i click on the [?] the query isnt even listed there

[pegasus]

Both of these queries get cached, you have to be lucky to catch it. You basically have to try to go to the [?] view first. Pick a random forum URL, type it into your address bar (or copy and paste from a link), and add ?explain=1 to the address. If you can't get them, you can also go to Settings > Options > Server Settings and Optimization Options > Disable Content Caching.

[Lord Doys]

ok beats me...

with cont cahce disabled:
Page generated in 30.404980182648 seconds with 50 queries,
spending 5.9577217102051 doing MySQL queries and
24.447258472443 doing PHP things.

still that query which does the latest changes sidebar:
Time Taken: 0.00023 seconds

[pegasus]

Even if the sidebar isn't cached so you can see the queries, the query itself can become cached in MySQL so it will just return the same results as last time, rather than looking them up again. Getting around this is difficult, you would have to RESET QUERY CACHE, and then the next page load (you or a user) would be reliably fresh.

Even with the 24 PHP seconds, I would look into fixing those 2 queries.
Then a good question is why PHP takes so long. You must have an extra loop in there somewhere that doesn't have an efficient end. Maybe the tag cloud loops for mysql_num_rows (24,000), it's hard to say without code access.

[Lord Doys]

i fixed that sidebar query before last post.

but since it isnt VW related, i wont bother you with it any more.. (unless you want to ;-)
like a goblin once told me.. "Time is Money my friend"

[Lord Doys]

holy crap... i disabled the tag cloud sidebar.. even though the cache is on 60 min.. the speed just went up inmense

[pegasus]

Yes, so I can get some sleep. Hope you figure it out soon. I'm guessing it's also in the sidebar somewhere, since the slowdown stops whenever the page loads from cache. That's why I suggested to look at the tag cloud. I don't know if this is vB's tag cloud or someone else's, I have never used one.

EDIT: So it was the cloud... I don't know if you have the coding experience, but you can try to fix the cloud or just leave it turned off. If it's vB's report it to them. It was cycling through every record in the database, I'm guessing in both MySQL and PHP, so yeah, that's not ideal to say the least.