Chicken or the egg problem

Jim Rice

Chicken or the egg problem
Hi all.

Recently we have run in to situations where our ssidMSTR starts consuming high amounts of cpu when transaction counts are high. We get rollbacks due to timeouts which take long amounts of elapsed time to rollback. Meanwhile transactions - CICS and DDF continue to queue up.

Has anyone else wandered in to this situation? We are v11.

We have been unable to get a dump (mgt doesn't want to take the time). Our dumps are taking 10 seconds or more to complete.

What would take rollbacks so long to complete. Lots of logs to read through?

IBM isn't much help without a dump to analyze.


Thanks in advance.


Jim

Southern Company

Walter Janißen

AW: Chicken or the egg problem
(in response to Jim Rice)
Hi

What is your timeout-value? Maybe it's too high so that too many transactions queue up. I would suggest to decrease it. We are going with 30 seconds in our production system.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rice, James L. (Jim) [mailto:[login to unmask email]
Gesendet: Freitag, 15. September 2017 14:19
An: [login to unmask email]
Betreff: [DB2-L] - Chicken or the egg problem

Hi all.

Recently we have run in to situations where our ssidMSTR starts consuming high amounts of cpu when transaction counts are high. We get rollbacks due to timeouts which take long amounts of elapsed time to rollback. Meanwhile transactions - CICS and DDF continue to queue up.

Has anyone else wandered in to this situation? We are v11.

We have been unable to get a dump (mgt doesn't want to take the time). Our dumps are taking 10 seconds or more to complete.

What would take rollbacks so long to complete. Lots of logs to read through?

IBM isn't much help without a dump to analyze.


Thanks in advance.


Jim

Southern Company

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Javier Estrada Benavides

RE: AW: Chicken or the egg problem
(in response to Walter Janißen)

Hi:

 The same thing has happened to me but that's kind of too broad to be able to offer proper help. Here's what I think..

If you're giving us the hints of timeouts then you might also want to check whether any threads are misbehaving by intentionally locking resources and the default high performance DBATs are contributing to lockwaits and lock timeouts.

You could also check your idle thread timeout value to see if those distributed threads are being allocated by default from the application servers (was, glassfish, weblogic, etc etc) and are just wasting resources (the default connection pool from a was would do that, and if you have for example 50 was pointing to your DB2 then you have 50 clients allocating threads unnecessarily).

Another example could be your log output buffer setting, where, if it's not big enough, your threads will be queuing up waiting to get their hands on the buffer.

There are many more things you could check, that's what I can quickly come up with.

 

Hope that helped a bit

 

Regards,

Javier Estrada Benavides, Mexico

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS

Tony Saul

Chicken or the egg problem
(in response to Jim Rice)
Do you have Application Performance Analyzer (APA) or Strobe? These might give an idea where to look without the overhead of a DUMP  Regards, Tony

From: "Rice, James L. (Jim)" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Friday, 15 September 2017, 21:49
Subject: [DB2-L] - Chicken or the egg problem

<!--#yiv7302118505 _filtered #yiv7302118505 {font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv7302118505 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv7302118505 #yiv7302118505 p.yiv7302118505MsoNormal, #yiv7302118505 li.yiv7302118505MsoNormal, #yiv7302118505 div.yiv7302118505MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri", sans-serif;}#yiv7302118505 a:link, #yiv7302118505 span.yiv7302118505MsoHyperlink {color:#0563C1;text-decoration:underline;}#yiv7302118505 a:visited, #yiv7302118505 span.yiv7302118505MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;}#yiv7302118505 span.yiv7302118505EmailStyle17 {font-family:"Calibri", sans-serif;color:windowtext;}#yiv7302118505 .yiv7302118505MsoChpDefault {font-family:"Calibri", sans-serif;} _filtered #yiv7302118505 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv7302118505 div.yiv7302118505WordSection1 {}-->Hi all.   Recently we have run in to situations where our ssidMSTR starts consuming high amounts of cpu when transaction counts are high.  We get rollbacks due to timeouts which take long amounts of elapsed time to rollback.  Meanwhile transactions – CICS and DDF continue to queue up.   Has anyone else wandered in to this situation?  We are v11.   We have been unable to get a dump (mgt doesn’t want to take the time).  Our dumps are taking 10 seconds or more to complete.   What would take rollbacks so long to complete.  Lots of logs to read through?    IBM isn’t much help without a dump to analyze.     Thanks in advance.     Jim   Southern Company
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email] a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Joel Goldstein

Chicken or the egg problem
(in response to Tony Saul)
If you are rolling back updates, that backout cpu cost used to be charged to MSTR. Probably still is…??

Rollbacks also significantly increase the amount of data logged during the rollback.





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Tony Saul [mailto:[login to unmask email]
Sent: Saturday, September 16, 2017 2:22 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Chicken or the egg problem



Do you have Application Performance Analyzer (APA) or Strobe? These might give an idea where to look without the overhead of a DUMP



Regards, Tony



_____

From: "Rice, James L. (Jim)" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Friday, 15 September 2017, 21:49
Subject: [DB2-L] - Chicken or the egg problem



Hi all.



Recently we have run in to situations where our ssidMSTR starts consuming high amounts of cpu when transaction counts are high. We get rollbacks due to timeouts which take long amounts of elapsed time to rollback. Meanwhile transactions – CICS and DDF continue to queue up.



Has anyone else wandered in to this situation? We are v11.



We have been unable to get a dump (mgt doesn’t want to take the time). Our dumps are taking 10 seconds or more to complete.



What would take rollbacks so long to complete. Lots of logs to read through?



IBM isn’t much help without a dump to analyze.





Thanks in advance.





Jim



Southern Company



-----End Original Message-----





-----End Original Message-----

Joel Goldstein

Chicken or the egg problem #2
(in response to Tony Saul)
The backout used to be all synch IO, which accounted for the very long times for backout to complete.



Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Tony Saul [mailto:[login to unmask email]
Sent: Saturday, September 16, 2017 2:22 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Chicken or the egg problem



Do you have Application Performance Analyzer (APA) or Strobe? These might give an idea where to look without the overhead of a DUMP



Regards, Tony



_____

From: "Rice, James L. (Jim)" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Friday, 15 September 2017, 21:49
Subject: [DB2-L] - Chicken or the egg problem



Hi all.



Recently we have run in to situations where our ssidMSTR starts consuming high amounts of cpu when transaction counts are high. We get rollbacks due to timeouts which take long amounts of elapsed time to rollback. Meanwhile transactions – CICS and DDF continue to queue up.



Has anyone else wandered in to this situation? We are v11.



We have been unable to get a dump (mgt doesn’t want to take the time). Our dumps are taking 10 seconds or more to complete.



What would take rollbacks so long to complete. Lots of logs to read through?



IBM isn’t much help without a dump to analyze.





Thanks in advance.





Jim



Southern Company



-----End Original Message-----





-----End Original Message-----

Larry Kintisch

Chicken or the egg problem
(in response to Jim Rice)
Hi Jim,
You didn't tell us the nature of the
"transactions" whose counts are high. Let's
assume they are INSERTS and you have a locking
problem causing the timeouts. [For DELETEs the following also applies.]

I recall a scenario from when I was teaching
IBM's DB2 z/OS Application Performance and Tuning
class back around version 8 or 9. For INSERTs or
DELETEs which may have multiple indexes [say
power utility ACCOUNT with a PAYMENTS table
having 4 indexes leading on TRANS#, ACCOUNT,
DATE, and PAYMENT$ respectively--I'll call them T, A, D and P].

If you do an INSERT into PAYMENTS, using
spinning discs you might expect worst case 5
synchronous reads of say 10-millisec each, one to
the table and one to each index, T, A, D and
P. [Note if RI tests are needed, add 1 SR per test.]

While the X-lock is only on the table, the
duration of the x-lock include the 5 SRs or up to 50-ms or 1/20th of a second.

A clever IBM Swiss systems engineer in the
1990's came up with the "Swiss solution" - first
do four SELECTs with no locks [UR] so that 4 SR's
are done before the INSERT bringing the 4 index
pages into their respective buffer pools before
the INSERT. Each would be INDEX ONLY in the order
of the index key, [for example the P index might
be (Payment$, Trans#, Account, Date)] such as

SELECT Account, Date from PAYMENTS WHERE
Payment$ = ? and Trans# = ? WITH UR]; similarly
for every other index [and RI check].

When the INSERT is done, the table read gets
the 1 SR and all other index inserts and RI
checks are in the buffer pool, shortening the X-lock to just 10-ms.

If there is a one-to-many INSERT [like ORDERS,
ORDER-LINES] this pre-reading of all of the
ORDERS and ORDER-LINES indexes using the Swiss
solution may reduce x-lock durations by 1/50th or more.

I understand this requires an application
change and all that that implies. But if your
company has mission critical processes that need
to be improved this is one technique.

Hope this is helpful. Contact me offline to continue this.

Larry Kintisch Pres, ABLE Information Services [login to unmask email]

At 08:18 AM 9/15/2017, you wrote:
>Hi all.
>
>Recently we have run in to situations where our
>ssidMSTR starts consuming high amounts of cpu
>when transaction counts are high. We get
>rollbacks due to timeouts which take long
>amounts of elapsed time to rollback. Meanwhile
>transactions – CICS and DDF continue to queue up.
>
>Has anyone else wandered in to this situation? We are v11.
>
>We have been unable to get a dump (mgt doesn’t
>want to take the time). Our dumps are taking 10 seconds or more to complete.
>
>What would take rollbacks so long to
>complete. Lots of logs to read through?
>
>IBM isn’t much help without a dump to analyze.
>
>
>Thanks in advance.
>
>
>Jim
>
>Southern Company
>
>Site Links:
> http://www.idug.org/p/fo/st/?post=182921&anc=p182921#p182921 View
>post
>online
> http://www.idug.org/p/fo/si/?topic=19 View
>mailing list
>online <mailto:[login to unmask email]>Start new
>thread via
>email
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing
>list http://www.idug.org/p/us/to Manage your
>subscription This email has been sent to: [login to unmask email]
>
>Setup a data refresh task in less time than it
>takes to make a cup of coffee + save up to 90%
>in CPU ESAi's BCV5 & XDM fast data refresh &
>Test Data Mgmt products will make you a hero to
>users. See http://www.ESAIGroup.com/idug http://www.ESAIGroup.com/idug
>
>
>Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2 http://www.idug.org/p/cm/ld/fid=2

Olle Brostrom

Chicken or the egg problem
(in response to Walter Janißen)
If you are running a high volume transaction system in DB2 I believe a timeout value of 30 seconds is far too high.
We run up to 2000 Tx per second and have the lock timeout value set to 5 seconds and deadlock detection 2 times per second.
Running with high values can easily build up large queing situations with severe locking problems as a result.
I do recommend decreasing the lock timeout value in steps and monitor the effect of the change.


Best Regards

Olle Broström

From: Walter Janißen [mailto:[login to unmask email]
Sent: den 15 september 2017 14:49
To: [login to unmask email]
Subject: [DB2-L] - AW: Chicken or the egg problem

Hi

What is your timeout-value? Maybe it's too high so that too many transactions queue up. I would suggest to decrease it. We are going with 30 seconds in our production system.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Rice, James L. (Jim) [mailto:[login to unmask email]
Gesendet: Freitag, 15. September 2017 14:19
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - Chicken or the egg problem

Hi all.

Recently we have run in to situations where our ssidMSTR starts consuming high amounts of cpu when transaction counts are high. We get rollbacks due to timeouts which take long amounts of elapsed time to rollback. Meanwhile transactions - CICS and DDF continue to queue up.

Has anyone else wandered in to this situation? We are v11.

We have been unable to get a dump (mgt doesn't want to take the time). Our dumps are taking 10 seconds or more to complete.

What would take rollbacks so long to complete. Lots of logs to read through?

IBM isn't much help without a dump to analyze.


Thanks in advance.


Jim

Southern Company

-----End Original Message-----

-----End Original Message-----