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.

Improve this question
3
  • 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, 2012 at 2:05
  • 1
    Tools: Windows Server Backup. The built-in one. Disk Queue: I mean Disk Queue Length as seen in perfmon.exe
    – HelloSam
    Jul 7, 2016 at 3:33
  • Tools: Windows Server Backup. The built-in one.
    – HelloSam
    Jul 7, 2016 at 3:34

2 Answers 2

Reset to default
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.

Improve this answer
5
  • 3
    +1 for Windows Internals reference. Probably my second favorite topic.
    – Ryan Ries
    Dec 13, 2012 at 1: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, 2012 at 3: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, 2012 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
    – Wesley
    Dec 13, 2012 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, 2012 at 6: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

Improve this answer
3
  • 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, 2012 at 5: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 Database Administrators for some real-world Postgres backup advice :-)
    – voretaq7
    Dec 14, 2012 at 3: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, 2012 at 6:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .