Estimating the size of the virtual portion of shared memory
You can use a formula to estimate the initial size of the virtual portion of shared memory. You specify the initial size in the SHMVIRTSIZE configuration parameter.
About this task
The formula for estimating an initial size of the virtual
portion of shared memory is as follows:
shmvirtsize = fixed overhead + shared structures +
(mncs * private structures) +
other buffers
Procedure
To estimate an SHMVIRTSIZE value with the preceding formula:
- Estimate the value for the
fixed overhead
portion of the formula as follows:fixed overhead = global pool + thread pool after booting
- Run the onstat -g mem command to obtain the pool sizes allocated to sessions.
- Subtract the value in the freesize field from the value in the totalsize to obtain the number of bytes allocated per session.
- Estimate a value for the thread pool after booting variable. This variable is partially dependent on the number of virtual processors.
- Estimate the value of
shared structures
with the following formula:shared structures = AIO vectors + sort memory + dbspace backup buffers + data-dictionary cache size + size of user-defined routine cache + histogram pool + STMT_CACHE_SIZE (SQL statement cache) + other pools (See onstat display.)
- Estimate the next part of the formula, as follows:
- Estimate the value of other buffers to account for private buffers allocated for features such as lightweight I/O operations for smart large objects (about 180 KB per user).
- Add the results of steps 1 through 4 to obtain an estimate for the SHMVIRTSIZE configuration parameter.
Results
Tip: When the database server
is running with a stable workload, you can use onstat -g
seg to obtain a precise value for the actual size of the
virtual portion of shared memory. You can then use the value for shared
memory that this command reports to reconfigure SHMVIRTSIZE.
To specify the size of segments that are added later to the virtual shared memory, set the SHMADD configuration parameter. Use the EXTSHMADD configuration parameter to specify the size of virtual-extension segments that are added for user-defined routines and DataBlade® routines.
What to do next
The following table contains a list of additional topics for estimating the size of shared structures in memory.
Shared-Memory Structure | More Information |
---|---|
Sort memory | Estimating memory needed for sorting |
Data-dictionary cache | Data-dictionary configuration |
Data-distribution cache (histogram pool) | Data-distribution configuration |
User-defined routine (UDR) cache | SPL routine executable format stored in UDR cache |
SQL statement cache | Enabling the SQL statement cache Monitor and tune the SQL statement cache |
Other pools | To see how much memory is allocated to the different pools, use the onstat -g mem command. |