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.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