Netezza: nzbackup and concurrent database use

PROBLEM: 

ETL process takes too long when running concurrently with nzbackup.   Why ?

SOLUTION:

Refrain from DDL statements below in ETL design to eliminate nzbackup dependency : 

• TRUNCATE TABLE
• DROP TABLE 
• ALTER TABLE
• GROOM

Above DDL statements will be on hold as "waiting" until nzbackup completes.  Eventually, these statements will succeed, but with delay, upon backup completion. Note, GROOM TABLE VERSIONS will succeed too, however, it will fail nzbackup.

No issues running these statements concurrently with nzbackup : 

• CREATE/DROP TEMP TABLE
• CREATE/DROP any object except table 
• INSERT/UPDATE/DELETE on TABLE
• GENERATE STATISTICS

EXPLANATION: 

Netezza NPS 7.2 documentation about concurrent user during backup operation: 

While a backup is running on a database, users cannot drop or truncate a table in that database until the backup completes. The DROP TABLE and TRUNCATE TABLE commands wait (and appear to hang) until the backup finishes. Operations such as an insert or load, or creating or dropping other object types such as view, run without waiting while the backup is in progress. If a GROOM TABLE VERSIONS command runs on a table that is being backed up, the backup process exits with an error and you must restart the backup process.

Refer  The nzbackup command

See related post: Netezza: nzbackup & nzrestore on NPS 7.2.

Related documentation:  Database backup and restore

Netezza: nzbackup & nzrestore on NPS 7.2

See related post: Netezza: nzbackup & nzrestore on NPS 7.0.

QUESTION: 

What parallelism factor to pick running nzbackup with -streams option for database backup?

What parallelism factor to pick running  nzrestore with -streams option for database restore?

ANSWER:

Pick -streams AUTO  for both nzbackup and nzrestore .   

Advise: I would recommended to test running multiple nzbackup -streams AUTO concurrently in shell script. This may shrink overall backup time by saturating all available network bandwidth. This option yet to be tested with external backup storage.  Stay tuned ... 

---

EXPLANATION: 

This empirical test was inspired by poorly performing nzrestore from milti-stream backupset at NPS 7.0 and later releases.  At October 2013 it was proven that  nzrestore recovery time degrades by factor of 2 with each uptick of streams in source nzbackup dataset. For more details about this experiments see post Netezza: nzbackup & nzrestore on NPS 7.0.

Starting from Netezza NPS 7.2 database restore can be done in multi-stream mode. IBM did a great job improving nzbackup and nzrestore utilities.  This test proves that new milti-stream operations show highly improved performance. 

TEST CASES:

The matrix below explains various combination running  nzbackup followed by nzrestore with -streams option. The test was conducted using host server disk for target work space. The database used for testing has size of 500GB with three dozen of tables consisting of mix datatypes columns. 

RESULT: 

The greener, the better.  The nzbackup -streams AUTO exhibit the best backup performance: "23 min".  The recovery performance running nzrestore -streams AUTO is very close to backup speed: "33 min".  To validate the data test AUTO vs. AUTO was repeated 8 times showing very close results. The average number was derived for both backup and restore: 23 min / 33 min.  

Related documentation:  Database backup and restore

Netezza: nzbackup & nzrestore on NPS 7.0

See related post: Netezza: nzbackup & nzrestore on NPS 7.2.

QUESTION: 

What parallelism factor to pick running nzbackup with -streams option at NPS 7.0  (late 2013)?

ANSWER:

Do NOT use -streams option. Always run single stream nzbackup.   Build for parallelism manually using shell script executing single-stream nzbackup jobs concurrently.  This will create a single-stream backupset, which performs fast when recover data with nzrestore. 

Advise: upgrade to NPS 7.2 or higher,where both nzbackup and nzrestore have  option that perform well. See Netezza: nzbackup & nzrestore on NPS 7.2 post. 

---

EXPLANATION: 

As of late 2013, Netezza NPS version 7.0 has parallel nzbackup option -stream, however nzrestore can only be made in a single stream.  Empirical test shows that recovery of data using nzrestore linearly degrades with increase of parallelism in source backupset.

TEST CASES: 

To obtain the data for this analysis we picked the same size of data. The nzbackup /nzrestore were tested on local backup at netezza host disk and NetBackup Data Domain disk storage.  Both disk and network storage showed relatively similar speed performance. 

RESULT: 

For nzbackup -streams 4  is the "sweet spot" to make create backupset fastest. Seems like a catch, but look further down for nzrestore timing. 

For nzrestore, on the other hand, is the fastest when data recovery made from a single-stremed backup set.  With increase of parallel -streams in original backupset nzrestore degrades linearly by factor of 2 with each step up in prallelism. Note, nzrestore with 4 streams failed in this test.

NZBACKUP vs. NZRESTORE:

On the same time scale (Axis Y) compare: nzbackup and nzrestor.

Related documentation:  The nzbackup command  and  The nzrestore command