Monday, January 30, 2017

Oracle Database Cloud (DBaaS) Performance - Part 3 - Storage - 12.2 Update

Recently I repeated the I/O related tests on a 12.2.0.1 instance for curiosity and was surprised by the fact that I consistently got significantly better results as on 11.2.0.4 and 12.1.0.2.

Now you're probably aware that the version 12.2 so far is "cloud-only", so I can't tell / test whether the version 12.2 is generically providing that increased performance or whether Oracle has optimized the underlying stack, so that previous versions in general could also benefit from better performance if they ran on the same platform. Repeated tests with versions 11.2.0.4 and 12.1.0.2 confirmed the performance figures reported in the previous installment of this series, so as of the time of writing it's only the version 12.2 that provides the improved I/O performance.

Note that as of the time of writing only a single instance configuration was supported with version 12.2, so I wasn't able to run the tests in RAC configuration.

Here are the 12.2 I/O related test results:

IOPS

Again running the test on a 4 OCPU single instance configuration (results in 8 CPUs / 8 cores as outlined previously) with eight sessions:


So that is more than 65,000 IOPS on average at 8 KB block size, significantly more than the corresponding (and already impressive) 40,000 IOPS seen in the previous versions, and even at 16 KB block size still more than 50,000 IOPS - that is more than 800 MB / sec in single block requests!

I/O Throughput

Repeating the same Parallel Execution based test that first creates a very large table (8 KB block size, "direct path write") and then re-reads it using Parallel Query ("direct path read") I got the following results on the 4 OCPU single instance configuration:


Again the results are significantly improved over previous versions. The read performance improved from 640 MB / sec to almost 940 MB / sec. More importantly however the write performance improved from 120 MB / sec to 200 MB / sec, a performance that should allow even more write intensive workloads to perform well.

I/O Latency

The same test as previously was run, switching from asynchronous I/O ("db file parallel read") to synchronous I/O ("db file sequential read") allowing measurement of single block reads, running with 8 threads on the 4 OCPU / 8 CPU / cores configuration at 8 KB block size.




Again an significant improvement across all figures, 0.375 ms average wait time vs. 0.45 ms previously. almost 19,000 IOPS vs. 16,380 IOPS and more than 90% of the waits within 512 microseconds vs. 75% before.

Write Performance

With the significantly improved write throughput figures the expectation was that the random read + write test would perform much better than before, and it did:



In previous versions this test waited almost 50% on "free buffer waits" (due to the minimized buffer cache), so clearly the DBWR couldn't keep up with writing dirty blocks to disk. The picture has changed here significantly, with "free buffer waits" going down to just 4.5% of the overall database time, and performing almost 5,000 write requests per second (and almost 20,000 IOPS in total).

Note that the version 12.2 obviously introduces an optimization that treats repeated modifications of the same kind to the same block (in this case here update a column of a row to the same value as before) not as a "db block change", therefore I had to adjust the test to update the column value to a different value each time. Running the original test case showed a totally different profile, due the minimized "db block changes".

Summary

The 12.2 version in the Oracle Cloud shows a significantly improved I/O performance profile compared to previous versions. So far it is unclear whether that is a generic improvement of the new release, or the underlying stack used by the virtual machines has been optimized.

In particular the improved write performance is an important improvement.

2 comments:

  1. Hi Randolf,


    Note that the version 12.2 obviously introduces an optimization that treats repeated modifications of the same kind to the same block (in this case here update a column of a row to the same value as before) not as a "db block change", therefore I had to adjust the test to update the column value to a different value each time. Running the original test case showed a totally different profile, due the minimized "db block changes".


    Could you please elaborate on this change: Is this counting in 12.2 under a different statistic?

    Regards

    ReplyDelete
  2. Hi Mikhail,

    I haven't checked this too thoroughly but according to the session stats it looks like in 12.2 the same code just shows far less "db block changes", "undo change vector size", "redo size" etc., but no other stats that increase accordingly. So it's just doing less work without showing why - please note that I can't say whether this is intended behaviour or a bug.

    Randolf

    ReplyDelete