Last time I wrote about DB2 Checkpoint and index definitions over your busiest tables. This week I will discuss the old style of tablespace definitions and their potential performance impact.
Tablespaces Need to Be Reorganized
The first thing to look at is the current state of the database tables and indexes. Usually I find that there are many database objects in need of reorganization and normal on-going maintenance. This is indicated by the number of extents found on the each of the various tablespace and indexspaces which can usually be obtained through a standard data set extent report. This is also discovered by querying the DB2 catalog information and highlighting the number of FAROFFPOS rows. FAROFFPOS indicates that retrieving these data rows requires extra I/Os when referenced in the system because they are not stored on their optimum position within the database tablespace or indexespace. These FAROFFPOS rows are usually found within several large tablespaces and their indexes of database that have not had normal maintenance done on them during the normal business cycle.
All database tables and indexes need to be analyzed to understand their daily activity (insert, update and delete frequency) so that their free space can be adjusted through the FREEPAGE and PCTFREE DB2 allocation parameters. These parameters and space allocations needed to be adjusted to handle the workload for a period of six months without database reorganization. The analysis and adjustments will pay off in better system and application performance, while freeing staff from database object reorganizations for an extended period of time.
Simple Tablespaces Should be Changed to Segmented Tablespaces
Simple tablespaces are not going to be allowed in future release of DB2. Usually when I am doing a performance review I find a small number of tablespaces that were still defined as a simple tablespace type. These tablespaces needed to be redefined as segmented tablespaces to leverage the better space management and processing features of segmented tablespaces.
Given that the simple tablespace definition is going away, it is a good idea to change these database tablespaces before the migration requirement creeps up on you and messes up or constrains your schedule.
Tablespace Segment Size is Too Small
Sometimes when I am looking into the segment tablespace definitions I find bad definitions or tablespace definitions that have taken the defaults. When this happens I usually find database tablespace segment size of 4 pages which is too small for normal databases. This segment size is used in secondary allocation efforts and pre-fetch operations.
Having a small segment size limits the efficiency of pre-fetch operations and could require additional I/O during normal operations. It is best to have a large segment size of 32, if possible, depending on the number of partitions and whether or not you are using the new Universal tablespace type. When defining a regular segmented tablespace use a large segment size whenever possible to improve I/O and prefetch operations.
Minimal Partitioning
In some shops partitioning is not really used for the databases. In another performance review I found that a majority of the current database tablespaces used a segmented tablespace definition for handling data. There were a number of tables that had a large number of rows. Those tablespaces needed be analyzed to determine whether they should be redefined as partitioned tablespaces.
Partitioning these tablespaces would split up the data into a number of smaller data sets and spread out the I/O and locking within the database. Partitioning allows better data set and space management for isolating processing or data set secondary extents. Partitioning also enables the processing to possibly use parallel access to reduce the elapsed time of any long running batch processes.
It is best to partition tables that have over one million rows. The DB2 Catalog can easily be queried to determine tables with over one million rows that aren’t partitioned.
Table Reorganization Schedule
Maintenance procedures needed be put in place to regularly review and schedule database utilities and database tools against the production database environment. These schedules and tools are very important to the efficiency of the database system. Performance problems can be very disruptive to the business environment.
Analysis of the environment uncovered tablespaces and indexes in multiple extents in need of reorganization. Workflow analysis and database change statistics needed to be gathered to develop an efficient maintenance schedule. Also standard jobs, downtime maintenance windows and operational procedures were needed to minimize the business disruption impact.
In Summary
Make sure that your checkpoints are executing every 10 to 15 minutes. If not, adjust the size of our active log data sets to encourage the correct checkpoint span.
Honing in on your tablespace and table structures can give you lots of places to improve DB2 performance. Take a look at these areas in your own shop:
- Are there tablespaces and tables that need to be reorganized? Check for a large amount of data set extents and a large number in FAROFFPOS.
- Do you have any simple tablespaces that should be changed to segmented tablespaces for better performance? Are your segmented tablespaces properly sized?
- Do you have frequently updated tables with a large number of indexes? Double check to see if any of these indexes can be eliminated to improve performance.
- Are there large simple or segmented tablespaces that really should be redefined as partitioned? Check for tables with over a million rows.
- Do you have a reorganization schedule an appropriate freespace that keeps tables cleaned up with a minimum of outage?
See you next week when we continue on with our exploration of this fascinating case study.
________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.
Leave a Reply