Jim and Jason,

I've been watching the thread and have the following to add.

I have a site with 100's of users and 3GB of data. The main issue of backup
and restore is to:
1) reduce the amount data loss in the case of a disaster
2) reduce the amount of downtime for users in such a case.

We currently:
1) backup at midnight
2) resotore on another machine daily
3) Burn both the backup and restore on CD (thus giving us the protection
against human destruction of data that takes a week or month to find out
about).

I have suggested on the group before a situation not disimilar to Jason's of
having incremental backups that can be applied to pre-restored versions.
This could be done every couple of hours even.

The key for us is that when a server dies and we are unsure about the
integrity of the data, we immediately give the latest read only version of
the software and data, i.e. our turnaround is less than 15 minutes. We can
then address the problem with only minimal disruption to the workflow of the
company.

I have seen databases that have had long standing transactions open for over
24 hours which must really kick garbage collection into touch (e.g. WS
crashes and doesn't get rebooted, the server can still ping gds32.dll
therefore the transaction is not rolled back). When you get larger
database, the "coal face" where the data is being changed tends to be a much
smaller percentage of the database than for smaller databases, this is why
incremental backup is so appealing. So to this end I feel the argument that
the database woulod just get too flaky with old transactions doesn't seem to
be an idea killer.

The trouble with the shadow scenario is that you end up with a duplicate of
the database to be store, moved whatever, when only a fraction of a percent
has changed since the last time the backup was done. Good C/S tells me that
you should only gather what you need, not everything. We move the data from
the main server to the backup servers over the network, we don't use tape
for this process, therefore it shares network bandwidth. This give us
maximum portability - Every PC has a CD.


JAC.

----- Original Message -----
From: Jim Starkey <jas@...>
To: <IB-Architect@egroups.com>; <IB-Architect@egroups.com>
Sent: 17/06/2000 12:10 AM
Subject: Re: [IB-Architect] Backups of large database & super transactions


> At 03:19 PM 6/16/00 -0700, Jason Wharton wrote:
> >Jim,
> >
> >> Jason, please start with the requirements. Unless and until you can
> >> state what problem you're trying to solve it is impossible to judge
> >> whether any particular scheme is a satisfactory solution.
> >
> >
> >AFAIK This is nothing like I am proposing. Please clear your mind of
those
> >biases.
> >
>
> OK, I'm clearing my mind.
>
> >
> >I'm not talking about maintaining an external "log" of any kind. I'm
> >proposing that the existing versioning engine be modified such that the
> >garbage collection and transaction management system will efficiently
> >protect a "frozen" transaction and make it so that the changes since that
> >time can be discernable. I see this as a natural extension/enhancement of
> >the versioning engine we already enjoy. The "log" is actually woven
through
> >the version tree of records. We would just need a way to extract and
> >re-apply them.
> >
>
> You're essentially talking about stopping garbage collection. Record
> chains get longer and longer, data pages get fluffier and fluffier,
> the amount of space filled with near useless crud goes up, indexes
> fill up with useless values pointing to unreachable records, index
> retrievals waste resources chasing deleted records, disk usage
> goes, disk IO does up, memory usage go use, the amount of processing
> required to find a record goes up. That's an extremely expensive
> way to take a snapshot. The resources wasted wallowing in a database
> with internals like a landfill dwarf the cost of spinning off a clone.
>
> Sure, it could be done and it would be easy. As soon as the source
> comes out, we'll make you a special version with garbage collection
> disabled and you can try for a week or two. The bad part won't be
> that your application slowly grounds to halt but the inevitable
> sweep that happens when garbage collection is re-enabled. It will
> finish sometime in the next millenium if you can keep your machine
> alive that long.
>
> Database performance is mostly an exercise in maintaining density
> and locality while avoiding tying yourself in knots. Turning off
> internal maintenance is not that good of an idea.
>
> >What I think it boils down to is a mechanism to extrapolate a "log" from
> >record versions protected under a saved super transaction. Then, on the
> >restore side, it would have to take that log and apply it by creating
> >appropriate record versions.
> >
>
> Sorry, all the versions you'll ever want are already there, clogging
> up the works. But perhaps if I look between the lines I'll discover
> that you really mean "remove the inappropriate record version." Or
> did I miss get it wrong again? You did say you weren't keeping an
> external log of any kind, didn't you?
>
> >I agree that things like Index(s) should be left out. It should only be
> >actual data that gets streamed out. I also think that any metadata
changes
> >should "break" the ability to walk from one freeze point to another. If
> >someone needs to alter their table structures then the database should be
> >considered incompatible with the other structure. Thus, a DBA would
> >establish a new base after performing any structural changes. It is
already
> >my rule of thumb to do a total backup and restore after altering a
database
> >structure...
> >
>
> Now I am confused. You're not keeping a log of any kind, you've got
> a special backup in bed with the engine, and something is streaming
> data.
>
> A backup can never hurt you, but you're wasting your time with the
> restore. There is no loss of performance over a meta-data change.
> The system just keeps track of what's what. We designed it that
> way to encourage people to keep their databases in sync with the
> their understanding of their problem. Anyway, any useful mechanism
> has to survive ordinary day to day operations.
>
> >
> >> So, Jason, in your application:
> >>
> >
> >> 1. How often can you afford to perform a full backup?
> >
> >No more than once a week, I easily imagine others at more of a monthly
> >schedule.
>
> Does that mean you can tolerate a week's loss of data? If so, I don't
> think your applications typical. Most folks get a bit testy when they
> lose a week's worth of work.
> >
> >> 2. After a disaster, how long can a recovery take?
> >
> >Isn't the goal of a commercial product to be as short as possible?
> >
>
> Nope. Everything is a tradeoff. Recovery time is always traded against
> normal update time. If recovery time were the only consideration, things
> would be a lot easier.
>
> >> 3. Is it reasonable to assume a skilled DBA is available
> >> during recovery to resolve problems?
> >
> >If there isn't one then they probably don't have a database over 500MB.
Who
> >cares?
> >
>
> I do. My goal is to reduce the expertise required to care and feed for
> a database, not provide for full employment for DBAs. If you want
> performance in Oracle, learn placement control. If you want both
> performance and a life, use InterBase.
>
> >
> >> 6. When recoverying from a journal, what information would a
> >> DBA logically require to find the stopping point?
> >
> >Again, your biases are leaking in here.
> >
>
> Sorry about that. If the problem was a goof, the roll forward has to
> stop before the goof. The problem is that you generally don't know
> exactly who did what. Unless there is some clue as to when a transaction
> started or what it did, the problem is unsolvable. (Hint: there are
> 2 bits per transaction and four states. How many bits are left over
> to record start time?).
>
> Jim Starkey
>
> ------------------------------------------------------------------------
> Wrox Wireless Developer Conference, Amsterdam, July 10-12. Choose from
> 40+ technical sessions covering application of WAP, XML, ASP, Java and
> C++ to mobile computing. Get your ticket to the future today!
> http://click.egroups.com/1/5689/4/_/830676/_/961197156/
> ------------------------------------------------------------------------
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>