Guy Harrison's web bits

Solid state disk technology can provide truly revolutionary improvements in database disk IO.

The economics of solid state disk –the high per-gigabyte costs in particular – may make complete replacement of magnetic disk with solid state disk impractical for larger databases. Many databases have very large, but relatively static data sets for which solid state disk is unnecessarily expensive given the high per-gigabytes costs and relatively low IO demand.

The best results will often be obtained when the solid state disk is combined with magnetic disk to provide both economical storage for static data and high performance retrieval for active data. In this paper we’ve looked at how best to implement solid state disk in Oracle databases, and recommend the following:

1. Use solid state disk strategically to relieve IO bottlenecks: solid state disk should be deployed where acceleration of IO leads to the greatest application performance benefit. Knowing your IO performance bottlenecks is a necessary prerequisite to the intelligent deployment of solid state disk.
2. When IO bottlenecks are limited to specific segments (tables, partitions, or indexes) then relocating those segments to solid state disk will almost always be effective in optimizing IO to those segments, regardless of the type of IO to which the segments are subjected.
3. Oracle’s database flash cache – while limited to the latest release of the Oracle 11g and available only on Oracle operating systems (Oracle Enterprise Linux and Solaris) – offers a very convenient means of exploiting limited amounts of solid state disk. The database flash cache can only optimize buffered IO, however, which means that it will not normally optimize full table scans or temporary tablespace IO.
4. Many solid state disk vendors provide caching technologies, such as FusionIO’s directCache. These allow the solid state disk to act as a secondary cache for any arbitrary storage medium in a similar manner to Oracle’s database flash cache but without the limitations of Oracle versions or operating systems. Unlike the database flash cache, these caching options can optimize non-buffered IO and are able to optimize full table scans and temporary segment IO.
5. For databases where temporary segment IO is particularly significant, placing the Oracle temporary table space on solid state disk can be very effective. This is particularly true when the IO results from multi-pass temporary segment operations. The lower latency afforded by solid state drives can vary significantly and reduce the overhead of these otherwise IO intensive operations.
6. Oracle’s redo log is a frequent source of intensive IO and, by design, Oracle sessions will normally wait for redo log IO to complete. However, the nature of redo log IO operations is essentially optimal for magnetic disk but least suitable for solid state disk. Redo log IO consists almost entirely of sequential write operations, which are the worst case for solid state disk and the best case for magnetic spinning disk. Placing redo logs on solid state disk is generally not recommended.

Conclusion

Almost any Oracle database can benefit from the judicious use of solid state disk. Attaining the benefits of solid state disk does not require a complete conversion from magnetic disk to solid state disk; indeed, for many databases, a combination of magnetic and solid state disk will offer the best balance between economical storage of static data and the economies of access for active data.

Getting the most benefit from solid state disk requires an understanding of the performance characteristics and economics of modern solid state flash drives in order to ensure their optimal deployment. 

Guy Harrison is a senior director of research and development at Quest Software and has more than 20 years of experience in database design, development, administration, and optimization. Guy is an Oracle ACE and the author of “Oracle Performance Survival Guide” (Prentice Hall, 2009) and “MySQL Stored Procedure Programming” (OReilly with Steven Feuerstein) as well as other books, articles, and presentations on database technology. Guy is the architect of Quest's Spotlight® family of diagnostic products and has led the development of Quest’s Toad® for Cloud Databases. Guy can be found on the internet at www.guyharrison.net/, via email at guy.harrison@quest.com and is @guyharrison on twitter.

References

Wikipedia article on SSD: en.wikipedia.org/wiki/Solid_state_disk

FusionIO direct cache: community.fusionio.com/media/p/1124/download.aspx

Oracle database flash cache: download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory005.htm#BABHEDBH

Authors notes on SSD and the Oracle database flash cache: guyharrison.squarespace.com/blog/tag/ssd

Do you anticipate that the location of the Flash would have a measurable impact on performance? I assume that the latency of flash on a PCIe card in the DB Server is less than the latency of the flash in a local SSD drive which would be less than the latency getting to flash in some sort of storage device at the end of a wire. Thanks.

There should be no performance degradation from putting the logs on Fusion-io, so if anyone wants to put the entire database on Fusion-io they should include the logs. Here’s what HP published in their Best Practices Guide, “Because of the IO Accelerator low-latency capabilities, moving the logs off of traditional storage to the IO Accelerator yields a significant performance benefit for write-latency-bound applications.” h10032.www1.hp.com/ctg/Manual/c02684012.pdf .

Guy,
great and very interesting article, we just plan for new hardware and i wonder how ssd can help us improving db performance ...
do you have any idea why ODA uses ssd for redo ?
(
see ORACLE DATA SHEET, ORACLE DATABASE APPLIANCE X4-2 :
....
Four 2.5-inch 200 GB SAS-2 SLC SSDs per shelf for database redo logs"
...
)

Thanks,
Matthias