Hi all,
first apologies if this question looks the same as another one I recently
posted - its a different thing but for the same szenario:-).
We are having performance problems when inserting/deleting rows from a large
table.
My scenario:
Table (lets call it FACT1) with 1000 million rows distributed on 12
Partitions (3 physical hosts with 4 logical partitions each).
Overall size of table is 350 GB. Each night 1.5 Million new rows will be
added
and approx. the same amount of old records will be deleted (Roll in/Roll out
with SQL INSERT/DELETE).
The table is stored in SMS tablespace with 16K Pagesize and 64 Pages
Extentsize.
The tablespace has 6 containers on each partition. Each container is on a
separate IBM ESS array.
Prefetchsize is 384 (6 containers * 64 pages). Prefetch behaves very well
with these settings (DB2_PARALLEL_I O is set)
DB2 is V8.1 ESE (DPF) FP5 and runs on AIX.
It takes 7 hours to insert 1.5 Million Rows into FACT1 and up to 7 hours to
delete the same amount.
The Insert is done via INSERT INTO FACT1 ... SELECT * FROM STAGING_TABLE.
Both the fact and the staging table are in tablespaces in the same nodegroup
and do have the same partitioning key.
On a similar table (lets call it FACT2) with a comparable amount of
data/rows and nearly identical configuration the same process takes only 5
minutes.
The main difference between these two tables is that FACT1 has 7 indexes
defined on it and FACT2 only 4.
One of the indexes in each case is unique, the others not (all type 2).
There is no clustering index and the APPEND attribute is set to ON.
I'm aware of the pseudo-delete mechanism of type-2 indexes and the
corresponding longer search time for insert's in the index leaf pages .
But an exclusive lock on the table before inserting/deleting does not change
the needed runtime.
(And the docs say that with a X-lock on table pseudo-deletes will not
happen).
Also after reorg of table and indexes the insert runtime is the same as
before.
Is it possible that the additional index maintenace for FACT1 leads to such
a longer runtime ?
What exactly happens internal for index maintenance (searched the docs - but
do not found internals)?
Anyone seen similar behaviour ?
I can post additional infos if required (table and Index definitions,
statistics ...) - but wanted to keep the posting small in first place.
TIA for any comments
Joachim
PS: Feel free to send comments by email to joklassen at web dot de
PPS: We are parallel investigating in MDC tables, using smaller tables (and
combining them with a UNION ALL view) and the use of LOAD FROM CURSOR
instead of INSERT
first apologies if this question looks the same as another one I recently
posted - its a different thing but for the same szenario:-).
We are having performance problems when inserting/deleting rows from a large
table.
My scenario:
Table (lets call it FACT1) with 1000 million rows distributed on 12
Partitions (3 physical hosts with 4 logical partitions each).
Overall size of table is 350 GB. Each night 1.5 Million new rows will be
added
and approx. the same amount of old records will be deleted (Roll in/Roll out
with SQL INSERT/DELETE).
The table is stored in SMS tablespace with 16K Pagesize and 64 Pages
Extentsize.
The tablespace has 6 containers on each partition. Each container is on a
separate IBM ESS array.
Prefetchsize is 384 (6 containers * 64 pages). Prefetch behaves very well
with these settings (DB2_PARALLEL_I O is set)
DB2 is V8.1 ESE (DPF) FP5 and runs on AIX.
It takes 7 hours to insert 1.5 Million Rows into FACT1 and up to 7 hours to
delete the same amount.
The Insert is done via INSERT INTO FACT1 ... SELECT * FROM STAGING_TABLE.
Both the fact and the staging table are in tablespaces in the same nodegroup
and do have the same partitioning key.
On a similar table (lets call it FACT2) with a comparable amount of
data/rows and nearly identical configuration the same process takes only 5
minutes.
The main difference between these two tables is that FACT1 has 7 indexes
defined on it and FACT2 only 4.
One of the indexes in each case is unique, the others not (all type 2).
There is no clustering index and the APPEND attribute is set to ON.
I'm aware of the pseudo-delete mechanism of type-2 indexes and the
corresponding longer search time for insert's in the index leaf pages .
But an exclusive lock on the table before inserting/deleting does not change
the needed runtime.
(And the docs say that with a X-lock on table pseudo-deletes will not
happen).
Also after reorg of table and indexes the insert runtime is the same as
before.
Is it possible that the additional index maintenace for FACT1 leads to such
a longer runtime ?
What exactly happens internal for index maintenance (searched the docs - but
do not found internals)?
Anyone seen similar behaviour ?
I can post additional infos if required (table and Index definitions,
statistics ...) - but wanted to keep the posting small in first place.
TIA for any comments
Joachim
PS: Feel free to send comments by email to joklassen at web dot de
PPS: We are parallel investigating in MDC tables, using smaller tables (and
combining them with a UNION ALL view) and the use of LOAD FROM CURSOR
instead of INSERT
Comment