V9.5 and SQL1084C
Lennart
First some background, we are currently using V8.2 but is planning to
move to 9.5 for the next next release of our application (within a
year or so). In our development environment we have a lot of
databases. This is due to the fact that each developer can create a
copy of the current master database, and develop against that. This
have worked very well for V8.2, but in our 9.5 environment we run into
SQL1084C as soon as we get a handful databases active. Of course we
will never get any performance out of a setup like that, but that is
of no importance in this environment.
Now, how do I pinpoint what limit I'm hitting? My first thought was
that kernel.shmmax was to low, so we have doubled it to 536870912
[db2inst1@wbv7d ~]$ cat /etc/sysctl.conf
[...]
kernel.shmmax = 536870912
kernel.msgmni = 1024
kernel.sem = 250 32000 32 1024
but that did not help. Next I tried running db2pd with various flags
(first time I've used it, and I'm having some difficulties
understanding the output), and AFAIK each database is not allocating
so much memory that it should be a problem.
I have tried to enable self_tuning_mem on each database, and set
bufferpools to size automatic, but that doesn't do any good:
for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 "update db cfg for $db using SELF_TUNING_MEM ON"; done
for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 connect to $db; db2 alter bufferpool IBMDEFAULTBP size
automatic; db2 alter bufferpool BPTMP2 size automatic; done
Any hints or help on how to determine what limit I'm hitting, and how
to resolve that is greatly appreciated.
/Lennart
The Boss
Hi Lennart,
What's your OS environment (Linux/AIX/Solaris/...) and how much memory does
it have?
I don't have any hands-on experience yet with 9.5, but from what I've heard
at IDUG 2007 in Athens, it is likely to need more memory due to the new
threaded model.
You said you've doubled shmmax, but it nevertheless is only a little over
half a Gig, which still seems not very much, especially in a multi-db
environment like yours. Depending on the amount of memory, I would advice
raising shmmax again.
HTH
--
Jeroen
The Boss
An additional thought: check your swap space.
When allocating a shared memory segment, the OS (Linux at least, not sure
about other OS's) needs enough swap space to be able to swap out the
segment.
As you have doubled shmmax, you therefor also may need to increase swap
space.
Cheers.
--
Jeroen
The Boss
I've done some googling and found a couple of links that might be of
interest:
http://www-1.ibm.com/support/docview.wss?uid=swg1IZ08069
http://www.ibm.com/developerworks/forums/thread.jspa?threadID=196706&tstart=0
Also found a blogpost but couldn't reach the blog-site directly, so here is
the interesting part I was able to grab via Google's cache:
<quote>
Tuesday, January 15, 2008
DB2 9.5 Migration SQL1084C, Registry Variables
Was doing another 9.5 migration on Linux last night, and ran into two
issues.
1) db2 migrate db pepto
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
It turns out the new threaded model is much more picky about the maximum
amount of shared memory it has available. To fix this issue modify
/etc/sysctl.conf and modify kernel.shmmax to something large like:
kernel.shmmax=1610612736 (1.5GB, should be the amount of memory in your
system)
Run sysctl -p (as root)
You should be able to migrate to 9.5 now.
2) Registry variables disappeared. Nothing seems to be in the doc about it
(unless I am blind). But make sure you check them after your migration.
Regards,
Rob Williams
</quote>
[Blog-link:
http://www.mhubel.com/blog/rob/2008/01/db2-95-migration-sql1084c-registry.html]
--
Jeroen
Lennart
Hi Jeroen. Thanx for your answer(s). It is Linux and for the dev
environments we have only 1 Gb of memory:
[db2inst1@wbv7d ~]$ uname -a
Linux wbv7d 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:58:04 EST 2007 i686
i686 i386 GNU/Linux
[db2inst1@wbv7d ~]$ cat /proc/meminfo
MemTotal: 1034468 kB
MemFree: 27200 kB
Buffers: 98884 kB
Cached: 515704 kB
SwapCached: 1352 kB
Active: 642608 kB
Inactive: 235380 kB
HighTotal: 130908 kB
HighFree: 924 kB
LowTotal: 903560 kB
LowFree: 26276 kB
SwapTotal: 2096472 kB
SwapFree: 2071924 kB
Dirty: 108 kB
Writeback: 0 kB
Mapped: 599156 kB
Slab: 104564 kB
CommitLimit: 2613704 kB
Committed_AS: 3687264 kB
PageTables: 10624 kB
VmallocTotal: 106488 kB
VmallocUsed: 3340 kB
VmallocChunk: 102680 kB
HugePages_Total: 0
HugePages_Free: 0
Hugepagesize: 2048 kB
Not much but it has worked well with 8.1 and 8.2. Do you have any
thoughts on how to estimate the amount of memory needed? What I would
like to do is to inspect 1 of these databases and determine the memory
needed. Then I could multiply that with the number of simultaneous
active databases and come up with a number.
I'm not sure how much memory that could be plugged into these machines
( I assume I can put in 4Gb), but if that is not enough I may need to
order new machines.
Thanx
/Lennart
> HTH
>
> --
> Jeroen
Lennart
FWIW, I'm pretty sure that I'm hitting a limit other than
kernel.shmmax. I changed that value to 256, 512 and 768 and it does
not make a difference on the number of active databases I can have.
Also, I did a redirected restore of the database 20 times on a machine
with 4Gb of memory, and I can activate more of them there (in addition
there is already a much bigger db active on this machine). On this
mkachine kernel.shmmax is 512
[db2inst1@db2-00 ~]$ for db in `db2 list db directory | grep LELLE |
grep name | 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.
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
As mentioned before, I would be great full if anyone has info
regarding db2pd or other tool that can help me determine what exact
limit I'm hitting.
Liam Finnie
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.
Lennart
[...]
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
Liam Finnie
Hi Lennart,
Glad that helped. You mentioned that databases in 9.5 need more
initial memory than in 8.2.... this may be because the configuration
advisor runs by default starting in v9, which means that during
database creation, the config advisor will look at the memory
configuration on your box, and update various configuration parameters
automatically (to get improved out-of-the-box performance). If you
want to bypass the configuration advisor in 9.5, you can add
'AUTOCONFIGURE APPLY NONE' - that will give you a very minimal initial
database configuration (similar to the default in 8.2).
Cheers,
Liam.
Lennart
Hi Liam, the databases on 9.5 are restored from a backup of the 8.2
database (and automatically migrated). I haven't checked, but I guess
that this option only applies when you create a db from scratch.
/Lennart
> Cheers,
> Liam.