How to shard 14 gb of data for archival database?

Question

We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state. We now want to store this in a database. There would be ~130 million records.

The data only needs to be written once. No new data will be added to this database. It will be read from ~10 times per hour. Almost all of the queries will use one indexed field. We're looking for a response time under 5 seconds.

Would MySQL be appropriate for this, and if so, what is the minimum amount of RAM you would think the server needs? Should it be sharded by state? If not MySQL, what other options do we have?

Answer 1

Any full featured database will be able to handle this with ease. My preference would be for Postgres, however SQL server, Oracle and MySQL are all valid options.

When designing the tables you need to consider how you will read the data so that you can optimize read performance. There are a couple of things to look at.

1> Appropriate indexes for the queries you are going to run.
2> The use of Partitioning (read the appropriate manuals). You would use partitioning in this case perhaps by having a partition per state assuming the queries that need to perform affect only one state's data at a time.
3> Disk and network. These can often be the cause of slow reads so make sure that they are as fast as you can afford. Consider if you need to have a RAID type replication or can live without this. i.e.: do you need 100% up-time [RAID] or can you afford to have some downtime [single disk and backup] if there is a disk failure.
4> If you have a choice of storage methods with the chosen DB, choose the most appropriate t the task at hand. Again refer to the DB's documentation for advice.
5> Maintenance plan. Ensure you regularly rebuild fragmented indexes and tables, this will help prevent slowing performance over time.

When it comes to RAM, get as much as you can fit into the server you have.

Answer 2

Use Infobright; it is a columnar database for OLAP and archiving needs. It is essentially a MySQL build with a special table engine, so exactly the same user interface. Data is compressed 10:1 and the engine is very fast at aggregated queries, no indexing needed (or permitted as the columnar nature of it already takes advantage of sequential reads).

The limitations are perfect for your scenario, since you only need to load and read. The free version of Infobright disables writes, deletes, and some DDLs. You can also create MyISAM tables within Infobright as well.

Answer 3

@tiredofcoding ;), since your DB is only 14GB, which is not considered a large DB, and you mentioned that No new data will be added to this database, there really is no need to shard. For storing 14GB of raw data, you should consider using a RAM 32 (> 14*2) host. That should be sufficient for storing all your data and indexes in memory in order to achieve your performance requirement of under 5-seconds response time.

Answer 4

(Note: I'm not very familiar with MySQL, so I'll make this answer database-agnostic.)

This is a difficult question to answer without seeing a sample of the data.

• If the data is mostly numbers, for example, the size of the database that has appropriately-typed columns will be much smaller than the source CSVs. The "14 GB" number is pretty much meaningless in this context except to provide a rough upper bound to the raw-data-in-the-database size.

• Since the data only needs to be written once, and memory is a concern, consider using a RDBMS that can compress the data in memory. Given the previous point, this may or may not even be needed, or may actually be a bad idea. While decompression slows read performance slightly, it may be a worthwhile tradeoff. (Test this.)

• What is your definition of response time? Specifically, I'm thinking in terms of maximum network throughput. If you have to shove 500 MB per query over a 1 gig network, you've already blown the proposed SLA. The concern probably won't be how fast the RDBMS can return the data; the concern will be how fast the application can receive and then consume the data. Determine and address the biggest bottleneck.

• As another answer mentioned, consider using a form of partitioning to efficiently access the underlying data. This is really RDBMS-dependent, but for the size of data you're talking about, all of the major systems will handle this easily.

Answer 5

Don't bother sharding your data; the dataset is small and can be easily handled by most modern server hardware.

The weak link here is the efficiency by which your database engine can handle the single index needed for your key. It really won't matter if all of the data can fit into memory, only that the index itself can fit into memory. If the index is smaller than the memory you're allocating for it, you're golden.

Given the rate at which your lookups occur, the index will remain "warm" and constantly in use, which in turn should keep it cached in memory, keeping your response time low.