IDS Forum

Re: BUFFERPOOL Configuration

Posted By: BENJAMIN THOMPSON
Date: Friday, 1 June 2018, at 10:17 a.m.

In Response To: BUFFERPOOL Configuration (LESTER POLICARPIO)

It is a great video. Obviously these are just examples.

1. The idea is to flush dirty pages to disk as fast as possible using the LRU mechanism. "1" and "2" are fairly aggressive values so the LRUs will be working hard. I did my own testing on this over 10+ years ago and the constant LRU activity does have a performance impact. However, data has to be flushed to disk somehow and there is a trade-off. The benefit of an aggressive LRU set up is shorter checkpoint interval. At checkpoint all dirty pages are written to disk. Keeping checkpoints very short used to be extremely important in the old days (version 10.00 and earlier) when checkpoints blocked server activity. Lester in the video recommends (for an OLTP system) keeping chunk writes low to avoid writing a lot of data out at the checkpoint, which has the potential to block (although rare) and dump a lot of writes to disk in a short space of time. If you have a modern SAN with a big write cache this won't be a problem and in this case I'd go against Lester's recommendation even for an OLTP system and allow a lot of writing at checkpoint time. If you don't have such a set up and writing a large amount of data quickly could impact read performance on your OLTP system, you'll follow what Lester says and trickle it through to disk using the LRU mechanism.

2. 50/60 is fine as long as you don't see performance any degradation during checkpoints. As discussed above this will depend on your throughput and storage.

3. On AIX the default page size is 4 kB so you must have a 4 kB bufferpool. Optionally you have 8, 12 and 16 kB ones as well. They will only be used if you have dbspaces using these page sizes.
I would say in general larger page sizes are better especially for indices. They also permit larger sized tables which could be especially important if you have workgroup edition (no partitioning).
Exceptions to this are if you a compress table or your table has a small row size: in these cases you may reach the 255 rows/page limit without filling the page completely, wasting space.

Ben.

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.