DB2内存使用过高优化

最近在在做DB2的benchmark，发现数据库的内存占用特别高，因为是做benchmark测试，所以表并不多，并且主要是针对其中的一张表做测

最近在在做db2的benchmark，发现数据库的内存占用特别高，因为是做benchmark测试，所以表并不多，并且主要是针对其中的一张表做测试，当这张表的数据快到1亿的时候，用top查询系统状态如下

# top
top - 20:06:34 up 5 days, 22:20, 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 0:27.13 db2sysc 0
...

其中db2sync进程用了27G的内存，从而导致系统物理内存几乎被用光并且系统也变的非常慢。 

使用db2mtrk命令来查看db2的内存使用情况，如下：

# db2mtrk -i -d -v
Tracking Memory on: 2014/06/22 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

95Shop仿醉品商城

95Shop可以免费下载使用，是一款仿醉品商城网店系统，内置SEO优化，具有模块丰富、管理简洁直观，操作易用等特点，系统功能完整，运行速度较快，采用ASP.NET(C#)技术开发，配合SQL Serve2000数据库存储数据，运行环境为微软ASP.NET 2.0。95Shop官方网站定期开发新功能和维护升级。可以放心使用！ 安装运行方法 1、下载软件压缩包； 2、将下载的软件压缩包解压缩，得到we

0

查看详情

 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 32k buffer pool) is of size 1835008 bytes
 Buffer Pool Heap (System 16k buffer pool) is of size 1572864 bytes
 Buffer Pool Heap (System 8k buffer pool) is of size 1441792 bytes
 Buffer Pool Heap (System 4k 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

其中重点是“Memory for database: PA913”下的“Buffer Pool Heap (1) is of size 24465047552 bytes”，用了近25G的内存，由于我确定我的数据库中的数据量不是很大，所以减少数据库内存就是我的首选了。 

查了一下资料，可以通过修改database_memory系统参数来设置数据库内存的使用。默认情况下database_memory的设置是AUTOMATIC，这里可以通过下面的命令来修改参数，，修改完后要重新启动db2

$ db2 update db cfg for using database_memory 8000000
$ db2stop force
$ db2start

数据库重启后，先用客户端连接一下数据库，然后再使用“db2mtrk -i -d -v”查看内存使用情况，就发现内存就会有明显的下降。用top查看结果也类似。

在真实的DB2使用情况下，修改这个值是需要慎重的，可以参考下面列出的参考资料。

本文永久更新链接地址：