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-9
Setting the SCF SQLMXBUFFER Attribute
Setting the SCF SQLMXBUFFER Attribute
For data volumes with NonStop SQL/MX, you can change the size of the segment data
area by using the SQLMXBUFFER attribute. The volume must be in the STOPPED
state to alter the SQLMXBUFFER attribute. From SCF, set SQLMXBUFFER as:
ALTER $volume, SQLMXBUFFER n (where n is the units in megabytes)
The minimum SQLMXBUFFER size is 1 MB, and the maximum is 768 MB. The default
value for SQLMXBUFFER is zero, which causes DP2 to automatically establish the
appropriate size for SQL/MX data space. For volumes accessed by NonStop SQL/MX,
use the default value (0) or values of at least 128 MB. Increasing SQLMXBUFFER
beyond 128 MB effectively reduces the maximum space available for cache. You do
not need to alter SQLMXBUFFER unless more virtual memory is needed for cache.
For detailed information about the SQLMXBUFFER attribute, see the SCF Reference
Manual for the Storage Subsystem.
Optimizing SQL/MX Memory Management
Certain embedded SQL/MX programs can use very large portions of the process’ flat
segment address range for:
•
Application-addressable memory
•
Memory space for the master executor to execute SQL/MX statements
The memory space requirement for the master executor can be minimized by using
parallelism to distribute the work to other processes, as explained in the discussion of
parallelism in the SQL/MX Query Guide.
SQL/MX processes can consume large amounts of addressable memory space as a
result of:
•
Parallelism among a large number of ESPs
•
The execution of plans that use sort and grouping operators
•
The optimization of complex plans
The heavy consumption of addressable memory space by SQL/MX processes can
lead to insufficient swap file space. As a result, you should provide more
kernel-managed swapped space on each processor by increasing the size of existing
swap files or adding new swap files.
You should periodically monitor your kernel-managed swap files while SQL/MX
programs are being compiled and executed to ensure that adequate swap file space is
available. If the required amount of swap file space is not available, an SQL/MX
compilation might fail, or an executing statement might return an “insufficient memory”
error.
See the discussion on managing kernel-managed swap files in the Kernel-Managed
Swap Facility (KMSF) Manual for information on how to use NSKCOM to monitor