DB2 shared memory and extended storage

Hi all,

I'm getting very confused about how DB2 uses shared memory and I
wonder if someone could clarify matters for me, please ?

We are running 32bit DB2 V7.2 FP9 under AIX 4.3.3 on a machine with 64
Gb of memory with a single non-partitioned database using extended
storage and with intra-parallelism enabled.

I've been experimenting with changing various parameters in an attempt
to make my buffer pool as large as possible but I can't make it bigger
than about 700 Mb and still get the database to activate.

From what I've read in the manuals and on the internet I believe that
DB2 should be able to use a maximum of 7 shared memory segments, which
is about 1.75 Gb of memory, that is used for everything except the
agents private memory (i.e. DBM shared memory + Db global memory +
Application global memory + Agent/application shared memory). Is that
correct ?

Again from what I've read it seems that:

DBM shared memory = Monitor heap(mon_heap_sz) + Audit buffer size
(audit_buf_sz) + Global control block(How big is this ?) + FCM
Connection Entries(fcm_num_connect) + FCM Message Anchors
(fcm_num_anchors) + FCM Request Block (fcm_num_rqb) + FCM
Buffers(fcm_num_buffers) + FCM Control Block(How big ?) + Something
for the database agents, the manual isn't clear what.

and

Max DB global memory = Buffer Pool size + Locklist(locklist) +
Database Heap(dbheap) + Utility Heap Size(util_heap_sz) + Package
Cache(pckcachesz) + Extended memory cache (How big ?)

and

Application global memory = app_ctl_heap_sz

and

Agent/Application shared memory = Application support layer
heap(aslheapsz) + Client I/O block (rqrioblk)

Adding all these things up gives me a total of 940Mb which added to
the Global Control Block, FCM Control Block, Extended memory cache and
something for the agents should give a grand total of 1.75 Gb.

So, should those last four things use about 800 Mb of memory or have I
missed something ?

The manual also states that the database global memory is affected by
the numdb parameter but I've tried changing this from 8 to 1 with no
affect on the total shared memory used.

On a slightly related note, there is a comment in the manual that the
size of the extended storage segments (ESTORE_SEG_SZ) is operating
system dependent but nowhere can I find anything about what a sensible
value for that parameter is. I have found that using 264 segments of
16000 pages each uses more shared memory than 66 segments of 64000
pages each but I don't know why or if it's better to use lots of
little segments rather than a few big ones!

Also, I suspect I'm not using FCM (DB2_FORCE_FCM_BP is not set), am I
still allocating memory to FCM if the four FCM parameters are set to
non-zero values ?

Cheers,

Jeremy Goodall

Jeremy, just a quick reply befor I dive into this one more!
The number of segments you define should be equal to the number of BPs
that are defined to use Ext. Storage.
The size of the segments is then the result of:
Amount of Storage planned for Estore divided by no. of segments.

For the rest it seems you have the right approach but I want to look
further into it.
HTH, Pierre.

Jeremy wrote:[color=blue]
> Hi all,
>
> I'm getting very confused about how DB2 uses shared memory and I
> wonder if someone could clarify matters for me, please ?
>
> We are running 32bit DB2 V7.2 FP9 under AIX 4.3.3 on a machine with 64
> Gb of memory with a single non-partitioned database using extended
> storage and with intra-parallelism enabled.
>
> I've been experimenting with changing various parameters in an attempt
> to make my buffer pool as large as possible but I can't make it bigger
> than about 700 Mb and still get the database to activate.
>
> From what I've read in the manuals and on the internet I believe that
> DB2 should be able to use a maximum of 7 shared memory segments, which
> is about 1.75 Gb of memory, that is used for everything except the
> agents private memory (i.e. DBM shared memory + Db global memory +
> Application global memory + Agent/application shared memory). Is that
> correct ?
>
> Again from what I've read it seems that:
>
> DBM shared memory = Monitor heap(mon_heap_sz) + Audit buffer size
> (audit_buf_sz) + Global control block(How big is this ?) + FCM
> Connection Entries(fcm_num_connect) + FCM Message Anchors
> (fcm_num_anchors) + FCM Request Block (fcm_num_rqb) + FCM
> Buffers(fcm_num_buffers) + FCM Control Block(How big ?) + Something
> for the database agents, the manual isn't clear what.
>
> and
>
> Max DB global memory = Buffer Pool size + Locklist(locklist) +
> Database Heap(dbheap) + Utility Heap Size(util_heap_sz) + Package
> Cache(pckcachesz) + Extended memory cache (How big ?)
>
> and
>
> Application global memory = app_ctl_heap_sz
>
> and
>
> Agent/Application shared memory = Application support layer
> heap(aslheapsz) + Client I/O block (rqrioblk)
>
>
> Adding all these things up gives me a total of 940Mb which added to
> the Global Control Block, FCM Control Block, Extended memory cache and
> something for the agents should give a grand total of 1.75 Gb.
>
> So, should those last four things use about 800 Mb of memory or have I
> missed something ?
>
>
> The manual also states that the database global memory is affected by
> the numdb parameter but I've tried changing this from 8 to 1 with no
> affect on the total shared memory used.
>
> On a slightly related note, there is a comment in the manual that the
> size of the extended storage segments (ESTORE_SEG_SZ) is operating
> system dependent but nowhere can I find anything about what a sensible
> value for that parameter is. I have found that using 264 segments of
> 16000 pages each uses more shared memory than 66 segments of 64000
> pages each but I don't know why or if it's better to use lots of
> little segments rather than a few big ones!
>
> Also, I suspect I'm not using FCM (DB2_FORCE_FCM_BP is not set), am I
> still allocating memory to FCM if the four FCM parameters are set to
> non-zero values ?
>
> Cheers,
>
> Jeremy Goodall[/color]

--
Pierre Saint-Jacques
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

"Jeremy" <jeremy@home.co.uk> wrote in message
news:i2i0pvco0184srehvrg9a2f0etg8dvisgb@4ax.com...[color=blue]
> Hi all,
>
> I'm getting very confused about how DB2 uses shared memory and I
> wonder if someone could clarify matters for me, please ?
>
> We are running 32bit DB2 V7.2 FP9 under AIX 4.3.3 on a machine with 64
> Gb of memory with a single non-partitioned database using extended
> storage and with intra-parallelism enabled.
>
> I've been experimenting with changing various parameters in an attempt
> to make my buffer pool as large as possible but I can't make it bigger
> than about 700 Mb and still get the database to activate.
>
> From what I've read in the manuals and on the internet I believe that
> DB2 should be able to use a maximum of 7 shared memory segments, which
> is about 1.75 Gb of memory, that is used for everything except the
> agents private memory (i.e. DBM shared memory + Db global memory +
> Application global memory + Agent/application shared memory). Is that
> correct ?
>
> Again from what I've read it seems that:
>
> DBM shared memory = Monitor heap(mon_heap_sz) + Audit buffer size
> (audit_buf_sz) + Global control block(How big is this ?) + FCM
> Connection Entries(fcm_num_connect) + FCM Message Anchors
> (fcm_num_anchors) + FCM Request Block (fcm_num_rqb) + FCM
> Buffers(fcm_num_buffers) + FCM Control Block(How big ?) + Something
> for the database agents, the manual isn't clear what.
>
> and
>
> Max DB global memory = Buffer Pool size + Locklist(locklist) +
> Database Heap(dbheap) + Utility Heap Size(util_heap_sz) + Package
> Cache(pckcachesz) + Extended memory cache (How big ?)
>
> and
>
> Application global memory = app_ctl_heap_sz
>
> and
>
> Agent/Application shared memory = Application support layer
> heap(aslheapsz) + Client I/O block (rqrioblk)
>
>
> Adding all these things up gives me a total of 940Mb which added to
> the Global Control Block, FCM Control Block, Extended memory cache and
> something for the agents should give a grand total of 1.75 Gb.
>
> So, should those last four things use about 800 Mb of memory or have I
> missed something ?
>
>
> The manual also states that the database global memory is affected by
> the numdb parameter but I've tried changing this from 8 to 1 with no
> affect on the total shared memory used.
>
> On a slightly related note, there is a comment in the manual that the
> size of the extended storage segments (ESTORE_SEG_SZ) is operating
> system dependent but nowhere can I find anything about what a sensible
> value for that parameter is. I have found that using 264 segments of
> 16000 pages each uses more shared memory than 66 segments of 64000
> pages each but I don't know why or if it's better to use lots of
> little segments rather than a few big ones!
>
> Also, I suspect I'm not using FCM (DB2_FORCE_FCM_BP is not set), am I
> still allocating memory to FCM if the four FCM parameters are set to
> non-zero values ?
>
> Cheers,
>
> Jeremy Goodall[/color]

You might try running the Performance Wizard and give DB2 a total amount of
memory and then let the wizard allocate it among the components. That could
be used as a starting point for further tuning efforts.

On Fri, 17 Oct 2003 14:48:59 -0600, "Mark A" <ma@switchboard.net>
wrote:

[color=blue]
>You might try running the Performance Wizard and give DB2 a total amount of
>memory and then let the wizard allocate it among the components. That could
>be used as a starting point for further tuning efforts.
>[/color]

Mark,

Thanks for the tip. I've tried that but the performance wizard just
recommends allocating 54 Gb to the bufferpools which won't work,

Cheers,

Jeremy

"> >You might try running the Performance Wizard and give DB2 a total amount
of[color=blue][color=green]
> >memory and then let the wizard allocate it among the components. That[/color][/color]
could[color=blue][color=green]
> >be used as a starting point for further tuning efforts.
> >[/color]
>
> Mark,
>
> Thanks for the tip. I've tried that but the performance wizard just
> recommends allocating 54 Gb to the bufferpools which won't work,
>
> Cheers,
>
> Jeremy[/color]

Actually, I have only used Performance Wizard on DB2 version 8 for Linux. It
seemed to work fairly well, but not sure about your AIX memory issues.

On Fri, 17 Oct 2003 16:23:47 -0400, "P. Saint-Jacques"
<sescons@attglobal.net> wrote:
[color=blue]
>Jeremy, just a quick reply befor I dive into this one more!
>The number of segments you define should be equal to the number of BPs
>that are defined to use Ext. Storage.
>The size of the segments is then the result of:
>Amount of Storage planned for Estore divided by no. of segments.
>
>For the rest it seems you have the right approach but I want to look
>further into it.
>HTH, Pierre.
>[/color]

Pierre,

Not really, I'm afraid. We have a single buffer pool and intend
allocating up to 32 Gb of extended memory.
We are using 4k pages so 32 Gb of storage would be over 8 million
pages which is somewhat more than the upper limit for estore_seg_sz
which is just over a million pages.

Should we be using the smallest number of segments then(i.e. 8
segments, each about one million pages in size) or are you saying that
a buffer pool can only use a single segment ?

Cheers,

Jeremy

If you have 32 GB and want to use it effectively, one of the following
things may be more efficient that extended storage:

1. 64-bit DB2
2. use the memory for file system caching if the database is on SMS or
DMS with files
3. consider DB2 EEE with between 8 and 16 partitions.

Jeremy wrote:
[color=blue]
> On Fri, 17 Oct 2003 16:23:47 -0400, "P. Saint-Jacques"
> <sescons@attglobal.net> wrote:
>
>[color=green]
>>Jeremy, just a quick reply befor I dive into this one more!
>>The number of segments you define should be equal to the number of BPs
>>that are defined to use Ext. Storage.
>>The size of the segments is then the result of:
>>Amount of Storage planned for Estore divided by no. of segments.
>>
>>For the rest it seems you have the right approach but I want to look
>>further into it.
>>HTH, Pierre.
>>[/color]
>
>
> Pierre,
>
> Not really, I'm afraid. We have a single buffer pool and intend
> allocating up to 32 Gb of extended memory.
> We are using 4k pages so 32 Gb of storage would be over 8 million
> pages which is somewhat more than the upper limit for estore_seg_sz
> which is just over a million pages.
>
> Should we be using the smallest number of segments then(i.e. 8
> segments, each about one million pages in size) or are you saying that
> a buffer pool can only use a single segment ?
>
> Cheers,
>
> Jeremy[/color]

On Fri, 17 Oct 2003 18:46:00 -0400, Blair Adamache
<badamache@2muchspam.yahoo.com> wrote:
[color=blue]
>If you have 32 GB and want to use it effectively, one of the following
>things may be more efficient that extended storage:
>
>1. 64-bit DB2
>2. use the memory for file system caching if the database is on SMS or
>DMS with files
>3. consider DB2 EEE with between 8 and 16 partitions.
>[/color]

Blair,

Thanks for that but we can't use 64 bit DB2 as our client application
is then unable to communicate with the database. That was our
preferred option.

Regarding your second point, we did actually work this way for some
time whilst we were on V6.1 (because of a suspected instability of
extended memory under V6.1 and AIX 4.3) and found that we were still
doing significant physical I/O. In fact, when we switched on extended
memory the system performance doubled so my impression is that
extended memory is a much more efficient use of memory than file
system caching, if only for our particular system.

We may be forced down the EEE route but for the moment I'd prefer to
avoid the extra complexity that would involve if at all possible. I am
a little nervous about extended memory, if only because there is so
little documentation about it, but it appears to be the most sensible
way to go at present.

Just to avoid any confusion, we've actually got 64 GB on the machine
but intend using 32 GB for extended memory.

Cheers,

Jeremy

Okay - I suppose you could always move to v8 to get around the client
problem.

Jeremy wrote:
[color=blue]
> On Fri, 17 Oct 2003 18:46:00 -0400, Blair Adamache
> <badamache@2muchspam.yahoo.com> wrote:
>
>[color=green]
>>If you have 32 GB and want to use it effectively, one of the following
>>things may be more efficient that extended storage:
>>
>>1. 64-bit DB2
>>2. use the memory for file system caching if the database is on SMS or
>>DMS with files
>>3. consider DB2 EEE with between 8 and 16 partitions.
>>[/color]
>
>
> Blair,
>
> Thanks for that but we can't use 64 bit DB2 as our client application
> is then unable to communicate with the database. That was our
> preferred option.
>
> Regarding your second point, we did actually work this way for some
> time whilst we were on V6.1 (because of a suspected instability of
> extended memory under V6.1 and AIX 4.3) and found that we were still
> doing significant physical I/O. In fact, when we switched on extended
> memory the system performance doubled so my impression is that
> extended memory is a much more efficient use of memory than file
> system caching, if only for our particular system.
>
> We may be forced down the EEE route but for the moment I'd prefer to
> avoid the extra complexity that would involve if at all possible. I am
> a little nervous about extended memory, if only because there is so
> little documentation about it, but it appears to be the most sensible
> way to go at present.
>
> Just to avoid any confusion, we've actually got 64 GB on the machine
> but intend using 32 GB for extended memory.
>
> Cheers,
>
> Jeremy[/color]

AIX OS (system) caching will not work if containers are DMS. So SMS
containers are to be considered if the OS capability is to be
activated and also the settings of DB2_MMAP_READ and DB2_MMAP_WRITE to
OFF to allow the OS cache to be a natural extended memory, without any
DB2 configuration of extended storage. But AIX 4.3.3 will maybe show
contention on locking (see explanation of DB2_MMAP_*: In most
environments, mmap should be used to avoid operating system locks when
multiple processes are writing to different sections of the same
file.). Remark: this file system contention problem has been improved
(lowered) in AIX 5.1 (see redbook SG24-5765-02, page 220, AIX 5.1.0
enhancement, Complex inode lock).

Bernard Dhooghe

If you use DMS with files, you can benefit from OS file system caching,
I believe. The restriction you're talking about is only DMS with raw
devices.

Bernard Dhooghe wrote:
[color=blue]
> AIX OS (system) caching will not work if containers are DMS. So SMS
> containers are to be considered if the OS capability is to be
> activated and also the settings of DB2_MMAP_READ and DB2_MMAP_WRITE to
> OFF to allow the OS cache to be a natural extended memory, without any
> DB2 configuration of extended storage. But AIX 4.3.3 will maybe show
> contention on locking (see explanation of DB2_MMAP_*: In most
> environments, mmap should be used to avoid operating system locks when
> multiple processes are writing to different sections of the same
> file.). Remark: this file system contention problem has been improved
> (lowered) in AIX 5.1 (see redbook SG24-5765-02, page 220, AIX 5.1.0
> enhancement, Complex inode lock).
>
>
> Bernard Dhooghe[/color]

Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bnjuku$arb$1@hanover.torolab.ibm.com>...[color=blue]
> If you use DMS with files, you can benefit from OS file system caching,
> I believe. The restriction you're talking about is only DMS with raw
> devices.
>
>[/color]
Correct.

Bernard Dhooghe

On 28 Oct 2003 06:50:31 -0800, nomen@attglobal.net (Bernard Dhooghe)
wrote:
[color=blue]
>Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bnjuku$arb$1@hanover.torolab.ibm.com>...[color=green]
>> If you use DMS with files, you can benefit from OS file system caching,
>> I believe. The restriction you're talking about is only DMS with raw
>> devices.
>>
>>[/color]
>Correct.
>
>Bernard Dhooghe[/color]

Except we did use DMS tablespaces without raw devices and didn't seem
to be benefiting from significant file caching,

Cheers,

Jeremy