heavy inserts and bufferpool

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dcruncher4@aim.com - 20 Apr 2008 09:39 GMT

I am new to DB2.

version info DB2 9.5 , Linux

some more info:-
create bufferpool bp1 size 140000 pagesize 8K ;
update db cfg for test using CHNGPGS_THRESH 5 ;
update db cfg for test using NUM_IOCLEANERS 3 ;
update db cfg for test using NUM_IOSERVERS 3 ;

I am tying to convert a perl program to multi threaded.
To test the difference I created a table and loaded 100000 rows.
It loads fine as a single thread program. Loads it in about 15 seconds.

When I run it as multi threaded with 3 threads and all 3 of them inserting, I
frequently
get this message:-

DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL1218N There are no
pages currently available in bufferpool "4097". SQLSTATE=57011
DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL1218N There are no
pages currently available in bufferpool "4097". SQLSTATE=57011

It seems the rate of insert is much higher than the ability of DB2 to clean the
dirty pages
out and frequently it reaches a point where it can't find a single free page to
load the newly inserted row. but why this is an error. I have informix
background and in informix we don't see such errors.

Once I change the code and insert a sleep time after every X number of rows
inserted,
the problem goes way. essentially giving the engine some time to flush the dirty
buffers.

Or may be I am missing out a basic thing.

thanks.

Reply to this Message

Mark A - 20 Apr 2008 22:48 GMT

>I am new to DB2.
>
[quoted text clipped - 40 lines]
>
> thanks.

I would try these:

db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON (this will override
CHNGPGS_THRESH)
db2set DB2_SKIPINSERTED=ON (this will reduce lock contention on inserts)
db2 update db cfg for test using NUM_IOCLEANERS 8
db2 update db cfg for test using LOGBUFSZ 256
db2 update db cfg for test using DBHEAP 2000 (unless it already at least
this high)

Reply to this Message

dcruncher4@aim.com - 21 Apr 2008 15:13 GMT

>db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON (this will override
>CHNGPGS_THRESH)
[quoted text clipped - 3 lines]
>db2 update db cfg for test using DBHEAP 2000 (unless it already at least
>this high)

I did it and it works. I still get this error but very rarely.
per a db2 dba, it is this which solved my problem.

db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON

I am going to test it again with everything at default setting,
and then set the above.

Reply to this Message

Mark A - 22 Apr 2008 02:09 GMT

>>db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON (this will override
>>CHNGPGS_THRESH)
[quoted text clipped - 11 lines]
> I am going to test it again with everything at default setting,
> and then set the above.

You probably don't want to use the default setting of 8 for LOGBUFSZ (at
least that was the default before version 9.5). For your high insert volume
application 256 should work better (and the LOGBUFSZ comes out of DBHEAP so
that needs to be increased by the same amount). I would also go with
NUM_IOCLEANERS of 8 or automatic. 3 is probably too low.

But I agree that for your symptoms, DB2_USE_ALTERNATE_PAGE_CLEANING=ON makes
the biggest difference.

Unless something is set to automatic from a fresh database create using DB2
9.5 (instead of an upgrade of database from a previous release), the
defaults are usually suspect, since many of them have not changed in about
10 years or more.

Reply to this Message

dcruncher4@aim.com - 22 Apr 2008 14:14 GMT

>But I agree that for your symptoms, DB2_USE_ALTERNATE_PAGE_CLEANING=ON makes
>the biggest difference.

I recreated the database with all default settings and changed only
the DB2_USE_ALTERNATE_PAGE_CLEANING=ON .

I inserted 2 million rows in 2 threads parallely. Only 4 times it
spewed out the error and only those 4 rows failed to insert.

Thanks Mark and Serge.

Reply to this Message

Mark A - 23 Apr 2008 00:00 GMT

> I recreated the database with all default settings and changed only
> the DB2_USE_ALTERNATE_PAGE_CLEANING=ON .
[quoted text clipped - 3 lines]
>
> Thanks Mark and Serge.

Ian mentioned in this thread that the bufferpool you created is too large
for the available real memory on your database server, and that is the
source of your problem. The bufferpool BP1 did not get allocated because of
memory limitations and the DB2 hidden (very small) 8K bufferpool was used
instead. This is the real source of your problem (although the other
recommendations will help speed things up).

If you do the following, it will probably fix your problem permanently:

db2 alter bufferpool bp1 size 30000;

Then (just to be safe):

db2stop
db2start

Reply to this Message

ajstorm@ca.ibm.com - 23 Apr 2008 14:12 GMT

> <dcrunch...@aim.com> wrote in messagenews:fukoc902976@drn.newsguy.com...
> > I recreated the database with all default settings and changed only
[quoted text clipped - 20 lines]
> db2stop
> db2start

Another option, of course, would be to enable self tuning memory for
your system.
Not sure if this is something that you've considered. To do that,
issue the following:

db2 connect to test
db2 update db cfg using SELF_TUNING_MEM ON DATABASE_MEMORY AUTOMATIC
db2 alter bufferpool bp1 size AUTOMATIC

Once this is done, you should no longer see any use of the hidden
buffer pools.

Thanks,
Adam

Reply to this Message

Serge Rielau - 21 Apr 2008 11:50 GMT

> update db cfg for test using CHNGPGS_THRESH 5 ;
> update db cfg for test using NUM_IOCLEANERS 3 ;
> update db cfg for test using NUM_IOSERVERS 3 ;

Have you tried just not touching these?
Here is the default setting I find on my laptop:
Changed pages threshold (CHNGPGS_THRESH) = 80
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC

Specifically note AUTOMATIC which means DB2 will take care of it.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

dcruncher4@aim.com - 21 Apr 2008 15:10 GMT

>> update db cfg for test using CHNGPGS_THRESH 5 ;
>> update db cfg for test using NUM_IOCLEANERS 3 ;
[quoted text clipped - 6 lines]
>
>Specifically note AUTOMATIC which means DB2 will take care of it.

well I started getting the problem even before I changed it.
In other words AUTOMATIC wasn't doing its job. I can test it
again by letting it being automatic and rerun my scripts.
Shall I contact you offline later on if the problem persists.
thanks.

Reply to this Message

Serge Rielau - 21 Apr 2008 20:28 GMT

>>> update db cfg for test using CHNGPGS_THRESH 5 ;
>>> update db cfg for test using NUM_IOCLEANERS 3 ;
[quoted text clipped - 12 lines]
> Shall I contact you offline later on if the problem persists.
> thanks.

Offline or here. Leave the alternate page cleaning proposed by Mark in
place though.
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Ian - 22 Apr 2008 06:50 GMT

> I am new to DB2.
>
[quoted text clipped - 18 lines]
> DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL1218N There are no
> pages currently available in bufferpool "4097". SQLSTATE=57011

The issue here is that you're hitting bufferpool 4097. This is the
"hidden" 8k bufferpools that will be used if your system does not have
enough memory to support the regular bufferpools. These bufferpools
are VERY small (64 pages, I think), so it's very easy to fill all of
the pages.

Your BP1 bufferpool is just over 1Gb in size. I don't know what other
bufferpools you've got, but check on the size of these and make sure
that your system has enough memory to support them.

When you do:

db2stop force
db2start
db2 connect to test

You should *not* get any error messages on the CONNECT statement. If
you do, you're oversized.

Reply to this Message