There's a forum on my board with nearly 100k threads and if i look into the forumdisplay's debug info, i see a slow query:
SELECT t.*, t.username AS threadusername, u.username FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) WHERE t.fid='6' AND (t.visible='1' OR t.visible='0') ORDER BY t.sticky DESC, t.lastpost desc LIMIT 0, 20
Query Time: 0.5653178691864
In other forums, this query is much faster. 0.02xxxxxx etc.
How can i solve this problem? Any suggestion would be appericated.
Thanks!
edit: It seems, this is the slowest query on my whole site.
What are you serving specs?
Server: Xenon Dual X3050, 4GB DDR2 RAM, SATA2 HDD
~35k visitors, ~150k pageloads, ~1500 posts /day.
Well that's a nice busy forum. You may not have your mysql optimized well.
Do you have a lot of stickies in this section? Try removing them and see the difference.
btw please consider joining Big Board group here on MyBB.com as we assist with custom edits and alterations for larger forums.
Did you optimize your website?
(2011-11-01, 04:16 PM)labrocca Wrote: [ -> ]Well that's a nice busy forum. You may not have your mysql optimized well.
Do you have a lot of stickies in this section? Try removing them and see the difference.
btw please consider joining Big Board group here on MyBB.com as we assist with custom edits and alterations for larger forums.
Thank you so much! You helped me a lot!
There's now 300 visitors/5 min on my board, so i can spot the slow queries easily.
2 other slow queries:
SELECT a.*, u.username FROM mybb_announcements a LEFT JOIN mybb_users u ON (u.uid=a.uid) WHERE a.startdate<='1320169014' AND (a.enddate>='1320169014' OR a.enddate='0') AND ((fid='5' OR fid='6') OR fid='-1') ORDER BY a.startdate DESC LIMIT 0, 5
Query Time: 2.3147180080414
and
SELECT u.*, f.* FROM mybb_users u LEFT JOIN mybb_userfields f ON (f.ufid=u.uid) WHERE u.uid='1' LIMIT 1
Query Time: 0.21911096572876
You'd need better analysis of your setup. Most likely you'll need config changes to your MySQL and even possibly an upgrade of hardware.
Yes, possibly I need to upgrade the CPU to a quad core.
Willy nilly hardware upgrades won't necessarily solve anything.
You need to identify why it's slow. Stuff like:
- is the slowness repeatable or once-off?
- do you notice anything from MySQL's explain query?
- what's the MySQL process list like when executing the query?
-The slowness is 24/7 (only a few visitors is enough to overload the CPU)
-Unfortunately I never heard about the explain query. :/