How does a bufferpool run out of pages.

[aczapran]

Everything I read on DB2 indicates that the bufferpool is a pool of
database pages used to speed up database access. It is controlled by
us, when we specify how many pages we would like to have allocated.

My question is:
1. Under what circumstances can the buffer pool run out?
Its my understanding that we would go into synchronous read/write mode
if all pages were dirty and we needed to read in a new page. In this
case, why would it fail.

The error I get is:
[IBM][CLI Driver][DB2/NT] SQL1218N There are no pages currently
available in bufferpool "4096". SQLSTATE=57011

The help documentation does not tell me what the "4096" bufferpool is.

NOTE: I have 2 databases defined in 1 instance. When 1 user only runs
applications, there is no problem. If another user uses the other
database, then we usually get this problem.

The documentation also makes note of the parameter: AVG_APPLS
-it implies that the optimizer uses this parameter to determine how
much of the buffer pool to use. What does this really mean. If the
optimizer thinks it can use 80% of the buffer pool and then it can't
get it, then it may fail? Is this possible?

Thanks for any suggestions.

[Mark A]

<ac******@gmail.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...

Everything I read on DB2 indicates that the bufferpool is a pool of
database pages used to speed up database access. It is controlled by
us, when we specify how many pages we would like to have allocated.

My question is:
1. Under what circumstances can the buffer pool run out?
Its my understanding that we would go into synchronous read/write mode
if all pages were dirty and we needed to read in a new page. In this
case, why would it fail.

The error I get is:
[IBM][CLI Driver][DB2/NT] SQL1218N There are no pages currently
available in bufferpool "4096". SQLSTATE=57011

The help documentation does not tell me what the "4096" bufferpool is.

NOTE: I have 2 databases defined in 1 instance. When 1 user only runs
applications, there is no problem. If another user uses the other
database, then we usually get this problem.

The documentation also makes note of the parameter: AVG_APPLS
-it implies that the optimizer uses this parameter to determine how
much of the buffer pool to use. What does this really mean. If the
optimizer thinks it can use 80% of the buffer pool and then it can't
get it, then it may fail? Is this possible?

Thanks for any suggestions.

Issue the following SQL statement on each of your two databases and post the
results here:

db2 select * from syscat.bufferpools

Most likely you just need to increase the size from the default, which is
ridiculously low.

Also, please indicate the amount of real memory installed on the database
server.

[Josh Tiefenbach]

My question is:

1. Under what circumstances can the buffer pool run out?

You'll get a -1218 when all the pages in the bufferpool are in use and
cannot be evicted in order to bring in a new page.

By 'in use', I mean being actively locked in memory by a query.

The help documentation does not tell me what the "4096" bufferpool is.

Bufferpool 4096 is one of the so-called 'hidden' bufferpools that are
always created on db startup. Its only 16 pages big, so its not all
that surprising that you'd start getting BP full errors.

NOTE: I have 2 databases defined in 1 instance. When 1 user only runs
applications, there is no problem. If another user uses the other
database, then we usually get this problem.

What it sounds like is happening is that db #1 is sucking up all the
memory available on the machine. When a user connects to db #2, DB2 is
unable to allocate memory for the bufferpools in db #2, instead
assigning each tablespace to use the hidden bp's.

Which then leads to BP full messages.

The documentation also makes note of the parameter: AVG_APPLS
-it implies that the optimizer uses this parameter to determine how
much of the buffer pool to use. What does this really mean. If the
optimizer thinks it can use 80% of the buffer pool and then it can't
get it, then it may fail? Is this possible?

I'm not quite certain on how the optimizer models bufferpool usage - I
believe (but am liable to be wrong :) that its mostly used to try to
figure out whether to introduce temp spills into the plan - ie if it
knows that it needs X pages, but the BP is only Y pages big, then some
temps will be introduced.

In any case, DB2 will mediate bp usage between applications, swapping
pages in and out of memory as required. The only time the query will
fail (modulo other resource issues) is, as I mentioned, if every single
page in the BP is being actively used by another query, and cannot be
evicted.

jsoh

[aczapran]

Excellent comments. Thanks for looking into this... But I have
additional information that my help further refine our problem.

Buffer pool is supposed to be a memory backing for a page system. Its
purpose is to speed up retrieval of data. Thus how can you lock a
buffer pool. It should spill to disk. Yes, it will be very slow, but
it does make sense. Locking a page is interesting. Why would this be
required, especially, since buffer pools are defined per database, and
in our case I have 1 user in db-1 and 1 user in db-2.
(These are just discussion points.. I believe you are absolutley
correct about the reason, its just not clear as to why this would
happen.)

Your comment:

In any case, DB2 will mediate bp usage between applications, swapping
pages in and out of memory as required. The only time the query will
fail (modulo other resource issues) is, as I mentioned, if every single
page in the BP is being actively used by another query, and cannot be
evicted.

Interesting since there is only 1 active user in this db. Thus there
is no other query.
I started a command window and tried to manually connect to db-2 while
a user was successful into db-1. The error I get was:
SQL1478W The defined bufferpools could not be started.... Exactly
like you implied.
Now we to further diagnose why I could not get the space. Details
follow.

System memory: 3.25 gb
Instance memory: 6600 x 4k = 27,033,600 (round to 27 meg)
Db-2 memory (from configure parms) = 449,196 * 4k = 1,839,906,816 = 1.8
G (rounded)(AUTOMATIC allocation)
db-1 memory exactly like db-2 above 1.8 G (AUTOMATIC allocation)

We see that 1.8+1.8 = 3.6 Gig which is more than my physical memory,
but DB2 is setup for AUTOMATIC database_memory allocation, thus I would
assume it would reassing the value.
Ideally, this is what we would like. If there is 1 database, then
grab as much as possible for this 1 db, but, if we need to
start/activate another db, then I thought this automatic would
negotiate between the 2 for a middle of the road value between the 2.
Additionally, no where does it say, you need the physical memory to
back up this number. (like it explicitly states for bufferpool
allocations).

I check the bufferpools for both databases.
Db-1 wants: 270,295 * 4k = 1,107,128,320 (or 1.1 gig rounded)
db-2 exatcly the same.
thus 2 bufferpools of 1.1+1.1 = 2.2 gig required.
- now the docs says this memory must be available or it will default to
the smaller internal bufferpoosl.

I have 3.25Gig on this machine thus this should be potentially
available and is, when I checked task manager:
Task manager reports:
(with db-1 and db-2 both active, but db-2 cripled)
Total memory: 3.2
available : 1.9 (db-2 wanted 1.1 gig only... why could it not get
it... all system overhead is already built in to this, since db-2 is
started with the small buffer pool)
And the db2syscs.exe using: 1,192,502 K. (not the assumed 1.8 G
(rounded) values we would expect from the database_memory setting).

So with 1 full functional db-1 connection we only used 1.2 Gig and the
system still says there is 1.9Gig available.
Thus why did the second db connection fail to get the bufferpool for
the db.
I can fix the problem by forcing a smaller database_memory value (ie,
removing the automatic and specifying a smaller value), but this limits
the memory used when only 1 db is active.
Any suggestions. (it couild be the numbers are just too close to the
pysical limits of the machine, even though the math indicates I should
have room).
Thanks for all your comments.

[p175]

What OS are you using, because if it is Windows 32bit there could be
issues there.

[Mark A]

"p175" <td******@hotmail.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...

What OS are you using, because if it is Windows 32bit there could be
issues there.

On a 32-bit instance, there are limits on database memory that range from
about 1.1GB to 2GB, depending on the OS.

[Knut Stolze]

ac******@gmail.com wrote:

Excellent comments. Thanks for looking into this... But I have
additional information that my help further refine our problem.

Buffer pool is supposed to be a memory backing for a page system. Its
purpose is to speed up retrieval of data. Thus how can you lock a
buffer pool.

The buffer pool is not locked but rather the pages in the buffer pool.

It should spill to disk. Yes, it will be very slow, but
it does make sense. Locking a page is interesting. Why would this be
required, especially, since buffer pools are defined per database, and
in our case I have 1 user in db-1 and 1 user in db-2.

A page _must_ be pinned in memory while DB2 reads data from the page or
writes data to it. Otherwise, the page could be evicted while the
read/write occurs and your whole system would become corrupt. Now it
depends on the amount of work that has to be done on a page and also what
other related (or unrelated) work is going on at the same time.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Josh Tiefenbach]

Buffer pool is supposed to be a memory backing for a page system. Its

purpose is to speed up retrieval of data.

That is correct.

Thus how can you lock a
buffer pool. It should spill to disk.

Err. I'm not quite sure what you're referring to by 'locking a buffer
pool'.

Locking a page is interesting. Why would this be
required,

For example, when an change is made to a page, it needs to be locked so
that other applications cant simultaneously make changes, possibly
overwriting changes that had been made.

'Locking' is technically an incorrect term, as it implies that the
normal db locks are used - they're not. Its a more lightweight, finer
granularity mechanism.

especially, since buffer pools are defined per database, and
in our case I have 1 user in db-1 and 1 user in db-2.

A bufferpool in db-1 has nothing to do with a bufferpool in db-2.
Except if the memory allocated in db-1 results in insufficient memory
being available when db-2 starts up to bring up those bufferpools.

Your comment:

In any case, DB2 will mediate bp usage between applications, swapping
pages in and out of memory as required. The only time the query will
fail (modulo other resource issues) is, as I mentioned, if every single
page in the BP is being actively used by another query, and cannot be
evicted.

Interesting since there is only 1 active user in this db. Thus there
is no other query.

Yes. But its a 16 page bufferpool. Thats insanely small. Its trivial to
craft a query that'll need that many pages simultaneously.

I check the bufferpools for both databases.
Db-1 wants: 270,295 * 4k = 1,107,128,320 (or 1.1 gig rounded)

This calculation is incorrect. There is overhead associated with a
bufferpool (ie, size of descriptors, other metadata) which is not
reflected in the actual page count. I dont have the fudge factor handy
at the moment - but I dont believe that it would account for more than
a 10% increase.

jsoh

[AncrewC]

Can anyone tell me why they think there is a limit on database memory?
- Yes I am using Windows Server 2003
- Where did you read this? Is it in the IBM docs or external.
I read that domain controllers have a limit on their internal database
maximums(ie. used by the domain controller), but this does not apply to
user applications such as db2 or SQL server.

What OS are you using, because if it is Windows 32bit there could be
issues there.

>On a 32-bit instance, there are limits on database memory that range from
about 1.1GB to 2GB, depending on the OS.

Otherwise, I guess there is no definite answer on how automatic memory
allocation for the database_memory setting really works and why it
could not obtain the memory I requested.

[Knut Stolze]

AncrewC wrote:

>>On a 32-bit instance, there are limits on database memory that range from
about 1.1GB to 2GB, depending on the OS.

Otherwise, I guess there is no definite answer on how automatic memory
allocation for the database_memory setting really works and why it
could not obtain the memory I requested.

These limits are either imposed by the operating system or by DB2 itself.
Usually, it is the operating system that does not allow DB2 to allocate
more than a certain amount of memory. And automatic memory management can
only operate with those limits.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany