12-06-2019 03:47 PM
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?
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.
12-09-2019 04:14 AM
2.3 | TB |
2,355.2 | GB |
2,411,724.8 | MB |
26.0 | hours |
93,600.0 | seconds |
25.8 | MB/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 ?
12-09-2019 11:04 AM
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
12-09-2019 11:20 AM
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 ?
12-17-2019 08:52 AM
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.
12-17-2019 09:18 AM - edited 12-17-2019 09:18 AM
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:
12-17-2019 10:41 AM
You're AWESOME! I'll read up on that article. Thank you!
12-17-2019 11:22 AM
I found this article, as well, which reiterates the procedure you mentioned, but it confirms that PostgreSQL can be a multi-stream operation:
12-17-2019 11:40 AM - edited 12-17-2019 11:53 AM
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).
12-23-2019 12:49 PM
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.
12-23-2019 01:13 PM
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?
12-27-2019 12:30 PM
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.
12-30-2019 02:25 AM
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.
12-31-2019 07:59 AM
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.
01-06-2020 03:05 PM
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.