Plus Users

If you’re a Plus user who’s been affected by the downtime over the past few days please e-mail me privately (support@deardiary.net) to get an extra week added to your Plus subscription by way of apology and compensation for the inability to use the system over the last few days.

There are still some residual issues I need to clear up, but it looks pretty likely that the main biggie is now nailed…

Thanks for your patience and support guys. If anyone see’s any really negative entries from people who are really pissed about the downtime over the last few days, please leave them a (nice) comment and point them to this diary so that people can understand what went wrong. Or point them to the forums – or point me to the diary so I can explain :)

Cheers all!
Steve

Think I may have found the little bugger

Hahah!

The problem is/was due to the Last 10 Updated Diaries query which was run at least every 2 minutes (or more if it happened to take too long to run and someone hit reload). For some reason it suddenly blew up in the last few days to the point where MySQL could no longer cope with it at all. The query basically never returned (indeed, it took the whole box down with it). The machine slowly crawled to a death.

I have done a LOT of digging through the MySQL documentation and found that the ORDER BY could be significantly (and I mean significantly) improved. The query now runs in (according to MySQL) 0.00secs instead of running practically forever…

We’ll see whether it really is the culprit – I think its actually just the worst of a bunch of badly optimized queries, but it was certainly the worst.

I’ll keep you posted.

Steve.

Running some tests

Well, given that with DD switched off the server survived over 24 hours when it wouldn’t do more than half an hour before, I have switched it back on presently. The ‘genre selector’ is still disabled at present because I believe that to be completely messed up and needs the query to be rewritten.

You’ll also notice that presently we’re back to the old index screen because some kind person (Les) pointed out that woes only really started when I switched to the new front page. I should point out though, that if thats the case it’s MY fault and not the people who designed the layout and look of the page, cos it’ll be my crappy queries barfing it all up :)

Anyway, we’ll see whether we stay alive long enough to allow postings again this time eh?

Steve.

Buggered Queries

The problem appears to be down to a specific query that has just blown itself to bits for some reason. It’s the ‘Genre Selector’ query, so the ability to search for recent updates in a specific genre is presently offline while I figure out a better way to deal with it.

There may be other issues that crop up as a result, that I’m as yet unaware of so if you find something that isn’t working it could well be related. Given the time constraints of my real life job it’s not likely that this will be fixed any time soon (ie, definitely not within the next few hours) as it will require a rethink on that particular area of code.

I suspect basically that finally there are just too many entries in the database for that particular query to handle well. Don’t worry though I won’t be deleting any!

test

test