Backup speed depending on whether file already exists

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup speed depending on whether file...

Backup speed depending on whether file already exists

Rate Topic

Display Mode

Topic Options

Author

Message

tim.nyland-jones

Posted Wednesday, March 13, 2013 3:28 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 17, Visits: 241

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?

Post #1430655

tim.nyland-jones

Posted Friday, March 15, 2013 4:04 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 17, Visits: 241

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)

Post #1431824

Lynn Pettis

Posted Friday, March 15, 2013 4:24 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 21,627, Visits: 27,482

tim.nyland-jones (3/15/2013)

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)

Post #1431833

tim.nyland-jones

Posted Friday, March 15, 2013 5:06 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 17, Visits: 241

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?

Post #1431841

Lynn Pettis

Posted Friday, March 15, 2013 5:19 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 21,627, Visits: 27,482

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

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

Post #1431846

« Prev Topic | Next Topic »

Permissions