Business Intelligence

I was working with one of my customers recently, for whom I configured a server in late 2009. The server purchased was an Acer with 2X Xeon quad-core CPU's, 12 GB of 1.3GHz DDR3 ram. Sadly, the server was only able to fit a pair of 2.5" drives, so the manufacturers put in a pair of 5400 rpm laptop drives (bargain!). I think my forehead still has a bruise from the headslap accompanied by 'DUH!' I gave myself when I found this out. This was like fitting retread tyres to a Ferrari and wondering why it could not corner above 50 km/h without a danger of the wheels falling off.

Business Context: The server was a host for Sql Server 2008 64-bit, running on Win-doze 2008 server 64-bit. anything that ran in memory ran like a lightning bolt, but to get it from disk to memory was another matter altogether. Page fault ratios were anything from 25:1 - 200:1 (yes, page faulted at least 24 times out of 25 attempts to access disk), so we spent most of our time waiting for disks. The tables involved in BI queries spanned about 10 million rows for each of the facts. Some of these queries were running in excess of 5 minutes, which was unacceptable to our customer.

IO is usually the slowest component of any system, but sometimes not as bad as network traffic, especially in a congested WAN. We'll address our solutions for minimizing network traffic in another blog post.

IO waits also often mask themselves as CPU time. This is because the kernel of most operating systems are designed to force a context switch (which involves eating up some CPU time), while the IO operation is seconded to the areas that interact with disk / storage. When the CPU spends its time doing mostly context switching (because of slow disks), the concurrency (number of simultaneous threads / sessions it can handle) that is theoretically possible, is dramatically reduced, because the CPU-slicing between threads now has to cater for more than 50% of its capacity servicing IO-wait induced context switching. In fact, 50% if you're doing well. Reality was closer to 90%. That's about as technical as I am going to get here in elaborating.

After doing some research, I recommended and had our VPS hosting partner install a pair of Solid State Falcon drives to replace the master drives that our BI software and the Sql Server ran on. At the time, the largest capacity with TRIM support was 256 GB, so we purchased a pair of these (the Database is about 30 GB for now and poised to grow 100% over the next 18 months) and after some gyrations (including reinstalling windows), we got it going for a price tag of under $2,000 (Australian dollars). We did a similar exercise for a very large Australian Telco in 2003 at a price of $340,000 for more or less the same capacity, so Moore's Law was acting in our favour very nicely!

So understanding the business equation:
VALUE = Benefit / Cost
We started to measure the effects of the solid state drive.

Writes are now about 5X faster, but since this is primarily used as a data warehouse, we focus mostly on reads. Reads now run anything from 7 - 15X faster. Our longest queries (where we join millions of rows across 3 facts (purchases, sales, stock / inventory) is at around 30 seconds (this used to time out after 10 minutes previously). The cumulative benefits are many hours saved each day for those users who need to see their information, the cost $2,000. This equates to very good value indeed! Long live SSD technology!

We (including our customer and their users) are very happy with the increased performance which was due mainly to the solid state disk technology and a few tweaks we made to the Sql Server implementation.

Page faults now on the system are less than 1:1, which means that most of the time, we actually find what we need without having to incur IO waits. Most dashboards (sometimes involving real-time summaries on millions of rows) are rendered in under 5 seconds. We'r also not running column-oriented database technology, though that might be a future consideration, so something like Sybase IQ, Vertica, ParAccel, InfoBright (which uses MySQL as its base, if you can get yourself to look past the basic skepticism of Oracle's acquisition of MySQL) looks like it might have a rosy future with our implementations.

When looking for Solid State drives, keep in mind that if the drive does NOT support TRIM, it will eventually degrade to something that is worse than a 5400 rpm drive. See here for a technical explanation why.

At the time of the exercise, based on a recent review of these in the PC magazine, we chose Falcon, but a Corsair or Patriot drive might have worked just as well.