Test your historian with made-up numbers and it will lie to you about disk

You are working out how much disk a historian needs. Someone sensibly suggests trying it first, so a script gets written that pumps a few million readings in, and everyone looks at how much space they took.

If that script makes its numbers up at random, the answer will be wrong. Not slightly wrong. In my test it was wrong by a factor of five, and wrong in the direction that makes you buy hardware.

Why it matters what the numbers look like#

A historian does not keep your readings one after another the way a spreadsheet would. It looks for repetition and strips it out.

A value that has not changed since the last reading costs it almost nothing to store. A value climbing by the same amount every second costs almost nothing either, because only the pattern needs keeping rather than every individual number.

Plant data is full of that kind of repetition, and it is worth actually looking at what it does rather than imagining it.

WHY A HISTORIAN CAN SQUEEZE PLANT DATA

Almost none of that day needs storing. The status tag sits on one value for hours at a time. The production counter climbs in a straight line. The flow is flat at zero, then flat at fifty when the pump runs. Even a temperature, which never truly stops moving, only wanders slowly between neighbouring values.

Random numbers have none of this. Each one has nothing to do with the one before it, so there is no pattern to find and nothing to remove. A trial built on random numbers is measuring a kind of data your plant will never produce.

The test#

I wrote something that generates six kinds of tag: a temperature, a flow, a pressure, a vibration reading, a production counter and an on/off status. They are mixed the way a real site is, with slow-moving analogue values outnumbering everything else.

Then I made the same quantity twice over. Ten million readings that behave like a plant, and ten million random numbers. Same database, same settings, same everything. The only difference was whether the numbers meant anything.

WHAT 700 MB OF READINGS COMES DOWN TO

700 MB in

16 MB

readings that behave like a plant

700 MB in

82 MB

random numbers

Seven hundred megabytes of raw readings came down to 16 MB when the data behaved like a plant, and 82 MB when it was noise.

Had I sized a historian from the random-number run, I would have bought five times the disk I actually needed, and been confident about it. The trial was real. The numbers were honest. They were just measuring the wrong thing.

WHAT A YEAR OF ONE INSTRUMENT COSTS

one readingunder 2 bytes

one tag, one yearabout 53 MB

1,000 tags, one yearabout 53 GB

The figure worth carrying in your head is that a stored reading costs under two bytes. One tag read every second for a whole year comes to roughly fifty megabytes. A thousand of those tags is about fifty gigabytes a year.

That is a number you can take into a budget conversation, and it is nothing like what the noise test would have told you.

Making ten million readings is not the hard part#

This is what I expected to be difficult, and it was not.

A single processor core can produce over three million readings a second. Turning them into the format a network carries costs about a third of that. Sending them across the network costs most of the rest. And writing them into the database is where nearly all the remaining time goes.

There is a lesson buried in that last step. There is a bulk-loading method that is supposed to be the fast way into this kind of database, and mine came out three times slower than the ordinary way, which should not happen.

My first guess was wrong, and it is worth admitting because it was a reasonable guess. I thought the timestamps were being rebuilt more often than necessary. Fixing that helped by about a tenth, so it was real, and it was not the answer.

The actual problem was that I was handing the database one reading at a time. Each handover costs the same whether it carries one reading or two thousand, and I was paying that cost ten million times over. Passing them in batches made it five and a half times faster. The database was never the problem. My own loop was.

A bigger machine would not have helped#

Once the loading was sensible I tried spreading the work across more processor cores, expecting it to speed up in proportion.

It did not. Six cores gave about a third more than one, nowhere near six times.

WHERE THE WORK ACTUALLY PILES UP

The reason is that generating the readings was never the hard part. One core can make three million a second while the database can only absorb a fraction of that, so everything queues at the database no matter how many cores are feeding it.

Which tells you where to spend. If your trial is running slowly, a bigger machine for the part making the data will do almost nothing, because that part was barely working. The database is the constraint, and that is the thing to tune or to accept.

The full ten million landed in 26 seconds, on a small box that was also running several other things at the time. Simulating a plant's worth of history is not a hardware problem.

Three things to take away#

Make your trial data behave like your plant. Values that hold still, values that step when a pump starts, counters that only climb. It does not need to be sophisticated, but it must not be noise, or every storage number that follows will be wrong in the expensive direction.

Hand the database its data in batches rather than one reading at a time, and check your own loading code before blaming the database. Mine was out by five and a half times and looked perfectly sensible.

And do the trial at all. Ten million readings took under half a minute to produce. That is a cheap way to find out what a year of history really costs before somebody signs for the disks.

The generator, the signal models, every measurement and the wrong guess along the way are in the lab write-up.

There is one measurement where a generated signal is the right instrument rather than the wrong one: judging what a deadband throws away, because the true shape is known exactly. That is in what a deadband actually costs.