type-2 index causing performance hit

[bernhard.willems]

We are using DB2 UDB version 8.1 fp7 & fp9 on AIX 5.2.
On our databases we do massive inserts and deletes (millions of rows).
The performance is dramatically reduced because of pseudo deleted keys.
In a 10 minutes it goes from 18000 transaction per minute to 9000 and
lower.
When an index reorg with CLEANUP ONLY PAGES is executed, the number of
transactions climbs again. But a few minutes later it drops again to
9000.
We have to run the reorg constantly to keep a steady speed.

I'm thinking of using the option minpctused on all indexes.
But how do you specify this when constraints like primary keys are
used?
Are there other options we can use to keep a steady performance.

[Mark A]

<be**************@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

We are using DB2 UDB version 8.1 fp7 & fp9 on AIX 5.2.
On our databases we do massive inserts and deletes (millions of rows).
The performance is dramatically reduced because of pseudo deleted keys.
In a 10 minutes it goes from 18000 transaction per minute to 9000 and
lower.
When an index reorg with CLEANUP ONLY PAGES is executed, the number of
transactions climbs again. But a few minutes later it drops again to
9000.
We have to run the reorg constantly to keep a steady speed.

I'm thinking of using the option minpctused on all indexes.
But how do you specify this when constraints like primary keys are
used?
Are there other options we can use to keep a steady performance.

Create an unique index with the attributes you want prior to creating the PK
(create the PK with an alter table).

I doubt that minpctused used will help. In fact it may make it worse since
it will attempt to reorg the index pages online while your are inserting and
deleting.

I would suggest using a high percent free value (25-30%) for the indexes.
Other options include using the load command.

[bernhard.willems]

Thanks for the tip and suggestions.
I'll try the high percent free value, but the load is not possible. The
application (not ours) just works this way.
I hope one of the options will work, cause else I must say DB2 is not
build for high transaction volumes we are doing and that would be very
sad.
I read about the benefits from type-2 indexes, but there seems to be a
big drawback because it reduce the efficiency of the index big time in
our situation.

[Mark A]

<be**************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Thanks for the tip and suggestions.
I'll try the high percent free value, but the load is not possible. The
application (not ours) just works this way.
I hope one of the options will work, cause else I must say DB2 is not
build for high transaction volumes we are doing and that would be very
sad.
I read about the benefits from type-2 indexes, but there seems to be a
big drawback because it reduce the efficiency of the index big time in
our situation.

DB2 has done very well in benchmarks with high transaction volumes, so it is
built for such workloads. In fact, DB2 holds the record for the highest tpmC
in the TPC-C benchmark.
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp

[bernhard.willems]

Yes DB2 is wel tuned for TPC-C...
But DB2 has like other databases some drawbacks.
Why does DB2 do logical deletes on indexes anyway? That's really
obsolete technology.
Enabling online index defragmentation using minpctused must have some
impact, otherwise it would be default or could someone shed a light why
you don't want that as default.
But why logical deletes?

And there other issues with DB2 like clustering and overflow rows which
all need reorgs because they reduce the performance overtime.
You can do online reorgs, but if your running high volumes transactions
24x7 it's a loose - loose situation:
- reduced perfomance because tables /indexes need reorgs
- reduced performance because your running reorgs constantly

Automation is nice, but maybe they can try and resolve the cause of the
high maintance DB2 requires because those high volumes we are running
the online reorgs just cannot keep up.

Well DB2 is old so it must be a feature. I just hope that someday they
announce these reorg features are no more.

[Serge Rielau]

be**************@gmail.com wrote:

Thanks for the tip and suggestions.
I'll try the high percent free value, but the load is not possible. The
application (not ours) just works this way.
I hope one of the options will work, cause else I must say DB2 is not
build for high transaction volumes we are doing and that would be very
sad.
I read about the benefits from type-2 indexes, but there seems to be a
big drawback because it reduce the efficiency of the index big time in
our situation.

Would you mind telling us more about the load.
E.g. do the INSERTs all happen at the end of the index (identity column)?
I haven't heard of issues with TYPE 2 indexes before. Maybe opening a
PMR would be in order to get to the bottom of this....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[bernhard.willems]

Hi Serge,

Well I think we have more than one table having problems with pseudo
keys.
Because of the high transaction volume a table lock is never possible,
so the index leaf pages are never cleaned of pseudo empty pages.
We have one table with one index on a sequence number.
One proces is filling the table the other is reading it bottom up and
deleting the processed rows.
When you do a min(sequence) query using UR it takes > 5 seconds before
we get an answer. After a index reorg it is quick again, wel for a
minute or so.

They way I look at it, it only works if a table lock can be achieved
and that does not seem to be the case. Same thing on other tables.

[Bob [IBM]]

I am curious ... you are linking this performance to Type-II indexes ... did
you run similar tests with the original Type-I indexes and if so what was
the performance at that time?

The whole point of Type-II indexes and pseudo-deleted keys is that only
those transactions that need to be blocked, are. So transaction rates should
be going up with Type-II as compared to Type-I... hence my question as to
what did you see with Type-I indexes? Do you have any comparative
performance numbers?

The last time I checked (and this "may" have changed), cleanup of pseudo
deleted keys and deletion of pseudo empty pages will be done on the fly by
other transactions after a deleting transaction has completed. Of course,
this can always be manually triggered as you have alluded to, but don't
forget that you now have "online" reorg of indexes. It does not have to be
an offline process.

Bob

§<be**************@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi Serge,

Well I think we have more than one table having problems with pseudo
keys.
Because of the high transaction volume a table lock is never possible,
so the index leaf pages are never cleaned of pseudo empty pages.
We have one table with one index on a sequence number.
One proces is filling the table the other is reading it bottom up and
deleting the processed rows.
When you do a min(sequence) query using UR it takes > 5 seconds before
we get an answer. After a index reorg it is quick again, wel for a
minute or so.

They way I look at it, it only works if a table lock can be achieved
and that does not seem to be the case. Same thing on other tables.

[bernhard.willems]

Hi Bob,

We never used type-1 indexes. We started with version 8.

DB2 IC says under tuning - online index defragmentation:
"For type-2 indexes, keys are removed from a page during key deletion
only when there is an X lock on the table. During such an operation,
online index defragmentation will be effective. However, if there is
not an X lock on the table during key deletion, keys are marked deleted
but are not physically removed from the index page. As a result, no
defragmentation is attempted."

But now I've read it again I'm confused if they mean when this is when
you use MINPCTUSED.

Well I'm open to suggestion why a REORG INDEXES CLEANUP ONLY PAGES will
actually speed things up.
I'm doing this while the applications is running (using ALLOW WRITE
ACCESS).

[Serge Rielau]

be**************@gmail.com wrote:

Hi Serge,

Well I think we have more than one table having problems with pseudo
keys.
Because of the high transaction volume a table lock is never possible,
so the index leaf pages are never cleaned of pseudo empty pages.
We have one table with one index on a sequence number.
One proces is filling the table the other is reading it bottom up and
deleting the processed rows.
When you do a min(sequence) query using UR it takes > 5 seconds before
we get an answer. After a index reorg it is quick again, wel for a
minute or so.

They way I look at it, it only works if a table lock can be achieved
and that does not seem to be the case. Same thing on other tables.

So what you do is queue processing. This, btw, is exactly what TPC-C
does and of course we use type-2 indexes there.
I'll send you some slides as a PDF. They may give you some ideas.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

I'll send you some slides as a PDF. They may give you some ideas.

Cheers
Serge

Hmm... an invalid gmail address? Well.. ping me if you want slides...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[bernhard.willems]

Sorry, but google does not let me use a gmail account for some reason.
But I'm interested in the slides, I'll email you from my business
account.

And indeed it is used as a queue.
Maybe you can explain the following snapshot from the dynamic SQL:
It's a snapshot taken with a 120 seconds interval (after reset):

Number of executions = 13
Number of compilations = 0
Worst preparation time (ms) = 4
Best preparation time (ms) = 1
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 192060
Buffer pool index physical reads = 192041
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 58.367505
Total user cpu time (sec.ms) = 5.380000
Total system cpu time (sec.ms) = 9.150000
Statement text = (SELECT MIN(EVENT_SEQ) + ? FROM
FMC.HS_AUDIT_TRAIL)

The table looks likes this and has > 20 miljoen rows.

------------------------------------------------
-- DDL Statements for table "FMC "."HS_AUDIT_TRAIL"
------------------------------------------------

CREATE TABLE "FMC "."HS_AUDIT_TRAIL" (
"EVENT_SEQ" DECIMAL(16,0) NOT NULL WITH DEFAULT 0 ,
"CREATED" TIMESTAMP ,
"EVENT" INTEGER ,
"TEMPL_VALID_FROM" TIMESTAMP ,
"PROCESS_NAME" VARCHAR(63) ,
"PROCESS_ID" VARCHAR(64) ,
"TOP_LVL_PROC_NAME" VARCHAR(63) ,
"TOP_LVL_PROC_ID" VARCHAR(64) ,
"PARENT_PROC_NAME" VARCHAR(63) ,
"PARENT_PROC_ID" VARCHAR(64) ,
"PROC_TEMPL_NAME" VARCHAR(32) ,
"BLOCK_NAMES" VARCHAR(254) ,
"USER_NAME" VARCHAR(32) ,
"SECOND_USER_NAME" VARCHAR(32) ,
"ACTIVITY_NAME" VARCHAR(32) ,
"ACTIVITY_TYPE" INTEGER ,
"ACTIVITY_STATE" INTEGER ,
"COMMAND_PARAMETERS" VARCHAR(1024) ,
"PROGRAM_NAME" VARCHAR(32) ,
"ACTIVITY_RC" INTEGER ,
"ASSOCIATED_OBJECT" VARCHAR(64) ,
"OBJECT_DESCRIPTION" VARCHAR(254) ,
"SECOND_ACT_NAME" VARCHAR(32) ,
"EXTERNAL_CONTEXT" VARCHAR(254) )
IN "HFX" ;
ALTER TABLE "FMC "."HS_AUDIT_TRAIL" VOLATILE CARDINALITY;

-- DDL Statements for indexes on Table "FMC "."HS_AUDIT_TRAIL"

CREATE INDEX "FMC "."HS_AT_SEQ_INDEX" ON "FMC
"."HS_AUDIT_TRAIL"
("EVENT_SEQ" ASC);
There are no trigger on the table.
After doing a full reorg from the index only the snapshot looked like
this:
Number of executions = 55
Number of compilations = 0
Worst preparation time (ms) = 12
Best preparation time (ms) = 0
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 9130
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 0.120709
Total user cpu time (sec.ms) = 0.000000
Total system cpu time (sec.ms) = 0.000000
Statement text = (SELECT MIN(EVENT_SEQ) + ? FROM
FMC.HS_AUDIT_TRAIL)

Even after the reorg each query takes an average of 166 i/o's. Must be
a big index tree.
Don't know how I can find out exactly how big the tree is in size and
levels.

[m0002a]

The number of levels is contained in the syscat.indexes tables as
documented in Appendix D fo the SQL Reference Vol 1. This value is
updated after you run runstats command on the index.