What architecture is best to store a large 3 dimensional array in MySQL?

Question

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy:

{ x, y, z } = { column, row, time }

The z dimension will be time, and I would like to store as much as possible. We are hoping for approximately 1TB of total stored data with modest performance. In other words, we are looking to take a snapshot of one MySQL Table, every 30 seconds, for years. It will be the same table every time, and will be about 10 columns {x} by 1000 rows {y} (approximately 50KB). So if you will, we want to take a snapshot of a 50KB table every 30 seconds.

So this creates the following problem: averting the need to store an endless amount of tables. I have read in various posts on Stack Exchange that it's bad architecture to have millions of tables in a database, and with such a design performance will suffer. So here are the two possible architectures I can think of:

1. Create a new table and name it using an epoch time, and create millions of new tables endlessly (not good).

2. Create one database with two columns: epoch_time and json. For every snapshot of the original table, every 30 seconds, convert that into a json string and store the entire table in the json column. So basically, a database with millions of rows containing json serialized tables.

Would number 2 be the best architecture? Is there a better way that I may be missing?

Answer 1

Deltas

The data you are "snapshotting" -- how often does it change?

I suggest looking into storing only the "deltas". When some piece of the snapshot does not change at all, the delta is empty, and you can store nothing.

For reconstructing a snapshot at some point in the past, the processing is costly -- you need to walk through the versions, applying the deltas as you go.

There are two ways to run the deltas -- forward or backward. Going 'forward', you would start with the original (complete) snapshot, then apply deltas until the desired time. Going 'backward' has the advantage that the most recent snapshot is complete. The going backward 'subtracts' off the changes.

Since you say "for years", it is probably wise to take complete snapshots every, say, day. Then finding a particular 30-second second snapshot won't involve more than 2880 deltas. This obviously leads to a speed/space tradeoff -- full snapshots are bulky, but infrequent snapshots leads to long 'reconstruction' times.

Trigger

Rather than "snapshotting", use a TRIGGER to build an "audit trail". This is similar the "deltas" I mentioned, but it is better in that it is continuous, not "every 30 seconds". The case I remember had over a billion rows in the audit trail; each row had (approximately) the timestamp, table name, PRIMARY KEY, and a compressed JSON blob of all the columns for that rows. Your needs may be better served by some variant of that.

Schema

Until I see the actual queries, I will advise against PARTITIONing since it is usually of no performance benefit.

The link about table size limits is missing one number: 64TB is the limit for one non-partitioned InnoDB table.

Answer 2

For the sake of it I'll describe an approach not mentioned above. It is typically used for temporal data. Not sure it will fit your needs, but here it goes. The idea is to have a copy of your original with two additional attributes, begin_time and end_time:

create table ...
( ...
, begin_time timestamp default now() not null -- MySQL timestamp deviates from standard so perhaps some kind of datetime is better 
, end_time timestamp -- null means current row
)

In the load process each row is compared with the current row and if nothing has changed it is ignored. If something has changed the current row's end_time is set to now() and a new row is inserted with begin_time now().

Trends for a certain "row" is easy as well as investigating how the row looked at a certain point in time.

Answer 3

Create a new table, that has all the columns of the existing table, but also a time column, which is set to the value of the time the snapshot was initiated. You're adding a new fact to store about an existing set of facts (each table should store one kind of fact, ideally). The value of the time column identifies each snapshot.

If you need to query across the whole table, then that's a practical disk IO problem. But, by keeping the data in plain old columns from the DB, you can add indexes to columns that are used by those time-consuming queries. If you need to keep aggregate data on the information, there's a disk IO problem with it being big, and the answer is to design views that perform those aggregations on updates, with intermediate historical data being maintained, instead of going over the whole data set every time. Keep as much large IO in the DB as possible.

A large table can be a problem for your memory and hard drives, should you need to access more than a small part of it, and is going to need a human touch to manage any large queries over it, with any design. MySQL, OTOH, will be fine with a very large table. Over-complicating it with many tables, or JSON stores, will do no good, and could come back to bite you if you ever need to perform any historical queries over it.

However, the implementation may eventually need partitioning. When you start reaching whatever limit is relevant for your DB or table, decide on partitioning. You will, in the future, have enough information to decide what the best method is. With InnoDB, it's quite possible that you may never reach any such limit. Likewise, depending on settings, you could hit such a limit with many tables just as well as one big one. http://dev.mysql.com/doc/refman/5.0/en/table-size-limit.html

Answer 4

I would not store this data in MySQL but in a NoSQL system as json. This kind of problem is terrific for that architecture. Much more scale able than MySQL.

In addition, if I did not need the actual data to be query-able, I'd further 7zip the stream before saving it to the DB. No reason to just waste space.