Support Forum

'Clean up post count' housekeeping on big databases? Not good.

CS Chuck Smith
Chuck Smith
Member
Member since Jan 2015
2 posts
Jan 27, 2015 · 11:02 am · Edited

Using latest rev of Simple:Press. 

Got a pretty big database.  The wp_sfposts table has 594,374 rows and is 279 MB.

Somehow the post counts have gotten screwed up.  Went into the Simple:Press housekeeping and told it to Clean Up the Post Counts.  After about 90 minutes of watching ‘please wait’ it breaks something and when you try to log into the WordPress site it says it can’t establish a link to the database.  I have to contact my host provider and they do some sort of database rebuild to get things back to normal… but my Simple:Press post counts are still way off. 

Tried disabling all WordPress plugins except Simple:Press.  Did the post count housekeeping again.  Same result.  Disabled all Simple:Press plugins.  Same results. 

I’m stumped.  Any suggestions?

3 Answers

New Answer
YS Yellow Swordfish
Yellow Swordfish
Member
Member since Nov 2008
16,437 posts
Jan 27, 2015 · 12:26 pm

Wow. Big numbers. Might be useful to know the number of members/users as well.

This task is always going to be a bit of a major DB hit so it might be that we just need a way to batch it up into smaller chunks.

Would you be willing and able to ask your host support what the problem they found was exactly and what they did to correct it? And perhaps while at it you could ask them if you database needs optimising.

It might be useful to know what got stuck so we can plan a way out of it.

CS Chuck Smith
Chuck Smith
Member
Member since Jan 2015
2 posts
Jan 27, 2015 · 1:44 pm

18,411 members/users

The host ended up rolling the site back a couple of days to when things were working.  Not much help in identifying the problem.

I tried optimizing the tables with phpMyAdmin.  No difference in the end result… still led to a lockup.

Is there another way to do the post count cleanup rather than on this live system?  Pretty sure my host is starting to think of me as a high maintenance customer… meaning my days are numbered. 

YS Yellow Swordfish
Yellow Swordfish
Member
Member since Nov 2008
16,437 posts
Jan 27, 2015 · 3:29 pm

Rolling back? That’s most unhelpful and somewhat careless of them. I guess just easier than actually tracing the problem. It is actually hard to see, on what is such a simple query update, what could go wrong short of a timeout condition. And i can come up with no reasonable explanation as to why that should corrupt anything at all.

Would you be happy to execute update queries on your database via phpMyAdmin?