Memory usage in DB2 (1)

Introduction
Understanding how DB2 uses memory can prevent excessive memory allocation and help optimize memory usage for better performance.

This article will teach you the basics of DB2 memory usage, as well as the concepts of shared memory and private memory. The content is applicable to both 32-bit and 64-bit systems. Although there are some restrictions on 64-bit systems, these restrictions will not be reached in the future. Therefore, we will focus on the memory limits that affect 32-bit systems and discuss them in detail.

We will first discuss how DB2 uses memory in general cases, then how memory management changes with the Platform AIX, Sun, HP, Linux, and Windows, and their impact on DB2. Finally, we will provide meaningful examples of customer situations/problems and their solutions in real life. This article applies to DB2 version 8.

DB2 memory structure Overview

Figure 1 illustrates the DB2 memory structure. 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 in four different memory sets. These four memory sets are:

• Instance shared memory)
• Database shared memory)
• Application group shared memory)
• Proxy private memory agent private memory)

Each memory set is composed of a variety of memory pools, also known as heap. Figure 1 also shows the names of memory pools. For example,LocklistIt is a memory pool that belongs to the database shared memory set.SortheapIt is a memory pool of the proxy private memory set.

We will discuss each memory set in detail.

Instance shared memory

Each DB2 instance has an instance with shared memory. Instance shared memory is allocated when the database manager starts db2start and is released as the database manager stops db2stop. This memory set is used for instance-level tasks, such as monitoring, auditing, and inter-node communication. The following database manager configures dbm cfg) parameters control the shared memory of instances and the limitations of some memory pools:

• Instance memoryInstance_memory).
• Monitor heapMon_heap_sz): Used for monitoring.
• Audit BufferAudit_buf_sz): Used for the db2audit utility.
• Fast Communication buffersFcm_num_buffers): Used for inter-node communication between partitions. Only applicable to partitioned instances.

Instance_memoryThe parameter specifies the amount of memory reserved for instance management. The default value isAUTOMATIC. This means that DB2 will calculate the number of instance memory required for the current configuration based on the size of the monitor heap, audit buffer, and FCM buffer. In addition, DB2 allocates some additional memory as an overflow buffer. Whenever a heap exceeds the configured size, the overflow buffer can be used to meet the peak demand of any heap in the shared memory area of the instance. In this caseSoftLimitation, which can further increase during the peak memory usage.

IfInstance_memoryIf it is set to a number, useInstance_memoryAndMon_heap_sz,Audit_buf_szAndFcm_num_buffersAnd. In this case, a hard limit is imposed on the instance memory, instead of a soft limit. When this limit is reached, a memory allocation error is returned. For this reason, we recommend that youInstance_memoryIs retainedAUTOMATIC.

IfInstance_memorySetAUTOMATIC, You can use the following command to determine its value:

• Db2 attachInstance_nameWhereInstance_nameIs the Instance name)
• Db2 get dbm cfg show detail

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

• Size of instance shared memory (4 kb) (INSTANCE_MEMORY) = automation (10313) Automation (10313)

Instance_memoryThe parameter only sets the shared memory limit for the instance. It does not indicate how much memory is currently used. To check the memory usage of an instance, you can 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 shows that although the memory reserved for the instance shared memory set is 42 MBDb2mtrkIt only takes about 21 MB to run.Note:In some cases, the size displayed by db2mtrk is greater than the value specified for the configuration parameter. In this case, the value assigned to the configuration parameter is used as a soft limit, and the memory actually used by the memory pool may increase, thus exceeding 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 connected for the first time. This memory set will be released when the database is not activated if the database is previously activated) or when the last connection is disconnected. This memory is used for database-level tasks, such as backup/recovery, locking, and SQL Execution.

Figure 2 shows various memory pools in the database shared memory set. The configuration parameters that control the memory pool size are shown in brackets.

Figure 2-DB2 database shared memory

The Green Box indicates 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 started for the first timeUtil_heap_szOnly about 16 KB of memory is allocated to the utility heap. When a database utility such as backup, recovery, export, import and load is startedUtil_heap_szThe specified size is allocated with full memory.

Primary Buffer Pool

The database buffer pool is usually the largest memory in the shared memory of the database. DB2 operates on all common data and index data. A database must have at least one buffer pool and multiple buffer pools, depending on factors such as workload characteristics and database page size used in the database. For example, a tablespace with a page size of 8 KB can only use a buffer pool with a page size of 8 KB.

You can use the extended storage option "EXTENDED" buffer pool in the create bufferpool statement. The extended storage ESTORE acts as a secondary cache for pages evicted from the buffer pool, which can reduce I/O. The size of an ESTORE is controlled by the configuration parameters num_estore_segs and estore_seg_sz. If you use an ESTORE, You need to obtain a certain amount of memory from the database shared memory for managing the ESTORE, which means that the memory for other memory pools will be less.

At this time, you may ask, why is it so troublesome to use ESTORE? Why not allocate a larger buffer pool? The answer is related to the limitation of addressable memory instead of physical memory. We will discuss it later.

Hidden Buffer Pool

When the database is started, four small buffer pools with a page width of 4 K, 8 K, 16 K, and 32 K are allocated. These buffer pools are "hidden" because they are not displayed in the system catalog Through SELECT * from syscat. BUFFERPOOLS ).

If the primary buffer pool is too large, the primary buffer pool may be unsuitable for addressing the memory space. We will talk about addressable memory later .) This means that 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 size of the buffer pool. For this reason, DB2 has allocated four such small buffer pools in advance. In this way, once the primary Buffer Pool cannot be started, DB2 can also use these small buffer pools to start the database. In this case, the user will receive a warning SQLSTATE 01626 )). Connect to the database and reduce the size of the primary buffer pool.