Alan Baugher

Database High Performance

Discussion created by Alan Baugher Employee on Aug 25, 2018
Latest reply on Aug 25, 2018 by Ashok kumar Muthu

Team,

 

I have collected notes during an exercise to get the best performance from an Oracle r12c RAC Database for the Identity Suite solution stack.

 

Jason_Wilcox and I have been working with a customer's IM and DBA team using Oracle AWR reports & the CA IM Bulk Load Client to leverage a peak I/O use-case.   

-      Using these two (2) process; along with other monitoring tools, we have been very successfully to achieve over 10,000 rows submitted for the create user use-case (with endpoint creation for AD & MS-Exchange mailboxes) in less than 25 minutes.

 

Some of the performance tweaks were at the database tier, others at the J2EE/JMS tier, and also within the business logic of the solution stack (streamlining "modify operations" to minimize I/O impact)

 

Below are summary pages that I will update as I refresh my notes:

 

 

 

Below diagram(s) shows the various tiers where updates were made.

 

 

 

 

 

History:    We started with this below process.  While this configuration below worked; it would not scale to the larger numbers we needed.   (To see AWR report for this initial deployment - scroll to bottom)

 

 

Our intermediate state, and final state:

 

 

List of changes made for the database and J2EE tier.

 

 

Dual cluster view in two (2) data centers:

 

 

         

New DB Service Names to replace the single VIP address:

 

 

 

Example of jboss-cli.sh script used to update the J2EE/Wildfly standalone-full-ha.xml configuration file (on the fly).

 

 

Example of using the jboss-cli.sh process to view the stats of the DB connection pool.

- This process assisted with us deciding to increase the default connection pool for the TP data source to a larger number, and then lower the max value for the other data source values.   This process also confirmed that the five (5) node J2EE cluster was working correctly and sharing the load among all members.   We did discover that we needed to explicitly declare the JMS pool to use round-robin policy, otherwise one node was being overwhelmed.

/subsystem=messaging/hornetq-server=default/pooled-connection-factory=hornetq-ra/:write-attribute(name=connection-load-balancing-policy-class-name,value=org.hornetq.api.core.client.loadbalance.RoundRobinConnecti"margin-top: 0pt; margin-bottom: 0pt; margin-left: 0in;"> 

 

Example of script used:

 

#!/bin/bash

pool7="iam_im-imworkflowdb-ds";
pool6="iam_im-imreportsnapshotdb-ds";
pool5="iam_im-imobjectstoredb-ds";
pool4="iam_im-imauditdb-ds";
pool3="iam_im-imarchivedb-ds";
pool2="iam_im-igdatasource-ds";
pool1="iam_im-imtaskpersistencedb-ds";
max=1;

while [ $max -eq 1 ]
do
clear
count=0;
echo "pool|activeCount|idleCount|inUseCount|maxUsedCount|maxWaitCount|totalBlockingTime";
while [ $count -lt 7 ]
do
data1="";
count=$(($count+1));
if [ $count -eq 1 ]
then
pool=$pool1;
elif [ $count -eq 2 ]
then
pool=$pool2;
elif [ $count -eq 3 ]
then
pool=$pool3;
elif [ $count -eq 4 ]
then
pool=$pool4;
elif [ $count -eq 5 ]
then
pool=$pool5;
elif [ $count -eq 6 ]
then
pool=$pool6;
elif [ $count -eq 7 ]
then
pool=$pool7;
fi

data1=`/opt/CA/wildfly-8.2.1.Final/bin/jboss-cli.sh -c "/subsystem=datasources/data-source=$pool/statistics=pool:read-resource(include-runtime=true)"`;
activeCount=`echo $data1 | sed 's/.*ActiveCount\" => \"//;s/\".*//'`;


idleCount=`echo $data1 | sed 's/.*IdleCount\" => \"//;s/\".*//'`;
maxUsedCount=`echo $data1 | sed 's/.*MaxUsedCount\" => \"//;s/\".*//'`;
maxWaitCount=`echo $data1 | sed 's/.*MaxWaitCount\" => \"//;s/\".*//'`;
inUseCount=`echo $data1 | sed 's/.*InUseCount\" => \"//;s/\".*//'`;
totalBlockingTime=`echo $data1 | sed 's/.*TotalBlockingTime\" => \"//;s/\".*//'`;
waitCount=`echo $data1 | sed 's/.*WaitCount\" => \"//;s/\".*//'`;

echo "$pool|$activeCount|$idleCount|$inUseCount|$maxUsedCount|$maxWaitCount|$totalBlockingTime";

done;
sleep 15;
done;

 

 

 

 

 

 

Let me know if you find this of value

 

 

Cheers,

 

Alan

 

 

 

 

PS.  Example of the AWR report that help identified the I/O issue with IM Task Persistence Database's object12_5 table due to RAC configuration; and the need to add indexes to the PX Data Element table.

 

Example to run a AWR report from Oracle GUI:

select dba_hist_snapshot.* from dba_hist_snapshot order by 5 desc;
select output from table (dbms_workload_repository.awr_global_report_html((select dbid from v$database),'1,2’,<Add_Beginning_Time_Index_Here>,<Add_Next_Time_Index_Here>));

 

 

Example of AWR report (summary sections):

 

 

 

 

Enclosing the db connection pool monitor script (shell script) updated for the Identity Suite vApp.

Attachments

Outcomes