I was asked to give a scaling road map for our mysql database.

Currently, I have a single mysql instance v5.6.28 running on an ubuntu VM on azure. It has a (software) RAID 0 disk made with two 1TBs.

My question is, when this 2TB disk gets filled up, what am i supposed to do? I am thinking that my options would be

  1. add another RAID 0.
  2. add another mysql VM to existing infrastructure
  3. migrate to mysql cluster
  4. ???

I want to know what is the recommended method to scale when the tables grow to an extent that the existing RAID runs out of space.

  • 1
    Why not implement a data retention policy? – Michael Green Apr 11 '16 at 9:33
  • Don't let the disk fill up completely. Keep at least enough room for another copy of the biggest table -- for maintenance, repair, whatever. – Rick James Apr 11 '16 at 19:09
  • Does that OS have a way of extending a filesystem when you add a new drive? If so, that would be the least invasive. – Rick James Apr 11 '16 at 19:10

You have to ask yourself the question "How much is my data worth to me/the company?".

Cheap solution:

You could buy 2 2TB disks to replace the 2 1TB ones you already have. That would be a stop gap IMHO. RAID0 really doesn't cut the mustard these days with bit-rot and 24x7 operation. If one of your disks goes down, you'll place a lot of extra strain on the other disk (possibly provoking its failure - then you're bunched :-( )

More expensive, but worth it (IMHO):

Personally, I'd use a FreeNAS box with RAID-Z also striped and mirrored - or possibly Z-3. If you have the expertise in-house, great, but if not, I'd recommend iXsystems (I am not associated with this company, just have heard good reports from people who work in this area - plus they develop FreeNAS).

  • with azure VMs for mysql, FreeNAS may not be a workable option, atleast for now – smk Apr 11 '16 at 9:16
  • I'm no expert, but if your underlying OS is Ubuntu, shouldn't it be possible to perform regular VM backups which are stored locally? There's also a cloud option - and/or RAID10 (the only kind worth considering IMHO). Found this if interested. – Vérace Apr 11 '16 at 9:30
  • the RAID was constructed using azure storage disks with Zone Redundant Storage option. i am looking for scaling not backup. performance is a new question altogether. – smk Apr 11 '16 at 9:50
  • OK - I've obviously misunderstood your requirements - I thought you were worried about local storage - it appears that you are backing up remotely/to the cloud (I hate the hype about the so-called "cloud"). Do you have reservations about your cloud storage arrangements? If so, what is it about them that you are worried about? But you did mention having 2 x 1TB disks - is this remote/virtual - a "notional" 2 disks or what? – Vérace Apr 11 '16 at 10:09
  • yes, 2 x 1TB Virtual Hard DIsks are added to the virtual machine, converted to RAID 0 and mounted as /data. this is where mysql database files are being stored. mysql server is working fine with this setup. i need recommendations for what should i do when tables grow beyond this 2TB – smk Apr 11 '16 at 10:55

I would go for option increase RAID desks since\if you are using one instance. since RIAD size id dynamic, can be increased any time you want, and you don't have to do any splitting. your options are"

  1. add another RAID 0 ==> means you added another drive that will not be best solution for you. that will be nice if you are using more than one DB
  2. instance. Adding another MySQL VM==> why you want to do that?
  3. Migrate to MySQL cluster==> Cluster can use one storage or multi storage. but will not fix the issue for you, I would go for it ifyou want to increase the performance and reduce the timeout.

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.