DB2記憶體使用量過高最佳化

DB2記憶體使用量過高最佳化

最近在在做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

 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 <db> using database_memory 8000000
$ db2stop force
$ db2start

資料庫重啟後，先用用戶端串連一下資料庫，然後再使用“db2mtrk -i -d -v”查看記憶體使用量情況，就發現記憶體就會有明顯的下降。用top查看結果也類似。

在真實的DB2使用方式下，修改這個值是需要謹慎的，可以參考下面列出的參考資料。

本文永久更新連結地址：