WordPress MU

Greetings,

My network team tells me there's excessive database queries run by our web servers, the results for which are being 'dropped to the floor':

Each of the 4 blog webservers are pulling in ~10Mbit peak from the database but are only serving ~2Mbit each. The cumulative wasted total of ~32Mbit peak is evaluated by the web servers (CPU intensive) and then dropped on the floor. Graphs below. This behavior is not new and has been happening since the conversion [to wpmu-1.3.3], possibly since inception.

The probable root cause has been determined to be from the following if statement in the get_alloptions() function (wp-includes/functions.php). For many of the blogs each test for !$options brings ~500K to the web server which is used only for the test.

Here's the code block to which he's referring:

if ( !$options = $wpdb->get_results("SELECT option_name, option_value FROM $wpdb->options WHERE autoload = 'yes'") ) {
	$options = $wpdb->get_results("SELECT option_name, option_value FROM $wpdb->options");
}

I imagine this similar line in wp_load_alloptions() may also cause grief:

if ( !$alloptions_db = $wpdb->get_results("SELECT option_name, option_value FROM $wpdb->options WHERE autoload = 'yes'") )
	$alloptions_db = $wpdb->get_results("SELECT option_name, option_value FROM $wpdb->options");

Any ideas how we can resolve or minimize this issue?

Thanks!

Clay

I think we can reduce the code to

$options = $wpdb->get_results("SELECT option_name, option_value FROM $wpdb->options WHERE autoload = 'yes'");

This is because we are only concerned to autoload options...

But I doubt if it will add to its performance because for most of the time, the if logic of the original code results to false because the database lookup is always successful because it can always retrieve options with autoload=yes from the db...

well, i don't know from here...maybe a little prayer would help?

I believe this issue can be made somewhat moot by clearing the plethora of rows in "wp_*_options where option_name like 'rss\_%'".

In my case, I've got two blogs with more than 2MB in its wp_*_options table. The third largest blog is close, with 1999616 bytes. It's got 28 rows that match "wp_*_options where option_name like 'rss\_%'".

I'd like to run the following SQL, but I'd feel more comfortable if I received confirmation of its goodness (or a better method!), from one of the WPMU heavies:

for table in ^mysql -uwpmu -pxxxxxxx wpmu -Bse "show tables like 'wp_%_options'"^; do
mysql -uwpmu -pstdh8zmi wpmu -Bse "DELETE from $table where option_name like "rss\_____________________________"
done

NOTE: I used '^' where a backtick should go.

This was discussed on wordpress.org and TechPedia and someone even created a Clean Options plugin to manage orphaned options...

Any idea if this little gotcha will be 'fixed'? It'd be nice to have a 'wp-cron' check to see if there are any extraneous table rows clogging up the pipes...
`

Those fields are the cache for incoming RSS feeds. Like on the dashboard, rss widget, etc.

No, they aren't exactly efficient, but that's what they are.

Thanks for the reply!

Is there a plan for deleting old versions of the cache? Does it make sense to add a button to clear them? Is it 'safe' to clear the cache?

What its intended to do is cache the remote feed, therefore hitting your database instead of the remote site however many times. It's updated at an interval, etc.

It should be clearing/updating on its own, and if you clear it yourself they will just be replaced.

I can't say for sure, but there may be an issue where if the feed link is changed, then it doesn't remove that feed from the database. It's something you would have to dig into though.

In short, any rss widget will have an entry, as well as the dashboard feeds as well. In MU's case, it would be nice if the two dashboard feeds could be stored globally, but with 1.5/2.5 and allowing the end user to change them, it's probably no longer an option unless you add in a check for the default feed links, and if so grab it from a global table.

Do you see any issues with my sql query to DELETE from {wp_*_options} WHERE option_name like 'rss\_%':

for table in ^mysql -uwpmu -pxxxxxx wpmu -Bse "show tables like 'wp_%_options'"^; do
mysql -uwpmu -pstdh8zmi wpmu -Bse "DELETE from $table where option_name like "rss\_*"
done

NOTE: I used '^' where a backtick should go.

Or do you think the Clean Options plugin would be better?

We ran the above bash/SQL query and the amount of database network traffic has gone down considerably. I suspect that the RSS feed cache rows in wp_%_options was orphaned by subsequent upgrades:

• wpmu-1.0 to wpmu-1.2.1
• wpmu-1.2.1 to wpmu-1.2.5a
• wpmu-1.2.5a to wpmu-1.3.3