OLTP Performance

Disclaimer: This post was written in 2012 and covers the Exadata X2 model. I have no plans to update it to cover the later models, so please be aware that the information below is no longer be up to date. In particular, the information about the Smart Flash Cache being a write-through cache is no longer correct for later versions of the Exadata software.

The HP Oracle Database Machine (V1) was designed with data warehousing workloads in mind. The main performance feature, Smart Scan, is only used for full segment scans (e.g. full table scans or fast full index scans) and does not offer any advantage for the typical single block read IOs associated with highly-transactional OLTP environments.

Upon releasing the Sun Oracle Database Machine (V2), Oracle made the claim that Exadata was now the “World’s First OLTP Database Machine”. This claim appears to be based on the inclusion of flash storage on each Exadata storage cell. However, a look at Oracle’s published figures for IO performance (page 5 of this white paper) shows an interesting issue with writes, particularly regarding the read / write ratios:

Further to this, since ASM uses software mirroring it can be said that the NORMAL redundancy mode in ASM (basic mirroring) will reduce the effective write IOPS by a half or a third.Since it is known that the Smart Flash Cache is a write-through cache (see page 6 of this white paper), the figures for Database Flash Cache IOPS are for reads only (in this case typically 8k block reads). This means that the write performance is limited to the performance of the disks – in the example of a full rack X2-2 with high performance disks this is 50,000 IOPS.

Normal redundancy would therefore reduce write IOPS to 25,000 on a full rack, which therefore gives a read to write ratio of 60:1. If the customer were to use HIGH redundancy (triple mirroring) this ratio would deteriorate even further to 90:1 – and it must be remembered that triple mirroring needs to be used if the customer wants to be able to perform online patching, since normal redundancy would leave data exposed without redundant copies during any patching operation. This means that when considering a system using high capacity disks and high redundancy (triple mirroring) the ratio becomes 180:1.

This heavy bias towards reads over writes indicates that Exadata may not be suited to working with OLTP applications – the considerable read performance will effectively be held back by the far inferior write performance. In fact there could be many situations where these ratios are unsuitable for data warehouse workloads – hence the requirement for customers to run proof of concepts with real data.

Kevin Closson makes a number of insightful points about Exadata and OLTP performance on his excellent blog and also in his Critical Analysis Meets Exadata videos on YouTube, which you can watch here and here.

This marks the end of the History of Exadata series of pages. Although I have offered some comments regarding the Exadata product I have generally attempted to stick to factual observations in this series. If you want to read more about my own personal opinions of Oracle Exadata I post these under the main blog section. A good starting place would be The Strategic Platform for All Database Workloads.