This was first published on https://blog.dbi-services.com/testing-oracle-on-exoscale-ch (2016-04-28)
Republishing here for new followers. The content is related to the the versions available at the publication date
My last post came from a discussion at SITB with exoscale. They are doing Cloud hosting with datacenters in Switzerland. In Switzerland a lot of companies cannot host their data outside of the country, which is a no-go for the big Cloud providers.
After the discussion they gave me a coupon for a trial IaaS instance. And if you follow my blog you should know that when I have a trial access, there are good chances that I try it… The provisioning interface is really simple: You choose the datacenter (I choose the one near Zurich, in a disused military bunker) and a VM with 2 vCPU and 2GB RAM. I want to install Oracle Database. I choose Linux CentOS. I’ll probably try CoreOS later if I’ve some credits remaining.
I’ll have to connect to it so I open the ssh port, which is done with a simple clic, and I add the listener port as my goal is to run SLOB on an Oracle Database and monitor performance with Orachrome Lighty – all components of my favorite ecosystem…
I don’t like passwords so I import my ssh public key:
and the system is ready in few seconds:
Now I can connect with ssh on the ip address provided, as root, and set the system for Oracle Database:
[root@franck ~]# groupadd oinstall [root@franck ~]# groupadd dba [root@franck ~]# useradd -g oinstall -G dba oracle [root@franck ~]# passwd oracle
I put my public ssh key into oracle authorized_keys. I set the kernel settings. I upload the 12c binaries and ready to install.
I install SLOB (https://kevinclosson.net/slob/), useing the create database kit and run the default PIO workload:
UPDATE_PCT: 25 RUN_TIME: 3600000 WORK_LOOP: 10000000 SCALE: 100M (12800 blocks) WORK_UNIT: 64 REDO_STRESS: LITE HOT_SCHEMA_FREQUENCY: 0 DO_HOTSPOT: FALSE HOTSPOT_MB: 8 HOTSPOT_OFFSET_MB: 16 HOTSPOT_FREQUENCY: 3 THINK_TM_FREQUENCY: 0 THINK_TM_MIN: .1 THINK_TM_MAX: .5
Result: 1500 IOPS with latency in few milliseconds for 8k reads.
Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 1.0 0.2 0.05 3.89 DB CPU(s): 0.1 0.0 0.00 0.39 Background CPU(s): 0.0 0.0 0.00 0.00 Redo size (bytes): 255,885.4 51,476.9 Logical read (blocks): 1,375.2 276.7 Block changes: 652.0 131.2 Physical read (blocks): 1,261.9 253.9 Physical write (blocks): 324.1 65.2 Read IO requests: 1,261.7 253.8 Write IO requests: 310.8 62.5 Read IO (MB): 9.9 2.0 Write IO (MB): 2.5 0.5
Wait events (I don’t know why the sum is above 100%…)
Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB Wait Event Waits Time (sec) Avg(ms) time Class ------------------------------ ----------- ---------- ---------- ------ -------- db file sequential read 1,826,324 2442.5 1.34 48.2 User I/O db file parallel read 74,983 2285.2 30.48 45.1 User I/O DB CPU 509.2 10.0
And wait event histogram in microseconds for single block reads:
EVENT WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT ------------------------------ --------------- ---------- ------------------------------ db file sequential read 1 0 1 microsecond db file sequential read 2 0 2 microseconds db file sequential read 4 0 4 microseconds db file sequential read 8 0 8 microseconds db file sequential read 16 0 16 microseconds db file sequential read 32 0 32 microseconds db file sequential read 64 0 64 microseconds db file sequential read 128 191 128 microseconds db file sequential read 256 3639 256 microseconds db file sequential read 512 71489 512 microseconds db file sequential read 1024 838371 1 millisecond db file sequential read 2048 887138 2 milliseconds db file sequential read 4096 21358 4 milliseconds db file sequential read 8192 3625 8 milliseconds db file sequential read 16384 1659 16 milliseconds db file sequential read 32768 1863 32 milliseconds db file sequential read 65536 4817 65 milliseconds db file sequential read 131072 3721 131 milliseconds db file sequential read 262144 5 262 milliseconds
Now time to look at CPU. The processors are not the latest Intel generation:
Model name: Intel Xeon E312xx (Sandy Bridge) CPU MHz: 2593.748
I’ve run same slob.conf except that I reduce the scal to fit in buffer cache and do no updates
Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 0.8 2.1 0.00 0.66 DB CPU(s): 0.8 2.0 0.00 0.65 Background CPU(s): 0.0 0.0 0.00 0.00 Redo size (bytes): 8,257.2 20,944.9 Logical read (blocks): 411,781.5 1,044,502.5 Block changes: 41.8 106.1 Physical read (blocks): 10.9 27.7 Physical write (blocks): 3.5 8.9 Read IO requests: 9.9 25.2 Write IO requests: 2.7 6.7
That’s 412000 logical reads per seconds per CPU. However I’ve only 0.8 CPU here.
I nearly forgot the great feature of 12c AWR report: the active-html with it’s ‘tetris’ view. Here it is:
Ok it seems I had a little delay at the beginning of the run before it was 100% in CPU. Knowing that, I run a report on a shorter period:
Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 1.0 2.1 0.00 2.81 DB CPU(s): 1.0 2.1 0.00 2.79 Background CPU(s): 0.0 0.0 0.00 0.00 Redo size (bytes): 8,518.5 18,255.4 Logical read (blocks): 518,770.0 1,111,743.4 Block changes: 41.0 87.9 Physical read (blocks): 11.8 25.3 Physical write (blocks): 1.2 2.6 Read IO requests: 11.5 24.7
Here are the numbers then. Up to 520000 logical reads per second per CPU.
That’s not extreme performance, but it’s an acceptable alternative to on-premises physical server. Provisioning is really easy.
I realize that the last sentence may be misleading. Actually the performance is not bad at all. I was just comparing with the same tests I did on Oracle Cloud Services where I hit the storage head cache most of the time. The IOPS may need further tests. However the following workload with intensive updates (large buffer cache, PCT_UPDATE=25%) is not bad at all:
Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 1.0 0.0 0.00 4.17 DB CPU(s): 0.9 0.0 0.00 3.88 Background CPU(s): 0.1 0.0 0.00 0.00 Redo size (bytes): 38,568,625.6 51,953.1 Logical read (blocks): 202,771.0 273.1 Block changes: 97,125.0 130.8 Physical read (blocks): 4.5 0.0 Physical write (blocks): 2,316.2 3.1 Executes (SQL): 2,971.5 4.0 Rollbacks: 0.0 0.0 Transactions: 742.4
The CPU (according to Kevin Closson) is ok:
. @dbiservices 518K LIOPS for SNP-EP E3 is good! Nice post. *awesome* use of SLOB as a science tool https://t.co/S0X13gx9xT
— Kevin (@kevinclosson) April 28, 2016
Hey Franck,
It was nice to meet you at SITB Geneva.
Thanks for this awesome blog post! You’ve done that great, explaining very clearly all the steps that allow anyone to build it’s own Oracle database on a Swiss Datacenter(s).
We’re glad to see that Oracle database works perfectly with Exoscale Open Cloud
You’re right this is not the latest CPU gen but new hardware will come very soon so we’ll keep you posted thus you’ll able to perform another test.
Hope to see you soon Cheers