Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    111

    Unhappy Unanswered: DB2 Alter bufferpool

    Hi,

    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?

    Thanks
    Raj

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Raj
    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

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2004
    Posts
    111
    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.

    Thanks
    Raj

  4. #4
    Join Date
    Sep 2003
    Posts
    237
    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.
    mota

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •