DB2 memory usage too high Optimization

Recently, I was working on the benchmark of DB2 and found that the memory usage of the database was very high. Because the benchmark test was performed, there were not many tables, and the test was mainly performed on one of the tables.

Recently, I was working on the benchmark of DB2 and found that the memory usage of the database was very high. Because the benchmark test was performed, there were not many tables, and the test was mainly performed on one of the tables.

Recently, I was working on the benchmark of DB2 and found that the memory usage of the database was very high. Because the benchmark test was performed, there were not many tables, And I mainly tested one of them, when the data in this table is approaching 0.1 billion, the status of the top query system is as follows:

# Top
Top-20:06:34 up 5 days, 12 users, load average: 18.53, 6.57, 3.19
...
Pid user pr ni virt res shr s % CPU % mem time + COMMAND
32458 db2inst 20 0 42.3g 27g 27g S 63.7 44.3. 13 db2sysc 0
...

The db2sync process uses 27 GB of memory, which causes the system's physical memory to be almost used up and the system to become very slow.

Run the db2mtrk command to view the memory usage of db2, as shown below:

# Db2mtrk-I-d-v
Tracking Memory on: at 21:50:19

Memory for instance

Other Memory is of size 63111168 bytes
FCMBP Heap is of size 851968 bytes
Database Monitor Heap is of size 983040 bytes
Total: 64946176 bytes

Memory for database: PA913

Backup/Restore/Util Heap is of size 65536 bytes
Package Cache is of size 262144 bytes
Other Memory is of size 196608 bytes
Catalog Cache Heap is of size 262144 bytes
Buffer Pool Heap (1) is of size 24465047552 bytes
Buffer Pool Heap (System 32 k buffer pool) is of size 1835008 bytes
Buffer Pool Heap (System 16 k buffer pool) is of size 1572864 bytes
Buffer Pool Heap (System 8 k buffer pool) is of size 1441792 bytes
Buffer Pool Heap (System 4 k buffer pool) is of size 1376256 bytes
Shared Sort Heap is of size 0 bytes
Lock Manager Heap is of size 3881172992 bytes
Database Heap is of size 94830592 bytes
Application Heap (47) is of size 131072 bytes
Application Heap (45) is of size 65536 bytes
Application Heap (44) is of size 65536 bytes
Application Heap (43) is of size 65536 bytes
Application Heap (42) is of size 65536 bytes
Application Heap (41) is of size 65536 bytes
Application Heap (40) is of size 65536 bytes
Application Heap (39) is of size 65536 bytes
Application Heap (38) is of size 65536 bytes
Application Heap (37) is of size 65536 bytes
Application Heap (36) is of size 65536 bytes
Application Heap (35) is of size 65536 bytes
Application Heap (34) is of size 65536 bytes
Application Heap (33) is of size 65536 bytes
Application Heap (32) is of size 65536 bytes
Application Heap (31) is of size 65536 bytes
Application Heap (30) is of size 65536 bytes
Application Heap (29) is of size 65536 bytes
Application Heap (28) is of size 65536 bytes
Application Heap (27) is of size 65536 bytes
Application Heap (26) is of size 65536 bytes
Application Heap (25) is of size 65536 bytes
Application Heap (24) is of size 65536 bytes
Application Heap (23) is of size 65536 bytes
Application Heap (22) is of size 65536 bytes
Application Heap (21) is of size 65536 bytes
Application Heap (20) is of size 65536 bytes
Application Heap (19) is of size 65536 bytes
Application Heap (18) is of size 65536 bytes
Application Heap (17) is of size 65536 bytes
Application Heap (16) is of size 65536 bytes
Application Heap (15) is of size 65536 bytes
Application Heap (14) is of size 65536 bytes
Application Heap (13) is of size 65536 bytes
Application Heap (12) is of size 65536 bytes
Application Heap (11) is of size 196608 bytes
Application Heap (10) is of size 65536 bytes
Application Heap (9) is of size 65536 bytes
Application Heap (8) is of size 65536 bytes
Application Heap (7) is of size 131072 bytes
Applications Shared Heap is of size 393216 bytes
Total: 28451340288 bytes

The key is the Buffer Pool Heap (1) is of size 24465047552 bytes under "Memory for database: PA913", with nearly 25 GB of Memory, i'm sure the data volume in my database is not very large, so reducing the database memory is my first choice.

After checking the information, you can modify the database_memory system parameters to set the database memory usage. By default, database_memory is set to AUTOMATIC. Here, you can use the following command to modify the parameters. After modification, you must restart db2.

$ Db2 update db cfg Using database_memory 8000000
$ Db2stop force
$ Db2start

After the database is restarted, use the client to connect to the database, and then use "db2mtrk-I-d-v" to check the memory usage. Then, the memory will drop significantly. You can use top to view the results.

In actual DB2 usage, it is necessary to modify this value with caution. You can refer to the reference materials listed below.