(Continued from page 4)

How are page sizes selected?
Larger page sizes (> 2k) offer certain benefits.  A major benefit is the ability to use row-level locking.  Another is I/O reduction on scans.  As with any feature, it is important to understand what you want to do and the associated tradeoffs of any changes you make.

Using a larger page size will have an effect on concurrency especially if you don't use row level locking.  Locking a larger page will reduce concurrency because more rows are affected with each page lock.  Although the decision to use row level locking may appear obvious, it may not be.  We'll discuss this in the next section.

Larger page sizes allow the row size to exceed 2k.  The ability to create rows whose length exceeds 2k is no excuse to abandon good logical database design techniques.  However, there are some cases where performance concerns are paramount and denormalization techniques are critical.

Finally, Ingres II requires a separate data cache for each page size.  This presents opportunities for additional tuning.  One particular benefit is using larger page sizes on tables that are scanned frequently (especially on read intensive tables).  The downside is any updates, inserts or deletes will lock more rows and reduce concurrency.  You can set different DMF parameters for each cache (like write behind start, write behind end, read ahead size, read ahead count and others).

The bottom line is to carefully analyze usage patterns, types of access, fan-out (rows affected per query), and row size before using larger page sizes.

When to use row level locking
Using row level locking requires some other changes whose impact must be carefully considered.  You must reduce the isolation level below SERIALIZABLE.  All DBMSs require this compromise when using row level locking.  When an isolation level below SERIALIZABLE is selected, you can no longer assume that two or more write transactions run simultaneously will yield the same results when run over and over again.  An interesting point is that other DBMS implementations may require table level locking when the isolation level is set to SERIALIZABLE.  Think carefully about the loss of serializable isolation and how it will affect your transactions and logical data integrity.

You must also use a page size greater than 2k.  This is required because additional information is needed in the page header in order to use row level locking.  Placing those additional data structure on the existing 2k page layout would affect upward compatibility - data that used to fit on a 2k page would suddenly no longer fit when upgrading to Ingres II.  You must also enable a DMF cache for that particular page size in CBF.

Experience has shown that the need for row level locking is more apparent than real.  Even in cases where row-level locking appears to make

(Continued on page 11)