Symantec Access Management

  • 1.  Database High Performance

    Posted Aug 25, 2018 12:50 AM
      |   view attached

    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.

    Attachment(s)

    zip
    db_monitor_vapp.sh.txt.zip   746 B 1 version


  • 2.  Re: Database High Performance

    Broadcom Employee
    Posted Aug 25, 2018 09:13 AM

    Hi Alan,

     

    Thank You so much for sharing this valuable notes with us. Much needed one.

     

    Appreciate your time and effort on this.

     

    Regards

    Ashok