Re: heavy inserts and bufferpool

[Mark A]

<dc********@aim.comwrote in message news:fu*********@drn.newsguy.com...

>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.

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)

[Mark A]

<dc********@aim.comwrote in message news:fu********@drn.newsguy.com...

In article <dS******************@bignews9.bellsouth.net>, Mark A says...

>>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)

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.

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.

[Mark A]

<dc********@aim.comwrote in message news:fu*********@drn.newsguy.com...

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.

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

[ajstorm]

On Apr 22, 7:00 pm, "Mark A" <[e-mail]:

<[e-mail] in messagenews:fu*********@drn.newsguy.com...

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.

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

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