IDS Forum

Re: BUFFERPOOL Configuration

Posted By: ART KAGEL
Date: Friday, 1 June 2018, at 12:50 p.m.

In Response To: BUFFERPOOL Configuration (LESTER POLICARPIO)

Let me answer your questions inline below:

Questions :

1. Coincidentally we are running on AIX with OLTP System so item #2 would be a great fit for us. But why is the min and max lru set to 1 and 2 respectively? does it mean 1% and 2%? Currently ours are set to 50 min and 60 max.

Ans: Benjamin's response is a good one. I would add: The trade off between LRU writes between checkpoints and checkpoint writes (aka chunk writes) is that even with the later releases (11.10+) that have non-blocking checkpoints, the massive IOs during a checkpoint can cause slowdowns in transaction performance on a busy OLTP system. My recommendation, based on benchmarking and client experiences, is to balance LRU Writes and Chunk Writes (see onstat -F) such that between 30% and 70% of writes are LRU writes. In the old days we wanted 70% or more LRU writes because of checkpoint blocking which is rarely a concern (see onstat -g ckp output to see if you are getting any sessions blocked and for how long). However, the severity of the checkpoint and transaction slowdown caused by too many Chunk Writes will depend on your system, so set to 50 & 60 and adjust it down until you are happy with the balance and performance effects.

2. If I will be adapting #2 how can I check if the 50/60 we have is sufficient or if we need to change to 1/2?

Ans:See #1 Basically watch onstat -F and onstat -g ckp

3. What are the PROS/CONS of adding another BUFFERPOOL (instead of the default 1)

Ans: I assume you are not talking about the "BUFFERPOOL default,..." entry which is ONLY used when you create a new dbspace with a pagesize for which you do not already have a size specific BUFFERPOOL entry. So, why use page sizes other than the default for your system (4K for AIX, Mac, & Windows; 2K everywhere else)? Several reasons:

- If the pagesize is too small for the length of the rows in some tables causing those rows to extend out to remainder pages or even to multiple full pages.

- If the number of pages in the table is approaching the partition page limit of 2^24 pages (16,777,216) and you do not want to partition the table.

- If the size of the row is such that there is significant wasted space on every page. Example: a 1500 byte row on a 2K page waste 520 bytes per page (and also per row in this example). Put that row in a 6K page and it wastes only 423 bytes per 6K page or only 106 bytes per row. On a 14K page that row size wastes 811 bytes but divided over the 9 rows that fit on a page that's only 90 bytes per row.

- Indexes do indeed perform better on wider pages with 16K pages performing best for all but the smallest indexes.

Messages In This Thread

• BUFFERPOOL Configuration
  LESTER POLICARPIO -- Friday, 1 June 2018, at 3:19 a.m.
  • Re: BUFFERPOOL Configuration
    BENJAMIN THOMPSON -- Friday, 1 June 2018, at 10:17 a.m.
  • Re: BUFFERPOOL Configuration
    ART KAGEL -- Friday, 1 June 2018, at 12:50 p.m.
    • Re: BUFFERPOOL Configuration
      ART KAGEL -- Friday, 1 June 2018, at 12:53 p.m.
  • Re: BUFFERPOOL Configuration
    LESTER POLICARPIO -- Monday, 4 June 2018, at 12:25 a.m.
    • Re: BUFFERPOOL Configuration
      BENJAMIN THOMPSON -- Thursday, 7 June 2018, at 8:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.