Server Resource Issues

[DrumCorpsFan]

I've been having a number of issues recently on my server which normally result in the system (RHES Rel 4 - dedicated) starting to swap pretty heavily - then down that slippery slope into oblivion. Watching 'top' shows me that before this happens, it appears that mysqld is running at >100% CPU and multiple httpd processes have stacked-up.

We were able to catch a snapshot of the mySQL process list during a couple of these incidents and they've all looked very similar. Here's one example:

73333 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	35 	Locked 	UPDATE main_topics SET posts=8,topic_queuedposts=0,last_poster_id=19738,last_poster_name='drummerdud

73335 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	38 	Sending data 	SELECT p.pid, p.queued, t.approved, t.forum_id FROM main_posts p LEFT JOIN main

73349 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	35 	Locked 	SELECT * FROM main_topics WHERE tid=108863

73350 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	35 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=290 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.p

73353 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	34 	Locked 	SELECT * FROM main_topics WHERE tid=109018

73355 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	34 	Locked 	SELECT * FROM main_topics WHERE tid=108860

73358 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	33 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73369 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	32 	Locked 	SELECT t.*, p.poll_only FROM main_topics t LEFT JOIN main_polls p ON ( p.tid=t.tid ) WHERE t.foru

73373 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	32 	Locked 	SELECT t.*, t.title as topic_title FROM main_topics t WHERE t.approved=1 AND t.state !='link' AND t

73374 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	31 	Locked 	SELECT * FROM main_topics WHERE tid=62667

73379 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	30 	Locked 	SELECT * FROM main_topics WHERE approved=1 and forum_id=3 ORDER BY pinned desc, last_post desc LIMIT

73380 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	29 	Locked 	SELECT * FROM main_topics WHERE tid=52111

73383 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	28 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73386 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	27 	Locked 	SELECT * FROM main_topics WHERE tid=107826

73388 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	27 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73389 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	26 	Locked 	SELECT * FROM main_topics WHERE tid=108863

73392 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	26 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=290 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.p

73394 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	25 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73395 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	25 	Locked 	SELECT * FROM main_topics WHERE tid=108972

73396 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	24 	Locked 	SELECT * FROM main_topics WHERE tid=108863

73398 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	23 	Locked 	SELECT * FROM main_topics WHERE tid=109018

73400 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	23 	Locked 	SELECT * FROM main_topics WHERE tid=109154

73402 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	22 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=290 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.p

73405 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	21 	Locked 	SELECT t.*, p.poll_only FROM main_topics t LEFT JOIN main_polls p ON ( p.tid=t.tid ) WHERE t.foru

73408 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	21 	Locked 	SELECT * FROM main_topics WHERE tid=109131

73411 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	20 	Locked 	SELECT * FROM main_topics WHERE tid=108967

73414 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	18 	Locked 	SELECT count(*) as count FROM main_posts p LEFT JOIN main_topics t ON (t.tid=p.topic_id)

73415 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	17 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73420 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	15 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=45 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pi

73421 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	14 	Locked 	SELECT count(*) as count FROM main_topics WHERE approved=1 AND state != 'link' AND forum_id IN(59,29

73423 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	13 	Locked 	SELECT * FROM main_topics WHERE tid=108963

73426 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	12 	Locked 	SELECT * FROM main_topics WHERE tid=100282 and approved=1

73430 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	11 	Locked 	SELECT * FROM main_topics WHERE tid=108874

73432 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	10 	Locked 	SELECT * FROM main_topics WHERE tid=107717

73433 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	10 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73434 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	9 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=4 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73438 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	7 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=290 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.p

73442 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	5 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=290 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.p

73450 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	3 	Locked 	SELECT * FROM main_topics WHERE tid=107717

73454 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	2 	Locked 	SELECT * FROM main_topics WHERE tid=73029

73456 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	2 	Locked 	SELECT count(*) as count FROM main_topics WHERE approved=1 AND state != 'link' AND forum_id IN(59,29

73459 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	1 	Locked 	SELECT * FROM main_topics t WHERE t.forum_id=3 AND t.pinned IN (0,1) and t.approved=1 ORDER BY t.pin

73460 	dcp_forumdb 	localhost 	dcp_dcpforum 	Query 	0 		SHOW PROCESSLIST

In the incidents we were able to catch, the query on the second row (SELECT p.pid, p.queued, t.approved, t.forum_id FROM main_posts p LEFT JOIN main) existed and was in the "Sending Data" state ... and all other processes were "Locked".

I'm wondering:

1) why the entire query is not shown - as "main" is not a valid table name.
2) why it seems this particular query is at the center of this 'lock-up'.
3) what can be done to fix this problem

I've checked the status of the database, run the index checker and fixed the problems that it found, and stopped/started both httpd and mysqld several times. The capture above was done AFTER these measures had been taken.

If someone has insight into this, please reply. The wisdom of your experience will be appreciated.

-john

[bfarber]

Firstly, I'd look at mysql config settings to see if you can optimize it some more first.

You ran "Show processlist" - you need to run "Show full processlist" for the full queries. I suspect that query is coming from the "Topics" tab in the profile portal if I had to guess, though it could be a search too. The full query would help diagnose the problem. How many posts do you have?

[DrumCorpsFan]

Thanks, Brandon. I have > 1.1M rows in the main_posts table - so there are a few there. Typical days see 300-500 users online at any given time throughout the day - sometimes more. Everything is running on the single dedicated server (Dual XEON 2.8Ghz/2G/RHES) during our 'off season', then during the summer months we add a dedicated database server to the mix and move all databases out there. Costs prohibit that year-round.

Catching the condition is sometimes difficult, but I'll try to do so with the FULL processlist. SQL tuning is next.

[bfarber]

If it turns out to be a search query, first thing I'd recommend is to use Sphinx for searching. The only issue I could forsee with it is the mixup configs you'd need to do when you add in the extra database server, but it would still be worth the trouble.

[DrumCorpsFan]

Thanks for the info. I realized that I had not implemented Full Text Search on my database, so that's now in-place. After doing some basic benchmarking, it definitely improves the searches - which is the most logical explanation for the intermittent heavy loads.

We'll continue to monitor and evaluate performance. If Sphinx is the next step, we'll proceed if we see additional problems.

Thanks again.

[bfarber]

Sounds good.

As a note, sphinx is overkill unless you have at least 100,000 topics generally (it won't hurt anything, but the gains will be minimal as MySQL is usually ok up to that point at least), however it provides huge performance improvements for search as the site gets larger beyond that.