The DB2 UDB memory model

Learn how DB2 UDB for Linux, UNIX and Windows uses memory, how that memory use varies by platform, and how it affects your applications.

This Content Component encountered an error

This article courtesy of IBM DeveloperWorks.

Introduction

Understanding how DB2 uses memory prevents you from over-allocating memory, and helps you tune memory use for better performance.

This article teaches you the basics of DB2 memory usage and the concepts of shared memory and private memory. These apply to both 32-bit and 64-bit systems. While there are restrictions to 64-bit systems, we will most likely not reach these limits for some time to come. Therefore we will focus on and discuss in detail the memory limitations affecting 32-bit systems.

We will discuss how DB2 uses memory in general, then move on to discuss how memory management differs by platform (AIX, Sun, HP, Linux, and Windows) and how that affects DB2. Finally, we will provide meaningful real life examples of customer situations/problems and their resolution.

The materials presented in this article apply to DB2 version 8.

Overview of DB2 Memory Structure

The DB2 memory structure is illustrated in the Figure 1; it is the same across all platforms.

Note: In a multi-partition environment, the following figure applies to each partition in the multi-partitioned instance.

Figure 1 - DB2 memory structure

DB2 breaks and manages memory in four different memory sets. They are:

  • Instance shared memory
  • Database shared memory
  • Application group shared memory
  • Agent private memory

Each memory set consists of various memory pools (also referred to as heaps). The names of the memory pools are also given in Figure 1. For example, the locklist is a memory pool that belongs to the database shared memory set. The sortheap is a memory pool that belongs to the agent private memory set.

We will discuss each of these memory sets in detail.

Instance shared memory
There is one instance shared memory set per DB2 instance. Instance shared memory is allocated when the database manager is started (db2start), and freed when the database manager is stopped (db2stop). It is used for instance level tasks such as monitoring, auditing and inter-node communication. The following database manager configuration (dbm cfg) parameters control the limits to the instance shared memory and its individual memory pools:

  • Instance memory (instance_memory)
  • Monitor heap (mon_heap_sz): for monitoring use.
  • Audit Buffer (audit_buf_sz): for use of the db2audit facility.
  • Fast Communication buffers (fcm_num_buffers): for inter-node communication between partitions. Partitioned instance only.

The instance_memory parameter specifies the amount of memory reserved for instance management. The default value is AUTOMATIC. This means DB2 will calculate the amount of instance memory needed for the current configuration, based on the sizes of the monitor heap, audit buffer and the FCM buffers. In addition, DB2 will also allocate some additional memory for overflow buffer. The overflow buffer is used to satisfy peak memory requirements for any heap in the instance shared memory region whenever a heap exceeds its configured size. In this case, the settings for the individual heaps are soft limits; they can grow during memory usage peaks.

If instance_memory is set to a number, then the larger of instance_memory or the sum of mon_heap_sz, audit_buf_sz and fcm_num_buffers will be used. In this case, you are setting a hard limit to the instance memory, as opposed to a soft limit. When this limit is reached, you will get memory allocation errors. For this reason, it is recommended to leave the instance_memory setting as AUTOMATIC.

If instance_memory is set to AUTOMATIC, it is possible to determine its value using the following commands:

  • db2 attach to instance_name (where instance_name is the name of the instance)
  • db2 get dbm cfg show detail

The following output indicates 42 MB of memory is reserved for the instance shared memory set (10313 pages * 4096 bytes per page):

  • Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(10313) AUTOMATIC(10313)

The instance_memory parameter only sets the limit for the instance shared memory. It does not tell you how much memory is currently being used. To find out the memory usage of an instance, use the DB2 memory tracker tool, db2mtrk. For example,

  • db2start
  • db2mtrk -i -v
  • Memory for instance
  • FCMBP Heap is of size 17432576 bytes
  • Database Monitor Heap is of size 180224 bytes
  • Other Memory is of size 3686400 bytes
  • Total: 21299200 bytes

The above example showed that although 42 MB is memory is reserved for the instance shared memory set, only about 21 MB is being used at the time db2mtrk is run.

Note: In some cases the size displayed by the db2mtrk tool will be larger than the value assigned to the configuration parameter. In such cases, the value assigned to the configuration parameter is used as a soft limit, and the pool's actual memory usage might grow beyond the configured size.

Database shared memory
There is one database shared memory set per database. Database shared memory is allocated when a database is activated or connected to for the first time. It is freed when database is deactivated (if it was activated) or the last connection disconnects. This memory is used by database level tasks such as backup/restore, locking, and SQL executions.

Figure 2 shows the various memory pools within the database shared memory set. The configuration parameters that control the sizes of these memory pools are shown in parenthesis.

Figure 2 - The DB2 database shared memory

The full green boxes mean the memory pools are allocated in full when the database is started. Otherwise, only partial amount of memory is allocated. For example, when a database is first started, only about 16 KB of memory is allocated to the utility heap, regardless of the value of util_heap_sz. When a database utility is started, such as backup, restore, export, import and load, then the full amount specified by util_heap_sz is allocated.

The main buffer pools

The database buffer pool(s) area is normally the largest component of the database shared memory. This is where all regular and index data is manipulated by DB2. A database must have at least one buffer pool, and can have a number of buffer pools depending on the workload characteristics, database page sizes used in the database, etc. For example, a table space with an 8KB page size can only use a buffer pool with page size of 8KB.

The size of a buffer pool can be "extended" by using the EXTENDED STORAGE option in the CREATE BUFFERPOOL statement. The extended storage (ESTORE) acts as a secondary cache for the pages that are being evicted from the buffer pool, therefore reducing the need for I/O. The size of the ESTORE is controlled by the num_estore_segs and estore_seg_sz database configuration parameters. When ESTORE is present, a certain amount of memory is taken away from the database shared memory for managing the ESTORE, which means less memory for the other memory pools.

Now you may ask why bother using ESTORE? Why not allocate a bigger buffer pool instead? The answer to this question has to do with the limitation to the addressable memory (as opposed to physical memory), which we will discuss later.

The hidden buffer pools

Four small buffer pools of page size 4K, 8K, 16K and 32K are allocated when a database is started. They are "hidden" because you won't see them in the system catalogs (SELECT * FROM SYSCAT.BUFFERPOOLS will not show them.)

When the main buffer pools are configured too large, it is possible that they will not fit into the addressable memory space. (We will talk about addressable memory later.) That means DB2 cannot start the database, because a database must have at least one buffer pool. If the database is not started, you cannot connect to the database and change the buffer pool sizes. For this reason, DB2 pre-allocates these four small buffer pools. Should the main buffer pools fail to start, DB2 will start the database with the small buffer pools. (In this situation, a warning will be returned to the user (SQLSTATE 01626). When this happens, you should connect to the database and reduce the size of the main buffer pools.

The Sort Heap Threshold (sheapthres, sheapthres_shr)

Sorting is required when no index satisfies the requested ordering of fetched rows, or the optimizer determines that a sort is less expensive than an index scan. There are two kinds of sorts in DB2, private sorts and shared sorts. Private sorts take place in an agent's private agent memory (which we will discuss in the next section); shared shorts take place in the database's database shared memory.

For private sorts, the database manager configuration parameter sheapthres specifies an instance-wide soft limit on the total amount of memory that can be consumed by private sorts at any given time. When the total private-sort memory consumption for an instance reaches this limit, the memory allocated for additional incoming private-sort requests will be considerably reduced. You will see the following message in the db2diag.log:

"Not enough memory available for a (private) sort heap of size size of sortheap. Trying smaller size..."

If you enable intra-partition parallelism or concentrator, DB2 may choose to perform a shared sort if it determines that method to be more efficient than a private sort. If a shared sort is performed, the sort heap for the sort is allocated in database shared memory. The maximum amount of memory used for shared sorts is controlled by the sheapthres_shr database parameter. This is a database-wide hard limit on the total amount of memory consumed by shared sorts at any given time. When this limit is reached, the application requesting the sort will receive the error SQL0955 (rc2). No further shared-sort memory requests will be allowed until the total shared-sort memory consumption falls below the limit specified by sheapthres_shr.

The following formula calculates approximately how much memory the database shared memory set requires: Database shared memory = (Main bufferpools + 4 hidden bufferpools + database heap + utility heap + locklist + package cache + catalog cache) + (number of estore pages * 100 bytes) + approx. 10% overhead

For databases with intra_parallel enabled or concentrator enable, shared sort memory must be pre-allocated as part of the database shared memory, thus the formula becomes: Database shared memory = (Main bufferpools + 4 hidden bufferpools + database heap + utility heap + locklist + package cache + catalog cache + sheapthres_shr) + (number of estore pages * 100 bytes) + approx. 10% overhead

Tip: To find out how much memory is allocated to main buffer pools, issue:
SELECT * FROM SYSCAT.BUFFERPOOLS

While the sizes of most of the memory pools are pre-determined by their configuration settings, the sizes for the following two memory pools are dynamic by default:

  • Package Cache: pckcachesz = maxappls * 8
  • Catalog Cache: catalogcache_sz = maxappls * 4
  • Maximum number of active applications: maxappls = AUTOMATIC

Setting maxappls to AUTOMATIC has the effect of allowing any number of connected applications. DB2 will dynamically allocate the resources it needs to support new applications. Therefore, the sizes for package cache and catalog can vary depending on the value of maxappls.

In addition to the above parameters, there is another parameter which also affects the amount of database shared memory. It is the database_memory parameter. The default value for this parameter is AUTOMATIC. This means DB2 will calculate the amount of database memory needed for the current configuration, based on the sizes of the various memory pools listed above. In addition, DB2 will also allocate some additional memory for overflow buffer. The overflow buffer is used to satisfy peak memory requirements for any heap in the database shared memory region whenever a heap exceeds its configured size.

If database_memory is set to a number, then the larger of database_memory or the sum of the individual memory pools is used.

If database_memory is set to AUTOMATIC, use the following commands to display its value:

  • db2 connect to dbname user userid using pwd
  • db2 get db cfg for dbname show detail

Use the db2mtrk tool to display the amount of memory that is currently being used: db2mtrk -i -d -v (On Windows, -i must be specified. On UNIX, -i is optional.)

 

Memory for database: SAMPLE
    Backup/Restore/Util Heap is of size 16384 bytes
    Package Cache is of size 81920 bytes
    Catalog Cache Heap is of size 65536 bytes
    Buffer Pool Heap is of size 4341760 bytes
    Buffer Pool Heap is of size 655360 bytes
    Buffer Pool Heap is of size 393216 bytes
    Buffer Pool Heap is of size 262144 bytes
    Buffer Pool Heap is of size 196608 bytes
    Lock Manager Heap is of size 491520 bytes
    Database Heap is of size 3637248 bytes
    Other Memory is of size 16384 bytes
    Application Control Heap is of size 327680 bytes
     Application Group Shared Heap is of size 57344000 bytes
    Total: 67829760 bytes

Application group shared memory

This shared memory set applies only to the following environments. (For other environments, this memory set does not exist.)

  • Multi-partitioned databases
  • Non-partitioned databases for which intra-parallel processing is enabled
  • Databases for which the connection concentrator is enabled.

Note: Connection concentrator is enabled when max_connections is greater than max_coordagents. These parameters are found in database manager configuration. (Use GET DBM CFG to display the database manager configuration.)

In the above environments, an application usually requires more than one agent to perform its tasks. It is desired to allow these agents to communicate (send/receive data to/from each other) with each other. In order to achieve this, we put these agents into a group called an application group. All the DB2 agents belonging to the same application group communicate using the application group shared memory.

The application group memory set is allocated from the database shared memory set. Its size is determined by the appgroup_mem_sz database configuration parameter.

Multiple applications can be assigned to the same application group. The number of applications that can fit into an application group is calculated by:appgroup_mem_sz / app_ctl_heap_sz

Within the application group, each application has its own application control heap. In addition, a portion of the application group shared memory is reserved for the application group shared heap. This is shown in the following figure:

Figure 3 - The DB2 application group shared memory

Example 1
Let's consider the following database configuration:

  • Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
  • Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 512
  • Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70

The following can be calculated:

  • The application group shared memory set is: 40000 pages * 4K per page = 160 MB
  • The size of the application group shared heap is: 40000*70%= 28000 4K pages = 114MB
  • The number of applications that can fit into this application group is: 40000/512 = 78
  • The application control heap for each of the application is:(100-70)% * 512 = 153 4K pages = 0.6MB

Do not be fooled by the app_ctrl_heap_sz parameter. This parameter is NOT the size of the individual application control heap of each application in an application group. It is only a value used in calculating how many applications can fit into this application group. The real application control heap size of each application is calculated by the formula given in Figure 3, which is ((100 - groupheap_ratio)% * app_ctrl_heap_sz).

Therefore, the higher the groupheap_ratio, the bigger the application group shared heap, thus the smaller the application control heap for each application.

Example 2
Suppose during the busiest time of the day, there are 200 applications connecting to the database described in Example 1. Since each application group can hold 78 applications, we need 200/78=3 application groups to hold all 200 applications. Make sure that your system has enough RAM to support this configuration. Otherwise SQL10003N will occur.

Agent private memory
Each DB2 agent process needs to acquire memory to perform work. It will use memory to optimize, build and execute access plans on behalf of the application, to perform sorts, to record cursor information such as location and state, to gather statistics, etc. Agent private memory is allocated for a DB2 agent when the agent is assigned as the result of a connect request or a new SQL request in a parallel environment.

The number of agents is limited by the lower of:

  • The total of the maxappls database configuration parameter for all active databases, which specifies the maximum number of active applications permitted.
  • The value of the maxagents database manager configuration parameter, which specifies the maximum number of agents permitted.

The agent private memory set consist of the following memory pools. Their sizes are specified in the database configuration parameters enclosed in parenthesis:

  • Application Heap (applheapsz)
  • Sort Heap (sortheap)
  • Statement Heap (stmtheap)
  • Statistics Heap (stat_heap_sz)
  • Query Heap (query_heap_sz)
  • Java Interpreter Heap (java_heap_sz)
  • Agent Stack Size (agent_stack_sz) (Windows only)

We have mentioned that private memory is allocated to a DB2 agent when the agent is "assigned" to perform work. When does it get released? The answer depends on the value of the dbm cfg parameter, num_poolagents. The value of this parameter specifies the maximum number of idle agents to keep at any time. If the value is 0, then no idle agents are allowed. As soon as an agent completes its work, it is destroyed and its memory is returned to the operating system. If the parameter is set to a non-zero value, then an agent is not destroyed after it completes its work. Instead, it is returned to the pool of idle agents, up to a maximum of num_poolagents. When a new request comes in, these idle agents are called to service the new request. This minimizes the overhead of creating and destructing agents.

When an agent becomes idle, it retains its agent private memory. This is designed to improve performance, because the agent will have its private memory ready when it is called again. If there are many idle agents and all of them retain their private memory, it is possible that the system runs out of memory. To avoid this, DB2 has a registry variable which limits the amount of memory each idle agent can retain. This variable is called DB2MEMMAXFREE. Its default value is 8 388 608 bytes. That means each idle agent can retain up to 8MB of its private memory. If you have 100 idle agents, then 800MB of memory is retained by these agents, so they eat up RAM quite quickly. You may want to lower or increase this limit, depending on the size of your RAM.

Figure 1 showed the DB2 memory structure for one DB2 instance. Figure 4 shows how two instances running concurrently on the same system. Virtual memory includes physical RAM and paging space. Shared memories "tend to" stay in RAM because they are accessed more often. Agent private memory may be paged out if the agent has been idle for a long time.

Figure 4 - Two DB2 instances running concurrently

 

Shared memory versus Private memory


So far we have discussed the instance shared memory, the database shared memory, the application group shared memory and the agent private memory. But what does shared memory and private memory mean?

To understand the difference between shared and private memory, let's first learn about the DB2 agent processes by quickly reviewing the DB2 process model. In DB2, all database requests are serviced by DB2 agents or subagents. For example, when an application connects to a database, a DB2 agent is assigned to it. When the application issues any database requests, such as a SQL query, the agent goes out and performs all the tasks that are required to complete the query - It works on behalf of the application. (If the database is partitioned or intra-parallel enabled, then more than one agent are assigned to work on behave of the application. These agents are called subagents.)

Each agent, or subagent, is considered a DB2 process, and it acquires a certain amount of memory to perform work. This memory is referred to as the agent private memory - It cannot be shared with any other agents. As we have mentioned earlier, the agent private memory includes several memory pools such as the application heap size, sort heap size and statement heap size. (See Figure 1)

In addition to its own private memory, in which the agent performs its "private" tasks such as private sorts, using the sortheap; the agent also requires database level resources such as the buffer pools, the locklist and the log buffers. These resources are found within the database shared memory (See Figure 1). The way DB2 works is that everything within the database shared memory is shared by all DB2 agents or subagents connected to the same database. Therefore this memory set is called shared memory, as opposed to private memory. For example, agent x connecting to database A uses the resources within the database shared memory of database A. Now a second agent, Agent y, also connects to database A. Agent y will share the database memory of database A with agent x. (Of course, both agent x and y have their own agent private memory, which is not shared.)

Same logic applies to the instance shared memory and the application group shared memory.

The following figure shows the DB2 memory sets allocated when two DB2 agents, agents x and y connect to database A. Assuming:

  • Database A belongs to instance db2inst1;
  • Database A is intra-parallel enabled with application group 1
  • Both agent x and agent y belong to application group 1.

Figure 5 - The DB2 agent process memory address space

Figure 5 shows the following memory sets are allocated in RAM:

  • An instance shared memory set for instance db2inst1;
  • A database shared memory set for database A;
  • An application group shared memory for application group 1;
  • An agent private memory set for agent x;
  • An agent private memory set for agent y;
  • Reserved memory for things like the kernel and libraries.

Both agent x and y shared the same instance memory, database memory and application group memory, since they belong to the same instance, same database and same application group. In addition, they have their own agent private memory.

Each DB2 agent process has its own memory address space. The memory addresses in the address space allow the agent to access the memories in the physical RAM. We can think of these addresses as pointers to the RAM, as shown in Figure 5. For any DB2 process, all the four memory sets must fit into this address space.

It is mentioned earlier that ESTORE is used to extend the size of a buffer pool. Why not create a bigger buffer pool instead, you may ask. The answer is: Because the address space is limited, a large buffer pool may not fit into the address space! In that case, you need to define a smaller buffer pool which fits into the address space. And if you have excess physical memory, you can use it to configure ESTORE.

Well then, how do we know how big a DB2 agent's address space is? The size of the address space depends on whether you have a 32-bit instance or a 64-bit instance. We explain in the next section.

Addressable memory in a 32-bit architecture versus 64-bit architecture
If you have a 64-bit DB2 instance, that means DB2 is using the 64-bit memory architecture. With this architecture, the address space of each process is 2 to the power of 64, or 18,446,744,073 GB, on all platforms. This is a huge amount of memory. You should have no problem fitting all the DB2 memory sets into this address space.

On the other hand, if you have a 32-bit DB2 instance, the address space is only 2 to the power of 32, or 4 GB, on all platforms (except on Linux/390, the address space is actually 2 to the power of 31. However, we will not be discussing DB2 on Linux/390 in this article.) So regardless how big your physical RAM is, for a DB2 process to be able to access all the resources it needs, the instance shared memory, database shared memory, application group shared memory, its own agent private memory plus memory for the kernel, etc, all have to fit into this 4GB address space.

This lead to two very important questions:

  • How much memory should we allocate for instance memory, database memory and application shared memory, so they can fit into the 4GB addressable space?
  • How should we configure each of the parameters listed in Figure 1 to make best use of the memory that is available?

While the limit of 4 GB of address space applies to all platforms, the answers to the above questions are platform-dependent. For example, the maximum amount of database memory you can allocate to a DB2 database on an AIX system is different from a database on a Solaris system. The following sections discuss how different platforms affect memory configuration in DB2.

Note: The rest of the article will dedicate to 32-bit memory architecture only. The issues that we will discuss do not apply to the 64-bit architecture.

DB2 memory configuration on 32-bit AIX
On 32-bit AIX, the 4GB of addressable memory space is broken down to 16 segments, 256MB each. Figure 6 shows the 32-bit memory address space for a DB2 agent process. (Assume both DB2 registry variables, DB2_MMAP_READ and DB2_MMA_WRITE, are set to NO. If not, the representation would be a little different. We will explain.)

Figure 6 - DB2 32-bit memory address space on AIX

Segment 0 - Reserved for AIX kernel

Segment 1 - Reserved for db2sysc process

Segment 2 - Reserved for agent private memory.

Segment 3 - Reserved for instance shared memory.

Segments 4 to B -The database shared memory starts at segment 4 and the segments must be attached contiguously. Potentially, all 8 segments (2 GB) can be used for database shared memory. However, each of the following configurations takes one segment (256MB) away from the database shared memory.

Note: DB2 will take away one segment from the database shared memory for each of the following configurations, starting from segment B.

  • If the database is partitioned, or intra-parallel enabled, or connection concentrator enabled, then one segment is used for Application Group Shared Memory.
  • Fast Communication Manager (FCM): FCM is used for communications between partitions that reside on the same physical node. By default, this is done via UNIX sockets. If DB2_FORCE_FCM_BP is set to YES, then the FCM communication occurs within the shared memory. This means one segment is used for FCM communication. Though FCM communication becomes faster, it reduces the database shared memory by one segment.
  • Fenced UDFs and Stored Procedures: If a fenced function or procedure is run on the database, then one segment is used for fenced mode communication.
  • A segment is used to handle communications between the agent and the application whom the agent is working for, if the application is local. By default, this communications segment attaches to segment B of the agent's address space. If DB2_MMAP_READ and DB2_MMAP_WRITE are both set to NO, then it attaches to segment E, and segment B can be used for something else, possibly database shared memory.
  • If ESTORE is enabled, then it will take another segment away from the database shared memory. Therefore, if you enable ESTORE, make sure it is at least 256MB or else it would not make sense, because a segment of 256MB from the database shared memory is taken for just managing the ESTORE. It is recommended to set the size of estore segment (estore_seg_sz) to 256MB, and then change the number of segments (num_estore_segs) according to the available memory.

Segment C - Reserved for DB2 trace facility

Segment D and F- Reserved for DB2 shared libraries

Segment E - By default, this segment is not used. However, if you set DB2_MMAP_READ=NO and DB2_MMAP_WRITE=NO, then this segment is used for communication between the DB2 agent and the application whom the agent is working for (as shown in Figure 6). This effectively frees up one segment for database shared memory.

Note: To maximize the space available for database shared memory, use the following registry variable settings: DB2_FORCE_FCM_BP=NO (This is the default), DB2_MMAP_READ=NO, DB2_MMAP_WRITE=NO.

The most important things to learn from this structure are:

  • For a single partition system with intra-parallel disabled, we can get up to 2GB for database shared memory (Segments 4 to B), with DB2_MMAP_WRITE=NO and DB2_MMAP_WRITE=NO. Otherwise, 1.75GB.
  • Each of the following configurations reduces the database shared memory by one segment (256MB): database with fenced UDFs or store procedures, database with DB2_FORCE_FCM_BP=YES, database with intra_parallel enabled, or concentrator enabled, or partitioned, and database with ESTORE enabled.

These limits dictate how we configure each of the memory pools in the database shared memory set. Use the formula given earlier to calculate the database shared memory. The sum cannot exceed the limit.

Note: Memory can be allocated, freed, and exchanged between different areas while the database is running. For example, you can decrease the locklist and then increase any given bufferpool by the same amount.

Using svmon to monitor memory usage on AIX
Besides the db2mtrk tool, on AIX, you can use the svmon tool to monitor the memory consumption by a DB2 agent process (root autority is required). The command is "svmon -P PID" where PID is the process ID of any DB2 agent (db2agent or db2agentp).

Figure 7 shows a sample output of svmon on a process called db2agent. The database that is associated with this db2agent process has the following characteristics:

  • The name of the database is TEST.
  • INTRAP_PARALLEL = YES (get dbm cfg)
  • DB2_FORCE_FCM_BP = YES (db2set -all)
  • DB2_MMAP_READ=NO, DB2_MMAP_WRITE=NO (db2set -all)

Figure 7 - svmon output of a DB2 agent process (svmon -P 11649046)

Some observations from figure 7:

  • The process ID is 11649046, the process name is db2agent (top left corner). This agent is connected to database TEST (The name of the database has been truncated in the svmon output, only 'TES' is shown).
  • The Esid column displays the memory segments that have been allocated:
  • Segment 4 (green) is allocated to database shared memory;
  • Segment 2 (orange) is allocated to agent private memory;
  • Segment 3 (blue) is allocated to instance shared memory;
  • Segment B (purple) is allocated to application group shared memory, because the database is intra_parallel enabled.
  • Segment A (pink) is allocated to FCM, because DB2_FORCE_FCM_BP=YES.
  • The local connection segment has been moved to segment E (red). This is because both DB2_MMAP_READ and DB2_MMAP_WRITE are set to NO. Otherwise, segment 8 will have to be used for agent and local connection communication, since both segments a and b have already been taken and segment 9 is reserved for fenced mode communications.

These observations match what we have illustrated in Figure 6.

Setting data and stack ulimit on 32-bit AIX systems
We said earlier that segment #2 in Figure 6 is used for agent private memory. Actually, this is not entirely true.

To be exact, segment #2 is reserved for data and stack. Data contains user data (which is the agent private memory). Stack contains instructions to be executed. Within this 256M segment, data grows from 0x20000000 downwards. Stack grows from 0x2FFFFFFF upwards. It is important to set their limits so they do not collide with each other. If they do, then instance will crash with signal 4 or signal 11.

Figure 8 - Data and stack segment

The data and stack limits are set in the ulimits (/etc/security/limits). Use SMIT to set them to the following values:
Data = 491519, Stack = 32767 (512 bytes)

The above values are in units of 512 bytes, which equal to 240MB for data and 16MB for stack. When displaying the limits using "ulimit -a", the values are displayed in units of 1K bytes instead of 512 bytes. Therefore the values become:
Data = 245760, Stack = 16384

Note: /etc/security/limits contains the limits in 512 bytes, not 1 KB.

Common problems with allocating database shared memory on 32-bit AIX
Failure to initialize the database shared memory properly results in the following problems:

  • At database start up time (activate database or database is being connected to for the first time) - SQL1478W, SQL0987C, SQL1084C
  • At runtime - SQL10003N, SQL1042C

The following examples show improper configurations which lead to problems.

Example 1

Consider the following configuration: (All pages are size of 4KB)

  • Single partition, concentrator disabled, INTRA_PARALLEL OFF, DB2_MMAP_READ=NO, DB2_MMAP_WRITE=NO, no fenced functions or procedures
  • IBMDEFAULTBP 450,000 pages
  • UTILHEAP 17,500 pages
  • DBHEAP 10,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages

Limit: In this configuration, the limit for database shared memory is 2GB, or 8 segments.

Calculation: Use the formula to calculate database shared memory, we get:
Database shared memory = (Total of 486,000 pages x 4KB per page) x 1.1 (account for 10% overhead) = ~2.1GB = 9 segments

Note: We will leave the four hidden buffer pools out of the calculation because they are too small to make any significant difference.

Problem: This exceeds the limit of 2GB. When activating the database or connect to database for the first time, you will get the following warning:
SQL1478W The defined buffer pools could not be started. Instead, one small buffer pool for each page size supported by DB2 has been started. SQLSTATE=01626

In db2diag.log, you will see message saying DB2 will start with hidden buffer pools. To resolve this problem, reduce the size of main buffer pools.

Example 2

Consider the following configuration: (All pages are size of 4K)

  • Single partition, No fenced functions or procedures, INTRA_PARALLEL=ON
  • IBMDEFAULTBP 300,000 pages
  • UTILHEAP 17,500 pages
  • DBHEAP 10,000 pages
  • SHEAPTHRES_SHR 50,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages

Limit: 1.5GB (6 segments). One segment is used for application group memory because INTRA_PARALLEL is ON. Another segment is used for local connections because DB2_MMAP_READ and DB2_MMAP_WRITE are set to YES (This is the default).

Calculation:
Database shared memory = (Total of 386,000 pages x 4KB per page) x 1.1 = ~1.67GB = 7 segments

Problem: This exceeds the limit of 1.5GB. You will get the following error message when trying to activate or connect to the database for the first time:
SQL1042C An unexpected system error occurred. SQLSTATE=58004

To resolve this problem:

  • Set DB2_MMAP_READ and DB2_MMAP_WRITE to NO using db2set. This forces DB2 to use segment E for local connections, thus freeing one segment for database shared memory.

Example 3

Consider the following configuration: (All pages in 4K size)

  • IBMDEFAULTBP 250,000 pages
  • INTRA_PARALLEL=ON
  • UTILHEAP 17,500 pages
  • DBHEAP 10,000 pages
  • SHEAPTHRES_SHR 20,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • Fenced UDF exists

Limit: Each of the following takes one segment: Intra-parallel ON, local connections, and fenced UDFB. This leaves 5 segments, or 1.25G for database shared memory.

Calculation:
Database shared memory = (Total of 306,000 pages x 4KB per page) x 1.1% = ~1.35GB = 6 segments

Problem: Query referencing a fenced UDF will fail with sql10003C. To resolve the problem, run UDF in an unfenced mode or reduce database shared memory set to no more than 5 segments.

DB2 memory configuration on 32-bit Sun Solaris, Version 2.6 and above


Unlike AIX, where each segment is 256MB in size, Solaris' memory segments are not fixed in size. The 32-bit Solaris addressable memory structure is shown in Figure 9.

Figure 9 - DB2 32-bit memory address space on Sun Solaris 32-bit

The address from 0x0 to 0x00010000 is not usable. The first segment starts at 0x00010000 and it is reserved for db2sysc executables and the agent private memory. By default, this segment ends at 0x10000000, which gives this segment a total of 256MB. (We can make this segment bigger by setting the DB2DBMSADDR DB2 registry variable, see below). Within this segment, the db2sysc executables take only a small fraction, the rest is used for agent private memory (200MB+).

By default, the starting address of the instance shared memory is fixed at 0x10000000. However, it can be changed to a higher address so more room is left for agent private memory. For example, if the instance shared memory starts at 0x12000000 instead of 0x10000000, then 0x12000000 minus 0x10000000, 32MB of additional memory can be used for the agent private memory. To do so, set the DB2DBMSADDR DB2 registry variable as follows:
db2set DB2DBMSADDR=0x12000000

Note: DB2DBMSADDR value range is from 0x10000000 to 0x10FFFFFF in increments of 0x10000.

The end address of the instance shared memory segment is not fixed. That means the instance shared memory can potentially be very large. Following the instance shared memory is the database shared memory. Since the end address of the instance shared memory is not fixed, the starting address for the database shared memory is not fixed either.

Following the database shared memory are the memory segments for DB2 trace, shared libraries and stack (instructions to be executed). Unlike AIX, where stack and agent private memory share the same memory segment, the agent private memory and stack on Solaris use separate memory segments; therefore, there is not risk of them colliding.

Since the sizes of all these memory segments are not fixed, we can only estimate the size of the instance and database shared memory to be:
4GB - agent private memory - DB2 trace - DB2 shared libraries - Stack

Note this amount includes both the instance shared memory and database shared memory, and their memory segments must be contiguous.

To see how memory is actually being used, run the /usr/proc/bin/pmap command (as root) on the ID of any db2sysc or db2agent process.

Note: On Solaris, when you use the "pe -ef | grep instance_name" command to display DB2 processes, all processes appear as the db2sysc process. You can use the db2ptree command to display DB2 processes in their "real" name.

Figure 9 shows a sample output of the pmap command.

Figure 10 - Sample pmap output on a db2sysc process (/usr/proc/bin/pmap -x 15444)

Some observations from figure 10 are:

  • The section from 0x00010000 to 0x023F8000 is reserved for the db2sysc executables. (~36MB.)
  • The section in orange (heap), from 0x023F8000 to 0x10000000, is used for the agent private memory. (~220MB)
  • The instance shared memory, in green, starts at the default address 0x10000000. That means DB2DBMSADDR is not set.
  • All three green segments are used for shared memory, which includes both instance shared memory and database shared memory. The pmap output does not tell you which segment is the instance shared memory and which is the database shared memory. All we know is that the database shared memory ends at 0xFE002000, because starting at that address is a segment for anonymous memory. So the size of instance shared memory and database shared memory combined is 0xFE002000 - 0x10000000 = 3,992,985,600 bytes = ~3.7 GB.
  • From 0xFFBC0000 to 0xFFFFFFFF is the memory segment for stack (~4MB).

Note: On 32-bit Solaris, we normally limit the DB2 database shared memory to around 3.5GB.

If the DB2DBMSADDR register variable is set, then the instance shared memory will start at the address specified by this variable. The following example shows how this can be done.

Example

Set the DB2DBMSADDR registry variable:

  • db2set DB2DBMSADDR = 0x12000000
  • db2stop
  • db2start (Need to restart the instance to make the change effective.)

Get the process ID of a db2sys process

  • ps -ef | grep sylviaq ('sylviaq' is the instance name)
  • -ef | grep sylviaq
  • sylviaq 13166 1 0 13:09:12 pts/2 0:00 /export/home/sylviaq/sqllib/bin/db2bp 13049C11221 5
  • sylviaq 13263 13256 0 13:11:02 ? 0:00 db2sysc
  • sylviaq 13265 13256 0 13:11:03 ? 0:00 db2sysc
  • sylviaq 13257 13254 0 13:10:59 pts/3 0:00 -ksh
  • sylviaq 13256 13253 0 13:10:59 ? 0:00 db2sysc
  • sylviaq 13262 13256 0 13:11:00 ? 0:00 db2sysc
  • sylviaq 13360 13049 0 13:11:41 pts/2 0:00 grep sylviaq
  • sylviaq 13264 13256 0 13:11:02 ? 0:00 db2sysc
  • sylviaq 13266 13261 0 13:11:03 ? 0:00 db2sysc

As root, cd to /usr/proc/pmap and run pmap on any db2sysc process:
./pmap -x 13263

The pmap output:

  • 13263: db2sysc
  • Address Kbytes Resident Shared Private Permissions Mapped File
  • 00010000 35808 4064 1608 2456 read/exec db2sysc
  • 02316000 896 168 48 120 read/write/exec db2sysc
  • 023F6000 744 264 8 256 read/write/exec [ heap ]
  • 12000000 243472 243472 - 243472 read/write/exec/shared [shmid=0xbc3]
  • 21000000 22512 22512 - 22512 read/write/exec/shared [shmid=0xbc4]
  • FCC00000 8328 8328 - 8328 read/write/exec/shared [shmid=0xa96]
  • FE002000 8 - - - read/write/exec [ anon ]

Note that the instance shared memory now starts at 0x12000000 instead of the default address 0x10000000. The size of the agent private memory (indicated by 'heap') is increased to from 220MB (Figure 10) to 252 MB. (0x12000000 - 0x023F6000 = 0xFC0A000 = 264282112 (in decimal) = ~252MB)

You probably have noticed that the 4GB address space present in Figure 9 does not include any kernel memory. That is right, on Solaris, the kernel has its own address space and it is separate from the process' address space. This leaves more room for other memory sets such as the database shared memory.

Although we have a larger address space for database shared memory on Solaris than AIX, (2GB on AIX), all shared memory is pinned to physical RAM on Solaris. This has significant impact on the number of databases you can run concurrently if the size of RAM is small. See example 2 in the "Common problems with allocating database shared memory on Sun Solaris" section.

The most important things to learn from this structure are:

  • Unlike AIX, Solaris' memory segments are not fixed in size. The agent private memory can be increased by moving the instance shared memory to a higher address by setting the DB2DBMSADDR DB2 registry variable.
  • The limit for database shared memory is around 3.5GB.
  • Shared memory is pinned to RAM and cannot be swapped out.

Common problems with allocating database shared memory on 32-bit Sun Solaris
Kernel parameters not configured sufficiently and failure to initialize the database shared memory properly can lead to failures like the following:

  • At database start up time (activate database or database is being connected to for the first time) - SQL1478W, SQL1084C, hang condition
  • At runtime - SQL2043N, hang condition

On Solaris systems, DB2 provides a tool called db2osconf. This tool makes recommendations for kernel parameter values based on the size of a system. The recommended values are high enough for a given system that they can accommodate most reasonable workloads.

The most common kernel parameter set incorrectly is shmmax. This parameter specifies the maximum size in bytes of a shared memory segment which can be allocated on the system. If DB2 is configured to create a database shared memory set which is larger than this, the request will fail. Other kernel parameters to be aware of is shmseg and shmmni.

Another common problem with allocating database shared memory on Solaris is due to the fact that all pages of a shared memory segment are pinned in physical RAM. If there are not sufficient free pages available in RAM or other pages which can be paged out by the OS to fit the database segment, a request to start a database will fail.

The following examples show improper configurations which lead to problems.

Example 1

Consider the following configuration: (All pages in 4K size)

Server:

  • Physical RAM on server 16GB
  • shmsys:shminfo_shmmax = 2097152 (2GB)

Database:

  • IBMDEFAULTBP 400,000 pages
  • UTILHEAP 17,500 pages
  • DBHEAP 30,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages

Limit: Any request by DB2 to create a database share memory set which is larger then the shmmax value (2GB in this case) will fail with an out of memory type error.

Calculation:
Database shared memory = (456,000 pages x 4KB per page) x 1.1 = ~2.0GB

Problem: It may be possible to activate or connect to the database. However, attempt to run an application may return the following error message:
SQL1224N A database agent could not be started to service request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032

This is a rather generic error that DB2 returns. However, on UNIX servers other then AIX, it is likely pointing on a memory resource issue. Specifically, the kernel parameters are not turned properly.

To resolve this problem, configure the kernel parameters appropriately. Set:
shmsys:shminfo_shmmax = 15099494 (~90% of 16GB)

Example 2

Consider the following configuration: (All pages in 4K size)

Physical RAM on server is 1GB.

Database A:

  • IBMDEFAULTBP 137,500 pages
  • INTRA_PARALLEL ON
  • UTILHEAP 10,000 pages
  • DBHEAP 10,000 pages
  • SHEAPTHRES_SHR 20,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • APPGROUP_MEM_SZ 20,000 pages

Database B:

  • IBMDEFAULTBP 92,500 pages
  • INTRA_PARALLEL ON
  • UTILHEAP 5,000 pages
  • DBHEAP 10,000 pages
  • SHEAPTHRES_SHR 15,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • APPGROUP_MEM_SZ 20,000 pages

Limit: Because shared memory is pinned to physical RAM, it will never be swapped out. Therefore, we can only allocate up to 1GB (physical RAM available) of shared memory for database use.

Calculation:

  • Database A shared memory = (186,000 pages x 4KB per page) x 1.1% = ~818MB
  • Database A application group memory = 20,000 pages x 4KB per page = 80MB
  • Database B shared memory = (131,000 pages x 4KB per page) x 1.1% = ~576MB
  • Database B application group memory = 20,000 pages x 4KB per page = 80MB
  • To start database A, requires: 818MB + 80MB = ~898MB
  • To start database B, requires: 576MB + 80MB = ~656MB

Problem: Assume that database A is activated. At least 898MB of shared memory is pinned to physical RAM. When you attempt to activate database B, you will encounter the following error message:
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019

When both database A and database B is started simultaneously, we will require at least 1.55GB (898MB + 656MB) of physical RAM available for shared memory to pin against. Clearly, 1GB of RAM is not enough. To resolve this problem:

  • Attempt to reduce buffer pool sizes for both databases. Or
  • Attempt to reduce the application group memory. Or
  • More than likely, add more physical RAM. In this case, you will need at least 1.55GB of physical RAM to start both databases simultaneously.

In this case, it is possible to start one database (database A or database B) at any given time, but not both simultaneously, given the above database configuration parameters. More physical RAM must be added.

This problem does not occur on AIX, where shared memory is not pinned. In this scenario, database B will be able to start. But this means the database memory for database A must be paged out. When database A is accessed by applications, the database memory of database B then will have to be paged out. You can imagine the amount of paging that is going to take place.

Example 3

Consider the following configuration: (All pages are size of 4K)

Server:

  • Physical RAM on server 16GB
  • shmsys:shminfo_shmmax = 15099494 (~90% of 16GB)

Database:

  • IBMDEFAULTBP 350,000 pages
  • UTILHEAP 17,500 pages
  • DBHEAP 10,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • ESTORE_SEG_SZ = 400000 pages
  • NUM_ESTORE_SEGS = 1

Calculation:

  • Database shared memory = (386,000 pages x 4KB per page + 400,000 pages of estore * 100bytes) x 1.1 = ~1.66GB
  • ESTORE memory = 400000 x 4KB = 1.6GB

Problem: The database shared memory is 1.66GB; it is less then the 3.35GB database shared memory limit. The shmmax parameter is set correctly. But it is possible to get the following error message when starting the database! You may see the following error in your db2diag.log:

  • 2003-12-04-10.10.13.362027 Instance:db2inst1 Node:000
  • PID:18327(db2agent (SAMPLE) 0) TID:1 Appid:*LOCAL.sample.047844091013
  • oper system services sqloVLMAttachVLMSegment Probe:20 Database:SAMPLE
  • sqloVLMAttachVLMSegment - shmat failed
  • 0xFFBE833C : 0x0000000C

shmat is a UNIX function that attaches the shared memory segment associated with the shared memory identified by shmid (another Solaris function) to the data segment of the calling process. shmat failed because of 0x000000C, which is ENOMEM or the available data space is not large enough to accommodate the shared memory segment.

In other words, the database failed to activate or connect because there was not enough shared memory to meet the request.

How is this so? The answer lies in the way we tried to allocate ESTORE. Each ESTORE segment must be a contiguous block. In our example, we tried to allocate one huge (contiguous) block of memory (1.6GB) for ESTORE. Even with 16GB of RAM, it could be fragmented and there is not a contiguous 1.6GB of memory.

To resolve this problem, set the following values for ESTORE in the database configuration file:

  • ESTORE_SEG_SZ = 40000 pages
  • NUM_ESTORE_SEGS = 10

By setting the above ESTORE values, we are in fact still trying to allocate a total of 1.6 GB for ESTORE. However, we will try to allocate 10 chunks of 160MB for ESTORE. This is a considerably smaller contiguous space to allocate and therefore will most likely resolve the problem.

DB2 memory configuration on 32-bit HP-UX


On 32-bit HP-UX platform, the default HP-UX memory management is based on quadrants where each process has its own space. Each process (including DB2 agents) can address up to 4GB of memory. The 4G of addressable memory is broken down into 4 quadrants, where each quadrant is 1G in size, as shown in Figure 11.

Figure 11 - DB2 32-bit memory address space on HU-UX

Quadrant 1 (1GB) is reserved for Program Text (executable code).

Quadrant 2 (1GB) is reserved for Global Program Data.

The combination of quadrant 1 and 2 (minus kernel and other process memory) can be used for private memory. Quadrant 1 and 2 exists n times (once for each process)

Quadrant 3 (1GB) is reserved for Global Shared memory.

Quadrant 4 (0.75GB) is reserved for Global Shared memory. The last 0.25GB is used for I/O mapping.

There is a 1.75GB system wide shared memory limit. That is, all shared objects (not only DB2) are mapped to quadrant 3 and 4 in one single space shared by all processes. The shared memory space is divided into 1GB in quadrant 3 and 0.75GB in quadrant 4. However, a shared memory segment cannot be "split" across quadrants and be guaranteed a contiguous address space. There are various segments allocated by DB2 depending on the process. Each segment can only map to either quadrant 3 or 4, but not both. Therefore, the largest shared memory segment, in quadrant 3, that DB2 is allowed to allocate is 1GB; and in quadrant 4 is 0.75GB. In reality, it is most likely less, since some small shared memory segment will probably already have been allocated (either by DB2 or other process). This means that the database shared memory set is really limited to approximately 0.75 to 1GB. This is much less then any database shared memory set available to other UNIX platforms.

Note: This default HP-UX memory architecture uses the SHARE_MAGIC kernel executable. Under HP-UX 10.20 kernel changes, it is possible to increase the global shared memory limit from 1.75G to 2.75GB, by compiling the application to use the SHMEM_MAGIC kernel executable. However, there are no plans to compile the DB2 code with the SHMEM_MAGIC executable yet.

With HP-UX 11.0 or later release, there is a work-around to the 1.75GB shared memory limitation in the form of Memory Windows. Memory Windows allow each process to define a unique global space of up to 1GB of shared memory. Shared memory segments placed in the unique space can only be accessed by processes within the same memory window. But different applications, or distinct instances of a single application, can be placed in different memory windows and consume more of the available physical memory on the system.

Memory Windows enable quadrant 3 to be a private shared memory space for a process group as defined in /etc/services.window. In the DB2 implementation of Memory Windows, each instance is mapped to an individual memory window. In other words, each DB2 instance can have its own quadrant 3 memory space. Quadrant 4 will still be used as a global shared memory area, where any shared process can allocate segments. Note that shared libraries will always be mapped to quadrant 4 first, if available. The shared memory available per instance, then becomes, 1GB plus whatever space is still available in quadrant 4. However, shared memory segments still can not cross quadrant boundaries. Depending on the type and size of the segment, DB2 can specify to create it in quadrant 4. The database shared memory set is still limited to approximately 1GB. The advantage of Memory Windows then allows us to allocate a full quadrant (quadrant 3, approximately 1GB) to a DB2 instance. Whereas, without Memory Windows, all instances must share the 1.75GB system wide shared memory limit.

Tip: If you create one database per instance, you can effectively allocate up to 1GB of database shared memory for all your databases.

Note: Memory Windows extends system wide virtual capacity for 32-bit applications only. By extending the virtual capacity, more of the underlying RAM can by used. Without memory windows, only 1.75GB of physical memory will be consumed, regardless of the size of RAM.

For more information on how to turn on Memory Window for DB2, refer to this link.

The most important things to learn from this structure are:

  • HP-UX default memory architecture only allows a maximum of 1.75GB of shared memory for ALL processes, not just DB2. It is only with Memory Windows enabled that HP-UX can allocate shared memory at a per instance level.
  • Shared memory segments are required to be contiguous, and therefore can not be split across quadrant boundaries. This effectively limits the database shared memory set to 1GB.
  • Memory Windows can be used to provide each DB2 instance an effective 1GB private database shared memory space, plus any space available from the global shared memory quadrant (4).
  • If one database is created per instance, it is possible to allocate up to 1GB for shared memory usage for that database.

Common problems with allocating database shared memory on 32-bit HP-UX
Kernel parameters not configured sufficiently and failure to initialize the database shared memory properly can lead to failures like the following:

  • At database manager start up time - SQL1220N
  • At database start up time (activate database or database is being connected to for the first time) - SQL1478W, SQL1084C, SQL3605C, hang condition
  • At runtime - SQL2043N, hang condition

The most common kernel parameter set incorrectly is shmmax. Please refer to the Quick Beginnings for proper value to set depending on your physical RAM. The SHMMAX specifies the maximum size in bytes of a shared memory segment which can be allocated on the system. If UDB is configured to create a database shared memory set which is larger than this, the request will fail. Other kernel parameter to be aware of is shmmni. Click here for information on modifying kernel parameters.

The following examples show improper configurations which lead to problems.

Example 1

Consider the following configuration: (All pages in 4K size)

Server:

  • Physical RAM on server 1GB
  • shmmax = 966 367 641 (1GB)
  • shmseg = 32

Database:

  • IBMDEFAULTBP 100,000 pages
  • UTILHEAP 5000 pages
  • DBHEAP 10,000
  • LOCKLIST 17,500 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages

Calculation:
Database shared memory = (140,000 pages x 4KB per page) x 1.1% = ~616MB

Problem: It may be possible to activate or connect to the database. However, the following message occurs intermittently in the db2diag.log when trying to connect to the database.
DIA3605C Memory allocation failure occurred.

Note that the database shared memory calculations show that we are still within available 1GB shared memory limit. We should then turn to the kernel parameter settings. Check the "Quick Beginnings for DB2 Servers" for recommendations on tuning kernel parameters on 32-bit HP-UX platforms. When you check, you will actually find that the shmseg parameter is not documented in the book. That means you should use the default settings. The default setting for shmseg is 120. (Because SHMSEG kernel parameter is set too low, some shared memory segments could not be allocated properly.)

To resolve this problem, configure the kernel parameters appropriately.

  • In this case, set shmseg = 120

Example 2

Consider the following configuration: (All pages in 4KB size)

Server:

  • Physical RAM on server 1GB
  • shmmax = 966 367 641 (1GB)
  • shmseg = 120
  • SWAP space on server 500MB

Database A:

  • IBMDEFAULTBP 100,000 pages
  • UTILHEAP 2500 pages
  • DBHEAP 10,000
  • LOCKLIST 20,000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages

Database B:

  • IBMDEFAULTBP 160,000 pages
  • UTILHEAP 7,500 pages
  • DBHEAP 20,000 pages
  • LOCKLIST 10,000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages

Limit: Because the physical RAM is only 1GB, the database shared memory set can only map to what is physically available to it, which is 1GB + swap space.

Calculation:

  • Database A shared memory = (140,000 pages x 4KB per page) x 1.1% = ~616MB
  • Database B shared memory = (205,000 pages x 4KB per page) x 1.1% = ~902MB

Problem: It is possible to activate or connect to Database A or Database B separately, but not concurrently. An attempt to activate both databases concurrently produces the following error message:
SQL1478W The defined buffer pools could not be started. Instead, one small buffer pool for each page size supported by DB2 has been started. SQLSTATE=01626

When both database A and database B is started simultaneously, we will require at least 1.52GB (616MB + 902MB) of shared memory. It should be possible to map Database A to quadrant 4 (~0.75GB shared memory available) and Database B to quadrant 3 (~1GB). However, this time, we are limited to physical memory. Clearly, 1GB of RAM is not enough to handle 1.52GB of shared memory mapping. In addition, the SWAP space is set much to low. To resolve this problem:

  • Attempt to reduce buffer pool sizes for both databases. Or
  • Attempt to increase the swap space to 2x the physical RAM. Or
  • Best solution, add more physical RAM. In this case, you will need at least 2GB of physical RAM to start both databases simultaneously.

Example 3

Consider the following configuration: (All pages in 4K size)

Server:

  • Physical RAM on server 6GB
  • Instance 1 (3 databases)

Database A:

  • IBMDEFAULTBP 140,000 pages
  • UTILHEAP 7,500 pages
  • DBHEAP 20,000 pages
  • LOCKLIST 10,000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • APPGROUP_MEM_SZ 20,000 pages

Database B:

  • IBMDEFAULTBP 80,000 pages
  • UTILHEAP 2500 pages
  • DBHEAP 10,000 pages
  • LOCKLIST 20,000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • APPGROUP_MEM_SZ 20,000 pages

Database C:

  • IBMDEFAULTBP 130,000 pages
  • UTILHEAP 7,500 pages
  • DBHEAP 20,000 pages
  • LOCKLIST 10,000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • APPGROUP_MEM_SZ 20,000 pages

Limit: Because the physical RAM is only 1GB, the database shared memory set can only map to what is physically available to it, which is 1GB + swap space.

Calculation:

  • Database A shared memory = (185,000 pages x 4KB per page) x 1.1% = ~814MB
  • Database A application group memory = 20,000 pages x 4KB per page = 80MB
  • Database B shared memory = (120,000 pages x 4KB per page) x 1.1% = ~528MB
  • Database B application group memory = 20,000 pages x 4KB per page = 80MB
  • Database C shared memory = (175,000 pages x 4KB per page) x 1.1% = ~770MB
  • Database C application group memory = 20,000 pages x 4KB per page = 80MB
  • To start database A, requires: 816MB + 80MB = ~894MB
  • To start database B, requires: 530MB + 80MB = ~608MB
  • To start database C, requires: 772MB + 80MB = ~850MB

Problem: Starting Database A and Database B was successful. Starting Database C failed with the following:
SQL1478W The defined buffer pools could not be started. Instead, one small buffer pool for each page size supported by DB2 has been started. SQLSTATE=01626

Physical memory is not an issue here as we have plenty of RAM (6GB). Further testing identified the following startup combinations:

  • Start A + B -> successful
  • Start B + C -> successful
  • Start A + C -> unsuccessful
  • Start (A + B) + C -> unsuccessful
  • Start (B + C) + A -> unsuccessful

Combination (A + B) and (B + C) is successful because the total shared memory required to initialize the databases are 1.5GB (894 + 608) and 1.46GB (608+850) respectively. This is well under the 1.75GB shared memory limit, and each database shared memory segment can safely map contiguously to a quadrant. The other combinations will fail with SQL1478W because they either exceed the 1.75GB shared memory limit, or a contiguous shared memory segment could not be allocated successfully within a quadrant for a database.

To resolve this problem:

  • Implement Memory Windows. The best solution is to define 3 DB2 instances. Restore 1 database into each instance. Create a memory window for each instance. Therefore, each database will have full access to its own 1GB memory window shared memory space. Note: max_mem_windows kernel parameter must be set to 2 (number of instances -1).

DB2 memory configuration on 32-bit Linux/Intel


On 32-bit Linux/Intel, the 4GB of addressable memory is shown in Figure 12.

Figure 12 - DB2 32-bit memory address space on Linux/Intel

The segment starting from 0x08048000 to 0x0FFFFFFF is reserved for db2sysc executables.

The segment starting from 0x10000000 to 0x3FFFFFFF is the instance shared memory, for a total of 0.75GB.

By default, the DB2 shared libraries start at 0x40000000.

It is possible load the shared libraries at a lower address, leaving more room for database shared memory. (This also means less memory for instance shared memory. But since instance memory is generally small compared to database memory, this gain is beneficial.) For example, if the shared libraries are loaded at an address lower than 0x40000000, then the database shared memory can be loaded at 0x38000000. If the shared libraries are loaded at an address lower than 0x2a000000, then the database shared memory can be loaded at 0x30000000, which gives over 2GB for database shared memory. These changes requires re-compiling kernel, we will not discuss this in this article. Please refer to the kernel recompilation section of your Linux manual for more information.

However, as of v8.1 FP2, on Redhat Advanced Server and SuSE SLES 8 only, DB2 will attempt to relocate the shared libraries to a lower address automatically, which does not require a kernel re-compilation. On these enterprise distributions, there is a file called /proc/pid/mapped_base which contains the address where the shared libraries are to be loaded into memory. By default it is 0x40000000, but we can lower it (as low as 0x20000000). When db2sysc starts up, we check to see if the mapped_base file exists. If it does exist, we will use the new value and reexec. Then, every process that is spawned from the process where the mapped_base value has changed uses the new value.

The database shared memory (including buffer pools) starts at 0x50000000 (default). It grows from 0x50000000 downwards toward the Stack. Stack contains instructions to be executed. The stack grows upwards starting from 0xC0000000.

It is important that the database shared memory and stack do not collide. We recommend a large 16MB for the stack. In that case, we will have ~1.73GB for database shared memory (From 0x50000000 to 0xC0000000, minus 16MB of Stack.).

When displaying the limits using "ulimit -a" or "ulimit -s", the values are displayed in units of 1K bytes.
Stack = 16384

It is important to set this limit so the stack and shared memory address space do not collide with each other. If they do, then instance will crash with signal 4 or signal 11.

The last 1GB of memory is reserved for the Linux Kernel.

You may have noticed that there is not a segment for agent private memory. You are correct. On Linux/Intel 32-bit, there is not a specific segment reserved for private memory. The agent private memory is allocated in any free space between the shared libraries.

To see how memory is being used, see the file called /proc/PID/maps, where PID is the process ID of any db2agent process. Figure 12 shows a sample output of a maps file.

Figure 13 - The maps file of a db2sysc process

Here are some observations from figure 13:

  • The segment in blue, starting at 0x10000000, is the instance shared memory.
  • The shared libraries are loaded starting at 0x40000000. In between the shared libraries, the segments in orange are the agent private memory.
  • The database shared memory, in green, starts at 0x50000000.
  • The last segment, in purple, is the Stack.

Tip:Read this article for a good description of how to setup AWE and what it does.

The most important things to learn from this structure are:

  • By default, the database shared memory is 1.75GB minus the Stack.
  • It is possible to load the shared library segment to a lower address, leaving less room for the instance shared memory, bur more room for the database shared memory.

Common problems with allocating database shared memory on 32-bit Linux
Kernel parameters not configured sufficiently and failure to initialize the database shared memory properly can lead to failures like the following:

  • At database manager start up time - SQL1220N
  • At database start up time (activate database or database is being connected to for the first time) - SQL1478W, SQL1084C, hang condition
  • At runtime - SQL2043N, hang condition

The most common kernel parameter set incorrectly is shmmax. Please refer to the Quick Beginnings for proper value to set depending on your physical RAM. The shmmax specifies the maximum size in bytes of a shared memory segment which can be allocated on the system. If UDB is configured to create a database shared memory set which is larger than this, the request will fail. Other kernel parameter to be aware of is shmmni. Refer to this link for information on modifying kernel parameters.

The following examples show improper configurations which lead to problems.

Example 1

Server:

  • Physical RAM on server 2GB
  • kernel.shmmax = 32768(32KB)

Database:

  • IBMDEFAULTBP 200,000 pages
  • INTRA_PARALLEL ON
  • UTILHEAP 17,500 pages
  • DBHEAP 10,000 pages
  • SHEAPTHRES_SHR 50,000 pages
  • LOCKLIST 1000 pages
  • PCKCACHE 5000 pages
  • CATALOGCACHE 2500 pages
  • APPGROUP_MEM_SZ 20,000 pages (default in v8)

Limit: SHMMAX is set to 32KB. DB2START will be able to allocate enough shared memory to successfully start the instance.

Calculation:

  • Database shared memory = (286,000 pages x 4KB per page) x 1.1 = ~1.26GB
  • Database application group memory = 20,000 pages x 4KB per page = 80MB

Problem: During db2start, the following error is encountered:
SQL1220N The database manager shared memory set cannot be allocated.

To resolve this problem, increase the SHMMAX parameter to a more reasonable size, like 2GB.

Example 2

Server:

  • DB2 v8.1.0.0
  • OS: Linux SuSE SLES-8
  • glibc version : 2.2.5
  • Kernel: 2.4.19

Problem: If SHMMAX is set to exactly 2GB i.e. 2147483648, a db2start will lower it to 268435456. By reducing the value by 1 byte, no changes are made by db2start. This problem has been fixed in v8 FP2, APAR LI70159.

Problem does NOT occur with SuSe 8.0.

DB2 memory configuration on 32-bit Windows


DB2 UDB has a fundamentally different architecture on Windows. All DB2 operations are implemented as threads in one process (db2sysc.exe). The Windows process model can address up to 4GB of memory, as shown in Figure 14.

Figure 14 - DB2 32-bit memory address space on Windows

There is no real concept of shared memory sets on Windows. All shared memory pools which would normally be part of a memory set on UNIX (buffer pools, dbheap, etc.) are all allocated in the private memory set of db2sysc as required.

In a non-Advanced Server Windows environment (NT, 2000 Pro/Standard), the 4GB address space is separated into 2GB of user and 2GB of kernel space. This effectively restricts the total memory accessible by the db2sysc process to 2GB.

It is possible to reconfigure the 2GB/2GB split to 3GB of user, and 1GB of kernel space on in an Advanced Server environment, as shown in Figure 14.

In order to allow memory usage over these limits you have to use the /3GB switch in the boot.ini file. One caveat is that the /3GB switch is only supported on Windows 2000 Advanced Server, Windows 2000 DataCenter, Windows2003 Enterprise Edition and Windows 2003 DataCenter. You can set the switch in Windows2000 Pro/Standard but the user-space will only be limited to 2GB while the kernel space is shrunk to 1GB, which in turn causes 1GB of memory to be lost.

Note: To effectively use the /3GB switch, you must have a minimum of 4GB of physical RAM installed in your Windows 32-bit environment.

To overcome the 2GB (or 3GB) user space limit, large memory support on Windows 2000 Advanced Server and Windows 2003 Advanced Server lines are available. They are Address Windowing Extensions (AWE) and Physical Address Extension (PAE).

AWE is a mechanism for accessing a very large portion of memory through a potentially smaller window. In this way, a large pool of memory can be addressed as if it were linear. Essentially, AWE allows you to create buffer pools over the 2GB limit. The AWE functionality is implemented by using the /PAE switch in the boot.ini. /PAE will work on Windows2000 Pro/Standard but Microsoft will not support these OS's running with this switch if not using Windows 2000 Advanced Server, Windows 2000 DataCenter, Windows2003 Enterprise Edition and Windows 2003 DataCenter.

Tip: This article gives a good description of how to setup AWE and what it does.

The most important things to learn from this structure are:

  • There is no true concept of memory sets on Windows architecture. All memory (shared or private) pools used by DB2 are allocated from the same memory segment - the user space which is restricted to 2GB on non-Advanced Servers.
  • It is possible to increase the restriction to 3GB on Advanced Servers using /3GB switch in the boot.ini file.
  • Buffer pools can be increased over the 2GB limit by using AWE. Essentially, it is possible to define a buffer pool with a maximum size of 64GB.

Summary

DB2's memory structure consists of four memory sets: Instance shared memory, database shared memory, application group shared memory (only if database is intra-parallel enabled, or concentrator enabled or multi-partitioned) and agent private memory. Each of these memory sets consists of individual memory pools. For example, the buffer pools and the database heaps are two separate memory pools within the database memory set.

There is one instance shared memory set per instance; it is shared by all databases within this instance. There is one database shared memory set per database; it is shared by all agents connected to this database. There is one application group shared memory set per application group; it is shared by all agents belonging to that application group. There is one agent private memory set per DB2 agent (or process). This memory is for exclusive use by that agent only.

With the 32-bit memory structure, regardless of how much physical RAM you may have on the system, the addressable memory by any process on any platform is 4GB. Moreover, a portion of this 4GB is reserved by the operating system. Only whatever is left (also called user space) is free for application usage (including DB2).

When configuring DB2 memory usage, keep in mind that all the memory pools need to fit into the addressable user space. The size of this space various from platform to platform. Table 1 lists the memory limits for each of the DB2 memory sets. Application group shared memory set is not listed because it is allocated from the database shared memory set.

Table 1 - DB2 memory set limits based on 32-bit memory structure
 

Platform Instance Shared Memory Database Shared Memory Agent Private Memory
AIX 256MB 1.25 – 2 GB (1) 240MB (2)
Solaris Not fixed (3) 3.5GB ~220MB (4)
HP-UX 0.75 - 1GB (5) 0.75 – 1GB Quadrant 1 & 2 (6)
Linux 256MB 1.75 – 2.25GB 256MB
Windows n/a (7) n/a (7) n/a (7)

Notes:
(0) In a multi-partitioned environment, these limits apply to each partition.
(1) The maximum is 2GB with DB2_MMAP_READ=NO and DB2_MMAP_WRITE=NO. Each of the following takes away one 256MB segment, leading to the minimum of 1.25GB: Fenced functions and procedures; DB2_FORCE_FCM_BP=YES; intra-parallel enabled/concentrator enable/multi-partitioned.
(2) The 256MB segment is shared between Data (Agent Private Memory) and Stack. Setting the stack to 16MB in ulimit gives 240MB for Agent Private Memory.
(3) On Solaris, the instance shared memory can be potentially very large because there are no fixed addresses for shared memory segments. The instance shared memory and database shared memory combined is around 3.7GB.
(4) On Solaris, the agent private memory shares the same 256MB segment with the db2sysc executables. The executables takes about 36MB.
(5) On HP-UX, all shared memories are located in quadrant 3 (1GB) and quadrant 4 (0.75 GB).
(6) The combination of quadrant 1 and 2 (minus kernel and other process memory) can be used for private memory.
(7) All instance shared memory, database shared memory and agent private memory have to fit into the 2GB user space limit, or the 3GB limit on Advanced Server using the /3GB switch in the boot.ini file. 64GB with AWE support.

With 32-bit memory structure, no matter how big the physical RAM is, the instance, database configurations are limited by the 4GB addressable space. However, if you have sufficient RAM, you may run multiple instances or databases on the system concurrently; just that each of them has to conform to the above limits. To overcome this limitation, you should consider switching to DB2 64-bit.

Acknowledgements

Special thanks to Philip Cho and Michael Cornish for their technical review of this article.

Resources

About the authors

Sylvia Qi is a IBM Certified Database Administrator for DB2 UDB. Sylvia has worked as a DB2 Support Specialist for the past 5 years. She has indepth knowledge in DB2 engine and connectivity areas.

Michael Dang is a IBM Certified Database Administrator for DB2 UDB. Michael has worked as a DB2 Support Specialist for over 5 years, and is working as an DB2 DBA.


 

This was first published in July 2004

Dig deeper on DB2 UDB (universal databases)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close