Monday May 11, 2009

Running your Oracle database on internal Solid State Disks : a good idea ?

Scaling MySQL and ZFS on T5440

Solid State Disks : a 2009 fashion

This technology is not new : it originates in 1874 when a German physicist named Karl Braun (pictured above) discovered that he could rectify alternating current with a point-contact semiconductor. Three years later, he had built the first CRT oscilloscope and four years later, he had built the first prototype of a Cat's whisker diode, later optimized by G. Marconi and G. Pickard. In 1909, K. Braun shared the Nobel Prize for physics with G. Marconi.

The Cat's whisker diodes are considered the first solid state devices. But it is only in the 1970s that they appeared in high-end mainframes produced by Amdahl and Cray Research. However, their high-cost of fabrication limited their industrialization. Several companies attempted later to introduce the technology to the mass market including StorageTek, Sharp and M-systems. But the market was not ready.

Nowadays, SSDs are composed of one of two technologies : DRAM volatile memory or NAND-flash non-volatile memory. Key recent announcements from Sun (Amber road and ZFS), HP (IO Accelerator) and Texas Instruments (Ram San 620) as well as lower cost of fabrication and larger capacities are making the NAND based technology a must-try for every company this year.

This article is looking at the Oracle database performance of our new 32Gbytes SSDs OEM'd from Intel. This new devices have improved their I/O capacity and MTBF with an architecture featuring 10 parallel NAND flash channels. See this announcement for more.

If you dig a little bit on the question, you will find this whitepaper . However, the 35% boost in performance that they measured seems insufficient to justify trashing HDDs for SSDs. In addition, as they compare a different number of HDDs and SSDs, it is very hard to determine the impact of a one-to-one replacement. Let's make our own observations.

Here is a picture of the SSD tested – thanks to Emie for the shot !


As any DBA knows, it is very difficult to characterize a database workload in general. We are all very familiar with the famous “Your mileage may vary” or “All customer database workloads are different”. And we can not trust Marketing department on SSDs performance claims because nobody is running a synthetic I/O generator for a living. What we need to determine is the impact for End-Users (Response time anyone ?) and how the Capacity Planners can benefit from the technology (How about Peak Throughput ?).

My plan is to perform two tests on a Sun Blade X6270 (Nehalem-based) equipped with two Xeon chips and 32Gb of RAM on one SSD and one HDD- with different expectations.

  1. Create a 16 Gigabytes database that will be entirely cached in the Oracle SGA. Will we observe any difference ?

  2. Create a 50 Gigabytes database that can only be cached about 50% of the time. We expect a significant performance impact. But how much ?

The SLAMD Distributed Load Generation Engine (SLAMD) is a Java-based application designed for stress testing and performance analysis of network-based applications. It was originally developed by Sun Microsystems, Inc., but it has been released as an open source application under the Sun Public License, which is an OSI-approved open source license. The main site for obtaining information about SLAMD is It is also available as a project.

iGenOLTP is a multi-processed and multi-threaded database benchmark. As a custom Java class for SLAMD, it is a lightweight workload composed of four select statements, one insert and one delete. It produces a 90% read/10% write workload simulating a global order system.

Software and Hardware summary

This study is using Solaris 10 Update 6 (released October 31st,2008), Java 1.7 build 38 (released Otober 23rd,2008), SLAMD 1.8.2, iGenOLTP v4 for Oracle and Oracle The hardware tested is a Sun Blade X6270 with 2xINTEL XEON X5560 2.8Ghz and 32 GB of DDR3 RAM . This blade has four standard 2.5 inches disks slots in which we are installing 1x32 Gbytes Sun/Intel SSD and 1x146Gb 10k RPM SEAGATE-ST914602SS drive with read-cache and write-cache enabled.

Test 1 – Database mostly in memory

We are creating a 16 Gigabytes database (4k block size) on one Solid State Disk and on one Seagate HDD configured in one ZFS pool with the default block size. We are limiting the ZFS buffer cache to 1 Gigabytes and allow an Oracle SGA of 24 Gigabytes. All the database will be cached. We will feel the SSD impact only on random writes (about 10% of the I/O operations) and sequential writes (Oracle redo log). The test will become CPU bound as we increase concurrency. We are testing from 1 to 20 client threads (I.e database connections) in streams.

In this case and for Throughput [in Transactions per second], the difference between HDD and SSD are evoluting from significant to modest when concurrency increase. In fact, this is interestingly in the midrange of the scalability curve that we observe a peak of 71% more throughput on the SSD (at 4 threads). At 20 threads, we are mostly CPU bound, therefore the impact of the storage type is minimal and the SSD impact on throughput is only 9%.

For response times [in milliseconds], it is slightly lower with 42% better response times at 4 threads and 8% better at 20 threads.

Test 2 – Database mostly on disk

This time, we are creating a 50 Gigabytes database on one SSD and on one HDD configured in their dedicated ZFS pool. Memory usage will be sliced the same way than test 1 but will not be able to cache more than 50% of the entire database. As a result, we will become I/O bound before we become CPU bound. Please remember that the X6270 is equipped with two eight-threads X5560 - a very decent 16-way database server !

Here are the results :

The largest difference is observed at 12 threads with more than twice the transactional throughput on the SSD. In response times (below), we observe the SSD to be 57% faster in peak and 59% faster at 8 threads.

In a nutshell

My intent for this test was to show you (for a classic Oracle lightweight OLTP workload)

the good news :

When I/O bound, we can replace two Seagate 10k RPM HDDs with one INTEL/SUN SSD for a similar throughput and twice faster response times

On a one for one basis, the response time difference by itself (up to 67%) will make your end users love you instantly !

Peak throughput in memory compared to the SSD is very close : in peak, we observed 821 TPS (24ms RT) in memory and 685 TPS (30ms RT) on the SSD. Very nice !

and the bad news :

When the workload is CPU bound, the impact of replacing your HDD by a SSD is moderate while losing a lot of capacity.

The cost per gigabyte need to be carefully calculated to justify the investment. Ask you Sales rep for more...

See you next time in the wonderful world of benchmarking....




« July 2016

No bookmarks in folder