Feed aggregator

SQL Saturday Paris 2018 – Pré-conférence SQL Server Linux, Docker et Azure

Yann Neuhaus - Mon, 2018-05-28 14:41

The next SQL Saturday in Paris is coming soon and the agenda has been published by the @GUSS on twitter. Le prochain SQL Saturday Paris arrive bientôt et l’agenda a été publié sur Twitter par le @GUSS

blog 135 - 0 - SQL Sat Paris 2018

Cette année, un savant mélange de speakers francophones et internationaux, un plus pour les participants …

Un rapide coup d’œil à l’agenda – si on se focalise sur la partie moteur SQL Server – montre également qu’une bonne partie du stream rouge concerne Docker et l’automatisation. Intéressant :)

Bien entendu les sujets sur la performance et l’architecture auront toujours leur popularité et vous pourrez continuer à y assister :) Ce n’est pas moi qui vais m’en plaindre :)

 

Pré-conférence SQL Server au delà de Windows : installation, déploiement et gestion

sqlsatparis2018

Comme vous le savez déjà, Microsoft s’est ouvert à l’Open Source depuis SQL Server 2017 et a également renforcé son offre Azure avec notamment les instances managées. Par conséquent l’écho-système autour de SQL Server s’est considérablement agrandi. Cette année j’aurai l’opportunité de partager quelques expériences à ce sujet lors d’une pré- conférence qui se déroulera le vendredi 6 juin 2017.

Nous aborderons ensemble des thématiques bien connus par tout administrateur de données comme l’installation, la configuration, le monitoring, la performance ou encore la haute disponibilité dans des environnements autre que Windows.

Pour vous inscrire c’est par ici.

Au plaisir de vous y retrouver!

 

 

 

Cet article SQL Saturday Paris 2018 – Pré-conférence SQL Server Linux, Docker et Azure est apparu en premier sur Blog dbi services.

EDB Failover Manager in EDB containers in Minishift/OpenShift

Yann Neuhaus - Mon, 2018-05-28 12:47

In the last three posts we deployed an EDB database container and two pgpool instances, scaled that up to include a read only replica and finally customized the PostgreSQL instance with ConfigMaps. In this post will we look at how the EDB Failover Manager is configured in the database containers.

This are the pods currently running in my environment, two pgpool containers and two PostgreSQL containers:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-gk8dt   1/1       Running   1          9d
edb-as10-0-1-n5z4w   1/1       Running   0          3m
edb-pgpool-1-h5psk   1/1       Running   1          9d
edb-pgpool-1-tq95s   1/1       Running   1          9d

The first one (edb-as10-0-1-gk8dt) is the primary instance and EFM should be running there as well:

dwe@dwe:~$ oc rsh edb-as10-0-1-gk8dt
sh-4.2$ psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 f
(1 row)

sh-4.2$ ps -ef | grep efm
edbuser    202     1  0 08:45 ?        00:00:04 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-7.b10.el7.x86_64/jre/bin/java -cp /usr/edb/efm-3.0/lib/EFM-3.0.0.jar -Xmx32m com.enterprisedb.efm.main.ServiceCommand __int_start /etc/edb/efm-3.0/edb.properties
sh-4.2$ 

Looking at the configuration there are some interesting points:

sh-4.2$ cat /etc/edb/efm-3.0/edb.properties | egrep -v "^$|^#"
db.user=edbuser
db.password.encrypted=ca78865e0f85d15edc6c51b2e5c0a58f
db.port=5444
db.database=edb
db.service.owner=edbuser
db.service.name=
db.bin=/usr/edb/as10/bin
db.recovery.conf.dir=/edbvolume/edb/edb-as10-0-1-gk8dt/pgdata
jdbc.sslmode=disable
user.email=none@none.com
script.notification=
bind.address=172.17.0.6:5430
admin.port=5431
is.witness=false
local.period=10
local.timeout=60
local.timeout.final=10
remote.timeout=10
node.timeout=10
stop.isolated.master=false
pingServerIp=8.8.8.8
pingServerCommand=/bin/ping -q -c3 -w5
auto.allow.hosts=false
db.reuse.connection.count=0
auto.failover=true
auto.reconfigure=true
promotable=true
minimum.standbys=0
recovery.check.period=2
auto.resume.period=0
virtualIp=
virtualIp.interface=
virtualIp.prefix=
script.fence=
script.post.promotion=/var/efm/post_promotion_steps.sh %f
script.resumed=
script.db.failure=/var/efm/stopEFM
script.master.isolated=
script.remote.pre.promotion=
script.remote.post.promotion=
script.custom.monitor=
custom.monitor.interval=
custom.monitor.timeout=
custom.monitor.safe.mode=
sudo.command=sudo
sudo.user.command=sudo -u %u
log.dir=/var/log/efm-3.0
jgroups.loglevel=
efm.loglevel=
jvm.options=-Xmx32m
kubernetes.port.range=1
kubernetes.namespace=myproject
kubernetes.pod.labels=cluster=edb
kubernetes.master.host=172.30.0.1
kubernetes.master.httpsPort=443
create.database.master=/var/lib/edb/bin/createmasterdb.sh
create.database.standby=/var/lib/edb/bin/createstandbydb.sh
kubernetes.is.init.master=true

The last 8 lines are not there when you do a manual EFM installation so this is something specific in the container deployment. Apparently it is EFM that creates the master and the replica instance(s). The rest is more or less the default setup. The cluster status should be fine then:

sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Master      172.17.0.6           UP     UP        
	Standby     172.17.0.8           UP     UP        

Allowed node host list:
	172.17.0.6

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.8

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.6           0/5000140        
	Standby     172.17.0.8           0/5000140        

	Standby database(s) in sync with master. It is safe to promote.

We should be able to do a switchover:

sh-4.2$ /usr/edb/efm-3.0/bin/efm promote edb -switchover    
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     172.17.0.6           UP     UP        
	Master      172.17.0.8           UP     UP        

Allowed node host list:
	172.17.0.6

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.6

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.8           0/60001A8        
	Standby     172.17.0.6           0/60001A8        

	Standby database(s) in sync with master. It is safe to promote.

Seems it worked so the instances should have switched the roles and the current instance must be in recovery:

sh-4.2$ psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

Fine. This works as expected. So far for the first look at EFM inside the containers. It is not the same setup you’ll find when you install EFM on your own and EFM is doing more here than it does usually. A lot of stuff happens in the scripts provided by EDB here:

sh-4.2$ ls -la /var/lib/edb/bin/
total 72
drwxrwx---  2 enterprisedb root  4096 May 11 20:40 .
drwxrwx--- 24 enterprisedb root  4096 May 28 18:03 ..
-rwxrwx---  1 enterprisedb root  1907 Feb 17 17:14 cleanup.sh
-rwxrwx---  1 enterprisedb root  4219 May 10 22:11 createmasterdb.sh
-rwxrwx---  1 enterprisedb root  2582 May 11 03:30 createstandbydb.sh
-rwxrwx---  1 enterprisedb root  1491 May 10 22:12 dbcommon.sh
-rwxrwx---  1 enterprisedb root 10187 May 10 22:28 dbfunctions.sh
-rwxrwx---  1 enterprisedb root   621 May 10 22:15 dbsettings.sh
-rwxrwx---  1 enterprisedb root  5778 Apr 26 22:55 helperfunctions.sh
-rwxrwx---  1 enterprisedb root    33 Feb 18 03:43 killPgAgent
-rwxrwx---  1 enterprisedb root  5431 May 10 22:29 launcher.sh
-rwxrwx---  1 enterprisedb root   179 May 10 22:12 startPgAgent
-rwxrwx---  1 enterprisedb root   504 May 11 12:32 startPgPool

These scripts are referenced in the EFM configuration in several places and contain all the logic for initializing the cluster, starting it up, stopping and restarting the cluster, setting up replication and so on. To understand what really is going on one needs to understand the scripts (which is out of scope of this post).

 

Cet article EDB Failover Manager in EDB containers in Minishift/OpenShift est apparu en premier sur Blog dbi services.

Big Data Introduction - Workshop

Abhinav Agarwal - Mon, 2018-05-28 12:30
Our focus was clear - this was a level 101 class, for IT professionals in Bangalore who had heard of Big Data, were interested in Big Data, but were unsure how and where to dig their toe in the world of analytics and Big Data. A one-day workshop - with a mix of slides, white-boarding, case-study, a small game, and a mini-project - we felt, was the ideal vehicle for getting people to wrap their minds around the fundamental concepts of Big Data.






On a pleasant Saturday morning in January, Prakash Kadham and I conducted a one-day workshop, "Introduction to Big Data & Analytics". As the name suggests, it was a breadth-oriented introduction to the world of Big Data and the landscape of technologies, tools, platforms, distributions, and business use-cases in the brave new world of big data.

We started out by talking about the need for analytics in general, the kinds of questions analytics - also known as business intelligence sometimes - is supposed answer, and how most analytics platforms used to look like at the beginning of the decade. We then moved to what changed this decade, and the growth of data volumes, the velocity of data generation, and the increasing variety of data that rendered traditional means of data ingestion and analysis inadequate.

A fun game with cards turned out to be an ideal way to introduce the participants to the concepts behind MapReduce, the fundamental paradigm behind the processing and ingestion of massive amounts of data. After all the slides and illustrations of MapReduce, we threw in a curve-ball to the participants by telling them that some companies, like Google, had started to move away from MapReduce since it was deemed unsuitable for data volumes greater than petabyte!

The proliferation of Apache projects in almost every sphere of the Hadoop ecosystem meant that there are many, many choices for the big data engineer to choose from. Just on the subject of data ingestion, there is Apache Flume, Apache Sqoop, Apache Kafka, Apache Samza, Apache NiFi, and many others. Or take databases, where you have columnar, noSQL, document-oriented, graph databases to choose from, each optimized for slightly different use-cases - Hbase (the granddaddy of of noSQL databases), Cassandra (that took birth at Facebook), MongoDB (most suited for documents), Neo4j (a graph database), and so on.

Working through a case-study helps bring theory closer to practice, and the participants got to work on just that - two case-studies, one in the retail segment and the other in healthcare. Coming off the slides and lectures, the participants dove into the case-studies with enthusiasm and high-decibel interactions among all the participants.

The day passed off fast enough and we ended the day with a small visualization exercise, using the popular tool, Tableau. At the end of the long but productive day, the participants had one last task to complete - fill out a feedback form, which contained six objective questions and three free-form ones. It was hugely gratifying that all but one filled out the questionnaire. After the group photo and the workshop was formally over, Prakash and I took a look at the survey questionnaire that the participants had filled out, and did a quick, back-of-the-envelope NPS (Net Promoter Score) calculation. We rechecked our calculations and found we had managed an NPS of 100!

The suggestions we received have been most useful, and we are now working to incorporate the suggestions in the workshop. Among the suggestions was for us to hold a more advanced, Level 200, workshop. That remains our second goal!

Thank you to all the participants who took time out to spend an entire Saturday with us, for their active and enthusiastic participation, and to the valuable feedback they shared with us! A most encouraging start to 2018!

This post was first published on LinkedIn on Feb 5, 2018.
© 2018, Abhinav Agarwal.

Some tips for using Oracle Linux in Oracle Cloud

Wim Coekaerts - Mon, 2018-05-28 11:44

Creating an Oracle Linux instance in Oracle Cloud Infrastructure is easy. For the most part it is the same as creating your own image from the install media but we have done a few extra things that are very useful and you should know about :)

- with recent images, the yum repo file points to a local OCI mirror of yum.oracle.com (and a few repos that are only available on linux.oracle.com for subscribers - but since all OCI users' instances are technically   subscribers -> remember - Oracle Linux support is included with OCI instances at no additional cost or no extra button to click or anything)

So downloading RPMs or using yum on an OCI instance is very, very fast and it does not incur any network traffic to the outside world.

- a number of repos are enabled by default - ol7_UEKR4, _developer, _developer_EPEL, _ksplice _latest _optional_latest _addons _software collections. This gives you direct access to a ton of Oracle Linux related packages out of the box. But consider looking at a number of other repos that we have not enabled by default.  All you have to do is change enabled=0 to enabled=1 in /etc/yum.repos.d/public-yum-ol7.repo. Example : ol7_preview Alternatively you can enable a repo from the yum commandline : yum --enablerepo=ol7_preview <option>

The reason we don't enable these by default is that some of the packages in these channels are newer but, in some cases, pre-releases or developer versions of packages and we want to default to the "GA" versions but you are more than welcome to add these other packages of course. For instance, By default docker-engine gets you 17.06 but... if you want 17.12, then that's in the ol7_preview channel. So if you're looking for something new, don't forget to go look there before manually downloading stuff from a random 3rd party site. We might already have it available.

Other channels include nodejs8, gluster312, php72, MySQL8, developer_UEKR5 etc... Take a look at the repo file. You can always browse the repo content on https://yum.oracle.com. And if you want to see what's added on a regular basis, go check out the yum.oracle.com what's new page.  Anyway having EPEL and software collections gives you quick access to a very wide range of packages. Again, no need to download a yum repo rpm or download packages with wget or what not. Easy to create a development environment and deployment environment.

- some tools are installed by default. For instance an OCI OL instance comes with oci-utils pre-installed. oci-utils contains a number of command lines tools that make it very easy to work with attached block volumes, handle instance metadata, find your public-ip easily, configure your secondary VNICs. I wrote a blog entry about this a few months ago.

- easy access to OCI toolkits:

Want to use terraform? No problem, no need to download stuff, just get it from our yum repo. # yum install terraform terraform-provider-oci  We are typically just a few days behind the tagged releases of both terraform and the oci provider.

Want to use the OCI SDK and OCI CLI? # yum install python-oci-cli python-oci-sdk done. Same as with terraform, these packages are updated at most a few days after the github projects have release tags. No need to mess with updates or adding dependency RPMs. We take care of it and we update them for you

Using Postman For REST API on Oracle Cloud Infrastructure

Michael Dinh - Mon, 2018-05-28 10:21

Just completed quick training for Oracle Cloud Infrastructure (OCI)

REST API can be used on OCI for automation and presentation.

I find presentation for JSON results from CLI on server is UGLY.

Finally, I found POSTMAN which does to very nice job of presentation.

POSTMAN has results history, command history, search function.

https://www.getpostman.com/apps

 

Why might Consistent Reads be increasing with every iteration of a select cursor being run by PL/SQL?

Tom Kyte - Mon, 2018-05-28 03:26
<code></code>We have some PL/SQL that is running many sql statements, and taking an awfully long time to complete. (It?s Oracle code, btw, in E-Business, not custom). The problem seems to be a couple of the statements, (run thousands of times each) w...
Categories: DBA Blogs

Full text indexes in Oracle XE

Tom Kyte - Mon, 2018-05-28 03:26
hello Is it possible to combine b-tree indices with Full-Text indices in oracle 11(express)? Merci
Categories: DBA Blogs

Need some Oracle Request syntaxe to extract informations

Tom Kyte - Mon, 2018-05-28 03:26
hy all, it's my first discussion that i post . please help me: i work on a solution of supervision of oracle database, and i need two request syntaxe to extract informations: The first: the requset to show the most Oracle query consume cpu time ...
Categories: DBA Blogs

Is it possible - to show OS disk free space together with DBA_DATAFILES data ?

Tom Kyte - Mon, 2018-05-28 03:26
In some of my custoner's databases the DBAs are using AUTOEXTENSIBLE datafiles, but with many datafiles comparting the same filesystem, in this sense : tablespace A, datafiles /u01/oradata/ts_A_file01.dbf autoextend unlimited tablespace B, da...
Categories: DBA Blogs

Exadata Vs RAC

Tom Kyte - Mon, 2018-05-28 03:26
Tom ? My understanding of RAC( or grid computing) , we use cluster of not so expensive servers for higher availability. But Oracle is marketing Exadata ( expensive severs ? relatively speaking ) for performance / higher availability , so on. ( if ...
Categories: DBA Blogs

Filtering LOBs

Jonathan Lewis - Mon, 2018-05-28 02:25

A two-part question about the FILTER operation appeared on the Oracle-L list server a couple of days ago. The first part was a fairly common question – one that’s often prompted by the way the optimizer used to behave in older versions of Oracle. Paraphrased, it was: “Why is the total cost of the query so high compared to the sum of its parts?”

Here’s the query, and the execution plan.

 INSERT INTO TEMP
  SELECT DISTINCT 'id',
    PHT.emplid
  FROM PHOTO PHT
  WHERE 1               =1
  AND PHT.PHOTO IS NOT NULL
  AND NOT EXISTS
    (SELECT 'x'
    FROM TEMP TMP
    WHERE PHT.EMPLID=TMP.EMPLID_SRCH
    AND TMP.OPRID  = 'id'
    )
  ;  

  
-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          | 21210 |  3334K|  5802K  (2)| 00:03:47 |
|   1 |  LOAD TABLE CONVENTIONAL | TEMP     |       |       |            |          |
|*  2 |   FILTER                 |          |       |       |            |          |
|*  3 |    TABLE ACCESS FULL     | PHOTO    | 21211 |  3334K|   313   (1)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL     | TEMP     |     1 |    17 |   380   (2)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT 0 FROM " TEMP" "TMP" WHERE
              "TMP"."EMPLID_SRCH"=:B1 AND "TMP"."OPRID"='id'))
   3 - filter("PHT"."PHOTO" IS NOT NULL)
   4 - filter("TMP"."EMPLID_SRCH"=:B1 AND "TMP"."OPRID"='id')

Note that the “not exists” subquery against temp runs as a filter subquery with a cost of 380 for the tablescan. Combine that with the cost of 313 for the driving tablescan of photo and you might wonder why the resulting cost isn’t something like 693 – and in some old versions of Oracle that’s probably how it would be reported.

Historically the optimizer has been very bad about producing a final cost when queries have included subqueries – whether as filter subqueries in the predicate section or as scalar subqueries in the select list. Sometimes the cost would simply vanish from the final cost, sometimes it would be added just once to the final cost regardless of how many times the subquery might actually execute.

In this example the subquery against temp is a correlated subquery and might have to run once for every row in photo where the column photo was not null. At best it would have to run at least once for every distinct value of the photo.emplid column (the correlation column) found in those rows. In recent versions of Oracle the optimizer has tried to introduce some estimate of how many times the subquery would run as part of its calculation of the total cost. So (to a crude approximation) 5802K = 313 + N * 380. Unfortunately if we try to work backwards to N we find it would be about 15,267 which is about 72% of the 21,200 rows estimated as the result of the tablescan of photo – I haven’t tried to investigate the algorithms yet but presumably the optimizer makes some allowances somewhere for “self caching” as the subquery runs.

The more interesting part of the question came when the OP decided to test the effect of getting rid of the subquery. Check the costs in the resulting plan:


  INSERT INTO TEMP
  SELECT DISTINCT 'id',
    PHT.emplid
  FROM PHOTO PHT
  WHERE 1               =1
  AND PHT.UC_PBI_PHOTO IS NOT NULL;

  
-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          | 21211 |  3334K|  3659   (1)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEMP     |       |       |            |          |
|*  2 |   TABLE ACCESS FULL      | PHOTO    | 21211 |  3334K|  3659   (1)| 00:00:01 |
-------------------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
     2 - filter("PHT"."PHOTO" IS NOT NULL)

Note how the cost of the tablescan of photo has gone up from 313 in the previous query to 3,659 in the simpler query! How can a tablescan that drives a subquery have a lower cost than the tablescan on its own? Bear in mind that in both cases the Cost attributed to the operation “Table Access Full” is purely about scanning the rows in the photo table and is (or should be) entirely disconnected from the cost and frequency of the subquery.

The clue is in the table definition. The column photo.photo is a BLOB.

Models

I think there are potentially two errors in the optimizer displayed by this example. The first is that it’s adding in a cost that it shouldn’t even be considering; the second is that it’s being inconsistent in the way that it’s deriving that cost.

To demonstrate what I think is happening, I built a variant of the OP’s example as follows:


rem
rem     Script:         optimizer_lob_costs.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2018
rem     Purpose:
rem
rem     Last tested
rem             12.2.0.1
rem             12.1.0.2
rem

create table photo (
        emplid          varchar2(11) not null,
        photo           clob,
        other_col       varchar2(1000)
)
lob (photo) 
store as
        photo_lob(
        disable storage in row 
        cache
        logging
)
;

create unique index ph_uk on photo(emplid);

insert /*+ append */ into photo
select
        lpad(2 * rownum,10,0),
        rpad('x',1000),
        rpad('x',1000)
from
        all_objects
where
        rownum <= 10000 -- > comment to avoid wordpress format issue
;

commit;

create table temp(
        oprid           varchar2(30),
        emplid_srch     varchar2(11)
)
;

insert /*+ append */ into temp
select
        'id',
        lpad(2 * rownum,10,0)
from
        all_objects
where
        rownum <= 1000 -- > comment to avoid wordpress format issue
;

commit;

execute dbms_stats.gather_table_stats(user,'photo',method_opt=>'for all columns size 1', cascade=>true)
execute dbms_stats.gather_table_stats(user,'temp', method_opt=>'for all columns size 1', cascade=>true)


I’ve changed the BLOB to a CLOB defined with storage in row disabled, and I’ve introduced a varchar2() column of the same size as the CLOB column. I’ve declared the correlating column not null and created a unique index on it. Here are the two queries I want to review – slightly simplified versions of the original:


explain plan for
insert into temp(emplid_srch)
select 
        distinct pht.emplid
from 
        photo pht
where 
        1 = 1
and  pht.photo is not null
-- and     pht.other_col is not null
and     not exists (
                select /*+ no_unnest */
                        null
                from 
                        temp tmp
                where 
                        pht.emplid=tmp.emplid_srch
        )
;  

select * from table(dbms_xplan.display);

explain plan for
insert into temp(emplid_srch)
select
        distinct pht.emplid
from    photo pht
where   1               =1
and  pht.photo is not null
-- and     pht.other_col is not nulL
;  

select * from table(dbms_xplan.display);

As you can see I’ve had to include a /*+ no_unnest */ hint in my SQL to get the FILTER operation to appear in the plan (the OP had the hidden parameter “_unnest_subquery” set to false); I’ve also allowed for two variants of each query, one referencing the CLOB column the other referencing the varchar2() column. The only results I’ll show are for the queries accessing the CLOB, and here are the plans first with, then without, the subquery. Check the cost of the tablescan of the photo table in the two cases:


----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |  9999 |   956K| 10458   (3)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEMP  |       |       |            |          |
|*  2 |   FILTER                 |       |       |       |            |          |
|*  3 |    TABLE ACCESS FULL     | PHOTO | 10000 |   957K|   216   (1)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL     | TEMP  |     1 |    11 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TEMP" "TMP"
              WHERE "TMP"."EMPLID_SRCH"=:B1))
   3 - filter("PHT"."PHOTO" IS NOT NULL)
   4 - filter("TMP"."EMPLID_SRCH"=:B1)


----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       | 10000 |   957K|   285   (2)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEMP  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL      | PHOTO | 10000 |   957K|   285   (2)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PHT"."PHOTO" IS NOT NULL)

With the subquery in place the tablescan of photo reports a cost of 285, in the absence of the subquery it reports a cost of 216, a difference of 69. Repeating the test but using the varchar2() column the cost of the tablescan was 213 in both cases – suggesting that the variation was due to the column being a LOB.

With no further clues in the plan it looked like one of those rare occasions when I have to look at the 10053 (optimizer) trace file – and this is what I got from the 12.1.0.2 trace, looking at the section headed “SINGLE TABLE ACCESS PATH” for the photo table. First the base query without the subquery:


SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for PHOTO[PHT]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#2): PHOTO(LOB)
    AvgLen: 87 NDV: 0 Nulls: 0 Density: 0.000000
  Table: PHOTO  Alias: PHT
    Card: Original: 10000.000000  Rounded: 10000  Computed: 10000.000000  Non Adjusted: 10000.000000
  Scan IO  Cost (Disk) =   210.000000
  Scan CPU Cost (Disk) =   13571440.480000
  Total Scan IO  Cost  =   210.000000 (scan (Disk))
                         + 70.000000 (io filter eval) (= 0.007000 (per row) * 10000.000000 (#rows))
                       =   280.000000
  Total Scan CPU  Cost =   13571440.480000 (scan (Disk))
                         + 9138463.200000 (cpu filter eval) (= 913.846320 (per row) * 10000.000000 (#rows))
                       =   22709903.680000

Note the “Total Scan IO Cost” described at line 13 includes a component at line 12 labelled “(io filter eval)” – why, for the predicate “photo is null”, would we do any special I/O when that predicate can be met in the basic table scan.

(Note: A predicate like “lob_column is null” means there is no lob locator in place, so no lob access need be done for that test. In fact the related, but very different, predicate “length(lob_column) = 0” meaning the lob locator exists but the lob is “empty” could also be satisfied during the tablescan without reference to the physical lob segment(s) because the length of the lob is included in the lob locator.)

Let’s assume that the optimizer is incorrectly assuming the run-time engine will have to access the lob in some way to determine that the lob is null. The worst case scenario is that Oracle will start by accessing the LOBindex – so why don’t we check how big the LOBindex is. The first step I took was to check the object_id of the LOBindex and then do a tree dump (which showed 66 leaf blocks) and then I checked the segment header block and dumped that with the following results:


  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 127
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x01400447  ext#: 0      blk#: 70     ext size: 127
  #blocks in seg. hdr's freelists: 4
  #blocks below: 70
  mapblk  0x00000000  offset: 0
                   Unlocked
     Map Header:: next  0x00000000  #extents: 1    obj#: 194295 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x01400401  length: 127

See the “Highwater::” information at line 6 – the allocated space in the segment is the first 70 blocks of the first extent. That’s (almost certainly) where the incremental cost of 70 (single block I/Os) comes from.  (And I did couple of big updates to the LOB, designed to expand the LOBindex without changing the segment size of the underlying table, to corroborate that hypothesis.)

This brings us to the question of why the cost of the tablescan drops when the subquery is included. Again we generate the 10053 trace and examine the details under the “SINGLE TABLE ACCESS PATH”:


SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for PHOTO[PHT]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: PHOTO  Alias: PHT
    Card: Original: 10000.000000  Rounded: 10000  Computed: 10000.000000  Non Adjusted: 10000.000000
  Scan IO  Cost (Disk) =   210.000000
  Scan CPU Cost (Disk) =   13571440.480000
  Total Scan IO  Cost  =   210.000000 (scan (Disk))
                         + 3.500000 (io filter eval) (= 0.000350 (per row) * 10000.000000 (#rows))
                       =   213.500000
  Total Scan CPU  Cost =   13571440.480000 (scan (Disk))
                         + 656923.160000 (cpu filter eval) (= 65.692316 (per row) * 10000.000000 (#rows))
                       =   14228363.640000


In this case the “(io filter eval)” at line 10 is only 3.5 – and if you know your optimizer and how it handles subqueries you’re allowed to guess that could be one of Oracle’s standard guesses of 5% coming into play. (Again, growing the index seemed to corroborate this hypothesis.)

So here’s (possible) bug number 2: the first bug is adding a cost for accessing the LOBindex when there should be no need to  access the index at all – the execution plan says we will get 10,000 rows from the table, the filter predicate does report a cardinality reduced by just 1 on a column that has been declared with a uniqueness constraint, but a fairly commonly used “guess factor” of 5% is used as an indicator of the number of times the lob predicate will be tested. The various bits of the arithmetic are not consistent with each other.

Summary notes:

If you have a tablescan with a predicate that references a lob column then the cost of the tablescan includes the cost of the lob access – and there are cases where lob access is not needed but still gets costed {this is bug number 1 – the predicates are column is/is not null, and length(column) = / != 0)}.

If the lob data itself does not need to be accessed then the size of the lob index – which you can’t easily find – may have a significant impact on the cost of the tablescan.

If the query also includes predicates that result in the optimizer guessing about cardinality effects (1%, 5%, 0.25% are common guesses) then that guess may be used to scale the assumed (and potentially irrelevant) cost of the lob access. (There is scope for further experimentation in this area to examine the effects of “non-guess” predicates and the assumed order of application of predicates, e.g. are lob predicates costed as the last to be applied, does the algorithm for costing matched the execution order.)

As often happens it’s easy to see that there are oddities in the arithmetic that affect the cost of a query in ways that might make the optimizer pick a silly execution plan. Unfortunately it’s not easy to predict when you’re likely to see the effects of these oddities; the best we can do is remember that there is an anomaly with costing lob-based predicates and hope that we think of it when we see the optimizer picking a bad plan for reasons that initially are not obvious.

How to find the UUID of a device in Linux for Oracle ASM

Pakistan's First Oracle Blog - Sun, 2018-05-27 22:45
UUID stands for Universally Unique Identifier. I use UUID for my disk device, when I need to create and add disks for Oracle ASM, as UUID is independet of device name or mountpoint. So its always a good idea to include UUID of device in the fstab file in Linux.

So here is how to find the UUID of a device in Linux for Oracle ASM:




[root@bastion ~]$ ls -l /dev/disk/by-uuid
lrwxrwxrwx 1 root root 11 JAN 18 20:38 1101c254-0b92-42ca-b34a-6d283bd2d31b -> ../../sda2
lrwxrwxrwx 1 root root 11 JAN 18 20:38 11dc5104-C07b-4439-bdab-00a76fcb88df -> ../../sda1

HTH.


Categories: DBA Blogs

Oracle ADF BC REST - Performance Review and Tuning

Andrejus Baranovski - Sun, 2018-05-27 00:12
I thought to check how well ADF BC REST scales and how fast it performs. For that reason, I implemented sample ADF BC REST application and executed JMeter stress load test against it. You can access source code for application and JMeter script on my GitHub repository. Application is called Blog Visitor Counter app for a reason - I'm using same app to count blog visitors. This means each time you are accessing blog page - ADF BC REST service is triggered in the background and it logs counter value with timestamp (no personal data).

Application structure is straightforward - ADF BC REST implementation:


When REST service is accessed (GET request is executed) - it creates and commits new row in the background (this is why I like ADF BC REST - you have a lot of power and flexibility in the backend), before returning total logged rows count:


New row is assigned with counter value from DB sequence, as well as with timestamp. Both values are calculated in Groovy. Another bonus point for ADF BC REST, besides writing logic in Java - you can do scripting in Groovy - this makes code simpler:


Thats it - ADF BC REST service is ready to run. You may wonder, how I'm accessing it from blog page. ADF BC REST services as any other REST, can be invoked through HTTP request. In this particular case, I'm calling GET operation through Ajax call in JavaScript on client side. This script is uploaded to blogger HTML:


Performance

I'm using JMeter to execute performance test. In below example, REST GET request is invoked in infinite loop by 100 concurrent threads. This creates constant load and allows to measure how ADF BC REST application performs under such load:


ADF BC REST scales well, with 100 concurrent threads it does request processing in 0.1 - 0.2 seconds. If we would compare it to ADF UI request processing time, it would be around 10 times faster. This is expected, because JSF and ADF Faces UI classes are not used during ADF BC REST request. Performance test statistics for 100 threads, see Avg logged time in milliseconds:


Tuning

1. Referenced Pool Size and Application Module Pooling

ADF BC REST executes request is stateless mode, REST nature is stateless. I though to check, what this mean for Application Module tuning parameters. I have observed that changing Referenced Pool Size value doesn't influence application performance, it works either with 0 or any other value in the same way. Referenced Pool Size parameter is not important for ADF BC REST runtime:


Application performs well under load, there are no passivations/activations logged, even when Referenced Pool Size is set to zero.


However, I found that it is still important to keep Enable Application Module Pooling = ON. If you switch it OFF - passivation will start to appear, which consumes processing power and is highly unrecommended. So, keep Enable Application Module Pooling = ON.

2. Disconnect Application Module Upon Release

It is important to set Disconnect Application Module Upon Release = ON (read more about it - ADF BC Tuning with Do Connection Pooling and TXN Disconnect Level). This will ensure there will be always near zero DB connections left open:


Otherwise if we keep Disconnect Application Module Upon Release = OFF:


DB connections will not be released promptly:


This summarises important points related to ADF BC REST tuning.

Regular Expression is not working if the search criteria with LIKE and NOT LIKE in single input field

Tom Kyte - Sat, 2018-05-26 14:46
Hi Tom, Need your help ! Please find the LiveSQL link Thanks in Advance ! I have a table st_exp with s_desc column only, user has option to search by s_desc criteria. Scenario :The user may enter text critiria LIKE and NOT LIKE in the ...
Categories: DBA Blogs

Oracle APEX 18.1 – Features

John Scott - Sat, 2018-05-26 14:21

Oracle APEX 18.1 has introduced the capability to easily add Features to your APEX application.

So what are Features you might ask? Well in APEX you have always been able to add region types to pages, for example Charts, Reports etc. Think of Features are being pre-built components that span more than just a single region, or page. Current options for features include –

  • About Page
  • Access Control
  • Activity Reporting
  • Configuration Options
  • Feedback
  • Theme Style Selection

I’m sure in future versions of APEX this list will grow, but for now lets take a look how we can incorporate a feature.

Let’s create a brand new application via the Wizard –

2018-05-29_20-25-34.png

Once we select Next > we can see the new Features option

2018-05-29_20-26-40.png

At this stage you can add multiple Options, but lets add the ability for users to modify the Theme Style Selection by themselves.

2018-05-29_20-27-59.png

We can now create the application (as an aside – I love the new dynamic progress bar you see while the application gets created).

2018-05-29_20-28-37.png

Once the application is created, we can login to it and see that an Administration section has been created for us automatically.

2018-05-29_20-30-53.png

2018-05-29_20-31-29.png

You’ll notice the Desktop Theme Style drop-down already contains a number of themes

2018-05-29_20-32-25.png

Changing the Theme and pressing Apply Changes, the new theme takes effect immediately (no need to logout, neat!).

2018-05-29_20-33-01.png

A great feature is that Administrators can enable this feature to end users

2018-05-29_20-34-32.png

Once you do this, then a Customize link will appear at the bottom of the page for End Users.

2018-05-29_20-37-26.png

Clicking this link allows end users to select one of the themes and apply it to the application – note the theme choice is specific to the End User and persists between sessions.

2018-05-29_20-38-26.png

Now, you might say – but John we could already do something like this manually ourselves, but the point here is you didn’t need to do anything manually. Just by selecting an option and not writing a single line of code, you have declaratively added functionality to your application that allows your End Users to customise the application to how they like it – and they will like it!

Oracle SOA Suite 12c: How to deploy a BPEL onto a weblogic server

Dietrich Schroff - Sat, 2018-05-26 12:39
After you have created your BPEL inside Jdeveloper, you have to add an application server inside jdev:







 And here we go:

Then go to the applications view:







And after the deployment check via Application Server view, if the BPEL version was deployed:


Running Istio on Oracle Kubernetes Engine–the managed Kubernetes Cloud Service

Amis Blog - Sat, 2018-05-26 08:30

imageIn a recent post, I introduced the managed Oracle Cloud Service for Kubernetes, the Oracle Kubernetes Engine (OKE): https://technology.amis.nl/2018/05/25/first-steps-with-oracle-kubernetes-engine-the-managed-kubernetes-cloud-service/. A logical next step when working with Kubernetes in somewhat challenging situations, for example with microservice style architectures and deployments, is the use of Istio – to configure, monitor and manage the so called service mesh. Istio – https://istio.io – is brand new – not even Beta yet, although a first production release is foreseen for Q3 2018. It offers very attractive features, including:

  • intelligent routing of requests, including load balancing, A/B testing, content/condition based routing, blue/green release, canary release
  • resilicience – for example through circuit breaking and throttling
  • policy enforcement and access control
  • telemetry, monitoring, reporting

In this article, I will describe how I got started with Istio on the OKE cluster that I provisioned in the previous article. Note: there is really nothing very special about OKE for Istio: it is just another Kubernetes cluster, and Istio will do its thing. More interesting perhaps is the fact that I work on a Windows laptop and use a Vagrant/VirtualBox powered Ubuntu VM to do some of the OKE interaction, especially when commands and scripts are Linux only.

The steps I will describe:

  • install Istio client in the Linux VM
  • deploy Istio to the OKE Kubernetes Cluster
  • deploy the Bookinfo sample application with Sidecar Injection (the Envoy Sidecar is the proxy that is added to every Pod to handle all traffic into and out of the Pod; this is the magic that makes Istio work)
  • try out some typical Istio things – like traffic management and monitoring

The conclusion is that leveraging Istio on OKE is quite straightforward.

 

Install Istio Client in Linux VM

The first step with Istio, prior to deploying Istio to the K8S cluster, is the installation on your client machine of the istoctl client application and associated sources, including the Kubernetes yaml files required for the actual deployment. Note: I tried deployment of Istio using a Helm chart, but that did not work and it seems that Istio 0.7.x is not suitable for Helm (release 0.8 is supposed to be ready for Helm).

Following the instructions in the quick start guide: https://istio.io/docs/setup/kubernetes/quick-start.html

and working in the Ubuntu VM that I have spun up with Vagrant and Virtual Box, I go through these steps:

Ensure that the current OCI and OKE user kubie is allowed to do cluster administration tasks:

kubectl create clusterrolebinding k8s_clst_adm –clusterrole=cluster-admin –user=ocid1.user.oc1..aaaaaaaavorp3sgemd6bh5wjr3krnssvcvlzlgcxcnbrkwyodplvkbnea2dq

image

Download and install istioctl:

curl -L https://git.io/getLatestIstio | sh –

imageThen add the bin directory in the Istio release directory structure to the PATH variable, to make istoctl accessible from anywhere.

image

    Deploy Istio to the OKE Kubernetes Cluster

    The resources that were created during the installation of the Istio client include the yaml files that can be used to deploy Istio to the Kubernetes cluster. The command to perform that installation is very straightforward:

    kubectl apply -f install/kubernetes/istio.yaml

    The screenshot shows some of the steps executed when this command is kicked off:

    image

    The namespace istio-system is created, the logical container for all Istio related resources.

    SNAGHTML197befee

    The last two commands:

    kubectl get svc -n istio-system

    and

    kubectl get pods -n istio-system

    are used to verify what has been installed and is now running [successfully]in the Kubernetes cluster.

    The Dashboard provides a similar overview:

    image

    Deploy supporting facilities

    Istio is prepared for interaction with a number of facilities that will help with monitoring and tracing – such as Zipkin, Prometheus, Jaeger and Grafana. The core installation of Istio does not include these tools. Using the following kubectl commands, we can extend the istio-system namespace with these tools:

    kubectl apply -f install/kubernetes/addons/prometheus.yaml

    kubectl apply -f install/kubernetes/addons/zipkin.yaml

    kubectl apply -n istio-system -f https://raw.githubusercontent.com/jaegertracing/jaeger-kubernetes/master/all-in-one/jaeger-all-in-one-template.yml

    kubectl apply -f install/kubernetes/addons/grafana.yaml

    kubectl apply -f install/kubernetes/addons/servicegraph.yaml

    image

    Istio-enabled applications can be configured to collect trace spans using Zipkin or Jaeger. On Grafana (https://grafana.com/):  The Grafana add-on is a pre-configured instance of Grafana. The base image (grafana/grafana:4.1.2) has been modified to start with both a Prometheus data source and the Istio Dashboard installed. The base install files for Istio, and Mixer in particular, ship with a default configuration of global (used for every service) metrics. The Istio Dashboard is built to be used in conjunction with the default Istio metrics configuration and a Prometheus backend. More details on Prometheus: https://prometheus.io/ .

    To view a graphical representation of your service mesh,  use the Service Graph Add-On:  https://istio.io/docs/tasks/telemetry/servicegraph.html .

    For log gathering with fluentd and writing them to Elastic Stack, see: https://istio.io/docs/tasks/telemetry/fluentd.html

     

     

    image

    Deploy the Bookinfo sample application with Sidecar Injection

    (the Envoy Sidecar is the proxy that is added to every Pod to handle all traffic into and out of the Pod; this is the magic that makes Istio work)

    The Bookinfo sample application (https://istio.io/docs/guides/bookinfo.html) is shipped as part of the Istio client installation. This application is composed of several (versions of) microservices that interact. These services and their interactions can be used to investigate the functionality of Istio.

    image

    To install the Bookinfo application, all you need to do:

    kubectl apply -f <(istioctl kube-inject –debug -f samples/bookinfo/kube/bookinfo.yaml)

    The istoctl kube-inject instruction (see https://istio.io/docs/reference/commands/istioctl.html) performs a preprocessing of the bookinfo.yaml file – injecting the specs for the Envoy Sidecar. Note: automatic injection of the sidecar into all Pods that get deployed is supported in Kubernetes 1.9 and higher. I did not yet get that to work, so I am using manual or explicit injection.

    image

    We can list the pods and inspect one of them:

    image

    The product page pod was defined with a single container – with a Python web application. However, because of the injection that Istio performed prior to creation of the Pod on the cluster, the Pod actually contains more than a single container: the istio-proxy was added to the pod. The same thing happened in the other pods in this bookinfo application.

    SNAGHTML199d2277

     

    This is what the Bookinfo application looks like:

    image

    (note: using kubectl port-forward I can make the application accessible from my laptop, without having to expose the service on the Kubernetes cluster)

    Try out some typical Istio things – like traffic management and monitoring

      Just by accessing the application, metrics will be gathered by the sidecar and shared with Prometheus. The Prometheus dashboard visualizes these metrics:

      image

      Zipkin helps to visualize the end to end trace of requests through the service mesh. Here is the request to the productpage dissected:

      image

      A drilldown reveals:

      image

      Reviews apparently is called sequentially, after the call to Details is complete. This may be correct, but perhaps we can improve performance by performing these calls in parallel. The calls to review takes much longer than the one to reviews. Both are still quite fast – no more than 35 ms.

      The Grafana dashboard plugin for Istio provides an out of the box dashboard on the HTTP requests that happen inside the service mesh. We can see the number of requests and the success rate (percentage of 200 and 300 response codes vs 400 and 500 responses)

      image

      Here are some more details presented in the dashboard:

      image

       

      At this point I am ready to start using Istio in anger – for my own microservices.

      Resources

      Istio.io – https://istio.io/

      Istio on Kubernetes – Quickstart Guide – https://istio.io/docs/setup/kubernetes/quick-start.html

      Working with the Istio Sample Application Bookinfo – https://istio.io/docs/guides/bookinfo.html

      YouTube: Module 1: Istio – Kubernetes – Getting Started – Installation and Sample Application Review by Bruno Terkaly – https://www.youtube.com/watch?v=ThEsWl3sYtM

      Istioctl reference: https://istio.io/docs/reference/commands/istioctl.html

      The post Running Istio on Oracle Kubernetes Engine–the managed Kubernetes Cloud Service appeared first on AMIS Oracle and Java Blog.

      Install MockupTiger wireframes on MacOS

      Nilesh Jethwa - Fri, 2018-05-25 23:07

      In this tutorial we will explore the option to install a specially designed download for MacOS. NOTE: If you are using Windows, Linux, MacOS and have your own setup of a LAMP, XAMPP, WAMP, MAMP or any other stack that … Continue reading ?

      Via: MockupTiger Wireframes

      Top 10 Albums Meme

      Greg Pavlik - Fri, 2018-05-25 21:27

      I’ve been hit by a barrage of social media posts on people’s top 10 albums, so I thought I would take a look at what I have listened to the most in the last 5 years or so. I’m not claiming these are my favorites or “the best” albums recorded (in fact there are many better albums I enjoy). But I was somewhat surprised to find that I do return to the the same albums over and over, so here’s the top 10, in no particular order.

      1)Alina, Arvo Part

      If you were going to stereotype and box in Part’s work, this would be a good album to use. It’s also amazing enough that it could run on a continuous loop forever and I’d be pretty happy with that.

      2)Benedicta: Marian Chants from Norcia, Monks of Norcia

      Yes, the music hasn’t changed much from the middle ages. And yes, these are actually monks singing, who somehow managed to top the Billboard charts. The term to use is sublime – this music is quintessentially music of peace and another album that bears repetition with ease.

      3) Mi Sueno, Ibrahim Ferrer

      I know the whole Bueno Vista Social Club thing was trendy, but this music – Cuban bolero to be precise – is full of passion, charm, and romance: it music for human beings (which is harder and harder to find these days). This is at once a work of art and a testament to real life.

      4) Dream River, Bill Callahan

      I don’t even know what to categorize this music as: it’s not popular music, rock, easy listening, country or folk. But it has elements of most of those. Callahan’s baritone voice sounds like someone is speaking to you rather than singing. This album just gets better with the years of listening and it’s by far his best.

      5) The Harrow and the Harvest, Gillian Welch

      Appalachian roots, contemporary musical twists – I don’t know what they call this: alt-blue grass? In any case, its Welch’s best album and a solid, if somewhat dark, listen.

      6) In the Spur of the Moment, Steve Turre

      Turre does his jazz trombone (no conch shells on this album – which I am happy about) along with Ray Charles on piano for the first third or so, later trending toward more Afro-Cuban jazz style. I know the complaint on this one is that it feels a bit passionless in parts, but it’s a hard mix not to feel good about.

      7) Treasury of Russian Gypsy Songs, Marusia Georgevskaya and Sergei Krotkoff

      I’ll admit that it sounds like Georgevskaya has smoked more than a few cigarettes. But this is timeless music, a timeless voice, from a timeless culture. Sophie Milman’s Ochi Chernye is sultry and seductive (she is really fantastic), but somehow I like Marusia’s better.

      9) Skeleton Tree, Nick Cave

      Nick Cave is uneven at best and often mediocre but this album is distilled pain in poet form and a major work of art. For some reason I listen to this end to end semi regularly on my morning commute.

      10) Old Crow Medicine Show, Old Crow Medicine Show

      End to end, just hits the right notes over and over again. From introspective to political to just plain fun, these guys made real music for real people at their peak. Things fell apart after Willie Watson, but there is an almost perfect collection of authentic songs.

      Which Bitnami service to choose in the Oracle Cloud Infrastructure?

      Yann Neuhaus - Fri, 2018-05-25 15:40

      In the Oracle Cloud PaaS you have a marketplace where you can choose your service. Some are packaged from Bitnami and available on multiple OS. My first idea is that in PaaS you do not care about the OS. But Oracle Cloud has this very nice feature where you still have full access to the OS, as root, even in PaaS. Then, you choose the Linux distribution of your preference. Except if performance is different. They run on different Linux kernels. Is Oracle Linux Unbreakable Kernel more efficient?

      We need to compare with relevant and reliable measures. And for that I’ve created a Postgres service and used Kevin Closson SLOB method, as I’m a lucky beta tester for pgio. I’ll post later about the creation of the Postgres service in the Oracle Cloud.

      Cached IO

      First, I’ve run cached IO to measure CPU performance.

      I’ve run with the following settings in pgio.conf:

      UPDATE_PCT=0
      RUN_TIME=300
      NUM_SCHEMAS=4
      NUM_THREADS=1
      WORK_UNIT=255
      SCALE=1G

      This is 4 schemas with 1GB of data. This fit in my 30GB host. Actually, here is a vmstat sample during the run showing 5GB cached and large free memory:

      procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
      r b swpd free buff cache si so bi bo in cs us sy id wa st
      4 0 0 15508168 12444 5495652 0 0 0 0 1530 1631 28 22 50 0 0
      4 0 0 15508292 12452 5495648 0 0 0 4 1506 1564 27 23 50 0 0

      There’s no I/O to block device here, which is my goal.

      I’ve run 10 times the runit.sh and here is the summary of main result with IOPS:


      $ for i in allopc@144.21.82.244.out allopc@144.21.82.255.out allopc@144.21.82.252.out ; do grep os-release $i | grep PRETTY | tail -1 ; grep x86 $i | tail -1 ; grep -E "runit.out.*DBNAME" $i | tail -10 | nl ; done
       
      Fri May 25 11:56:11 UTC 2018 /etc/os-release : PRETTY_NAME="Ubuntu 16.04.4 LTS"
      Linux ubuntu 4.4.0-127-generic #153-Ubuntu SMP Sat May 19 10:58:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
      1 Fri May 25 11:11:05 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >393232< CACHE_HITS/s >15711<
      2 Fri May 25 11:16:06 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398179< CACHE_HITS/s >16000<
      3 Fri May 25 11:21:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >402080< CACHE_HITS/s >16019<
      4 Fri May 25 11:26:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >404682< CACHE_HITS/s >16086<
      5 Fri May 25 11:31:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >408524< CACHE_HITS/s >16327<
      6 Fri May 25 11:36:08 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >409209< CACHE_HITS/s >16390<
      7 Fri May 25 11:41:08 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >403647< CACHE_HITS/s >16327<
      8 Fri May 25 11:46:09 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397440< CACHE_HITS/s >15894<
      9 Fri May 25 11:51:09 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397273< CACHE_HITS/s >15956<
      10 Fri May 25 11:56:11 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >396906< CACHE_HITS/s >15904<
       
      Fri May 25 11:54:56 UTC 2018 /etc/os-release : PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
      Linux debian 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 GNU/Linux
      1 Fri May 25 11:09:53 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395672< CACHE_HITS/s >15882<
      2 Fri May 25 11:14:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >401400< CACHE_HITS/s >16188<
      3 Fri May 25 11:19:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >391551< CACHE_HITS/s >15764<
      4 Fri May 25 11:24:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >393827< CACHE_HITS/s >15802<
      5 Fri May 25 11:29:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >404462< CACHE_HITS/s >16198<
      6 Fri May 25 11:34:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >392712< CACHE_HITS/s >15732<
      7 Fri May 25 11:39:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >399389< CACHE_HITS/s >16063<
      8 Fri May 25 11:44:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >390283< CACHE_HITS/s >15567<
      9 Fri May 25 11:49:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >391426< CACHE_HITS/s >15771<
      10 Fri May 25 11:54:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >392760< CACHE_HITS/s >15874<
       
      Fri May 25 11:53:58 UTC 2018 /etc/os-release : PRETTY_NAME="Oracle Linux Server 7.5"
      Linux b5e501 4.1.12-124.15.2.el7uek.x86_64 #2 SMP Tue May 22 11:52:31 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
      1 Fri May 25 11:08:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395815< CACHE_HITS/s >15759<
      2 Fri May 25 11:13:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395346< CACHE_HITS/s >16009<
      3 Fri May 25 11:18:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395340< CACHE_HITS/s >15898<
      4 Fri May 25 11:23:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >402556< CACHE_HITS/s >16200<
      5 Fri May 25 11:28:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397847< CACHE_HITS/s >16039<
      6 Fri May 25 11:33:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398848< CACHE_HITS/s >16027<
      7 Fri May 25 11:38:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398817< CACHE_HITS/s >16089<
      8 Fri May 25 11:43:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398878< CACHE_HITS/s >15961<
      9 Fri May 25 11:48:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395885< CACHE_HITS/s >15606<
      10 Fri May 25 11:53:58 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395965< CACHE_HITS/s >15893<

      I’ve put this data in Excel to show the picture where, basically, all 3 environments perform exactly the same:

      CapturePGLIOPS

      Physical IO

      For the second test, I wanted to test physical IOPS. But not to measure disk performance, which is the same for my 3 services. I want to do physical I/O only to see if there’s a difference in context switches when doing non-blocking I/O – which we do not see in the previous test because they were filesystem cache hits. Then I kept the small scale of 4 sessions with 1GB so that there’s a good chance that it remains in the storage cache. But I reduced the memory in order to have less than 4GB in filesystem cache.

      pgio comes with an utility (pgio_reduce_free_memory.sh) to allocate enough huge page to limit the filesystem cache:


      $ sudo bash pgio/pgio_reduce_free_memory.sh 2
       
      Taking action to reduce free memory down to 2GB available.
      total used free shared buff/cache available
      Mem: 30886100 124676 30698952 19088 62472 30469900
      Swap: 0 0 0
       
      Attempting to allocate 13966 huge pages
      MemAvailable: 1869148 kB
      HugePages_Total: 13966

      This is perfect: 13966 huge pages, that’s 27 GB in my 30GB VM that cannot be used by the filesystem, so that my 4x1GB will need I/O calls to the disk.

      Here is a vmstat extract to confirm that the filesystem cache is less than 2GB

      procs -----------memory---------- ---swap-- -----io---- ---system-- ------cpu-----
      r b swpd free buff cache si so bi bo in cs us sy id wa st
      0 4 0 154416 2344 2015204 0 0 93025 0 32425 24295 2 3 51 44 1
      0 4 0 153816 2428 2015848 0 0 94923 21 32400 24414 2 3 51 44 1

      runit.sh calls vmstat with a 3 seconds delay so you can see a higher number of block/s and context switches.

      Here are the interesting lines from the runit.sh output:

      $ for i in allopc@144.21.89.85.out allopc@144.21.89.53.out allopc@144.21.89.26.out ; do grep os-release $i | grep PRETTY | tail -1 ; grep x86 $i | tail -1 ; grep -E "runit.out.*DBNAME" $i | tail -10 | nl ; done
       
      Fri May 25 14:58:47 UTC 2018 /etc/os-release : PRETTY_NAME="Ubuntu 16.04.4 LTS"
      Linux ubuntu 4.4.0-127-generic #153-Ubuntu SMP Sat May 19 10:58:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
      1 Fri May 25 14:13:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >1214< CACHE_HITS/s >55<
      2 Fri May 25 14:18:42 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >4545< CACHE_HITS/s >195<
      3 Fri May 25 14:23:43 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >17053< CACHE_HITS/s >682<
      4 Fri May 25 14:28:43 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18865< CACHE_HITS/s >801<
      5 Fri May 25 14:33:44 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18933< CACHE_HITS/s >794<
      6 Fri May 25 14:38:44 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18832< CACHE_HITS/s >777<
      7 Fri May 25 14:43:45 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18826< CACHE_HITS/s >757<
      8 Fri May 25 14:48:46 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >19229< CACHE_HITS/s >819<
      9 Fri May 25 14:53:46 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19408< CACHE_HITS/s >835<
      10 Fri May 25 14:58:47 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19524< CACHE_HITS/s >799<
       
      Fri May 25 14:58:41 UTC 2018 /etc/os-release : PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
      Linux debian 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 GNU/Linux
      1 Fri May 25 14:13:35 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >1727< CACHE_HITS/s >82<
      2 Fri May 25 14:18:36 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >10743< CACHE_HITS/s >534<
      3 Fri May 25 14:23:37 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18660< CACHE_HITS/s >763<
      4 Fri May 25 14:28:37 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18496< CACHE_HITS/s >811<
      5 Fri May 25 14:33:38 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18537< CACHE_HITS/s >757<
      6 Fri May 25 14:38:38 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18647< CACHE_HITS/s >774<
      7 Fri May 25 14:43:39 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18838< CACHE_HITS/s >775<
      8 Fri May 25 14:48:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18686< CACHE_HITS/s >786<
      9 Fri May 25 14:53:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18742< CACHE_HITS/s >782<
      10 Fri May 25 14:58:41 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >18634< CACHE_HITS/s >793<
       
      Fri May 25 14:57:25 UTC 2018 /etc/os-release : PRETTY_NAME="Oracle Linux Server 7.5"
      Linux b5e501 4.1.12-124.15.2.el7uek.x86_64 #2 SMP Tue May 22 11:52:31 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
      1 Fri May 25 14:12:20 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >17025< CACHE_HITS/s >721<
      2 Fri May 25 14:17:21 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19331< CACHE_HITS/s >792<
      3 Fri May 25 14:22:21 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19271< CACHE_HITS/s >770<
      4 Fri May 25 14:27:22 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19324< CACHE_HITS/s >802<
      5 Fri May 25 14:32:22 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18943< CACHE_HITS/s >802<
      6 Fri May 25 14:37:23 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19202< CACHE_HITS/s >818<
      7 Fri May 25 14:42:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18999< CACHE_HITS/s >803<
      8 Fri May 25 14:47:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19459< CACHE_HITS/s >823<
      9 Fri May 25 14:52:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19138< CACHE_HITS/s >836<
      10 Fri May 25 14:57:25 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18958< CACHE_HITS/s >781<

      And I’ve graphed them:
      CapturePGPIOPS

      As I hit the storage here, I needed a few runs to warm up the storage cache and get the I/O I wanted: low latency rfom storage cache, but involving context switches on the server. And basically, all 3 alternatives (Ubuntu with Linux 4.4, Debian with Linux 4.9 and OEL with the unbreakable kernel 4.1) behave the same. From these tests, I can say that the performance is not the major criteria to choose one of the PaaS alternatives. Just choose the distribution you like.

      About pgio, it is great to get performance measures that are reliable and focused on what I want to test. I had no problems to run it on all 3 platforms. Just needed to apt-get / yum to install systat and bc which are not there by default.

       

      Cet article Which Bitnami service to choose in the Oracle Cloud Infrastructure? est apparu en premier sur Blog dbi services.

      Pages

      Subscribe to Oracle FAQ aggregator