heavy inserts and bufferpool
in [DB2]
|
Prev: OSERR : 5 "Access is denied."
Next: Reorgchk, Reorg, Runstats not having any effect after completion. Why is this?
From: Mark A on 20 Apr 2008 17:48 <dcruncher4(a)aim.com> wrote in message news:fuevgh02ug6(a)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)
From: Serge Rielau on 21 Apr 2008 06:50 dcruncher4(a)aim.com wrote: > 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 -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Serge Rielau on 21 Apr 2008 15:28 dcruncher4(a)aim.com wrote: > In article <6739rqF2n9jigU2(a)mid.individual.net>, Serge Rielau says... >> dcruncher4(a)aim.com wrote: >>> 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. > > 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. Offline or here. Leave the alternate page cleaning proposed by Mark in place though. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
From: Mark A on 21 Apr 2008 21:09 <dcruncher4(a)aim.com> wrote in message news:fui7er03bt(a)drn.newsguy.com... > In article <dSOOj.28633$Q52.8397(a)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.
From: Ian on 22 Apr 2008 01:50
dcruncher4(a)aim.com wrote: > 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 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. |