473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Re: heavy inserts and bufferpool

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

Jun 27 '08 #1
3 4940
<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.
Jun 27 '08 #2
<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
Jun 27 '08 #3
On Apr 22, 7:00 pm, "Mark A" <nob...@nowhere.comwrote:
<dcrunch...@aim.comwrote 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

Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul | last post by:
Hi, is there any way to know the content of the bufferpool : - space usage not present in snapshot - tables actually cached in and number of pages, same for index ... thx
1
by: Christian Berg | last post by:
Hi, I have got a problem with resizing the bufferpool of a DB2 v.8.2 instance. The DB2 runs on an AIX 5.x platform. Problem is that an "ALTER BUFFERPOOL ..." command is not persistent if...
5
by: Hemant Shah | last post by:
Folks, I am not sure what I am doing wrong, but We have an transaction that does some serious calculation on small chink of data over and over again. It selects few rows from the table...
20
by: Hemant Shah | last post by:
Folks, I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create? I tried to create a 4GB bufferpool db2 complained that is cannot allocate enogth memory. I have 16GB on this...
3
by: dotyet | last post by:
Hi Everyone, I am in a very strange situation, and am looking for suggestions to tackle it. I have a 10 gig database on 64-bit windows 2003 running platform. The database has about 5 gigs of...
1
by: Raja Shekar | last post by:
HI Every body , I would like to know whether is it mandatory to give Tablespace page size and Bufferpool page size equal..? i also heard like while creating tablespace if pages size of tablespace...
0
by: Ian | last post by:
dcruncher4@aim.com wrote: 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...
3
by: Amber | last post by:
We are using 8.2.9 Windows 64 edtion, in one of our projects we need to recreate a few lager tables which have many millions of rows each, we have used concurrent Java threads to read from data...
3
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.