| Refresher: In December 2009, I weighed about 210LBs. My wife was nagging me about losing weight. My blood pressure was a bit high and so was my resting heart rate. Adding insult to injury, I stepped on our new Wii Balance Board and our Wii Fit Plus game verified I was overweight. You can argue (unsuccessfully) with your wife, but you can't argue with a game. There was only one thing I could do. I started running my butt off. By April 2010, I weighed 180LBs and ran in my first 10K race. In September 2010, I ran in the Philadelphia Half Marathon. By the end of 2010, I logged just over 500 miles for the year. Last month, April 2011, I ran in the Nashville Half Marathon. I've had a lot of time to think about how tuning "me" is like tuning DB2... |
Lesson #2: START SLOWLY!
You can't one day decide that you are going to START an exercise program and the next day run a marathon. You'll injure yourself. You have to make incremental improvements over time.Most articles I've read in Runner's World magazine recommend not increasing your speed or distance by more than 10% per week. This is really good advice. I KNOW first hand that it is really good advice because I tend to be impatient (always in a hurry to get more done more quickly - must be my performance oriented mindset) and an independent thinker. I have FAILED, on occasion, to heed this 10% advice, and I've often nearly crippled myself in my quest for rapid over achievement. The first time I tried running 13.1 miles was about two weeks after I started running in January 2010. I could barely walk for a week afterward.
How does this apply to DB2 tuning?
You can't, or shouldn't, neglect your database (body) for extended periods of time and then, all of a sudden one day, decide that you are going to make Herculean tuning efforts that include 67 changes within a period of a few days. Folks, this is a really BAD idea.For database tuning, the 10% incremental training rule translates to ideally making just ONE change at a time. After making a change (or going for a run), you have to rest, assess, and refuel. This means that you need to measure the performance consequences of the ONE change that you made. You need to get validation that your change provided incremental improvement and that it did no material harm.
If you rapidly make multiple changes in a short time frame, then your database will be at great risk of injury and you will have no means of isolating which changes were beneficial and which were harmful, and nor can you successfully quantify the value or harm of any one change.
DB2 Performance Tuning Lesson Learned: Make one change at a time and measure to validate your incremental success. When measuring, focus on averages and costs - not rates. You can't trust rates because rates vary by hour of the day, day of the week, time of the month, and other business cycles. Costs however, in the absence of change, will be fairly constant.
Are you ready to START making INCREMENTAL improvements?
Do you have a tool or other means that will enable you to measure the performance consequences of your changes? You can't just make changes and then pray. And it probably isn't a good idea to use "screaming users" as your measuring stick - most people tend to stew in frustration before speaking up, or they may abandon the organization or their shopping carts.
In previous DB2 LUW Performance blog posts, I've offered the DB2 community several key performance health indicators and cost measurements. Several of these are described (with links) in the VERY popular blog post Are you REALLY Ready for Production?
Certainly, if you want, if you have LOTS of time, you can measure some of those key costs and ratios by hand or maybe with scripts. And never mind those "free bundled" tools, they give you lots of rates with high overhead.
In fact, if you'll oblige me a brief marketing moment, on 26 April 2011 DBI announced our industry breakthrough release of Brother-Panther® for DB2 LUW. This DB2 LUW Performance Analysis, Tuning, and Trending Tool has always had industry leading performance trend charts since its first debut in August of 2007, but in the PRESS RELEASE DBI announced that it was now possible, for the first time in our industry, to COMPARE database and SQL Workloads, for two different time windows, side by side, in as few as TWO mouse clicks. TRUST ME - YOU'VE NEVER SEEN ANYTHING LIKE THIS BEFORE, BUT YOU'VE PROBABLY FANTASIZED ABOUT IT FOR YEARS!. Visit Brother-Panther's web page and watch the second video near the bottom. Try not to drool on your phone before calling us to place your order.
The importance of making one change at a time cannot be over stated. In recent weeks, while working with DBI customers around the globe, I have seen in vivid color how "one" change to tune just "one" statement actually ended up improving dozens and dozens of other statements while simultaneously somewhat harming the performance of others. The ripple effect of performance consequences of just "one" change can be mind blowing. If you're thinking about making multiple changes all at once, try to just flush that thought right out of your brain if you can.
As a reminder from Part 1, Lesson 1, here are a few FREE things you can do right now to start incrementally improving the health and efficiency of your DB2 LUW databases:
- 75 minutes: Watch a recorded webinar replay on DB2 Index Design Best Practices
- 8 minutes: Request a FREE copy of the White Paper "IBM DB2 LUW Critical Performance Measurements". This white paper is packed full of tuning ideas and SQL Snapshot commands that you can run to start tuning your databases immediately.
- 62 minutes: ACTUALLY READ the white paper you just requested in #2. My wife frequently reminds me that buying paint does not, in fact, get the house painted. Read the paper after you receive it!
- 60 minutes: Run the SQL commands found within the white paper and discover things you can do to have a positive impact on performance!
- 5 minutes: Register for DBI's webinar "Fearless Database and Application Performance Management from Cradle to Grave" offered on 16 June 2011 at 10am CDT: Details and Registration Link
- 2 minutes 40 seconds: Watch the short flash video on DBI's home page.
- 95 minutes: Watch a replay of Episode #52 of The DB2Night Show™ on "Writing Optimized DB2 LUW SQL Queries" with special guest John Hornibrook, IBM Canada
OK. We're off to the races! Now I'm already looking forward to Lesson #3 in Part 3 of "Running DB2 LUW Faster: Performance Lessons Learned on the Treadmill".
Questions? Comments? Feedback? Contact DBI.
Cheers,
Scott
PS - I may be a DB2 doctor, but I'm not a health doctor. Consult your physician before beginning any exercise program. Consult with DBI before changing your hardware, applying fixpacks, changing your applications, or upgrading DB2.