var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: DB2 Alter bufferpool
We have total memory of 32gb
BP_ALL bufferpool = 160000 16k pages
BP_Temp bufferpool = 60000 16k pages
We reduced system memory to 16gb.
Altered BP_ALL bp to 80000 16k pages with default immediate
Altered BP_temp bp to 30000 16k pages with default immediate
I think alter bp is dymanic in v8.2 (AIX 5.2)
But when we try to issue the query or load,
System iowait is around 80-90% and paging space around 60%.
Usually the below statement takes 2 hours to complete with the original conf of 32gb memory and ....
INSERT INTO X.CURR SELECT * FROM Y.CURR <-- where 290 millions rows present in Y.CURR.
After the alter the same statement took 22 hours still it completed 1/4th.
Do we need to recycle the instance, once we alter bp?
Not able to comment on paging etc ..
to know if your bp change has happened, you may do 'db2 get snapshot for bufferpools on <dbname> .. You will find the information you need in the output
Visit the new-look
, register to gain access to the excellent content.
Once I lowered the BP size, immediately
I took the BP snapshot and it shows the Lowered BP size.
Still, performance is very poor. I think agents and bp fills/tightens up the 16gb memory thinking of 32gb.
have you looked at the system with NMON,or TOPAS?What other applications,databases are running on the system? What does vmo -L say?
When you shrink the bufferpool, no guarantee thet memory segments were released to OS; an OS shutdown and startup will . Increasing NUM_IOCLEANERS and maybe NUM_IOSERVERS may help; also look at alter bufferpool numblockpages ..; its a sequential read; bufferpool is fragmented; would help to have contiguous large blocks.