Jump to content

Server Resource Issues


Guest DrumCorpsFan

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...