Performance tuning

 

1. check db and find there are a million + records. and no index for the related table

idm_prod_20180405=# select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by rowCounts desc ;
table_name | rowcounts 
----------------------------------+------------
token_store | 1.3e+06
user_statistics | 1.07541e+06
permission_role | 13639
metadata | 9069
roles_users_bak | 7304
abstract_user_metadata | 7131

idmdb1=# \d token_store
                   Table "public.token_store"
        Column        |            Type             | Modifiers 
----------------------+-----------------------------+-----------
 uuid                 | character varying(50)       | not null
 request_token        | character varying(50)       | 
 authentication_token | text                        | 
 subject              | character varying(50)       | 
 expiration           | timestamp without time zone | 
 token_id             | character varying(50)       | 
 refresh_before       | timestamp without time zone | 
 refresh_id           | character varying(50)       | 
Indexes:
    "token_store_pkey" PRIMARY KEY, btree (uuid)

idmdb1=# \d user_statistics
             Table "public.user_statistics"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 uuid         | character varying(32)       | not null
 user_id      | character varying(32)       | not null
 username     | character varying(1024)     | 
 url          | character varying(1024)     | 
 method       | character varying(32)       | 
 request_time | timestamp without time zone | not null
 create_time  | timestamp without time zone | 
Indexes:
    "user_statistics_pkey" PRIMARY KEY, btree (uuid)
2 . Create index

create index token_store_req_idx on token_store(request_token);
create index user_statistics_req_idx on user_statistics(request_time);

3. Check result

Before performance tuning

@@@com.hp.ccue.identity.domain.persistence.PersistenceService::query$ end.......consumed@507ms
------------------------------FROM com.hp.ccue.identity.domain.ha.TokenStoreObject as tsObj WHERE request_token = :_0

after performance tuning 

@@@com.hp.ccue.identity.domain.persistence.PersistenceService::query start....... Hibernate: select tokenstore0_.UUID as UUID1_39_, tokenstore0_.authentication_token as authenti2_39_, tokenstore0_.expiration as expirati3_39_, tokenstore0_.refresh_before as refresh_4_39_, tokenstore0_.refresh_id as refresh_5_39_, tokenstore0_.request_token as request_6_39_, tokenstore0_.subject as subject7_39_, tokenstore0_.token_id as token_id8_39_ from token_store tokenstore0_ where request_token=? @@@com.hp.ccue.identity.domain.persistence.PersistenceService::query$ end.......consumed@8ms ------------------------------FROM com.hp.ccue.identity.domain.ha.TokenStoreObject as tsObj WHERE request_token = :_0 0:java.lang.Thread::getStackTrace 1:com.hp.ccue.identity.domain.Perform::stack 2:com.hp.ccue.identity.domain.persistence.PersistenceService::query 3:com.hp.ccue.identity.domain.persistence.PersistenceService::query 4:com.hp.ccue.identity.service.TokenDatabaseServiceImpl::find 5:com.hp.ccue.identity.ha.TokenStoreDatabase::contains

4. Others

a. show sql by the setting

 71 # Echo all executed SQL to stdout
 72 idm.persistence.show_sql = true

b. add class 

import java.util.Date;

import org.hibernate.service.spi.Startable;

public class Perform {
    String className;
    String methodName;
    String option;
    long b;

    public Perform(String className, String methodName) {
        init(className, methodName, "");
        System.out.println("@@@" + className + "::" + methodName + " start.......");
    }

    public Perform(String className, String methodName, String optionalDesc) {
        init(className, methodName, optionalDesc);
        System.out.println("@@@" + className + "::" + methodName + "$" + optionalDesc + " start.......");
    }

    private void init(String className, String methodName, String optionalDesc) {
        this.className = className;
        this.methodName = methodName;
        b = new Date().getTime();
        this.option = optionalDesc;
    }

    public void end() {
        if (print() > 50) {
            System.out.println("!!!!!!");
            stack();
        }
    }

    public void endStack() {
        print();
        System.out.println("!!!!!!!!!");
        stack();
    }

    private long print() {
        long consumedTime = new Date().getTime() - b;
        System.out.println("@@@" + className + "::" + methodName + "$" + option + "  end......." + "consumed@"
                + consumedTime + "ms");
        return consumedTime;
    }

    public void stack() {
        StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
        for (int i = 0; i < stackTrace.length; i++) {
            System.out.println(i + ": " + stackTrace[i].getClassName() + "::" + stackTrace[i].getMethodName());
        }
    }

}

Use perform class

            StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
            Perform p = new Perform(stackTrace[1].getClassName(),
                    stackTrace[1].getMethodName());
            
            List result = query.list();
            
            p.end();