Fairly serious performance issue – auto-loaded options

My quick one-time fix to make the specific site I’m working on load faster – 321 less rows fetched from the database on every page load!

mysql> update wp_options set autoload=’no’ where option_name like ‘_site_transient%brute_%’;
Query OK, 321 rows affected (0.72 sec)
Rows matched: 321 Changed: 321 Warnings: 0

For context, it’s interesting to know how many site transients there are, to see how much of the pie BruteProtect is eating….

mysql> select count(*) from wp_options where option_name like ‘_site_transient_%’;
+———-+
| count(*) |
+———-+
| 328 |
+———-+

Afterwards:

mysql> select count(*) from wp_options where autoload=’yes’;
+———-+
| count(*) |
+———-+
| 709 |
+———-+
1 row in set (0.09 sec)

i.e. A one-third reduction in auto-loaded options was achieved.

David, are you saying BruteProtect loads 321 rows from the database on every page, or every time someone accesses the login page?

What’s the downside to your autoload fix?

Every page. WordPress auto-loads all options that are marked with autoload=yes on every page. (N.B. It’s WordPress that does the loading, not BruteProtect).

As it’s a common query, it’s likely to be in your MySQL query cache, I’d have thought. But it’s still a lot of useless back-and-forth.

David

Hey David– yikes! I’m working on a patch for core, hopefully they can fix this in 4.0.1, but certainly for 4.1.

We’re also looking at a way to fix this manually in the interim.

Hey David– just so you know, we have built out a patch for this issue until WordPress core can be fixed. This patch will be included in the next version of BruteProtect

Hi Sam,

Thanks – that’s encouraging.

I had been thinking – “hmmm, these guys don’t really care any more, they must have moved on” – but then a couple of weeks ago it became clear what had been keeping you busy; congratulations!

David

It looks like this bug still exists. I am running WP 4.0 and the latest version of this plugin, and I found that BruteProtect was keeping almost 45,000 rows in my wp_options table with autoload = ‘yes’. I changed them all to ‘no’ and added the following code to my functions.php file to repair future options immediately after BruteProtect sets them.

function dc_fix_BP_site_transients( $option, $value ) {
	global $wpdb;
	if ( stristr( $option, "_site_transient_brute_loginable_" ) ) {
		$transient_fragment = substr( str_replace( "_site_transient_", "", $option ), 0, 40 );
		$result = $wpdb->query("
			UPDATE {$wpdb->options}
			SET autoload = 'no'
			WHERE option_name LIKE ( '%{$transient_fragment}%' )
		");
		if ( FALSE === $result ) {
			error_log("Error trying to repair site transient {$option}");
		} else {
			//error_log("Successfully repaired site transient {$option}");
		}
	}
}
add_action( 'added_option', 'dc_fix_BP_site_transients', 10, 2 );

I hope this helps someone until there is a better workaround built in to the plugin.

The code in my last comment is not working consistently, sometimes it fires, other times it does not. Revising it now.

I never got my original approach to work because it looks like using the set_site_transient() function prevents these action hooks from being called, so there is no opportunity to fix the transient right after it’s inserted. So instead I decided to schedule a custom cleanup function every 15 minutes. I had to define a custom cron schedule option in order to do this. Code below:

// define a custom shorter cron interval
function dc_customize_cron_schedules( $schedules ) {
	$schedules['every15'] = array(
		'interval' => 60 * 15,
		'display' => __( 'Every 15 Minutes' )
	);
	return $schedules;
}
add_filter( 'cron_schedules', 'dc_customize_cron_schedules' );

// make sure cron job is set
function dc_setup_cron_schedule() {
	if ( ! wp_next_scheduled( 'dc_cron_every15' ) ) {
		wp_schedule_event( time(), 'every15', 'dc_cron_every15');
	}
}
add_action( 'wp', 'dc_setup_cron_schedule' );

// fix site transients autoload bug for BruteProtect
function dc_cron_fix_BP_site_transients() {
	global $wpdb;
	$result = $wpdb->query("
		UPDATE {$wpdb->options}
		SET autoload = 'no'
		WHERE option_name LIKE ( '%_brute_loginable_%' )
	");
}
add_action( 'dc_cron_every15', 'dc_cron_fix_BP_site_transients' );

Hi,
Has there been a fix released for this?
I’ve got thousands of entries, all with autoload = yes.

Cheers

Paul

Hi Paul– unfortunately, this has exposed a deeper flaw in the way that WordPress handles site options in multisite installs. We’re working on developing a solution, but it’s not something that we can fix solely within BruteProtect at the moment.