How to avoid Issue's activity load preload on Service Desk startup

[Anon Anon]

Hallo.

I need to avoid that Service Desk preloads, on service startup, issue's Activity log because the table contains too many records and preload takes,in total, about half an hour (80% of the total time to preload issalg table).

Anybody knows how to modify NX.ENV or other configurations to do that?

 

Unfortunately I'm not able to archive&purge inactive or older issues, because my colleagues needs the whole historical archive online for reporting.

 

Thank you!

Emanuele

[Raghu Rudraraju]

Emanuele,

 

Would you happen to have some cache entries in the web.cfg or  <hostname>-web*.cfg  files in  NX_ROOT\bopcfg\www  directory?

 

SelListCacheExclude

 

SelListCachePreload

 

issalg is in Exclude option normally. Maybe you have it in the PreLoad section?

 

_R

[Anon Anon]

Thank you Raghu for your reply.

 

I've just checked and issalg in, as normally, in SetListCacheExclude.

 

SelListCacheExclude acc_lvls act_type_assoc ADMIN_TREE agt alg all_lr am_asset_map ANI api arcpur_rul arcpur_hist asset atev attmnt attmnt_lrel attr_alias audlog bhvtpl bhvtpl_wftpl bmhier bmrep bmlrel bns BU_TRANS ca_application_registration ca_asset ca_asset_source ca_asset_source_unrestricted ca_asset_type ca_cmpny ca_logical_asset ca_logical_asset_property chg chg_tpl chgalg chgcat_grp chgcat_loc chgcat_workshift CI_ACTIONS CI_BOOKMARKS CI_DOC_LINKS cnt country cpex cr cr_prp cr_prptpl crsq cst ctab ctp dlgsrvr dmc dcon dcon_typ EBR_ACRONYMS EBR_DICTIONARY EBR_DICTIONARY_ADM EBR_FULLTEXT EBR_FULLTEXT_ADM EBR_FULLTEXT_SD EBR_FULLTEXT_SD_ADM EBR_INDEX EBR_INDEX_ADM EBR_INDEXING_QUEUE EBR_INDEXING_QUEUE_ADM EBR_KEYWORDS EBR_LOG EBR_METRICS EBR_NOISE_WORDS EBR_PATTERNS EBR_PREFIXES EBR_PROPERTIES EBR_SUBSTITS EBR_SUFFIXES EBR_SYNONYMS EBR_SYNONYMS_ADM ES_CONSTANTS ES_MODES ES_SESSIONS ES_RESPONSES event_log event_type evt_dly evtdlytp fmgrp grc grp grp_loc grpmem help_content help_item help_lookup hier INDEX_DOC_LINKS in intfc ir iss issalg isscat_grp isscat_loc isscat_workshift iss_prp iss_wf KCAT KD KD_ALL KD_ATTMNT KD_FILE KD_QA kdlinks kmlrel KT_REPORT_CARD kwrd ldap ldap_group LONG_TEXTS lr lrel1 macro macro_type menu_tree menu_tree_res mfrmod mgs mgsstat nr nr_com ntfl ntfm ntfr O_COMMENTS O_EVENTS options P_GROUPS pcat_grp pcat_loc pcat_workshift pr prod_list prp prptpl response role_go_form rptm sapolicy saprobtyp session_log session_type SHOW_OBJ sqchg sqcr survey svy_ans svy_atpl svy_qtpl svy_ques svystat svy_tpl svytrk swex text_api tkt tspan url USP_PREFERENCES USP_PROPERTIES usq wf wftpl wftpl_chgcat wftpl_grp wspcol wsptbl kcd ktd

 

It must be something else...

 

Emanuele

[Raghu Rudraraju]

What type of entries do you see in the SDM logs during startup?  Do you see something like this? (just a sample for actlog  in the SetListCachePreLoad)

 

07/08 21:19:20.74 Server sqlagt:select3	4932 SIGNIFICANT  sqlclass.c	1049 The following statement took 4462 milliseconds: Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)
07/08 21:19:22.35 Server sqlagt:select3	4932 SIGNIFICANT  prov_base.c	1010 SQL Query has returned (5000) rows for (domsrvr/superuser). Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)
07/08 21:19:24.22 Server sqlagt:select3	4932 SIGNIFICANT  prov_base.c	1010 SQL Query has returned (10000) rows for (domsrvr/superuser). Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)
07/08 21:19:25.80 Server sqlagt:select3	4932 SIGNIFICANT  prov_base.c	1010 SQL Query has returned (15000) rows for (domsrvr/superuser). Clause (SELECT act_log.id, act_log.persid, act_log.description FROM act_log ORDER BY act_log.time_stamp DESC, act_log.id DESC) Input (<None>)

[Anon Anon]

The following are the statements in stdlog during start-up:

 

07/09 09:57:59.53 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4490000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

07/09 09:58:00.22 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4495000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

07/09 09:58:00.89 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4500000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

07/09 09:58:01.47 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (4505000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

 

Until whole table is loaded.

 

07/09 10:00:43.85 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (5715000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

07/09 10:00:44.53 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            965 SQL Query has returned (5720000) rows for (domsrvr/). Clause (SELECT issalg.id FROM issalg) Input (<None>)

07/09 10:00:45.02 ILSD-PRIMARY   sqlagt:select1046   16852 SIGNIFICANT  prov_base.c            980 SQL Query has finished (5723550) rows for (domsrvr/).  Clause (SELECT issalg.id FROM issalg) Input (<None>)

 

Thanku!

[Raghu Rudraraju]

Thank you Emanuele. The above logs do seem to indicate that the cache PreLoad is somehow coming into effect.  Maybe there's some other customization that's impacting this somehow in addition to the web.cfg entries.  Maybe you have multiple web engines on the primary/secondary and one of the <hostname>*web*.cfg  files is introducing the above behavior.

 

May I recommend opening a support ticket for this for better traction?

 

_R

[Anon Anon]

I add a last more information, before opening a support ticket.

 

I posted the same question to a friend, employee in Ca Italy, and he answered me as follows:

In object definition you have to swap the MLIST and RLIST settings.

Unfortunately this has the contraindication that activities list in Issue's detail wouldn't be loaded immediately but just by clicking 'Search' button.

 

I have the file \site\mods\majic\issalg.mod which contains (I can't remember why I built this file in past ) :

MODIFY FACTORY issalg {

  STANDARD_LISTS {

    RLIST ON;

  };

};

 

I tried modifying it, according to my understanding of my friend answer, to:

MODIFY FACTORY issalg {

  STANDARD_LISTS {

    MLIST OFF;

    RLIST ON;

  };

};

 

But without success (nothing changed).

Any more ideas?

 

If not, I'm going to open a support ticked and, after resolution, I'm updating this discussion.

 

Regards,

Emanuele

[Michael Mueller]

Hi Emanuele.

Just to confirm the rootcause of the problem, set both definition to OFF, RLIST and MLIST, and see, if the problem still persist.

I don't think that this will have effects on the issue detail Activities list, because the initial search is triggered in a different way, at least in the web interface. I remember some dependicies of these settings in the old vbop and java client. But this is history..... I also remember a golden rule, never ever set MLIST and RLIST differently.

Btw., MLIST and RLIST is OFF for all the ticket types like Incidents and Changes, and everything is working fine.

Give it a try....

Regards

.............Michael

[Anon Anon]

Thank you for your reply.

Actually I tried in our test environment and it works .

Tonight I try to apply the solution in production environment and, if it works even there (as it should), I close the post and confirm your answer.

 

Soon,

Emanuele

[Anon Anon]

It works!

Really thanx!

 

Emanuele

[Raghu Rudraraju]

Great idea Michael, thank you.  Glad it worked out for you as well Emanuele.

[Lindsay_Estabrooks]

You might also want to check to see if you have any stored queries that reference issalg and are being used by a scoreboard node.

[Anon Anon]

Hi Lindsay!

 

I've checked in our Stored query but I didn't find anyone pointing to issalg object.

I've also looked for the object type allowed to stored_query and  issalg isn't listed (see below the full list) .

Is my analysis correct?

Can I exclude your hypothesis?

  

sym	name
ambiguity_twaci	Ambiguous CI Transactions
chg	Change Order
cnote	Announcement
cnt	Contact
cr	Request
g_chg_queue	Global Change Queue
g_cr_queue	Global Request Queue
g_iss_queue	Global Issue Queue
iss	Issue
iss_wf	Issue Workflow Task
KD	Knowledge Document
KT_LIFE_CYCLE_REP	KT Life Cycle Report
KT_REPORT_CARD	Knowledge Report Card
nr	Configuration Item
nra	Ambiguous Configuration Item
O_COMMENTS	Knowledge Document Comment
sa_notif	Assistance Session
superseded_ci	Supereded Configuration Item
url	URL
wf	Workflow Task

 

Thanks,

Emanuele

[Lindsay_Estabrooks]

If anything, it would be in the iss object with a reference to one of the BREL (issalg,act_log, act_log_all) to the issalg.

[Anon Anon]

I've checked in Stored Query (object crsq) with the following whereclause, without result.

delete_flag=0 and obj_type.sym = 'iss' and (where_clause like '%act_log%' OR where_clause like '%issalg%')

 

Anyway thank you for your advice!

 

Emanuele