What kind of hardware/software config should I look at for a MySQL database with 10,000 plus tables

Question

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!

Answer 1

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)

website1_articles
website1_navigation
website1_overhead
website1_blogs
website2_articles
website2_navigation
website2_overhead
website2_blogs
.......
website375_articles
website375_navigation
website375_overhead
website375_blogs

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.

Answer 2

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.

Answer 3

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.