3 More Ways to Improve Database I/O Performance

As I talked about last week, the application and database performance involves constantly analyzing different DB2 I/O aspects of your environment. By analyzing the DB2 I/O reports you’ll discover there are many different aspects to examine to optimize your environment, application workload, and processing.
The following are three more important items to analyze when tuning your database and application I/O performance.

1. Give priority to the DB2 system I/O activity. Using the daily DB2 I/O activity reports gathered through your DB2 performance monitor or storage monitor, do a detailed analysis of the I/O performance of DB2 system activity. If the DB2 system I/O performs poorly, the entire system and all of its applications will be delayed. Especially look closely at the I/O service time for all the logging structures, the BSDS, and the DB2 catalog data sets. All of these components should be getting the highest priority and best DB2 I/O performance within your environment.
2. Separate DB2 object types by first I/O type, object priority and object activity size. Segregating the DB2 I/O types is the most important tasks to insure that all the buffer pool I/O workloads are complimentary. By separating the objects along DB2 I/O types your buffer pools will cache more efficiently and you won’t have a random I/O buffer pool overwritten or flushed out by a sequential scan. Next, create several groups, separated and prioritized by your most to least important database application objects. Separate the tables and the indexes into different groups since their DB2 I/O patterns are usually different. Then separate the large database objects away from the smaller objects so that the big table activity does not dominate or flush smaller table buffer pool activity. These basic segregation and separation schemes are a starting point for improving your DB2 I/O performance.
3. Dig deeper into the path of the I/O. There are several technologies and components that are involved with your DB2 I/O performance. These components are z/OS I/O host connections through FICON channels, the storage device cache, and the solid state or storage device disks themselves. It is typical to have multiple connections and paths to the storage devices in the z/OS environment for failover and sharing storage. All of these shared storage devices within your performance scheme need to be monitored. All the storage vendors along with the z/OS environment have standard reports that monitor and document the performance of these pathway components. Talk with your storage administrator to get a glimpse of these hardware-manufacturer-supplied reports to see the daily and hourly average utilization of the channels, cache, and paths.

Sometimes these DB2 I/O issues are tough to understand within the complex, diverse and seasonal business cycles. Getting everything configured properly, setting up monitoring, and getting daily reports analyzed is a big task that takes persistent detecting to get to the bottom of the DB2 I/O performance issues. Use these tips from the last two weeks and at least you’ll be on the proper path to improving your environment.

Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, and President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.

• Sign up for the IDUG DB2 Technical Conference in Phoenix, Arizona this May 12-16th.
• Sign up for dinner with me during the IDUG Dine Around Dinner Thursday night.
• Also plan on attending my presentation 1221-F04 “Big Data Disaster Recovery Performance” Tuesday 4:30 at the IDUG conference.

Also the IDUG European conference is still accepting presentation abstracts to be potentially picked for the IDUG EU conference in Prague, Czech Republic November 9-14th.
For more details on any of these items go to www.idug.org.