Allocating Memory for DSS Queries

Home | Previous Page | Next Page Tuning Configuration for Resource Usage > Tuning Memory-Management Parameters >

Allocating Memory for DSS Queries

Large memory-consuming queries are considered DSS queries whether they are carefully written and tuned SQL statements or ad hoc queries created by users through third-party analysis tools. Such queries are managed by the Resource Grant Manager (RGM), as described in Appendix B. How SQL Queries Are Executed.

The following sections describe the use of configuration parameters that set global values for queries managed by the RGM. For information about managing how individual sessions and queries use memory, see Managing Memory for DSS Applications.

Specifying the Maximum Amount of Memory Available for Queries

To manage large queries in both OLTP and DSS environments, you set the DS_TOTAL_MEMORY configuration parameter, which limits the amount of shared memory that queries can obtain on each coserver. To allow more memory-intensive queries and fewer transactions to run concurrently, set this parameter to a high value. For information about the limits for SHMVIRTSIZE and DS_TOTAL_MEMORY, refer to the release notes.

The database server uses the value that you specify when it allocates memory to queries. For a figure that illustrates the relation of the memory-configuration parameters for DSS queries and provides more information about how the database server uses the parameter that limits memory for DSS queries, refer to Memory Allocation.

If you do not specify an amount of memory for memory-consuming queries or if you set it to an inappropriate value, the database server derives an appropriate value. If the database server changes the value that you set, it sends a message to your console in the following format:

DS_TOTAL_MEMORY recalculated and changed from old_value kilobytes 
            to new_value kilobytes

If this message appears, follow the steps that the database server uses to estimate a new amount, as described in Using the Database Server Algorithm to Derive a Minimum for DSS Memory. You can then adjust the configuration setting.

To limit the number of queries that can run at one time, specify an appropriate number as the argument to the DS_MAX_QUERIES configuration parameter. For more information, refer to Limiting the Number of Concurrent Queries.

Estimating Memory Required for DSS Queries

When you estimate the amount of memory to allocate for memory-consuming queries, you take various aspects of the database server use into account.

To estimate required DSS query memory

Use the following formula as a starting point for estimating the amount of shared memory to allocate to decision-support queries:
```
DS_TOTAL_MEMORY = p_mem - nondecision_support_memory
```
The value of p_mem is the total physical memory available on the computer.
Use the following formula to estimate the amount of memory required for other queries and other applications:
```
nondecision_support_memory = os_mem - rsdnt_mem -
               (128 kilobytes * users) - other_mem
```
Variable

Description

os_mem

Size of operating system memory, including buffer cache

rsdnt_mem

Size of Informix resident shared memory

users

Number of expected users (connections) that the third argument of the NETTYPE configuration parameter specifies

other_mem

Size of memory used for other (non-IBM Informix) applications

In general, for OLTP applications, set the SHMTOTAL configuration parameter to between 20 and 50 percent of the value of total shared memory in kilobytes. For DSS applications, specify between 50 and 80 percent of total shared memory. If the database server is used exclusively for DSS queries, specify as much as 90 percent of total shared memory.

Using the Database Server Algorithm to Derive a Minimum for DSS Memory

Follow these steps to find out how the database server arrives at its estimate of required DSS memory:

The database server first establishes a minimum for decision-support memory, using the following formula:
```
min_ds_total_memory = NUMCPUVPS * 4 * 128 kilobytes
```
The database server next establishes a working value for the amount of decision-support memory in the following steps.
1. If the configuration file specifies the total amount of DSS memory, the database server checks the total amount of shared memory that is specified for the database server with the SHMTOTAL configuration parameter. To avoid setting the total amount of memory for DSS queries too high to be allocated, the database server uses the following formula:
```
IF DS_TOTAL_MEMORY <= SHMTOTAL - nondecision_support_memory THEN
   decision_support_memory = DS_TOTAL_MEMORY
ELSE
    decision_support_memory = SHMTOTAL -
                  nondecision_support_memory
```
  For information about estimating an appropriate amount of memory for OLTP applications, see Estimating Memory Required for DSS Queries.
  
  If the total amount of shared memory is not specified or is set to 0, the database server sets decision-support memory to the value that you specified for DSS memory.
2. If the configuration file does not specify the total amount of DSS memory, the database server checks the total amount of shared memory that is specified for the database server. If SHMTOTAL is set, the database server uses the following formula to calculate the amount of decision-support memory:
```
decision_support_memory = SHMTOTAL -
                  nondecision_support_memory
```
  If SHMTOTAL is not set, the database server sets decision-support memory to the minimum value as calculated in step 1.
If the database server verifies that the amount of shared memory available to the database server is both greater than the calculated minimum amount required and less than the maximum physical memory for the coserver, the database server sets DSS memory equal to the calculated minimum amount.
If the database server finds that the derived value for DSS memory is greater than the maximum possible amount of memory for the computer, it sets DSS memory equal to the maximum possible memory.

Managing Memory Allocation for a Single Query

You can set the PDQPRIORITY configuration parameter to specify the percentage of shared memory that the database server can allocate for a query. If this configuration parameter is not explicitly set, it has a value of 0, at which the database server grants a minimum of 128 kilobytes of shared memory for each SQL operator instance on each coserver.

The global PDQPRIORITY configuration parameter can be set to specify a memory range as a minimum and maximum percentage of memory permitted for memory-consuming queries. To override this configuration parameter setting, the SQL user or client application can set the PDQPRIORITY range with the SQL statement or the environment variable.

Important:

To allow the database server to estimate an appropriate memory allocation for each query or specify memory allocation behavior for individual user sessions and distribute the allocated memory proportionally among query operators, use the SET ENVIRONMENT IMPLICIT_PDQ and SET ENVIRONMENT IMPL_PDQ_BOUND statements in a sysdbopen() routine For information, see Adjusting Resource Use by Specific Sessions.

Two other configuration parameters limit the amount of memory that each query is granted. The DS_TOTAL_MEMORY parameter determines the amount of shared memory available for DSS queries, as described in Specifying the Maximum Amount of Memory Available for Queries. The MAX_PDQPRIORITY parameter specifies the percent of the PDQPRIORITY amount that a query is actually granted, as described in Limiting the Amount of Memory That a Query Actually Receives.

The following sections describe how the DS_TOTAL_MEMORY, PDQPRIORITY, and MAX_PDQPRIORITY configuration parameters are interrelated and how they can control the amount of memory allocated for memory-consuming queries.

Specifying the Amount of Memory That a Query Can Request

The PDQPRIORITY configuration parameter provides a minimum value or a range from a minimum value to a maximum value that determines the percentage of shared memory that an individual query can be granted.

The largest PDQPRIORITY value in the range is the optimal memory allocation. The smallest PDQPRIORITY value is the minimum acceptable memory allocation for the query.

Consider the following factors when you set PDQPRIORITY:

If you set PDQPRIORITY to a range, the query can run when available memory is significantly within the range. The amount of memory allocated for a query can vary from one execution to another, depending on the workload on the database server.
If you specify a single value, the query can run only when at least that amount of memory is available.
If PDQPRIORITY is not set or is set to 0, each SQL operator instance required by a query is granted 128 kilobytes of memory.
For example, on a single coserver system with one CPU virtual processor, a query with two hash joins is granted 2 * 128, or 256 kilobytes of memory. On a single coserver system with two CPU virtual processors, each hash join SQL operator has two instances, so the query is granted 2 * 2 * 128, or 512 kilobytes of memory. This amount of memory is not likely to be sufficient for the hash join, which will need to overflow to temporary disk space.

Tip:

The optimizer can ignore PDQPRIORITY, estimate the amount of memory required by a query, and allocate memory appropriately. For information, see Enabling Optimizer-Determined Memory Allocation.

For more information on memory resources that can be allocated to queries, see Specifying the Maximum Amount of Memory Available for Queries and Limiting the Amount of Memory That a Query Actually Receives.

Limiting the Amount of Memory That a Query Actually Receives

The MAX_PDQPRIORITY configuration parameter specifies the percentage of PDQPRIORITY-specified memory that a single query can actually receive. This parameter dampens the effect of large CPU- and memory-intensive queries on transaction throughput and make it possible for more queries to run concurrently.

Users and client applications can set the PDQPRIORITY environment variable or use the SET PDQPRIORITY statement in SQL to override the PDQPRIORITY configuration parameter setting.The MAX_PDQPRIORITY configuration parameter limits the amount of memory that is actually granted.

Setting MAX_PDQPRIORITY can help to balance a system that runs both OLTP clients and DSS queries. To allocate more resources to OLTP processing, reduce the value of MAX_PDQPRIORITY. To allocate more resources to decision-support processing, increase the value of MAX_PDQPRIORITY.

Tip:

To manage memory use for specific sessions or groups of sessions, use the mixed-workload management features described inAdjusting Resource Use by Specific Sessions.

Tip:

The DS_TOTAL_MEMORY, DS_MAX_QUERIES, PDQPRIORITY, and MAX_PDQPRIORITY configuration parameters can be tuned with the onutil SET command while the server is running.

For more information about DSS query memory and an illustration of the way in which the memory-use configuration parameters are related, refer to How the RGM Grants Memory. For more information about the environment variable and the SQL statement, refer to the IBM Informix: Guide to SQL Reference and the IBM Informix: Guide to SQL Syntax, respectively.