Solved

SQL backup speed  - tape vs disk

Posted on 2011-02-17
31
869 Views
Last Modified: 2012-08-13
should backup to disk always be faster than to tape- or at time, could tape be faster, if the disk is not high config? in a db environment.

thanks
0
Comment
Question by:anushahanna
  • 13
  • 6
  • 4
  • +5
31 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 28 total points
ID: 34923829
hi
as a general rule the HDD will perform faster than the tape, and you should use it at the top of your backup pyramid
0
 
LVL 9

Assisted Solution

by:Tomas Valenta
Tomas Valenta earned 56 total points
ID: 34924037
It depend on your HW environment. Normally is not recommended backup to local disk drive only.
You can backup to Tape drive locally (LTO3 drive - backup speed approx. 1 100MB/s) and the second
backup should be done to USB external HDD (backup speed of SQL db is approx 850MB/s).
Another point of your backup should be disk array connected directly to server (Fiber, SAS). This
is faster then external USB drive but cannot be moved outside if you would like to have a copy of your data
outside building. The last is backup to NAS (Network attached storage) - here speed may vary based on network connection
- often is used separate network segment for improving backup speed. The backup speeds I mentioned were from our real system.
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 166 total points
ID: 34924604
You're not likely to find a disk that you can write to at 140MB/s native - 280MB/s compressed native speed, which is what you can get with an LTO5. The things are so fast you need to have an array of disks just to keep up with them.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 28 total points
ID: 34924661
also it depends where is your tape device located (network or attached..)and  you are running backup (local or network HDD) , databases- backup size, etc... what software and from where you are using to backup...

---
to make sure - you need to try to backup and see what is faster: it is up to your environment setupt,  configuration, business, etc
0
 
LVL 20

Assisted Solution

by:SelfGovern
SelfGovern earned 83 total points
ID: 34925431
It's a common misconception that "Tape is slow".   As Andy points out, for at least several generations of LTO, it's been the source disk that has trouble keeping up with tape!   I don't know where Tominov gets his numbers from -- they don't match to any reality I've ever seen -- but the real performance numbers for tape are:
Drive type             Native Speed
LTO-3                   80 MB/second
LTO-4                  120MB/second
LTO-5                  140MB/second

In the real world, it takes a fast array of disks and a fast RAID controller to be able to get that much data to a tape drive over long periods... and if your data is compressible, multiply the numbers above by the compression you achieve to get the even higher performance tape will achieve (that is, if your data compresses at 2:1, LTO-4 will hit 240MB/sec.   At 1.5:1, it will hit 180MB/sec -- **if** your disks can get it the data that fast!).

That compression -- the ability to fit significantly more data on a tape than its native capacity, and with no performance overhead on the backup server -- is a real benefit.   Other benefits in LTO-4 and LTO-5 tape drives are hardware encryption, which means that your data is secure even if the tape is lost, and WORM capability, ensuring that data can't be changed or overwritten once it's on the tape.

Sure, you can do short bursts from cache from most hard drives and see better numbers.   You can run benchmarks that access the disk by sector and not by file and get great numbers -- but that's not how backups are commonly done, because then file or object restores take too long.

The reason that big companies are spending tens and hundreds of thousands of dollars on disk-to-disk appliances (aka virtual tape libraries or VTL) is not because a particular server gets backed up faster compared to tape -- it's typically because:
1) The server can't keep a single tape drive used to maximum capacity -- the server isn't fast enough
2) Many more backups can be run to a VTL simultaneously, meaning that the overall backup window shrinks.
3) Some causes of tape backup failure are eliminated, such as dirty tape heads, wrong media, etc.
4) Backup to disk allows for faster single-file restores
5) Backup to disk with deduplication allows for easy low-bandwidth replication of backup jobs to a remote site, such as a data center

As a side note -- I also get worried when people recommend you backup to a USB drive.   Not only will performance be less than you expect (the cheap consumer-quality drives in these enclosures just aren't up to the task), but if you're counting on that backup being available in a year or three, you may well be very disappointed; disks are not designed for archive.   They have to be powered on to keep the data refreshed.   Long periods of sitting unused on a shelf will lead to increasing failures over time.     Tape, on the other hand, is known to be a good archive media for twenty or thirty years.

Yes, tape is being used less for the day-to-day backups in many businesses.   But it's still the best for archive, and the price/performance beats disk by an order of magnitude.
0
 
LVL 9

Assisted Solution

by:Tomas Valenta
Tomas Valenta earned 56 total points
ID: 34925687
The data is from our BackupExec SW. Backup of MS SQL DB of 200GB size takes 140minutes. Backup was done to the local LTO3 drive.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34926874
Andy
>>You're not likely to find a disk that you can write to at 140MB/s native - 280MB/s compressed native speed, which is what you can get with an LTO5.

are you suggesting LTO5 over disk, and that is can handle upto 280 MB/sec?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34926878
SelfGovern
could you comment on how these folks hit a lot of MB/sec
http://tinyurl.com/348bmte

Am I understanding you right that LTO-5 or 4 can be a better option than to disk, but not LTO-3?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34926891
in the link search for '65'
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34926894
Tominov, 200 GB in 140 minutes is 24.38 MB/sec?
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 166 total points
ID: 34926897
Yup, LTO5 is faster than disk. 280MB/s is what you might get if the data is compressible but you'lll get at least 140MB/s as long as you can read from SQL that fast.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34926986
if i am getting 6MB/sec for min and 56 MB/sec for the tape backups right with SQL Server, does that indicate the backupserver had LTO3?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34926989
56 MB/sec max
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 166 total points
ID: 34927082
LTO3 native 80MB/s so the figure of 56MB is reasonable, the limit's most likely how fast you can red from your SQL server.

HP's specs - speeds are at the bottom...
http://h18000.www1.hp.com/products/quickspecs/13572_div/13572_div.html#Technical%20Specifications
0
 
LVL 42

Assisted Solution

by:kevinhsieh
kevinhsieh earned 28 total points
ID: 34927168
Disk can be faster than tape if you can't feed your tape drive fast enough. That sounds backwards, but tape can only write fast and faster. Disk can write as slow as you need, so it will go the speed that you require until it maxes out. With tape, if you don't feed it fast enough it has to start and stop while it waits for more data. It is the stopping and starting that kills the throughput. If your current environment can't keep LTO3 happy and full with data, LTO4 would probably perform worse because the speed mismatch between the backup source and the backup tape drive just got bigger.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 20

Assisted Solution

by:SelfGovern
SelfGovern earned 83 total points
ID: 34927865
Kevin, your information is out of date, at least for HP LTO drives.   I've seen data fed to them from 1MB/sec all the way up to full native speed, and the throughput was the same as the feed speed.   It used to be the case with DLT tapes that not feeding them fast enough actually had a performance penalty.

Now, there is an issue if you feed a drive so slowly that you get significant buffer underrun incidents.  In this case, though, the problem won't be speed, it will be early tape drive wear (from all the stop/rewind/restart).

To the OP: Something you have to remember is that each backup system has only one bottleneck.  This is the thing that slows you down.   If the bottleneck is "A", then getting a faster "B" won't change the throughput, because A is the bottleneck, and the bottleneck is what limits your performance.  If you fix A, then B or C or D might become the new bottleneck... but until you fix A, fixing the others gains you no performance benefit.

That presentation you linked to explains common performance problems and tells you what to do about them.    It's got very good information, so study it and see what you can learn.  I can't put everything I know here, because it's taken me 11 years in data protection to know what I know.  That said, the linked-to presentation does tell you a lot, and I've summarized some of the common fixes below.

If you have LTO-3 today and are seeing 56MB/second, then the tape drive is not your bottleneck, because that drive can go at least 80MB/sec.   Unless Windows Perfmon shows you that you're memory or processor constrained, your current disk is probably your bottleneck.  To fix it, you can get faster disks (15K vs, 10K), better RAID striping, more disks in your RAID system, better RAID controller, split your data over multiple RAID system (or use something like an HP EVA that does broad striping), perhaps a newer version of the database program, reduce contention when the backup is running, or tune the backup process in your backup/DB system -- among others.    I can't tell you from here which is the best for you; people who analyze these things need access to your system, and probably charge a pretty penny -- but you've got some clues to help get you started thinking in the right direction.    
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 166 total points
ID: 34929385
The data rate matching values are in the same place as the max speed in the queckspecs I posted earlier.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 111 total points
ID: 34933710
Latest LTO readers are very fast but all has to do with the restore target time you want as part of your recovery plan.  

Tape media adds an additional step to transfer first from tape to disk to allow final restore, while disk backup is already there to be restored.  Depending on database size the total restore time may be very long when having to do both.  Personally, I keep one week disk retention on disk and 8 weeks on tape.  

There are great benefits from that first being that I don't need to call the tape team everytime I need to restore, copy or move a database.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 111 total points
ID: 34933727
In other words, I believe that the speed of media is not be dictating your recovery plan.  If you have no more than 1 hour to bring back a big database online, I guarantee you that you will have better chances on meeting that time constraint by restoring data that is already on disk at time of crash.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34936047
SelfGovern, thanks for the recommendation to check the disk config.

In the tinyurl, on page 17, the achieved 650 + MB/Sec.. how is this possible (regardless if disk or tape type 5)?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34936054
andy, is "data rate matching" like the min and max (like mine was 6-56)
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34936057
Racimo, SQL Legato Network is able to restore directly from Tape to SQL, as we have it now; is that impressive?
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 166 total points
ID: 34936241
Data rate matching for LTO is a form of maximum / minimum, the drive motor speeds up and slows down in an attempt to keep the tape streaming and avoid the stop/start "shoe-shining" that Kevin refers to above.

The article you posted in tunyurl is all about SQL and says nothing about what they were backing up to, they may well have been backing up to a null device just to prove how fast the backup could be done. Quite possibly they had an array with a lot of hard disks in it. A tape is faster than a disk but it's not faster than an array of 20 disks.
0
 
LVL 20

Assisted Solution

by:SelfGovern
SelfGovern earned 83 total points
ID: 34938981
> In the tinyurl, on page 17, the achieved 650 + MB/Sec.. how is this possible
>  (regardless if disk or tape type 5)?

Andy's got it right, above.   The target would have either had to be a null device (i.e., not actually written anywhere, in order to benchmark the pure backup limit), or, a big VTL like an HP VLS9000 with multiple simultaneous targets, or, several LTO tape drives, each doing an independent piece of the backup.

The adaptive write speed, or data rate matching, of LTO is the minimum and maximum incoming data rates that the tape drive motor can handle without stopping and starting.  With LTO drives, it's typically 1/3 of the full write speed*.   So with uncompressible data (or compression turned off) a 120MB/sec LTO-4 drive will be able to handle incoming streams as slow as 40MB/second without emptying the buffer and having to stop, rewind, start writing again.     If your data is 1.5:1 compressible, that number would be 60MB/second.    Earlier tape technology (DLT, DAT) did not have this feature; if you couldn't feed them at their rated speed, they for sure would empty the buffer, and that's not a good thing.

* Some algorithms are better than others.   HP's is constantly variable for best real-world use; IBM's at least used to be steps, and only adjust at end-of-track, which meant you could have some really bad performance for some data streams.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 111 total points
ID: 34939028
<<Racimo, SQL Legato Network is able to restore directly from Tape to SQL, as we have it now; is that impressive?>>
I am sorry but you are completely missing the point of my comments.  Requirements such as target restore time should determine what tools, approach is to be used to meet those requirementd, not the way around.   Recovery plan involve people, calendars, human error, processes, not just tapes and sofware.

Until you determine the best mix of each, beginning your thought as a contest between tape vs disk medias, or as a contest to what iis the best technology, simply has little value in determining any integrated recovery plan solution.

IMHO - Hope this helps...
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35060273
very much appreciate your input. i wish MS mentioned what target they used.

with the above logic in mind, with tape, I get max of 50-60 MB/sec in tape, but with Disk (SAN Lun), 225 MB/sec.

0
 
LVL 6

Author Comment

by:anushahanna
ID: 35060295
Racimo, after you set your target restore time, depending on what technology is faster/better, you will adapt to the better technology in that environment- is that what you are saying?
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 166 total points
ID: 35061090
Your SAN based LUN isn't one disk, it's an array of them. That's why it's faster than your tape.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 111 total points
ID: 35061655
<<is that what you are saying?>>
What I am saying is meeting the target restore time the end while the combination of hardware, software technology and process to be used.remains a mean.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35064361
>>Your SAN based LUN isn't one disk, it's an array of them. That's why it's faster than your tape.

Is it because there are many spindles to do parallel writes? in that case, if, theoretically, if unlimited number of disks should be used in a LUN, we could have GB/s worth of throughput on disk possible? (like in the example from the tinyurl, 650+ MB/sec was possible; is there a limitation that you could'nt go beyond 650 MB/sec - what is the highest throughput you have seen with (array of) disk?)
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35064367
Racimo, thanks- I see your thoughts more clearly now..

have you seen any case study paper written on the concept of starting with target restore time, so i can follow it with a real example of how it was deduced (the means)

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now