Slow Index Loading

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Slow Index Loading

Post by Oleg »

Adding two more conditions on the thread join should make it possible to use the forum_appr_last index, which appears to come with phpbb itself.

SELECT f.*, ft.mark_time, t.topic_title, t.topic_id, t.topic_last_post_id FROM (phpbb_forums f) LEFT JOIN phpbb_forums_track ft ON (ft.user_id = 2 AND ft.forum_id = f.forum_id) LEFT JOIN phpbb_topics t ON (f.forum_last_post_id = t.topic_last_post_id AND t.topic_moved_id = 0 and t.topic_approved=1 and t.forum_id=f.forum_id) ORDER BY f.left_id

Without t.topic_approved=1 check unapproved topics are displayed, which probably is not intentional.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
MichaelBuckle
Registered User
Posts: 13
Joined: Tue Oct 06, 2009 3:39 pm

Re: Slow Index Loading

Post by MichaelBuckle »

Could this be causing high CPU load? - We've hitting 100% with about 150 online...

Surely that shouldn't use so much process?

VPS Spec:
Guaranteed RAM 256 MB
Burstable RAM (up to) 1024 MB
Operating system: Linux 2.6.9-023stab051.3-smp
CPU: Authentic AMD, Quad-Core AMD Opteron(tm) Processor 2352
Average load: 6.25; 6.81; 5.81

It's majority traffic on httpd service... which is odd!

Cheers,
Mike
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Slow Index Loading

Post by Oleg »

The query in question examines 256*7872 = 2m rows each time it runs.

Try adding the bold code to this line

Code: Select all

		'ON'	=> "f.forum_last_post_id = t.topic_last_post_id AND t.topic_moved_id = 0"
and see how much difference it makes.

Post back results of explain after the change.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Slow Index Loading

Post by Oleg »

MichaelBuckle wrote: Average load: 6.25; 6.81; 5.81
Is that load for just your vps or the entire server? 8gb servers are incredibly cheap nowadays, and such a server would fit 32 vpses at 256mb each (what you have allocated).
We've hitting 100% with about 150 online...
With 150 concurrent users each needing 2m rows examined I can see how you could bring your box to its knees.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
MichaelBuckle
Registered User
Posts: 13
Joined: Tue Oct 06, 2009 3:39 pm

Re: Slow Index Loading

Post by MichaelBuckle »

nn- wrote:
MichaelBuckle wrote: Average load: 6.25; 6.81; 5.81
Is that load for just your vps or the entire server? 8gb servers are incredibly cheap nowadays, and such a server would fit 32 vpses at 256mb each (what you have allocated).
We've hitting 100% with about 150 online...
With 150 concurrent users each needing 2m rows examined I can see how you could bring your box to its knees.
That's in the Plesk control panel so I assume our VPS load, we're happy to upgrade to more Ram, I've emailed the support for them to look into it, but if ram is going to help with the load we're happy to do so.

Thanks nn, I will give that ago on our dev forum now.
MichaelBuckle
Registered User
Posts: 13
Joined: Tue Oct 06, 2009 3:39 pm

Re: Slow Index Loading

Post by MichaelBuckle »

I think you may have cracked it nn-! Queries are alot quicker, loading speed is much improved!

Genuis! Thanks :).
Locked

Return to “[3.0.x] Support Forum”