I have known for quite some time that a busier machine probably consumed more electricity. After all, I can bake brownies on the left side of my laptop when I run a heavy un-tuned SQL workload against my local DB2 Windows database.
I began an exhaustive and tedious search of Google to find white papers, blogs, or other research that correlate CPU busy rates to energy consumption. After many hours of searching and reading, I finally hit pay dirt. I found an IBM Austin research paper written in 2002 titled The Case for Power Management in Web Servers. This paper describes how IBM researchers measured power consumption of various components inside a Linux Whitebox computer. If you have time to read it, you will see that an idle CPU consumed about 5 watts of power and the same fully saturated 100% busy CPU consumed about 26.9 watts of power - there is over a 500% increase in CPU power consumption between idle and 100% busy.
Now, Database Servers are very close relatives, possibly siblings, to Web Servers. Both are subjected to fluctuating heavy workloads, and often a busy Web Server will cause a Database Server to become busy as well.
I, like many of you, am the kind of person that wants to see the facts for myself, so I ordered a Kill-A-Watts power meter and paid rush shipping.
As soon as it arrived, I was like a kid with a new toy. I plugged my toaster oven into it and figured out how much it costs to cook toast. Toast is way over rated. I plugged my vacuum cleaner into it and determined that I now have a new excuse not to vacuum the house. Then I plugged my Toshiba Dual Core laptop into the meter...
At idle with my screen powered off, my laptop burns 46 Watts. When the LCD monitor turns on, power consumed jumps to 68 Watts - a 22 Watt increase. Think about how many monitors you have in your company and the potential savings from just turning these off at night. When I ran a very heavy SQL workload against my DB2 Windows database to drive my CPUs to 100% busy, power consumption shot up to 98 Watts - a 44% increase! I attribute this power increase largely to CPU because STMM was active and the entire table easily fit into the bufferpool. After a missing index was added, power consumption dropped to 75 Watts - a nice 25% savings.
Well, this is a laptop after all - not a "real" database server. To add credibility to the energy savings argument, the next reasonable step was to recreate this database tuning experience on an IBM pSeries server:
(Watch the video in high quality mode if you can)
When this server is idle at 340 Watts (LCD monitor off), it costs DBI $268/year at $.09/KwH. Because of this idle power consumption, this helps make the very strong case for server consolidation and virtualization. The energy required to power disks, fans, and memory is relatively stable compared to the significant fluctuation of power consumed by the CPUs (as per the IBM research paper and our findings on this machine). It follows then, that a machine with more CPUs (4, 8, 16, 32, 64) would have greater power consumption volatility based on CPU busy rates and the related costs of processing transactions and queries.
It should also be noted that about 45% of a Data Center's energy costs are attributable to the equipment therein, and 55% of the energy costs are attributable to cooling. When more energy is consumed at higher CPU utilization, more heat is produced which requires more cooling (I'm cooking another batch of brownies next to my laptop right now). Given the 45/55% ratio (as per IBM at a GREEN IT Executive Briefing), it logically follows that reducing power consumption via database tuning likewise results in reduced cooling costs.
DBI is presently attempting to team with IBM to earn the "Ready for Energy & Environment" validation mark; we will be seeking to reproduce these power experiments at IBM Innovation Centers on machines having larger quantities of CPUs. I look forward to sharing the results of these tests as further evidence and validation for the value of database tuning as it relates to energy savings.
Energy savings aside, let us all not forget the root cause of why we are all gathered around this campfire --- when you tune your database, transactions and queries run faster and people get more done!!!
Fringe Benefits
Years ago people ate blueberries because they tasted good. Then some smart people figured out that blueberries have these things in them called anti-oxidants that can help prevent cancer. Now that word is out about blueberries and anti-oxidants, have you seen the price of blueberries in your grocery store lately?!?!? It almost takes a second mortgage (if you can get one) on your home to buy a bucket of these little blue miracle workers! So why are we now talking about blueberries in a DB2 blog? Because anti-oxidants are to blueberries what energy savings are to database tuning! BONUS! Better health! Better Performance!
You might be wondering now just how you are going to measure your energy savings from all your database tuning efforts. Well, at the IBM Austin Executive Briefing Center during a GREEN IT event, I was given a tour of Austin's "GREENING Data Center". In this data center, we got a sneak preview of IBM's recently announced IBM Tivoli Monitoring for Energy Management - the demonstration was impressive! This is probably a bit more professional grade than trying to hook a Kill-A-Watts meter up to all of your data center equipment.
I mention this new Tivoli offering because DBI is also partnering with IBM to OEM DB2 LUW, Tivoli, and Cognos, and DBI's Brother-Hawk™ can send SNMP trap alerts to Tivoli.
Measuring Your Database Energy Savings
Now, in case you can't get access to the Tivoli Energy Management console, how can you still measure your GREEN IT energy savings via your tuning efforts?
You can get CPU consumption information from a Statement or Transaction Event Monitor and store this information in a performance repository for reporting purposes over different periods of time. We have also previously discussed the cost measurement "Bufferpool Logical Reads per Transaction" (also see this blog post for more BPLRTX information) as being your most valuable barometer for tracking your database processing cost reductions. When you lower BPLRTX, you are lowering CPU consumption AND energy costs.
Are you ready to start reducing your IT Costs, optimizing performance, and achieving GREEN IT Objectives?
Here is your game plan (5 Steps, 2 for Extra Credit):
Step 1) Tune and Optimize your database on your current hardware. For goodness sakes, please stop throwing money away on hardware that you don't need! Find your most costly SQL in your workload and create just five "optimum" indexes - clustering indexes preferably to mitigate sort costs - and it is statistically likely that you will cut CPU utilization from 50-90%. Wow! Now your systems run faster, productivity is up, and you've benefited from anti-oxidant Energy Savings too!
Step 2) Consolidate and Virtualize your servers to cut out idle overhead and leverage the flexible power of CPU sharing. It is important that you complete Step 1 first because, by putting your database transaction processing costs on a diet, you will be more successful at consolidating more servers into the same physical frame. How many people can you get into a VW Beetle? More if you use smaller people - you get the idea.
Step 3) Tune and Optimize your databases again - the hardware changed and the database's optimizer is aware of CPU speeds and memory. New hardware opens the door to new access plans, and a renewed need to tune your databases!
Step 4) Implement DB2 Deep Compression. Compression typically cuts database storage requirements by 40-70% AND queries run faster! Now take some of those expensive 73GB drives offline and save electricity! Or, at least pass the operational cost to another department when the storage is re-purposed! [grin]
Step 5) Tune and Optimize your databases again - the data statistics have changed and the database optimizer is influenced by data statistics!
Earning Extra Credit - or Energy Credits
Step 6) Monitor and Measure your energy consumption and savings. Track your performance improvements. Check out that new IBM Tivoli offering.
Step 7) Replace your DB2 HADR configuration which requires double the hardware with one active and one passive machine, and instead upgrade to Xkoto GRIDSCALE to achieve higher availability and performance with an Active/Active configuration. When Xkoto first introduced GRIDSCALE I thought "Oh great, here's another way for companies to waste money throwing hardware at performance problems". But, if you have properly tuned up the databases in steps 1, 3, and 5, a GRIDSCALE Active/Active configuration can produce screaming high performance and high availability that best optimizes existing assets and energy consumption. NOTICE: Don't skip database tuning steps 1, 3, and 5, otherwise you will replicate the un-tuned, under performing, bloated, and excessively costly workload from the original copy of the database to the clones.
How do you save your job in this economy?
You need to become an IT Cost Optimization leader. You need to execute or orchestrate steps 1-5 or 1-7 above AND demonstrate your extreme value to your organization. It may take some blood, sweat, tears, and budget, but make it happen. You have to spend money to make money and save money. International economies are all stuck sitting on their hands right now, paralyzed by fear, waiting for other people to take action first. Be a mover and a shaker and get it done. People who tell you something can't be done are too busy making excuses while the achievers in the world are busy making good things happen.
FREE EDUCATION
DBI, in cooperation with IDUG, is offering FREE DB2 LUW Performance Webinars on 11 March, 8 April, and 6 May titled "DB2 LUW Performance and IT Cost Optimization for a Smarter Planet". This one hour session contains excerpts from my 2009 IDUG Ed Seminar "300 Fast Fabulous Facts for DB2 LUW". You can find times and registration links at www.DBIsoftware.com/events.php.
The Shameless Marketing Moment
DBI customers typically enjoy 30-90% CPU savings and improved response times within just a few hours of using DBI's integrated performance management tools for DB2 LUW. DBI has also partnered with Xkoto to help customers fully optimize their existing IT assets and maximize performance and productivity.
|
DBI's Brother-Panther™ provides industry leading, best of breed, statement workload analysis capabilities plus computes all of the metrics discussed in this blog series. You will quickly see the statements that have highest aggregate costs, and you can pass individual statements or entire costly workloads to the IBM Design Advisor for rapid solutions. After you have implemented a physical design change with the intent of improving performance, Brother-Panther's Performance Trend Charts with integrated, correlated, and plotted change event histories make it easy to verify your performance improvement success AND track STMM change effectiveness. No tool on the market makes the full performance management life cycle of identify, isolate, resolve, and verify easier, nor can any other tool deliver better performance results. |
Just for Fun
If you can afford blueberries, check out these recipes. Healthy eating habits will help you out live what little is left of your retirement savings.
More interesting reading on saving precious IT dollars: "Power In, Dollars Out: How to Stem the Flow in the Data Center"
With kindest regards,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
IBM Data Champion
Join me on LinkedIn: www.linkedin.com/in/scottrhayes
Follow me on Twitter: twitter.com/srhayes
DBI is an IBM Advanced, Industry Optimized, Business Partner
DBI is an Oracle Technology Network Partner
Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=127