mySQL Options table flooded with 10,000 values; database bloated

This plugin, and many others, use transients in the options table to store cache. Assuming those that have bloated your table belong to this plugin then you can simply get rid of them by turning off the caching (although you’ll have to wait for the existing ones to expire, depending on how long you set the caching for).

Not a huge amount if cached – just the code that the plugin outputs – but this can aid with performance, so quite why your table has inflated to much I don’t know. However, each variance will create a cache – so each post and page, for instance, will.

David.

Thanks, David.
Yes, this would be consistent with the number of pages viewed daily by real users and search engines. I’m guessing that the plugin is creating a ~3KB mySQL record for each of 10,000 pages on my site (with an option_name something like: %_transient_timeout_asb_%)

From your plugin description, the default is for cached entries to last 24 hours.
I will therefore modify my code to add a cache=false statement, and then confirm that the cache has been cleared.

Peter

PS This is one of the very best social media plugins—unlike many of the others that create a crippling delay in page load time! Thanks for your work.

David,
I edited my template to add the cache=false statement yesterday, but today the cache does not seem to have cleared, and there are thousands of additional lines in my option table, generated by the ASB plugin.
I tried manually loading a single page in my browser several times, but none of the new lines in the database corresponded to this page (are these database entries just coming from search engine crawling?).
I am also running WP Super Cache, just in case this makes any differences.

Any ideas how I could troubleshoot? THX.

Did you clear it when you edited your template? If not, the old cache may still be lingering.

Instead of changing the template you can simply modify the cache time in the YouTube Embed options screen. There are 2 caches, so it may be one rather than the other.

David.

Sorry, I don’t how to actively “clear” the cache.
I am not using youtube, just four social media buttons+email button.

All I did was add the cache=false statement to the line of php code in my template. This is the only place I use the social_bookmarks code. It is placed above and below the loop:

<?php echo social_bookmarks( 'default=off&facebook=on&twitter=on&googleplus=on&pinterest=on&email=on&iconfolder=xmyfoldernamex&cache=false&target=_new' ); ?>

THX, Peter.

Apologies, I got confused with my plugins!?!

Looking at the code I think it’s maybe not turning off as you’ve requested. If you open up the file asb-generate-bookmarks.php in the plugins’ include folder, find the line…

if ( $cache_time !== false ) { $echoout = get_transient( $cache_key ); }

And change it to…

if ( $cache_time != 'false' ) { $echoout = get_transient( $cache_key ); }

Now look for the line…

if ( $cache_time !== false) { set_transient( $cache_key, $echoout, 3600 * $cache_time ); }

And change it to…

if ( $cache_time != 'false' ) { set_transient( $cache_key, $echoout, 3600 * $cache_time ); }

This may help – certainly it should allow you to turn off cache now.

Otherwise, I’ll be looking at this in the next release and giving it an overhaul.

David.

OK. THX.

David,
Yes, your suggested code now prevents the proliferation of new entries in the options table.

There are, however, about 25,000 old entries in the table: should I delete these, and if so, how? THX.

Theoretically they should go when the cache expiry ends. Are you sure they belong to this plugin? The name in the options table should include the text asb_

David.

Yes,
All the entries I am referring to include _asb_ in the option_name and half of these have “Artiss” in the option_value.
My main question is whether these might affect the performance of the site in any way, so that I can decide whether to delete them.
THX, Peter

Assuming you have access to MySQL, the following query should clear them down…

DELETE FROM wp_options WHERE option_name LIKE '_transient_asb_%'

I’ve assumed your table prefix is wp_, otherwise you’ll need to change this.

I’ll put this functionality in the next release of the plugin (i.e. an option to allow you to clear any down).

David.

Thanks for helping me clear up this glitch with this excellent plugin!
Regards, P.