Is a mySQL or mongoDB relational database scalable for holding 80 GBs of additional logs per day? I am currently deciding on a long term architecture solution for storing DNS logs. The amount of data we are talking about numbers some 80 GBs of logs per day at the peak. Currently I am looking at noSQL databases such as mongoDB, as well as relational - mySQL. I want to structure a solution that has three requirements:


Storage: This is a long term project, so I want the necessary capability to store 80 GBs of logs per day (~30 TB a year!). I realize this is pretty ridiculous, so I'm willing to have a retention period (keep 6 months' worth of logs = 15 TB constant).
Scalability: As it is a long term solution, this is a big issue. I've heard that mongoDB is horizontally scalable, while mySQL is not? Any elaboration on this would be very well received.
Query speed: As close to instantaneous querying as possible.


It should be noted that our logs are stored on an intermediary server, so we do not need to forward logs from our dns servers.
MySQL can handle at least some aspects of such.

At 15TB, disk I/O is the number one problem!  Both on INSERT and SELECT.

Indexes are BTree structured.  This gives "close to instantaneous" fetching of one row, given the index.  It also gives rapid scanning in the BTree order, forward or backward.

You can have multiple unrelated indexes, but it is problematical.  Indexes are kept up to date as rows are inserted.  This is not the least bit of a problem if the indexes fit in cache.  And not much of a problem when the indexes are somewhat bigger than cache.  The problem comes when an index is much bigger than can be cached in RAM, and the next insert needs to update some spot in the BTree that is not currently cached.  INSERTing slows to disk speed -- perhaps 10s of millions of rows inserted per day.  (This is regardless of LOAD DATA, batched INSERTs, staging tables, etc.)

So, you can't have all the indexes you may want.  And without indexes, forget about "close to instantaneous".

So, what to do?  I suggest you turn the design around -- What do you want out of this project?  That is, put aside the discussion of size and storage; instead think about what SELECTs you would like.  Then think about how to arrange the data to minimize I/O.  Would you like to present some of the SELECTs for discussion?

I will probably push you toward summarizing the data as it comes in, storing the summaries in tables, and tossing the raw data (or at least not storing it in a database).

Two further notes:

Tokudb is excellent at ingesting indexed data -- it can generally do it faster than the standard "engines" in MyISAM.  MariaDB has Tokudb built in.

For purging the data, see my blog on such.