Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2004
    Posts
    21

    Unanswered: Dump speed of ASE 12.5.2

    Anyone that knows at approximately what speed
    and ASE 12.5.2 server dumps at?

    We have a system where it takes 4,5 hour to dump 5,2 Gb.
    "Backup Server: 4.58.1.1: Database leffe: 5236292 kilobytes DUMPed"

    Is this a normal backup speed?

    We are using compress level 6 if that has much impact.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The speed of dumps will of course depend on the hardware, and in particular on the speed of the IO subsystem.

    You can often get better performance by striping the dumps (i.e. splitting the dump file into 3 or 4 stripes), and I usually use level 1 compression which I find sufficiant and uses a minimum of CPU.

    Have you run top/nmon/etc to see what the machine/OS is doing during the dumps?

    Michael

  3. #3
    Join Date
    Oct 2004
    Posts
    21
    We run it on an SUN E2900 that has 8 CPUs, so the machine is quite powerful
    and we use 32 stripes.

    I haven't run performace tool like top/prstat during dump..
    I think that this will be the next step to check this.

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Absolutely. For example here I have a 30GB database that is dumped in 11 minutes (6 stripes) on an IBM PSeries (AIX 5.2, 6 CPUs) machine...

    Michael

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    When compressing it largely depend on your CPU speed
    I suggest using stripes equal to the amount of free CPUs and minimum (default) compression.
    More stripes will just make the processes fight for CPU.

    I get similar performance than Michael
    Dump 45074270 kilobytes in 12 minutes using 6 stripes at minimum (default) compression on a SUN V880 with 8 x 1.2 GHz CPUs

    The same dump on an old E5500 10 x 400 MHz CPU take 38 minutes
    3 times longer at 1/3 the CPU speed
    Last edited by pdreyer; 07-06-06 at 10:44.

  6. #6
    Join Date
    Jul 2006
    Posts
    13
    dump is mainly I/O process, so what is the speed of your tabe?
    Are datbase devices on RAID device level 5 , make system diagonestic.
    Try dbcc before dump, it is recomended by SYBASE.
    Do dump as nighty job.

    M. Hassan

  7. #7
    Join Date
    Oct 2004
    Posts
    21
    We are dumping to filesystem.

    Will the backup be faster if a "dbcc checkstorage" is done before the dump?
    How many stripes is recomended if we try this on a V440 with
    four 1593 MHz CPUs?

  8. #8
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Running dbcc checks prior to making the dump is not likely to speed things up (AFAIK).

    As for the number of stripes - I think you should run a few tests (if you can) to see at what point you saturate the CPU and/or IO subsystems. As a rule of thumb, if you are running the backups at a time when the system is fairly lightly loaded then I would use as many stripes as CPUs as a starting point.

    Michael

  9. #9
    Join Date
    Oct 2004
    Posts
    21
    I have tried with 4 stripes and with 32 stripes and the backup time was more or
    less the same. So hopefully there is something else that can be done.

  10. #10
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by epkhani
    I have tried with 4 stripes and with 32 stripes and the backup time was more or
    less the same. So hopefully there is something else that can be done.
    Have you tried using no or less compression? I use level 3 most of the time. It gives sufficient compressing in an acceptable time.
    I'm not crazy, I'm an aeroplane!

  11. #11
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    4.5 hours to dump 5.2gb is very strange - most of us do 5.2gb in minutes, not tens of minutes. Is 5.2gb the allocated db space or the used db space (ie. total dump file size) ?

    1 Eliminate compression, or use level 1
    2 If you are dumping to 'filesystem', then make sure that the filesystem does not contain the db you are dumping as well, as you will be fighting with yourself.
    3 Stripes are great but for 5.2gb, you really do not need them. If you use them, start with no_of_stripes = no_of_CPUs. 32 on an 8 CPU system is simply wrong.
    4 How many CPUs are available for the Backup Server. Ie. figure out how many are dedicated to ASE; whate else is running; what is left. You may end up with say 3 stripes/CPUs. Therefore 32 stripes were fighting over say 3 CPUs and they were strung out compressing the bejesus out of it.
    5 Your filesystem needs examination. Ensure that the dump space is not fragmented, and spread it across spindles.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  12. #12
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    On what devices does the database live (internal disk, fibre attached storage array, etc).
    And where do you dump to? NFS mounted disk over slow network?
    Or if you dump to /tmp (tempfs) when you are low on memory the system will swap and adversely affect performance.

    To eliminate Sybase try the following on your disks
    Create a 500MB file with random data e.g.
    I copy 100MB of my sysprocs device
    # time tail -104960000c sysprocsdev.dat >xx

    real 0m5.63s
    user 0m0.01s
    sys 0m0.77s
    ### Copy it 5 times
    # time cat xx xx xx xx xx >yy

    real 0m23.25s
    user 0m0.00s
    sys 0m4.37s
    # ls -l yy
    -rw-r--r-- 1 syb125 sybase 524800000 Jul 21 11:28 yy
    ### test read & write without compression
    # time cp yy zz

    real 0m23.04s
    user 0m0.01s
    sys 0m3.75s
    ### Then test with compression
    # time gzip -1 zz

    real 0m37.00s
    user 0m30.20s
    sys 0m2.07s

    So that mean for 5 Gig it will take
    37*10/60=6.2 minutes
    And 50 GB over 6 stripes should take
    37*100/60/6=10.3 minutes
    which is close to the actual time I get with Sybase

    And gzip 6
    # time gzip -6 yy

    real 1m29.67s
    user 1m24.74s
    sys 0m2.21s

    So 5 GB should take
    (60+29.67)*10/60=14.95 min
    And 50 GB over 6 stripes should take
    (60+29.67)*100/60/6=24.9 Min
    # rm xx yy.gz zz.gz

  13. #13
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    PS. To more accurately simulate stripes you can split the 500 MB into e.g. 6 to simulate 6 stripes.
    # time tail -87381334c sysprocsdev.dat >xx

    real 0m5.16s
    user 0m0.00s
    sys 0m0.68s
    #time (for x in 1 2 3 4 5 6
    \> do
    \> cp xx yy$x &
    \> done; wait)
    [1] 21971
    [2] 21972
    [3] 21973
    [4] 21974
    [5] 21975
    [6] 21976

    real 0m12.05s
    user 0m0.00s
    sys 0m4.72s
    # time (for fn in yy*
    \> do
    \> gzip -1 $fn &
    \> done;wait)
    [1] 21992
    [2] 21993
    [3] 21994
    [4] 21995
    [5] 21996
    [6] 21997

    real 0m7.45s
    user 0m31.73s
    sys 0m2.71s

    So for 50 GB over 6 stripes it should take 7.45*100/60=12.4 min

  14. #14
    Join Date
    Aug 2006
    Posts
    4
    sp_helpdb <dbname>
    go

    and you'd better return your backupserver log

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •