Bother: Table archiving.

Nil Punkt for my design of the supply level audit table.

Every time the capacity of a vehicle for a given HCUnit changes, its added to a save list. Once every 5 seconds the vehicles on the savelist are checked to see if they haven’t returned to their original value and saved.

Key point: The table only updates on a delta. 2fjd.png

Trouble is these tables are getting kinda large, and I don’t need to retain the fidelity I have after 7 days. I might start by reducing the frequency of saves on the host from 5 seconds to 60 seconds.

But what about the data I already have. I can’t average it, I can’t min or max it. It seems like I’m going to have to massage my archive by writing just the first and last entry in each given 15 minute window.

I was contemplating doing this with a relatively simple stored procedure that uses cursors. But it seems kind of lossy to me so I’ve half a mind to write a perl script instead and have it follow trends instead so that if the number goes up or down for 8 points and then changes direction, I write the change-in-direction point.

When you move between towns, the equipment gets ‘transfered’ – it’s actually still on the spawn list, but the count that gets sent to the client is onSpawnList – inTransfer. The number I write to the database includes the transfer vehicles so transfer-trickle doesn’t appear in the tables.

With that in mind, and with a 7 hour resupply time, I think it might be safe to go with the first and [where relevant] last approach. I just hate the lossiness.

I thought about going high and low, but which do you write first? :)

I’m also regretting this layout from a stats point of view since, in order to generate “over time” charts I have to manually fill in missing datapoints — if the number of sappers on a list drops to 4 at 6pm but I want to analyze the list at 7pm, there’s no data point for sappers at 7pm.

This was a nuisance for my chart since I’m aggregating strengths by weapon category – which means for each time there is a point for any weapon, I have to project the last known value for each other weapon in that category — there might only be data points for sappers at 6pm and 7pm, but riflemen and smgs will have data points all the way inbetween.

Very messy. Of course, if I just wrote all of the spawn lists every minute, I’d have a whole hell of a lot more data points messing with my head :)

7 Comments

Would the RRDTool libs help, as a “don’t reinvent the wheel” thing? I know they do a very smooth data reduction over time so you get fine-grained data in the short term and long coverage, all for very small data size.

I think you got distracted by the graphs ;)

Are you wanting to purge data and not purge relevant data? Or you wanting data points filled in for your graphs?

Does my SQL use Case? You can build your logic into a case statement. I’m feeling lazy, actually pissed off at something at work, so I don’t feel like looking it up.

Nah, I understood that you were only recording deltas so as to minimize data storage requirements. Remove the size of the data storage and the need to jump through hoops by only recording deltas should go away, no?

You can start to discard data after 7 days, or downsample it.

Perhaps I’m not understanding your data model. I would have a “current state” table, and a “historical state” table. The former would have one record per equipment type per unit, the latter would be timestamped and very large.

I’m gathering from rereading that your concern is that you could very well have data in your table that is older than 7 days (and can’t be dropped) but that still represents the current number of units available in the brigade?

No, honestly, I was recording deltas because of a little case of laziness ;) The game server is maintaining supply level lists and making sure that the tables are fully populated with initial values seemed like hassle and the audit table is populated by a trigger (the server was up and running, the patch released, and I thought “well if I add a trigger I don’t need to rebuild the host and I can just remove the trigger if I want to turn it off again)

The real issue is that I’m already performing several different analyses on the different values in each row which put conflicting demands on the data in terms of how I would summarize, and each row contains 5 different values.

“Sloppy” is the word :) I will probably just have to write a script to re-populate the early parts of the audit table, make the servers INSERT IGNORE each vehicle added to an active brigade from a template as it’s loaded, and make it save the current and historic supply levels every 60 seconds and the historic every 5 minutes – say.

i.e. do it properly ;)

Of course, certain satisfaction in my biggest post-rollout TOE headache being that an audit table I pulled out of my hat after release needs work ;)

Leave a Reply

Name and email address are required. Your email address will not be published.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: