SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-17
Maximizing Parallel Index Maintenance
For more information about indexes, see Determining When to Use Indexes on
page 3-16.
Maximizing Parallel Index Maintenance
Indexes are automatically updated whenever a row is inserted into the underlying table
and when any key column of the index is changed. Multiple indexes can be updated in
parallel. The file system accomplishes parallel index maintenance by issuing
asynchronous I/O requests to each disk process serving the indexes. Parallel index
maintenance occurs automatically without your having to specify a statement or
directive.
To take maximum advantage of parallel index updates, the indexes of a table should
reside on separate volumes and should be configured on separate processors to
eliminate any contention of parallel operations on indexes serviced by the same disk
process. Also, when the indexes cannot use separate volumes and processors, some
parallelism is achieved by a single disk process, which can process multiple requests
concurrently.
These limitations apply to parallel index maintenance:
•
Parallel updating is not performed when a large number of indexes are defined on
the same table, although the number of indexes that can be defined on the table
and still allow parallel updating is quite large.
•
Parallel updating is temporarily suspended when the file system is undoing a
transaction that failed.
Managing Cache Memory Size
The disk process uses a buffer in virtual memory to keep copies of the disk blocks that
have been accessed most recently. This area of virtual 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 it is that a block must be read only once.
To see if cache is operating efficiently, use the STAT option of the PUP LISTCACHE
command (D-series only). 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.
For G-series RVUs, use the SCF INFO DISK, CACHE command to display the disk
cache configuration information for the specified disk.
Control cache size by using PUP. For more information on setting the cache size, see
the Peripheral Utility Program (PUP) Reference Manual (D-series only) and the SCF
Reference Manual for the Storage Subsystem (G-series only).