Database choking =x, possible character purge imminent

[Rollie]

So you may have noticed the updates have begun to slow and back up again. The reason is just that the updates are taking longer and longer due to the huge amount of data being tracked. There are 38M characters and nearly 200M character entries in the database.

I have a few options. I can rearch the structure to break the characters out into separate tables by region or server. I can get another DB server and start doing some replication. Or I can purge old, invaluable character data.

I did some checking and I currently have over 10M characters under level 10 that haven't been seen in over 6 months. It would seem to me that this data is not all that valuable and is quite likely a lot of throw away characters.

I guess what I'm wanting to do is open up a discussion on if this is a bad idea or not and what your feelings are on possibly losing that type of history information.

[Balgair]

I'm a bit of a completionist so I'd be sorry to see data purged, however I can also see that the database is struggling badly with processing speed at the moment. I can understand if it becomes necessary but if there's a less destructive option it would be nice.

How many of those 10M characters are under 5 out of interest? Those are very much throwaway characters and wouldn't be as bad to lose, but it takes a little more effort to reach 8 or 9.

Incidentally you can probably feel free to go through and delete every character named "Census" (or Censusii, which is my default when the name Census is already taken) under lvl 10, as the vast majority of those are ones made by me or other censusers

Yup, by my counting only 21 out of 224 characters named "Census" are lvl 10 or higher, and all Censusii's are 6 or under Ok the active census characters will return next time they run a census but it'll clear out the deleted ones anyway, and I'm seeing 4 or more on some servers so clearly some must no longer exist (I know I've deleted a lot myself to make space to move onto other servers).

[DM.]

5%.... That isn't a very large number, and thats what those 10M chars represent... only 5% of the total chars. Sure go ahead and remove them, but the way I see it is that you will run into this same question again in the coming weeks and months ahead when those same 10M chars are replaced by more regularly played lvl 60 chars.

I think you should think about separating it by Region (US and EU) and Server. Perhaps group it up into those battlegroups that Blizzard has, or separate it by server type (pvp, pve, rp etc...). I think it might be better to separate it into Region (US or EU) and Server-Type (pvp, pve, rp)

[Balgair]

Separating by region is a good idea, yes; there's basically never going to be anyone uploading data for both regions in the same upload (can't be on the same account) so I imagine theoretically it should be possible to separate them? Might also allow for the feature I've wanted for ages: top contributors by region

Another way to split them within the regions if it's needed, would be by timezone for the US servers, and language for the european ones - in fact even if that's not needed, info on the server language would be a nice addition, as I always see a server on the most wanted list then have to search through three different tabs on the server list ingame to actually find it Just put "Eng/FR/DE" in the bit where it currently has the timezone, as all our servers are CET

[Rollie]

Actually, that 10M is out of the 38M chars, so roughlyl 25%. As for the 200M character entry rows, I'm betting it's a much smaller amount, maybe 5 - 10%.

And yes, you are correct, this would only be a stop gap measure as with time it will still be an issue.

I am going to HAVE to make some arch changes regardless if I do the purge or not. The only thing a purge will do is buy me some time to think through the rearch.

Separating by region and getting a 2nd server will give great performance boost across the board, but will require me to go through and rework some areas of the site.

[Rollie]

Oh, and 5854208 of the current chars are lvl 5 or under and not been seen in the past 6 months.

[WyriHaximus]

Removing everychar lvl10- not seen in 6months is a great thing to do. And dunno about the DB but that is a good idee aswell. Maybe your fresh recruted devs can help .

[Hybuir]

under lvl 10 innactive over 6 months sounds reasonable... they would only take up space and processing when calculating guild exes (which puts enough strain as it is). They serve no purpose, since theyve been innactive.

[Rollie]

Yeah, that guild exes query is nasty. I cache the results, but the first time it's run on a guild after a reset can take a couple minutes to return...

[Skyfire]

I'm going with the general consensus: dump the lvl 10-'s. Maybe not seen within the past 3 months instead of 6 though. How many in that category?

[oiseaux]

I understand the problem you are now having with the databases being slowed down. I think that 6 months should be the limit though. Going 3 months might be pushing it a bit in my opinion. I know that when I transferred servers I checked this site to see what names were already taken. Most of the names I liked were all characters under level 10 that had not been seen for quite some time. So I would no longer be able to use this site for that purpose. However, I am not saying that this site should be used in that regard. I am just simply saying that I could no longer rely on the information.

Overall, I think it will help you put a bandage on the situation so to speak. However, is that bandage really going to change anything in a few months or even weeks?

[xpolockx]

If it buys you the necessary time needed to rework the architecture, I say do it. If they haven't been played in 6 months they probably won't ever be played again... and if they are, then most likely someone will get a census snapshot of them

I do think separating it by region will probably be a big help.

Even if it's just a bandaid, if it can help while you work on a more permanent solution then I think it's worthwhile

[Sianni]

I agree a purge of level ten and unders not seen for 6 months is okay. Also agree seperating by region is probably a good longer term idea.