Well @mr-papa is much more knowledgeable than me when it comes to WP-MS so I am not the best person to respond – he will be back, all being well, on Sunday.

So – the usermeta table. Do the users and usermeta tables contain entries for each database in the MS environment or are they kept database by database? Just trying to understand the size of them here…

The query you have there should – as far as I can see – do exactly what you want – remove the orphaned rows. Are you saying that it did not do so? Also remember that many plugins may well add usermeta data. It is very common.

As to the ‘sleep’ issue. I am looking at this. First up is this similar question: https://stackoverflow.com/questions/12194241/show-processlist-in-mysql-command-sleep

You will see that respondent ‘Rufinus’, after his answer, also gives 2 further links: https://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-too-many-connections – and – https://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep

Does that help?

Actually Jim – this is as pretty good and simple description of the sleep issue: https://www.znetlive.com/blog/what-is-sleep-query-in-mysql-database/

As far as I understand things when a php script ends then connections are automatically closed. SO under WordPress this would be at the final actions. Whether it performs an implicit ‘close’ I don’t know but I will try and find out,

Yellow Swordfish said …the usermeta table. Do the users and usermeta tables contain entries for each database in the MS environment

WordPress Multisite maintains data for all sites in one database. We are not running any sort of multi-db environment. The wp_usermeta table contains meta data for all users of the network (all sites). So, we are only dealing with one databse.

FYI: Our network currently has 10,500+ registered users, with 1200+ sites.

The query you have there should …remove the orphaned rows. Are you saying that it did not do so?

It did reduce the table size from 27.2+ Million rows to 26.8± Million rows, so it did remove something…just wondering if there are ways to identify (and remove) any other potential detritus. The table just seems huge, and the load/resource issue points to it for some reason.

Also remember that many plugins may well add usermeta data.

Understood. Guess I’m seeking advice for how to find orphan usermeta data in general. I can repeat the above process to keep deleting users and their data, but if there is a way to find SF (or other plugin) metadata, I’m all ears…

For instance, any way to search for metadata stored by SF plugins/add-ons for all users which we may no longer be using. This old notice about custom profile fields got me thinking.

As to the ‘sleep’ issue…

Thanks for the leads! Will check those out.

mmm. Out of curiosity I just counted my own usermeta entries – 95 !!! This is partly because I am an admin of course but so may of those entries are put there by plugins and even the WP theme.

As far as I am aware we only use it for the profile fields – which are quite a small number.

Yellow Swordfish said
…we only use it for the profile fields – which are quite a small number. 

Indeed. I only found four meta_key entries for Custom Profile Fields we once used. I confirmed that SF plugin is no longer active, and deleted a few hundred values to save a whopping 50.7 KiB.

Does that help?

Yes! Thank you pointing me in the right direction. By digging deeper for any SF meta_value I discovered a plugin that has apparently been exponentially creating meta_value entries. After deleting 2.2+ Million rows from wp_usermeta, the table size reduced from 192+ MB to  just 20 MB.

Hopefully that explains and addresses the resources being eaten up by the wp_usermeta query above. I’ll need to investigate the mysterious sleeping business further if that persists to cause trouble.

@mr-papa may enjoy reviewing this and chiming in, if only for entertainment’s sake. As always, any additional feedback is always welcome.

Thanks again.

Wow! 2.2million? That is grim…

The other WordPress table that can give problems is the (wp_)options table. This can grow almost exponentially with the use of transient records that do not get removed. And it is worth remembering that this table is accessed and queried on every page load.

There are a few plugins that you can use to clean this table up – such as Transients Manager by Pippin Wiliamson. There is WP-Sweep by Lester Chan that will perform many other clean-ups as well. Both Pippin and Lester are long time and respected plugin authors.

In my opinion – it would be worth trying out one of these on a test system/database if you have not done so already. I would be interested to know how that table reduces in size!

By the way – you probably know this already – but after a mass delete like the one you have done on usermeta, it is always worth performing a table optimise to clean things up.

Yellow Swordfish said

I would be interested to know how that table reduces in size!

Thanks again, sorry for the late reply.

I perform regular optimization via phpMyAdmin to delete transients and perform other cleanup.

After wrangling that rogue usermeta and performing other dbd tuning I was able to restore stable performance of the site. The biggest improvement came after converting all tables to InnoDB. (That was fun with 14,000+ tables.) And turning PHP7 back on after NextGen finally released a compatible version. Long story…

Quick related question: Am I correct in assuming that the only way to reduce the size of wp_sfposts is via pruning old posts? And does this table get queried on every /forums page load? Guess that’s two questions…

yes, we have found innodB better ourselves…

yes, sfposts contains all your posts in the forums…  to reduce size, you would need to remove old posts…  just a question of how much old posts you want to keep around for historical purposes…  our prune database plugin is useful for pruning if you go that way…

as to when its loaded, it depends…  and we dont generally load a whole table – only a very small subset…  so for example on topic view, we only load the number of rows from that table that is visible on that page (ie 20 posts)…  however, in some cases, depending on the complexity of the query, it can actually be faster to load the whole table rather than do limits, subqueries, etc…  but I dont think the sfposts table is one where we do that…

also, keep in mind, that its not generally a real big deal for php/mysql to deal with tables that have millions of rows…  depends what one does with the data and why you would load a whole table of that size… 

Mr Papa said
sfposts…we dont generally load a whole table

Smart! Thanks again for the good work and helpful feedback.

np – glad to help….

A plugin will delete expired or all transients. I use this one Optimize Database after Deleting Revisions. https://wordpress.org/plugins/rvg-optimize-database/