DB2 Buffer Pool Tuning

Cache Pool:

The flush pool is an area in memory that DB2 will use to put data into the buffer pool to improve performance. The buffer pool is too small for each query to still operate on the disk, which does not achieve the buffering effect. The buffer pool is too large to exceed the operating system management limit and can cause errors in the database not being able to connect.

A buffer pool is associated with a table space with a data table, which is stored in the specified table space with the specified buffer pool for each table space. Because each data table stores a different amount of data, generally based on the maximum amount of data stored per record, we will specify 4k-32k different table spaces for the data table to store for optimal storage and performance, the buffer pool is similar. This is typically allocated when the database is created.

Early warning of the incident:

Application System program: usually login, home page, to-do list, such as large data volume module, Response time is long, take a few seconds to a few 10 seconds is possible.  Sometimes it is the peak of the user visit slow, off time and more normal. Operating System performance: typically the middleware server (was) system is normal, CPU and IO consumption will not last more than 50%, the system running process will not have a continuous wait. The database server is very busy, the CPU occupies more than 50%, tend to reach 90% or so, IO occupation may not be high. Judging from the system level, the performance bottleneck is on the database

Tuning ideas:

DB2 's performance and operating system, locks, buffer pools, indexes and other parameters, as well as SQL writing are very important, subject to personal knowledge, here is the main introduction buffer pool and index tuning methods.
Buffer pool adjustment is relatively simple, generally can adjust the buffer pool first, if the effect is not obvious, then adjust the index and SQL

switch Instance User ID  view cache pool related content Su db2inst1db2 connect to DB// View Cache pool Information DB2 "Select Bpname,npages,pagesize the buffer pool name (bufferpoolname) used by the from Syscat.bufferpools tablespace name (tablespacename), Table Space page size (tbspagesize) Number of buffer pools (bufferpoolpages), buffer pool page size data (bufferpoolsize) information

To view the table space and buffer pool information for the Mv_workitem table, the buffer pool used by the table that starts with the general "MV_" is the Focus object:

 db2  " select tabschema  Tableschemaname, tabname tablename, t.tbspace tablespacename,b.bpname bufferpoolname,  b.npages bufferpoolpages,  
 b.pagesize bufferpoolsize   from   syscat. Tables t ,syscat. Bufferpools b,syscat. tablespaces s   where    TabName  = " mv_workitem   "  and s.bufferpoolid  =b.bufferpoolid   and t.tbspace=s.tbspace " //" Span style= "COLOR: #008000" > note tableschemaname, Bufferpoolname, bufferpoolsize   

// To turn on the buffer pool monitor: using  // after the application reproduces the problem, check the snapshot of the buffer pool:get for

Check the DB2 buffer pool snapshot, focus on data and index logical/physical reading and writing, in general, in the case of sufficient buffer pool, physiical reads value into 0, and logical and reads value is very large, the case is not enough to use the problem, For example, the case is a 32k buffer pool snapshot of the XXX system:

bufferpool Snapshot bufferpool name=BF32//Buffer pool data Logical reads = 493907//Buffer Pool Data physical reads =Buffer Pool temporary data logical reads =129662Buffer Pool Temporary data physical reads=0Buffer Pool Data writes=1 //Buffer Pool Index Logical reads = 10302//Buffer Pool Index Physical reads = 122//If you find that the value of physical and logical reading is not significant, then use the following command to adjust the buffer pool size, generally can increase by about 2000 each time

DB2 ALTER bufferpool BF32 size 18000

The buffer pool adjustment takes effect immediately and does not require a restart of the database. It is important to note that the size of the buffer pool is limited by physical memory and operating system, and the total buffer pool size cannot exceed 1G under a typical 32-bit operating system. If, under this restriction, all buffer pools are not sufficient to achieve physical reads approaching 0, consider maximizing the buffer pool size (used by tables starting with MV, um, and so on) that has the greatest impact on the user experience. In theory, 64-bit operating systems can manage more memory space, so you can get better performance. The total size of the buffer pool, as shown below, is:

1x4+4x4+3x8+2.5x16+2.5x32+1x32=226m

Repeat the above steps to collect the snapshot information again:

Usingusing Bufferpool onget for bufferpools on pzbdw|grep-i   buffer|  // because the buffer pool monitor collects data that is self-enabled, it should be turned off and re-opened to collect the snapshot information once it has been adjusted for the correct situation

DB2 Buffer Pool Tuning