SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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.
NOTE: The backup DP2 CPU for a given disk does not use the SQL/MX buffer space unless it
becomes the primary DP2. Still, you should always plan and prepare for a smooth transition to
the backup CPU and backup physical memory should a takeover occur.
For more information about CPU memory requirements for NonStop SQL/MX, see SQL/MX
Installation and Upgrade Guide.
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.
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, see “Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute” (page 299).
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 the “Managing SQL/MX Buffer Space”
(page 294).
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
300 Enhancing SQL/MX Database Performance