So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

by Guest Mon 27 Sep 2010 - 8:34

In an interview with hankford, TrueAchievement explains the events of the weekend of 18th/19th September that took the site down.

The questions were inspired by questions received over email and twitter since the site went offline.

First things first, what happened on Saturday?

I noticed the site seemed to be hanging a bit when I was checking it in the morning so I jumped on the servers and had a look to see if there was anything obviously wrong.

There were some RAID (hard disk related) errors in the event viewer on the database server, Googling the errors suggested it could be a timing problem on the RAID array which a reboot could possibly resolve.

I posted about possible server problems and tried rebooting it, the errors stopped for a while so I did a bit of coding and played a little Halo before checking the servers again a couple of hours later.

I could no longer access the server at this point so rang the hosting company and asked them to take a look. They told me the server had crashed, rebooted itself and was stuck at the RAID menu as there was a failed drive in one of the RAID arrays. I asked them to allow the server to boot and I then assessed the damage.

What was the damage?

The drive that had failed was half of a RAID 0 array. This is basically a set of 2 disks optimized to make the data access on them faster - important on a high volume data server. However, if one fails then both drives are effectively "dead".

That sounds bad?

Yes, but I knew that I had a process that backs the data up every day onto one of the other disks so I wasn't overly distraught, I thought that I could simply order a couple of replacement drives, restore from the backup and everything would be as it was on Saturday morning.

So is that what you did next?

No, unfortunately when I got back onto the server I couldn't find any backups at all. There was a 2Kb file where the backup should have been.

2Kb? That's not big enough surely?

Indeed. When I saw this and it eventually dawned on me that there wasn't a backup I was (literally) physically sick. I checked the logs and then realised that the backup was running at the time of the unplanned crash.

You must have had more than one backup though?

I used to keep 2 backups, one from the previous day and one from the day before. The database has now got so massive (approximately 60Gb) that there isn't enough room on the spare drive for there to be multiple backups stored at once. I had to change the routine a few months ago to only store one copy at any one time in order to fit it on there. So before it starts backing up, it has to remove the previous day's data.

That sounds risky?

I guess it does, but the backup process takes under 30 minutes, and I had the data files on different drives to the backup so I thought I was covered.

Then what happened?

The drive that failed contained the index and log files without which the database is broken - in theory you can still get the data out with a bit of messing around in SQL. So that's what I tried to do next.

Did you not have ANY full backups?

The most recent one I had was from January 12th this year.

Wow, that's pretty old. Did you manage to get the data back from the broken database?

I got some of it out. There was another problem I didn't envisage. Having the indexes on the broken drive wouldn't have been a problem had I not unintentionally moved the biggest tables in the database into the index files while trying to speed the site up some time ago.

That sounds unfortunate

I've tried to put some probabilities against what happened - the drive that failed being on the striped RAID array, the time of the crash coinciding with the backup starting, and then me accidentally moving some of the data into an index file. I think the odds of this series of events happening are up there in the thousands to 1.

What tables are you talking about?

Sadly, most of the biggest ones - Gamer, Game, Achievement, PrivateMessage, Solution, Review, GamerFeed, GamerFeedComment, GamerGame, GamerAchievement and GamerHistory.

What does that mean, have we lost all of that data?

No, I have it all from January from the backup. But nothing since then.

Is none of that recoverable?

I have managed to gather most of the GamerTags and email addresses from those that registered since January from the other undamaged tables. I know that the Game, Achievement, GamerGame and GamerAchievement tables can also be recreated by the scanners doing their "normal" work.

But Solutions and Reviews???

I know, it's an utter disaster. We have nothing since January. I don't really know what I can say to make people feel any better about that, I just hope the community come together to re-post their solutions and reviews. I can only say how truly sorry I am about it, believe me that this week has been the most sickening in my life.

Is there no way of getting that stuff back?

The drives will be looked at by specialist data recovery experts next week. I wanted to get the site back up and running before then as I know how much everyone wants to see it back, and it may take them some time to find out whether anything is salvageable. And there is a chance that they may not be able to get anything back, I didn't want to leave it that long before having the site back up.

Is there anything that's not lost?

Yes, the forums and news are all still intact, as well as 90% of the other tables. The database now contains 150 tables and it was only the really massive ones that are corrupted.

So what's the plan now?

Well, the scanners are trying to catch up with (effectively) 40,000 new gamers to scan, and all the achievements from everyone else since January. While that's happening we will see unusually large scores, and quite a few gamers on the site with no achievements. I have prioritized the scanning of gamers that were using the site in the 2 months prior to the crash as I would imagine they would be the ones most likely to return immediately. As soon as you try to log in (more on this shortly), you will be prioritised in the queue assuming that you haven't been scanned yet.

How long will it take to get everyone scanned?

It's looking like it may take 2 weeks to catch up, but if I can get the data back from the recovery guys sooner then I should be able to just drop that data "back in". In the mean time we won't take new registrations.

Why did you not have more room on the servers for more backups?

When I started the site back in March 08 the database was tiny and I was hosting it at my normal workplace. By the time we got to January 09 I realised that I needed to have a whole server that would have to be hosted by a proper hosting company. At this point the database was maybe 8Gb so I leased a Dell server and paid for it to be hosted. The database, website and scanners were all hosted on this one box. I couldn't afford to get a higher spec'd server (although it still cost about $2,000), so even then I didn't have any redundancy, I just had 2 large striped disks with everything on. With so much number crunching you HAVE to have fast disks, and that means no redundancy unless you can afford a lot more. In around November last year I bought another server, stuck a load of fast SCSI drives in it and had that hosted at the same company. The website, images and scanners stayed on the old server and I moved the database onto the new one.

The problem we have is the amount of data we store. As well as our new users, all of our existing users play more games, win more achievements, and we store a daily history of every gamer and every achievement. The database was 25Gb in January, but it was 60Gb by September. It's hard to legislate for that sort of growth and the investment to cover that is huge. If you think you need to have 2 concurrent backups of the database as well as the database itself, index files etc we need to have 4 times that amount of room just for the data with the site the size it is at this moment. 9 months from now we'll need more than double that. Replacing servers every 9 months is just not feasible.

You sound like you're not happy with the site's success?

No, anything but that. I'm the most proud person you could possibly meet, this site is an incredible thing to be involved with. The messages of support I've had over twitter and xbox live since the site went down have been fantastic. I have so many ideas for how we can make the site even better, the community continues to blow me away with their dedication to TA, it's just the best thing in the world!

So how are you going to make sure this doesn't happen again?

I am already making plans for a new server. This will replace the original box I bought in January 09 and will host the database. I am going to have full RAID mirroring on all drives so if one fails it will still stay online, and the broken drive can be hot-swapped by the hosting company. We will have a capacity of 900Gb across the drives, and one of the backups will be moved to the other server. This should hopefully allow us growth for at least 18 months. The database server I have now will become the website/scanner server.

That doesn't sound cheap

It isn't, and we will be looking at more ways to generate revenue for the site, including competitions, merchandise, maybe partnerships with stores, possibly even changing the donation system slightly. I don't want to discuss all that now though, let's get back on our feet first.

Is there anything we need to do now?

Yes, we lost your TA options, so please try to log in if you haven't done so already and follow the instructions!

Should I repost my solutions and reviews?

If you have a copy of them then definitely. I don't have a probability of the data being recovered, but I would think it's no more than 50/50, I should know one way of the other by next weekend (2/3rd October). If we recover the data then I will overwrite any new matching solutions for achievements you've written with the historical ones as they have all the comments and votes.

Anything else you want to say to the TA Community?

I want to apologise again for the last week, denying the community their TA fix and possibly losing all that data has been devastating for me. But, what doesn't kill you makes you stronger, so I'm hoping that we can come back better than ever, and I'm hoping that the amazing community we have will help us to achieve that.

I also want to thank those who re-mapped all the games for me in the last few days (you know who you are!) - you guys were amazing and have helped enormously in the data restructuring process!

by **N1NJA FWG** Mon 27 Sep 2010 - 10:50

Gutted that it happened but its done and gone and given that the site has been run for free makes it hard to complain. I just need to reactivate my account and I will be up and running again. I feel for the many gamers that have taken the time to post solutions etc as it is them that is most affected by this.

At least they look to have taken steps to ensure it doesnt happen again and they still have my support. In fact, when cash permits, I will gladly be stumping up a donation towards their costs.

The site is a resounding success - its growth is huge and the fact M$ allowed them greater access to Live servers to scan GT's quicker and more regularly backs up the value of this resource.

TA will rise again!!

by **Andy** Mon 27 Sep 2010 - 10:59

struggling to re activate my account - its not accepting that any of my emails are on file. could someone send them a message for me iff possible cheers (have messaged them on live but no reponse)

by **FWG Plod** Mon 27 Sep 2010 - 11:25

send me your email mate and i will do it for you Wink

It shows when i site goes down just how much you use it. I feel for the people who input all the solutions etc as that must take sometime. There is a link on the homepage to get them from a cached page so hopefully if they didnt save them they can retrieve them that way.

by **Andy** Mon 27 Sep 2010 - 12:07

andy@kitehighland.com is the one i am sure i used - username FWG JEDI

if not then could be kitehighland@googlemail.com

by **FWG Plod** Mon 27 Sep 2010 - 12:15

I have sent the mail mate, it also states if having trouble verifying account send a mail to trueachievements via xbox live, worth a try if you havent already.

by **Andy** Mon 27 Sep 2010 - 12:30

did that as well

cheers bud

by **FWG Plod** Mon 27 Sep 2010 - 12:38

Cool, thats alright mate, that's what i'm here for so i guess i better start exercising my power a bit Wink

by **Waylander FWG** Mon 27 Sep 2010 - 13:05

N1NJA FWG wrote:Gutted that it happened but its done and gone and given that the site has been run for free makes it hard to complain. I just need to reactivate my account and I will be up and running again. I feel for the many gamers that have taken the time to post solutions etc as it is them that is most affected by this.

At least they look to have taken steps to ensure it doesnt happen again and they still have my support. In fact, when cash permits, I will gladly be stumping up a donation towards their costs.

The site is a resounding success - its growth is huge and the fact M$ allowed them greater access to Live servers to scan GT's quicker and more regularly backs up the value of this resource.

TA will rise again!!

Agree with all this, and will also consider a donation moving forward.

by **Waylander FWG** Mon 27 Sep 2010 - 13:17

Im loving the FWG Leaderboard, Im second!!!

by **FWG Plod** Mon 27 Sep 2010 - 13:18

That will be because you are a dirty whore.
I'm not doing to bad now though, a way to catch up with you lot though.

by **Waylander FWG** Mon 27 Sep 2010 - 13:19

I know I am, I cant help it!!

by **N1NJA FWG** Mon 27 Sep 2010 - 16:15

FWG Waylander wrote:Im loving the FWG Leaderboard, Im second!!!

Not for long fucker Cat

by **Andy** Mon 27 Sep 2010 - 16:37

i thought zero was second or has he fecked off??????

and dont forget martyn, if we allow him back on then thats his place by my reckoning

by **Waylander FWG** Mon 27 Sep 2010 - 17:21

The gamerscores havent updated yet (I think they go back to January).

by **at5 77** Mon 27 Sep 2010 - 18:20

i dont seem to be able to get my achivements back

by **Andy** Mon 27 Sep 2010 - 18:21

FWG ATS 77 wrote:i dont seem to be able to get my achivements back

aawwwww didums Laughing

by **at5 77** Mon 27 Sep 2010 - 18:35

ha ha you cant even get on the site

by **FWG Plod** Mon 27 Sep 2010 - 18:37

FWG ATS 77 wrote:ha ha you cant even get on the site

Mine all seems ok

by **N1NJA FWG** Mon 27 Sep 2010 - 18:49

Same here, account reactivated. just waiting for the scanners to catch up and update my score Smile

by **FWG Plod** Mon 27 Sep 2010 - 19:16

They did it over night with mine

by **N1NJA FWG** Mon 27 Sep 2010 - 19:31

I can imagine their scanners are working like mad as their members flood back 9 months out of date Laughing

I will be patient

by **FWG Rigor Mortice** Mon 27 Sep 2010 - 19:49

Mines all good. Yours looks about right too Chris

by **N1NJA FWG** Mon 27 Sep 2010 - 19:59

Nah, mine is still behind Steve - its only showing an actual achievement score of 22k. Yours looks very close though - less than 50 points short

by Guest Tue 28 Sep 2010 - 19:31

A quick update on this:

Hey all

Firstly I want to thank everyone for their amazing support since the site came back up - both the PM's and the donations have been truly inspirational and I can't thank you guys enough!

Secondly, I want to give you an update on the scanning.

Because we lost all achievement info for new gamers that signed up since Januray, and all the achievements won by our older members since then, each scan is taking around 5 minutes on average to complete.

So, with the 5 scanners, we can get through around 60 an hour. At the moment, the queue is around 8000 which means it's going to be a week or so until all of those gamers have been scanned. I'm going to add some additional measures tomorrow to help this (no forced scans, no adding friends that aren't registered).

I did prioritise the last 7000 gamers that logged into the site prior to bringing us back online so all those people should have already been scanned.

As everyone else has re-registered they've been added to the queue.

Unfortunately Xbox live was down for a bit today which also put as back somewhat, so please be patient in the mean time!

Some of you will no doubt ask why we can't add more scanners, but when we do that we get completely blocked by Microsoft.

I know this isn't ideal, but it's a necessary part of the rebuilding process.

I also want to ask anyone that has donated that hasn't received the benefits to PM me with the email address they donated with, and if anyone else donates then please put your GamerTag in the message!

Thanks again all, you guys rock!

by Sponsored content

So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....

Re: So what exactly happened at True Achievements last week? and what to expect over the coming weeks....