Support Forum
Support Forum
Offline
My server has been dropping to it's knees (with less than 50 concurrent users which should never happen) and as a result i've been doing a lot of troubleshooting and performance tuning. MySQL process starts to spike at 500%+ and then once loads get's around 10 average it's keeps going. Around 35 load average the server is frozen and i have to sent a request to softlayer to manually reboot into single user mode with ssh to slowing start up the services again.
I've discovered hundreds of long running my sql queries from 15-40 seconds long. The primary culprit is this one
mysql> SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id, wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id, topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum_id = wp_sfposts.forum_id JOIN wp_sftopics ON wp_sftopics.topic_id = wp_sfposts.topic_id LEFT JOIN wp_sfmembers ON wp_sfmembers.user_id = wp_sfposts.user_id WHERE wp_sfforums.forum_rss_private = 0 AND wp_sfposts.forum_id IN (2,3,4,7,8,9,10,11,12) ORDER BY wp_sfposts.post_id DESC LIMIT 15;
What does this code do? Where is it called from? How can i fix this?
Web site: http://www.fantasyfootballxtre.....com/forums (rather large forum compared to many of your installs i believe)
Environement
-----------------------
| Simple:Press |
Version: 5.1.3 Build: 8972 Release: Release |
|
| WordPress |
Version: 3.4.1 Language: en-US Character Set: UTF-8 Theme: |
|
| PHP |
Version: 5.2.12 Memory: 128M Max Upload: 6M Timeout: |
|
| MySQL |
Version: 5.0.95 Prefix: wp_ |
|
| Server | Version: Apache/2.2.14 (Unix) mod_ssl/2.2.14 OpenSSL/0.9.8e-fips-rhel5 mod_bwlimited/1.4 mod_perl/2.0.4 Perl/v5.8.8 | |
| WP Plugins |
Add Link to Facebook (1.163) Akismet (2.5.6) Gold Cart for WP e-Commerce (2.9.7) Google XML Sitemaps (3.2.8) Newsletters (3.9.1) Peter's Login Redirect (2.5.3) runPHP (2.3.1) Simple:Press (5.1.3) SN FFX - Admin Panel (1.0.0) Sports Notion System (1.0.0) WP e-Commerce (3.8.8.3) WPSC Members Access (2.0) WP Super Cache (1.1) |
|
| SP Plugins |
Admin Bar (1.1.1) Editor QuickTags BBCode (1.1.1) Editor TinyMCE Rich Text (1.2) Post Ratings (1.1.3) Private Message System (1.2.1) Profanity Filter (1.1.1) Profile Display Control (1.0.2) Prune Database (1.0.2) Report Post (1.1.2) Subscriptions (1.1.3) Watches (1.1.3) Who's Online (1.1.3) |
Offline
That's an RSS instigated query - I will need to check which flavour but I am 99% sure it is an RSS feed query. I will find out which one.
 |
YELLOW
SWORDFISH
|
Offline
Yes - that is the 'All' RSS feed query.
It's not a particularly heavy or complex query and certainly shows no red flags upon monitoring.
|
YELLOW
SWORDFISH
|
Offline
On my system it's runs very heavy. Have all of your tables been join and index optimized? How often does this query run? I have to resolve this issue immediately. It's an emergency. Is there a way to turn off this query or turn off RSS?
# Time: 120821 21:52:45
# Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
# User@Host: snffx_www[snffx_www] @ localhost []
# Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
# Query_time: 26 Lock_tme: 0 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
# Time: 120821 21:52:54
# Query_time: 35 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
# Query_time: 65 Lock_time: 30 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
# Query_time: 35 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
# Time: 120821 21:53:00
# User@Host: snffx_www[snffx_www] @ localhost []
# Query_time: 41 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT wp_sfposts.post_id, post_content, DATE_SUB(post_date, INTERVAL 2 HOUR) as post_date, wp_sfposts.topic_id, wp_sfposts.forum_id,
wp_sfposts.user_id, guest_name, post_status, post_index, forum_name, forum_slug, group_id,
topic_name, topic_slug, wp_sftopics.post_count, display_name FROM wp_sfposts JOIN wp_sfforums ON wp_sfforums.forum$
Offline
I might be onto something. Is your query triggered by cron job? Does this line of code trigger the long running query?
$rss = fetch_feed('http://www.fantasyfootballxtreme.com/forums/rss/d3d98b8e-dfd0-40fc-a37e-efc3d05a0743/');
I have a forum activity module in my page so i can show current activity. I couldn't figure out how to do this correctly with your code so i just grabbed it as a feed. Maybe because i was running this on ever page load, even though i was caching, it was calling your RSS code? here was my code. Do not try this at home.
include_once(ABSPATH . WPINC . '/feed.php');
function cache_duration( $seconds )
{
// change the default feed cache recreation period to 15 minutes
return 900;
}
// Get a SimplePie feed object from the specified feed source while controlling cache time
add_filter( 'wp_feed_cache_transient_lifetime' , 'cache_duration' );
$rss = fetch_feed('http://www.fantasyfootballxtreme.com/forums/rss/d3d98b8e-dfd0-40fc-a37e-efc3d05a0743/');
remove_filter( 'wp_feed_cache_transient_lifetime' , 'cache_duration' );
if (!is_wp_error( $rss ) ) : // Checks that the object is created correctly
// Figure out how many total items there are, but limit it to 5.
$maxitems = $rss->get_item_quantity(5);
// Build an array of all the items, starting with element 0 (first element).
$rss_items = $rss->get_items(0, $maxitems);
endif;
?>
<ul class="vertical">
<?php if ($maxitems == 0) echo '<li>Trouble loading... Try refreshing the page.</li>';
else
// Loop through each feed item and display each item as a hyperlink.
foreach ( $rss_items as $item ) : ?>
<li class="cat-item forum-item-">
<a href='<?php echo esc_url( $item->get_permalink() ); ?>'
title='<?php echo 'Posted '.$item->get_date('j F Y | g:i a'); ?>'>
<?php echo esc_html( $item->get_title() ); ?></a>
</li>
<?php endforeach; ?>
</ul>
</div>
Offline
It's not a cron job it is on request but yes - that code will run it. For whatever user that feedkey belongs to.
|
YELLOW
SWORDFISH
|
Offline
show current activity?? do you just want to show the latest posts? we provide multiple template tags for doing that in the template tags plugin...
Visit Cruise Talk Central and Mr Papa's World
All RSS1 Guest(s)