Troubleshooting
Problem
Compression is enabled. These are tiny rows that compress very well and consume little disk space. Although the table is highly compressed, CTAS performance is slow.
Symptom
Consider the following table definitions on a test database:
- nzsql test<<eof
CREATE TABLE T1 ( COL1 integer not null ) DISTRIBUTE ON (COL1);
CREATE TABLE T2 ( COL1 integer not null ) DISTRIBUTE ON (COL1);
eof
And data being populated as follows:
- dsn=$(nzinventory -type dataslices | egrep "^[ ]*[0-9]+" | wc -l)
for ((loop=1;loop<=dsn;loop++)); do echo $loop ; done | nzload -db test -t t1
for ((loop=1;loop<=22;loop++)); do nzsql test -c "insert into t1 select * from t1"; done
At this point, all SPUs have (basically) the same data and the same number of rows with 0% skew.
Compression is enabled. These are tiny rows that compress very well and consume little disk space.
Although the table is highly compressed, CTAS performance is slow.
- => insert into t2 select * from t1;
INSERT 0 1,811,939,328
Elapsed time: 1m54.158s
Resolving The Problem
Rowid numbers are located within the catalog in the /nz/data/RowCounter directory. They are assigned in batches of 100,000 based on the following setting:
- system.rowIdChunkSize = 100000
This means that the more rows per SPU (in 100k multiples).
- Each SPU is going to request more rowid batches.
- All SPUs request rowid batches during the CTAS.
- The bigger the system, the slower the CTAS performance will be.
This situation is resolved by maximizing the rowIdChunkSize setting as follows:
- system.rowIdChunkSize = 5000000
A new CTAS test shows that on a 10400 NPS system, the performance would improve by four times.
- => insert into t2 select * from t1;
INSERT 0 1,811,939,328
Elapsed time: 0m29.693s
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ816827
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21570538