Database Implementation

For our DB2 tests, we needed to setup the database. Details on our database setup are below.

The database was created across all 15 ranks on SCSI disks while the logs are placed on a separate rank with ECKD disks. To enable parallelism, we allocated 15 containers for each table and index and placed each container on a SCSI disk from a different rank. Each rank contained eight SCSI disks of 47 GB size and two SCSI disks of 6 GB size. We created EXT3 file systems on all the disks and spread the large tables evenly across them. We created 14 database-managed table spaces with the NO FILE SYSTEM CACHING clause (see Direct I/O) - using a mixture of regular and large table spaces. Large table spaces, introduced with DB2® 9, differ from the regular type primarily in that they can store table objects that are thirty-two times larger in size compared to regular table spaces. Furthermore, large slots, which are also available, allow more than 255 rows per data page.

For the memory configuration, approximately 42 GB of RAM was allocated for the database memory. 37 GB of that RAM was used for buffer pools.

Another tuning implementation was the enablement of asynchronous I/O (AIO) for the page cleaners.

Asynchronous I/O

AIO permits a single application thread to overlap processing with I/O operations. In other words, the application thread does not have to wait for an I/O request to complete before it resumes its regular processing. AIO is enabled via the registry variable, DB2LINUXAIO.

Direct I/O

Direct I/O (DIO), or unbuffered I/O, is an I/O operation that avoids copying the data from a user space buffer to the page cache from the Linux® kernel. This requires that the application handle all I/O optimizations, for example, caching and request merging, by itself. DIO is only available with FCP disks and is enabled via create/alter table space commands using the NO FILE SYSTEM CACHING clause.

Workload Description

The workload used for this study models an order-entry wholesale part supplier business. It simulates users executing transactions against the database. These transactions include order entry and delivery, payment entry, order status queries, and stock level queries.