Btree replace/updates

root@candle.uucp (Bruce Momjian) writes...
>We run on VAX/VMS and Ingres 6.4, and have seen that replace/updates in
>large btree tables to be very sluggish.  So sluggish that if a table
>with 10,000 rows (100 byte width) has this command executed:
> 
>	replace (field1 = 3)
>	   or
>	update set field1 = 3
> 
>If the table is isam   = 20 seconds
>If the table is heap   = 18 seconds
>If the table is btree  =  3 hours
> [stuff deleted] 
>It is definitely not locking, because we ran a test on a newly created
>database with one user at night and got the same results.
> 
>It is not the 'where clause' because there isn't one.  The table is
>fully optimized.  We tried creating other tables for testing and they
>have the same problem.  I have gone to Isam until it is fixed. 
> 
>Retrieves from Btree tables is fine.
> 
There are several possibilities. I am willing to bet it is logging-related.
Bear with me - this will be long-winded.

1) Row movement in a btree will cause intensive logging activity. If you are
	- updating a varchar field to a string of a longer length AND/OR
	- updating a null field to a non-null value AND/OR
	- updating a field in a compressed table
   then the resulting row length may not fit on the page, requiring a row move.
   This will cause a change to 2 data pages (from and to pages) plus at least
   one update to a leaf index page. ISAM would not see this as it does not
   have a dense index. Ditto heap (no index at all).

2) Updating a key value will cause changes to the index. While there may
   only be one data page change, there may be extensive index changes
   including index splitting.

3) The transaction granularity may be writing extra pages to the log.
   By default, each change page is written to the log FOR EACH ROW.
   This is often referred to as savepoints. If there is extensive
   index activity as well, that is that much more log activity.
   10,000 rows updated will mean a minimum of 10,000 pages logged.
   10,000 index rows updated will mean AN ADDITIONAL 10,000 pages
   logged. Even if not in a btree, changing the granularity may
   help.

3) [Yes, you ruled this out. But hey, this is my soapbox.]
   Locking in the btree index may be interfering with your operation.

You can easily check the volume of logging activity using IPM.
There are several solutions if logging is the culprit.

1) SET SESSION WITH ON_ERROR = ROLLBACK TRANSACTION. This will cause
   a change page to be logged only once, even if many rows on that
   page are updated within a single transaction. This transaction
   granularity can only be changed in 6.4 on.

2) SET NOLOGGING. [warning - not intended for production systems]
   This does just what it says. It certainly saves a lot of overhead.
   Turning off logging however is contrary to the point of having a DBMS.
   You are turning off the ability to cleanly recover from a crash. Read
   the manuals for more scary info.

3) MODIFY TO HEAP, do the update and then MODIFY TO BTREE. This will
   get around whatever is happening in the btree. The modify time
   should be less than the several hour updates you are seeing.

Oops - gotta run. Feel free to email if you want to discuss further.

Larry Stein
Ingres Consulting
lstein@ingres.com
Ingres Q & A
To William's Home Page
Email William