cancel
Showing results for 
Search instead for 
Did you mean: 

CPU Utilized more in Postgresql Server.

Silver Adventurer

CPU Utilized more in Postgresql Server.

Hi Folks,

           we have a postgresql cluster setup with 3 GB data,but it utilize the 96% cpu and application cannot load a page.

The application running is oracle now they migrate the data through java into postgresql 10.7.15

 

1.is this Application issue?

2.Database issue?

 

Fyi,

 Os :- Rhel 7.7

Postgres EDB:- 10.7.15

Cpu 4,then increase 8,after we will increase 12Core's

Ram :- 64GB.

Shared buffer:- 16GB

Work mem:- 500MB

Max_connection- 500

overall 3 application connection upto =250 - 300

 

I will share the  logs of

Free -l

vmstat

iostat

top session

pg_stat_activity.

 

 

Thanks

 

 

 

2 REPLIES 2
Silver Adventurer

Re: CPU Utilized more in Postgresql Server.

Attachemnets;

Select * from pg_stat_activity;

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+--------------+-------+----------+----------------+------------------------------------------------+---------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
| | 7063 | | | | | | | 2019-11-26 12:04:14.213026+08 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
| | 7067 | 10 | postgres | | | | | 2019-11-26 12:04:14.214298+08 | | | | Activity | LogicalLauncherMain | | | | | background worker
| | 7066 | 10 | postgres | | | | | 2019-11-26 12:04:14.21519+08 | | | | Extension | Extension | | | | | background worker
16452 | sgaolprd | 22867 | 40649 | aolusrdta | PostgreSQL JDBC Driver | | | | 2019-11-26 12:30:29.807846+08 | 2019-11-26 12:30:29.802493+08 | 2019-11-26 12:30:29.80287+08 | 2019-11-26 12:30:29.809455+08 | | | active | | 2464860 | SELECT U.USER_ID, U.STATUS, U.LOGIN_ID, U.OAN, U.USER_TYPE, U.CLNT_NUM, UI.USER_INTERNAL_ID, CASE WHEN UI.SURNAME IS NOT NULL THEN UI.SURNAME ELSE PU.SURNAME END AS SURNAME, CASE WHEN UI.GIVNAME IS NOT NULL THEN UI.GIVNAME ELSE PU.GIVNAME END AS GIVNAME, CASE WHEN UI.PHONE_NO IS NOT NULL THEN UI.PHONE_NO ELSE PU.MOBILE_NO END AS PHONE_NO, CASE WHEN UI.NRIC IS NOT NULL THEN UI.NRIC ELSE PU.NRIC END AS NRIC, UI.EMAIL, UI.REASON_TO_CHANGE_STT, UI.REMARKS, U.tou_accepted TermsOfUseAccepted, U.last_login_date, (case when dt_last_password_updated is null then 'N' when dt_last_password_updated is not null then case when DATE_PART('day',now() - dt_last_password_updated) > (select to_number(param_value,'99D9') from user_system_param where param_type = 'PWD_EXPIRY_PERIOD' and param_code = 'PWD_EXPIRY_PERIOD') then 'Y' else 'N' END END) pwdExpired, (case when dt_last_password_updated is null then 'N' when dt_last_password_updated is not null then | background worker
| | 7071 | 16464 | rep_user | walreceiver | 10.138.97.34 | | 38374 | 2019-11-26 12:04:15.180944+08 | | | 2019-11-26 12:04:15.183409+08 | Activity | WalSenderMain | active | | 2464651 | | walsender
16452 | sgaolprd | 22323 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49430 | 2019-11-26 12:29:42.526455+08 | | 2019-11-26 12:29:42.531955+08 | 2019-11-26 12:29:42.531983+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22861 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49498 | 2019-11-26 12:30:29.19971+08 | | 2019-11-26 12:30:29.206051+08 | 2019-11-26 12:30:29.206079+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 9940 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34382 | 2019-11-26 12:10:57.061113+08 | | 2019-11-26 12:30:27.914166+08 | 2019-11-26 12:30:27.914264+08 | Client | ClientRead | idle | | | COMMIT | client backend
16461 | efm_check_db | 22792 | 16459 | efm_check_user | 'efm-3.4' | 10.138.97.35 | | 47140 | 2019-11-26 12:30:23.745474+08 | | 2019-11-26 12:30:23.75046+08 | 2019-11-26 12:30:23.750557+08 | Client | ClientRead | idle | | | select version() | client backend
16452 | sgaolprd | 22863 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49500 | 2019-11-26 12:30:29.454992+08 | | 2019-11-26 12:30:29.461477+08 | 2019-11-26 12:30:29.461516+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22241 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49394 | 2019-11-26 12:29:30.567426+08 | | 2019-11-26 12:29:30.572675+08 | 2019-11-26 12:29:30.572706+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22868 | 40649 | aolusrdta | PostgreSQL JDBC Driver | | | | 2019-11-26 12:30:29.80971+08 | 2019-11-26 12:30:29.802493+08 | 2019-11-26 12:30:29.80287+08 | 2019-11-26 12:30:29.811854+08 | | | active | | 2464860 | SELECT U.USER_ID, U.STATUS, U.LOGIN_ID, U.OAN, U.USER_TYPE, U.CLNT_NUM, UI.USER_INTERNAL_ID, CASE WHEN UI.SURNAME IS NOT NULL THEN UI.SURNAME ELSE PU.SURNAME END AS SURNAME, CASE WHEN UI.GIVNAME IS NOT NULL THEN UI.GIVNAME ELSE PU.GIVNAME END AS GIVNAME, CASE WHEN UI.PHONE_NO IS NOT NULL THEN UI.PHONE_NO ELSE PU.MOBILE_NO END AS PHONE_NO, CASE WHEN UI.NRIC IS NOT NULL THEN UI.NRIC ELSE PU.NRIC END AS NRIC, UI.EMAIL, UI.REASON_TO_CHANGE_STT, UI.REMARKS, U.tou_accepted TermsOfUseAccepted, U.last_login_date, (case when dt_last_password_updated is null then 'N' when dt_last_password_updated is not null then case when DATE_PART('day',now() - dt_last_password_updated) > (select to_number(param_value,'99D9') from user_system_param where param_type = 'PWD_EXPIRY_PERIOD' and param_code = 'PWD_EXPIRY_PERIOD') then 'Y' else 'N' END END) pwdExpired, (case when dt_last_password_updated is null then 'N' when dt_last_password_updated is not null then | background worker
16452 | sgaolprd | 22304 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49410 | 2019-11-26 12:29:39.449608+08 | | 2019-11-26 12:29:39.454094+08 | 2019-11-26 12:29:39.454122+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22325 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49432 | 2019-11-26 12:29:42.967754+08 | | 2019-11-26 12:29:42.974769+08 | 2019-11-26 12:29:42.974821+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22313 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49428 | 2019-11-26 12:29:40.806436+08 | | 2019-11-26 12:29:40.811058+08 | 2019-11-26 12:29:40.811088+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22533 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53828 | 2019-11-26 12:30:00.376664+08 | | 2019-11-26 12:30:00.382241+08 | 2019-11-26 12:30:00.382268+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22727 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49804 | 2019-11-26 12:30:16.459878+08 | | 2019-11-26 12:30:16.467182+08 | 2019-11-26 12:30:16.467238+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22354 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53798 | 2019-11-26 12:29:45.68328+08 | | 2019-11-26 12:30:29.942675+08 | 2019-11-26 12:30:29.942852+08 | Client | ClientRead | idle | | | SELECT PERMISSION_CODE FROM ROLE_PERMISSION RPM INNER JOIN PERMISSION PM ON PM.PERMISSION_ID = RPM.PERMISSION_ID WHERE RPM.ROLE_ID = 25 | client backend
16452 | sgaolprd | 22381 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49714 | 2019-11-26 12:29:47.491039+08 | | 2019-11-26 12:29:47.495381+08 | 2019-11-26 12:29:47.495412+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22347 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53794 | 2019-11-26 12:29:45.135886+08 | | 2019-11-26 12:29:45.139447+08 | 2019-11-26 12:29:45.139475+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 10166 | 10 | postgres | PostgreSQL JDBC Driver | 10.138.172.97 | | 54743 | 2019-11-26 12:11:24.958818+08 | | 2019-11-26 12:25:42.551894+08 | 2019-11-26 12:25:42.55218+08 | Client | ClientRead | idle | | | select * from pg_locks | client backend
16452 | sgaolprd | 22262 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53788 | 2019-11-26 12:29:34.201478+08 | | 2019-11-26 12:30:26.100279+08 | 2019-11-26 12:30:26.100826+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 7933 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47294 | 2019-11-26 12:06:30.621943+08 | | 2019-11-26 12:30:29.732582+08 | 2019-11-26 12:30:29.733246+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 7934 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47296 | 2019-11-26 12:06:30.960748+08 | | 2019-11-26 12:30:27.063453+08 | 2019-11-26 12:30:27.063961+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 7936 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47298 | 2019-11-26 12:06:30.990012+08 | | 2019-11-26 12:30:29.346771+08 | 2019-11-26 12:30:29.347258+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 7937 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47300 | 2019-11-26 12:06:31.027799+08 | | 2019-11-26 12:30:29.005198+08 | 2019-11-26 12:30:29.005663+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 7938 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47302 | 2019-11-26 12:06:31.074781+08 | | 2019-11-26 12:06:31.116298+08 | 2019-11-26 12:06:31.116363+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 7941 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47304 | 2019-11-26 12:06:31.128002+08 | | 2019-11-26 12:06:31.170113+08 | 2019-11-26 12:06:31.170147+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 7942 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47306 | 2019-11-26 12:06:31.176533+08 | | 2019-11-26 12:06:31.220188+08 | 2019-11-26 12:06:31.220219+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 7943 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47308 | 2019-11-26 12:06:31.222065+08 | | 2019-11-26 12:06:31.24965+08 | 2019-11-26 12:06:31.249699+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 7944 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47310 | 2019-11-26 12:06:31.256802+08 | | 2019-11-26 12:06:31.2869+08 | 2019-11-26 12:06:31.286952+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 7945 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47312 | 2019-11-26 12:06:31.296597+08 | | 2019-11-26 12:06:31.328059+08 | 2019-11-26 12:06:31.32809+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8051 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47316 | 2019-11-26 12:06:41.389781+08 | | 2019-11-26 12:30:29.743502+08 | 2019-11-26 12:30:29.744059+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 8052 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47318 | 2019-11-26 12:06:41.755119+08 | | 2019-11-26 12:30:27.663454+08 | 2019-11-26 12:30:27.663997+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 8053 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47320 | 2019-11-26 12:06:41.798358+08 | | 2019-11-26 12:06:41.833658+08 | 2019-11-26 12:06:41.833719+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8054 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47322 | 2019-11-26 12:06:41.840269+08 | | 2019-11-26 12:06:41.862822+08 | 2019-11-26 12:06:41.862879+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8055 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47324 | 2019-11-26 12:06:41.872815+08 | | 2019-11-26 12:06:41.915584+08 | 2019-11-26 12:06:41.915646+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8056 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47326 | 2019-11-26 12:06:41.92373+08 | | 2019-11-26 12:06:41.951453+08 | 2019-11-26 12:06:41.951516+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8057 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47328 | 2019-11-26 12:06:41.963208+08 | | 2019-11-26 12:06:41.984816+08 | 2019-11-26 12:06:41.984854+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8058 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47330 | 2019-11-26 12:06:41.994333+08 | | 2019-11-26 12:06:42.034483+08 | 2019-11-26 12:06:42.034538+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8059 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47332 | 2019-11-26 12:06:42.041155+08 | | 2019-11-26 12:06:42.062038+08 | 2019-11-26 12:06:42.062089+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 8060 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 47334 | 2019-11-26 12:06:42.065607+08 | | 2019-11-26 12:06:42.075672+08 | 2019-11-26 12:06:42.075698+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22352 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53796 | 2019-11-26 12:29:45.377625+08 | | 2019-11-26 12:30:28.746785+08 | 2019-11-26 12:30:28.956614+08 | Client | ClientRead | idle | | | SELECT * FROM USERS WHERE UPPER(LOGIN_ID) = $1 | client backend
16452 | sgaolprd | 9721 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34344 | 2019-11-26 12:10:34.067695+08 | | 2019-11-26 12:13:22.148947+08 | 2019-11-26 12:13:22.149135+08 | Client | ClientRead | idle | | | select sum(c) as c from( (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY where APIPOLICY.NAME =$1 AND APIPOLICY.TENANT_ID =$2 AND APIPOLICY.DEFAULT_QUOTA_TYPE = 'bandwidthVolume') union (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY , AM_CONDITION_GROUP cg where APIPOLICY.NAME =$3 AND APIPOLICY.TENANT_ID =$4 AND cg.policy_id = APIPOLICY.policy_id AND cg.quota_type = 'bandwidthVolume') union (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY, AM_API_URL_MAPPING RS, AM_CONDITION_GROUP cg where RS.api_id = $5 AND APIPOLICY.NAME = RS.throttling_tier AND APIPOLICY.TENANT_ID =$6 AND cg.policy_id = APIPOLICY.policy_id AND cg.quota_type = 'bandwidthVolume' ) union (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY, AM_API_URL_MAPPING RS where RS.api_id = $7 AND APIPOLICY.NAME = RS.throttling_tier AND APIPOLICY.TENANT_ID =$8 AND APIPOLICY.DEFAULT_QUOTA_TYPE = 'bandwidthVolume') union (SELECT count(*) as c FROM AM_POLICY_SUBSCRIPTION SUBPOLICY WHERE SUBPOLICY.NAME= | client backend
16452 | sgaolprd | 22389 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49444 | 2019-11-26 12:29:48.646056+08 | | 2019-11-26 12:30:29.349484+08 | 2019-11-26 12:30:29.34971+08 | Client | ClientRead | idle | | | SELECT lookupParam.* FROM USER_LOOKUP_PARAM lookupParam WHERE lookupParam.STATUS = 'Y' AND LOWER(lookupParam.LOOKUP_CODE) = LOWER($1) AND LOWER(lookupParam.LOOKUP_TYPE) = LOWER($2) | client backend
16452 | sgaolprd | 22401 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49446 | 2019-11-26 12:29:49.975341+08 | | 2019-11-26 12:30:29.012594+08 | 2019-11-26 12:30:29.012749+08 | Client | ClientRead | idle | | | SELECT PERMISSION_CODE FROM ROLE_PERMISSION RPM INNER JOIN PERMISSION PM ON PM.PERMISSION_ID = RPM.PERMISSION_ID WHERE RPM.ROLE_ID = 25 | client backend
16452 | sgaolprd | 22418 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49726 | 2019-11-26 12:29:50.760235+08 | | 2019-11-26 12:29:50.764461+08 | 2019-11-26 12:29:50.764612+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 9722 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34346 | 2019-11-26 12:10:34.137424+08 | | 2019-11-26 12:30:28.487261+08 | 2019-11-26 12:30:28.487281+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 9924 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34358 | 2019-11-26 12:10:56.50225+08 | | 2019-11-26 12:14:33.512035+08 | 2019-11-26 12:14:33.512047+08 | Client | ClientRead | idle | | | ROLLBACK | client backend
16452 | sgaolprd | 11536 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52038 | 2019-11-26 12:15:25.019804+08 | | 2019-11-26 12:30:29.937299+08 | 2019-11-26 12:30:29.938412+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 10017 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34402 | 2019-11-26 12:11:04.464984+08 | | 2019-11-26 12:13:22.13308+08 | 2019-11-26 12:13:22.133098+08 | Client | ClientRead | idle | | | ROLLBACK | client backend
16452 | sgaolprd | 22333 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53790 | 2019-11-26 12:29:44.452963+08 | | 2019-11-26 12:29:44.457534+08 | 2019-11-26 12:29:44.457614+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22338 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53792 | 2019-11-26 12:29:44.874444+08 | | 2019-11-26 12:29:44.878809+08 | 2019-11-26 12:29:44.878836+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22378 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49710 | 2019-11-26 12:29:47.343756+08 | | 2019-11-26 12:29:47.348114+08 | 2019-11-26 12:29:47.34815+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22447 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49736 | 2019-11-26 12:29:54.316422+08 | | 2019-11-26 12:30:29.321282+08 | 2019-11-26 12:30:29.322496+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 9927 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34364 | 2019-11-26 12:10:56.510024+08 | | 2019-11-26 12:14:33.514586+08 | 2019-11-26 12:14:33.514622+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 10780 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34562 | 2019-11-26 12:13:22.053957+08 | | 2019-11-26 12:30:29.938757+08 | 2019-11-26 12:30:29.938947+08 | Client | ClientRead | idle | | | select sum(c) as c from( (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY where APIPOLICY.NAME =$1 AND APIPOLICY.TENANT_ID =$2 AND APIPOLICY.DEFAULT_QUOTA_TYPE = 'bandwidthVolume') union (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY , AM_CONDITION_GROUP cg where APIPOLICY.NAME =$3 AND APIPOLICY.TENANT_ID =$4 AND cg.policy_id = APIPOLICY.policy_id AND cg.quota_type = 'bandwidthVolume') union (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY, AM_API_URL_MAPPING RS, AM_CONDITION_GROUP cg where RS.api_id = $5 AND APIPOLICY.NAME = RS.throttling_tier AND APIPOLICY.TENANT_ID =$6 AND cg.policy_id = APIPOLICY.policy_id AND cg.quota_type = 'bandwidthVolume' ) union (SELECT count(*) as c FROM AM_API_THROTTLE_POLICY APIPOLICY, AM_API_URL_MAPPING RS where RS.api_id = $7 AND APIPOLICY.NAME = RS.throttling_tier AND APIPOLICY.TENANT_ID =$8 AND APIPOLICY.DEFAULT_QUOTA_TYPE = 'bandwidthVolume') union (SELECT count(*) as c FROM AM_POLICY_SUBSCRIPTION SUBPOLICY WHERE SUBPOLICY.NAME= | client backend
16452 | sgaolprd | 10783 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34576 | 2019-11-26 12:13:22.115754+08 | | 2019-11-26 12:27:24.379211+08 | 2019-11-26 12:27:24.379226+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11255 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47000 | 2019-11-26 12:14:32.686935+08 | | 2019-11-26 12:30:29.513771+08 | 2019-11-26 12:30:29.514383+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11315 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47026 | 2019-11-26 12:14:46.163335+08 | | 2019-11-26 12:30:29.163138+08 | 2019-11-26 12:30:29.163641+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 22458 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49744 | 2019-11-26 12:29:55.366052+08 | | 2019-11-26 12:30:27.227775+08 | 2019-11-26 12:30:27.228498+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11316 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47028 | 2019-11-26 12:14:46.250437+08 | | 2019-11-26 12:14:46.282967+08 | 2019-11-26 12:14:46.283003+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 9934 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34378 | 2019-11-26 12:10:56.543967+08 | | 2019-11-26 12:14:33.51624+08 | 2019-11-26 12:14:33.51626+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11311 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47024 | 2019-11-26 12:14:45.770924+08 | | 2019-11-26 12:30:29.016506+08 | 2019-11-26 12:30:29.017081+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 9932 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34374 | 2019-11-26 12:10:56.534143+08 | | 2019-11-26 12:10:59.539707+08 | 2019-11-26 12:10:59.539726+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 9941 | 16457 | apidta | PostgreSQL JDBC Driver | 10.138.96.73 | | 34384 | 2019-11-26 12:10:57.1666+08 | | 2019-11-26 12:25:57.328663+08 | 2019-11-26 12:25:57.328693+08 | Client | ClientRead | idle | | | ROLLBACK | client backend
16452 | sgaolprd | 11317 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47030 | 2019-11-26 12:14:46.28639+08 | | 2019-11-26 12:14:46.314131+08 | 2019-11-26 12:14:46.314159+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11318 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47032 | 2019-11-26 12:14:46.319438+08 | | 2019-11-26 12:14:46.382126+08 | 2019-11-26 12:14:46.382214+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11256 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47002 | 2019-11-26 12:14:32.772769+08 | | 2019-11-26 12:30:28.860616+08 | 2019-11-26 12:30:28.86112+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11254 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 46998 | 2019-11-26 12:14:32.226344+08 | | 2019-11-26 12:30:29.625328+08 | 2019-11-26 12:30:29.626198+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11257 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47004 | 2019-11-26 12:14:32.821706+08 | | 2019-11-26 12:30:28.047441+08 | 2019-11-26 12:30:28.048234+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11258 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47006 | 2019-11-26 12:14:32.859142+08 | | 2019-11-26 12:30:28.573198+08 | 2019-11-26 12:30:28.573898+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11259 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47008 | 2019-11-26 12:14:32.895255+08 | 2019-11-26 12:30:29.648159+08 | 2019-11-26 12:30:29.648429+08 | 2019-11-26 12:30:29.64843+08 | | | active | | 2464856 | update agent_service_request_log set created_by=$1, created_date=$2, end_point=$3, host_requester=$4, last_updated_by=$5, last_updated_date=$6, request_message=$7, response_message=$8, source_system=$9, status=$10 where id=$11 | client backend
16452 | sgaolprd | 11261 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47010 | 2019-11-26 12:14:32.927596+08 | | 2019-11-26 12:30:24.877311+08 | 2019-11-26 12:30:24.877867+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11262 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47012 | 2019-11-26 12:14:32.960449+08 | | 2019-11-26 12:30:27.961686+08 | 2019-11-26 12:30:27.962353+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11263 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47014 | 2019-11-26 12:14:32.999277+08 | | 2019-11-26 12:30:26.760154+08 | 2019-11-26 12:30:26.760929+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11264 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47016 | 2019-11-26 12:14:33.033339+08 | | 2019-11-26 12:14:33.06665+08 | 2019-11-26 12:14:33.066703+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11319 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47034 | 2019-11-26 12:14:46.387602+08 | | 2019-11-26 12:14:46.443202+08 | 2019-11-26 12:14:46.443231+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11320 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47036 | 2019-11-26 12:14:46.445261+08 | | 2019-11-26 12:14:46.479074+08 | 2019-11-26 12:14:46.479136+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11321 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47038 | 2019-11-26 12:14:46.494748+08 | | 2019-11-26 12:14:46.528999+08 | 2019-11-26 12:14:46.529035+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11322 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47040 | 2019-11-26 12:14:46.539738+08 | | 2019-11-26 12:14:46.572059+08 | 2019-11-26 12:14:46.57217+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11323 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 47042 | 2019-11-26 12:14:46.581513+08 | | 2019-11-26 12:14:46.592919+08 | 2019-11-26 12:14:46.592973+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22532 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53826 | 2019-11-26 12:30:00.272182+08 | | 2019-11-26 12:30:00.277985+08 | 2019-11-26 12:30:00.278037+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22531 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49462 | 2019-11-26 12:29:59.871879+08 | | 2019-11-26 12:29:59.878315+08 | 2019-11-26 12:29:59.878345+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22448 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49738 | 2019-11-26 12:29:54.323012+08 | | 2019-11-26 12:30:28.871256+08 | 2019-11-26 12:30:29.106895+08 | Client | ClientRead | idle | | | SELECT * FROM USERS WHERE UPPER(LOGIN_ID) = $1 | client backend
14240 | postgres | 20381 | 10 | postgres | Postgres Enterprise Manager - Agent Monitoring | 10.138.97.35 | | 46832 | 2019-11-26 12:26:56.583432+08 | | 2019-11-26 12:30:13.133379+08 | 2019-11-26 12:30:13.133729+08 | Client | ClientRead | idle | | | SELECT 1 | client backend
16452 | sgaolprd | 13720 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.185.24 | | 55313 | 2019-11-26 12:19:16.400435+08 | | 2019-11-26 12:20:11.37754+08 | 2019-11-26 12:20:11.480957+08 | Client | ClientRead | idle | | | select * from users +| client backend
| | | | | | | | | | | | | | | | | | where last_login_date > last_logout_date +|
| | | | | | | | | | | | | | | | | | and to_char(last_login_date,'yyyymmdd') = '20191126' +|
| | | | | | | | | | | | | | | | | | and to_char(last_login_date,'hh24') >= '12' +|
| | | | | | | | | | | | | | | | | | --order by last_login_date desc |
16452 | sgaolprd | 22358 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53802 | 2019-11-26 12:29:45.779367+08 | | 2019-11-26 12:29:45.783093+08 | 2019-11-26 12:29:45.783124+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22556 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49772 | 2019-11-26 12:30:01.615884+08 | | 2019-11-26 12:30:01.622221+08 | 2019-11-26 12:30:01.622256+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22454 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49448 | 2019-11-26 12:29:55.285296+08 | | 2019-11-26 12:29:59.857852+08 | 2019-11-26 12:29:59.858017+08 | Client | ClientRead | idle | | | SELECT USER_ID, CLNT_NUM FROM USERS WHERE LOGIN_ID = $1 | client backend
16452 | sgaolprd | 22557 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53834 | 2019-11-26 12:30:01.63063+08 | | 2019-11-26 12:30:01.636441+08 | 2019-11-26 12:30:01.636471+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11537 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52042 | 2019-11-26 12:15:25.467194+08 | | 2019-11-26 12:25:51.653914+08 | 2019-11-26 12:25:51.654309+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11538 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52044 | 2019-11-26 12:15:25.526491+08 | 2019-11-26 12:30:29.951379+08 | 2019-11-26 12:30:29.95149+08 | 2019-11-26 12:30:29.951491+08 | | | active | | 2464864 | update agent_service_request_log set created_by=$1, created_date=$2, end_point=$3, host_requester=$4, last_updated_by=$5, last_updated_date=$6, request_message=$7, response_message=$8, source_system=$9, status=$10 where id=$11 | client backend
16452 | sgaolprd | 11539 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52046 | 2019-11-26 12:15:25.59266+08 | | 2019-11-26 12:30:28.736471+08 | 2019-11-26 12:30:28.737133+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11540 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52048 | 2019-11-26 12:15:25.635526+08 | | 2019-11-26 12:30:29.624625+08 | 2019-11-26 12:30:29.625808+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11541 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52050 | 2019-11-26 12:15:25.693363+08 | | 2019-11-26 12:30:26.971373+08 | 2019-11-26 12:30:26.972377+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11542 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52052 | 2019-11-26 12:15:25.716724+08 | | 2019-11-26 12:15:25.745512+08 | 2019-11-26 12:15:25.745538+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11544 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52054 | 2019-11-26 12:15:25.750923+08 | | 2019-11-26 12:15:25.789614+08 | 2019-11-26 12:15:25.78965+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11545 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52056 | 2019-11-26 12:15:25.797233+08 | | 2019-11-26 12:15:25.830645+08 | 2019-11-26 12:15:25.830673+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11546 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52062 | 2019-11-26 12:15:25.839121+08 | | 2019-11-26 12:15:25.880903+08 | 2019-11-26 12:15:25.880931+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22536 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49760 | 2019-11-26 12:30:00.406366+08 | | 2019-11-26 12:30:29.530712+08 | 2019-11-26 12:30:29.801289+08 | Client | ClientRead | idle | | | SELECT * FROM USERS WHERE UPPER(LOGIN_ID) = $1 | client backend
16452 | sgaolprd | 11587 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52066 | 2019-11-26 12:15:37.358421+08 | | 2019-11-26 12:30:28.470154+08 | 2019-11-26 12:30:28.470716+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11588 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52068 | 2019-11-26 12:15:37.732769+08 | | 2019-11-26 12:30:25.916647+08 | 2019-11-26 12:30:25.917476+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 11589 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52070 | 2019-11-26 12:15:37.859238+08 | | 2019-11-26 12:15:37.894947+08 | 2019-11-26 12:15:37.895039+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11590 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52072 | 2019-11-26 12:15:37.907533+08 | | 2019-11-26 12:15:37.958054+08 | 2019-11-26 12:15:37.958127+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11591 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52074 | 2019-11-26 12:15:37.966407+08 | | 2019-11-26 12:15:38.006591+08 | 2019-11-26 12:15:38.006652+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11592 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52076 | 2019-11-26 12:15:38.02436+08 | | 2019-11-26 12:15:38.074188+08 | 2019-11-26 12:15:38.074248+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11593 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52078 | 2019-11-26 12:15:38.081119+08 | | 2019-11-26 12:15:38.117684+08 | 2019-11-26 12:15:38.117757+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11594 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52080 | 2019-11-26 12:15:38.134292+08 | | 2019-11-26 12:15:38.172276+08 | 2019-11-26 12:15:38.17235+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11595 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52082 | 2019-11-26 12:15:38.182024+08 | | 2019-11-26 12:15:38.219434+08 | 2019-11-26 12:15:38.219493+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 11597 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 52084 | 2019-11-26 12:15:38.229077+08 | | 2019-11-26 12:15:38.258585+08 | 2019-11-26 12:15:38.258663+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22539 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53830 | 2019-11-26 12:30:00.586403+08 | | 2019-11-26 12:30:09.040421+08 | 2019-11-26 12:30:09.040583+08 | Client | ClientRead | idle | | | SELECT PERMISSION_CODE FROM ROLE_PERMISSION RPM INNER JOIN PERMISSION PM ON PM.PERMISSION_ID = RPM.PERMISSION_ID WHERE RPM.ROLE_ID = 25 | client backend
16452 | sgaolprd | 22357 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53800 | 2019-11-26 12:29:45.689612+08 | | 2019-11-26 12:30:11.292886+08 | 2019-11-26 12:30:11.293072+08 | Client | ClientRead | idle | | | SELECT PERMISSION_CODE FROM ROLE_PERMISSION RPM INNER JOIN PERMISSION PM ON PM.PERMISSION_ID = RPM.PERMISSION_ID WHERE RPM.ROLE_ID = 25 | client backend
16452 | sgaolprd | 22529 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49458 | 2019-11-26 12:29:59.850999+08 | | 2019-11-26 12:29:59.860907+08 | 2019-11-26 12:29:59.861007+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22537 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49762 | 2019-11-26 12:30:00.413327+08 | | 2019-11-26 12:30:06.800615+08 | 2019-11-26 12:30:06.80076+08 | Client | ClientRead | idle | | | SELECT PERMISSION_CODE FROM ROLE_PERMISSION RPM INNER JOIN PERMISSION PM ON PM.PERMISSION_ID = RPM.PERMISSION_ID WHERE RPM.ROLE_ID = 25 | client backend
16452 | sgaolprd | 22783 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49826 | 2019-11-26 12:30:22.424589+08 | | 2019-11-26 12:30:22.429826+08 | 2019-11-26 12:30:22.42986+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22540 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.39 | | 53832 | 2019-11-26 12:30:00.59495+08 | | 2019-11-26 12:30:00.601224+08 | 2019-11-26 12:30:00.601252+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22455 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49742 | 2019-11-26 12:29:55.302131+08 | 2019-11-26 12:30:29.802493+08 | 2019-11-26 12:30:29.80287+08 | 2019-11-26 12:30:29.802875+08 | | | active | | 2464860 | SELECT U.USER_ID, U.STATUS, U.LOGIN_ID, U.OAN, U.USER_TYPE, U.CLNT_NUM, UI.USER_INTERNAL_ID, CASE WHEN UI.SURNAME IS NOT NULL THEN UI.SURNAME ELSE PU.SURNAME END AS SURNAME, CASE WHEN UI.GIVNAME IS NOT NULL THEN UI.GIVNAME ELSE PU.GIVNAME END AS GIVNAME, CASE WHEN UI.PHONE_NO IS NOT NULL THEN UI.PHONE_NO ELSE PU.MOBILE_NO END AS PHONE_NO, CASE WHEN UI.NRIC IS NOT NULL THEN UI.NRIC ELSE PU.NRIC END AS NRIC, UI.EMAIL, UI.REASON_TO_CHANGE_STT, UI.REMARKS, U.tou_accepted TermsOfUseAccepted, U.last_login_date, (case when dt_last_password_updated is null then 'N' when dt_last_password_updated is not null then case when DATE_PART('day',now() - dt_last_password_updated) > (select to_number(param_value,'99D9') from user_system_param where param_type = 'PWD_EXPIRY_PERIOD' and param_code = 'PWD_EXPIRY_PERIOD') then 'Y' else 'N' END END) pwdExpired, (case when dt_last_password_updated is null then 'N' when dt_last_password_updated is not null then | client backend
16452 | sgaolprd | 22687 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49794 | 2019-11-26 12:30:12.956967+08 | | 2019-11-26 12:30:29.786412+08 | 2019-11-26 12:30:29.787157+08 | Client | ClientRead | idle | | | COMMIT | client backend
16452 | sgaolprd | 22307 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49412 | 2019-11-26 12:29:39.508305+08 | | 2019-11-26 12:30:26.816845+08 | 2019-11-26 12:30:26.817194+08 | Client | ClientRead | idle | | | COMMIT | client backend
14240 | postgres | 22875 | 10 | postgres | psql.bin | | | -1 | 2019-11-26 12:30:29.953504+08 | 2019-11-26 12:30:29.957015+08 | 2019-11-26 12:30:29.957015+08 | 2019-11-26 12:30:29.957018+08 | | | active | | 2464864 | select * from pg_stat_activity; | client backend
14240 | postgres | 22172 | 10 | postgres | Postgres Enterprise Manager - Agent Monitoring | 10.138.97.35 | | 47064 | 2019-11-26 12:29:25.053435+08 | | 2019-11-26 12:29:25.059231+08 | 2019-11-26 12:29:25.063223+08 | Client | ClientRead | idle | | | SELECT datname AS database_name, pid AS procpid, usename, client_addr, client_port, backend_start, +| client backend
| | | | | | | | | | | | | | | | | | xact_start, query_start, CASE WHEN wait_event IS NULL THEN false ELSE true END AS is_waiting, +|
| | | | | | | | | | | | | | | | | | state = 'idle' AS is_idle, state = 'idle in transaction' AS is_idle_in_transaction, query ilike $$VACUUM%$$ as is_vacuum, +|
| | | | | | | | | | | | | | | | | | client_port IS NULL AND (query like $$autovacuum:%$$ OR query like $$VACUUM%$$) as is_autovacuum, +|
| | | | | | | | | | | | | | | | | | now() AS capture_time, wait_event, wait_event_type, query, state, state_change FROM pg_catalog.pg_stat_activity |
16452 | sgaolprd | 22530 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49460 | 2019-11-26 12:29:59.864194+08 | | 2019-11-26 12:29:59.869941+08 | 2019-11-26 12:29:59.869984+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22782 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49818 | 2019-11-26 12:30:21.812096+08 | | 2019-11-26 12:30:21.818531+08 | 2019-11-26 12:30:21.818606+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22553 | 40649 | aolusrdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49766 | 2019-11-26 12:30:01.134175+08 | | 2019-11-26 12:30:06.207754+08 | 2019-11-26 12:30:06.207982+08 | Client | ClientRead | idle | | | SELECT PERMISSION_CODE FROM ROLE_PERMISSION RPM INNER JOIN PERMISSION PM ON PM.PERMISSION_ID = RPM.PERMISSION_ID WHERE RPM.ROLE_ID = 25 | client backend
16452 | sgaolprd | 22684 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.37 | | 49480 | 2019-11-26 12:30:11.891311+08 | | 2019-11-26 12:30:11.895607+08 | 2019-11-26 12:30:11.895635+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 22795 | 40651 | aolappdta | PostgreSQL JDBC Driver | 10.138.97.38 | | 49828 | 2019-11-26 12:30:23.887749+08 | | 2019-11-26 12:30:23.892298+08 | 2019-11-26 12:30:23.89233+08 | Client | ClientRead | idle | | | SET application_name = 'PostgreSQL JDBC Driver' | client backend
16452 | sgaolprd | 21087 | 40651 | aolappdta | pgAdmin 4 - DB:sgaolprd | 10.138.185.24 | | 56376 | 2019-11-26 12:27:58.930883+08 | | 2019-11-26 12:27:59.015111+08 | 2019-11-26 12:27:59.01591+08 | Client | ClientRead | idle | | | SELECT CASE WHEN usesuper +| client backend
| | | | | | | | | | | | | | | | | | THEN pg_is_in_recovery() +|
| | | | | | | | | | | | | | | | | | ELSE FALSE +|
| | | | | | | | | | | | | | | | | | END as inrecovery, +|
| | | | | | | | | | | | | | | | | | CASE WHEN usesuper AND pg_is_in_recovery() +|
| | | | | | | | | | | | | | | | | | THEN pg_is_wal_replay_paused() +|
| | | | | | | | | | | | | | | | | | ELSE FALSE +|
| | | | | | | | | | | | | | | | | | END as isreplaypaused +|
| | | | | | | | | | | | | | | | | | FROM pg_user WHERE usename=current_user |
16452 | sgaolprd | 21119 | 40649 | aolusrdta | pgAdmin 4 - DB:sgaolprd | 10.138.185.24 | | 56386 | 2019-11-26 12:28:01.095081+08 | | 2019-11-26 12:30:29.064224+08 | 2019-11-26 12:30:29.065689+08 | Client | ClientRead | idle | | | /*pga4dash*/ +| client backend
| | | | | | | | | | | | | | | | | | SELECT +|
| | | | | | | | | | | | | | | | | | (SELECT sum(blks_read) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = 16452)) AS "Reads", +|
| | | | | | | | | | | | | | | | | | (SELECT sum(blks_hit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = 16452)) AS "Hits" |
| | 7061 | | | | | | | 2019-11-26 12:04:14.212363+08 | | | | Activity | BgWriterHibernate | | | | | background writer
| | 7060 | | | | | | | 2019-11-26 12:04:14.212192+08 | | | | Activity | CheckpointerMain | | | | | checkpointer
| | 7062 | | | | | | | 2019-11-26 12:04:14.212721+08 | | | | Activity | WalWriterMain | | | | | walwriter
(132 rows)

 

 

select * from pg_locks;

locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
relation | 14240 | 11595 | | | | | | | | 121/1714 | 22879 | AccessShareLock | t | t
virtualxid | | | | | 121/1714 | | | | | 121/1714 | 22879 | ExclusiveLock | t | t
relation | 16452 | 43339 | | | | | | | | 73/858 | 11259 | RowExclusiveLock | t | t
virtualxid | | | | | 73/858 | | | | | 73/858 | 11259 | ExclusiveLock | t | t
relation | 16452 | 43222 | | | | | | | | 118/2331 | 22455 | AccessShareLock | t | t
relation | 16452 | 43089 | | | | | | | | 118/2331 | 22455 | AccessShareLock | t | t
relation | 16452 | 43192 | | | | | | | | 118/2331 | 22455 | AccessShareLock | t | t
relation | 16452 | 43230 | | | | | | | | 118/2331 | 22455 | AccessShareLock | t | t
virtualxid | | | | | 118/2331 | | | | | 118/2331 | 22455 | ExclusiveLock | t | t
relation | 16452 | 43339 | | | | | | | | 93/1294 | 11538 | RowExclusiveLock | t | t
virtualxid | | | | | 93/1294 | | | | | 93/1294 | 11538 | ExclusiveLock | t | t
relation | 16452 | 43230 | | | | | | | | 12/810 | 22868 | AccessShareLock | t | t
relation | 16452 | 43222 | | | | | | | | 12/810 | 22868 | AccessShareLock | t | t
virtualxid | | | | | 12/810 | | | | | 12/810 | 22868 | ExclusiveLock | t | t
relation | 16452 | 43230 | | | | | | | | 4/305 | 22867 | AccessShareLock | t | t
relation | 16452 | 43222 | | | | | | | | 4/305 | 22867 | AccessShareLock | t | t
virtualxid | | | | | 4/305 | | | | | 4/305 | 22867 | ExclusiveLock | t | t
(17 rows)

 

-----------FREE -m
total used free shared buff/cache available
Mem: 64264 1430 59120 2001 3713 60433
Swap: 16383 0 16383

----------------TOP--------------------------
[?1h=[?25l(Btop - 12:30:03 up 1:03, 4 users, load average: 4.05, 4.93, 3.35(B(B
Tasks:(B 394 (Btotal,(B 13 (Brunning,(B 381 (Bsleeping,(B 0 (Bstopped,(B 0 (Bzombie(B(B
%Cpu(s):(B 86.9 (Bus,(B 9.4 (Bsy,(B 0.0 (Bni,(B 3.8 (Bid,(B 0.0 (Bwa,(B 0.0 (Bhi,(B 0.0 (Bsi,(B 0.0 (Bst(B(B
KiB Mem :(B 65807224 (Btotal,(B 60532740 (Bfree,(B 1471764 (Bused,(B 3802720 (Bbuff/cache(B(B
KiB Swap:(B 16777212 (Btotal,(B 16777212 (Bfree,(B 0 (Bused.(B 61877020 (Bavail Mem (B(B

 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND (B
(B11263 postgres 20 0 17.1g 631740 629320 R 100.0 1.0 0:22.18 postgres: aolappdta sgaolprd 10.138.97.38[47014] UPDATE (B
(B22307 postgres 20 0 17.1g 326568 323408 R 100.0 0.5 0:00.94 postgres: aolusrdta sgaolprd 10.138.97.37[49412] SELECT (B
(B22455 postgres 20 0 17.1g 319788 316372 R 100.0 0.5 0:00.73 postgres: aolusrdta sgaolprd 10.138.97.38[49742] SELECT (B
(B22597 postgres 20 0 17.1g 103324 101236 R 100.0 0.2 0:00.16 postgres: bgworker: parallel worker for PID 22455 (B
(B22600 postgres 20 0 17.1g 113888 111800 R 100.0 0.2 0:00.16 postgres: bgworker: parallel worker for PID 22307 (B
(B11256 postgres 20 0 17.1g 647600 643112 R 87.5 1.0 2:37.25 postgres: aolappdta sgaolprd 10.138.97.38[47002] UPDATE (B
(B22599 postgres 20 0 17.1g 94348 92260 R 87.5 0.1 0:00.15 postgres: bgworker: parallel worker for PID 22455 (B
(B22601 postgres 20 0 17.1g 102528 100440 R 87.5 0.2 0:00.14 postgres: bgworker: parallel worker for PID 22307 (B
(B22448 postgres 20 0 17.1g 336352 333380 S 56.2 0.5 0:01.15 postgres: aolusrdta sgaolprd 10.138.97.38[49738] idle (B
(B11258 postgres 20 0 17.1g 637184 634472 S 50.0 1.0 1:22.33 postgres: aolappdta sgaolprd 10.138.97.38[47006] idle (B
(B22536 postgres 20 0 17.1g 189688 186192 R 50.0 0.3 0:00.56 postgres: aolusrdta sgaolprd 10.138.97.38[49760] SELECT (B
(B22602 postgres 20 0 17.1g 61356 59164 R 37.5 0.1 0:00.06 postgres: bgworker: parallel worker for PID 22536 (B
(B11259 postgres 20 0 17.1g 635652 632908 R 31.2 1.0 1:10.85 postgres: aolappdta sgaolprd 10.138.97.38[47008] UPDATE (B
(B22603 postgres 20 0 17.1g 51584 49392 R 31.2 0.1 0:00.05 postgres: bgworker: parallel worker for PID 22536 (B
(B22005 postgres 20 0 17.1g 113736 111216 S 12.5 0.2 0:00.19 postgres: aolappdta sgaolprd 10.138.97.38[49632] idle (B
(B22239 root 20 0 162312 2572 1588 S 12.5 0.0 0:00.13 top (B
(B 6241 efm 20 0 4401772 166244 14340 S 6.2 0.3 0:07.91 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.222.b10-0.el7_6.x86_64/jre/bin/java -cp /usr/edb/efm-3.4/lib/EFM-3.4.jar -Xmx128m com.enterprisedb.efm.main.ServiceCommand __int_+ (B
(B 7056 postgres 20 0 17.1g 901268 900028 S 6.2 1.4 0:07.26 /PG_HOME/10.7/pg_install/bin/edb-postgres (B
(B22598 postgres 20 0 162320 2572 1620 R 6.2 0.0 0:00.02 top -c -n 1 (B
(B 1 root 20 0 194220 7420 4220 S 0.0 0.0 0:03.15 /usr/lib/systemd/systemd --switched-root --system --deserialize 22 (B
(B 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 [kthreadd] (B
(B 4 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/0:0H] (B
(B 5 root 20 0 0 0 0 S 0.0 0.0 0:00.00 [kworker/u24:0] (B
(B 6 root 20 0 0 0 0 S 0.0 0.0 0:00.01 [ksoftirqd/0] (B
(B 7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 [migration/0] (B
(B 8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 [rcu_bh] (B
(B 9 root 20 0 0 0 0 S 0.0 0.0 0:02.33 [rcu_sched] (B
(B 10 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [lru-add-drain] (B
(B 11 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/0] (B
(B 12 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/1] (B
(B 13 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [migration/1] (B
(B 14 root 20 0 0 0 0 S 0.0 0.0 0:00.01 [ksoftirqd/1] (B
(B 16 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/1:0H] (B
(B 17 root 20 0 0 0 0 S 0.0 0.0 0:00.00 [kworker/u25:0] (B
(B 18 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/2] (B
(B 19 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [migration/2] (B
(B 20 root 20 0 0 0 0 S 0.0 0.0 0:00.03 [ksoftirqd/2] (B
(B 22 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/2:0H] (B
(B 23 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/3] (B
(B 24 root rt 0 0 0 0 S 0.0 0.0 0:00.00 [migration/3] (B
(B 25 root 20 0 0 0 0 S 0.0 0.0 0:00.00 [ksoftirqd/3] (B
(B 27 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/3:0H] (B
(B 28 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/4] (B
(B 29 root rt 0 0 0 0 S 0.0 0.0 0:00.00 [migration/4] (B
(B 30 root 20 0 0 0 0 S 0.0 0.0 0:00.00 [ksoftirqd/4] (B
(B 31 root 20 0 0 0 0 S 0.0 0.0 0:00.02 [kworker/4:0] (B
(B 32 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/4:0H] (B
(B 33 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/5] (B
(B 34 root rt 0 0 0 0 S 0.0 0.0 0:00.00 [migration/5] (B
(B 35 root 20 0 0 0 0 S 0.0 0.0 0:00.02 [ksoftirqd/5] (B
(B 37 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/5:0H] (B
(B 38 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/6] (B
(B 39 root rt 0 0 0 0 S 0.0 0.0 0:00.00 [migration/6] (B
(B 40 root 20 0 0 0 0 S 0.0 0.0 0:00.01 [ksoftirqd/6] (B
(B 42 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 [kworker/6:0H] (B
(B 44 root rt 0 0 0 0 S 0.0 0.0 0:00.01 [watchdog/7]

Highlighted
EDB Team Member

Re: CPU Utilized more in Postgresql Server.

Hi  Din2pg,

 

The database size and CPU load are not directly dependable. If the application is sending excessive traffic, the load on CPU will eventually increase irrespective of the database size.

 

Here you can check for the following :

 

If there are any slow running queries, if yes then try tunning the queries/database parameters so that they would complete faster leaving comparatively less load on the server.

 

Blindly increasing the RAM and CPU cores won't help much until the database is not set properly to use the memory it should be using.

Hope this helps.