March 26, 2010 at 7:05 pm
I recently changed jobs. During the interview, I was asked about my largest DB and how long it took to run a back. It was a 2TB DB and took about 45 minutes to run a full backup...One of the interviewer told be how long "it should run", he based that on backup stripes. It's my understanding of backups, that time cannot be just calculated, it depends on the RAID Level (RAID 5, v RAID 10), the number of disks in the array (5, 10 , 24, what ever), the speed of the disk (10k v 15 k), the SAN configuration (are we sharing those disks with someone else, or are they solely dedicated to SQL backups), the number of HBA cards, the time the backup is occuring (CPU utilization, the number of transactions that occur during the backup), 64 v 32 bit, CPU speed, memory, etc...The dude was somewhat adament that we were doing backups wrong, because he calculated it could be done in under 10 minutes. without ever seeing the server (96 core, 256 GB of RAM). It was not worth arguing in an interview...
The only way I have ever calculated backup times is looking at historical backup data for a given server and eyeballing what has happened in the past.
So I accepted the position. One of my first task was to re-write their backup SP to allow for multi-threded (multi stripes backups). Well the first thing I did was to create a job on a test serever to backup up a 50 GB test DB, a job that runs a backup of 1 file, 2 files, 4 files, 8 files...etc upt to 64 files. Then I expanded the SP, the first part of the SP back's up only to the dedicated back up drive, then I added to the SP a second part that runs the backup between both the log drive and the backup drive. In my tests (about 8,000 total backups), I found the difference between a the fastest backup (8 threads) and the slowest back (1 thread) to be 10 seconds (for a six minute backup, a difference that amounts to nothing in a production environment.
Here is what I found, and it makes total sense. No matter how many backup threads you are running, when backing up to a singe drive, the backup throughput is the same +/- 3% (and a very tight standard deviation on backup time). When backup up to two drives, backups are significantly quicker (even if the second drive is the C drive). Being the new guy, I went ahead and wrote an SP that allows up to 128 (in 2^n increments) backup threads
So here is my question, has anyone every found a significant performance increase in multi-threaded backups to the same drive (after all, IO is the bottleneck). I really feel that I wasted a week in writing this (albeit with the SP I wrote can handle multiple drives, which can be specified, and using two or more physical drives can certainly improves performance...assuming your SAN group will give you several drives). Further, I contend that if the DB is in a single file on a single drive, backup speeds are then dictated by IO on the drive that MDF resides on (If you have several NDF's on different drives, it would improve performance).
I just don't see any advantage to multi-striped backups to a single drive, the bottleneck is not SQL, but the backup drive.
Thoughts???
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
March 27, 2010 at 12:39 pm
Definitely multiple devices will increase the threads and will do the parallelisim while backing up and restore even if it is on single drive. But if there is bottleneck with the drive (Means reaching to its maximum throughput) then not sure how much it will help to improve the performance.
[Copying the data and transaction log from the backup devices to the database and transaction log files is performed by reader/writer threads; one thread is assigned to each backup device. Performance is limited by either the ability of the backup devices to deliver the data or the ability of the database and transaction log files to accept the data. Therefore, performance increases with the number of backup devices, until the maximum throughput of the database or transaction log files to accept the data is reached. ]
Please read this article (If not read): may give more clues for optimization
Optimizing Backup and Restore Performance in SQL Server
http://msdn.microsoft.com/en-us/library/ms190954.aspx
Further you can explore -Data Transfer Options (BUFFERCOUNT / MAXTRANSFERSIZE ) in backup command.
March 28, 2010 at 3:56 am
GregoryF (3/26/2010)
It was a 2TB DB and took about 45 minutes to run a full backup
2TB of data? That would almost be a record - see http://www.sqlskills.com/BLOGS/PAUL/post/High-end-backup-compression-numbers.aspx
So here is my question, has anyone every found a significant performance increase in multi-threaded backups to the same drive
In general, no - it's a myth. SQL Server issues asynchronous I/O for both reads and writes. It's an awful lot faster to issue async I/O requests than it is to complete them, so one thread is typically sufficient to extract maximum I/O performance.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply