Support Forum
Support Forum
Offline
Hope y'all are enjoying a well deserved vacation. Thanks in advance for any help!
The Tripawds forums are basically offline until I resolve some serious database issues randomly bringing our server down. We've performed some serious server configuration tuning, and my server manager has identified "the database" as the problem.
Specifically, with SimplePress active we see numerous resources being eaten up like this:
112666 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Sleep | 62 | | NULL |
| 112764 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Sleep | 16 | | NULL |
| 112925 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Query | 3 | Creating sort index | SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (5517) ORDER BY umeta_id ASC |
| 112946 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Sleep | 16 | | NULL |
| 112951 | tripawds_wpmu1 | localhost | tripawds_wpmudb0709 | Sleep | 18 | | NULL |
FYI: In pruning and tuning our database, following directions received here (at least a couple years ago) I used phpMyAdmin to delete numerous inactive sfmembers, and then delete registered site users who were not sfmembers.
I then ran:
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users)
Our wp_usermeta table alone, however, still has some 2.6+million rows weighing in at 210+MB.
Since @mr-papa has been very helpful here over the years with similar issues, I'm looking for guidance to help reduce the size of our wp_usermeta table. More specifically, how might I locate and delete any orphan SF data stored in usermeta. Any guidance regarding that sleep business is also greatly appreciated.
NOTE: In my latest tests, I locked the forums and deactivated other plugins, then blocked any other visitors to the site with a Maintenance Mode screen. Server load remained stable at 3± for 12+ hours. I then opened the site to allow traffic (which logs show did increase /forums page views) but kept the forums locked. With me alone loading topics and replying I watched server load triple. Load immediately dropped when I deactivated SimplePress.
Discuss...
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
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/ques.....mand-sleep
You will see that respondent 'Rufinus', after his answer, also gives 2 further links: https://stackoverflow.com/ques.....onnections - and - https://dba.stackexchange.com/.....s-to-sleep
Does that help?
 |
YELLOW
SWORDFISH
|
Offline
Actually Jim - this is as pretty good and simple description of the sleep issue: https://www.znetlive.com/blog/.....-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
|
Offline
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.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
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
|
Offline
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.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
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
|
Offline
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...
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
Offline
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...
Visit Cruise Talk Central and Mr Papa's World
Offline
Mr Papa said
sfposts...we dont generally load a whole table
Smart! Thanks again for the good work and helpful feedback.
Simple:Press powers the Tripawds Discussion Forums.
It's better to hop on three legs than to limp on four.
The Tripawds Blogs Community is made possible by The Tripawds Foundation.
All RSS1 Guest(s)