Database design will usually affect I/O, object availability, and application throughput. Figure 1 lists these resource constraints, with common root issues and the typical fixes or workarounds implemented.
In Figure 1, notice the presence of partitioning in the typical fixes column. Partitioning schemes greatly influence the options a DBA has for tuning. Some schemes may prevent certain tuning tactics, while others are more accommodating.
The way a table and its indexes are partitioned affects more than just resource constraints. Partitioning usually determines backup, reorg, runstats frequency, and data purge strategy; it can also be used as a performance tool to distribute activity across volatile pagesets to avoid hot spots.
What specific measures can we take during database design or application tuning to minimize I/O, availability, and throughput constraints? The biggest issue will be data distribution and how activity is distributed across the pagesets. There are many different models and examples of this; we’ll consider only a few, but the principles are valid across most models.
Insert Hot Spots
Sometimes activity concentrated at a single point is a good thing. Consider a table with a sequentially ascending clustering key that experiences frequent row inserts. All new rows can be added at or near the physical end of the table space without interfering with each other since DB2 prefers to ensure that a row insert doesn’t wait (or timeout) rather than insist on placing the row on the “best” page. If most table activity is these inserts, then concentrating activity at one point may be acceptable.
When we have a high insert rate of rows that are evenly distributed across a table we may encounter resource problems. If every row is inserted on a page different from the last, then each row requires a getpage and a lock (usually a page lock). As the number of getpages and locks per transaction increases, the number of physical I/Os tends to increase. In addition, more pages are locked. This can lead to excessive synchronous I/Os, excessive page locking, long transaction execution times, and increased chance of deadlock or timeout. This can be somewhat annoying in an online environment, as the victim of the timeout or deadlock is rolled back by DB2, requiring either failing the transaction or re-executing it.
Several database designs address various aspects of this situation. The most common is a “rotating” partitioning scheme that focuses new row inserts in the last logical partition coupled with purge logic to remove or archive old data from earlier partitions. At some point, you can implement a partition rotation process using SQL similar to the following:
ALTER TABLE <table-name> ROTATE PARTITION