Database Forum / DB2 Topics / January 2006
type-2 index causing performance hit
|
|
Thread rating:  |
bernhard.willems@gmail.com - 02 Jan 2006 18:35 GMT 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 - 02 Jan 2006 18:44 GMT > 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). [quoted text clipped - 10 lines] > 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@gmail.com - 02 Jan 2006 20:40 GMT 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 - 02 Jan 2006 22:07 GMT > Thanks for the tip and suggestions. > I'll try the high percent free value, but the load is not possible. The [quoted text clipped - 5 lines] > 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@gmail.com - 03 Jan 2006 00:06 GMT 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 - 03 Jan 2006 00:08 GMT > Thanks for the tip and suggestions. > I'll try the high percent free value, but the load is not possible. The [quoted text clipped - 5 lines] > 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
 Signature Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab
bernhard.willems@gmail.com - 03 Jan 2006 00:28 GMT 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] - 03 Jan 2006 00:41 GMT 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
> Hi Serge, > [quoted text clipped - 11 lines] > 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@gmail.com - 03 Jan 2006 00:53 GMT 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 - 03 Jan 2006 01:28 GMT > Hi Serge, > [quoted text clipped - 11 lines] > 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
 Signature Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab
Serge Rielau - 04 Jan 2006 03:04 GMT > 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
 Signature Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab
bernhard.willems@gmail.com - 05 Jan 2006 19:09 GMT 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@yahoo.com - 05 Jan 2006 22:15 GMT 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.
|
|
|