SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup speed depending on whether file already exists


Backup speed depending on whether file already exists

Author
Message
tim.nyland-jones
tim.nyland-jones
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 316
Hi, I'm observing strange backup behaviour on my system. We have a large (400GB) database and one full backup job for each day of the week. Each job is set to overwrite, so while the filenames remain the same, the contents are updated. At any point in time therefore we have 7 full backups available.

Now, we recently purchased HyperBac (due to backup space and time pressure) and initially the new compressed backups didn't result in smaller backup files. I realised that retaining a constant filename and overwriting the contents of the backup file meant that the file wouldn't shrink to the new backup size.

So I deleted one of the files (Monday's) and waited for the new compressed, faster backup to run. And here's the odd thing. The new backup was compressed - to about 20% of the data file size (80GB). Great. Problem was, the reduction in I/O wasn't reflected in the backup time. In fact, the backup time was longer than when the file was 400GB - twice as long in fact!

So now I have a situation where backups Thursday-Saturday fill a small part of an existing 400GB file and take an hour and a quarter, while the backups Monday-Wednesday extend an existing 80GB file slightly and take two and a half hours.

Our storage system is an EMC SAN with 30 physical drives incl 9 SSDs as a storage pool and cache, so we should be getting better performance than that anyway, but that's another question. Every time I've used HyperBac in the past it's given me smaller, faster backups, but not this time. (This is not a HyperBac-specific question though, running the backups without compression gives the same timings - 2.5h if it has to create or grow the backup file, 1.25h if it can fit the backup into an existing file.)

The processes running on the server are pretty much the same each night, so there's nothing else taking up I/O resources on some nights and not others.

I've looked at spreading the backup across multiple files, changing numbers of buffers, block sizes, etc, and I may shave a couple of minutes off but nothing close to an hour and a quarter!

So, it appears to me that, where there's an existing file to backup into, and that file has sufficient space to hold the backup without having to grow, then the backup will run much faster. I've not been able to find anything on the web, official or otherwise, to prove or disprove that SQL Server and the backup subsystem behaves in this way.

Anyone seen anything like this?
tim.nyland-jones
tim.nyland-jones
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 316
Well, interesting findings over the past few days... it's not whether the file exists, it's all down to HyperBac compression...

HyperBac off: Backups 400GB, 1.25 hours, read speed 100MBps, write speed 100MBps. (roughly)
Hyperbac on: Backups 80GB, 2.5 hours, read speed 50MBps, write speed 8MBps (again, roughly).

This is not the performance I've come to expect from HyperBac, anyone seen anything similar?

(As it's now a product-specific question, I'll also post on the RedGate forums, but very interested to hear any similar experiences here)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91829 Visits: 38954
tim.nyland-jones (3/15/2013)
Well, interesting findings over the past few days... it's not whether the file exists, it's all down to HyperBac compression...

HyperBac off: Backups 400GB, 1.25 hours, read speed 100MBps, write speed 100MBps. (roughly)
Hyperbac on: Backups 80GB, 2.5 hours, read speed 50MBps, write speed 8MBps (again, roughly).

This is not the performance I've come to expect from HyperBac, anyone seen anything similar?

(As it's now a product-specific question, I'll also post on the RedGate forums, but very interested to hear any similar experiences here)


Used Hyperbac at a previous employer, the compressed backups ran in half the time of the uncompressed backups.

Your times above, are they computed or did you get them from specific counters, just curious. At another previous employer they experienced a different problem with compressed backups. Sometimes they would report as failed even though the backup had actually completed. It turned out that part of the problem was that the estimated size of the backup file was much greater than the actual file size of the backup and the system was timing out while the OS reset the EOF for the file. The backups were being written to an EMC NAS.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
tim.nyland-jones
tim.nyland-jones
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 316
Thanks for the reply Lynn. I've used HyperBac at a previous employer myself and found it to both reduce backup size and backup time with no problems. What size backups were you generating to produce those errors?

My figures are rounded a little, but not significantly. Backup times are taken from the backup job run time and the read/write rates are taken from a perfmon I have running collecting (amongst others) disk reads/sec on the data volume and disk writes/sec on the backup volume.

I've found (via CrystalDiskMark) that the data volume is capable of sequential reads up to 190 MBps and the backup volume 115 MBps - so HyperBac compression should get me down to about 35 minutes before we go near tuning the SAN, surely?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91829 Visits: 38954
It was a while ago and I even posted the info here on SSC on another thread where an OP was reporting the same error we had experienced. I can't remember the details, but the server was estimating the compressed file at about 70% of the uncompressed file size, and the actual was much smaller.

I may still have the email with the info in it but may take some time to find if I didn't delete it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search