tuning kernel for "IBM DB2"

[Jerry Sims]

Received the following message when DB2 instance(s) are brought up :

2004-01-02-02.45.07.623898 Instance:cpdmoi Node:000
PID:22645(db2agent (CPDMO)) Appid:*LOCAL.cpdmoi.040102104506
buffer_pool_services sqlbinit Probe:300 Database:CPDMO

BufferPool and/or Estore memory cannot be allocated.
totalBufferPoolPages: 0000 0040 ...@


2004-01-02-02.45.07.629198 Instance:cpdmoi Node:000
PID:22645(db2agent (CPDMO)) Appid:*LOCAL.cpdmoi.040102104506
buffer_pool_services sqlbinit Probe:330 Database:CPDMO

Database is starting up with tiny bufferpools.
Number of bufferpools coming up: 0000 0004 ....


String Title:sqlbinit PID:22645 Node:000
Database is starting up with 4 small Buffer Pools,
each of size 16 pages

Are there kernel parameters that need to be changed ?

[Bill Hassell]

Like most commercial database products, the error message is very misleading. It says that "Estore memory cannot be allocated". What kind of memory? Is it local to the program (ie, malloc system call) or shared memory? Has the program grown larger than maxdsiz? Or is the system truly out of virtual memory (ie, RAM + swap)? Is the program a 32bit program and trying to access more memory than it can address? If the error is related to shared memory (and a 32bit program), has the shared memory pool for 32bits become fragmented?

It might be as simple as: need more swap space. Use swapinfo -tm to see the total virtual memory (last line). If it is approaching 80%-90% then add more swap and if the programs now run OK, order lots more RAM because swapping (paging) can cost 100:1 or more in performance loss.


Since the program won't tell you what's really wrong, start with identifiying whether the DB2 programs are 32bit or 64bit. If you don't know, start with the command:

getconf KERNEL_BITS

If it says 32, then you are running a 32bit kernel and *all* programs will be 32bits (64bit programs cannot be run at all). If the kernel is 64, then identify the type of executable using the file command as in:

file /path-to-DB2/some-binary

If it has the word ELF in it, then the executable is 64bits, otherwise the programs are 32bit and subject to significant limitations for addressability (regardless of the amount of RAM and swap space).

Now the next few issues assume 32bit programs which are the most limiting. Start with maxdsiz in the kernel. By default, the largest program can grow to about 67megs (maxdsiz=67,000,000 apx). If DB2 needs more RAM, increase the kernel parameter maxdsiz (SAM) to 960 megs (the max for standard 32bit programs) and reboot. That will fix programs that need local memory up to 960 megs (and assumes that virtual memory is large enough).

If the program needs more than 960 megs then you'll have to change the executables to use the EXEC_MAGIC feature and connect quadrant 1+2 together for up to 1750 megs of local data. You'll also need to change maxdsiz to 1750 megs too (reboot).

However, if the program(s) do not require hundreds of megs of local RAM, then the error is likely due to shared memory. And just like local memory, similar limitations exist for shared memory (shmmax = 960 and 1750 megs) only now the memory is in a single 32bit pool and all 32bit processes must obtain contiguous chunks from that pool. The exception is when using memory windows - but that is a long discussion, look at docs.hp.com for details.

So shmmax should be set to a large value. Just like maxdsiz, these are simple fences or limits to prevent bad programs from using all the virtual memory. But now the tricky part: Each program's request for a shared memory segment will only succeed if there is a contiguous piece of memory of the requested size. Stop and restart some programs that use big and small chunks of shared memory and memory can become seriously fragmented with lots of chunks that are too small for the request. Rebooting will help. To see what has happened to your shared memory, get a copy of shminfo from ftp:/contrib:9unsupp8/hprc.external.hp.com/sysadmin/programs/shminfo/

An existing command to see actual segments in use is: ipcs -bmop but this will not show fragmentation. Also, ipcs -bmop will also show unattached segments, usually caused by (wrongfully) terminating processes with kill -9. You can (carefully) use ipcrm to remove unused segments from memory on a running system.

However, if you have 64bit executables, the only limits are total virtual memory (swapinfo -tm) and maxdsiz_64 and/or shmmax.

[Bill Hassell · Accepted solution]

Sorry, typo in the ftp URL:

ftp://contrib:9unsupp8@hprc.external.hp.com/sysadmin/programs/shminfo/

Notice that this is NOT a web page but an ftp site combined with a login. You can use regular ftp (assuming your ftp can go through your firewall) or the URL in any GUI browser. Note that for IE5 and higher, you may need to turn off "Enable folder view" in the Advanced Internet Options to prevent hangs.

[Bill Hassell]

And I forgot one other value that is defined in the environment: ulimit -d which shows the maximum amount of data that can be requested (should be unlimited). Note that ksh may not be able to show this value. Note also that ulimit must be run in the same manner as starting DB2 so you'll see the same environment (add uname -a to the startup script for additional info)

[Jerry Sims]

Wow, I don't know where to start thanking you for all the good information. This troubleshooting process is all new to me. I asked the dba to contact "IBM DB2" vendor to deliver the correct kernel parameters to me. So far they delivered "change shmseg from `16` to `120`, which I did. That got rid of the error :
"Memory allocation failure occured".

Here is some general information about the system:
o uname -a
HP-UX hostname B.11.11 U 9000/800 683389376 unlimited-user license

*) DBA indicates "IBM DB2" is a 32bit product

*) swapinfo -tm
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 0 4096 0% 0 - 1 /dev/vg00/lvol2
reserve - 2458 -2458
memory 4746 747 3999 16%
total 8842 3205 5637 36% - 0 -
*) [/]# getconf KERNEL_BITS
64

*) [/]# find . -name db2 -exec file {} \; | grep -v emc
./opt/IBMdb2/V7.1/bin/db2: PA-RISC1.1 shared executable dynamically linked -not stripped
./var/adm/sw/patch/PDB2_7104005/opt/IBMdb2/V7.1/bin/db2: PA-RISC1.1 shared executable dynamically linked -not stripped
./var/adm/sw/patch/PDB2_7104405/opt/IBMdb2/V7.1/bin/db2: PA-RISC1.1 shared executable dynamically linked -not stripped
./var/adm/sw/patch/PDB2_712060005/opt/IBMdb2/V7.1/bin/db2: PA-RISC1.1 shared executable dynamically linked -not stripped

*)[/]# ipcs -bomp
IPC status from /dev/kmem as of Fri Jan 2 14:42:21 2004
T ID KEY MODE OWNER GROUP NATTCH SEGSZ CPID LPID
Shared Memory:
m 0 0x411c0281 --rw-rw-rw- root root 0 348 698 10615
m 1 0x4e0c0002 --rw-rw-rw- root root 1 61760 698 10615
m 2 0x41200178 --rw-rw-rw- root root 1 8192 698 10615
m 3 0x301c6ca7 --rw-rw-rw- root root 3 1048576 1292 1309
m 9004 0x0c6629c9 --rw-r----- root root 2 12464424 2395 6383
m 5 0x06347849 --rw-rw-rw- root root 1 77384 2395 2438
m 1006 0x49140a80 --rw-r--r-- root root 0 22908 2310 2438
m 95007 0x435dce60 --rw-rw-rw- root root 1 8024 2784 2784
m 87008 0x74544d22 --rw-rw-rw- portdevi dba 33 524288 14729 23337
m 5009 0x00000000 D-rw------- cpdmoi dba 1 65536 7289 8842
m 85010 0x745411f5 --rw-rw-rw- cptesti dba 42 524288 27646 23315
m 166011 0x615411f5 --rw------- cptesti dba 42 4096000 27646 27906
m 31012 0x00000000 --rw------- cptesti dba 34 532152320 27780 28127
m 4014 0x00000000 D-rw-rw-rw- cpdmoi dba 1 524288 7284 28008
m 4015 0x74540fa1 --rw-rw-rw- cpdmoi dba 9 524288 16551 23346
m 782016 0x61540fa1 --rw------- cpdmoi dba 9 4096000 16551 23336
m 9017 0x00000000 D-rw------- cpdevi dba 1 65536 7351 8138
m 43018 0x61544d22 --rw------- portdevi dba 33 4096000 14729 15059
m 9020 0x00000000 D-rw------- cpdmoi dba 1 65536 7289 7501
m 13022 0x00000000 D-rw-rw-rw- cpdevi dba 1 524288 7346 28767
m 8025 0x00000000 --rw------- portdevi dba 22 383287296 14741 14920
m 7026 0x74540fa6 --rw-rw-rw- cpdevi dba 48 524288 14804 23319
m 51027 0x61540fa6 --rw------- cpdevi dba 48 4096000 14804 14804
m 128028 0x00000000 --rw------- cpdevi dba 40 532152320 14813 21463

*) kmtune
NSTRBLKSCHED - - 2
NSTREVENT 50 - 50
NSTRPUSH 16 - 16
NSTRSCHED 0 - 0
STRCTLSZ 1024 - 1024
STRMSGSZ 65535 - 65535
acctresume 4 - 4
acctsuspend 2 - 2
aio_listio_max 256 - 256
aio_max_ops 2048 - 2048
aio_physmem_pct 10 - 10
aio_prio_delta_max 20 - 20
allocate_fs_swapmap 0 - 0
alwaysdump 1 - 1
bcvmap_size_factor 2 - 2
bootspinlocks - - 256
bufcache_hash_locks 128 - 128
bufpages 64000 - 64000
chanq_hash_locks 256 - 256
core_addshmem_read 0 Y 0
core_addshmem_write 0 Y 0
create_fastlinks 0 - 0
dbc_max_pct 50 - 50
dbc_min_pct 5 - 5
default_disk_ir 0 - 0
desfree - - 0
disksort_seconds 0 - 0
dnlc_hash_locks 512 - 512
dontdump 0 - 0
dskless_node - - 0
dst 1 - 1
effective_maxpid - - ((NPROC<=30000)?30000:(NPROC*5/4))
eisa_io_estimate - - 0x300
enable_idds 0 - 0
eqmemsize 15 - 15
executable_stack 1 - 1
fcp_large_config 0 - 0
file_pad - - 10
fs_async 0 - 0
ftable_hash_locks 64 - 64
hdlpreg_hash_locks 128 - 128
hfs_max_ra_blocks 8 - 8
hfs_max_revra_blocks 8 - 8
hfs_ra_per_disk 64 - 64
hfs_revra_per_disk 64 - 64
hp_hfs_mtra_enabled 1 - 1
hpux_aes_override - - 0
initmodmax 50 - 50
io_ports_hash_locks 64 - 64
iomemsize - - 40000
ksi_alloc_max 16512 - (NPROC*8)
ksi_send_max 32 - 32
lotsfree - - 0
max_async_ports 50 - 50
max_fcp_reqs 512 - 512
max_mem_window 0 - 0
max_thread_proc 1200 - (MAXUSERS*3)
maxdsiz 0x10000000 - 0x10000000
maxdsiz_64bit 0x40000000 - 0X40000000
maxfiles 2048 - 2048
maxfiles_lim 2048 Y 2048
maxqueuetime - - 0
maxssiz 0x800000 - 0X800000
maxssiz_64bit 0x800000 - 0X800000
maxswapchunks 2048 - 2048
maxtsiz 0x4000000 Y 0X4000000
maxtsiz_64bit 0x40000000 Y 0X40000000
maxuprc 1500 Y 1500
maxusers 400 - 400
maxvgs 10 - 10
mesg 1 - 1
minfree - - 0
modstrmax 500 - 500
msgmap 2050 - 2050
msgmax 65535 Y 65535
msgmnb 65535 Y 65535
msgmni 1024 - 1024
msgseg 32767 - 32767
msgssz 16 - 16
msgtql 2048 - 2048
nbuf 0 - 0
ncallout 14512 - (2*((((NPROC*7)/4)+16)*2))
ncdnode 150 - 150
nclist 6500 - (100+16*MAXUSERS)
ncsize 7168 - (NINODE+VX_NCSIZE)+(8*DNLC_HASH_LOCKS)
ndilbuffers 30 - 30
netisr_priority - - -1
netmemmax - - 0
nfile 8192 - (4*NINODE)
nflocks 8192 - 8192
nhtbl_scale 0 - 0
ninode 2048 - 2048
nkthread 2400 - (2*MAX_THREAD_PROC)
nni - - 2
no_lvm_disks 0 - 0
nproc 2064 - ((MAXUSERS*5)+64)
npty 60 - 60
nstrpty 60 - 60
nstrtel 60 - 60
nswapdev 10 - 10
nswapfs 10 - 10
nsysmap 4128 - ((NPROC)>800?2*(NPROC):800)
o_sync_is_o_dsync 0 - 0
page_text_to_local - - 0
pfdat_hash_locks 128 - 128
public_shlibs 1 - 1
region_hash_locks 128 - 128
remote_nfs_swap 0 - 0
rtsched_numpri 32 - 32
scroll_lines 100 - 100
scsi_max_qdepth 8 Y 8
scsi_maxphys 1048576 - 1048576
sema 1 - 1
semaem 16384 - 16384
semmap 2050 - 2050
semmni 2048 - 2048
semmns 4096 - 4096
semmnu 1024 - 1024
semmsl 2048 Y 2048
semume 20 - 20
semvmx 32767 - 32767
sendfile_max 0 - 0
shmem 1 - 1
shmmax 5798205849 Y 5798205849
shmmni 1000 - 1000
shmseg 120 Y 120
st_ats_enabled 0 - 0
st_fail_overruns 0 - 0
st_large_recs 0 - 0
st_san_safe 0 - 0
streampipes 0 - 0
swapmem_on 1 - 1
swchunk 2048 - 2048
sysv_hash_locks 128 - 128
tcphashsz 0 - 0
timeslice 10 - (100/10)
timezone 420 - 420
unlockable_mem 0 - 0
vas_hash_locks 128 - 128
vnode_cd_hash_locks 128 - 128
vnode_hash_locks 128 - 128
vps_ceiling 16 - 16
vps_chatr_ceiling 1048576 - 1048576
vps_pagesize 4 - 4
vx_fancyra_enable 0 - 0
vx_maxlink 32767 - 32767
vx_ncsize 1024 - 1024
vx_ninode 0 - 0
vxfs_max_ra_kbytes 1024 - 1024
vxfs_ra_per_disk 1024 - 1024

To make a long story short. I should recomend
changing the following parameters :
*) [/]# kmtune | egrep "maxdsiz|shmmax"
maxdsiz 0x10000000 - 0x10000000
calculated value 268435456
maxdsiz_64bit 0x40000000 - 0X40000000
calculated value 1073741824
shmmax 5798205849 Y 5798205849

To new parameters :
maxdsiz 960000000
maxdsiz_64bit no change
shmmax no change

?

[Jerry Sims]

[/]# ulimit -d
262144

[Bill Hassell]

So you're running a 64bit kernel but the executables are all 32bit. Now the limitations described above will apply to the DB2 programs. shmseg is the maximum number of shared memory segments for a single process to attach. Like maxdsiz and shmmax, shmseg prevents runaway programs from using memory without limits. So the error message meant that a shared memiry request was refused but not because there wasn't enough memory, but because more than 16 segments were being requested. You might see if there is a DB2 document concerning other kernel parameters for HP-UX that may be important.

As far as other parameters, you need to change dbc_max_pct from 50 to 10. This is an unfortunate default value that should work out to about 400-600 megs. nproc and nfile will have to be monitored (sar -v 1) since the formula values usually aren't adequate. nproc needs to go up as more suers and processes are run at the same time, and nfile is usually 5-10x the size of nproc.

[john bilyeu]

We fought with DB2 v7 (32bit) and its dixie-cup RAM allocations before upgrading to v8.1 64bit. We can now use much larger buffer pools, etc. but IBM was very little help with tuning suggestions, so we fumbled our way through (FYI we are currently at 24 gig RAM on a 1.5 TB data warehouse). There is a utility called db2osconf which will make some kernel tuning suggestions (not sure in what release it became available).
Kudos to Bill for that excellent recap of memory related information.