11

Ok, really don't know how to ask what I am looking to find out which is likely why my reading and research otherwise is yielding nothing relative per say. So what I currently have is a single MySQL database, and I know sooner or later the data is going to pile up and I will run out of room on this server. So before that happens I want to get stuff in line so I can have the database spread across multiple machines one for load balancing and two pure storage factor.

So my question is, how does that work? Maybe I am thinking too 1 Dimensional here but I can't figure out how that would work, is it one DB that spills over onto the next machine and the next as the data grows or is it individual DB's with the same schema's for each, either way I just can't picture in my head let alone get into a logic for future proofing whats being worked on currently. As we don't want to hit the wall and pull an all stop in the event when the data is just to much for one machine.

So what do I have to read, research, methods, practices, etc to get myself on the right track with this?

1
  • This helped me, I'm a newb when it comes to (my)sql service spanning multiple machines. I've been having a little difficulty finding the right questions to ask in order to break in to this space. +1 both question and answer. ;) Feb 22, 2014 at 16:52

1 Answer 1

9

You can indeed, 'tis MySQL Clustering you need.

One feature in particular - "auto-sharding" - will spread your data across multiple machines. "Online scaling" allows you to add new nodes if you need more capacity while the db is running. Pretty cool. You can download it here.

However, there are a few other things you can consider to resolve the problem, which may be easier to achieve:

Get a bigger hard drive!

Sounds a bit obvious but if you could safely say that a xxxGb hard drive would last you Y years then it might be worth going for. You could either take an image of the drive and install it on the new one, or you could install it as an additional drive and move the MySQL data files to the new drive.<

Split the tables across multiple databases.

Split tables across multiple databases, which can then be stored across multiple drives. Windows Azure has size limitations on its databases and Microsoft recommend this approach. It also improves performance a little.

Can you archive data?

Are you able to archive large tables onto another drive? Is the data really needed? If it's historic and only required for reference can you set up an archival process that pushes it into files on another drive, or into archive tables in another database that points to datafiles on another drive?

1
  • This is intended to be a production system, where yes some archiving can be done over time but in general the expected data that will be regularly called upon by a handful of tables is going to easily be a few terabytes. All though the Clustering bit helps out a great deal Ill have to do some heavy reading on the subject, thanks. The bigger HD is out though unfortunately, that much data on a single point of failure to me is to great a risk.
    – chris
    Mar 10, 2012 at 0:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .