MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Adam Nelson Date:July 25 2003 2:54pm
Subject:RE: large mysql/innodb databases
View as plain text  
There's also netapp (www.netapp.com).  I don't have much experience in
some of these things, but I would stick with the same vendor that you
use now or whomever they have a partnership with.  I can't imagine that
Sun,HP,EMC,Netapp,IBM have major differences in the quality of their
solutions or even the price (when totatl cost of ownership is included).

> -----Original Message-----
> From: Joe Shear [mailto:joe@stripped] 
> Sent: Thursday, July 24, 2003 3:09 PM
> To: Andrew Braithwaite
> Cc: mysql@stripped
> Subject: RE: large mysql/innodb databases
> 
> 
> On Wed, 2003-07-23 at 15:27, Andrew Braithwaite wrote:
> > >>Power problems are handled by our colo facility, we want 
> to quickly
> > restore for most hardware problems (disk/machine failures). 
> > 
> > Just have multiple inexpensive fully replicated servers 
> with failover built
> > into the application layer (that's what we do) - Individual 
> machines can go
> > down and the service still stays up.  When those dead boxes 
> recover, they
> > can catch up from the replication logs and go back into service..
> 
> Our first backup plan is to move over to the slaves if one of the
> masters fails right now, but we do need to have something on 
> tape, just
> in case of some sort of major disaster.
> 
> > >>On a periodic basis, we will take a snapshot using innodb 
> hotbackup of the
> > master machine that will go to a third box with a bunch of 
> big raid-5 ide
> > drives. We were planning on starting with NFS for the short 
> term since
> > innodb hot backup doesn't go over the network and figure 
> something else out
> > later.
> > 
> > That's a good idea - my findings were that NFS was really 
> slow and the best
> > solution was to backup from a fully replicated slave (after it had
> > temporarily stopped replicating) by piping the raw data 
> files through tar
> > and gzip (appropriate for you as you're not concerned abou 
> cpu) to a backup
> > big raid-5 ide server.
> > 
> 
> NFS seems to be working ok for now since it's writing to a big raid-5
> ide server.  Heikki mentioned that they are working on adding the
> ability to use innodbhotbackup over a socket, so we plan to 
> move to that
> when it's available.  We aren't really comfortable with 
> taking snapshots
> from a slave since we've had problems with slaves getting 
> corrupted over
> time.  Most of the problems have been fixed, but until there have been
> no replication bugs fixed for a few months, we aren't comfortable
> relying solely on it.  
> 
> > >>One issue we have is that we are trying to plan out our 
> setup for storing
> > a total of about 25TB of data and we are trying to find the 
> lowest cost
> > solution, with decent reliability.
> > 
> > And I'm trying to find the secret of eternal youth :)
> > 
> 
> We're considering moving to a solution like EMC's -- do you or anybody
> else have any experience with that?  
> 
> > Cheers,
> > 
> > Andrew
> > 
> > 
> > 
> > 
> > -----Original Message-----
> > From: Joe Shear [mailto:joe@stripped] 
> > Sent: Wednesday 23 July 2003 22:51
> > To: Andrew Braithwaite
> > Cc: mysql@stripped
> > Subject: RE: large mysql/innodb databases
> > 
> > 
> > We don't expect recovery to be shorter than the time it 
> takes for the
> > hardware to copy the data over.  Restoring from tape should 
> be a solution
> > that is only needed in the case of a severe problem.  Power 
> problems are
> > handled by our colo facility, we want to quickly restore 
> for most hardware
> > problems (disk/machine failures).  
> > 
> > We don't actually store any archive/aggregate information.  
> Everything we
> > store on the main databases is used on a relatively 
> constant basis.  
> > 
> > What we are currently thinking about doing right now is 
> having an identical
> > master and slave, each with about 500 gigs (later these 
> will be at about 1TB
> > each).  On a periodic basis, we will take a snapshot using 
> innodb hotbackup
> > of the master machine that will go to a third box with a 
> bunch of big raid-5
> > ide drives.  We were planning on starting with NFS for the 
> short term since
> > innodb hot backup doesn't go over the network and figure 
> something else out
> > later.  This machine would then shutdown the slave, copy 
> over the new
> > snapshot, and restart replication at the point from the 
> point that innodb
> > hotbackup started running at. 
> > We would also take the snapshot from the IDE box, and write 
> it to tape at
> > this point.  Any thoughts on this?  What are you doing?
> > 
> > One issue we have is that we are trying to plan out our 
> setup for storing a
> > total of about 25TB of data and we are trying to find the 
> lowest cost
> > solution, with decent reliability.  
> > 
> > On Wed, 2003-07-23 at 14:33, Andrew Braithwaite wrote:
> > > Hi,
> > > 
> > > 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,
> > > 
> > > Andrew
> > > 
> > > 
> > > 
> > > -----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
> > > are
> > > > 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>
> 
> 

Thread
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