DB2 memory usage is too high optimized

Original link: DB2 Memory usage optimization

Recently in doing DB2 benchmark, found that the memory of the database is particularly high, because it is done benchmark test, so the table is not much, and mainly for one of the tables to do the test, when the data of this table is fast to 100 million, with the top query system status as follows

# top
top-20:06:34 up to 5 days, 22:20, users, Load average:18.53, 6.57, 3.19
...
 PID USER PR NI virt RES SHR s%cpu%mem time+ COMMAND
32458 db2inst 0 42.3g 27g 27g S 63.7 44.3 0:27.13 DB2SYSC 0
   ...

The Db2sync process uses 27G of memory, which causes the system's physical memory to be almost exhausted and the system to become very slow.

Use the DB2MTRK command to view the memory usage of DB2 as follows:

# DB2MTRK-I-d-v tracking Memory ON:2014/06/22 at 21:50:19 Memory to instance other Memory is of size 63111168 byte S FCMBP Heap is of size 851968 bytes Database Monitor Heap are of size 983040 bytes total:64946176 bytes Memory for da  tabase:pa913 Backup/restore/util Heap is of the size 65536 bytes Package Cache is of size 262144 bytes?? Size 196608 bytes Catalog Cache Heap is of size 262144 bytes Buffer Pool Heap (1) is of size 24465047552 bytes Buffer Pool Heap (System 32k buffer pool) is to size 1835008 bytes Buffer pool Heap (System 16k buffer pool) is of size 1572864 Bytes Buffer pool Heap (System 8k buffer pool) is of the size 1441792 bytes Buffer Pool Heap (System 4k buffer pool) is of s  ize 1376256 bytes Shared Sort Heap is of size 0 bytes Lock Manager Heap ' size 3881172992 bytes Database Heap is of Size 94830592 Bytes Application Heap (() is of size 131072 bytes Application Heap (a) of size 65536 bytes Applica tion Heap () is of sizE 65536 bytes Application Heap (() is to size 65536 bytes application (a) of size Heap 65536 bytes Application
 P (a) is of the size 65536 bytes Application Heap () is of size 65536 bytes application Heap (m) is of size 65536 bytes Application Heap () is of size 65536 bytes Application Heap (a) is of size 65536 bytes application (s) ize 65536 bytes Application Heap () is of size 65536 bytes application Heap (%) is of size 65536 bytes Application H EAP (a) is of the size 65536 bytes Application Heap () is to size 65536 bytes application Heap (to) is of size 65536 byte  Application Heap () is of the 65536 bytes Application Heap () is of size 65536 bytes application Heap (s)  Size 65536 bytes Application Heap (a) is of size 65536 bytes Application Heap () is of size 65536 bytes application Heap is of size 65536 bytes Application Heap (a) is of size 65536 bytes Application Heap () is of size 65536 by TES Application Heap (22 is of the size 65536 bytes Application Heap () is to size 65536 bytes application (a) is of size Heap 65536 App Lication Heap () is of size 65536 bytes Application Heap (a) is of size 65536 bytes Application Heap () 65536 bytes Application Heap () is of size 65536 bytes Application Heap () of size 65536 bytes Application Heap () is of size 65536 bytes Application Heap (A) of size 65536 bytes Application Heap (A) of size 65536 bytes A Pplication Heap (a) is of size 196608 bytes Application Heap (a) of size 65536 bytes Application Heap (9) is of siz E 65536 bytes Application Heap (8) is to size 65536 bytes Application Heap (7) is of size 131072 bytes applications Sha Red Heap is of size 393216 bytes total:28451340288 bytes

The emphasis is on "Buffer Pool Heap (1) is of size 24465047552 bytes" under "Memory for database:pa913", which uses nearly 25G of memory, and because I am sure that the amount of data in my database is not very large, it reduces Database memory is my first choice.

After checking the data, you can set the database memory usage by modifying the database_memory system parameters. By default, the Database_memory setting is automatic, where you can modify the parameters by using the following command to restart DB2

$ DB2 Update db CFG for <db> using database_memory 8000000
$ db2stop Force
$ db2start

After the database restarts, first connect the database with the client, then use "Db2mtrk-i-d-v" to view the memory usage, the memory will have a noticeable drop. The top view results are similar.

In the case of real DB2 use, it would be prudent to modify this value, referring to the resources listed below.

Reference

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0006017.html?lang=en

Http://www-01.ibm.com/support/knowledgecenter/api/content/SSEPGG_9.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0005181.html

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008712.html?lang=en

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0406qi/

http://www.db2china.net/home/space.php?uid=3821&do=blog&id=8829

http://www.db2china.net/home/space.php?uid=3821&do=blog&id=8830

http://www.db2china.net/home/space.php?uid=3821&do=blog&id=8831