Re: Looooooooooong update times on ARSCHEMA
Hi Guillaume, Yes, that value is set to 20 already. But as Derek indicated: this issue occurs only on Distributed Pending (T26) form - which I believe Request ID Block Size changes have no effect on. Or am I wrong? Thanks, Alkan Koray -- View this message in context: http://old.nabble.com/Looong-update-times-on-ARSCHEMA-tp26202209p31806727.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
Hello Derek, I have been facing with the exact same situation as you are since for a while sparodically. Although I have been in contact with BMC for months - believe me - I still have no progress. Wondering - Has you issue been resolved by now? If yes, how did you manage to do that? Regards, Alkan Koray -- View this message in context: http://old.nabble.com/Looong-update-times-on-ARSCHEMA-tp26202209p31800734.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
How would that affect a form that isn't getting new inserts more than occasionally? The nextId setting only affects inserts. Rick On Jun 8, 2011 10:31 AM, Guillaume Rheault guilla...@dcshq.com wrote: Hi Alkan, Have you set the Next Request ID Block Size to 10 or 20 (or even 100) ? This setting definitely alleviates the contention on the ARSCHEMA table. Definitely worth a try. Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Alkan Koray [koray.al...@siemens-enterprise.com] Sent: Wednesday, June 08, 2011 9:40 AM To: arslist@ARSLIST.ORG Subject: Re: Looong update times on ARSCHEMA Hello Derek, I have been facing with the exact same situation as you are since for a while sparodically. Although I have been in contact with BMC for months - believe me - I still have no progress. Wondering - Has you issue been resolved by now? If yes, how did you manage to do that? Regards, Alkan Koray -- View this message in context: http://old.nabble.com/Looong-update-times-on-ARSCHEMA-tp26202209p31800734.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
Well, contention on the ARSCHEMA table affects all tables related to Remedy forms, since as you know the ARSCHEMA table stores the nextId for any Remedy form, and this table is updated and queried constantly. It therefore can frequently become a contention hot spot. Since the Remedy admin does not have control over the locking mechanisms on the table for insert, update or queries, then the only thing that can be done at the Remedy level is to set the Next Request ID Block Size. Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Rick Cook [remedyr...@gmail.com] Sent: Wednesday, June 08, 2011 11:49 AM To: arslist@ARSLIST.ORG Subject: Re: Looong update times on ARSCHEMA ** How would that affect a form that isn't getting new inserts more than occasionally? The nextId setting only affects inserts. Rick On Jun 8, 2011 10:31 AM, Guillaume Rheault guilla...@dcshq.commailto:guilla...@dcshq.com wrote: Hi Alkan, Have you set the Next Request ID Block Size to 10 or 20 (or even 100) ? This setting definitely alleviates the contention on the ARSCHEMA table. Definitely worth a try. Guillaume From: Action Request System discussio_attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
What I think we have is some confusion in terms. While the nextId value for each form is stored in the arschema table for each form, there isn't any need (I would argue that it would be counterproductive) to have that value set at anything greater than zero FOR the arschema form itself. Since he reported issue wasn't with any form except arschema, and since the only time a nextId is requested for that form is when a new form is created, I fail to see the connection between your solution and the reported problem. Now if you had suggested adding a db index to the arschema table, I could see the potential for performance improvements. Rick On Jun 8, 2011 12:13 PM, Guillaume Rheault guilla...@dcshq.com wrote: Well, contention on the ARSCHEMA table affects all tables related to Remedy forms, since as you know the ARSCHEMA table stores the nextId for any Remedy form, and this table is updated and queried constantly. It therefore can frequently become a contention hot spot. Since the Remedy admin does not have control over the locking mechanisms on the table for insert, update or queries, then the only thing that can be done at the Remedy level is to set the Next Request ID Block Size. Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Rick Cook [remedyr...@gmail.com] Sent: Wednesday, June 08, 2011 11:49 AM To: arslist@ARSLIST.ORG Subject: Re: Looong update times on ARSCHEMA ** How would that affect a form that isn't getting new inserts more than occasionally? The nextId setting only affects inserts. Rick On Jun 8, 2011 10:31 AM, Guillaume Rheault guilla...@dcshq.commailto: guilla...@dcshq.com wrote: Hi Alkan, Have you set the Next Request ID Block Size to 10 or 20 (or even 100) ? This setting definitely alleviates the contention on the ARSCHEMA table. Definitely worth a try. Guillaume From: Action Request System discussio_attend WWRUG11 www.wwrug.comARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
In the case of Alkan, the Next Request ID Block Size would need to be set for the entire AR System, since he does not know what form(s) are causing contention on the ARSCHEMA table. The Next Request ID Block Size cannot be set for the arschema, as you know. The arschema has been a signficant contention hot spot in the past, for pretty much any Remedy environment, until the Next Request ID Block Size feature was introduced. Just ask any DBA that monitors a Remedy database to find contention hot spots. From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Rick Cook [remedyr...@gmail.com] Sent: Wednesday, June 08, 2011 1:27 PM To: arslist@ARSLIST.ORG Subject: Re: Looong update times on ARSCHEMA ** What I think we have is some confusion in terms. While the nextId value for each form is stored in the arschema table for each form, there isn't any need (I would argue that it would be counterproductive) to have that value set at anything greater than zero FOR the arschema form itself. Since he reported issue wasn't with any form except arschema, and since the only time a nextId is requested for that form is when a new form is created, I fail to see the connection between your solution and the reported problem. Now if you had suggested adding a db index to the arschema table, I could see the potential for performance improvements. Rick On Jun 8, 2011 12:13 PM, Guillaume Rheault guilla...@dcshq.commailto:guilla...@dcshq.com wrote: Well, contention on the ARSCHEMA table affects all tables related to Remedy forms, since as you know the ARSCHEMA table stores the nextId for any Remedy form, and this table is updated and queried constantly. It therefore can frequently become a contention hot spot. Since the Remedy admin does not have control over the locking mechanisms on the table for insert, update or queries, then the only thing that can be done at the Remedy level is to set the Next Request ID Block Size. Guillaume From: Action Request System discussion list(ARSList) [a href=mai_attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are?_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
I see you have been afflicted with trusting the BMC sales...err...product documentation on that feature. If you have real experience that shows clearly that this makes a positive difference, I would love to hear about it. I have significant experience with using this setting, and after our data showed it actually diminishing performance, I had conversations with BMC engineering on the subject to help me understand why that was. According to the engineers, the nextId setting is only going to help if you have a very high volume of records being inserted into the same form from multiple sources simultaneously. This is likely to happen only in large installations having more than one automated ticket generation mechanism. My research and data validated their statement. This setting does reduce contention for new Entry Ids, but its effect is only seen under those circumstances. Again, my research showed using any number greater than zero (or one) outside of the parameters I outlined actually DIMINISHED performance. Rick On Jun 8, 2011 12:35 PM, Guillaume Rheault guilla...@dcshq.com wrote: ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
Well, I have used this setting at the form level, and it works very well; in my case, I currently use it at teh form level for some custom functionality that I developed that can create hundreds of entries in a very short period of time in two forms. Without this setting, I used to get ARS errors about database timeout, but with this setting configured for these specific forms I don't get these errors anymore. Maybe at the entire AR System level the overhead created by this feature outweighs any benefits gained. I guess it depends on your specific implementation (how big the server where the Remedy app server is running, type of database, number of ARS threads, usage patterns, etc, etc). Anyway, my reply to Alkan was a a suggestion that may solve or may alleviate his problem. You can always undo / reset this parameter if the performance has not improved. As with any suggestions for fixes found on any public internet forum, the responsibility solely rests with the recipient of the suggestion if he / she chooses to implement such suggestion at his own risk and with whatever due diligence he / she follows Guillaume From: Action Request System discussion list(ARSList) [arslist@ARSLIST.ORG] on behalf of Rick Cook [remedyr...@gmail.com] Sent: Wednesday, June 08, 2011 1:59 PM To: arslist@ARSLIST.ORG Subject: Re: Looong update times on ARSCHEMA ** I see you have been afflicted with trusting the BMC sales...err...product documentation on that feature. If you have real experience that shows clearly that this makes a positive difference, I would love to hear about it. I have significant experience with using this setting, and after our data showed it actually diminishing performance, I had conversations with BMC engineering on the subject to help me understand why that was. According to the engineers, the nextId setting is only going to help if you have a very high volume of records being inserted into the same form from multiple sources simultaneously. This is likely to happen only in large installations having more than one automated ticket generation mechanism. My research and data validated their statement. This setting does reduce contention for new Entry Ids, but its effect is only seen under those circumstances. Again, my research showed using any number greater than zero (or one) outside of the parameters I outlined actually DIMINISHED performance. Rick On Jun 8, 2011 12:35 PM, Guillaume Rheault guilla...@dcshq.commailto:guilla...@dcshq.com wrote: _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Looooooooooong update times on ARSCHEMA
Listers... I was helping a person who contacted me look at a performance problem. I do not have access to the system. It appears the update to ARSCHEMA that is called by the CE API call is taking forever for just ONE record. In other words when this executes: UPDATE arschema SET nextId = nextId + 1 WHERE schemaId = xxx ...it works fine for all but 1 row. Most rows complete in 0.01 seconds or less. The troublesome row update can take 90 seconds or more. I've never seen that. Has anyone else? I thinking rebuilding the indexes on that table is the first thing to try. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Corporate Website, www.stratacominc.com http://www.stratacominc.com/ Blog, www.williamrentfrow.com http://www.williamrentfrow.com/ 715-410-8156 C ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
AW: Looooooooooong update times on ARSCHEMA
William, this looks like a locking issue. Perhaps some other threads has also updated this row but hasn't commited yet. There is a parameter you can set in ar.conf Next-ID-Commit: T When the system generates the next ID number for a record in the database, it performs a new commit transaction if this parameter is set to T (true). If the parameter is set to F (false), the transaction to generate the next ID is included as part of the create entry transaction. Set the value to T to increase efficiency and for debugging. The default is F. This option does not work with Informix databases. HTH Kind Regards Conny Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von William Rentfrow Gesendet: Mittwoch, 4. November 2009 19:39 An: arslist@ARSLIST.ORG Betreff: Looong update times on ARSCHEMA ** Listers... I was helping a person who contacted me look at a performance problem. I do not have access to the system. It appears the update to ARSCHEMA that is called by the CE API call is taking forever for just ONE record. In other words when this executes: UPDATE arschema SET nextId = nextId + 1 WHERE schemaId = xxx ...it works fine for all but 1 row. Most rows complete in 0.01 seconds or less. The troublesome row update can take 90 seconds or more. I've never seen that. Has anyone else? I thinking rebuilding the indexes on that table is the first thing to try. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Corporate Website, www.stratacominc.com http://www.stratacominc.com/ Blog, www.williamrentfrow.com http://www.williamrentfrow.com/ 715-410-8156 C _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
They should have the DBA check for any blocking in the db. Also have them check to see if there is any triggers or calls to an outside system that fire on create to this form. I remember a case where a company had a db trigger on insert to the H table of a form (so they could push data to a different system in real-time). It turned out that the outside system had problems and it was causing Remedy to be slow. The Remedy logs showed the same slowness in the ARSCHEMA update. Fred From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of William Rentfrow Sent: Wednesday, November 04, 2009 12:39 PM To: arslist@ARSLIST.ORG Subject: Looong update times on ARSCHEMA ** Listers... I was helping a person who contacted me look at a performance problem. I do not have access to the system. It appears the update to ARSCHEMA that is called by the CE API call is taking forever for just ONE record. In other words when this executes: UPDATE arschema SET nextId = nextId + 1 WHERE schemaId = xxx ...it works fine for all but 1 row. Most rows complete in 0.01 seconds or less. The troublesome row update can take 90 seconds or more. I've never seen that. Has anyone else? I thinking rebuilding the indexes on that table is the first thing to try. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.commailto:wrentf...@stratacominc.com Corporate Website, www.stratacominc.comhttp://www.stratacominc.com/ Blog, www.williamrentfrow.comhttp://www.williamrentfrow.com/ 715-410-8156 C ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are
Re: Looooooooooong update times on ARSCHEMA
I ran into a situation like this where that one form was the Distributed Pending form. We had a situation where there were three application servers initiating between 750k and 850k DSO transactions a day. During peak times, the UPDATE ARSCHEMA SET nextId = nextId + 1 WHERE schemaId = Distributed Pending SchemaID was causing significant lock contention at the database level. We ended up setting the Next-ID-Block-Size parameter to 25 and used a Push Fields action to create a record in a form entitled APP_DistributedPendingTransactions. We have four separate escalation threads running against the APP_DistributedPendingTransactions form and pushing data into the Distributed Pending form. We had to take this route because the 'Next-ID-Block-Size' parameter works for every form EXCEPT Distributed Pending. The response from BMC support was less than encouraging. They asked us why we needed to DSO so many records and it was recommended that we try an alternate replication technology. We're still using DSO because our work-around performs exceptionally. If your mystery form is NOT Distributed Pending, then I'd try setting the Next-ID-Block-Size parameter. Derek On Nov 4, 2009, at 1:39 PM, William Rentfrow wrote: ** Listers... I was helping a person who contacted me look at a performance problem. I do not have access to the system. It appears the update to ARSCHEMA that is called by the CE API call is taking forever for just ONE record. In other words when this executes: UPDATE arschema SET nextId = nextId + 1 WHERE schemaId = xxx ...it works fine for all but 1 row. Most rows complete in 0.01 seconds or less. The troublesome row update can take 90 seconds or more. I've never seen that. Has anyone else? I thinking rebuilding the indexes on that table is the first thing to try. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com Corporate Website, www.stratacominc.com Blog, www.williamrentfrow.com 715-410-8156 C _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: Where the Answers Are