1. Problem description
During a POC the customer found that ingesting almost 10M rows
into
their table took from 50x to 100x longer using the GA product
compared to Beta1R2. Part of this was due to the problem
described
in APAR IC85355 (performance problem in db2GetRowPartNum). The
fix
for APAR IC85355 has been delivered to V10.1 FP1. But even with
that fix the performance degradation was still anywhere from 2x
to
10x. The problem can be seen in these timings from Beta1r2, GA
without the fix for APAR IC85355, and GA with the fix for APAR
IC85355:
$ cat time.beta1r2
real 0m18.14s
user 0m0.01s
sys 0m0.08s
$ cat time.ga
real 16m27.99s
user 0m0.02s
sys 0m0.04s
$ cat time.ga.fix
real 0m31.17s
user 0m0.02s
sys 0m0.05s
Various configurations show different amounts of degradation,
but in all cases Beta1r2 performs best, followed by GA with the
fix for APAR IC85355, followed by GA.
2. Operating system and level: AIX
3. Client, server, and gateway information
a. Client information, if applicable
Project:
Build Level:
Environment (including Platform and bitness):
b. Gateway information, if applicable
Project:
Build Level:
Environment (including Platform and bitness):
c. Server information, if applicable
Project:
Build Level:
Environment (including Platform and bitness):
4. System setup
(For example, if your database is partitioned, this is where
this information should be included. Provide clearcase view
name, and contents of db2nodes.cfg file, including catalog,
coordinator node information. Also which machine name has which
node number(s) for DPF.)
5. Diagnostic information
(This section should include applicable db2diag.log entries,
stack traces, lineofcode tool output, and formatted dump files.
Also include any other FODC or PD tool output that helped you or
will help Development diagnose the failure you're opening this
defect for. Include the location of all files not attached but
referenced by this defect.)
6. How to reproduce the problem
(Include the name of the bucket and snippet of existing or
planned test unit that exposed the failure described above. A
"repro" should be as small as possible. Also, include the degree
of difficulty of the repro. A repro is Easy if it can be done
on demand, Medium if it can be done after several attempts, and
Hard if it is not possible.)
setup.clp
----------
DB2START;
CREATE DB testdb;
CONNECT TO testdb;
CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'D', NULL, NULL);
CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', NULL, NULL);
DROP TABLE my_table;
CREATE TABLE my_table (
"ORDERKEY" BIGINT NOT NULL,
"SHIPDATE" DATE NOT NULL ,
"COMMITDATE" DATE NOT NULL ,
"RECEIPTDATE" DATE NOT NULL)
DISTRIBUTE BY HASH("ORDERKEY");
ingest.sh
----------
db2 -tvf setup.clp
db2 -v "INGEST SET disk_write 0"
db2trc on -m "*.*.SQLO,SQLUDI,SQLU.*.*" -perfcount -t
# The input file has about 10 million records.
time db2 -v "INGEST FROM FILE input_file.del \
FORMAT DELIMITED BY '|' \
RESTART OFF \
INSERT INTO my_table"
db2trc dump cdi_perf.dmp
db2trc off
db2trc perffmt cdi_perf.dmp cdi_perf.fmt
Then look at the timings in the performance trace.
7. Did anything in particular seem to cause this problem?
8. Is this problem a regression? If so, please provide details
on the last successful build level similar to (3) above.
A regression from Beta1r2. |
