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 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.

  • Not a direct answer as its really not the sort of thing that can have a one size fits all. But a few things will make or break this : how fast are your disks ? and how many do you have ? Ideally for what sounds like a large DB you want to be using Partitioning, say 1000 customers per partition with each partition on its own fast RAID10, and then all of your index's stored on a set of SSD's. But without knowing how many inserts/selects you will be doing its impossible to say much more - you say data is coming in every 15mins - but how much ? 1mb ? 1gb ? 1tb ? Mar 19 '10 at 19:56
  • Lots. The database is already in the around 16tb scale to start with ;) Not mySQL area.
    – TomTom
    Mar 19 '10 at 20:17
  • Don't worry I was not expecting for a direct answer to my question, but it's good to have some advice from real DBA (usually I don't deal with this volume of data). The DB server has 4 10.000rpm HDD in raid10. And to answer your question I took a look to the MRTG graph traffic at the entry of the data collector server and it seems around 20Gbytes a day (I throw 60% of it and only store portions of text, so hard to tell the exact volume, I'll try to catch this in my log). As I say in a another comment the data table is not growing once it reach 5Gigs because the app archive older data.
    – Remiz
    Mar 19 '10 at 21:21
  • The db server is terribly undersized to start with. 4 x10k smells like velociraptors. That is a great disc (I have one myself), but I run a LOT more of them for my 600gb market data storage ;) You definitely will more discs.... at least another 2 for the OS / LOGS. SSD may make sense, too.
    – TomTom
    Mar 20 '10 at 6:20
  • If you would not have the server yet, I would strongly suggest going with a SuperMicro custom build - they have a case that is 2 rack unit and holds 24 discs.
    – TomTom
    Mar 20 '10 at 6:22

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


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.

  • Make some math - the numbers dont work out. We talk of a multi terabyte database.
    – TomTom
    Mar 19 '10 at 20:17
  • I started to math it out, but picked up on terms like 'expecting.' I didn't want to suggest that the poor guy go out and buy a pile of Symmetrix boxes if it's still a work in progress. That said, if the data already exists or is ready for import, Mr Tom^2 here is right, you're going to need some serious hardware and some fast disk. Other things to consider... can some data be archived off? All a working set? Storage tiers?
    – McJeff
    Mar 19 '10 at 20:38

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 ;)

  • You possibly are fine. Make sure you get more discs or can expand easily. SuperMicro has nice SAS cases with 24 discs per 2 HE rackspace - I use them myself on an Adapted raid controller that can address nearly 200 discs. WD Velociraptors are nice, but you MAY want to try SSD in a RAID 5 configuration ;) This should easily scale, hardware wise, for your needs ;)
    – TomTom
    Mar 20 '10 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.