I pushed my father in a wheel chair for the first time last month. As I looked at the man who was my Scout Master, who helped me achieve Eagle rank, who taught me to bowl, and inspired me with strong core values of helpfulness and community service, these wheel chair excursions marked a pivotal inspiration point for contemplating my own current health, and have given rise to my own self-improvement goals that you can read about in my personal blog. This year's new IDUG presentations and Ed Seminars will also include new information on DB2 Health monitoring. So, in this blog post, let's look into the health and fitness of your databases.

[ more .. ]

I was recently contacted by a DBA at a large insurance company. After upgrading from DB2 V8.2 to 9.1.0.3, the DBA activated the Self Tuning Memory Manager (STMM) in the hope that it would solve all performance woes. To the contrary, performance got worse, and the DBA noted a marked increase in SORT overflows. He even opened a PMR with IBM. But this isn't a case of STMM gone crazy; something else is going on, but what? Let's take a look at the numbers and discover the truth.

[ more .. ]

Congratulations to the IDUG Europe Conference Planning Committee (CPC)! IDUG Europe 2008 was one of the best IDUG conferences ever! On 17 October 2008, I taught the one day Ed Seminar "DB2 LUW Performance Diagnosis Learning Lab." This post contains a review of one of the participant's data and findings. The data comes from a busy production database that runs a banking application.

[ more .. ]

Congratulations on hunting your elephants. The prior blog post was the most popular ever; it has received well over 3,000 hits and continues to ascend in popularity. I readily concede that elephants can wreak havoc in your databases, but there exists a much more dangerous predator of your database's performance: mosquitoes.

Mosquitoes are, on an individual basis, small, inexpensive statements with an apparently low timeron cost, but, when they are executed with high frequency, they will suck the life out of your system and degrade the performance of your business. Two blog posts ago, the topic of statement workload analysis was introduced. If you have not read it yet, please do so now.

[ more .. ]

Hopefully you read the prior blog post on elephants and mosquitoes which discusses different approaches to statement performance analysis. If you haven't read it yet, please do so now. In this post, we will look at ways to hunt and kill your elephants. And, by no means do I favor cruelty to animals in any way, this is just a metaphor. Let us imagine that your phone just rang and your boss is screaming "What's happening RIGHT NOW?!?!?!!?!?" ...

[ more .. ]

It's said that there is more than one way to skin a cat, meaning there are multiple ways to accomplish the same objective. As for the saying, I don't think this is very kind to cats even though I prefer dogs. The prior blog post discussed making adjustments to CHNGPGS_THRESH to reduce, avoid, or mitigate transient "brown outs" in transaction throughput. There is another way...

[ more .. ]

A student who took this class last week at WDUG emailed me the day after to thank me. He said he enjoyed the class and successfully reduced the elapsed time of a troublesome query from two hours to three minutes! If you missed the WDUG session, I hope you will be able to join my Ed Seminar at IDUG "DB2 LUW Performance Diagnosis Learning Lab". This post covers topics related to optimizing Write I/O performance.

[ more .. ]

Understanding Table I/O performance is critically important to properly diagnosing the health and efficiency of a database and pinpointing problems. In fact, if I only had just a few minutes to quickly assess a database, I'd look at 3 key measurements...

[ more .. ]

Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.

[ more .. ]

First, my apologies for being away from the blog keyboard for so long. Kim Moutsos actually contacted me to see if I was still alive. Truth be known, my grandmother died, my father is in the hospital battling cancer, and I've been traveling the US States quite a bit helping companies save millions in software and hardware costs. Nonetheless, here's a quickie on some lock formulas and other updates. The good news is, I suppose, I'm accumulating a great deal of new material to share with you in future posts.

[ more .. ]

Every once in a while I hear a DBA say they are having Lock problems. Since read-only or read-mostly Data Warehouse databases rarely have lock problems, I quickly assume they have an OLTP database. It is my opinion that locks are rarely, if ever, a PROBLEM. Locks are a SYMPTOM of another very real problem.

[ more .. ]

In the prior blog post, we learned how to determine if your database is CPU bound, lock bound, sort bound, or I/O bound, and how to determine if a performance problem is attributable to the database or not. We will now turn our attention to statement analysis methodologies so that we can discover the sources of bottlenecks. "Statements" is broadly defined to include both classic SQL and newer XML queries.

[ more .. ]

In one of the earlier blog posts "DB2 LUW Performance: Key Cost Measures", we introduced the number of sorts per transaction (SRTTX). In a more recent post "DB2 LUW Performance: The Most Important Cost", we looked at the importance of measuring Bufferpool Logical Reads per Transaction (BPLRTX). If performing excessive and unnecessary logical I/O is the number one performance killer for a database (and it usually is), then performing excessive and unnecessary sorts is the number two performance killer in most databases.

[ more .. ]

The database is often presumed guilty if there is a performance issue. But your database seems fine; all the symptoms of good performance are present, so now how do you prove the innocence of your database?

In the prior blog post, I provided a checklist of some metrics that would help you assemble your defense if it was asserted that your database was the source of performance problems. But those ratios and indicators are just circumstantial evidence of probable innocence. Here comes the DNA test. It's hard. It's time consuming. It's complex. But the analytical effort just might help get you out of the hot seat and properly direct a performance issue to application or networking teams...

[ more .. ]

If your database updates its data via Inserts, Updates, Deletes, Imports, or Loads, then this blog post is for you. Write times tend to be slower than read times, and synchronous writes can be particularly painful. When tuning your databases, it is desirable to achieve a high percentage of Asynchronous writes as this type of write is faster. We need to learn the average write time for the database overall, and write times for each tablespace.

[ more .. ]

In the next few blog posts, we'll take a look at formulas for time metrics so that we can understand "where the time goes" and uncover bottlenecks. Since both OLTP and Data Warehouse databases perform a great deal of I/O read activity, we'll begin by looking at metrics for computing important read times.

[ more .. ]

It is an unfortunate reality that the database and the DBA are too often presumed guilty by default. Everyone tends to want to blame the database first, even though performance degradation could be caused by network problems, storage problems, the Web server, sun spots, or poor application coding. So, as a database professional, how do you get yourself out of the hot seat and prove your database's innocence? Here's a checklist to assemble your defense:

[ more .. ]

During a recent Webinar, and reiterated by attendees at this weeks IDUG Conference in Athens Greece, we've heard that very few organizations are taking advantage of DB2 9 autonomic tuning as implemented by the Self Tuning Memory Manager, or STMM. WHY? Because they don't trust it.

As with relationships between people, building trust takes time and requires a series of reliable and favorable experiences. In this blog post, we'll consider when and how to best engage STMM, and discuss how to build trust with this new DB2 9 capability.

[ more .. ]

Another DB2 magazine blog reader, Geoff, read the recent post "DB2 LUW Performance: Asked and Answered (BPLRTX)" and inquired about the optimum setting for NUMBLOCKPAGES. Let's take a closer look...

[ more .. ]

In DBI's Webinar "DB2 LUW Index Physical Design & DBI Performance Solutions: Your Roadmap to Becoming a Performance Hero", we discuss some important index physical design guidelines and techniques (the next Webinar is October 30th @ 10:30am CDT). Ideally, the FULLKEYCARD cardinality of an index should be at least 75% of the table's cardinality. Here is a sample SQL query that can help you do a quick cardinality check on your indexes...

[ more .. ]

I want to thank Brian Stewart, Greg Marino, and Marco Bartolli for their comments and questions posted in response to DB2 LUW Performance: The Most Important Cost. These comments include some very good questions that I'll attempt to answer in this blog post.

[ more .. ]

The Catalog Cache is like a special memory bufferpool dedicated to catalog objects; it stores information about tables, indexes, views, and other objects to speed up the BIND process for dynamic and static SQL. It CAN be changed online dynamically, but it DOES NOT participate in DB2 9 Autonomic Tuning - SO, you'll have to tune this one yourself. Here's how...

[ more .. ]

We've looked at several metrics that can help you discover the presence of physical design "opportunities for improvement", or problems. But, as many of you know, I don't like the word "problems" - 1) Problems is too negative sounding, and 2) Sometimes inefficiencies, or tuning opportunities, aren't severe enough to merit the label "problem". "Status Quo" operations for many databases may include many inefficiencies but the performance delivered is "good enough" - well, that is, it's "good enough" until the next hardware upgrade cost comes due or the application falls over and dies when 20 more users are added.

[ more .. ]

The database configuration parameter LOGBUFSZ controls the amount of memory that DB2 uses to buffer I/O to its recovery log files. The default size of 8 4K pages is grossly to small for most databases. This blog post introduces a new metric "Buffer Log Read hit Ratio" and offers tuning suggestions for Performance Heroes.

[ more .. ]

It's difficult to talk about DB2 performance and not have the subject of Bufferpool Hit Ratios come up. It's as if high bufferpool hit ratios are somehow capable of saving the planet from global warming. Yes, bufferpool performance is relevant, but we need to evaluate these with a dose of reality. Performance Heroes will spend much more time on workload analysis and physical design than twiddling memory bits.

[ more .. ]

In this post, I'll attempt to summarize the key metrics we've discussed so far and provide links back to the original posts for your reference. Database Files Closed will also be discussed. In upcoming posts, we'll look at time measurements, important ratios, workload analysis, and physical design techniques to reduce costs and improve performance. It would please me greatly for all of you to become Performance Heroes in your organizations.

[ more .. ]

Let's take a look at a cost measurement that can help you detect the presence of Index Leaf Page scans, BPLITX. While perhaps not as costly as data page scans, index leaf page scans can quickly suck your CPUs dry of processing capacity and rob your organization of performance that it would otherwise be entitled to. Performance Heroes will be successful at reducing the cost of both data and leaf page scans.

[ more .. ]

In this blog post, I will describe the most important cost metric that you MUST measure, and work to improve, to become a Performance Hero in your organization. This cost metrics is "Bufferpool Logical Reads per Transaction (BPLRTX)".

To compute this metric, use Database, Bufferpool, and Tablespace snapshots as described by the preparation instructions.

[ more .. ]

As described in my prior blog post, understanding workload costs is critical to successful database tuning. Performance Heroes diligently work to reduce costs of processing statement workloads. Here are two more important cost metrics.

[ more .. ]

The secret to successful database performance tuning and optimization requires an intimate understanding of workload costs. You can become a Performance Hero in your organization by determining current workload costs, and then making physical design and configuration changes to lower transaction costs - or the costs of doing business in the database.

Too often we find database people being excessively obsessed with rates, or chasing individual queries. Rates can vary day by day depending on the day of the week, the time of the month, or the hour of the day. Successful Performance Heroes will focus on reducing transaction costs to improve efficiency. Be a hero!

Cost Measurements:

[ more .. ]