CA Service Management

Hello,

Could you please help me to know and understand to decide on the agent lock value should be given. .

"pdm_options_mgr -c -s VIRTDB_LOCK_AGENTS -v <***> -a pdm_option.inst"

I want to install this option to increase the performance as per the below techdoc.

Env: R14.2 c2

Service Desk Manager is performing slowly due to - CA Knowledge 

Thanks

sharath

Sharath, default is 1...so I would use 2 if I were you. I don't think too many agents will help much so starting with 2 and see of performance improves. Thanks _Chi

Hi Sharath,

I recommend that you start the value with 6 and based on your requirement would increase it by multiple of 2.

"pdm_options_mgr -c -s VIRTDB_LOCK_AGENTS -v 2 -a pdm_option.inst"

Some more details on this variable -

The variable was introduced as part of the fix T5U3349 (a design change in the way the locking mechanism works). The read me of the fix clearly explains about this variable.

Service Desk Manager r12.7 and later versions store the record lock information in database to control concurrent transactions. Before every record is updated, a lock is obtained, i.e, an entry is added to usp_record_lock table in database. Once the record is updated, the entry is deleted from the database.

These updates on usp_record_lock table is performed via single dedicated update database agent. With huge number of users connected to Service Desk, the number of update and delete transactions increase significantly. This may lead to performance issues and operations like editing and saving a ticket will take unusually long time. This results in degraded performance and operations like editing tickets will take unusually long time.

When this problem happens, the output of the command 'pdm_vdbinfo' will show the last update agent, responsible for all the locking transactions, having huge number of queued transactions.

"Agent #18 - (UPD) prov#4696_bpvirtdb_srvr @01/07/2015 15:33:19 with 1 in work and 326 queued"

The correction to this problem would distribute the load among multiple dedicated update agents.

The correction to this problem introduces a new NX variable - NX_VIRTDB_LOCK_AGENTS - which defines the number of update agents responsible for locking transactions.

In a nutshell, we had single dedicated update database agent earlier for locking transactions, now the variable will let us define multiple update agents.

regards,

Maheshwar

Thanks, i will do this during the weekend and let you know . Thanks for your time!!

Thanks for sharing!

We've noticed performance issues caused by locks about 2 weeks ago and this solution helped us.

I would appreciate if you provide any additional info about bpvirtdb log metrics. I'm mostly interested in "Delayed ID Queue" part.

Here is log example trimmed to 4 tables:

========================================
VDBINFO invoked at 07/09/2018 10:47:19
========================================
Min Config Agents    = 8
Max Config Agents    = 16
Max DB Agents        = 16
Tgt num idle         = 2
Num Agents running   = 29
Num Agents starting  = 0
Num Requests pending = 0
Actual num idle      = 6

Agent #0 - (UPD) prov#3380_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #1 - (UPD) prov#6744_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #2 - (UPD) prov#3960_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #3 - (UPD) prov#3956_bpvirtdb_srvr @07/09/2018 10:47:17 with 0 in work
Agent #4 - (UPD) prov#6800_bpvirtdb_srvr @07/09/2018 10:47:12 with 0 in work
Agent #5 - (UPD) prov#6816_bpvirtdb_srvr @07/09/2018 10:47:09 with 0 in work
Agent #6 - (UPD) prov#3988_bpvirtdb_srvr @07/09/2018 10:47:17 with 0 in work
Agent #7 - (UPD) prov#4076_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #8 - (UPD) prov#6840_bpvirtdb_srvr @01/01/1970 05:00:00 with 0 in work
Agent #9 - (UPD) prov#6860_bpvirtdb_srvr @01/01/1970 05:00:00 with 0 in work
Agent #10 - (UPD) prov#6880_bpvirtdb_srvr @07/09/2018 10:29:52 with 0 in work
Agent #11 - (UPD) prov#952_bpvirtdb_srvr @07/09/2018 10:46:54 with 0 in work
Agent #12 - (UPD) prov#6900_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #13 - (SEL) prov#23392_bpvirtdb_srvr @07/09/2018 10:47:17 with 1 in work
  SELECT_FULL (db_id=33033296) SELECT act_log.time_stamp, act_log.id, act_log.id FROM act_log, act_type WHERE act_log.call_req_id = ? AND ( act_log.type = act_type.code AND act_type.z_short_view = 1 ) ORDER BY act_log.time_stamp DESC , act_log.id DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:21) CNT(<userid>)
Agent #14 - (SEL) prov#30648_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #15 - (SEL) prov#23112_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=32983472) SELECT call_req.open_date, call_req.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", call_req.ref_num, call_req.z_atev_violate, call_req.z_tr_time FROM (call_req LEFT JOIN attached_sla ON call_req.persid = attached_sla.mapped_cr LEFT JOIN usp_target_time ON call_req.persid = usp_target_time.mapped_cr) WHERE call_req.type = ? AND call_req.customer = ? GROUP BY call_req.open_date, call_req.id, call_req.ref_num, call_req.z_atev_violate, call_req.z_tr_time ORDER BY call_req.open_date DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:11) CNT(<userid>)
Agent #16 - (SEL) prov#22128_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=33014933) SELECT issue.open_date, issue.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", issue.ref_num, issue.resolve_date FROM (issue LEFT JOIN attached_sla ON issue.persid = attached_sla.mapped_iss LEFT JOIN usp_target_time ON issue.persid = usp_target_time.mapped_iss) WHERE ( ( ( issue.requestor = ? ) OR ( issue.requestor IN (SELECT z_deputys.z_head FROM z_deputys WHERE z_deputys.z_deputy = ?) ) ) AND issue.active_flag = 0 AND issue.status = ? ) AND ( ( issue.requestor = ? ) AND ( issue.requestor = ? ) ) GROUP BY issue.open_date, issue.id, issue.ref_num, issue.resolve_date ORDER BY issue.open_date DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:01) CNT(<userid>)
Agent #17 - (SEL) prov#1512_bpvirtdb_srvr @07/09/2018 10:47:17 with 1 in work
  SELECT_FULL (db_id=33040094) SELECT issue.open_date, issue.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", issue.ref_num, issue.resolve_date FROM (issue LEFT JOIN attached_sla ON issue.persid = attached_sla.mapped_iss LEFT JOIN usp_target_time ON issue.persid = usp_target_time.mapped_iss) WHERE ( ( ( issue.requestor = ? ) OR ( issue.requestor IN (SELECT z_deputys.z_head FROM z_deputys WHERE z_deputys.z_deputy = ?) ) ) AND ( issue.status = ? ) ) AND ( ( issue.requestor = ? ) AND ( issue.requestor = ? ) ) GROUP BY issue.open_date, issue.id, issue.ref_num, issue.resolve_date ORDER BY issue.open_date DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:01) CNT(<userid>)
Agent #18 - (SEL) prov#8764_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=1921378) SELECT issue.open_date, issue.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", issue.ref_num FROM (issue LEFT JOIN attached_sla ON issue.persid = attached_sla.mapped_iss LEFT JOIN usp_target_time ON issue.persid = usp_target_time.mapped_iss) WHERE ( issue.requestor = ? ) AND ( ( issue.requestor IN (SELECT ca_contact.contact_uuid FROM ca_contact WHERE ca_contact.last_name LIKE ?) ) OR issue.requestor = ? ) GROUP BY issue.open_date, issue.id, issue.ref_num ORDER BY issue.open_date DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:21) CNT(<userid>)
Agent #19 - (SEL) prov#5452_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=1380232) SELECT ntfr.sym, usp_lrel_ntfr_macrol_att_ntfrl.id FROM (usp_lrel_ntfr_macrol_att_ntfrl LEFT JOIN ntfr ON usp_lrel_ntfr_macrol_att_ntfrl.ntfr = ntfr.id) WHERE usp_lrel_ntfr_macrol_att_ntfrl.macro = ? ORDER BY ntfr.sym
    FIRST (0 of 100 recs) PROC(domsrvr:01) CNT(<userid>)
    <macro:400362>
Agent #20 - (SEL) prov#4656_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #21 - (SEL) prov#736_bpvirtdb_srvr @07/08/2018 01:18:23 with 1 in work
  SELECT_FULL (db_id=23213094) SELECT attr_alias.obj, attr_alias.alias_name, attr_alias.alias_value, attr_alias.id FROM attr_alias WHERE attr_alias.del = 0 ORDER BY attr_alias.obj , attr_alias.alias_name
    MORE (2173 of 100 recs) PROC(domsrvr:21) CNT()
Agent #22 - (SEL) prov#5424_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #23 - (SEL) prov#6016_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #24 - (SEL) prov#5260_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #25 - (SEL) prov#3284_bpvirtdb_srvr @07/09/2018 10:47:19 with 0 in work
Agent #26 - (SEL) prov#6076_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=1969592) SELECT act_log.time_stamp, act_log.id, act_log.id FROM act_log, act_type WHERE act_log.type = act_type.code AND act_type.z_short_view = 1 AND act_log.call_req_id = ? ORDER BY act_log.time_stamp DESC , act_log.id DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:21) CNT(<userid>)
Agent #27 - (SEL) prov#5448_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=32852481) SELECT call_req.open_date, call_req.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", call_req.ref_num, call_req.z_atev_violate, call_req.z_tr_time FROM (call_req LEFT JOIN attached_sla ON call_req.persid = attached_sla.mapped_cr LEFT JOIN usp_target_time ON call_req.persid = usp_target_time.mapped_cr) WHERE ( ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = ? OR call_req.type = ? OR call_req.type IS NULL ) AND call_req.active_flag = 1 AND call_req.resolve_date IS NULL ) AND ( ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) ) GROUP BY call_req.open_date, call_req.id, call_req.ref_num, call_req.z_atev_violate, call_req.z_tr_time ORDER BY call_req.open_date DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:21) CNT(<userid>)
Agent #28 - (SEL) prov#7152_bpvirtdb_srvr @07/09/2018 10:47:19 with 1 in work
  SELECT_FULL (db_id=32852481) SELECT call_req.open_date, call_req.id, min(attached_sla.time_to_violation) AS "mintime", min(usp_target_time.target_time) AS "mintgt", call_req.ref_num, call_req.z_atev_violate, call_req.z_tr_time FROM (call_req LEFT JOIN attached_sla ON call_req.persid = attached_sla.mapped_cr LEFT JOIN usp_target_time ON call_req.persid = usp_target_time.mapped_cr) WHERE ( ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = ? OR call_req.type = ? OR call_req.type IS NULL ) AND call_req.active_flag = 1 AND call_req.resolve_date IS NULL ) AND ( ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) OR ( call_req.category IN (SELECT prob_ctg.persid FROM prob_ctg WHERE prob_ctg.sym LIKE ?) ) ) GROUP BY call_req.open_date, call_req.id, call_req.ref_num, call_req.z_atev_violate, call_req.z_tr_time ORDER BY call_req.open_date DESC
    SUSPENDED (100 of 100 recs) PROC(domsrvr:21) CNT(<userid>)

QUEUED requests (0)

Table Updates:
 ca_contact                ID(00) Inserts(00020) Updates(35491) Deletes(00000) Total(35511)
 usp_contact               ID(00) Inserts(00020) Updates(35491) Deletes(00000) Total(35511)
 Animator                  ID(00) Inserts(135831) Updates(00000) Deletes(132902) Total(268733)
 Attached_Events           ID(00) Inserts(134256) Updates(130874) Deletes(34874) Total(300004)

Delayed ID Queue
 usp_contact               Hash(0101) Queue(0101) MaxQueue(0101) Min|Max|Cur(0.000| 360.986|  51.730)
 ca_contact                Hash(0101) Queue(0101) MaxQueue(0101) Min|Max|Cur(0.000| 360.986|  51.730)
 Animator                  Hash(0095) Queue(0095) MaxQueue(0101) Min|Max|Cur(0.000| 360.967| 106.845)
 Attached_Events           Hash(0101) Queue(0101) MaxQueue(0101) Min|Max|Cur(0.000| 360.986|  38.751)

==============================================================================
VDBINFO completed at 07/09/2018 10:47:19                 --- End of Report ---
==============================================================================
-=-=-=-=-=-=-=-=-=-=-=-=

Best regards,

Timur