Understanding how DB2 uses memory can prevent over-allocating memory and help tune memory usage for better performance.
This article will teach you the basics of DB2 memory usage, as well as the concept of shared memory and private memory. These contents apply to both 32-bit and 64-bit systems. Although there are some limitations on 64-bit systems, they are unlikely to reach these limits for some time to come. Therefore, we focus on the memory limitations that affect 32-bit systems and discuss them in detail.
We first discuss how DB2 uses memory in general, and then discuss how memory management varies with the platform (AIX, Sun, HP, Linux, and Windows) and their impact on DB2. Finally, we will give some meaningful examples of real-life customer situations/problems and their solutions.
The contents of this article apply to DB2 version 8.
DB2 Memory Architecture Overview
The DB2 memory structure is illustrated in Figure 1. This memory structure is consistent across all platforms.
Note: in a multi-partition environment, the following figure applies to each partition in a multi-partition instance.
Figure 1-DB2 Memory structure
DB2 splits and manages memory within 4 different memory sets. These 4 sets of memory are:
Each memory set consists of a variety of different memory pools (also known as heaps). Figure 1 also gives the name of each memory pool. For example, locklist is a pool of memory that belongs to a database shared memory set. Sortheap is a pool of memory that belongs to the proxy private memory set.
We will discuss each memory set in detail.
Instance shared memory
Each DB2 instance has an instance of shared memory. Instance shared memory is allocated when the Database Manager starts (Db2start) and is released as the database Manager stops (Db2stop). This set of memory is used for instance-level tasks such as monitoring, auditing, and inter-node communication. The following Database Manager configuration (dbm cfg) parameter controls the limit on the instance shared memory and the individual memory pools within it:
The Instance_memory parameter specifies the amount of memory reserved for instance management. The default value is AUTOMATIC. This means that DB2 calculates the amount of instance memory required for the current configuration based on the size of the monitor heap, the audit buffer, and the FCM buffer. In addition, DB2 will allocate some extra memory as an overflow buffer. Whenever a heap exceeds its configured size, an overflow buffer can be used to satisfy the peak demand for any heap in the instance shared memory area. In this case, the individual heap settings are soft-restricted, and they can grow further during peak memory usage.
If Instance_memory is set to a number, instance_memory and MON_HEAP_SZ, AUDIT_BUF_SZ, and fcm_num_ are used The larger of the buffers and between. At this point, a hard limit is imposed on the instance memory, not the soft limit. When this limit is reached, a memory allocation error is received. For this reason, it is recommended that you leave the Instance_memory setting as AUTOMATIC.
If instance_memory is set to AUTOMATIC, you can use the following command to determine its value:
The following output indicates that a memory set of 10313 MB is reserved for the instance Shared memory (page * 4096 bytes/page):
The Instance_memory parameter only sets the limit for instance shared memory. It does not say how much memory is currently in use. To find out the memory usage of an instance, you can use the DB2 Memory Tracker tool DB2MTRK. For example
The above example shows that although the memory reserved for the instance shared memory is in the amount of megabytes, it only uses about MB when the DB2MTRK is running.
Note: In some cases, the size of the DB2MTRK display is greater than the value assigned to the configuration parameter. In this case, the value that is given to the configuration parameter is used as a soft limit, and the memory pool is actually used by a memory that may grow beyond the configured size.
Database Shared Memory
Each database has a database shared memory set. The database shared memory is allocated when the database is activated or the first time it is connected. The memory set is released when the database is inactive (if the database was previously active) or when the last connection was disconnected. This memory is used for database-level tasks such as backup/restore, locking, and SQL execution.
Figure 2 shows the various pools of memory in a database shared memory set. The configuration parameters that control the size of these memory pools are shown in parentheses.
Figure 2-DB2 Database Shared memory
The full green box means that the memory pool is fully allocated when the database is started, otherwise, only part of the memory is allocated. For example, when a database is first started, no matter what the value of UTIL_HEAP_SZ, only about five KB of memory is allocated to the utility heap. When a database utility (such as backup, restore, export, import, and Mount) is started, the full amount of memory is allocated at the size specified by UTIL_HEAP_SZ.
Primary buffer pool
A database buffer pool is typically the largest chunk of memory in a database's shared memory. DB2 in which all regular data and index data are manipulated. A database must have at least one buffer pool and can have multiple buffer pools, depending on the characteristics of the workload, the size of the database page used in the database, and so on. For example, a table space with a page size of 8KB can only use a buffer pool with a page size of 8KB.
You can extend the buffer pool through the EXTENDED STORAGE option in the CREATE bufferpool statement. Extended Storage (ESTORE) acts as a secondary cache of pages that are evicted from the buffer pool, which can reduce I/O. The size of the ESTORE is controlled by the two database configuration parameters Num_estore_segs and ESTORE_SEG_SZ. If you use ESTORE, you will need to take out a certain amount of memory from the database shared memory to manage ESTORE, which means less memory for other memory pools.
At this point you may ask, why bother to use ESTORE? Why not allocate a larger buffer pool? The answer is related to the limitations of addressable memory (not physical memory), which we'll discuss later.
Hidden buffer Pool
When the database starts, allocate 4 small buffer pools of 4K, 8K, 16K, and 32K, respectively, to the page width. These buffer pools are "hidden" because they are not visible in the system catalog (via SELECT * from SYSCAT. Bufferpools not shown).
If the primary buffer pool is configured too large, there may be cases where the primary buffer pool is not suitable for addressable memory space. (We'll talk in the back to addressable memory.) This means that DB2 cannot start the database because a database must have at least one buffer pool. If the database does not start, you cannot connect to the database, and you cannot change the size of the buffer pool. For this reason, DB2 pre-allocated 4 such small buffer pools. This way, once the primary buffer pool fails to start, DB2 can also use these small buffer pools to start the database. (In this case, the user will receive a warning (SQLSTATE 01626)). At this point, you should connect to the database and reduce the size of the primary buffer pool.
Threshold value for sort heap (sheapthres, SHEAPTHRES_SHR)
Sorting is required if no index satisfies the order in which rows are taken, or if the optimizer determines that the ordering is less expensive than the index scan. There are two sorts in DB2, a private sort and a shared sort. Private sorting occurs in the proxy's private agent memory (discussed in the next section), while a shared sort occurs in the database shared memory of the database.
For private sorting, the Database Manager configuration parameter sheapthres Specifies the soft limit of the total amount of memory that a private sort can consume at any point in the instance range . If the total private sort memory consumed by an instance reaches this limit, the memory allocated for additional incoming private sort requests is greatly reduced. This will see the following message in Db2diag.log:
"Not enough memory available for a (private) sort heap of size size of sortheap. Trying smaller size ... "
If internal partition parallelism (intra-partition parallelism) or concentrator (concentrator) is enabled, DB2 chooses to perform a shared sort when DB2 determines that a shared sort is more efficient than a private sort. If you perform a shared sort, the sort heap used for this sort is allocated in database shared memory. The maximum amount of memory used for shared sorting is specified by the sheapthres_shr database parameter. This is a hard limit on the total amount of memory that can be consumed at any point in the database-wide range of shared sorts . When this limit is reached, applications that request sorting will receive error SQL0955 (RC2). After that, any request to share sort memory is not allowed until the total shared memory consumption falls back below the limit specified by SHEAPTHRES_SHR.
The following formula calculates how much memory the database shared memory set roughly needs: Database shared memory = (primary buffer pool + 4 hidden buffer pool + database Heap + Utility heap + Locklist + Package cache + Catalog cache) + (estore pages * 100 bytes) + approx. 10% of the Overhead
For databases with Intra_parallel or concentrator enabled, shared sort memory must be pre-allocated as part of the database shared memory, so the above formula becomes: Database shared memory = (primary buffer pool + 4 hidden buffer pool + database Heap + Utility heap + locklist + Package cache + Catalog Cache + sheapthres_shr) + (number of pages in eSTORE * 100 bytes) + approx. 10% overhead
tip: to find out how much memory is allocated to the primary buffer pool, you can emit:
SELECT * from SYSCAT. Bufferpools
Although the size of most memory pools is predetermined by their configuration parameters, the size of the following two memory pools is dynamic by default:
The effect of setting Maxappls to Automatic is to allow any number of applications that connect to the database. DB2 will dynamically allocate the required resources to support new applications. Therefore, the size of the package cache and catalog can vary with the value of the MAXAPPLS.
In addition to the above parameters, there is also a parameter that affects the amount of database shared memory. This parameter is database_memory. The default value for this parameter is AUTOMATIC. This means that DB2 will calculate the amount of database memory that is required for the current configuration based on the size of each pool listed above. In addition, DB2 will allocate some extra memory for the overflow buffer. Whenever a heap exceeds its configured size, an overflow buffer can be used to satisfy the peak demand for any heap in the instance shared memory area.
If the database_memory is set to a number, the larger of the database_memory and the sum of the memory pools is used.
If database_memory is set to AUTOMATIC, you can use the following command to display its value:
Use the db2mtrk tool to display the amount of memory currently in use:db2mtrk -i -d -v （在 Windows 中，-i 必须指定。在 UNIX 中，-i 是可选的。）
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.) ）
Note: when the value of max_connections is greater than the value of max_coordagents, the connection concentrator is enabled. These two parameters can be found in the Database manager configuration. (Use the GET DBM CFG to display the Database Manager configuration.) )
In these environments, applications typically require more than one agent to perform their tasks. It is necessary to allow these agents to communicate with each other (sending/receiving data to each other). To achieve this, we put these agents into a group called the application group. All DB2 agents that belong 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. The size is determined by the APPGROUP_MEM_SZ database configuration parameters.
Multiple applications can be assigned to the same application group. The number of applications that can fit within an application group can be calculated like this: APPGROUP_MEM_SZ/APP_CTL_HEAP_SZ
Within an application group, each application has its own application control heap. In addition, there is a portion of the application group shared memory that you want to prestage to the application group shared heap. As shown in the following:
Figure 3-DB2 Application Group shared memory
Example 1
Consider the following database configuration:
You can calculate the following values:
Don't be fooled by the APP_CTRL_HEAP_SZ parameter. This parameter is not the size of each application control heap that is used within an application group for each application. It is just a value that is used to calculate how many applications can be accommodated in this application group. The actual application control heap size for each application is calculated from the formula given in Figure 3, which is ((100-groupheap_ratio)% * APP_CTRL_HEAP_SZ).
Therefore, the higher the Groupheap_ratio, the larger the application group shared heap is, and the smaller the application control heap is for each application.
Example 2
Suppose that during the busiest time of the day, 200 applications are connected to the database described in Example 1. Since each application group can hold 78 applications, we need 200/78 = 3 application groups to accommodate a total of 200 applications. This should ensure that the system has enough RAM to support this configuration. Otherwise, a sql10003n error will occur.
Proxy Private Memory
Each DB2 agent process needs to acquire memory to perform its tasks. The agent process uses memory on behalf of the application to optimize, build, and execute access plans, perform sorting, log cursor information (such as location and state), collect statistics, and so on. To respond to a connection request or a new SQL request in a parallel environment, allocate proxy private memory for a DB2 agent.
The number of agents is limited by the lower of the following:
The proxy private memory set consists of the following memory pools. The size of these memory pools is specified by the database configuration parameters in parentheses:
We have mentioned that private memory is assigned to a DB2 agent when it is "assigned" to perform a task. So, when does private memory release? 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 that can be retained at any time. If the value is 0, then a restricted proxy is not allowed. As soon as an agent completes its work, the agent is destroyed and its memory is returned to the operating system. If the parameter is set to a value other than 0, then an agent will not be destroyed after it has completed its work. Instead, it will be returned to the idle agent pool until the number of idle proxies reaches the maximum value specified by Num_poolagents. When a new request is passed in, these idle agents are called to serve the new request. This reduces the overhead of creating and destroying agents.
When the agent becomes an idle agent, it still retains the private memory of its agent. This is designed to improve performance because it has the private memory ready when the agent is called again. If there are many idle agents, and all of these idle agents retain their private memory, it can cause the system to run out of memory. To avoid this situation, DB2 uses a registry variable to limit the amount of memory that each idle agent can retain. This variable is db2memmaxfree. Its default value is 8 388 608 bytes. This means that each idle agent can retain up to 8MB of private memory. If there are 100 idle agents, then these agents will retain 800MB of memory, so they will soon run out of RAM. You may want to reduce or increase this limit, depending on the size of the RAM.
Figure 1 shows the DB2 memory structure of a DB2 instance. Figure 4 shows a scenario in which two instances of the same system are running concurrently. Virtual memory includes physical RAM and paging space (paging spaces). Shared memory "tends to" stay in RAM because of the more frequent access to them. If the agent is idle for a longer period of time, its proxy private memory will be paged out.
Figure 4-Two DB2 instances running concurrently
Shared Memory and private memory
At this point, we have discussed instance shared memory, database shared memory and application group shared memory, and proxy private memory. But what is the meaning of shared memory and private memory?
To understand the difference between shared memory and private memory, let's start by quickly reading the DB2 process model to understand the DB2 agent process. In DB2, all database requests are serviced by DB2 agents or sub-agents. For example, when an application connects to a database, a DB2 proxy is assigned to it. When the application issues any database requests, such as an SQL query, the agent comes out to perform all the tasks required to complete the query-it works on behalf of the application. (If the database is partitioned, or if Intra-parallel is enabled, you can assign more than one agent to work on behalf of the application.) These proxies are called sub-agents. )
Each agent or sub-agent is treated as a DB2 process, which obtains a certain amount of memory to perform its work. This memory is called the proxy private memory-it cannot be shared with any other agent. As we mentioned before, proxy private memory includes some memory pools, such as the application heap size, the sort heap size, and the statement heap size. (see Figure 1)
In addition to private memory, where agents use the sort heap to perform "private" tasks, such as private sorting, proxies also require database-level resources, such as buffer pools, locklist, and log buffers. These resources are in the database shared memory (see Figure 1). The way DB2 works is that all resources in the database shared memory are shared by all agents or sub-agents that are connected to the same database. Therefore, the memory set is called shared memory, not private memory. For example, proxy x that is connected to database a uses a database of databases A to share resources in memory. Now another proxy, Agent Y, is also connected to database A. Then Agent y shares database A's database memory with Agent X. (Of course, both proxy x and proxy y have their own proxy private memory, which is not shared.) )
The same logic applies to instance shared memory and application group shared memory.
Shows the set of DB2 memory allocated when two DB2 proxies (proxy x and proxy y) are connected to database A. Assume:
Figure 5-DB2 Agent process memory address space
Figure 5 shows the following set of memory allocations in RAM:
Agent X and Agent y share the same instance memory, database memory, and application group memory because they belong to the same instance, the same database, and the same application group. In addition, they have their own proxy private memory.
Each DB2 agent process has its own memory address space. The memory address in the memory space allows the delegate to access memory in the physical RAM. We can think of these addresses as pointers to RAM, as shown in Figure 5. For any DB2 process, this address space must be able to accommodate all 4 of these memory sets.
As mentioned earlier, ESTORE is used to extend the size of the buffer pool. So you might want to ask, why not create a larger buffer pool? The answer is: because the address space is limited, the address space may not be able to accommodate a larger buffer pool! In this case, you need to define a buffer pool that can accommodate a smaller address space. If there is an excess of physical memory, you can use that memory to configure the ESTORE.
So, how do we know how big the address space of a DB2 agent is? The size of the address space depends on whether the current instance is a 32-bit instance or a 64-bit instance. We'll explain this in the next section.
32-bit architecture and addressable memory in 64-bit architectures
If you have a 64-bit DB2 instance, it means that DB2 is using a 64-bit memory architecture. In this architecture, for all platforms, the address space for each process is 2 64, or 18,446,744,073 GB. This is a pretty huge memory. There should be no problem putting all DB2 memory sets into this address space.
On the other hand, if there is a 32-bit DB2 instance, for all platforms, the address space is only 2 32, or 4 GB (except for the linux/390 platform, where the address space is actually only 2 of 31 of the time. However, in this article we do not discuss the DB2 in linux/390). So, regardless of the size of the physical RAM, to enable an DB2 process to access all the resources it needs, including instance shared memory, database shared memory, application group shared memory, its own proxy private memory, and memory for the kernel, all of these resources must be placed in the 4GB address space.
This leads to two very important questions:
Although the 4GB address space limitation applies to all platforms, the answers to the above questions are platform-dependent. For example, the maximum number of database memory that can be allocated to an DB2 database on an AIX system is different from the database on the Solaris system. The following sections discuss how different platforms affect the memory configuration in DB2.
Note: The subsequent sections of this article are only for 32-bit memory architectures. The problem we are about to discuss does not apply to the 64-bit architecture.
DB2 memory configuration in 32-bit AIX
On 32-bit AIX, the addressable memory space of 4GB is split into 16 segments, 256MB per segment. Figure 6 shows the 32-bit memory address space used for a DB2 agent process. (assuming that the two DB2 registry variables, Db2_mmap_read and Db2_mma_write, are set to NO.) If these two variables are not set to No, the method is a little different. We'll explain it later. ）
DB2 32-bit memory address space in Figure 6-aix
Segment 0-reserved for AIX cores.
Segment 1-reserved for the DB2SYSC process.
Segment 2-reserved for proxy private memory.
Segment 3-reserved for instance shared memory.
Segment 4 to segment B-database shared memory starts at segment 4, and these segments must be close together. All of these 8 segments (2GB) may be used for database shared memory. However, each of the following configurations takes a segment (256MB) from the database shared memory.
Note: for each of the following configurations, DB2 will take a segment from the database shared memory, which starts at segment B.
However, if you do not want to use the loopback solution, there is also a way to force DB2 to select segment E for proxy/local application communication so that the database shared memory is unaffected (that is, not reduced). See the explanations that follow.
Segment C-reserved for the DB2 trace usage program.
Segment D and F reserved to DB2 shared library
Paragraph E-By default, this segment is not used. However, if you set Db2_mmap_read=no and Db2_mmap_write=no, then the segment is used for communication between the DB2 agent and the local application (as shown in Figure 6). This will effectively share a segment of memory for the database.
Note: in order to maximize the space of the database shared memory, you should use the following registry variable setting: Db2_force_fcm_bp=no (the value is the default), Db2_mmap_read=no,db2_mmap_write=no.
DB2 Memory Summary
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
