(Continued from page 5)
sense, utilizing it may not always yield increases in concurrency. This can be caused by transaction design considerations (i.e. all tables in a transaction must be considered, not just one table). Also, it is very important that all access to a table is consistent - all relevant transactions should use row level locking or all transactions should use page level locking. Note this does not apply to transactions and/or queries that set readlock = nolock.
You must weigh concurrency improvements against loss of some logical data integrity when contemplating the use of row-level locking.
How should caches be configured?
Ingres II has additional features that make answering this question less straightforward. The ability to enable fast commit without using shared cache is one such case. Now you can use a separate cache per DBMS server without losing fast commit. Although there are cache synchronization overhead issues, you can now avoid the "shared cache - shared crash" problem. If one DBMS fails, the others will not be forcibly terminated (this is done to prevent cache corruption).
Data access patterns continue to be a consideration when looking at shared vs. private caches. If users in different servers access the same data (especially for update), the cache synchronization overhead required with private caches may be excessive. Previous versions of Ingres assumed pessimistic fast commit - you could not utilize fast commit with private cache. The assumption was predicated on the belief that cache synchronization overhead would always be too expensive.
Shared cache (or caches) is the most common configuration.
The next decision depends on how many different page sizes are in use. For each page size, a separate cache is required. This increases the amount of tuning you need to consider. For example, the defaults for mlimit, wb_start and wb_end may need to be adjusted.
If scans are frequent, you may need to increase the number and/or the size of group buffers.
Ultimately, transaction design and access patterns will determine how you configure your DMF cache(s).
Any time a new release brings increases in flexibility and opportunity for performance improvements there is more understanding required to properly use the new features. Tools like IPM and information provided by IMA can help you understand Ingres performance. As with any previous version of Ingres, trying out changes on a test system and observing the effects is the best way to learn.
In a future issue, I'll explore the remaining three topics. Until then, happy tuning.