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-13
Strategies for Reducing Reuse Failures
•
You cannot specify a value that is too small. For example, if you specify 0 (zero),
the default value of 128 MB is used. However, if you specify a buffer size that is too
large, SCF rejects the request.
•
DP2 automatically rounds any positive value you specify to the modulo16 value.
•
There should be sufficient physical memory in both the primary and backup DP2
CPUs to accommodate the altered buffer configuration.
For more information about using SCF ALTER DISK to set or change the
SQLMXBUFFER attribute, see the SCF Reference Manual for the Storage Subsystem.
Strategies for Reducing Reuse Failures
After you have confirmed that the SQL/MX buffers on one or more disks are
experiencing a large number of reuse failures, you should investigate these strategies
for reducing them:
•
Verify Sufficient CPU Physical Memory on page 15-13
•
Increase SQL/MX Buffer Space on page 15-13
•
Reduce the Number of Client Processes Accessing Each Disk on page 15-14
•
Perform Data Routing at the Application Level on page 15-14
Verify Sufficient CPU Physical Memory
Check that sufficient physical memory has been provided for the system CPUs. Both
the SQL/MX buffer and DP2 data cache must be configured with adequate physical
memory in both the primary and backup CPUs for the data volume. While both are
contained within virtual space, and clean pages can be stolen by the memory manager,
over-configuration can lead to excessive page swapping and performance loss. In the
worst-case scenario, an over-configured data cache can cause insufficient physical
memory to meet the demand of pages, which can contribute to a variety of failures.
For information about CPU memory requirements for NonStop SQL/MX, see Hardware
Requirements on page 2-1.
Increase SQL/MX Buffer Space
A common contributing factor for high reuse failure rates is an SQL/MX buffer that is
too small to effectively store all the plan fragments that are repeatedly executed by
applications, particularly larger fragments. After you confirm a high reuse failure rate in
a buffer, use the SCF ALTER command to increase SQLMXBUFFER size, starting with
128 MB and increasing it, as required, in increments of 128 MB.
Note. The backup DP2 CPU for a given disk does not use the SQL/MX buffer space unless it
becomes the primary CPU. Still, you should always plan and prepare for a smooth transition to
the backup CPU and backup physical memory should a takeover occur.