MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Andrew Braithwaite Date:July 23 2003 9:33pm
Subject:RE: large mysql/innodb databases
View as plain text  

I'm afraid that with that amount of data and having a few huge constantly
updated tables will result in huge restore times for disaster recovery (just
untaring/copying backups of the magnitude of terabytes back to the live
environment will take hours and hours..)

You're talking "massive enterprise sized solutions" and "we're on a budget"
in the same sentence (which are not compatible with each other) - I know
because we are the same here!

A couple of things I can suggest:

1. Redesign your applications so that you archive/aggregate information that
will never be used again.

2. Write a function that will backup the "often changed" stuff on a daily
basis and backup the seldom changed stuff on a weekly basis.  (as you're on
a budget use a few inexpensive IDE raid 5 linux boxes - 6 x 250GB = 1.25 TB
for backup)

3. Put in place a replication system that is so resilient that how ever many
machines go down, there will still be plenty of fully replicated servers to
satisfy the demand.  Make sure that you have UPS so that if the power fails
you can get a clean shutdown. And ignore backups completely.

Hope this helps,


-----Original Message-----
From: Joe Shear [mailto:joe@stripped] 
Sent: Wednesday 23 July 2003 21:50
To: Andrew Braithwaite
Cc: mysql@stripped
Subject: RE: large mysql/innodb databases

The data is constantly updated.  There are 3 or 4 huge tables, and several
smaller tables.  We would love to have an incremental solution that is
*guaranteed* to be correct, but we haven't found a way to do that, so what
we've been thing is we'd do a complete snapshot once a week, and do
incremental backups of one form or another every day.  The replicated slave
is allowed to stop replicating during backup.  There is no absolute
requirement on the time needed to restore.  We'd like most disaster recovery
to go fairly quickly, but we realize that on our budget, that a major
disaster could cause us fairly significant downtime.

On Wed, 2003-07-23 at 13:43, Andrew Braithwaite wrote:
> Hi,
> We have similar numbers here.
> A couple of questions:
> - are they logfiles that could be rolled over on a daily basis or are 
> they constantly updated huge tables?
> - is the type of backup you want incremental or a daily/weekly 
> snapshot one?
> - do you have a requirement for the speed of restore needed in the 
> case of disaster recovery?
> - is the replicated slave allowed to stop replicating whilst the 
> backup is being performed?
> Let me know and I think I'll be able to help :)
> Cheers,
> Andrew
> -----Original Message-----
> From: Joe Shear [mailto:joe@stripped]
> Sent: Wednesday 23 July 2003 21:08
> To: mysql@stripped
> Subject: large mysql/innodb databases
> I was wondering if anyone had any experience with setting up large and 
> fairly high performance databases.  We are looking at setting up 
> databases with each machine having somewhere between 500 gigs and 2 
> terabytes along with a slave box and we'd like to backup everything to 
> tape at a minimum of once a week, but if possible, daily.  We're also 
> looking at central storage solutions.  However, we're hesitant because 
> that will result in a (very
> expensive) single point of failure.  Of course, we could buy 2, but they
> fairly expensive.  Has anyone had any experience with setups like this?
> What kind of backup solutions did you use?  We aren't too concerned about
> the CPU usage as our databases tend to be i/o bound.  
> -- 
> Joe Shear <joe@stripped>
Joe Shear <joe@stripped>
large mysql/innodb databasesJoe Shear23 Jul
  • Re: large mysql/innodb databasesmos23 Jul
RE: large mysql/innodb databasesAndrew Braithwaite23 Jul
  • RE: large mysql/innodb databasesJoe Shear23 Jul
RE: large mysql/innodb databasesAndrew Braithwaite23 Jul
  • RE: large mysql/innodb databasesJoe Shear23 Jul
RE: large mysql/innodb databasesAndrew Braithwaite24 Jul
  • RE: large mysql/innodb databasesAdam Nelson24 Jul
  • RE: large mysql/innodb databasesJoe Shear24 Jul
    • RE: large mysql/innodb databasesAdam Nelson26 Jul