Sunday, October 23, 2016

Oracle Database Cloud (DBaaS) Performance Consistency - Part 6

In the next parts of this series I'll have a look at the results of similar performance consistency tests performed on a comparable Amazon RDS Oracle cloud database instance.

Let's start with the configuration used, which was an Amazon RDS "db.m4.2xlarge" instance - the latest RDS generation available at time of testing. This is an 8 VCPUs resp. Hyper Threads / 4 cores configuration with 32GB of RAM.
As storage I've used the "General Purpose SSD" setup. Although there is a "Provisioned IOPS" configuration available, too, I haven't used for testing because according to the price calculator available it would have costed me several hundred dollar even for a very short period of testing because some part of the configuration gets charged per month, no matter how long I actually use it. I'll get back to that point when dealing with the I/O related tests and the raw performance figures.
Therefore I've only run the tests on the "General Purpose SSD" configuration - more on that in later installments.
Even without the "Provisioned IOPS" storage the tests were only conducted for several hours instead of several days, therefore they can't be compared directly to the other test results.
The first test conducted was the "PL/SQL" CPU burning test I've outlined in the first part of this series.
The overall consistency result looked like this:

Again the graph is supposed to show how many of the runs deviated how much from the overall median runtime. The results - at least for the short period of the test run - are very consistent, similar to the other test results.
Looking at the results on a more granular basis is only of limited use in this case due to the short test run time, hence the resolution is changed to hours instead of days here:
There were a couple of hours that show a very similar behaviour and again another set of hours with rather a similar profile, and there is an hour with a rather unusual profile.
Finally, looking at the runtime of the individual threads:
Except for one threads all other show a very similar runtime, also the runtime is very consistent.
It should be noted however, that the actual raw performance of each thread is pretty different from that of the Oracle DBaaS offering based on 4 OCPUs, because the DBaaS offering actually provides 8 cores, and hence scales much better when running with 8 threads - see the corresponding previous test results. The Amazon RDS raw performance is more comparable to that of the 4 cores physical host used in the previous tests for comparison.

Monday, October 17, 2016

Oracle Database Cloud (DBaaS) Performance Consistency - Part 5

This is the fifth part of this installment, and before coming to comparisons to other cloud providers, in this part I show the results of the read-only test that I've already described in part three of this series, but repeated at a later point in time. The test setup was identical and can be checked in the mentioned previous part.

The reason for running the test again was the fact that I was informed during the first test run that the zone that my Oracle Cloud domain was assigned to was temporarily overloaded, which I also noticed since I wasn't able to create new services for some time.

Hence I decided to repeat the tests after it was confirmed that the issue got resolved.

So here is a comparison of the second test run results compared to the first test run - the corresponding results for the dedicated physical host can be found in part three.

Overall consistency second run:

Overall consistency first run:

Again the graph is supposed to show how many of the runs deviated how much from the overall median runtime. The second run shows a much improved overall consistency much closer to that of the dedicated physical host, although a few outliers are more extreme than in the first run,

The same data on per day basis, second run:

First run:

Except for the first two days the second run shows a pretty consistent behaviour per day, and it becomes obvious that it's the second day of the second run that is responsible for the extreme outliers and also the significant inconsistency in the area between 8 and 12 percent deviation.

Finally the individual thread performance, second run:

First run:

The individual thread performance graph confirms the much improved consistency compared to the first run.

Sunday, October 9, 2016

Upcoming Public Appearances

It's that time of the year again... I'll be speaking at the following events:

- SSWUG 2016 Fall Virtual Conference: On November 15th I'll do the geeky stuff that was rejected by DOAG this year, which is "Oracle Database Parallel Execution plans deep dive - understanding the internals of Parallel Execution plans". You should only attend this session if you already have a good understanding of serial execution plans.

- DOAG Conference 2016: On November the 16th I'll present "Oracle Database Cloud Performance" where I talk about my experience regarding the performance of the Oracle DBaaS offerings. Note that some printed conference schedule material is outdated, I'll speak at 3pm, not 10am

- IT Tage 2016: On December 13th I'll present "Oracle 12c - Cost Based Optimizer Grundlagen" which describes the basics of the Cost Based Optimizer with an update how things might be different with Oracle 12c

Hope to see you at some of these events - if you're going to be there, say Hello!

Monday, October 3, 2016

Oracle Database Cloud (DBaaS) Performance Consistency - Part 4

This is the fourth part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time the previous read-only test was modified to be a 100% update read-write test. So every row read was updated in the following way:

    for rec in (
          select /*+
    ) loop
      update t_i&tabname t_i
      set n = rec.id_fk
      where id = rec.id_fk;
    end loop;
    insert into timings(testtype, thread_id, ts) values ('&testtype', &thread_id, systimestamp);
  end loop;

The setup of the tables T_O and T_I<n> was identical to the previous read-only test and can be checked in part 3 of this series. Again this was executed by several sessions concurrently, this time by 8 sessions on both the DBaaS database and the dedicated physical host - since this wasn't CPU bound and due to better write performance the dedicated physical host could keep up with the load at 8 sessions.
Due to the minimum buffer cache used, the way the data and test is crafted this setup puts maximum pressure of the DBWR background process, due to the constant need to read new blocks into the small buffer cache and write dirty blocks at the same time. By choosing a large block size of 16KB and at the same time using a very small actual change data volume the log writer didn't play a role in this test here. Both databases operated in NOARCHIVELOG mode and with Flashback disabled.
The test showed that for this setup the DBWR for the DBaaS database was not capable of writing the data fast enough, so that significant time was spent on "free buffer waits", waiting for the DBWR to free up buffers by writing dirty blocks to disk, amplified by the faster read I/O of the DBaaS service compared to the dedicated physical host. The DBaaS storage obviously is optimized for reads - having to write encrypted blocks makes things not faster - the dedicated physical host didn't use TDE encryption. In a separate Parallel Execution test the DBaaS service performed a maximum read throughput of 640 MB per second and 120 MB per second write throughput.
A sample AWR Top profile looked like this:

Physical Host:

Unfortunately this test could only be executed for a couple of hours on the dedicated physical host and had then to be stopped due to technical problems, distorting some of the following charts.
The overall consistency graph looked like this:


Physical host:

Again the graph is supposed to show how many of the runs deviated how much from the overall median runtime. Again the DBaaS service shows a significantly different profile, but this time performed more consistently than in the previous read-only test - the probable reason for the difference will be explained in a later part of this series. The DBaaS service this time had some extreme outliers (max. > 160 percent deviation) that I didn't include in the graph to not distort the graph too much - but it were just a few runs out of more than 25.000 that were affected.

The same data on per day (per hour for the physical host) basis:


Physical host:

As mentioned above the physical host data covers only a few hours and hence can't really be compared to the DBaaS data covering more than a week of data.

Finally the individual thread performance:


Physical host:

Both environments show pretty stable run times per thread over time, and the graphs confirm what is mentioned above: The physical host this time outperforms the DBaaS, however, as already mentioned, a slightly unfair comparison as the physical host didn't had to read and write encrypted blocks.

The next parts will cover comparisons to other cloud providers.