Input/Output Performance

[ Team LiB ]

Input/Output Performance Typically, performing I/O is the slowest function a database needs to complete. DB2 provides several features to eliminate I/O bottlenecks, including Prefetching data pages into memory in anticipation of the pages being needed Performing I/O in parallel by prefetching several pages of data at a time Fetching block-related pages during the prefetch Validating constraints after loading data instead of while the data is being loaded Creating more than one buffer pool to store data pages and set configuration parameters to ensure that data pages are written to disk in a timely manner Indexes Indexes can dramatically increase performance by reducing the need for table scans or the number of columns retrieved for a given query. Indexes perform best when the values are unique (high cardinality) and are placed on fast disk devices. The Index Wizard can assist you in creating an index. To use the Index Wizard, open the Control Center and right-click the Indexes folder. Then select the Create option to display the Create Index window (see Figure 20.24). This example creates an index on the Staff table, by selecting the ID column for an index. You can consider the following options when creating an index: Figure 20.24. Create Index window. Unique� Creating a unique index can improve query performance by potentially avoiding sorts. Cluster� This index parameter can assist insert performance by having the new inserted rows clustered according to an index. Insert performance is improved, page splits are reduced, and the need for reorganizing data is reduced. Allow reverse scans� Indexes can be defined in ascending or descending order. This parameter allows index scans to occur in the reverse direction from the way the index was defined; otherwise, the index is not used if the query is requesting the data in reverse order of the defined index. Free space� This parameter specifies the PCTFREE keyword value as a percentage of how much free space should remain on the index page, to allow for inserts to be applied later. This can improve performance for applications that will insert data. Minimum Amount of Used Space� The parameter specifies the MINPCTUSED keyword value as a percentage of the index page. When this threshold is exceeded, DB2 will automatically try to determine whether two adjacent leaf pages can be merged. This process is known as online index defragmentation. The following are some recommendations you should keep in mind when creating indexes: Be selective about which columns you choose to create an index because there will be overhead for applications that update data. Select columns that are used often to join tables. For tables defined with referential integrity, creating indexes on the foreign keys improves the performance of delete and update operations on the parent table. If your index is made of multiple columns defining a partial index, specifying a subset of the existing index generally does not improve performance. Buffer Pools Each table is associated with a buffer pool. When you create a table space, the default buffer pool (IBMDEFAULTBP) is used. You can modify the size of the default buffer pool. The default of one buffer pool will usually suffice, but for a higher level of database tuning, you can create additional buffer pools and assign table spaces to individual buffer pools. For example, you could create four buffer pools, one each for catalogs, temporary space, indexes, and data. The extended buffer pool allows DB2 to take advantage of large amounts of memory. You can create additional buffer pools in the Control Center by right-clicking the Buffer Pools folder and selecting Add from the pop-up menu. Day 10, "Creating Table Spaces," provides details on creating buffer pools. To alter the size of a buffer pool beyond what's recommended by the Configuration Advisor, select the Buffer Pool folder from the object list in the Control Center. Right-click IBMDEFAULTBP and select Alter from the pop-up menu. Modify the Size in 4KB Pages field and click OK. The change will take effect the next time the database is started. Asynchronous Buffer Writer To read or modify data, pages of data are moved from disk to the buffer pool. If a page has been modified, it should be written back to the disk. The purpose of the buffer writers is to write out most changed pages to disk, so regular database agents always find empty slots and don't have to hold the transaction to write pages out. This means that agents won't wait for additional I/O, and queries should run faster. The buffer writers run in parallel with the database agents. Pages are written from the buffer pool to disk when the percentage of space occupied by changed pages in the buffer pool has exceeded the value specified by the CHNGPGS_THRESH database configuration parameter. You can also configure the number of page cleaners that are used for the database. Page cleaner agents perform I/O that would normally be done by database agents. As a result, your applications can run faster because transactions are not forced to wait while their database agents write pages to disk. To set the number of page cleaner agents, use the NUM_IOCLEANERS database configuration parameter. Row Blocking DB2 Universal Database uses row blocking to provide transmission of data in blocks, instead of one row at a time. A block is a group of rows returned from a local or remote database in response to a FETCH request from an application. Performance is usually enhanced by reducing the number of requests made against the database manager. There are three types of row blocking: UNAMBIGUOUS� Causes blocking for read-only requests ALL� Causes blocking for read-only and ambiguous requests NO� Does not block any requests Row blocking is specified at precompile time or when an application is bound to the database. If no blocking method is specified, the default UNAMBIGUOUS is used. To see the isolation level used for a package, select the package folder in the object tree in the Control Center. The right panel provides details on the package, including the blocking type used: The Database Manager Configuration parameter aslheapsz specifies application support layer heap size for local applications. The Database Manager Configuration parameter rqrioblk specifies the size of the communication buffer between remote applications and their database agents on the database server. To specify row blocking, follow these steps: Use the values of the aslheapsz and rqrioblk configuration parameters to estimate how many rows are returned for each block. In both formulas orl is the output row length in bytes. Use the following formula for local applications: Rows per block = aslheapsz * 4096 / orl The number of bytes per page is 4096. Use the following formula for remote applications: Rows per block = rqrioblk / orl To enable row blocking, specify an appropriate argument to the BLOCKING option in the PREP or BIND commands. If you do not specify a BLOCKING option, the default row blocking type is UNAMBIG. For the command-line processor and call-level interface, the default row blocking type is ALL. Prefetching Data Pages Prefetching data pages into the buffer pool can help improve performance by reducing the time spent waiting for I/O to complete. To prefetch pages means that one or more pages are retrieved from disk in anticipation of their use. Prefetching is started when DB2 determines that sequential I/O is appropriate and prefetching may help to improve performance. There are two types of prefetch: Sequential prefetch� Reads consecutive pages into the buffer pool before the pages are required by the application. When the access plan looks sequential, the DB2 engine automatically begins sequential prefetch. Having DB2 activate or deactivate sequential prefetching as necessary is known as sequential detection. To specifically enable sequential prefetching, set the SEQDETECT database parameter in the database configuration file to yes. List prefetch, or list sequential prefetch�Provides a way to access data pages efficiently, even when the data pages needed aren't consecutive. If access to the tables includes many queries or transactions that process large quantities of data, prefetching data from the tables may provide significant performance benefits. A DMS table space using multiple device containers in which each container is on a separate disk offers the best potential for efficient prefetching. Parallel Input/Output Many I/O operations can be performed in parallel on behalf of a single query. This can improve application response time, especially when the application performs a table scan. You enable parallel I/O for a database by setting the NUM_IOSERVERS configuration parameter that controls the number of prefetch servers created. This parameter is set in the database configuration file. Configuring enough I/O servers can greatly enhance the performance of queries. Having extra I/O servers does not hurt performance because these extra servers are not used and their memory pages are paged out. Big Block Reads Using big block reads allows several disk pages to be read by using a single I/O operation. Reading several disk pages at a time reduces the CPU usage and elapsed time for I/O, and improves query response time. Big block reads are used by the database manager every time prefetch is done. They may also be used for list prefetch if multiple pages being read belong to the same table space extent. DB2 determines when it is necessary to use big block reads.

[ Team LiB ]