Highlighted

PostgreSQL Backup Performance Tuning

Hello,

I've searched for methods of tuning PostgreSQL backups for speed and space and couldn't find anything recent. I have also put in a request to Veritas support and haven't received a response for two weeks, other than they are researching. With the following setup, is there a way to find a faster method and optimize snapshot space to backing up PostgreSQL?

  • Third-party master and stand-alone media, running NBU 8.2 with Linux
  •  500GB snapshot space and running in no hangup mode (only way jobs succeed)

They currently have 2.3Tb of data that is expected to grow and they're quickly running out of space. It takes 22-26 hours to complete backups weekly. We've played with snapshot space and 500GB enabled backups to succeed while still having a cushion for growth.

I appreciate any suggestion/experience you may have.

 

Tags (2)
14 Replies

Re: PostgreSQL Backup Performance Tuning

2.3TB
2,355.2GB
2,411,724.8MB
26.0hours
93,600.0seconds
25.8MB/s

.

I agree, 25 MB/s is not very fast.  I think you first need to prove :

1) source data volume max read throughput, e.g. dd to nul

2) source data volume max plain file read by NetBackup, e.g. bpbkar to nul

3) max of PostgreSQL own dump speed to a fast local spindle or fast RAID parity group or fast SAN storage

4) max network egress speed, use nbperfchk source zero to network port listenner (also an nbperfchk listenning to port, output to dev nul)

5) an FTP/SCP max file transfer speed across network

...and when you have proven that all five of the above can max out your wire speed, then you might be able to start delving into NetBackup logs.

.

It might help if you explain the structure layout of the source data LUNs, and also the nature/configuration of the NetBackup target storage unit ?

Re: PostgreSQL Backup Performance Tuning

Thank you for your response. If you don't mind, could you clarify your request to commands and location to conducting them? ie. bpkar null test on client

Tags (1)

Re: PostgreSQL Backup Performance Tuning

A little bit of googling gives:

1) source data volume max read throughput, e.g. dd to nul

https://fedoramagazine.org/check-storage-performance-with-dd/

2) source data volume max plain file read by NetBackup, e.g. bpbkar to nul

http://netbackupblog.blogspot.com/2010/01/how-to-benchmark-performance-of-bpbkar.html

3) max of PostgreSQL own dump speed to a fast local spindle or fast RAID parity group or fast SAN storage

(beyond me - maybe ask your DBA... what is the fastest backup he/she can achieve?)

4) max network egress speed, use nbperfchk source zero to network port listenner (also an nbperfchk listenning to port, output to dev nul)

4a) nbperfchk network speed

https://www.veritas.com/support/en_US/article.100038623

4b) nbperfchk disk speed

https://www.veritas.com/content/support/en_US/doc/89578623-126555802-0/v89990565-126555802

5) an FTP/SCP max file transfer speed across network

https://notesbytom.wordpress.com/2013/07/26/network-speed-test-with-scp/

.

Wondering if you might be able to explain the structure layout of the source data LUNs, and also the nature/configuration of the NetBackup target storage unit ?

Re: PostgreSQL Backup Performance Tuning

sdo,

Thank you for your reply, unfortunately the customer did not perform the steps needed to get those answers. Instead, they rebuilt their old 5230 and changed out the 1Gb pipe to a 10Gb pipe. Now, their Postgresql jobs are completing in 7 hours. However, they are requesting if they can configure multi-data streams on their Postgresql.

Tags (1)

Re: PostgreSQL Backup Performance Tuning

I've looked through the NetBackup for PostgreSQL Admin Guide and can find no mention of multi-streaming.

However, I found this which discusses a multi-policy approach of one set of policies per database, where it says:

"For each database, your system should have a separate policy and script file for each backup type. For one database, you can have three policy and script files, representing the full, differential, and cumulative backup types. If you had three databases, you would have nine policy and script files, plus possibly one for the -globals option you specified."

...see:

https://www.ibm.com/support/knowledgecenter/SS5FPD_1.0.0/com.ibm.ips.doc/postgresql/admin/c_sysadm_n...

 

Re: PostgreSQL Backup Performance Tuning

You're AWESOME! I'll read up on that article. Thank you!

Tags (1)

Re: PostgreSQL Backup Performance Tuning

I found this article, as well, which reiterates the procedure you mentioned, but it confirms that PostgreSQL can be a multi-stream operation: Smiley Happy

https://www.ibm.com/support/knowledgecenter/SS5FPD_1.0.0/com.ibm.ips.doc/postgresql/admin/c_sysadm_m...

Re: PostgreSQL Backup Performance Tuning

There may well be a difference between client side streams in PostgreSQL own "nzbackup" (e.g. Oracle RMAN channels) versus integration / interop with any third-party backup product streams (e.g. NetBackup client and policy multi-streaming).

The point that is not clear to me is whether PostgreSQL own "nzbackup" multi-streaming has an integration / process / method of working with NetBackup Client's "config" files for backup of PostgreSQL.  What I mean is, in my ignorance, is that NetBackup's (currently (as of v8.2) not so policy based) config of backups of PostgreSQL appears (at this NetBackup v8.2 stage of product interop development) to be driven more-so through a defined set of "config" files and less-so in the traditional NetBackup policies... and so I myself would be mindful of not expecting too much at this stage.  Again what I mean is, that it is not clear to me whether PostgreSQL "nzbackup" streams are synonomous with NetBackup multi-streaming (in perhaps the way that Oracle RMAN channels kind of are).

Re: PostgreSQL Backup Performance Tuning

I truly appreciate your research. I've checked it out and suggested it to a Veritas Engineer to try and work it out, but he and his backline engineers said it's not supported and if I recommend it to the customer, they will no longer be supported. :/ I was hoping to at least troubleshoot it in a lab or something. Alas, I just got shot down. 

Tags (1)

Re: PostgreSQL Backup Performance Tuning

Ok - so PostgreSQL own database side multi-streaming might not be synonomous with NetBackup multi-streaming, but I'm pretty sure one of the links I sent discusses a multi-database approach.  Might that help?

Re: PostgreSQL Backup Performance Tuning

sdo,

Thank you. It helps to know, but it doesn't change the solution for the customer, unfortunately. If Veritas won't support it, that's the end of the road for them because anything can go wrong from this point forward, if we implement it. I really do appreciate the time you placed into researching this issue.

Tags (1)

Re: PostgreSQL Backup Performance Tuning

Unfortunately, current PostgreSQL Agent isn't perfect and I belive it will be improved soon. If you need multistreaming you have to use bpstart_notify then pg_start_backup then bpend_notify and after that archive generated WALs. 

Re: PostgreSQL Backup Performance Tuning

I appreciate your response Mike; however, sdo is correct. Even if we set up multi-streaming, it will not work harmoniously with PostgreSQL's nzbackup and Netbackup's multi-streaming config files. I'm bringing it up with Veritas' Engineering team next week, they don't support it right now, but maybe it can be in the works. 

Re: PostgreSQL Backup Performance Tuning

I'm sorry, I didn't specify that you need to use "Standard" policy type with bpstart_notify/bpend_notify. It works this way, I tried.