The 5% rule of Ram Based Storage

By Randy James, Editor in Chief  (insert email address here)

So your mouth is watering after you read about the jaw dropping performance of a ram based storage solution…but…then you looked at the cost of buying a terabyte or two of space…and now you have sticker shock!   Many ram based storage solutions have list prices in the $500 per GB range…what is a stressed out technology manager to do?

First, step away from the screen and take a few deep breaths!   It’s going to be ok!

Not every byte of your data needs to be hosted on such a screaming platform – in fact, the general rule of thumb is that about 5% of your data needs to be on ultra fast storage to maximize performance.

In response to this fact, storage manufacturers have created multiple “Tiers” to match the demand for I/O performance against different price points.  While the definition (or number) of storage tiers is not standardized, the concept is easy to understand:

• Tier 0 is the fastest, highest cost per GB, and usually refers to semiconductor based storage (Ram or Flash)
• Tier 1 refers to high performance, high reliability, high cost (“enterprise class”) hard disk storage (typically a Storage Area Network or SAN) using 15K Fibre Channel or SAS drives
• Tier 2 refers to lower cost, lower performance, lower reliability hard disk storage (typically SATA based using 7200 or 10K drives) – often used less frequently assessed files (last years financial reports, old order history, etc.), and for disk to disk backups and archiving of regulated data (emails, health information, etc.)
• Tier 3 is usually off-line storage – using high capacity magnetic tape, DVD, Internet cloud based storage, etc. where a “restore” operation to a higher Tier is needed before it can be utilized.

So how do you select the data that can benefit from Tier 0 storage?

Let’s take a look at a typical SQL database.   If it is small enough, or if you have enough money, you can place the entire database on a ram based platform – and stand back and watch it scream!  But in many cases, you can’t afford or need 100% Tier 0 storage, so the goal then becomes to buy a smaller amount of Tier 0 storage and use it for maximum benefit.

Let’s assume that we have a 500GB database for our following discussion.  When you take a close look, you will find that some tables (files) are accessed much more frequently than others, including:

• TempDB (scratch space) used for joins, sorts, etc.
• Indexes
• Transaction logs/journals used to roll back transactions and provide backup
• Frequently accessed/updated tables

TempDB can benefit from Tier 0 since it is essentially “virtual memory” for your database, and it is very write intensive.

Fast access to indexes is also another common use of Tier 0 storage – it provides great performance that can speed up the fetching of data from your SAN.  As most databases are also write intensive (adding new records also creates new indexes and causes current indexes to be modified), you’ll be able to update indexes hundreds of times faster than hard disk storage.

Transaction logs (sometimes called journals) also benefit because they are nearly 100% writes – the faster the log record is written, the sooner the application is notified of the successful I/O completion, and sooner that the next step of processing can continue.  You also gain the safety of having your logs on a physically separate and fast media - very useful when a database recovery is needed.   This is a key point – many IT specialists don’t consider the restore/recovery speeds of their backups.

You will likely identify some larger tables, such as a customer master table, that are accessed with every transaction – the order entry staff must search the table to check if this is a new or existing customer. They may search by a variety of index fields – customer name, address, customer ID, SIC code, sales region, key contacts, etc.  Other tables, like the order history table, may benefit if the table has been partitioned, so that only the most recent orders are stored on Tier 0 – everything else is on Tier 1 or 2 media.

Balanced I/O is Important

To maximize the performance of your overall application, it is important to have the right “mix” of high speed and lower speed storage media, with the right amount of I/O capacity and bandwidth.  For example if your indexes are on Tier 0 media (capable of 100,000+ I/O’s a second), and your database is on Tier 1 storage spread across 4 disk drives (capable of a combined 1000 I/O’s a second) shared with multiple other applications, don’t call your ram based storage provider and complain about the poor performance!

Don’t forget the Infrastructure

It is critical that all elements of your infrastructure have enough capacity and are well tuned to maximize the performance of your applications.  I can’t stress this enough!  Nearly every client I visit, I find that the infrastructure (networks, servers, storage) has been given little attention, and is incorrectly and under configured.  IT administrators often assume that if a device is “on-line” that it is working well – when there are major issues that need to be addressed.

Infrastructure with a poor design, or not enough capacity, or improper configuration will cause performance and stability issues with the application that your DBA or application developers will NEVER fix.   If you don’t have the skills (or the time) to optimize your infrastructure (and few IT teams do), hire someone who specializes in this area.  It will save you a LOT of time and money and make you a hero!

As a final note, don’t overlook the design of your application.   I have stopped counting the number of poorly designed databases or applications that I have observed.  A poor design will consume ALL available I/O resources, no matter how much capacity you have.

Do your homework (and don’t be afraid to get some expert help) and you will see some amazing results when using Tier 0 storage!