What is a good DBMS for archiving?

Question

I've been stuck in a MsSql/MySql world now for a few years, and I've decided to spread my wings a little further. At the moment I'm researching which DBMS is good at things needed when archiving data. Eg. lots of writes and low reads.

I've seen the NoSQL crusade, but I have a very RDBMS mindset, so I'm a bit skeptical.

Anyone have any suggestions? Or even any pointers to where there are some benchmarks etc for this kind of stuff.

Thank you :) Thomas

---

edit

Since there was a question, I'll try to give a bit more info on what I'm thinking

I'm going to run a service on several servers, which will all have their local database. These databases will have a huge amount of hits (1/1 read/write), so I'm trying to keep them as empty as possible to keep query time down. My initial estimate is that no row will sit in that database for longer than 30min. Running an archive db on each of those services, seems like a waste of resources, so a central archive architecture looks better.

I'll try to ascii up a quick network architecture

     ___________    ___________    ___________
    | service 1 |  | service 2 |  | service 3 |
     -----------    -----------    -----------
          |____________|_______________|
                   ____|____
                  | Archive |
                   ---------

As you might know, MsSQL and MySQL only scales vertically when dealing with writing (not sure if it's a rdbms thing). So I'm looking into getting the most performance out of that archive DBMS as possible.

Answer 1

If you're interested in "spreading your wings", consider sqlite. I don't know what its read/write performance is, but it's likely quite good enough. sqlite is sufficiently different from mysql that it should help you spread your wings.

Answer 2

Oracle or PostGresSQL are also very powerful DBMS. But if you already know and used MySQL, why change? MySQL is free, performant, well documented...

But if you mostly have write operations and not a lot of reads, and you don't want anymore of the commonly used DBMS, then you might consider a document based DBMS

I would recommend you to have a look at eXist db and Mongo DB

Hope this helps!

Answer 3

If the structure of the data you are archiving is relatively simple you could consider archiving directly to flat files. Good for writing, not so good for reading. There's some discussion on this topic in this question: http://stackoverflow.com/questions/332825/are-flat-file-databases-any-good/332868#332868

Otherwise, I'd stick with MySql and make sure it's properly tuned for high-write/low-read usage.

Answer 4

so I'm trying to keep them as empty as possible to keep query time down

First, Query speed is not directly proportional to Database size unless you're doing only full table scans. A Unique Index lookup is proportional to the depth of the index. From the time a index root block splits to the next time it splits could be millions of additional rows. In fact deleting rows to keep the database "as empty as possible" may not actually make the database smaller. Until you rebuild the index, you could have very sparse branch and leaf blocks making making index scans take longer and longer.

I'm not sure how MSSQL or MYSQL fill partially empty pages, but you may not see any space savings at all from deletes.

In Oracle, I'd suggest partitioning and drops over deletes for actually keep a database a certain size.

But I said all that to encourage you to spread your wings into an using an In memory database for your server usage, instead of focusing on your archive usage. In this case you've said nothing that makes me think an RDBMS isn't the best solution for archiving.

Answer 5

You can see read/write performance result of diferent database with this Database Benchmark Software (GNU GPL) that is suitable find some answers.