Support Forum
I have been doing quite a bit of housecleaning in our database lately, trimming lots of fat.
Is there any way to reduce the size of wp_1_sfposts other than pruning? I know there is an admin tool for pruning the database of old topics. But does this table include any transient data or other artifacts that could be safely cleaned to reduce it's size? It is currently the largest table in the db by far, weighing it at a whopping 120 MB!
On that note, our wp_1_sfmembers table is 33+ MB with 4,865 rows. Same question: Can anything be done to reduce it's size? And on a related note, will deleting users via the WP admin delete them from this table? If not, we've got a lot of ghosts lurking in there – any way to find and get rid of them?
Thanks in advance for any help developing queries to reduce the size of these tables. Or, are these numbers normal and just a fact of life for a site with 5,000+ users, approaching 100,000 forum posts?
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.
those sizes are nothing... I am working on one site now with one table at 22 million rows (wp post meta)... the wp posts table is only 700k rows... 
you nailed it though... every forum post you have is a row in that table... and the size of each row is going to be driven by how many characters in that post... so only way to reduce is to prune older topics/posts... though not sure you really need too... except eventually your mysql db will be too big to dump... but replication is a better option anyways...
Visit Cruise Talk Central and Mr Papa's World
Yes - deleting a user via the WP Admin 'Users' panel also removes that user from the Simple:Press Members table. The SP sfmembers and WP users tables should have the same number of rows (although it is possible for some older SP forums to not be 100% the same). Not sure about multi-site either come to that!
We do have a plugin available that attempts to identify and optionally remove all 'spam registrants' - that is people who have registered on your site but never contributed any post or comment.
Also - when you say 'housecleaning' - are you actually also running a MySQL optimise on your tables? This is something I would recommend be done regularly - once a week say - and gets even more important as the tables get larger.
 |
YELLOW
SWORDFISH
|
Mr Papa said
only way to reduce is to prune older topics/posts...
As I suspected, thank you for the clarification
Yellow Swordfish said
are you actually also running a MySQL optimise on your tables?
That, and them some. It had been a long time since doing any db maintenance (thanks for the reminder to do it more often). I was also able to delete more than a GB (yes gigabyte) of unnecessary data by running queries to delete old transients, commentmeta and post revisions.
Our sfmembers and users are indeed out of sync since not all members of the network are users of site_1 where we run the forums. But I would like to attempt to reduce the size of that table by deleting old sfmembers who have never posted and not visited since a certain date, or in over x days.
This appears like it will do the trick:
In our case, this will remove 1,750+ rows. Before I run it, however, I'm just wondering if any member related data (for these folks who have never posted) resides in other sf tables – auths, memberships, permissions, etc? Most of these other sf tables are small, but if I'm cleaning out members I do want to delete all of their related data. If I do need to delete rows from other tables, I could use some help editing this query.
(NOTE: I do realize this will not delete these sfmembers from wp_users. That's OK, since our user table us only 850±kb, which I find odd since sfmembers is 30+MB.)
Aside from this, it sounds like our numbers are not unexpected nor uncontrollable for the site/forums of our size.
Thanks again for the feedback and help.
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.
Correction: My actual query is as follows (I forgot to delete the * from my SELECT query):
DELETE FROM wp_1_sfmembers WHERE posts = "0" AND lastvisit < "2013-01-01 00:00:00"
Also, just curious... what's that "REMOVE MARKER" business all about, in the post editor that does not appear and Mr Papa's quote is not italicized... 
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.
Yes - there is data in other tables - as you assumed.
Again - our SP plugin to remove 'spam registrations' will remove all data associated with a user although this will include the WP user record as well.
We are having a little problem with quoting at the moment so am working on that. But I have no idea where this 'remove marker' bit comes from. Steve?
|
YELLOW
SWORDFISH
|
Yes - there is data in other tables ...
Hmmm... any pointers as to what in which rows? I'd hate to remove 1800± sfmembers then have all their transient data hiding somewhere. I'll take a loo at the spam reg plugin, but didn't care to remove the users record since some may have sites on our network.
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.
OK, I think I'm making some progress. After dissecting the spam reg plugin, I only found reference to what I believe to be six database tables. Please let me know if I'm on the right track, and be kind I'm learning as we go here...
To delete desired rows from sfmembers:
DELETE FROM wp_1_sfmembers WHERE posts = "0" AND lastvisit < "2013-01-01 00:00:00"
Then, to clean up leftover user ids from sfmemberships:
DELETE FROM wp_1_sfmemberships WHERE user_id NOT IN ( SELECT user_id FROM wp_1_sfmembers )
That's where I get stuck. I see no db tables for these spdb_queries I found in the plugin:
- SFUSERS
- SFUSERMETA
- SFWPPOSTS
- SFWPCOMMENTS
A. Are my queries above correct for deleting sf_members and their related sf_membership data without deleting their wp_user table entries?
B. Where will I find these other tables/rows and what do I need to delete from them?
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.
those are wp tables... you are just looking at a define.. but they point to the standard wp users, usermeta, posts and comments tables...
I have no idea what that remove marker is.. never seen it before... dont think its from us...
btw, you can clean out any transients in your options table on forum - toolbox - housekeeping... should be happening auto periodically but cron can be suspect...
Visit Cruise Talk Central and Mr Papa's World
As to question A then yes.
It is probably pretty safe to assume that if the user has made no forum posts then they will not be active in other tables (like private messaging for example). But any if the forum tables with a user_id column can potentially have user related data. But it will not be much and will not be harmful if orphaned.
Good luck keeping the WP transient rows under control in the options table! I really dpn't know how they came up with that particular total mess but mess it is!
|
YELLOW
SWORDFISH
|
1 Guest(s)