Printable View
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.
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?
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.
@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:
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?Code:SELECT ...
FROM vb3_thread
...
WHERE threadid IN (...)
ORDER BY sticky DESC, dateline DESC
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)
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 (?)
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 ofQuote:
Originally Posted by 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:
Then you don't even need to subquery or GROUP BY.Code: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
where the heck do you see those numbers? when i click on the [?] the query isnt even listed there
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.
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
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.
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"
holy crap... i disabled the tag cloud sidebar.. even though the cache is on 60 min.. the speed just went up inmense
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.
sleep well, and thnx for the help
yes it must have been the tag cloud. its vB own tagcloud
the db has the following tag tables:
tag: 4000+ lines
tagcontent 318545+ lines (ouch)
tag search: 6000+ lines
I see you're on vB 4.1.7. The tag cloud was supposedly fixed in 4.1.12: http://tracker.vbulletin.com/browse/VBIV-10802
Worth checking out if you really liked the block.