On May 22, 6:56 pm, Liam Finnie <lfin...@ca.ibm.comwrote:
[...]
Hello,
You should take a look at the INSTANCE_MEMORY configuration parameter
- that limits the total amount of memory that can be allocated by the
DB2 instance. Once you get close to that limit, you likely won't be
able to activate any more databases. By default, the limit is
AUTOMATIC, and defaults to somewhere between 75% and 95% of RAM, and
you are not allowed to set it larger than the RAM on your box. In
previous releases, DB2's memory configuration was not limited to RAM
on the box, which means similar memory configurations may have worked,
but would have likely resulted in heavy pageing (and so, very poor
performance).
BTW - DB2 9.5 should not need more memory due to the threaded model,
in fact, overall memory requirements should have decreased slightly,
since threads are much more lightweight than entire processes.
If you want to monitor overall memory usage by DB2, you can use the
admin_get_dbp_mem_usage table function:
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T
db2pd also has a new option that outputs memory consumption with
better granularity:
db2pd -dbptnmem
Search for dbptnmem in the docs for a description of the info returned
by this command.
As for why self-tuning may not have helped (at least a bit), the
reason could be that you are not on a high enough Linux kernel level.
You need to be on at least RHEL5 or SLES10 SP1 (or equivalent) to
enable database memory tuning by STMM if both INSTANCE_MEMORY and
DATABASE_MEMORY are set to AUTOMATIC. If you set INSTANCE_MEMORY to a
specific value, that will allow STMM to start tuning database memory
consumption.
Cheers,
Liam.
Thanx for your valuable input Liam. Exactly the kind of info I hoped
someone would provide. Meanwhile I did some research on my own. First
I tried to activate as many databases as possible before hitting
SQL1084C. Then ran:
[db2inst1@wbv7d ~]$ db2mtrk -i -d -v | grep "Total" | awk '{ print
$2 }'
16121856
32440320
35192832
32899072
31981568
31850496
31981568
31850496
21495808
But that only sums up to ~250Mb, so I figure there are more mem being
used. Next I checked the dbm cfg for INSTANCE_MEMORY
[db2inst1@wbv7d ~]$ db2 get dbm cfg show detail | grep INSTANCE_MEMORY
Size of instance shared memory (4KB) (INSTANCE_MEMORY) =
AUTOMATIC(193963) AUTOMATIC(193963)
which is indeed close to the amount of memory on the machine. The I
looked at one of the activated databases for DATABASE_MEMORY:
[db2inst1@wbv7d ~]$ db2 get db cfg for nyav7 show detail | grep
DATABASE_MEMORY
Size of database shared memory (4KB) (DATABASE_MEMORY) =
COMPUTED(23424) COMPUTED(23424)
So we have roughly 100Mb per database according to this.
The function you mention turns out very useful. By activating db by db
and looking at the result I can actually determine how much memory
that is consumed step by step:
deactivate all db
[db2inst1@wbv7d ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type = Indirect" | grep "Database
alias" | cut -f2 -d=`; do db2 activate db $db; db2 connect to $db; db2
"SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T"; db2
connect reset; done
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 356188160
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 493092864
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 629997568
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 767033344
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 783024128
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 785973248
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 794230784
794427392
[...]
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]
>>493092864-356188160
136904704
>>629997568-493092864
136904704
>>767033344-629997568
137035776
>>783024128-767033344
15990784
>>794230784-785973248
8257536
Now the funny part, when I where to compare this with how one of my
8.2 installations behaved I realized that all 8.2 machines actually
have at least 2Gb memory. Anyhow, I can activate a lot more databases
on one of those machine so it looks as if a database needs more
initial memory on 9.5 than on 8.2
[db2inst1@wb-05 ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type
= Indirect" | grep "Database alias" | cut -f2 -d=`; do db2
activate db $db; done
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1494W Activate database is successful, however, there is already
a
connection to the database.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]
I will probably stuff 4Gb memory in the 9.5 dev machines and hopefully
that will solve this issue for now. I feel much better once I realized
that the machine causing this trouble had half the mem compared to the
8.2
Thanks both to Jeroen and Liam for taking your time with this
/Lennart