DB2 : The Effect of 0 Buffers

by bitznbitez

The effects of adding buffers to a database are well understood.   But have you ever seen what happens when you take all buffers away from a database ?     The benefit of caching little things like the system catalogs and related cannot be overstated.   Here I am measuring a count(*) of a large table I have created.

By increasing memory beyond the ability of my system to handle things I was able to prevent the bufferpool from starting.   This results in db2 using one of the hidden bufferpools, in this case IBMSYSTEMBP4K which has 16 pages total.   Sixteen pages being close to 0 pages I called this 0 pages.

The chart above shows response times after each bufferpool size has been modified.   Although not required I recycled the instance after every bufferpool modification so the first run would always be without the benefit of any cached data.   I also ensured my tablespace was NO FILE SYSTEM CACHING so linux wouldn’t keep anything cached either.

You notice that the empty cache times ( First Run ) are the same no matter the cache size except when it is 0.   It is amazing db2 even comes up and runs at all without the bufferpool allocated.   By the time I added 200,000 buffers my cache was holding most of my table as the graph also shows.

Thus concludes this evening exploration of running db2 sans bufferpool.