Reduce the I/O priority of Windows Backup (Windows Server 2008 R2)

I have a PostgreSQL running on Windows Server 2008 R2 x64 box. And I have scheduled a backup everyday from the RAID 1 DB disk to a dedicated standalone disk. They are SAS 15k on Dell PERC 6i. I am using the built-in Windows Server Backup for purpose.

The problem is, whenever the backup process is kicked in, the database performance is hogged. I would say almost a 10x of performance reduction.

From the resource monitor, the disk queue is in the double digit range when backing up, and less than 1 during the day. The disk activity is like ~30-50MB/s during backup, so I guess the hardware is acting normally, though wbengine.exe takes up most of the portions.

I think reduce the IO priority of the backup process would be an answer, but I couldn't find a way to. Tuning process CPU priority does not seems to help.

Solution 1:

I/O priority is affected by thread CPU priority in Windows. For deeper reference, look into Mark Russinovich's books on the Windows kernel. The short answer is that you have to change CPU priority of the calling process. You'll want your process priority to be either Below Normal or Idle to change the I/O to not negatively impact database usage.

In your case, you'll probably want to call a backup script with low priority that itself calls wbadmin or whatever your backup tool is.

Solution 2:

Is your version of PostGRES a VSS writer? (I'm fairly certain there is no such beast.) If not, then you're probably not taking usable, recoverable backups. Taking a file-based backup of the data and log files, without shutting off the database engine first, is doin' it wrong. And this could possibly be where your IO performance problems are coming from, too.

You should use a native PG tool to dump your DB(s) to disk, and then back up the dumped files.

http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows

http://www.postgresql.org/docs/current/static/backup-file.html