IBM TotalStorage DS6000 Series: Performance Monitoring and Tuning

428 IBM TotalStorage DS6000 Series: Performance Monitoring and Tuning

Figure 13-2 Allocating DB2 containers using a “spread your data” approach

In addition, consider the following:

򐂰 You may intermix data, indexes, and temp spaces on the DS6000 Ranks. Your I/O activity

will be more evenly spread and thus will avoid the skew effect, which you would otherwise

see if the components were isolated.

򐂰 For DPF systems, establish a policy that allows partitions and containers within partitions

to be spread evenly across DS6000 resources. You should choose a vertical mapping, in

which DB2 partitions are isolated to specific arrays, with containers spread evenly across

those arrays.

13.5.3 Use DB2 to stripe across containers

Use the inherent striping of DB2, placing containers for a tablespace on separate DS6000

logical disks on different DS6000 Ranks. This will eliminate the need for using underlying

operating system or logical volume manager striping.

Look again at Figure 13-2. In this case, we are striping across arrays, across disk adapters,

across clusters, and across DS6000 boxes. This can all be done using the striping capabilities

of DB2’s container and

shared nothing concept. This eliminates the need to employ AIX

logical volume striping.

13.5.4 Selecting DB2 logical sizes

The three settings in a DB2 system that primarily affect the movement of data to and from the

disk subsystem work together. These are:

򐂰 Page size

򐂰 Extent size

򐂰 Prefetch size

Container

1

Container

2

Container

5

Container

6

Container

7

Container

8

Container

3

Container

4

5

7

6

8

1

2

3

4

Chapter 13. Databases 429

Page size

Page sizes are defined for each tablespace. There are four supported page sizes: 4 K, 8 K, 16

K, and 32 K. Some factors that affect the choice of page size include:

򐂰 The maximum number of records per page is 255. To avoid wasting space on a page, do

not make page size greater than 255 times the row size plus the page overhead.

򐂰 The maximum size of a tablespace is proportional to the page size of its tablespace. In

SMS, the data and index objects of a table have limits, as shown in Table 13-1. In DMS,

these limits apply at the tablespace level.

Table 13-1 Page size relative to tablespace size

Select a page size that can accommodate the total expected growth requirements of the

objects in the tablespace.

For OLTP applications that perform random row read and write operations, a smaller page

size is preferable, because it wastes less buffer pool space with unwanted rows. For DSS

applications that access large numbers of consecutive rows at a time, a larger page size is

better, because it reduces the number of I/O requests that are required to read a specific

number of rows.

Extent size

If you want to stripe across multiple arrays in your DS6000, then assign a LUN from each

Rank to be used as a DB2 container. During writes, DB2 will write one extent to the first

container, the next extent to the second container, and so on until all eight containers have

been addressed before cycling back to the first container. DB2 stripes across containers at

the tablespace level.

Since DS6000 stripes at a fairly fine granularity (256 KB), selecting multiples of 256KB for the

extent size makes sure that multiple DS6000 disks are used within a Rank when a DB2

prefetch occurs. However, you should keep your extent size below 1 MB.

I/O performance is fairly insensitive to selection of extent sizes, mostly due to the fact that

DS6000 employs sequential detection and prefetch. For example, even if you picked an extent

size such as 128 KB, which is smaller than the full array width (it would involve accessing only

four disks in the array), the DS6000 sequential prefetch would keep the other disks in the

array busy.

Prefetch size

The tablespace prefetch size determines the degree to which separate containers can

operate in parallel.

Page size Maximum data/index object size

4 KB 64 GB

8 KB 128 GB

16 KB 256 GB

32 KB 512 GB

Tip: Experience indicates that page size can be dictated to some degree by the type of

workload. For pure OLTP workloads a 4 KB page size is recommended. For a pure DSS

workload a 32 KB page size is recommended. For a mixture of OLTP and DSS workload

characteristics we recommend either an 8 KB page size or a 16 KB page size.