I’m evaluating if infobright can used to store data out of an industrial control system (for example temperature,level,..)

My table has this shape

bigint         => to identify the temperature ...
datetime
double

the primary key is the bigint + double column

there are two different sizes:

a) 15 000 different id with a new rows / per ID / every second .. put only a few differnet values per Id (for example the temperature is relative stable) (it also happens that a value is the same for a month (.. very high compression)

The size is apporx. 200 GB with a little bit more then one GB per Day (each day a new partion)

b) 100 000 tags with new values / every minunte)

(size whould the same..)

can / should ice be used for this (there are also some special purpose databases for this from companys like wonderware, gefanuc)

Hi,

It would be nice to see if ICE will be suitable for your task. Actually it should be (Infobright warehouse was already successfully used for similar task). It depends also what type of queries are you going to execute then - is it only for storage, or also for statistics, like min/max values in various time intervals?

From the performance point of view, I have two remaks:
- As ICE is more warehouse-like than transactional database, it likes to have data loaded in larger chunks. Loading every row separately is not a good idea for sure - I suggest to make it on hourly or daily basis. I.e. to prepare input file with thousands or milions of new rows and then to load it.
- If the “double” column is used not only for storing values, but also for e.g. defining conditions in queries, then it is better to use rather DECIMAL(n,m) data type. Supposing you may determine precision really needed in (or really provided by) your control system. DECIMALs are faster and should be compressed better.

Thanks for proposing so interesting use case,
Jakub Wroblewski

Hi superwolfi,

This is a very interesting application. One of our current customers also uses Infobright to store large amounts of numeric data (voltage, temperature readings). So it sounds like we may be compatible with what you are trying to do.

Can you please explain a bit more about your application?

You said your table structure is

bigint => to identify the temperature ...
datetime
double

the primary key is the “bigint + double column”.. I assume you meant the bigint + datetime column?

What kinds of queries do you need to do?

How often do you need to load data? Our existing customer loads 1,000,000 rows of data every 5 seconds using a custom loader script.

Do you need to query during load?

Geoffrey