5

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.

HelloSam
  • 171
  • 1
  • 6
  • 1
    The question is incomplete. Please finish the sentence. You might also want to describe what backup tool you use, which disk queue you are talking about. – eckes Dec 13 '12 at 02:05
  • 1
    Tools: Windows Server Backup. The built-in one. Disk Queue: I mean Disk Queue Length as seen in perfmon.exe – HelloSam Jul 07 '16 at 03:33
  • Tools: Windows Server Backup. The built-in one. – HelloSam Jul 07 '16 at 03:34

2 Answers2

8

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.

Wesley
  • 32,690
  • 9
  • 82
  • 117
  • 3
    +1 for Windows Internals reference. Probably my second favorite topic. – Ryan Ries Dec 13 '12 at 01:39
  • These are great points, and definitely address the question that was asked, but I'm worried he's backing his stuff up the wrong way (unless he's left out some steps that he's already doing.) – mfinni Dec 13 '12 at 03:43
  • I will give a shot in the next backup schedule. If that works - the next question logically follow would be how could I start backup in Idle everytime. The link you posted which has the software, could be the answer... And now I miss the "nice" command in Linux. – HelloSam Dec 13 '12 at 10:51
  • 2
    @HelloSam Use the `start` command in a script with the `/low` switch to start an application in Idle mode. More information on `start` can be found [here](http://www.computerhope.com/starthlp.htm#03) – Wesley Dec 13 '12 at 17:55
  • 1
    These are nice suggestions, but there were a few problems. 1. start /low doesn't work, apparently the command line just RPC the backup service to start a backup, but does not do the hardworking job itself. 2. Manually setting the priority to Idle in the task manager doesn't change the IO priority from normal to anything. (From the resource manager it shows the PID that do most disk I/O, I change the priority of that process). – HelloSam Dec 14 '12 at 06:17
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

mfinni
  • 36,144
  • 4
  • 53
  • 86
  • pg_dumps dump all. Windows Server Backup could use shadow copy to do a incremental dump (albeit the pages will be touched with write/locking read anyway...) Why they would be not usable? Once WAL is flushed to disk, PG could recover it up to exactly that point. In short, everything committed should be committed. At least, that's how I understand. It does not have VSS writer - but backup/shadow copy shouldn't suddenly make fsync unreliable, right? – HelloSam Dec 13 '12 at 05:34
  • @HelloSam If you're doing filesystem-level backups, please (***PLEASE*** for the sake of your data!) read the links in the answer above. Postgres filesystem backups have to be done a ***VERY*** particular way, or you've got a substantial chance of having a non-usable backup when you go to restore. If you're using `pg_dump`, Don't :-) It does work (very well for small databases), but it's a performance killer all its own when you start talking about bigger databases. You can ask a separate question here or on [dba.se] for some real-world Postgres backup advice :-) – voretaq7 Dec 14 '12 at 03:08
  • I understand that filesystem-level backups like tar/robocopy against a live partition won't work. But with Windows Backup a snapshot (Shadow Copy) is taken first - copy happens on that read only snapshot, not the live-data-changing partition. Totally agree it's a performance killer. Thank you for your suggestion - maybe I should try on PG mailing lis, too. – HelloSam Dec 14 '12 at 06:15