Big and Slow

All too often, folks will take a broad-brush “bigger is better” approach when sizing, configuring, and tuning databases.  After all, if 5 is good, 10 must be better, right?  Problems from this brute-force approach can show up quickly with “not enough memory” error messages, excessive paging, and slow start-up. But sometimes bad results can be delayed or indirect.  DBA “coolwinds” points out how high CPU utilization can result from an oversized tempspace bufferpool:

If a temporary tablespace has a very large bufferpool, and temporary tables are often dropped, CPU usage might be high. (Even if your application does not explicitly drop temporary tables, DB2 might still create and drop temporary tables internally in normal query processing.)

He explains why (dirty list scanning) and how to correct it (create a smaller bufferpool dedicated to the temporary tablespaces).

Tools make it easy to view bufferpool sizes, but you can always query the system catalog to check:

select tbspace, datatype, t.pagesize, bpname, npages
from syscat.tablespaces t, syscat.bufferpools b
where t.bufferpoolid = b.bufferpoolid
order by datatype, tbspace

Right-sizing can be a challenge. Fortunately, for DB2 9.1 and higher, you can use automatic sizing for many things and let STMM do the work.  When overriding, it helps to ask autoconfigure for its opinion.

Share This:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
  • Google Buzz
  • RSS