Sybase VLDB SIG 6/94 online conf notes

Sybase VLDB SIG 6/94 online conf notes

Post by Don Plast » Sat, 18 Jun 1994 03:12:00

June 9, 1994

Hosted by:  Don Plaster, David Zodikoff
Moderator:  Perry Bent
Topic: Backup, Restore and DBCC of VLDB's

- - - - - - - - - - - - - - - - - - - -
Conferencing Guidelines and Protocols:

        If you are logged on at 9600+, you may save yourself some mon
        by re-logging ot 2400 baud.  You do not need highspeed comms
        for conferencing!

        Type   ?   on a line by itself if you have a question, and then
                   wait to be recognized by the moderator
               !   on a line by itself if you have a comment, and then
                   wait to be recogznied by the moderator
               GA  when you are finished typing (GA = Go Ahead)

        The moderator will recognize you by typing:

               GA your-name

        You may then type your cont or question.  Everyone else should
        wait until you are finished.  You should type GA when you are
        finished with your question or comment.

- - - - - - - - - - - - - - - - - - - -

Perry Bent:   Ok, why don't we get started?

David Zodikoff:   At the conference, backups of a Gig ranged from 30 min
                  to 2 hrs. any idea about the discrepancies?

Peter Thawley:   No, I haven't heard of such a disparity.  Same HW
                 between the two tests?

David Zodikoff:   Yes, I believe they were both HP 9000.

Peter Thawley:   There wasn't any optimizations in v4.9 that would lead
                 SQL Server from doing things differently.  I suspect there
                 must have been other forces at work here (raw vs file system
                 or perhaps other issues).  All the optimizations went into
                 v10 with the multi-device I/O and large block sizes.

Don Plaster:   There are several factors that might influence backup time
               for a given data size.  Disk speed, processor speed, tape
               speed, etc.  Maybe some other things as well.  Does anyone
               have any tuning suggestions within the software realm, other
               than throwing faster hardware at the problem?

Bill Andreas:   Thanks.  Could you elaborate more about DBCC in 4.9 and
                System 10, Peter?

Peter Thawley:   In v10, we've taken the extent-based I/O concept and
                 extended dbcc to do these large I/O's for dbcc tablealloc.

Bill Andreas:   Are there any other differences?

Peter Thawley:   As far as I know, that is the only performance change.
                 At some point, we reduced the granularity for tables, but
                 I forget when that came.  We can't yet talk futures but
                 suffice it to say there's a lot more relief coming !!!

                 Just looked in the ref and found that in v10, we added
                 optimized versions of the checktable and added checkindex

David Zodikoff:   We just completed a Disk Subsystem benchmark and got some
                  strange results, using HP RAID drives and an I70, we couldn't
                  push more than 80 - 90 I/Os against a disk set up in
                  independant mode, it also seemed that when we changed
                  from ASYNC to non ASYNC that our CPU went down
                  substantially, 8 fold DISCLAIMER!!!  - These are
                  preliminary results still any thoughts?

Peter Thawley:   David.  What was your benchmark doing ... with Sybase's
                 current 2kb io on normal DML, its often unlikely that
                 you can push the devices to their limits.  Larger IO as

David Zodikoff:   Dumps and Loads

Peter Thawley:   David, was the db on raw or did you dump to raw device?

David Zodikoff:   DBs were on raw and we dumped to tape ... do you think
                  the tapes are that slow?  we use DAT drives

Peter Thawley:   David, off the top of my head, I can't come up with any
                 good rationale.  Perhaps we can take it off-line and get
                 more details to understand what you were measuring!

Bill Andreas:   When the server comes up I see a messages about load
                buffers ... are these configurable and will additional
                buffers increase my load speed?  ANYBODY??  Peter?

Peter Thawley:   Bill, which parameter are you referring?  Backup Server
                 currently does not allow one to configure buffers.  It
                 makes this determination based on system info obtained when
                 the device is opened!

Chuck Privitera:   I posed this question at the Sybase conference:
                   when working with VLDBs, we would like a better dbcc. ;)
                   Namely, we want it faster like everyone else,
                   but also, we would like other options besides

                   People on the panel at the conference
                   didn't seem to understand why I didn't
                   want to dropdb a 5GB database, but I think this
                   forum does.

Peter Thawley:   Chuck,  what other options would you like to see on the
                 repair side ( I've got an engineering mgr who's sitting
                 right next to me ...

Chuck Privitera:   Well, in general, I would like to surgically
                   remove the cancer;  basically remove the offending
                   object if possible, and try to salvage the rest
                   of the database.  This way, if I am using something
                   like SQL Backtrack, I can restore a single
                   object (I hope!) and get on with life.

Peter Thawley:   Chuck, so you want single object restore ... how about
                 page patching?

Chuck Privitera:   Well, I don't know if I'd call it single object restore,
                   but single object dbrepair(drop, object).

                   Page patching would be nice too.

                   I suggested something like Norton Utilities for Sybas                   but they were not amused.

Peter Thawley:   Chuck .. could you explain what you'd like again ...
                 dbcc dbrepair (drop) ... does that mean you'd like to
                 bring up a db that's suspect and drop an object ?

Chuck Privitera:   That's right.

Chuck Privitera:   Basically, when you have tons of data
                   I would like to recover as fast as possible.

                   Another option I once requested which was
                   also shot down was to restore a device, since that is
                   usually what goes in case of failure.

Don Plaster:   Like, restore the segment, and roll-forward the updates to
               that segment?

Chuck Privitera:   Exactly!

Peter Thawley:   So what you want is object backup and restore ...
                 Message Received.

Heidi Myers:   Anyone have any more details on TEMPDB?  What does it
               mean for large multi-table selects?

               I'm asking about TEMPDB because we've been told that it
               will only help our small reporting database, not our 4 GIG
               application database.

Don Plaster:   If you have VLDB's, you may need a VL TEMPDB. Also, you
               may want to put it on 'RAM-DISK'.

Peter Thawley:   Some apps require large tempdb's, other do not.   Any
                 application that does lots of sorting, aggregates, order
                 by's, etc. will often need lots of temp space.  Technically,
                 the number of users has a bigger impact.  Systems with
                 1000 users all doing order by's would need as much tempdb
                 space as a system with 10 users order by'ing a VLDB!

David Zodikoff:   To Don's point of 'RAM-Disks' ...

                  We're looking into them, Solid State Devices,
                  not only for Tempdb, but possibly Trans Logs
                  and Stable queues for Rep Svr.

                  They now have reliable backups to disk and battery packs
                  or so I've heard.

Bill Andreas:   Also anyone can set up a RAM-DISK is memory
                under SUNOS no problem.

Don Plaster:   First, the Solid State disk vendor is Disk Emulation Systems
               (not an ad, just a comment).

               Second, there was a talk at the San Jose conference on
               TEMPDB. If Heidi can get the notes from there, she may
               find some more info there.

Don Plaster:   I wanted to suggest everyone update their member entry in
               the forum, so we can see what your interests are.

               Also, if we could reserve a few minutes at the end to discuss
               the benefit of this conference, and the SIG in general, I'd
               appreciate it.

               Now back to your regularly scheduled topic. Thanks.

Heidi Myers:   What about joins between 3 mil record tables?

               My previous question was about TEMPDB.  The responses
               were all directed to sort, order by, etc.

               Also, I'm wondering about joins across 3 million record

Don Plaster:   Are you asking how TEMPDB is used in such a big join?

Heidi Myers:   Yes.

Don Plaster:   If nobody can address that online, I can refer it to Jeff
               Garbus offline.  He has some experience with large joins.

Heidi Myers:   Ok, who is Jeff Garbus?

Don Plaster:   He's one of the other gurus who are helping with the SIG.
               He could not be with us today.

Peter Thawley:   Yes, joins don't necessarily use TEMPDB.  Since we have
                 a rolling result set, we may never utilize tempdb in
                 many joins.

Chuck Privitera:   First, Peter, we should talk about my previous request.
                   What's your internet address?

                   Second, I requested that the SQL Server
                   device capacity be increased from 256 to
                   something more like 1024 or something, since
                   we have a large number of infrequently used
                   devices.  Again, the panel did not understand
                   the rationale for this, but the Navigation Server
                   people told me I shouldn't have let the panel
                   off the hook so easily.

Peter Thawley:   Chuck, increasing the # of devices per db and system
                 is being looked at.  There may be some issues that may
                 limit us which we haven't uncovered yet.  Stay tuned,
                 and as soon we announce the next release, I'll let you

Bill Andreas:   What is the difference between the backup strategy in
                4.9 and 10.  Not just Backup Server, but queueing updates
                done after the start of the dump?

Peter Thawley:   The difference in the dump instant between 4.9 and
                 System 10 is huge.

                 In 4.9 the "point of the dump" (the dump instant) is
                 very near the beginning of the actual dump.  In System
                 10, the dump instant is very close to the end.  This
                 means that the majority of the transactions that
                 occur during the time of the dump actually make it into
                 the dump.

                 The dump in System 10 is done in 3 phases.  I believe
                 these phases are documented in the System 10 documentation,
                 however, it is these phases that allow us to capture
                 transactions (particularly non-logged operations)
                 during the period of the dump.

Bill Andreas:   Great, thanks for the update.

German Batz:   Is it really necessary to run a DBCC everyday?

Peter Thawley:   No, dbcc is important to run, but most people run it once
                 a week depending on their transaction volumes.

David Zodikoff:   *** Conference will now officially turn to SIG business
                      at 2:50 PDT, 5:50 EDT  ; 7 > ***

German Batz:   Some people told me that it is important to run a DBCC before
               I do a backup.

David Zodikoff:   Absolutely, since you don't know if your backup
                  is valid without it.

Don Plaster:   And you probably should run the DBCC AFTER the backup as
               well, if you REALLY want to make sure it is a good backup.

               But how many hours do you have anyway.

               SQL Backtrack says they are working on the ability to run
               the DBCC on the backup tape.  But, what do you do if you
               find a problem?

German Batz:   OK, if I run a daily backup then I have to run a daily
               DBCC too.

David Zodikoff:   To Don's point ...

                  There's what you should do and what you can do.
                  With VLDB there's only so much time in your batch window
                  (pre sys 10) to do even backups, no less DBCCs.

Peter Thawley:   Just a quick note .... DBCC's should be done before dumps
                 and after loads.  These allow one to guarantee the state
                 of the database is "good"

Heidi Myers:   My question involves "hardware" appropriate for VLDBs.

               We are currently running RS/6000 500 series servers to
               attempt to support a database that currently is 4GB, and is
               expected to grow above 10 in the next 6 months.

               We are told that the model 590 we are currently on
               provides us with ample power and that the SCSI interface
               is sutilable for our future needs.

               Any ideas (or amo) on how to approach this issue with our
               hardware folks?

               What do you all use for your platforms?

Don Plaster:   I'd suggest running some benchmarks for what your performance
               is with your current configuration.  Then see if you can
               extrapolate from there.

               Also, look at your disk speed, not just the SCSI interface.
               Is the SCSI SCSI 2 , fast and wide, or what?

David Zodikoff:   I thought we were the only ones doing benchmarks.
                  Anyway, Heidi we are on HP 9000s looking at HPT500s
                  and have begun moving away from VLDB towards
                  VLS, Very Large Systems.

                  We were looking at a 40 Gig DB and were able to break
                  it up into several 2 - 4 Gig DBs by region with a driver DB
                  for those who need to see all the data, luckily very few.

Peter Thawley:   David, perhaps you could explain your rationale behind that

David Zodikoff:   No Problem.  I'll try to keep it short.

                  The prospect of running a National, Hawaii to Maine, DB
                  with 5 - 7K Users was not only daunting, but operationally
                  a nightmare.  Our system is OLTP and we would need one
                  heck of an expensive and big machine to satisfy that load
                  Luckily our business, for the most part is regional, so the
                  bulk of the databases could be split by region with their
                  associated processing.  We use Replication Server for data
                  that needs to be shared, once again, luckily very little.

                  Hope that helped.

Heidi Myers:   Thanks for the information, I'll continue this discussion in
               the VLDB section some other day.  Thanks again.

Peter Thawley:   Does anyone know how much System 10 improved your batch

David Zodikoff:   Better question might be is anybody on System 10, not
                  us yet.

Mark Parsons:   I was running S10 for 6 months and had lots of problems
                with Solaris and SS1000 ...  but the new features were
                great (except for Table RI!! ;-)

Bill Andreas:   Was?

Mark Parsons:   Yeah, I just quit . . . independent consultant now . . anyone
                got a job for me?? ;-)

                I found that the nightly dbcc and dumps were quicker than
                in past with 4.2 or 4.8.  Yep, online backup/dumps were
                hardly noticeable with S10.

Peter Thawley:   Testing has shown that on-line backups are now possible
                 with System 10!

                 Benchmarking on v4.9.2 showed a transaction processing
                 performance degraded by 97% during a dump.  In System 10,
                 transaction processing performance degraded by only
                 3.5 %    WOW.  Buy once, buy often ... Go TEAM GO
                 Enough Sales Tactics

Mark Parsons:   Peter . . you or me?? ;-)

David Zodikoff:   Peter, I know this is a Sybase forum and all,
                  but what about the issues that I've heard on the street with
                  Sys 10.  Are most/many cleared up in newest release
                  have you been hearing fewer complaints?

                  You don't have to answer if it puts you in
                  a funny situation, I understand GA really this time.

Peter Thawley:   David, what issues / complaints are you referring to?

David Zodikoff:   Reliability mostly, but also pains in recoding
                  around some of the optimizer changes.

Peter Thawley:   David, I think all the optimizer changes were well
                 documented.  I guess everyone wanted ANSI until they found
                 out what it meant!

David Zodikoff:   True and Aint that the truth.  Also ...

                  It wouldn't hurt so bad if we coded these apps, but some of
                  the stuff we have to maintain, makes it painful to convert,
                  like 500 items in an IN list.

Peter Thawley:   I suppose this is why good project managers who can plan an
                 upgrade make soooo much money!

Mark Parsons:   One of my major complaints was the whole table RI 'thing';
                limitations on the number of RI constraints(although you
                could create all you wanted, code would bomb if constraints
                on a single table > 16);  buggy compilations with sp's that
                accessed tables that had RI constraints;  RI constraints that
                don't function properly across database lines (could be a
                problem with VLDB users who break data up into separate
                databases but require the RI constraints);  and, of course,
                the goat rope you have to go through to drop and reload
                tables that have RI constraints attached to them (you
                have to 'peel back' all RI constraints, drop the table,
                then re-attach the RI constraints that you 'peeled back'.

                Um, that's just a couple complaints with the RI 'stuff' . . .
                not so much a question as an answer to Peter's question about

Peter Thawley:   Don't worry ... I don't take these personally ... where's
                 that martini I ordered!

Mark Parsons:   Referential Integrity.

Peter Thawley:   There's an EBF of 10.0.1 which eliminates the 16 table limit.
                 I don't recall the # or date but it's there.

Mark Parsons:   Ok . . . sounds good.

Don Plaster:   What are the VLDB performance issues, pros, cons for using
               declarative RI, rules, etc, in 10.0 synatx, rather than the
               4.x way of using triggers?

Mark Parsons:   Well, just one follow up . . hey Peter, you didn't address
                the other RI issues!! ;-)  you just threw me a bone!! ;-)

Peter Thawley:   No flies on you huh.

                 Obviously, you need to tune your RI to ensure adequate
                 indexes etc to avoid unnecessary I/O.  Triggers generally
                 offer more flexibility in Integrity Coding but either way,
                 one needs to do standard tuning for performance.

Mark Parsons:   Don't forget that with RI that cascaded deletes are much
                trickier.  Table RI is also much harder to maintain in a
                development environment . . I'm done.