Redistribution methods

Deployment options: Netezza Performance Server for Cloud Pak for Data System

Choose between online and offline redistribution after ensuring sufficient disk space to support online redistribution. The conditions determining this choice are summarized in Table 1.

Table 1. Recommendation to choose the redistribution method
Condition	Recommendation
If there is sufficient disk space to redistribute tables online and rolling outages that disallow modification to the currently redistributing table are acceptable.	Online
If there is sufficient disk space to redistribute all tables online. If unable to modify a large or critical table while redistributing online. If second shorter full outage to redistribute the large or critical tables is acceptable.	Online-then-Offline
If there is insufficient disk space to redistribute all tables online. If second shorter full outage to redistribute the large or critical tables is acceptable.	Online-then-Offline
If there is insufficient disk space to redistribute all tables online. If second shorter full outage to redistribute the large or critical tables is not acceptable.	Offline
If you want to perform the redistribution quickly (during NPS software expansion) A single extended full outage (all tables completely inaccessible) is more acceptable than rolling outages per table.	Offline

Note: If there is insufficient disk space to redistribute at least one table, you can choose to redistribute maximum possible tables online. Redistribute the remaining tables offline.

Expansion and redistribution

You must complete the prepare-to-expand and post-expansion steps irrespective of the redistribution method.

Table 2. IBM's and user's responsibilities
Steps	Offline	Online
1	Both software expansion and offline data redistribution are performed under a single invocation of `nzredrexpand`.	Software expansion is performed using `nzredrexpand`.
2		Redistribute tables after expansion using `nz_redistribute`.

System type and size

Expansion of Netezza Performance Server on Cloud Pak for Data System 1.0.X is supported with Lenovo hardware. A Base+2 or larger system can expand by a multiple of two enclosures (8 Lenovo SPU nodes).

Table 3. NPS Version redistribution applicability
Netezza Performance Server version	Offline	Online
11.2.0.0 to 11.2.1.10	✓
11.2.1.11	✓	✓

SPU disk space requirements

Redistribution (both offline and online) processes and redistributes the rows in one table at a time. Additional disk storage is needed to accomplish this.

Table 4. SPU disk space requirements
Offline	Online
Requires a minimum of 240 free disk extents (3MB per extent) on each data slice, independent of the size of any table.	`nz_redistribute` needs free disk space to hold a second copy distributed over the new number of data slices of the table that it is currently redistributing. Therefore, the size of the largest table, which is taken into consideration by the `nz_redistribute -SpaceEstimate` command, will determine whether all tables can be redistributed online.

Caveats

This information is applicable only for Lenovo systems.

Expansion is supported only for addition of 8 or more nodes to provide necessary resiliency characteristics.
For Base + 1 to Base + 2 expansion, the system must be reinitialized. A full backup and restore is necessary.
Although expansion allows adding nodes, it does not change the mirroring topology of existing nodes.
New systems with 8 or more nodes have data slice mirroring across groups of eight nodes ("mirror domains").
A Base + 1 system cannot be expanded with the data redistribution method. A Base + 1 system must be expanded by backing up the existing data with nzbackup, reinitializing the 8-node system, then running nzrestore.

The reason for this restriction is that the expanded system would consist of eight nodes. The data slices would be mirrored among groups of four nodes (two mirror domains of size 4). Such a solution would not satisfy the requirement for 8-node mirror domains on Base + 2 and larger size systems.
The redistribution time estimate depends on sample data. The actual redistribution time is highly dependent on the structure of table and compression ratio. The actual redistribution time might differ from the estimation by up to a factor of two (it might take twice the time to as the displayed estimate).
The redistribution time estimate can be inaccurate for systems with large numbers of empty tables (counted in thousands). The time estimate is based on the total number of extents that are to be redistributed. The time estimate does not account for the time that is needed to update empty tables, which can contribute to much longer redistribution process if the count is high (in thousands).
This is corrected in versions 11.2.1.1 and later.
It is not possible to do an incremental backup, which is based on a full backup that was made before the redistribution process. You must take a full backup after the redistribution. Only then can you take incremental backups again.

Redistribution time

For either of the redistribution methods, the time that is needed to redistribute the data depends on the total amount of user table data.

Table 5. Redistribution time
Offline	Online
After software expansion, `nzredrexpand` provides an estimated time to redistribute the data.	`nz_redistribute` does not provide a time estimate. The time to redistribute the data depends on the workload that it shares with the system.

Performance and availability

Offline: Netezza Performance Server is unavailable for the customer workload during redistribution.

Online

Netezza Performance Server is available for the customer workload during online redistribution with some limitations on performance and availability.

nz_redistribute will request and acquire an Access Exclusive lock LOCK TABLE on the table currently being redistributed, that locks out all concurrent modifications to that table or waits for it during duration of redistribution of that table. SELECTs and sub-SELECTs parameters will be able to run concurrently on a redistributing table. If the bulk of the user’s data, and hence “online” redistribution time, is from one or a small number of very large tables, a table (one such table at a time) may be unavailable for update activity for a long period of time. The user might instead choose:
- Offline redistribution of all tables.
- Online redistribution of smaller tables, followed by offline redistribution of the very large tables. Note that, offline redistribution of a very large table may be the only option when there is not enough disk space to support online redistribution of that table. Also, choosing online-then-offline involves another round of stopping and restarting Netezza Performance Server.
- Online redistribution of the very large tables first, during a period when the system is relatively idle and workloads that need to update these tables can be disabled or deferred, followed by online redistribution of the remaining tables.
  - Choose to redistribute the most critical and high-traffic databases / schemas / tables first, as quickly as possible.
Until a given table has been redistributed, all of its rows reside on the original (pre-expansion) set of data slices only. New rows, from Inserts and Updates, will also be added to these data slices only and will not make use of the available space on the new data slices (that reside on the new SPUs). If a very large amount of data is added to one or more tables that have not yet been redistributed, it could prevent a large table (which may or may not be one of the tables modified) from successfully redistributing online.
Queries that join a table that has been redistributed with a table that has not yet been redistributed may take longer than they otherwise would have, in particular if they could have used a “co-located join” on the join key columns. Redistribution of all tables needs to complete in order to restore performance of such queries.
Online redistribution will use system resources, and may negatively impact the performance of the workload (compared with in the absence of redistribution). It is recommended to create a scheduler rule to control the system resources available to nz_redistribute, since it needs to run as Netezza user admin. You can also choose to run nz_redistribute during less heavily loaded time periods.
As expected, the workload may negatively impact the performance of nz_redistribute. Therefore, online redistribution of all tables will typically take longer than offline redistribution. (The more the restrictions on the resources available to nz_redistribute and on when it can be run, the longer the overall time needed to achieve redistribution of all tables.) Thus, there is a tradeoff here between one long complete “outage” (with offline redistribution) and a longer period of “rolling outages” of one table at a time being unavailable for update activity (with online redistribution).
An invocation of nz_redistribute will redistribute one table at a time. Multiple tables can be redistributed in parallel if desired, but there are additional implications described in Implications.
- Multiple parallel invocations of nz_redistribute do not coordinate with each other and parcel out the tables to redistribute. Rather, you will want to choose the command arguments carefully so that the tables they redistribute do not significantly overlap. Where there is overlap, a given table will be redistributed only by the first nz_redistribute process that gets to it and the other(s) will not redistribute it again (but will determine that redistribution is done and is no longer needed).
- Each parallel invocation will need free disk space to redistribute one table. If two invocations happen to be redistributing two very large tables at the same time, they may both fail to find enough disk space; even though they might have both succeeded if redistributed one after the other
- Parallel invocations of nz_redistribute will have more of an impact on the customer workload than a single one (redistributing one table or one table at a time). You may therefore want to limit this to times when the system is mostly idle or running a very limited subset of your workload.
- If performed when the system is idle, parallel invocations of nz_redistribute may finish redistributing all tables faster than offline redistribution. There will not be any additional speedup beyond 3-4 parallel invocations.

Backup and Restore

Restoring pre-expansion incremental backups fails from a backup set that was started prior to the expansion. This is because redistribution generates new unique rowid for the redistributed rows. Incremental deletes fail to find rows that match the original rowid. This is enforced by nzredrexpand (for offline redistribution) and nz_redistribute (for online redistribution) without redistributing rows in a locked database.
Note: Unlocking a locked and dropping the database is recommended in the Prepare for NPS expansion steps, which will disallow incremental restores.
Because of the same rowid mismatches between pre-expansion and post-expansion rows, a new post-expansion incremental backup in an existing pre-expansion backup set cannot use incremental deleted row files. Instead, if an incremental backup is requested and a table that has not yet been redistributed has had any rows deleted since the expansion, a full backup of that table will be taken instead. Hence, it is recommended to take a new full backup, which will start a new backup set, of each database after all tables in the database (for online redistribution) or in the system (for offline redistribution) have been redistributed.
Restoring a database from a pre-expansion backup set will work so long as it starts from the initial full backup. The restore can be expected to perform poorly because of the data slice count mismatch (between the pre-expansion and post-expansion numbers of data slices).

The following implications are specific to online redistribution only:

A backup taken while redistribution has not completed for all tables in a database will have some table backups based on the original data slice count and some based on the expanded data slice count; we will call such a backup a mixed backup. A restore of the first set of tables onto the same system or to another system with the same data slice count will perform poorly, as with any other case of restoring from a backup when there is a data slice count mismatch. nzbackup will output a warning message if it is backing up a database where not all tables have been redistributed. We discourage mixed backups and generally recommend you wait to backup a database until after all tables have been redistributed. But nzbackup will allow mixed backups to be taken, issuing a warning message.
The database {dbname} is currently undergoing redistribution of {number-of-tables} tables. Performance of restore from this backup may be impacted.
If the backups in the backup set are to be restored onto a different system (for example, a disaster recovery system) with a different number of data slices anyway, then performance of a restore will be no worse if a backed up table had the pre-expansion number of data slices (on the source system) than if it had the post-expansion number of data slices.
If a table is being redistributed when nzbackup is attempting to back up that table, the backup will wait for that redistribute to complete, at which point the table will have the current expanded data slice count. Conversely, if backup of a table starts before it has been redistributed, an attempt by nz_redistribute to redistribute that table will wait for the backup transaction to complete (which is when the lock on the backed up table will be released).
Incremental backups of a database are not supported after expansion, for both online and offline redistribution. A complete backup is needed before incremental backups can be taken.

Table 6. Backup after redistribution
Offline	Online
Full backups of all databases must wait until offline redistribution of all tables is complete (during which time the system is unavailable to all clients, including backups).	You may take a full backup of any given database when all tables in that database have been redistributed. That condition can be checked via `nz_redistribute <dbname>` or by querying the system view _V_TABLES_TO_REDIST while connected to the database in question. In some cases, it may be appropriate to take a “mixed” backup before all tables in the given database have been redistributed.