DrumCorpsFan Posted January 29, 2008 Share Posted January 29, 2008 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 More sharing options...
bfarber Posted January 30, 2008 Share Posted January 30, 2008 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 More sharing options...
DrumCorpsFan Posted January 30, 2008 Share Posted January 30, 2008 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 More sharing options...
bfarber Posted January 31, 2008 Share Posted January 31, 2008 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. Link to comment Share on other sites More sharing options...
DrumCorpsFan Posted February 7, 2008 Share Posted February 7, 2008 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 More sharing options...
bfarber Posted February 8, 2008 Share Posted February 8, 2008 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.