MySQL : table organisation for very large sets with high update frequency

Question

I'm facing a dilemma in the choice of my MySQL schema application. So before I start here is a picture extremely simplified of my database :

Schema here : http://i43.tinypic.com/2wp5lxz.png

In one sentence : for each customer, the application harvest text data and attached tags to each data collected.

As approximation of the usage of each table, here is what I expect :

• customer : ~5000, shouldn't grow fast
• data : 5 millions per customer, could double or triple for big customers.
• tag : ~1000, quite fixed size
• data_tag : hundred of millions per customer easily. Each data can be tagged a lot.

The harvesting process is permanent, that means that around every 15 minutes new data come and are tagged, that require a very constant index refreshing.

A lot of my queries are a SELECT COUNT of DATA between specific DATES and tagged with a specific TAG on a specific CUSTOMER (very rarely it will involve several customers).

Here is the situation, you can imagine with this kind of volume of data I'm facing a challenge in term of data organization and indexing. Again, it's a very minimalistic and simplified version of my structure. My question is, is it better:

1. to stick with this model and to manage crazy index optimization ? (which involves potentially having billions of rows in the data_tag table)
2. change the schema and use one data table and one data_tag table per customer ? (which involves having 5000 tables on my database)

I'm running all of this on a MySQL 5.0 dedicated server (quad-core, 8Go of ram) replicated. I only use InnoDB, I also have another server that run Sphinx. So knowing all of this, I can't wait to hear your opinion about this.

Thanks.

---

edit

Thanks to your answers I realize how crazy this numbers are. So here is an updated more realistic usage of the tables (based on the actual server that is just a basic rackspace box).

• customer : 2000 (fixed)
• data : 1 million per customer (fixed, archiving of older data. And very unfair : some customers have few thousand, the biggest 5 million)
• tag : 1000 (fixed)
• data_tag : ~3 or 5 millions per customer (depend on data, so unfair too).

Thank you.

Answer 1

My 2-cents based on my experience using MySQL for many years is that your latter option sounds more logical and realistic.

Going with one Data and one data_tag per customer has simpler overall manageability than your current schema. Coding for your second option will be simpler as well.

You can ask many more MySQL experts; your second option is the best.

I can go into detail if you like, this is a simple answer for a simplified question to a big issue. it goes both ways

Answer 2

Without knowing a whole lot about your application other than what you've put here, it's slightly difficult to say. Your data model is pretty simplistic and that's to your benefit as you're expecting, literally, billions of rows. I would avoid creating upwards of 5k tables as you'll probably run into file descriptor problems and cache limitations down the road if you try that.

Granted, you can probably ulimit/configure them away, it's still not an optimal configuration.

Are you creating indexes on non-key data as well? These name columns, for example? That could slow down your write performance such that your 15 minute batch jobs get backed up.

Quite honestly, if this were my application, I would look at two potential solutions:

1. Go with what you have now and split customers up between multiple MySQL servers if performance becomes a problem. Unless you have this data and these customers lined up, it's not a problem just yet. Don't spend too much time designing for "what if." Stick with the simplistic schema and introduce your first set of users to the first server. When you start getting to capacity, introduce a second server and isolate those new users to that database. Sharding, so to speak. Back it up with resource monitoring and good administration techniques so you know when that "at capacity" line is getting close.

2. Would something like Cassandra or MongoDB work? I don't know enough about your queries to suggest it or rule it out. MongoDB might be an option. Worth checking out.

So, I guess in short, let MySQL do what it does well, just run more of them. Or, if possible, look at something like Mongo.

Answer 3

Hm, from my experience - are you sure MySQL is even the best database for that? Tried looking at Oracle or SQL Server (though oracle clustering may have an advantage here)?

If you think the licensing cost are going to kill you, let me just say that you dont have an idea yet what hardware you are going to need to run it. Once you get the first offers for the SAN you need for that - you probably will laugh at the price of the corresponding software.

Just an idea.

• Customer - lets say 10.000, as you indiccate it will grow fast.
• Data - let's assume 7 million for an average customer. That is already 70 bilion rows for the data table. Yeah, sorry, the 4 zeroes really add that up.
• If you get 10 tags per data (you dont indicate anything) we talk of closely to 700 billion rows for the data_tag field.

Gets crazier.

• If DataTag has no index and no overhead (which it has), data:tag is 10 bytes per entry - 2 for the tag_id (65536 is enough), sadly 8 for the data_id - you can not address 700 billion entries in 4 bytes. This is a total of around 7800 gigabyte of raw data (700.000.000.000 * 12 / 1024 / 1024 / 1024). Indexing POSSIBlY doubles that.

In order to process that efficiently this is a HIGH END SAN. We dont talk of "10 discs" here, we talk of a high end SAN with possibly 400 upward discs in order to handle all this data - don't forget so far we dont really have ANY indices.

I'm running all of this on a MySQL 5.0 dedicated server (quad-core, 8Go of ram) replicated.

NICE try. That is good for exactly what? Sorry to ask, but 8gb RAM wont really help (not impressed here), go for a 256gb machine... Which probably requires AMD and one of those really expensive Opteron 8000. But you will need the RAM.

By any means, this would be (I doubt you properly represented the facts) one of the largest database installations on the world.

You DEFINITELY want something that can handle that - Oracle clustering or SQL Server clustering may work speeding this up if you really have to do that. This is imho WAY above what free databases can even think of handling. Really.

And you need proper backup procedures in place (which MySQL lacks). You also may LOVE SQL Serve 2008 Data Page Compression which MAY reduce your data size around 50% on disc. Not only for the saved costs disc wise, but because it means less IO - which directly translates into more performance here (as you can not cache the table in memory).

As much as I hate to say it, you may also want to consider using IBM DB2 on a nice Mainframe - and I dont mean running a Linux VM on it. VMS is vastly superior for handling super scale databases due to the hardware architecture. Dont ask about the price ;)