Slow Index Loading

[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.

[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]

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.

[Oleg]

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.

[MichaelBuckle]

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]

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

Genuis! Thanks .