Heavy INSERT Workloads
One of the most difficult performance tuning situations for the
DBA is that of heavy application workloads that do SQL inserts into a single
table. There are several potential issues to contend with, including “hot spots”
in the table where newly-inserted rows having similar keys should be added,
system logs filling with new changed data for recovery purposes, CPU and I/O
used to search for empty space in the table and index, and others.
In the past, DBAs have attempted to alleviate some of these bottlenecks
through creative database design techniques. One method is to physically
partition the table and randomly assign new rows to partitions, thus avoiding
heavy changes to a single place in the table. Another method is to define the
tablespace with frequent free space areas, initially empty of rows, so that
newly-inserted rows can be added there.
Db2 v12 provides a new method to alleviate some of the constraints
of a heavy insert workload. This is a new algorithm for determining where new
rows are to be inserted, and is called insert algorithm 2. Db2 can use this new
insert algorithm to speed up non-clustered inserts where the constraint is
multiple concurrent threads doing inserts associated with delays finding
available space for the new rows. While not a panacea for every situation, this
new algorithm does provide constraint relief in many cases.
Estimates made by IBM indicate that a table with a single index
can experience more than five million inserts per second using the new