We’re making some changes to the way we do MySQL…
One of the most popular suggestions we have is to be able to have multiple databases per hostname. Along with that, people want to be able to use the same mysql username to access multiple databases. To top it off, they are even so bold as to want one PhpMyAdmin management area for all the databases on their account!
This hasn’t been possible with our system since very near the beginning, because our customer database servers are separate from our web servers. That’s why you can’t use “localhost” to connect to your database. It is not why your database may sometimes seem slow. The reason for that (if it happens) is because the database server itself is overloaded (or maybe dns was messed up). And actually, if it had been the same physical machine as your web server, that would have also meant all the websites on that server would be slow too (not just the ones accessing that database server). SO THERE!
But that’s not really why we kept our database servers separate from our web servers. The main reason is we mount the file system on our web servers over NFS, and that is just noooo good for MySQL performance. The other reason is we do performance tweaks on our MySQL servers that we wouldn’t want to do on Apache servers.
Now, when you’ve got your database servers separated from your web servers, you’ve got to have some way to determine which database server new databases go on.
The simplest thing, I guess, would be to just assign database servers to particular web servers.
If we’d just done that, everything people are asking for now would have always been possible!
But, there is one teeensy drawback with doing it that way. It’s not the most efficient use of hardware.
And when you’re a self-funded, completely independent, low-price, big-feature web host, efficient use of hardware is pretty much the difference between driving a ferrari and driving a limo. Let me explain (not the “driving a limo” part)…
Here are the three essential facts for your consideration:
1. Each database server has a maximum number of databases it can support.
2. Customers continue adding databases gradually for the life of their account with us.
3. Moving databases between servers causes some downtime and is a big pain in the admin’s behind.
So our solution to these three facts was to just make a current “active database server.” Any new MySQL created by a customer, regardless of their home web server, went on it, and when it filled up, we just added a new one.
That works great, and is pretty much the maximum efficiency you can get in terms of hardware use.. at any given time you only have one non-full database server, and it’s in the process of filling up pretty darn fast!
The only thing that doesn’t work great is one person’s databases are most likely spread across multiple servers… which means you need a separate hostname for each one, and most importantly, you can’t do all those things you Happy Customers clamor so loudly for! It also means if one database server poops its chute, a huge swath of customers are affected instead of just the ones on web servers tied to that database machine.
But still, it was the cheapest and easiest way to do things!
However.. we’re rich now and we like a challenge!
So, we’re in the process of changing our system to start assigning each new customer to a database server for life! What this means though, if we don’t want to move databases later (see fact 3 above), is we have to essentially “cut off” a database server before it’s “full’ (see facts 1 + 2). And that means add another database server sooner than we would otherwise have to.
But it also means all your databases will be on the same server, which means soon you WILL be able to manage them all from one hostname (and one username). It will also mean there’ll be less chance of your databases being affected should a random mysql server have problems. In fact, it also also means if you’re a heavy MySQL user and causing problems for your server, you’ll mostly be affecting your own performance and not too many unsuspecting neighbors!
It’s more expensive, but you guys are worth it!