October 2009 - Posts

Well there's a thing

Just noticed this post from the PSS Team   http://blogs.msdn.com/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx

never knew you could do double assignment   x =  y = z.

Posted by GrumpyOldDBA with no comments

Greenwash and ROI

First up let's make clear I do support most things green, or greener, however I feel that so much is talked green but misses critical information, e.g. the point of it in the first place.

I was reading about the usual sucess story of how virtualisation had turned a situation green; reported savings were £20,000 p.a in power and £50,000 p.a. in staff savings as the infrastructure was now easier to manage!
Of course to achieve this saving two sans had been installed and server sprawl reduced, let's assume we've cut down the number of boxes.
So how much might a couple of SANs cost, well you might expect to pay somewhere between £100k and £200k  each at the bottom end, a decent server, say a DL580 might come in at around £10k, a base price on the HP website states £15k.
So in terms of ROI it'll probably take at least 6 years to pull back the power savings, staff savings are more tricky, but no doubt the architects of the solution ( because there was a third party involved ) would have a few consultants in at maybe £650 a day, it was London, so maybe £1k a day.
So to the power savings, 34kwh down to 1.1kwh, wow!, or not wow, a quick check on the HP website shows us that a DL580 can use all this power on it's own, some other "interesting" mathematics,  2TB of sata storage uses 11W vs 85W for fibre channel disks. Now let's think about this, typically a fc disk is 300GB and 15k spindle speed, a sata disk is,  suprise surpise,  2TB and spins at 7.2k, so the spindle count has been reduced by a factor of 7 and the iops  1,200 to 75  not really  a balanced argument is it?

To make a comparison here a colleague was complaining that a database restore was taking 47 mins - well it's 40GB mdf and a couple of non raid 7k2 sata disks.
Me, I can restore the same database in 4 mins but I do have 15k SAS disks ( 8 in a raid 10 ) and this is only a 4 to 1 ratio 4 x 300Gb vs 1 300gb, excluding the raid.
So do the statements hold true, no, even 34kwh isn't particularly high, a typical single rack in a data centre supports approx 7kw, and you can get racks which support far more, up to 20kw.
So 1.1. kw is one server with internal disks, I'm not sure about the two SANs maybe they're running on solar power!
It all sounds good, a bit like some of the 1.1 litre "green eco" cars, but how will this car cope with 2 adults + 2 children going on holiday with 2 kayaks and 2 surfboards and all the camping gear for two weeks?
Well the short answer is it won't and the same applies to many of the "green" storage claims. SSDs are touted for their low power, and yes I agree they do have low power but a serious enterprise SSD of 256GB capacity will likely set you back a few £k vs. the £300 av cost of a 300GB 15k sas disk.
So your SSD costs are maybe 8 to 10 times more expensive and if your databases need several terabytes of storage it's not really going to work.
I did read an interesting case study of placing a sql 2008 database entirely on SSD, however it was only 30GB in size.
Of course trying to find out all the figures yourself and do the maths is a nightmare as the power usage and price of most enterprise SSDs are not widely available, typically we can say that £2k would be a fair price for 256GB and likely power consumption will be 1 watt compared to say, 14 watts for 15k disk.
However you need to put your disks in an enclosure, this in itself with redundant power supplies will likely draw about 800 Watts.
So 8 sas disks £2,000 , 8 SSDs £16,000. Power 112 Watts vs 8 Watts, save 104 watts, that means we'll save one unit of electricity every 9.6 hours.
I cost the energy at 15p and I make it 102 years to recoup the £14k savings from the lower use of power.

I know this is a somewhat one sided view but consider as an example "eco" cars. You decide to buy a cheap eco car for commuting to work, let's say £6k, it does 60mpg compared to your current car 20mpg.
Based on 10k miles per annum and some annual running costs, say £400 p.a. it will be 6 years before you actually save any money. If you invest your £6k and consider within 6 years you'll need some MOTs and most likely tyres the £400 running costs are probably low and you'd have gained £1k in interest if you put your money in long term bond it looks more like 8 years before you actually save any money!
More miles means quicker savings of course, less miles means you'd never get your money back! ( based upon 103p per litre and 3% simple interest after tax on £6k )

Welscome to green wash! < grin >

Posted by GrumpyOldDBA with no comments
Filed under:

When does 2+2 != 4 ?

Disclaimer: Results and observations are from a specific series of tests using sql 2008 enterprise on windows 2008 enterprise. I've been working on a series of benchmarking tests for an application with the aim of showing scaling of the storage for future growth.Essentially we were attempting to prove that adding extra spindles to the storage pool would give extra performance. The one interesting point that emerged was that the initial test runs were throttled by the bandwidth of the fibre channel switch.The initial setup was through two 4GB HBAs using mpio through a 2GB switch into two 4GB nodes on the SAN, I'd actually missed the fact it was a 2GB switch. We ran a series of tests with some different disk setups but results were strangely very similar and it was only a set of very extended times for a run of one test led us to discover that the switch ports had actually been swamped.It was at this point we realised we had a 2GB switch and not a 4GB switch and that our tests were actually being bottlenecked by the fibre bandwidth. To get the full bandwidth we decided to remove the switch and connect direct, but the initial HBA setup was in failover so we still only had 4GB bandwidth, however what was interesting was that we were able to drive more iops and test times dropped. To summarise:
  1.  If you think you may have performance issues on your fibre attached storage check that your route is actually at the speed you think it is
  2.  Monitor your switch ports to make sure you don't have a bottleneck
  3. In my tests 2 x 2GB did not give the same performance as a single 4GB connection
  4.  If your fabric is shared into the storage from your database server make sure you do 2) above. 
To give a feel for the differences one of the tests was a database restore ( 55GB database ) with the 2 x 2GB connects we had a time of  5.5 minutes, 1 x 4GB dropped this to 3.5 minutes and 2 x 4GB dropped this to 2.5 minutes ( times rounded for simplicity ).( NB. The actual performance of a database restore depends upon more than just the fc bandwidth, however this is still a valid observation. ) ( The actual server was a 16 core intel with 26gb ram allocated to sql server, the storage subsystem was all raid 10 - I will blog further concerning the vendor and setups we tested and the monitoring tools used to gather this information ) 


Posted by GrumpyOldDBA with no comments
Filed under: , ,