After our server got shut down a little while back due to late payments (that would be moi), it wasn’t behaving the same after we got it booted back up again. We’ve been scratching our heads trying to find the root cause before doing anything drastic, but we can’t honestly say we managed to narrow our problems down to any single cause. Chances are it never came down to one, but single breaking points, hence we’ve gone with the shotgun approach on this one.
Back to vanilla
Our immediate solution is simple. I call it the “jmonkeyengine.org life-support plan”
We cut the custom theme (which has been blocking some updates), revert to default WordPress’ TwentyTwelve theme
Update all critical plugins
Disable all non-critical plugins (this includes the BBcode Toolbar, which is buggy and unsafe)
Get site up and running again.
Currently we have not re-enabled BuddyPress, as that appears to be one of the major performance culprits (no surprise there I’m afraid). This means no @mentionsnotifications (link works, but people won’t be pinged) and a few other inconveniences.
We may not be completely out of the woods yet, so please bear with us.
Future plans
I’ll make a more in depth explanation of our future website set-up once we’re closer to completion, but in short:
We are moving to a new dedicated server, free of charge(!)
Nah, the it took 30 seconds just to click the reply button just now… but the other day when things were really bad it was taking 5 minutes just to bring up a post, 5 minutes to respond, etc… with 1 out of 4 times just returning a server unavailable error.
Thanks for the update! I was curious as to why the site was running slow the past few days, I guess this would explain it. I hope your future plans work out well!
We have tuned some mysql settings in order to make the forum usable. The root cause of the performance problems is stil not fixed though, it is an issue with the WP/DB database schema.
I noticed that the search option vanished on some pages but not on others. Specifically it vanished on the Forum pages and still works on the DOC pages. Was that turned off also?
@DieSlower said:
I noticed that the search option vanished on some pages but not on others. Specifically it vanished on the Forum pages and still works on the DOC pages. Was that turned off also?
Yeh, our custom Google search was magic courtesy of @thetoucher I believe. Go get'im. (don't tell him I sent you, he'll say obscenities).
@erlend_sh said:
Yeh, our custom Google search was magic courtesy of @thetoucher I believe. Go get'im. (don't tell him I sent you, he'll say obscenities).
@loopies, I monitored the queries made by WP/BP with a plugin called “Query Monitor”. With a single user that query would execute in roughly 3,5 seconds.
@kwando ouch ok.
Making the assumption it isn’t due to attacks or a new installed plugin.
Some more data in a db doesn’t make it’s response times to jump so awfully apart if something is failing somewhere. It’s a gradual process otherwise.
does the db server have enough memory? If it started swapping, the consequences could be horrible. Oracle would deal fine with that, but not sure about mysql (could depend on configuration). Mysql used to require holding all data of a table in memory… but that was ages ago, dunno now.
do the queries use the indexes? That could explain the horrible response times. Maybe an explain plan could give us the answer here. Also need to make sure the mysql server has room on the hdd for more indexes. Oracle allows different tablespaces for indexes (and so on)… dunno about mysql.
@loopies
The performance problem is that mysql have to create a tmp table in order to resolve a particular query on the “latest posts” page. When the tmp table is getting larger than a certain threshold, mysql will write that temporary table to disk and it will be painfully slow. The size of that temporary table is currently somewhere between 128M and 384M, and it will increase over time as the database grows… unless the WP/BP database model is patched or mysql gets smarter.
Mysql does not support indexing the one column that would make that particular query perform better.
@kwando said:
@erlend_sh, did you do something with the PMs?
Yes, this is due to BuddyPress being inactive. The PMs are all there. I'll see if I have some time to experiment with re-enabling BuddyPress this weekend.
When we migrate the forum, I strongly doubt we’ll attempt to migrate Private Messages, so I’ll put a notice out to warn users about that.
Don’t want to end you loosing time answering my questions so I’m avoiding some paths and will leave you doing your job without being interrupted… after a last question :D.
Just wondering: can you change the query that populates that latest posts page? If yes, maybe you could
filter on dates so it doesn’t get populated with too old stuff, therefore making it smaller, therefore keeping it in memory?
@kwando Thank you for sending me here instead to continue this conversation. How can a LATEST POSTS query end up creating a view that’s 100-300MB, WTF
Why wouldn’t something like this be useable instead? : SELECT id FROM posts ORDER DESC LIMIT 100
I mean, I’d seriously like a shot at fixing this. I’ve been doing web development professionally for over 10 years now. I really doubt there’s nothing we can do about a simple theme OOM error.
@.Ben. said:
@kwando Thank you for sending me here instead to continue this conversation. How can a LATEST POSTS query end up creating a view that's 100-300MB, *WTF*
Why wouldn’t something like this be useable instead? : SELECT id FROM posts ORDER DESC LIMIT 100
I mean, I’d seriously like a shot at fixing this. I’ve been doing web development professionally for over 10 years now. I really doubt there’s nothing we can do about a simple theme OOM error.
First thing, you do net get to write your queries by hand… there is an API that generate the query for you and wordpress kind of assumes you use it…
The main issue with that particular query is that we have to join post_meta table on order to get the latest_post-timestamp and then sort on that column… and that is bound to be slow since it cannot be indexed.
But really, even if we could design a clever query by hand there will still be the issue to integrate it into WP… have you ever looked at the wordpress code?
