I am looking at rolling out a CMS system that will require the creation of around 10,000 tables within the primary MySQL database of the system.

The database will be the data store for several hundred small website front ends that might draw a modest load of around 150k unique viewers per month, but this might have to scale on short notice.

  1. I'm looking for some advice around what kind of hardware should be used to be cost effective but also to have the ability to scale if the need arises.

  2. I would also like some advice around the software configuration: i.e. should the MySQL setup be clustered or just straight forward MySQL with a high number of open files?

Any advice will be greatly appreciated!

  • 6
    I don't know anything about (hard/soft)ware config, but 10,000 tables seems a lot for what you are describing, are you sure the database is normalizated correctly?
    – eiefai
    Jan 16 '11 at 23:40
  • Yea, it is and it can't be changed. The tables are reasonably small and the queries won't be overly complex and the heavy lifting will be cached. I just need some direction as so what kind of hardware infrastructure would be recommended for such a large number of tables.
    – Shawn
    Jan 17 '11 at 1:07
  • 2
    10000 'reasonably small' tables. Hmmm... Jan 17 '11 at 2:24
  • 2
    how much data in each table? "Table" is a bad metric for performance exploration. Jan 17 '11 at 6:01
  • The tables won't get much larger than a reasonably sized blog. My issue is not so much the query time as it is the sheer number of tables and following that, open files that the database must maintain. The actual overhead from query load should be manageable; I don't expect there will be much more than 10 to 15 concurrent requests at any given time.
    – Shawn
    Jan 17 '11 at 17:43

This is no different than the average shared web hosting company. Hundreds to a thousand or more small sites sharing single database server. The high number of tables won't affect anything. I have a database server with over 40,000 actively used tables.

So don't use that as a basis in your hardware planning at all. Get as much RAM and fast disks as you can, as that's what your bottlenecks will always be.

When you grow beyond the capacity of one server, partitioning will be simple thanks to having separate tables for each CMS instance. You just need a scheme to map each one to which server its tables are on.

  • 1
    Yay, someone from a field that is not mine, who has experiences that are not mine. Also note he finally gave more detail after I posted my exposition ;)
    – jcolebrand
    Jan 25 '11 at 2:56
  • Thanks for the answer, that is pretty much what I wanted to hear. My plan is currently for a master DB server with dual quad core, 24Gb RAM, 2x 120Gb SSD disks in RAID1 and a replicated slave as a warm failover and to change the MySQL config to allow for a lot of open files. Think that might work?
    – Shawn
    Jan 25 '11 at 5:47
  • Probably overkill for the level of traffic you're expecting -- if you can afford that setup, you should have room to grow I'd think. Jan 25 '11 at 6:26
  • Yea, I have to over spec since I don't have the luxury of time if more traffic arrives than anticipated. Thanks for your help!
    – Shawn
    Jan 25 '11 at 8:22

welcome to the dba.StackExchange.com website, and it looks like you've got quite a pickle. So before I answer the question that you posed, I'll mention some details to (hopefully) get you off to a good start:

10,000 tables in a database are always wrong.

I take that back. If you had all of the Microsoft or IBM corporate databases on one server, 10k tables might be appropriate. However, the data aggregate that outlines is enormous. So we'll overlook the "it's possible to be right" and stick with the 99% of statistical non-outliers. For all intents and purposes if you suggest 10,000 tables to a dba, they will laugh at you.

I'm going to hazard a guess that your tables look like: (I don't mean the exact structure, I mean the concept of the structure)


which would (for this very simplistic example) yield me right at 1500 tables. But if you'll notice, the only thing that changes on each one is the website ID.

I could instead normalize to include a website ID on each record and reduce the entire mess to four tables.

Now, I know what you're thinking:

"But that means I have to redo my entire database architecture, and it will ruin everything."

OR you're thinking:

"But that will destroy the security, because then anyone could read anyone else's website information"

But really a) you've already killed the database by suggesting 10k tables, and b) this is in no way different than what you're suggesting.

Of course, it's entirely possible I'm wrong here, but anytime anyone suggests 10k tables, this is the course they're taking. And it's wrong.

So here's my bit of teaching for the day:

SQL is about sets. You should really consider that the power of SQL is in its ability to handle sets quickly and easily, and in the ability of the language to parse for the relevant matches to a set of query parameters. What this means for you is that the intent of SQL is to have one massive table of all blog posts (to pull a single example, that may or may not be appropriate) and to select the matching blog posts from the table by query.

Additionally, you ask about open files, but that's just it, by doing what I suggest above, you have at most four tables to worry about open files on. And it's "how the big boys generally solve this problem" if you're really curious.

But the fact that you're asking about open files and other micro-optimizations leads me to think that

The real point I'm trying to make here is this: Don't try to re-engineer the database according to what you think would make it optimized, because you're not a database engineer.

While this may seem to be a bit harsh, it's not. I assure you. If you were a database engineer, or worked on a database core-development team, you would already know the answers to the questions. Thus QED. But you have to understand that this is what it sounds to an experience DBA like what you're doing; this trying to optimize the database by theory rather than rational approach.

Now, having said all that, and having been a bit harsh, and having seemed to reject your request outright, I'ld like to ask for more details so I can really focus on answering the question you've asked, altho, if I can hazard a guess, you've gone and ignored everything I've had to say after the second paragraph.

So with that in mind, here's what would make this question a little more answerable:

What platform are you running this on? How many disks are there? What size and speed are they? What version of the OS? If Linux, what distro do you intend on running? Will this be in a VM? Will it be supported for updates in the future or do you need a prop-once and forget box? (meaning negligible long term support budgeting)

And if I may ask, what framework are you using for this database that it will have 10k tables? I ask for personal curiousity.

  • 1
    Epic answer, I consider myself properly berated! I will post an answer to your other questions in a bit.
    – Shawn
    Jan 18 '11 at 2:48
  • 1
    @shawn lol ok, I'll look forward to revamping and revisiting ;) ... I don't think anybody here will ever mind helping you get this setup, just that 10k is soooo wrong
    – jcolebrand
    Jan 18 '11 at 4:00
  • 1
    Right, so the setup is a 650 site WordPress MU (network install) and as far as I know, there is no way to change it to use separate databases. It creates all the tables in the one database and about 9 per site. I don't have the time to change the WordPress code and can't change the platform, so I have to deal with the tables. The DB server will be a beefy server or three (not virtual) and will be Linux (probably Ubuntu). The setup will also have a large memcache setup to reduce DB load.
    – Shawn
    Jan 25 '11 at 1:03
  • You should really rewrite the question to include that information and a bit more detail on what sort of server you have, and what your options are
    – jcolebrand
    Jan 25 '11 at 2:57
  • At the time of writing I had no server (I still don't), the question was to get advice for what server (or cluster of) to get. I also don't think the fact that it's a network install of WordPress makes a difference, there will still be between 6k and 10k tables in the DB (wrong as that may be).
    – Shawn
    Jan 25 '11 at 5:41

I see from one of your answers you're planning on using SSDs for your server. Bear in mind you'll need to take into account TRIM support or your drives will slow down over time. Most of the recent drives support "Garbage collection", where they clean the drive up in idle time if there's no TRIM. There might not be a lot of that in a heavily used server though.

Not sure what OS your planning, Windows 7 supports this out the box. From what I can gather Ubuntu supports this in 10.10 and can be enabled like this. HTH.

  • Thanks for that, I hadn't considered it before. I'll mention it to the sysadmins that will take care of the box.
    – Shawn
    Jan 28 '11 at 4:02

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.