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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-14
Managing DP2 Data Cache Memory Size
To preserve proper data cache function, try not to increase the SQL/MX buffer size
above 512 MB. If high reuse failure rates persist, consider implementing the other
strategies described in this subsection.
For more information about changing the size of the buffer, see Using SCF ALTER
DISK to Resize the SQLMXBUFFER Attribute on page 15-12.
Reduce the Number of Client Processes Accessing Each
Disk
Another factor that contributes to reuse failures is when too many client processes
attempt to access affected disks. To reduce this client process load, repartition tables
so that fewer are partitioned across each of your disks running NonStop SQL/MX. For
example, if all your 100 tables (T1 through T100) are partitioned across each of your
ten disks, repartition them so that T1 through T50 are partitioned across one set of five
disks and T51 through T100 are partitioned across the other set of five disks. This new
configuration produces half the plan fragments for the DP2 process and SQL/MX buffer
on each disk as the original configuration.
Perform Data Routing at the Application Level
Another strategy for reducing the number of client processes that access a given disk
is the use of data routing at the application level, whereby a running application
depends on a key value to determine which process communicates with any given
table row. The result is that each application accesses a much smaller number of DP2
processes.
For more information, contact your support representative.
Managing DP2 Data Cache Memory Size
The DP2 process for each disk running SQL/MX sessions uses a buffer in memory to
keep copies of the disk blocks that have been accessed most recently. This area of
memory is called DP2 data cache. If the DP2 process finds a requested block in cache,
it can satisfy the request immediately without requesting a physical I/O operation.
DP2 data cache size has an important effect on performance. The larger the DP2 data
cache, the more likely that a block need be read only once. However, because the DP2
data cache and SQL/MX buffer contend for the same virtual space, both must be sized
with consideration for the size requirements of the other. For more information, see
Managing SQL/MX Buffer Space on page 15-8.
To see if a DP2 data cache is operating efficiently, use the SCF STATS DISK, CACHE
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 even one or two
percent, consider reducing the cache size, adding more physical memory to the CPU,
or processing to other CPUs.