The Sun BestPerf blog "Oracle Flash Cache -- SGA Caching on Sun Storage F5100" posted by Senthil Ramanujam reports on the combination of new features delivered by Oracle and Sun's Flash Cache technology and its effect on the improved database performance of the Sun Storage F5100. The benchmark results in this blog deal with the performance of Oracle Database 11 (Release 2), which enables extending the System Global Area (SGA) size and caching beyond physical memory, to a large flash memory storage device as the Sun Storage F5100 flash array.

Oracle uses SGA, a group of shared memory areas dedicated to an Oracle "instance," in its database solutions to hold information, such as storing incoming data and index buffers and internal control information needed by the database. The size of the SGA is limited by the size of the available physical memory, Ramanujam writes.

In one benchmark test the blogger found a dramatic performance improvement (almost 5x) using the Oracle Extended SGA feature on flash storage by reaching SGA sizes in the hundreds of GB range, at a more reasonable cost than equivalently-sized RAM, and with much faster access times than disk I/O.

The workload, Ramanujam explains, consisted in a high volume of SQL select transactions accessing a very large table in a typical business-oriented OLTP database. To obtain a baseline, throughput and response times were measured applying the workload against a traditional storage configuration and constrained by disk I/O demand (DB working set of about 3x the size of the data cache in the SGA). The workload was then executed with an added Sun Storage F5100 Flash Array configured to contain an Extended SGA of incremental size. The workload, he concedes in a response to one comment, was read-only.

The server configuration was a Sun SPARC Enterprise M5000 Server with 8 x SPARC64 VII 2.4GHz Quad Core and 96 GB memory. Storage configuration was 8 x Sun Storage J4200 Arrays, 12x 146 GB 15K RPM disks each (96 disks total)1 x Sun Storage F5100 Flash Array, and software configuration was Oracle 11gR2 and Solaris 10 OS.

According to the blog, the tests have shown scaling throughput along with increasing Flash Cache size. The benchmark used a database that was a multiple of the physical memory size in order to avoid the case in which the accessed data could be entirely or almost entirely cached in physical memory. Such a size, the blogger contends, represents a typical “use case” in which the Flash Cache Extension is able to show remarkable performance advantages.

Commenting on the workload, Ramanujam notes that increasing the Extended Cache beyond a specific threshold, dependent on various factors, may reduce the benefit of widening the Flash SGA and actually reduce the overall throughput. This new cache is somewhat similar architecturally to the L2ARC on ZFS. Once written, flash cache buffers are read-only, and updates are only done into main memory SGA buffers. This feature is expected to primarily benefit read-only and read-mostly workloads, he adds.

Ramanujam points out that a typical sizing of database flash cache is 2x to 10x the size of SGA memory buffers. With that header information stored in the SGA for each flash cache buffer (100 bytes per buffer in exclusive mode, 200 bytes per buffer in RAC mode), the number of available SGA buffers is reduced as the flash cache size increases, and the SGA size should be increased accordingly.

The benchmarking exercise introduces two new init.ora parameters, illustrated below:

db_flash_cache_file = /lfdata/lffile_raw

db_flash_cache_size = 100G

Here, the db_flash_cache_file parameter takes a single file name, which can be a file system file, a raw device, or an ASM volume. The db_flash_cache_size parameter specifies the size of the flash cache. Note that for raw devices, the partition being used should start at cylinder 1 rather than cylinder 0 (to avoid the disk's volume label).

The effects on performance of extending the SGA size of the Sun Storage F5100 are fairly linear, as a chart in the blog shows, ranging from 169396 query txns/min at 25 GB extension with an average response time in seconds of 0.053 for a speedup ratio of 2.2. At 100 GB, query txns/min rises to 357086; average response time to 0.025 and speedup ratio is 4.6.

More Information

Oracle Database 11g Release 2

Lowering Your IT Costs with Oracle Database 11g Release 2

Oracle 11g Database New Features

Oracle on Sun Beats IBM's Best Results on DB2 with Power 595

Accelerate Databases with the Sun Storage F5100 Flash Array [...read more...]

fullsource

From the latest issue:

Recent Blog Entries as of March 22, 2010, 7:15 am
Oracle Works to Assuage the Anxieties of Its Customers SysAdmin News Bites Oracle Enterprise Manager Ops Center Released Suits and Their Countersuits A More Direct Channel Model Planned for Oracle-Sun	The Importance of Information Storage and Management Sun POJO Service Engine User's Guide Sun Storage 7000 2010.Q1 Quality and Features PSA Deploys GlassFish in the Manufacture of Peugeot, Citroën Vehicles Running VirtualBox VMs as Services in Windows
Blog RSS Feed