SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-11
Using Co-located Indexes
To take maximum advantage of parallel index updates, put a table’s indexes on
separate volumes and configure them on separate processors to eliminate contention
of parallel operations on indexes serviced by the same disk process.
Using Co-located Indexes
NonStop SQL/MX supports co-located indexes, where an index is partitioned across
the same disk volumes as its underlying table.
The primary advantage of co-located indexes is reduction in message traffic. To
perform index maintenance, a single message can be sent to the DP2 process for the
disk volume on which both the index and table partition are located.
The primary disadvantage of co-located indexes is the increased disk arm movement
and resulting latency from accessing all index and table data on the same physical
disk, especially if the index and table partitions are large. This issue might not exist for
small index and table partitions located on the same disk volume.
Another disadvantage of co-located indexes is that partition management of indexes is
not automatic. If a table partition is moved to another disk, you must remember to
move its corresponding index partition.
Managing Cache Memory Size
The disk process uses a buffer in memory to keep copies of the disk blocks that have
been accessed most recently. This area of memory is called cache. If the disk process
finds a requested block in cache, it can satisfy the request immediately without
requesting a physical I/O operation.
Cache size has an important effect on performance. The larger the cache, the more
likely that a block need be read only once.
To see if cache is operating efficiently, use the STAT option of the PUP LISTCACHE
command. If CACHE READ HITS are less than 90 percent, consider increasing the
cache size. If the ratio of CACHE FAULTS to CACHE CALLS is greater than one
percent, consider reducing the cache size, adding more physical memory to the
processor, or processing to other processors.
Cache size is controlled through the Subsystem Control Facility (SCF). For more
information on using SCF to set cache size, see the SCF documentation.
Maximizing Disk Process Prefetch Capabilities
NonStop SQL/MX can enhance performance by reading blocks of data into cache
asynchronously before they are needed. This disk process prefetch operation works
best when you request long sequential scans through data or when your access plan
has a low selectivity value (as described in the SQL/MX Query Guide).