WarcraftRealms.com

Does anyone know why we're experiencing a ~60 hours delay from upload to process?

Normally, uploads are processed within 8 hours..

I don't KNOW anything... but I am making two guessses:

1) It's a serverside problem as the DB has crashed multiple times in the past few days.

2) Balgair and I are overloading the queue.

I hope Rollie has a few minutes to look into the matter soon as it seems the backlog is growing daily, it went from normal to 34 hours, then 43 and now it exceeds 61 hours.

The way that you 2 are going lately, it is more likely option 2

It's a combination of factors.

1 - The database has hit that critical size again where the table sizes are greatly impeding updates.

2 - Some certain over achievers are submitting 80k character updates ;p


In reference to #1 above, I took the site offline for a few hours yesterday to run a full database backup and have started culling characters again. Some of you may remember when I instituted a cull that removed all characters under level 50 that hadn't been seen in 2 years. I have again started this process of culling out sub level 60 characters that have not been seen in 1.5 years.

In addition, during the standard update process, the update required that characters be at least level 10 before it would track them at all. I have increased this value to level 30 for the time being. I realize that there are some twinkers out there that purposefully do not level above a certain level (19, 29, etc) that will get lost in the shuffle.

This is unfortunate and i wish I didn't have to go to these measures, but I don't really have much choice. To be able to handle it all as I would like to, would require a fair bit of re-architecting, not to mention beefier hosting, which I simply cannot afford.

Rollie wrote:2 - Some certain over achievers are submitting 80k character updates ;p

A shame you've had to reduce the amount of characters that are counted again, but I can totally understand why, here's hoping the database can keep up a bit better now anyway! Perhaps some better way will come along in the future

Yeah, I'm going to start looking at alternative methods that I might employ to stop the cull process.

I know I could get more hardware, shard the database and more, but that all takes $$$ that I don't really have to spend right now =x

Doesn't look like it have helped.. We're in excess of 100 hours delay now.

Process seems to be totally stuck now..

The last ~36 hours, there have been no progress.
Belgair, 1974 and my self have all had our last process around 14-15 o'clock on 20th (yes, it's the upload time, but it's the only thing we can track it on).
1974 have a sending from same day, around 18 o'clock. This was the same status wednesday evening.

20/11 was sunday (maybe monday, if taking timezone into consideration), which was LONG before v4.0.3a was released anywhere, so new race/class-combo can't be the problem..

Maybe a bad upload (between the above mentioned times) is blocking the processing?

Not only is the queue frozen... the whole dbstats.php page seems to have frozen... the ONLY part of it updating is the current server time. 2010-11-20 17:45:39 is listed as the last processed. Nov 20, 10 18:26:24 is my first unprocessed. So the problem area is either between those times or not directly connected to the uploads.

Eeek, yes, there is an issue, looking into it now

1974ER wrote:Not only is the queue frozen... the whole dbstats.php page seems to have frozen... the ONLY part of it updating is the current server time. 2010-11-20 17:45:39 is listed as the last processed. Nov 20, 10 18:26:24 is my first unprocessed. So the problem area is either between those times or not directly connected to the uploads.

Last processed file was uploaded on 2010-11-21 03:08:11.

Looks like Rollie is waving his wand..

And now --> Last processed file was uploaded on 2010-11-21 04:34:09.

Slow but steady --> Last processed file was uploaded on 2010-11-21 09:52:12.

Between 6 and 9 this morning (CET timezone), all of 23rd was processed.. YAY!

However, for the last 3 hours, it have been stuck again.
Last processed file was uploaded on 2010-11-24 01:12:10.

Nov 24, 10 01:39:22 0 0 E

It's currently crunshing on that from me, so it's apparently not completely stuck. Also, it has processed 2010-11-24 01:26:55 already... maybe it was just some very large files. I know that one from me is big, because I was only able to submit one on the 23rd.

I've noticed it often just stops for a rest normally - been a few times I've noticed it's stopped for a while directly before one of my submissions, but doesn't have mine showing as E (ie currently processing), and has just updated the totals on updates and new characters, which I think it only does when it stops. So might just be builtin for it to take a break every now and then! Definitely seems to be closing in a little anyway, good signs!

I noticed something else, which to be frank, worries me. Due to the backlog, I am not yet fully sure if my observation is correct, but initial indications point to a possibility of almost half of my actual played characters being negatively impacted by the changes of 24.11.10.

Furthermore, on a generic level, even accounting for the backlog, two important census database indicators have dropped to alarmingly low levels:

Characters seen in the last 7 days 162,942
Characters created in the last 7 days 8,352

Those values used to exceed 1,7M and 100k, before the troubles struck... and during more active months... I occasionally saw the former exceed 2,1M and the latter was somewhere around 220k, if my recollection is correct.

Rollie, are you absolutely sure you didn't axe too much??? Because as far as I know, I have on many occasions gone above 50k seen in a single day alone, Balgair has to have seen even more during her recent "superactivity". 162k divided by two is only 81k per day... If I and Balgair already exceed the daily total... something is wrong... or the value has been axed to the total amount characters of level 30+ seen by all census users, which means it's no longer even remotely representive of actual activity.

Rollie wrote:
In addition, during the standard update process, the update required that characters be at least level 10 before it would track them at all. I have increased this value to level 30 for the time being.

this means that for a period of time.. new characters (i.e. level 10) won't be seen.. until the players get them to level 30

I will also note that I'm seeing a lot of people starting new characters as they explore the changes made to the starting levels and the changes in old world Azeroth.

Would file processing go faster if people purged their data files after every upload?

Is the parser able to tell that it's already processed a bunch of the data in a file before, or does it need to query the database for all the data again?

If people are running censuses every day and uploading, but have a month's worth of data in the upload file that all needs to be parsed again (even though 29 days worth have been processed before) I'd expect that to slow things down a lot if it needs to access the database for all the data, not just the new data...

pencey wrote:Would file processing go faster if people purged their data files after every upload?

The quick answer is: Yes, No, Maybe

Yes.. the process to merge your census run into the Warcraftrealms.com database does require that every record of your census run be read and then compared with the db.
If a match is found for that unique character on the realm then a further quick check is done to see if level has change, if it has then the db has to be updated with the characters new level and the date stamp for when the new level was first seen.
If the unique character hasn't been seen then assuming the level is >= to Rollie's minimum cutoff level the db has to have a new record created for the character.

If you were the only person running census runs on that realm/faction then purging your local records after every update is the most efficient BUT you would lose the ability to figure out for yourself how active that realm faction is.. since every census run would show all characters as new.

No.. The problem with purging after every run is that since you won't gain an idea of the real leveling of characters on the realm you will most like tend to upload the census runs more often then you would really need to which would create much more load on DB updating process.

Maybe.. take for example that you, like most users of Census+ run the addon but only as a side action of your real reason for being logged into the realm.. ie.. you are there to play the game. Let us say your play a character for 2-3 hours.. you will probably have 4-6 census runs trigger. If you are not purging you might notice that on a low or medium population server that you only see 10's of new characters and 10's to low hundreds of updates.
With that low a number of updates you might decide that sending in an update doesn't have to happen today.. in fact many incidental census+ runners only upload there data after more then a week of playing. As long as the upload of the data happens some time.. it doesn't matter for the db since you will have captured leveling and new character creations in your census runs.

Now if you doing the Census runs for the goal of top gun naming rights.. then you want to upload as often as possible to make sure that another addon user doesn't get the credit tag for the character update.. hmm sounds a lot like playing some of World of Warcraft quests.

Rollie has put in place an upload file size limiter.. somewhere in the 10-15MB size if I remember correctly. That stops the top guns (or anyone else for that matter) from overloading the update process with huge amount of data.

As a top ten contributor doing census runs on trial account I by default auto purge every 10 days, I don't bother purging more often because I find my census+ data file doesn't get above 10MB. The two current top guns are purging at a minimum 5 to 10 times a month... or they would bang into the upload limit. Considering that they do census runs on a lot of realms I expect they don't pay too much attention to their local census reports and purge more frequently so they don't have to worry about hitting the limit.

So the unasked question .. are the top guns loading down the db server and slowing the update processing.. well YES they are.. but then they are providing Rollie more census updates then the rest of us combined.. and that is what it is all about.