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 !





Goals

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 ?


SLAMD and iGenOLTP
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 http://www.slamd.com/. It is also available as a java.net 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 10.2.0.2. 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....

Comments:

The first transistorized (solid state) computer was made in Manchester in 1953. They became popular in the 1960s, e.g. the CDC1604 (1960) and the Ferranti Atlas (1962). Cray's CDC6600 (1964) was his first to use silicon transistors. The 1970s was when large scale integration became feasible and therefore microprocessors. The Cray-1 (1975) was his first to use integrated circuits.

Posted by Tony Finch on May 11, 2009 at 05:32 PM PDT #

PS. I don't see any numbers in the body of your article that support the conclusion that you lose a lot of capacity when running on an SSD.

Posted by Tony Finch on May 11, 2009 at 05:37 PM PDT #

I think SSD (given their price higher then traditional hdd) are destined the best for redo logs. So there should be three tests:
- OLTP on traditional (e.g. 12-24) HDDs
- OLTP on HDD + SSD (redo logs on SSD)
- OLTP on SSD (the most expansive solution)

Posted by Przemek Bak (przemol) on May 11, 2009 at 06:43 PM PDT #

@ Tony Finch

While not explicit, I think the implication is that SSD costs significantly more than HDD. Given x number of dollars to budget for Database storage, you will receive significantly less SSD capacity than HDD capacity.

Posted by Matthew Vines on May 14, 2009 at 02:26 AM PDT #

You really should test with multiple SSDs, if possible on a fast RAID controller. Especially for reads, multiple SSDs scale incredibly well. Maybe the following link is helpful in giving an indication of the potential performance: http://jdevelopment.nl/hardware/one-dvd-per-second/

Posted by arjan on May 14, 2009 at 06:13 AM PDT #

While not explicit, I think the implication is that SSD costs significantly more than HDD. Given x number of dollars to budget for Database storage, you will receive significantly less SSD capacity than HDD capacity

Posted by louisvuitton handbags on May 27, 2010 at 02:22 PM PDT #

Test 2 – Database mostly on disk

Test 2 may be more external.

Posted by Laptop battery on September 19, 2010 at 07:37 PM PDT #

Test 2 – Database mostly on disk

Test 2 may be more external.

Posted by Laptop battery on September 19, 2010 at 07:37 PM PDT #

You really should test with multiple SSDs, if possible on a fast RAID controller. Especially for reads, multiple SSDs scale incredibly well. Maybe the following link is helpful in giving an indication of the potential performance: http://jdevelopment.nl/hardware/one-dvd-per-second/

You test is clear as this :)

Posted by Akku on September 19, 2010 at 07:39 PM PDT #

It is amazing at how far they will go! I admire you and thank you for trying to wake people up with all the great information you are putting out there.

Posted by Timberland boots on October 27, 2010 at 03:49 AM PDT #

Akku; i cant open link ( http://jdevelopment.nl/hardware/one-dvd-per-second/ ) have problem?

Posted by Egitim on December 09, 2010 at 10:36 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

mrbenchmark

Search

Categories
Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today
News
Blogroll
deepdive

No bookmarks in folder