Post reply

What's Your Backup Speed?

  • November 3, 2016 at 9:52 pm

    #400987

    Comments posted to this topic are about the item What's Your Backup Speed?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • November 4, 2016 at 2:18 am

    #1910527

    Steve Jones - SSC Editor (11/3/2016)

    I think it's one of the most solid subsystems in SQL Server

    I should jolly well think so!!!

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • November 4, 2016 at 2:57 am

    #1910531

    1.4 TB database, 52 mins backup time.

    So, 1 TB would be like 37 mins. Approximately 27 GB/min, 450 MB/s.

    This is with Compression = On.

    Database is on RAID 6, 10 SSDs. Backup is on 8 HDDs, don't remember the RAID level.

  • November 4, 2016 at 3:10 am

    #1910533

    165GB in 10 min 24 sec, which would mean just over an hour for 1TB (63 minutes).

    DB files on SAN disk, SQL 2008 standard, Red Gate SQL Backup with level 1 compression (produces 21.5GB file at 87% compression).

    441GB in anything from 18 to 25 minutes. 18 minutes would mean 1TB in under 41 minutes.

    Again DB files on SAN, Red Gate SQL Backup with level 1 compression (produces 13GB file at 97% compression) but this time we're running SQL 2012 Enterprise.

  • November 4, 2016 at 5:40 am

    #1910558

    20-mins (190GB in 221s @ 880MB/s - my Client's most important database)

    Windows Server 2008 R2 Enterprise x64 (VM 4vCPU 14GB)

    SQL Server 2008 R2 Enterprise x64

    Native backups via Ola Hallengren

    Compression

    Checksum

    8-stripes

    BufferCount 64

    Blocksize 65536

    Files on HPE 3PAR StoreServ 8200 (16x 3.84TB cMLC SSD SFF RAID5 (3+1))

    Backup across network to HPE StoreEasy 1650 NAS server (8x 4TB 7.2K SAS RAID6)

    10 Gbit/s Ethernet (no jumbo frames or dedicated backup network)

  • November 4, 2016 at 7:28 am

    #1910584

    2.4TB in just over 2 1/2 hours, or 1TB in just over an hour. This is for ~40DBs in total, compression ON. SQL 2012 using native backups.

    Ken

  • November 4, 2016 at 7:28 am

    #1910585

    DB Size = 1.1TB

    Backup Duration = 2Hrs 26Min

    We use EMC's Avamar backup client

  • November 4, 2016 at 7:52 am

    #1910594

    2.3 TB in an hour and a couple of minutes. On a good night, it takes about 56 minutes. And that's to NAS storage, which is a huge "safety of the data" bonus.

    We have a 10GB pipe between the server and the NAS storage. I don't know the configuration of that storage since I'm very fortunate to have an infrastructure team that does an incredible job. Of course, we're also using compression so the throughput isn't actually 2.3GB per hour but the net result is a 2.3TB box does its full backups in about an hour. That also says a bit about the machines they built for the company.

    we're also doing offsite DR to another box using "Nimble". Again, I don't personally know much about the configuration but it, too, does and awesome job and, even with the huge overnight imports and updates we do, it keeps up very nicely.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 4, 2016 at 7:57 am

    #1910596

    This is not a good example for people to use to estimate their performance, but is a good thing to read to understand how badly things can go.

    We have a database from Intersystems, they provide a product named Cache. According to them it is not an RDBMS. It does store data in multiple files, and our system uses multiple mount points on an RHEL 7 system. We recently restored production to a test environment. The servers are almost identical, the differences do not affect performance in any measurable way.

    Restoring 700GB took 27 hours!

    Yes, we know there are issues. We are working on that.

    This restore was done from a Commvault tape backup system that utilizes disks to increase backup and restore speed. The disks are later written to tape. It is a well designed system. I don't want to get into what caused the delay as it would sound too much like pointing fingers, but what is important is that even though we have a fine network and backup system and the servers are outstanding, we are not able to restore in a timely manner.

    How did we find out? We tested.

    Dave

  • November 4, 2016 at 8:42 am

    #1910612

    826 GB

    Average backup time 14 Min 7 Seconds

    Compression is turned on

  • November 4, 2016 at 8:55 am

    #1910619

    this is averages over a few months:

    average database size was 186 GB, on Compellent SAN

    average backup time (using TSQL commands, with compression) to external Drobo disk array was 20 minutes

    average backup time (using CommVault) to tape was 79 minutes

    so extrapolating for 1 TB would be:

    115 minutes to Drobo disk

    433 minutes to CommVault tape (ouch)

    glad we're discontinuing CommVault backup of databases directly to tape!

  • November 4, 2016 at 9:46 am

    #1910642

    How about the restore speed?

    LinkedIn Profile

  • November 4, 2016 at 12:05 pm

    #1910691

    Nightly restore of 2 main production databases:

    DB1: 515GB, 13.35% free space, 10.0GB Log file

    DB2: 76GB, 36.69% free space, 33.5GB Log file (yeah... I know)

    Total time to restore including time to build the log file... 17:34 to 18:56 (mm:ss)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 7, 2016 at 2:04 am

    #1910853

    Backup times were before vs after optimisation for one of my databases.

    Native sql default options

    BACKUP DATABASE successfully processed 4098542 pages in 3049.584 seconds (10.499 MB/sec)

    Native sql Buffercount=500, maxtransfer = 1MB

    BACKUP DATABASE successfully processed 4098705 pages in 110.046 seconds (290.979 MB/sec)

    Redgate - SQLBackup

    BACKUP DATABASE successfully processed 4098225 pages in 513.281 seconds (62.377 MB/sec).

    SQLBackup did not change times when I passed in different buffercount and maxtransfersize.

    Native sql

    Buffercount = 8

    RESTORE DATABASE successfully processed 4780071 pages in 140.001 seconds (266.743 MB/sec).

    Buffercount = 16

    RESTORE DATABASE successfully processed 4780071 pages in 137.478 seconds (271.638 MB/sec).

    Buffercount = 32

    RESTORE DATABASE successfully processed 4780071 pages in 137.080 seconds (272.427 MB/sec).

    Buffercount = 64

    RESTORE DATABASE successfully processed 4780071 pages in 145.945 seconds (255.879 MB/sec).

    Buffercount = 512

    RESTORE DATABASE successfully processed 4780071 pages in 147.236 seconds (253.635 MB/sec).

    Page count is different as I did the backups optimisation first then did the restore optimisation weeks later, which had very little to do witht he buffercount and a lot to do with rightsizing an unnecessarily bloated log file.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 7, 2016 at 7:05 am

    #1910913

    We recently had to perform some backup and restore tests in order to estimate the impact of compressed vs non compressed backups and the effect they would have on our dedupe backup tape infrastructure. Times for the backups and restore are shown below. The backups are dumped to a san (XtremeIo) mounted local volume. The restores are performed across a wan link via unc path to a non-prod server. These are from a SQL2012 Enterprise Server running 16 cores.

    Type BackupSizeGB OnDiskSizeGB Stripes Duration MB/min SystemState BackupNumber

    Compressed 1,404 302 1 78 18,432 BatchRunning 1

    Compressed 1,390 294 1 103 13,818 BatchRunning 2

    Compressed 1,479 313 1 90 16,830 BatchRunning 3

    Compressed 1,434 303 1 88 16,687 BatchRunning 4

    UnCompressed 1,479 1,479 1 240 6,309 LowLoad 5

    Compressed 1,451 307 1 94 15,808 BatchRunning 6

    UnCompressed 1,489 1,489 1 84 18,151 LowLoad 7

    UnCompressed 1,532 1,532 1 87 18,032 MediumLoad 8

    UnCompressed 1,580 1,580 1 146 11,082 LowLoad 9

    UnCompressed 1,575 1,575 1 84 19,200 LowLoad 10

    UnCompressed 1,579 1,579 1 91 17,768 LowLoad 11

    UnCompressed 1,584 1,584 1 87 18,644 LowLoad 12

    UnCompressed 1,594 1,594 16 41 39,811 LowLoad 13

    UnCompressed 1,463 1,463 16 35 42,803 LowLoad 14

    UnCompressed 1,467 1,467 16 37 40,600 LowLoad 15

    UnCompressed 1,508 1,508 16 37 41,735 LowLoad 16

    UnCompressed 1,496 1,496 16 37 41,403 LowLoad 17

    UnCompressed 1,502 1,502 16 41 37,513 LowLoad 18

    Type BackupSizeGB OnDiskSizeGB Stripes Duration MB/min SystemState UsingBackupNumber

    Compressed 1,479 313 1 160 9,467 LowLoad 3

    Compressed 1,479 313 1 124 12,215 LowLoad 3

    Compressed 1,434 303 1 108 13,597 LowLoad 4

    Uncompressed 1,479 1,479 1 688 2,201 LowLoad 5

    Uncompressed 1,532 1,532 1 1,482 1,059 HighLoad 8

    Uncompressed 1,580 1,580 1 465 3,479 LowLoad 9

    Uncompressed 1,575 1,575 1 567 2,844 HighLoad 10

    Uncompressed 1,584 1,584 1 618 2,625 LowLoad 12

    Uncompressed 1,594 1,594 16 519 3,145 LowLoad 13

    Uncompressed 1,463 1,463 16 489 3,064 LowLoad 14

    Uncompressed 1,467 1,467 16 395 3,803 LowLoad 15

    UnCompressed 1,508 1,508 16 468 3,300 ModerateLoad 16

    UnCompressed 1,496 1,496 16 592 2,588 LowLoad 17

    Uncompressed 1,502 1,502 16 486 3,165 LowLoad 18

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply