(Continued from page 14)

While this scheme allows for much larger tables (2^23 pages * page_size), it does not provide for more than 512 rows per page.  The following is a matrix of the maximum number of rows possible per page.  This test was conducted by creating a table with a single column defined as "char(1) not null not default".

Page Size               Max # Rows (per page)
    2 KB                           511
                 4 KB                           138
                 8 KB                           279
               16 KB                           512
               32 KB                           512
               64 KB                           512

This may not be much of an issue when using large tuple sizes, but is definitely an issue that needs to be evaluated prior to implementation.  Given the large tuple overhead and limited row capacity per page it is generally not advisable to use this feature on very small rows.

Row-level locking is a new feature that can be beneficial to some environments (usually those with concurrency problems due to schema and/or transaction design, where it is not feasible to redesign the database and/or those programs).  This feature is only available for the larger page sizes and is set with the "set lockmode" statement.  When row-level locking is used it is very important to increase both the locks per transaction and the maxlocks values (or concurrency will likely decrease instead of improve).  OpenIngres 2.0 / Ingres II provide significantly more locks (up to 2 billion within an installation) to accommodate this feature.

Some of the PROS of using > 2 KB page sizes include: ability to have a larger row size; ability to have row-level locking; potential to improve performance due to decreased I/O; and the potential to save disk space.  Some of the CONS include limited row capacity; increased row overhead; potential to have a negative impact on performance if improperly configured; and the potential to con

sume more disk space.  Also, if tools like "auditdb" are important to your site make sure to test them thoroughly on the larger sized pages.

All in all this is a great feature for many environments.  As with any type of change it is important to define the goal of the change, collect baseline metrics, benchmark the change, and only implement if it makes good business and technical sense.  Please refer to the Ingres II / OpenIngres 2.0 Database Administrator's Guide for more information on the topics discussed in this article.

Chip Nicolett is a senior consultant with Caribou Lake Software. He is a former CA employee, and the current Vice President of the NAIUA. He has been working with Ingres in a variety of positions for many years, with current focus being on Java connectivity.