Improving performance with parallel database query and the memory grant manager
Parallel database query (PDQ) can improve performance dramatically. PDQ enables the database server to distribute the work for a query. For example, if an index needs to be built on a large table, the work can be distributed among multiple threads and processes.
PDQ includes functionality for resource management. When the database server uses PDQ to perform a query in parallel, a heavy load can be placed on the operating system. The following resources can be tuned:
- CPU VPs
- Disk I/O (to fragmented tables and temporary table space)
- Scan threads
When configuring the database server, you must consider how the use of PDQ affects other users. For example, if a query is running that is taking up all CPU resources, then the database server might not be as responsive to another query that usually runs very quickly. Care must be taken so that critical non-PDQ queries are still able to run with acceptable performance.
You can use the following methods to control how the database server uses resources:
- Limit the priority of parallel database queries
- Adjust the amount of memory from the memory grant manager
- Limit the number of scan threads
- Limit the number of concurrent queries
PDQPRIORITY environment variable
determines the degree of parallelism resources. The variable is used in
MAX_PDQPRIOIRTY as a
scaling factor in how the database server allocates
resources, as shown in Listing 24.
Listing 24. Setting the PDQPRIORITY environment variable
setenv--PDQPRIORITY--+-HIGH--------------------------------+ +-LOW---------------------------------+ +-OFF---------------------------------+ '-resources--+------------------------+ | (1) | '-------------high_value-'
Table 6 shows the PDQPRIORITY settings.
Table 6. PDQPRIORITY settings
|High||When the database server allocates resources among all users, it gives as many resources as possible to the query.|
|Low or 1||Data values are fetched from fragmented tables in parallel.|
|OFF||PDQ processing is turned off.|
|resources||An integer between 0 and 100; sets the percentage of the user-requested PDQ resources actually allocated to the query.|
|Optional high value||Optional integer value that requests the maximum percentage of memory. When you specify this value after the resources value, you request a range of memory expressed as a percentage.|
The more resources a database server dedicates to a query, typically the faster the server can complete the query. Contention for those resources can result, however, if other queries are trying to access those same resources. This can result in degraded performance.
The SQL statement SET PDQPRIORITY can be used to adjust the priority manually for a particular session.
MAX_PDQPRIORITY is the
ONCONFIG parameter that limits the PDQ
resources that the database server can allocate to any one DSS query.
MAX_PDQPRIORITY is used as a percentage
against the PDQPRIORITY for which any particular client asks. For
example, suppose that a user is in a hurry to get his data and sets
his PDQPRIORITY to 100. However, the DBA realizes that certain batch
jobs have to be run at the same time every night, and the DBA sets
MAX_PDQPRIORITY to 50. Fifty percent of 100
is 50, so the maximum percent of PDQPRIORITY resources that the user
can actually get is 50.
You can use
onmode -D to change the value of
MAX_PDQPRIORITY while the database server
On a system with both OLTP and DSS queries, a balancing act must be
maintained. If you set a
MAX_PDQPRIORITY too high,
and OLTP queries will suffer. If you set the value is too low, DSS queries
will not perform optimally. A DBA must therefore take care in tuning
You can set
MAX_PDQPRIORITY to one of the
values described in Table 7.
Table 7. MAX_PDQPRIORITY settings
|0||Turns off PDQ. DSS queries use no parallelism.|
|1||Fetches data from fragmented tables in parallel (parallel scans), but uses no other form of parallelism.|
|100||Uses all available resources for processing queries in parallel.|
|Any number||An integer between 0 and 100; sets the percentage of the user-requested PDQ resources actually allocated to the query.|
The ONCONFIG parameter
DS_TOTAL_MEMORY specifies the amount of
memory available for PDQ queries. The amount is specified in KB,
and it is allocated from the virtual portion of the Informix instance
DS_TOTAL_MEMORY should be set large enough
to allow a sizable chunk of work to be loaded into memory at one time.
Following are considerations to take into account when
- The total memory on the computer (do not exceed it)
- Overhead, such as the buffer pool
- Other processes on the computer. Reduced performance benefits and actual degradation of performance can ensue if paging or swapping results from a too-high setting.
SHMTOTAL specifies all the memory for the
database server (total of the resident, virtual, and message portions
SHMVIRTSIZE specifies the
initial size of the virtual portion of shared memory.
Allocating additional virtual memory for PDQ queries can trigger the
dynamic addition of one or more virtual shared memory segments.
Some operating systems experience performance overhead for each
additional segment added to shared memory. Avoid this performance hit
SHMTOTAL appropriately as follows so the minimum
number of segments is allocated:
- For OLTP applications, a base recommendation is to set
DS_TOTAL_MEMORYto between 20 and 50 percent of the value of
- If the database server is used for DSS queries exclusively, set
DS_TOTAL_MEMORYto between 90 and 100 percent of
- For systems that use both types of queries, a base recommendation is to
DS_TOTAL_MEMORYto between 50 and 80 percent of
You can set
DS_TOTAL_MEMORY dynamically with
onmode -M command.
Note that the value allowable for
DS_TOTAL_MEMORY is platform dependent. The
value for 32-bit systems must be an unsigned integer between 128 *
DS_MAX_QUERIES and 1,048,576. On 64-bit
systems, the limit is generally higher and varies with the operating
system. On HP 9000 platforms, for example, the maximum value is
DS_MAX_SCANS limits the number of PDQ scan
threads that can run concurrently. If this parameter is set too high,
the database server will have too many scan threads from multiple
decision-support queries running concurrently. This will cause
resource contention. The ready-queries queue in
onstat -g mgm grows.
The formula in Listing 25 can be used to calculate the number of scans allocated to a query.
Listing 25. Calculating the number of scan threads allocated to one query
scan_threads = min (-nfrags-, (DS_MAX_SCANS * -pdqpriority- / 100 * MAX_PDQPRIORITY / 100) ) |-------10--------20--------30--------40--------50--------60--------70--------80--------9| |-------- XML error: The previous line is longer than the max of 90 characters ---------|
-nfrags- is the number of fragments in the table with the largest number of fragments. -pdqpriority- is the setting for that particular query.
You can set the maximum number of scan threads dynamically with the
onmode -S option. This value must be an unsigned integer between 10 and 1,048,576.
Suppose a large table contains 50 fragments. If
DS_MAX_SCANS is not set, there is no limit
on the number of concurrent scans allowed. Fifty scans will be
allocated by the database server. The engine would try to run all
50 scan threads to read this table. If this report can be run by any
user, what happens if 50 people try
to run that report simultaneously? Without being checked, the engine would allocate
50 threads for each running of that report. Twenty-five hundred
threads would be spawned. Add overhead for other reports and other DSS
queries, and there is a potential for major contention issues.
Judicious use of
DS_MAX_SCANS prevents this
The balancing nature of database performance tuning is illustrated in this aspect of PDQ tuning. To reduce the time that scan threads for a large query will be in the ready-queries queue, reduce the number of scan threads allocated. However, if the number of scan threads for a query is less than the number of fragments, the query takes longer once it is underway.
DS_MAX_QUERIES is the
ONCONFIG parameter that specifies the
maximum number of PDQ queries that can run concurrently. The memory
grant manager (MGM) reserves memory for a query based on the formula
in Listing 26. The formula is also how the database server
decides how much memory to allocate for a query.
Listing 26. Reserving memory for a query
memory_reserved = DS_TOTAL_MEMORY * (PDQ-priority/100) * (MAX_PDQPRIORITY/100)
onmode -Q command can be used to set
onstat -g mgm option prints memory grant
manager (MGM) resource information. You can use the
onstat -g mgm option to monitor how MGM
coordinates memory use and scan threads. Listing 27
shows sample output.
Listing 27. Sample onstat -g mgm output
IBM Informix Dynamic Server Version 11.50.FC9 -- On-Line -- Up 00:00:24 -- 250944 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 4 DS_MAX_SCANS: 1048576 DS_NONPDQ_QUERY_MEM: 128 KB DS_TOTAL_MEMORY: 512 KB Queries: Active Ready Maximum 0 0 4 Memory: Total Free Quantum (KB) 512 512 128 Scans: Total Free Quantum 1048576 1048576 1 Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 (Queue Length) 0 0 0 0 0 Active Queries: None Ready Queries: None Free Resource Average # Minimum # -------------- --------------- --------- Memory 47.8 +- 2.7 32 Scans 1048575.3 +- 0.0 1048574 Queries Average # Maximum # Total # -------------- --------------- --------- ------- Active 1.0 +- 0.1 2 3371 Ready 0.0 +- 0.0 0 0 Resource/Lock Cycle Prevention count: 0
The MGM uses a series of gates, as shown in Table 8, to make sure that a PDQ query has enough resources to run correctly.
Table 8. Gate descriptions
|1||Is there sufficient memory available?|
|2||Have you exceeded DS_MAX_SCANS?|
|3||This gate is a queue for all queries with the same priority.|
|4||Have you exceeded DS_MAX_QUERIES?|
|5||Check to make sure that you are not dynamically changing resources before allowing the query to run.|
PDQ queries are allocated memory in units called quanta. The number of
quanta available to a query is determined by the percentage of PDQ
resources available to that query. The
onstat -g mgm output displays the size of
one quantum. The size of one quantum is calculated as shown in Listing 28.
Listing 28. Calculating quantum
memory quantum = DS_TOTAL_MEMORY/DS_MAX_QUERIES
As an example, going back to the sample output from Listing 27, Listing 29 shows how to plug in the numbers.
Listing 29. Quantum calculation example
memory quantum = 512/4
The sample run generates a quantum of 128000 bytes.
As the math shows, the more queries you allow, the smaller a quantum will be.