Mutex - guard ?

Posted in 2014

Summary

On IDS 11.50.FC9X6 / AIX 6.1, many short-lived parallel 4GL programs piled up waiting on the undocumented 'guard' mutex (seen via onstat -g wmx). Marco Greco noted it belongs to ASF and can bottleneck under heavy connect/disconnect. Fernando Nunes asked about sysdbopen/sysdbclose, which jogged the poster's memory of an earlier PMR: his sysdbopen/close routines queried sysnetworkio, and the smi_network_io call serialises on that mutex. No code fix existed; workarounds were to stop querying sysnetworkio, or (IBM's advice for Power7) switch the LPAR from SMT-4 to SMT-2. A stack trace was posted.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Connectivity: ESQL/C, 4GL & Embedded SQL, Platform-Specific Issues

Cesar Martins — Fri, 05 Sep 2014 14:34:32 — source: IIUG Forums & Mailing Lists

Hi , IFX 11.50 FC9X6 - AIX 6.1 + 1 read only RSS node We are running a bunch of parallel 4GL programs. This programs start and finish very fast... We are getting wait at "guard" mutex ... and I found no information about it . Any tips ? Cesar --20cf303ea812e58d860502525d63

↪ replying to Cesar Martins

Art Kagel — Fri, 05 Sep 2014 14:46:32 — source: IIUG Forums & Mailing Lists

I don't understand "Guard" mutex. Where are you seeing that? Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < cesar.inacio.martins@gmail.com> wrote: > Hi , > > IFX 11.50 FC9X6 - AIX 6.1 > + 1 read only RSS node > > We are running a bunch of parallel 4GL programs. > This programs start and finish very fast... > > We are getting wait at "guard" mutex ... and I found no information about > it . > > Any tips ? > > Cesar > > --20cf303ea812e58d860502525d63 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c372be9ca97a0502528871

↪ replying to Art Kagel

Marco Greco — Fri, 05 Sep 2014 14:49:19 — source: IIUG Forums & Mailing Lists

On 05/09/14 15:46, Art Kagel wrote: > I don't understand "Guard" mutex. Where are you seeing that? > > Art > There is a 'guard' mutex. It's used in ASF - I think it could be a bottleneck on lots of connections / disconnections, but I haven't checked it out. > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > cesar.inacio.martins@gmail.com> wrote: > >> Hi , >> >> IFX 11.50 FC9X6 - AIX 6.1 >> + 1 read only RSS node >> >> We are running a bunch of parallel 4GL programs. >> This programs start and finish very fast... >> >> We are getting wait at "guard" mutex ... and I found no information about >> it . >> >> Any tips ? >> >> Cesar >> >> --20cf303ea812e58d860502525d63 >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a11c372be9ca97a0502528871 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

↪ replying to Marco Greco

Cesar Martins — Fri, 05 Sep 2014 15:21:24 — source: IIUG Forums & Mailing Lists

Hi Art, Hi Marco, Hmm... make sense... sometimes we got nfs.lock too , but this I know is related with parallel open/close connections... But the "guard" too...is new for me.... $ og wmx IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up 28 days 23:12:48 -- 183239776 Kbytes Mutexes with waiters: mid addr name holder lkcnt waiter waittime 3287 700001c8c0e0940 guard 311522329 0 311521906 0 311522235 0 311519249 1 311522173 0 311522502 0 311522306 0 311498199 0 311522330 0 311518371 0 311521699 0 311521847 0 311521991 1 311522751 0 311522203 0 311522325 1 311522657 0 311522623 0 311522521 0 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > On 05/09/14 15:46, Art Kagel wrote: > > I don't understand "Guard" mutex. Where are you seeing that? > > > > Art > > > > There is a 'guard' mutex. > It's used in ASF - I think it could be a bottleneck on lots of connections > / > disconnections, but I haven't checked it out. > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > > cesar.inacio.martins@gmail.com> wrote: > > > >> Hi , > >> > >> IFX 11.50 FC9X6 - AIX 6.1 > >> + 1 read only RSS node > >> > >> We are running a bunch of parallel 4GL programs. > >> This programs start and finish very fast... > >> > >> We are getting wait at "guard" mutex ... and I found no information > about > >> it . > >> > >> Any tips ? > >> > >> Cesar > >> > >> --20cf303ea812e58d860502525d63 > >> > >> > >> > >> > > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --001a11c372be9ca97a0502528871 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Ciao, > Marco > > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b41403683ecae0502530390

↪ replying to Cesar Martins

Fernando Nunes — Fri, 05 Sep 2014 16:18:51 — source: IIUG Forums & Mailing Lists

Do you have sysdbopen/sysdbclose() procedures? On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < cesar.inacio.martins@gmail.com> wrote: > Hi Art, Hi Marco, > > Hmm... make sense... > sometimes we got nfs.lock too , but this I know is related with parallel > open/close connections... > But the "guard" too...is new for me.... > > $ og wmx> > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up 28 days > 23:12:48 -- 183239776 Kbytes> > Mutexes with waiters: > mid addr name holder lkcnt waiter > waittime > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > 311522235 0 > > 311519249 1 > > 311522173 0 > > 311522502 0 > > 311522306 0 > > 311498199 0 > > 311522330 0 > > 311518371 0 > > 311521699 0 > > 311521847 0 > > 311521991 1 > > 311522751 0 > > 311522203 0 > > 311522325 1 > > 311522657 0 > > 311522623 0 > > 311522521 0 > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > On 05/09/14 15:46, Art Kagel wrote: > > > I don't understand "Guard" mutex. Where are you seeing that? > > > > > > Art > > > > > > > There is a 'guard' mutex. > > It's used in ASF - I think it could be a bottleneck on lots of > connections > > / > > disconnections, but I haven't checked it out. > > > > > Art S. Kagel, Principal Consultant > > > ASK Database Management > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on the IIUG, nor any other organization with which I > > am > > > associated either explicitly, implicitly, or by inference. Neither do > > > those opinions reflect those of other individuals affiliated with any > > > entity with which I am affiliated nor those of the entities themselves. > > > > > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > > > cesar.inacio.martins@gmail.com> wrote: > > > > > >> Hi , > > >> > > >> IFX 11.50 FC9X6 - AIX 6.1 > > >> + 1 read only RSS node > > >> > > >> We are running a bunch of parallel 4GL programs. > > >> This programs start and finish very fast... > > >> > > >> We are getting wait at "guard" mutex ... and I found no information > > about > > >> it . > > >> > > >> Any tips ? > > >> > > >> Cesar > > >> > > >> --20cf303ea812e58d860502525d63 > > >> > > >> > > >> > > >> > > > > > > > > > ******************************************************************************* > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > >> > > >> > > > > > > --001a11c372be9ca97a0502528871 > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Ciao, > > Marco > > > > > > ______________________________________________________________________________ > > Marco Greco /UK /IBM Standard disclaimers apply! > > > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > > 4glworks http://www.4glworks.com > > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7b41403683ecae0502530390 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c3c3866ad617050253d296

↪ replying to Fernando Nunes

Cesar Martins — Fri, 05 Sep 2014 17:33:32 — source: IIUG Forums & Mailing Lists

Hi Fernando , Yes!! and your question just help me remember a PMR open by my self... after check my own history of PMR (because the limited IBM Support site not able to keep the history of your customers PMR) I already get into this situation before and just forgot... ( 2011-10-17 - 40939,228,631) At my sysdbopen/close I collect lot of information of the session and one of them is selecting the sysnetworkio . The solution what work at that time is avoid access the sysnetworkio , but few months later I reactive this access because our "overhead" of this parallel process was minimized for other reasons... now it's heavy again, the problem just come back. Not sure, but as far I remember, this issue (sysnetworkio VS guard mutex) was resolved at new versions... I just need upgrade my instance... Thank you! Bests Regards Cesar 2014-09-05 13:18 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > Do you have sysdbopen/sysdbclose() procedures? > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > cesar.inacio.martins@gmail.com> wrote: > > > Hi Art, Hi Marco, > > > > Hmm... make sense... > > sometimes we got nfs.lock too , but this I know is related with parallel > > open/close connections... > > But the "guard" too...is new for me.... > > > > $ og wmx> > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up 28 days > > 23:12:48 -- 183239776 Kbytes> > > > Mutexes with waiters: > > mid addr name holder lkcnt waiter > > waittime > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > 311522235 0 > > > > 311519249 1 > > > > 311522173 0 > > > > 311522502 0 > > > > 311522306 0 > > > > 311498199 0 > > > > 311522330 0 > > > > 311518371 0 > > > > 311521699 0 > > > > 311521847 0 > > > > 311521991 1 > > > > 311522751 0 > > > > 311522203 0 > > > > 311522325 1 > > > > 311522657 0 > > > > 311522623 0 > > > > 311522521 0 > > > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > > > On 05/09/14 15:46, Art Kagel wrote: > > > > I don't understand "Guard" mutex. Where are you seeing that? > > > > > > > > Art > > > > > > > > > > There is a 'guard' mutex. > > > It's used in ASF - I think it could be a bottleneck on lots of > > connections > > > / > > > disconnections, but I haven't checked it out. > > > > > > > Art S. Kagel, Principal Consultant > > > > ASK Database Management > > > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions > > > > and do not reflect on the IIUG, nor any other organization with > which I > > > am > > > > associated either explicitly, implicitly, or by inference. Neither do > > > > those opinions reflect those of other individuals affiliated with any > > > > entity with which I am affiliated nor those of the entities > themselves. > > > > > > > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > >> Hi , > > > >> > > > >> IFX 11.50 FC9X6 - AIX 6.1 > > > >> + 1 read only RSS node > > > >> > > > >> We are running a bunch of parallel 4GL programs. > > > >> This programs start and finish very fast... > > > >> > > > >> We are getting wait at "guard" mutex ... and I found no information > > > about > > > >> it . > > > >> > > > >> Any tips ? > > > >> > > > >> Cesar > > > >> > > > >> --20cf303ea812e58d860502525d63 > > > >> > > > >> > > > >> > > > >> > > > > > > > > > > > > > > > > ******************************************************************************* > > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > >> > > > >> > > > > > > > > --001a11c372be9ca97a0502528871 > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > -- > > > Ciao, > > > Marco > > > > > > > > > > > > ______________________________________________________________________________ > > > Marco Greco /UK /IBM Standard disclaimers apply! > > > > > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > > > 4glworks http://www.4glworks.com > > > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --047d7b41403683ecae0502530390 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a11c3c3866ad617050253d296 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf303ea614ec5119050254ddbf

↪ replying to Cesar Martins

Fernando Nunes — Fri, 05 Sep 2014 18:21:50 — source: IIUG Forums & Mailing Lists

I was looking at the same PMR, but didn't notice it was yours :) And we keep the old PMRs online, but not forever... I think I can see PMRs with 1 year... Regards On Fri, Sep 5, 2014 at 6:33 PM, Cesar Martins < cesar.inacio.martins@gmail.com> wrote: > Hi Fernando , > > Yes!! and your question just help me remember a PMR open by my self... > after check my own history of PMR (because the limited IBM Support site not > able to keep the history of your customers PMR) I already get into this > situation before and just forgot... > ( 2011-10-17 - 40939,228,631) > > At my sysdbopen/close I collect lot of information of the session and one > of them is selecting the sysnetworkio . > The solution what work at that time is avoid access the sysnetworkio , but > few months later I reactive this access because our "overhead" of this > parallel process was minimized for other reasons... now it's heavy again, > the problem just come back. > > Not sure, but as far I remember, this issue (sysnetworkio VS guard mutex) > was resolved at new versions... I just need upgrade my instance... > > Thank you! > > Bests Regards > Cesar > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > > > Do you have sysdbopen/sysdbclose() procedures? > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > cesar.inacio.martins@gmail.com> wrote: > > > > > Hi Art, Hi Marco, > > > > > > Hmm... make sense... > > > sometimes we got nfs.lock too , but this I know is related with > parallel > > > open/close connections... > > > But the "guard" too...is new for me.... > > > > > > $ og wmx> > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up 28> days > > > 23:12:48 -- 183239776 Kbytes > > > > > > Mutexes with waiters: > > > mid addr name holder lkcnt waiter > > > waittime > > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > > > 311522235 0 > > > > > > 311519249 1 > > > > > > 311522173 0 > > > > > > 311522502 0 > > > > > > 311522306 0 > > > > > > 311498199 0 > > > > > > 311522330 0 > > > > > > 311518371 0 > > > > > > 311521699 0 > > > > > > 311521847 0 > > > > > > 311521991 1 > > > > > > 311522751 0 > > > > > > 311522203 0 > > > > > > 311522325 1 > > > > > > 311522657 0 > > > > > > 311522623 0 > > > > > > 311522521 0 > > > > > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > > > > > On 05/09/14 15:46, Art Kagel wrote: > > > > > I don't understand "Guard" mutex. Where are you seeing that? > > > > > > > > > > Art > > > > > > > > > > > > > There is a 'guard' mutex. > > > > It's used in ASF - I think it could be a bottleneck on lots of > > > connections > > > > / > > > > disconnections, but I haven't checked it out. > > > > > > > > > Art S. Kagel, Principal Consultant > > > > > ASK Database Management > > > > > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > opinions > > > > > and do not reflect on the IIUG, nor any other organization with > > which I > > > > am > > > > > associated either explicitly, implicitly, or by inference. Neither > do > > > > > those opinions reflect those of other individuals affiliated with > any > > > > > entity with which I am affiliated nor those of the entities > > themselves. > > > > > > > > > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > >> Hi , > > > > >> > > > > >> IFX 11.50 FC9X6 - AIX 6.1 > > > > >> + 1 read only RSS node > > > > >> > > > > >> We are running a bunch of parallel 4GL programs. > > > > >> This programs start and finish very fast... > > > > >> > > > > >> We are getting wait at "guard" mutex ... and I found no > information > > > > about > > > > >> it . > > > > >> > > > > >> Any tips ? > > > > >> > > > > >> Cesar > > > > >> > > > > >> --20cf303ea812e58d860502525d63 > > > > >> > > > > >> > > > > >> > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > >> Forum Note: Use "Reply" to post a response in the discussion > forum. > > > > >> > > > > >> > > > > > > > > > > --001a11c372be9ca97a0502528871 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > -- > > > > Ciao, > > > > Marco > > > > > > > > > > > > > > > > > > > > ______________________________________________________________________________ > > > > Marco Greco /UK /IBM Standard disclaimers apply! > > > > > > > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > > > > 4glworks http://www.4glworks.com > > > > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --047d7b41403683ecae0502530390 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --001a11c3c3866ad617050253d296 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --20cf303ea614ec5119050254ddbf > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e010d8dd67c7ddb0502558a36

↪ replying to Cesar Martins

david@smooth1.co.uk — Fri, 05 Sep 2014 21:12:34 — source: IIUG Forums & Mailing Lists

For interest (and the FAQ!) do you have an onstat -g stk for any of the holders or waiters of the mutex? And an APAR/release numbers when it was fixed? Marco/IBM can you confirm what the mutex is used for? Kind Regards, David. > On 05 September 2014 at 18:33 Cesar Martins <cesar.inacio.martins@gmail.com> > wrote: > > > Hi Fernando , > > Yes!! and your question just help me remember a PMR open by my self... > after check my own history of PMR (because the limited IBM Support site not > able to keep the history of your customers PMR) I already get into this > situation before and just forgot... > ( 2011-10-17 - 40939,228,631) > > At my sysdbopen/close I collect lot of information of the session and one > of them is selecting the sysnetworkio . > The solution what work at that time is avoid access the sysnetworkio , but > few months later I reactive this access because our "overhead" of this > parallel process was minimized for other reasons... now it's heavy again, > the problem just come back. > > Not sure, but as far I remember, this issue (sysnetworkio VS guard mutex) > was resolved at new versions... I just need upgrade my instance... > > Thank you! > > Bests Regards > Cesar > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > > > Do you have sysdbopen/sysdbclose() procedures? > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > cesar.inacio.martins@gmail.com> wrote: > > > > > Hi Art, Hi Marco, > > > > > > Hmm... make sense... > > > sometimes we got nfs.lock too , but this I know is related with parallel > > > open/close connections... > > > But the "guard" too...is new for me.... > > > > > > $ og wmx> > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up 28 days > > > 23:12:48 -- 183239776 Kbytes> > > > > > Mutexes with waiters: > > > mid addr name holder lkcnt waiter > > > waittime > > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > > > 311522235 0 > > > > > > 311519249 1 > > > > > > 311522173 0 > > > > > > 311522502 0 > > > > > > 311522306 0 > > > > > > 311498199 0 > > > > > > 311522330 0 > > > > > > 311518371 0 > > > > > > 311521699 0 > > > > > > 311521847 0 > > > > > > 311521991 1 > > > > > > 311522751 0 > > > > > > 311522203 0 > > > > > > 311522325 1 > > > > > > 311522657 0 > > > > > > 311522623 0 > > > > > > 311522521 0 > > > > > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > > > > > On 05/09/14 15:46, Art Kagel wrote: > > > > > I don't understand "Guard" mutex. Where are you seeing that? > > > > > > > > > > Art > > > > > > > > > > > > > There is a 'guard' mutex. > > > > It's used in ASF - I think it could be a bottleneck on lots of > > > connections > > > > / > > > > disconnections, but I haven't checked it out. > > > > > > > > > Art S. Kagel, Principal Consultant > > > > > ASK Database Management > > > > > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > opinions > > > > > and do not reflect on the IIUG, nor any other organization with > > which I > > > > am > > > > > associated either explicitly, implicitly, or by inference. Neither do > > > > > those opinions reflect those of other individuals affiliated with any > > > > > entity with which I am affiliated nor those of the entities > > themselves. > > > > > > > > > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > >> Hi , > > > > >> > > > > >> IFX 11.50 FC9X6 - AIX 6.1 > > > > >> + 1 read only RSS node > > > > >> > > > > >> We are running a bunch of parallel 4GL programs. > > > > >> This programs start and finish very fast... > > > > >> > > > > >> We are getting wait at "guard" mutex ... and I found no information > > > > about > > > > >> it . > > > > >> > > > > >> Any tips ? > > > > >> > > > > >> Cesar > > > > >> > > > > >> --20cf303ea812e58d860502525d63 > > > > >> > > > > >> > > > > >> > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > > >> > > > > >> > > > > > > > > > > --001a11c372be9ca97a0502528871 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > -- > > > > Ciao, > > > > Marco > > > > > > > > > > > > > > > > > > > ______________________________________________________________________________ > > > > Marco Greco /UK /IBM Standard disclaimers apply! > > > > > > > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > > > > 4glworks http://www.4glworks.com > > > > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --047d7b41403683ecae0502530390 > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --001a11c3c3866ad617050253d296 > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --20cf303ea614ec5119050254ddbf > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Cesar Martins

Cesar Martins — Mon, 08 Sep 2014 14:05:11 — source: IIUG Forums & Mailing Lists

Hi David , I've checked my history of emails and this is what I have : - There is no FIX , only a suggestion for workaround. - The problem occur with exclusive with Power7 processors using SMT-4 configuration What they was explained , this is because of internal architecture of P7 in SMT-4 configuration. - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 to SMT-2 - The ontat -g stk bellow - This is into version 11.50 FC9X6 , AIX 6.1 stack for thread: 9063619 sqlexec base: 0x0700001cdecb7000 len: 266240 pc: 0x000000010003618c tos: 0x0700001cdecf21e0 state: mutex wait vp: 49 0x000000010003618c (oninit)yield_processor_mvp 0x0000000100038b6c (oninit)mt_lock_wait 0x0000000100045a2c (oninit)mt_lock 0x0000000100d72958 (oninit)smi_network_io 0x000000010030a0fc (oninit)pstread 0x00000001002f1c00 (oninit)pst_rsread 0x00000001002fafd8 (oninit)rsread 0x00000001004c0c18 (oninit)fmread 0x0000000100668588 (oninit)readseq_single 0x0000000100668cd0 (oninit)gettupl 0x000000010066c488 (oninit)scan_next 0x0000000100d1182c (oninit)next_row 0x0000000100d1151c (oninit)get_first_row_from_producer 0x0000000100d10498 (oninit)hash_process_all_groups 0x0000000100d13f9c (oninit)group_open 0x000000010065f428 (oninit)filltemp 0x000000010066cd40 (oninit)scan_open 0x000000010066dc58 (oninit)materialize_viewtmp 0x000000010066dcdc (oninit)materialize_viewtmp 0x000000010066dcc8 (oninit)materialize_viewtmp 0x000000010066dcc8 (oninit)materialize_viewtmp 0x000000010040be94 (oninit)prepselect 0x000000010067ad80 (oninit)subqprep 0x000000010067b2f4 (oninit)exsubq 0x00000001005953d4 (oninit)geval 0x000000010075f308 (oninit)eval_projection_list 0x00000001007646c8 (oninit)dodmlrow 0x0000000100767254 (oninit)dodelupd 0x000000010041d030 (oninit)aud_dodelupd 0x000000010042593c (oninit)excommand 0x000000010023d4c0 (oninit)ip_evalsql 0x0000000100248f78 (oninit)runproc 0x0000000100243c7c (oninit)udrlm_spl_execute 0x0000000100ce98e4 (oninit)udrlm_exec_routine 0x0000000100581ea4 (oninit)udr_execute 0x00000001005bb768 (oninit)exroutine 0x00000001004240d0 (oninit)execproc 0x0000000100418120 (oninit)aud_execproc 0x0000000100426288 (oninit)excommand 0x000000010023d4c0 (oninit)ip_evalsql 0x0000000100248f78 (oninit)runproc 0x0000000100243c7c (oninit)udrlm_spl_execute 0x0000000100ce98e4 (oninit)udrlm_exec_routine 0x0000000100581ea4 (oninit)udr_execute 0x00000001003d8230 (oninit)exec_sysdbproc 0x0000000100cef16c (oninit)sqscb_cleanup 0x0000000100139a5c (oninit)destroy_session 0x00000001001f9894 (oninit)sqsetconerr 0x0000000100217148 (oninit)asf_recv 0x00000001002184c0 (oninit)_iread 0x000000010021883c (oninit)_igetint 0x000000010022451c (oninit)sqmain 0x000000010037c160 (oninit)listen_verify 0x000000010037a608 (oninit)spawn_thread 0x0000000100dd528c (oninit)startup 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk <david@smooth1.co.uk>: > > For interest (and the FAQ!) do you have an onstat -g stk for any of the > holders or waiters of the mutex? > > And an APAR/release numbers when it was fixed? > > Marco/IBM can you confirm what the mutex is used for? > > Kind Regards, > David. > > > On 05 September 2014 at 18:33 Cesar Martins < > cesar.inacio.martins@gmail.com> wrote: > > > > > > Hi Fernando , > > > > Yes!! and your question just help me remember a PMR open by my self... > > after check my own history of PMR (because the limited IBM Support site > not > > able to keep the history of your customers PMR) I already get into this > > situation before and just forgot... > > ( 2011-10-17 - 40939,228,631) > > > > At my sysdbopen/close I collect lot of information of the session and > one > > of them is selecting the sysnetworkio . > > The solution what work at that time is avoid access the sysnetworkio , > but > > few months later I reactive this access because our "overhead" of this > > parallel process was minimized for other reasons... now it's heavy > again, > > the problem just come back. > > > > Not sure, but as far I remember, this issue (sysnetworkio VS guard > mutex) > > was resolved at new versions... I just need upgrade my instance... > > > > Thank you! > > > > Bests Regards > > Cesar > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > Hi Art, Hi Marco, > > > > > > > > Hmm... make sense... > > > > sometimes we got nfs.lock too , but this I know is related with > parallel > > > > open/close connections... > > > > But the "guard" too...is new for me.... > > > > > > > > $ og wmx> > > > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up 28> days > > > > 23:12:48 -- 183239776 Kbytes > > > > > > > > Mutexes with waiters: > > > > mid addr name holder lkcnt waiter > > > > waittime > > > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > > > > > 311522235 0 > > > > > > > > 311519249 1 > > > > > > > > 311522173 0 > > > > > > > > 311522502 0 > > > > > > > > 311522306 0 > > > > > > > > 311498199 0 > > > > > > > > 311522330 0 > > > > > > > > 311518371 0 > > > > > > > > 311521699 0 > > > > > > > > 311521847 0 > > > > > > > > 311521991 1 > > > > > > > > 311522751 0 > > > > > > > > 311522203 0 > > > > > > > > 311522325 1 > > > > > > > > 311522657 0 > > > > > > > > 311522623 0 > > > > > > > > 311522521 0 > > > > > > > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > > > > > > > On 05/09/14 15:46, Art Kagel wrote: > > > > > > I don't understand "Guard" mutex. Where are you seeing that? > > > > > > > > > > > > Art > > > > > > > > > > > > > > > > There is a 'guard' mutex. > > > > > It's used in ASF - I think it could be a bottleneck on lots of > > > > connections > > > > > / > > > > > disconnections, but I haven't checked it out. > > > > > > > > > > > Art S. Kagel, Principal Consultant > > > > > > ASK Database Management > > > > > > > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > > opinions > > > > > > and do not reflect on the IIUG, nor any other organization with > > > which I > > > > > am > > > > > > associated either explicitly, implicitly, or by inference. > Neither do > > > > > > those opinions reflect those of other individuals affiliated > with any > > > > > > entity with which I am affiliated nor those of the entities > > > themselves. > > > > > > > > > > > > On Fri, Sep 5, 2014 at 10:34 AM, Cesar Martins < > > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > >> Hi , > > > > > >> > > > > > >> IFX 11.50 FC9X6 - AIX 6.1 > > > > > >> + 1 read only RSS node

↪ replying to Cesar Martins

Paul Watson — Mon, 08 Sep 2014 14:13:42 — source: IIUG Forums & Mailing Lists

You might want to turn off SMT completely, my testing with 11.7 and 6.1 showed greater throughput with SMT off Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Cesar Martins > Sent: Monday, September 08, 2014 9:05 AM > To: ids@iiug.org > Subject: Re: Mutex - guard ? [33720] > > Hi David , > > I've checked my history of emails and this is what I have : > > - There is no FIX , only a suggestion for workaround. > > - The problem occur with exclusive with Power7 processors using SMT-4 > > configuration > > What they was explained , this is because of internal architecture of P7 > > in SMT-4 configuration. > > - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 > > to SMT-2 > > - The ontat -g stk bellow > > - This is into version 11.50 FC9X6 , AIX 6.1 > > stack for thread: 9063619 sqlexec > base: 0x0700001cdecb7000 > len: 266240 > > pc: 0x000000010003618c > tos: 0x0700001cdecf21e0 > state: mutex wait > > vp: 49 > > 0x000000010003618c (oninit)yield_processor_mvp > 0x0000000100038b6c (oninit)mt_lock_wait > 0x0000000100045a2c (oninit)mt_lock > 0x0000000100d72958 (oninit)smi_network_io > 0x000000010030a0fc (oninit)pstread > 0x00000001002f1c00 (oninit)pst_rsread > 0x00000001002fafd8 (oninit)rsread > 0x00000001004c0c18 (oninit)fmread > 0x0000000100668588 (oninit)readseq_single > 0x0000000100668cd0 (oninit)gettupl > 0x000000010066c488 (oninit)scan_next > 0x0000000100d1182c (oninit)next_row > 0x0000000100d1151c (oninit)get_first_row_from_producer > 0x0000000100d10498 (oninit)hash_process_all_groups > 0x0000000100d13f9c (oninit)group_open > 0x000000010065f428 (oninit)filltemp > 0x000000010066cd40 (oninit)scan_open > 0x000000010066dc58 (oninit)materialize_viewtmp > 0x000000010066dcdc (oninit)materialize_viewtmp > 0x000000010066dcc8 (oninit)materialize_viewtmp > 0x000000010066dcc8 (oninit)materialize_viewtmp > 0x000000010040be94 (oninit)prepselect > 0x000000010067ad80 (oninit)subqprep > 0x000000010067b2f4 (oninit)exsubq > 0x00000001005953d4 (oninit)geval > 0x000000010075f308 (oninit)eval_projection_list > 0x00000001007646c8 (oninit)dodmlrow > 0x0000000100767254 (oninit)dodelupd > 0x000000010041d030 (oninit)aud_dodelupd > 0x000000010042593c (oninit)excommand > 0x000000010023d4c0 (oninit)ip_evalsql > 0x0000000100248f78 (oninit)runproc > 0x0000000100243c7c (oninit)udrlm_spl_execute > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > 0x0000000100581ea4 (oninit)udr_execute > 0x00000001005bb768 (oninit)exroutine > 0x00000001004240d0 (oninit)execproc > 0x0000000100418120 (oninit)aud_execproc > 0x0000000100426288 (oninit)excommand > 0x000000010023d4c0 (oninit)ip_evalsql > 0x0000000100248f78 (oninit)runproc > 0x0000000100243c7c (oninit)udrlm_spl_execute > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > 0x0000000100581ea4 (oninit)udr_execute > 0x00000001003d8230 (oninit)exec_sysdbproc > 0x0000000100cef16c (oninit)sqscb_cleanup > 0x0000000100139a5c (oninit)destroy_session > 0x00000001001f9894 (oninit)sqsetconerr > 0x0000000100217148 (oninit)asf_recv > 0x00000001002184c0 (oninit)_iread > 0x000000010021883c (oninit)_igetint > 0x000000010022451c (oninit)sqmain > 0x000000010037c160 (oninit)listen_verify > 0x000000010037a608 (oninit)spawn_thread > 0x0000000100dd528c (oninit)startup > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > <david@smooth1.co.uk>: > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of the > > holders or waiters of the mutex? > > > > And an APAR/release numbers when it was fixed? > > > > Marco/IBM can you confirm what the mutex is used for? > > > > Kind Regards, > > David. > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > Hi Fernando , > > > > > > Yes!! and your question just help me remember a PMR open by my self... > > > after check my own history of PMR (because the limited IBM Support site > > not > > > able to keep the history of your customers PMR) I already get into this > > > situation before and just forgot... > > > ( 2011-10-17 - 40939,228,631) > > > > > > At my sysdbopen/close I collect lot of information of the session and > > one > > > of them is selecting the sysnetworkio . > > > The solution what work at that time is avoid access the sysnetworkio , > > but > > > few months later I reactive this access because our "overhead" of this > > > parallel process was minimized for other reasons... now it's heavy > > again, > > > the problem just come back. > > > > > > Not sure, but as far I remember, this issue (sysnetworkio VS guard > > mutex) > > > was resolved at new versions... I just need upgrade my instance... > > > > > > Thank you! > > > > > > Bests Regards > > > Cesar > > > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes > <domusonline@gmail.com>: > > > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > Hi Art, Hi Marco, > > > > > > > > > > Hmm... make sense... > > > > > sometimes we got nfs.lock too , but this I know is related with > > parallel > > > > > open/close connections... > > > > > But the "guard" too...is new for me.... > > > > > > > > > > $ og wmx> > > > > > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up28 > > days > > > > > 23:12:48 -- 183239776 Kbytes > > > > > > > > > > Mutexes with waiters: > > > > > mid addr name holder lkcnt waiter > > > > > waittime > > > > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > > > > > > > 311522235 0 > > > > > > > > > > 311519249 1 > > > > > > > > > > 311522173 0 > > > > > > > > > > 311522502 0 > > > > > > > > > > 311522306 0 > > > > > > > > > > 311498199 0 > > > > > > > > > > 311522330 0 > > > > > > > > > > 311518371 0 > > > > > > > > > > 311521699 0 > > > > > > > > > > 311521847 0 > > > > > > > > > > 311521991 1 > > > > > > > > > > 311522751 0 > > > > > > > > > > 311522203 0 > > > > > > > > > > 311522325 1 > > > > > > > > > > 311522657 0 > > > > > > > > > > 311522623 0 > > > > > > > > > > 311522521 0 > > > > > > > > > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > > > > > > > > > On 05/09/14 15:46, Art Kagel wrote: > > > > > > > I don't understand "Guard" mutex. Where are you seeing that? > > > > > > > > > > > > > > Art > > > > > > > > > > > > > > > > > > > There is a 'guard' mutex. > > > > > > It's used in ASF - I think it could be a bottleneck on lots of > > > > > connections > > > > > > / > > > > > > disconnections, but I haven't checked it out. > > > > > > > > > > > > > Art S. Kagel, Principal Consultant > > > > > > > ASK Database Management > > > > > > > > > > > > > > Blog: http:

↪ replying to Paul Watson

Art Kagel — Mon, 08 Sep 2014 14:22:29 — source: IIUG Forums & Mailing Lists

I got the same results at a client on P7 & AIX 6. Better with SMT2, best with SMT disabled. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Sep 8, 2014 at 10:13 AM, Paul Watson <paul@oninit.com> wrote: > You might want to turn off SMT completely, my testing with 11.7 and 6.1 > showed greater throughput with SMT off > > Cheers > Paul > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Cesar Martins > > Sent: Monday, September 08, 2014 9:05 AM > > To: ids@iiug.org > > Subject: Re: Mutex - guard ? [33720] > > > > Hi David , > > > > I've checked my history of emails and this is what I have : > > > > - There is no FIX , only a suggestion for workaround. > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > configuration > > > > What they was explained , this is because of internal architecture of P7 > > > > in SMT-4 configuration. > > > > - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 > > > > to SMT-2 > > > > - The ontat -g stk bellow > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > stack for thread: 9063619 sqlexec > > base: 0x0700001cdecb7000 > > len: 266240 > > > > pc: 0x000000010003618c > > tos: 0x0700001cdecf21e0 > > state: mutex wait > > > > vp: 49 > > > > 0x000000010003618c (oninit)yield_processor_mvp > > 0x0000000100038b6c (oninit)mt_lock_wait > > 0x0000000100045a2c (oninit)mt_lock > > 0x0000000100d72958 (oninit)smi_network_io > > 0x000000010030a0fc (oninit)pstread > > 0x00000001002f1c00 (oninit)pst_rsread > > 0x00000001002fafd8 (oninit)rsread > > 0x00000001004c0c18 (oninit)fmread > > 0x0000000100668588 (oninit)readseq_single > > 0x0000000100668cd0 (oninit)gettupl > > 0x000000010066c488 (oninit)scan_next > > 0x0000000100d1182c (oninit)next_row > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > 0x0000000100d10498 (oninit)hash_process_all_groups > > 0x0000000100d13f9c (oninit)group_open > > 0x000000010065f428 (oninit)filltemp > > 0x000000010066cd40 (oninit)scan_open > > 0x000000010066dc58 (oninit)materialize_viewtmp > > 0x000000010066dcdc (oninit)materialize_viewtmp > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > 0x000000010040be94 (oninit)prepselect > > 0x000000010067ad80 (oninit)subqprep > > 0x000000010067b2f4 (oninit)exsubq > > 0x00000001005953d4 (oninit)geval > > 0x000000010075f308 (oninit)eval_projection_list > > 0x00000001007646c8 (oninit)dodmlrow > > 0x0000000100767254 (oninit)dodelupd > > 0x000000010041d030 (oninit)aud_dodelupd > > 0x000000010042593c (oninit)excommand > > 0x000000010023d4c0 (oninit)ip_evalsql > > 0x0000000100248f78 (oninit)runproc > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > 0x0000000100581ea4 (oninit)udr_execute > > 0x00000001005bb768 (oninit)exroutine > > 0x00000001004240d0 (oninit)execproc > > 0x0000000100418120 (oninit)aud_execproc > > 0x0000000100426288 (oninit)excommand > > 0x000000010023d4c0 (oninit)ip_evalsql > > 0x0000000100248f78 (oninit)runproc > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > 0x0000000100581ea4 (oninit)udr_execute > > 0x00000001003d8230 (oninit)exec_sysdbproc > > 0x0000000100cef16c (oninit)sqscb_cleanup > > 0x0000000100139a5c (oninit)destroy_session > > 0x00000001001f9894 (oninit)sqsetconerr > > 0x0000000100217148 (oninit)asf_recv > > 0x00000001002184c0 (oninit)_iread > > 0x000000010021883c (oninit)_igetint > > 0x000000010022451c (oninit)sqmain > > 0x000000010037c160 (oninit)listen_verify > > 0x000000010037a608 (oninit)spawn_thread > > 0x0000000100dd528c (oninit)startup > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > <david@smooth1.co.uk>: > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of the > > > holders or waiters of the mutex? > > > > > > And an APAR/release numbers when it was fixed? > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > Kind Regards, > > > David. > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > Hi Fernando , > > > > > > > > Yes!! and your question just help me remember a PMR open by my > self... > > > > after check my own history of PMR (because the limited IBM Support > site > > > not > > > > able to keep the history of your customers PMR) I already get into > this > > > > situation before and just forgot... > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > At my sysdbopen/close I collect lot of information of the session and > > > one > > > > of them is selecting the sysnetworkio . > > > > The solution what work at that time is avoid access the sysnetworkio > , > > > but > > > > few months later I reactive this access because our "overhead" of > this > > > > parallel process was minimized for other reasons... now it's heavy > > > again, > > > > the problem just come back. > > > > > > > > Not sure, but as far I remember, this issue (sysnetworkio VS guard > > > mutex) > > > > was resolved at new versions... I just need upgrade my instance... > > > > > > > > Thank you! > > > > > > > > Bests Regards > > > > Cesar > > > > > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes > > <domusonline@gmail.com>: > > > > > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > > > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > Hi Art, Hi Marco, > > > > > > > > > > > > Hmm... make sense... > > > > > > sometimes we got nfs.lock too , but this I know is related with > > > parallel > > > > > > open/close connections... > > > > > > But the "guard" too...is new for me.... > > > > > > > > > > > > $ og wmx> > > > > > > > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up> 28 > > > days > > > > > > 23:12:48 -- 183239776 Kbytes > > > > > > > > > > > > Mutexes with waiters: > > > > > > mid addr name holder lkcnt waiter > > > > > > waittime > > > > > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > > > > > > > > > 311522235 0 > > > > > > > > > > > > 311519249 1 > > > > > > > > > > > > 311522173 0 > > > > > > > > > > > > 311522502 0 > > > > > > > > > > > > 311522306 0 > > > > > > > > > > > > 311498199 0 > > > > > > > > > > > > 311522330 0 > > > > > >@@N

↪ replying to Art Kagel

Paul Watson — Mon, 08 Sep 2014 14:36:52 — source: IIUG Forums & Mailing Lists

And it is a very easy thing to turn on and off - so testing is fairly trivial Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Monday, September 08, 2014 9:23 AM > To: ids@iiug.org > Subject: Re: Mutex - guard ? [33722] > > I got the same results at a client on P7 & AIX 6. Better with SMT2, best > with SMT disabled. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Sep 8, 2014 at 10:13 AM, Paul Watson <paul@oninit.com> wrote: > > > You might want to turn off SMT completely, my testing with 11.7 and 6.1 > > showed greater throughput with SMT off > > > > Cheers > > Paul > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Cesar Martins > > > Sent: Monday, September 08, 2014 9:05 AM > > > To: ids@iiug.org > > > Subject: Re: Mutex - guard ? [33720] > > > > > > Hi David , > > > > > > I've checked my history of emails and this is what I have : > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > > > configuration > > > > > > What they was explained , this is because of internal architecture of P7 > > > > > > in SMT-4 configuration. > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > SMT-4 > > > > > > to SMT-2 > > > > > > - The ontat -g stk bellow > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > stack for thread: 9063619 sqlexec > > > base: 0x0700001cdecb7000 > > > len: 266240 > > > > > > pc: 0x000000010003618c > > > tos: 0x0700001cdecf21e0 > > > state: mutex wait > > > > > > vp: 49 > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > 0x0000000100045a2c (oninit)mt_lock > > > 0x0000000100d72958 (oninit)smi_network_io > > > 0x000000010030a0fc (oninit)pstread > > > 0x00000001002f1c00 (oninit)pst_rsread > > > 0x00000001002fafd8 (oninit)rsread > > > 0x00000001004c0c18 (oninit)fmread > > > 0x0000000100668588 (oninit)readseq_single > > > 0x0000000100668cd0 (oninit)gettupl > > > 0x000000010066c488 (oninit)scan_next > > > 0x0000000100d1182c (oninit)next_row > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > 0x0000000100d13f9c (oninit)group_open > > > 0x000000010065f428 (oninit)filltemp > > > 0x000000010066cd40 (oninit)scan_open > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > 0x000000010040be94 (oninit)prepselect > > > 0x000000010067ad80 (oninit)subqprep > > > 0x000000010067b2f4 (oninit)exsubq > > > 0x00000001005953d4 (oninit)geval > > > 0x000000010075f308 (oninit)eval_projection_list > > > 0x00000001007646c8 (oninit)dodmlrow > > > 0x0000000100767254 (oninit)dodelupd > > > 0x000000010041d030 (oninit)aud_dodelupd > > > 0x000000010042593c (oninit)excommand > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > 0x0000000100248f78 (oninit)runproc > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > 0x0000000100581ea4 (oninit)udr_execute > > > 0x00000001005bb768 (oninit)exroutine > > > 0x00000001004240d0 (oninit)execproc > > > 0x0000000100418120 (oninit)aud_execproc > > > 0x0000000100426288 (oninit)excommand > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > 0x0000000100248f78 (oninit)runproc > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > 0x0000000100581ea4 (oninit)udr_execute > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > 0x0000000100139a5c (oninit)destroy_session > > > 0x00000001001f9894 (oninit)sqsetconerr > > > 0x0000000100217148 (oninit)asf_recv > > > 0x00000001002184c0 (oninit)_iread > > > 0x000000010021883c (oninit)_igetint > > > 0x000000010022451c (oninit)sqmain > > > 0x000000010037c160 (oninit)listen_verify > > > 0x000000010037a608 (oninit)spawn_thread > > > 0x0000000100dd528c (oninit)startup > > > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > > <david@smooth1.co.uk>: > > > > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of the > > > > holders or waiters of the mutex? > > > > > > > > And an APAR/release numbers when it was fixed? > > > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > > > Kind Regards, > > > > David. > > > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > > > Hi Fernando , > > > > > > > > > > Yes!! and your question just help me remember a PMR open by my > > self... > > > > > after check my own history of PMR (because the limited IBM Support > > site > > > > not > > > > > able to keep the history of your customers PMR) I already get into > > this > > > > > situation before and just forgot... > > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > > > At my sysdbopen/close I collect lot of information of the session and > > > > one > > > > > of them is selecting the sysnetworkio . > > > > > The solution what work at that time is avoid access the sysnetworkio > > , > > > > but > > > > > few months later I reactive this access because our "overhead" of > > this > > > > > parallel process was minimized for other reasons... now it's heavy > > > > again, > > > > > the problem just come back. > > > > > > > > > > Not sure, but as far I remember, this issue (sysnetworkio VS guard > > > > mutex) > > > > > was resolved at new versions... I just need upgrade my instance... > > > > > > > > > > Thank you! > > > > > > > > > > Bests Regards > > > > > Cesar > > > > > > > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes > > > <domusonline@gmail.com>: > > > > > > > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > > > > > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > Hi Art, Hi Marco, > > > > > > > > > > > > > > Hmm... make sense... > > > > > > > sometimes we got nfs.lock too , but this I know is related with > > > > parallel > > > > > > > open/close connections... > > > > > > > But the "guard" too...is ne

↪ replying to Paul Watson

Fernando Nunes — Mon, 08 Sep 2014 14:41:33 — source: IIUG Forums & Mailing Lists

Off or SMT-2?! On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > You might want to turn off SMT completely, my testing with 11.7 and 6.1 > showed greater throughput with SMT off > > Cheers > Paul > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Cesar Martins > > Sent: Monday, September 08, 2014 9:05 AM > > To: ids@iiug.org > > Subject: Re: Mutex - guard ? [33720] > > > > Hi David , > > > > I've checked my history of emails and this is what I have : > > > > - There is no FIX , only a suggestion for workaround. > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > configuration > > > > What they was explained , this is because of internal architecture of P7 > > > > in SMT-4 configuration. > > > > - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 > > > > to SMT-2 > > > > - The ontat -g stk bellow > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > stack for thread: 9063619 sqlexec > > base: 0x0700001cdecb7000 > > len: 266240 > > > > pc: 0x000000010003618c > > tos: 0x0700001cdecf21e0 > > state: mutex wait > > > > vp: 49 > > > > 0x000000010003618c (oninit)yield_processor_mvp > > 0x0000000100038b6c (oninit)mt_lock_wait > > 0x0000000100045a2c (oninit)mt_lock > > 0x0000000100d72958 (oninit)smi_network_io > > 0x000000010030a0fc (oninit)pstread > > 0x00000001002f1c00 (oninit)pst_rsread > > 0x00000001002fafd8 (oninit)rsread > > 0x00000001004c0c18 (oninit)fmread > > 0x0000000100668588 (oninit)readseq_single > > 0x0000000100668cd0 (oninit)gettupl > > 0x000000010066c488 (oninit)scan_next > > 0x0000000100d1182c (oninit)next_row > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > 0x0000000100d10498 (oninit)hash_process_all_groups > > 0x0000000100d13f9c (oninit)group_open > > 0x000000010065f428 (oninit)filltemp > > 0x000000010066cd40 (oninit)scan_open > > 0x000000010066dc58 (oninit)materialize_viewtmp > > 0x000000010066dcdc (oninit)materialize_viewtmp > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > 0x000000010040be94 (oninit)prepselect > > 0x000000010067ad80 (oninit)subqprep > > 0x000000010067b2f4 (oninit)exsubq > > 0x00000001005953d4 (oninit)geval > > 0x000000010075f308 (oninit)eval_projection_list > > 0x00000001007646c8 (oninit)dodmlrow > > 0x0000000100767254 (oninit)dodelupd > > 0x000000010041d030 (oninit)aud_dodelupd > > 0x000000010042593c (oninit)excommand > > 0x000000010023d4c0 (oninit)ip_evalsql > > 0x0000000100248f78 (oninit)runproc > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > 0x0000000100581ea4 (oninit)udr_execute > > 0x00000001005bb768 (oninit)exroutine > > 0x00000001004240d0 (oninit)execproc > > 0x0000000100418120 (oninit)aud_execproc > > 0x0000000100426288 (oninit)excommand > > 0x000000010023d4c0 (oninit)ip_evalsql > > 0x0000000100248f78 (oninit)runproc > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > 0x0000000100581ea4 (oninit)udr_execute > > 0x00000001003d8230 (oninit)exec_sysdbproc > > 0x0000000100cef16c (oninit)sqscb_cleanup > > 0x0000000100139a5c (oninit)destroy_session > > 0x00000001001f9894 (oninit)sqsetconerr > > 0x0000000100217148 (oninit)asf_recv > > 0x00000001002184c0 (oninit)_iread > > 0x000000010021883c (oninit)_igetint > > 0x000000010022451c (oninit)sqmain > > 0x000000010037c160 (oninit)listen_verify > > 0x000000010037a608 (oninit)spawn_thread > > 0x0000000100dd528c (oninit)startup > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > <david@smooth1.co.uk>: > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of the > > > holders or waiters of the mutex? > > > > > > And an APAR/release numbers when it was fixed? > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > Kind Regards, > > > David. > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > Hi Fernando , > > > > > > > > Yes!! and your question just help me remember a PMR open by my > self... > > > > after check my own history of PMR (because the limited IBM Support > site > > > not > > > > able to keep the history of your customers PMR) I already get into > this > > > > situation before and just forgot... > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > At my sysdbopen/close I collect lot of information of the session and > > > one > > > > of them is selecting the sysnetworkio . > > > > The solution what work at that time is avoid access the sysnetworkio > , > > > but > > > > few months later I reactive this access because our "overhead" of > this > > > > parallel process was minimized for other reasons... now it's heavy > > > again, > > > > the problem just come back. > > > > > > > > Not sure, but as far I remember, this issue (sysnetworkio VS guard > > > mutex) > > > > was resolved at new versions... I just need upgrade my instance... > > > > > > > > Thank you! > > > > > > > > Bests Regards > > > > Cesar > > > > > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes > > <domusonline@gmail.com>: > > > > > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > > > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > Hi Art, Hi Marco, > > > > > > > > > > > > Hmm... make sense... > > > > > > sometimes we got nfs.lock too , but this I know is related with > > > parallel > > > > > > open/close connections... > > > > > > But the "guard" too...is new for me.... > > > > > > > > > > > > $ og wmx> > > > > > > > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line -- Up> 28 > > > days > > > > > > 23:12:48 -- 183239776 Kbytes > > > > > > > > > > > > Mutexes with waiters: > > > > > > mid addr name holder lkcnt waiter > > > > > > waittime > > > > > > 3287 700001c8c0e0940 guard 311522329 0 311521906 0 > > > > > > > > > > > > 311522235 0 > > > > > > > > > > > > 311519249 1 > > > > > > > > > > > > 311522173 0 > > > > > > > > > > > > 311522502 0 > > > > > > > > > > > > 311522306 0 > > > > > > > > > > > > 311498199 0 > > > > > > > > > > > > 311522330 0 > > > > > > > > > > > > 311518371 0 > > > > > > > > > > > > 311521699 0 > > > > > > > > > > > > 311521847 0 > > > > > > > > > > > > 311521991 1 > > > > > > > > > > > > 311522751 0 > > > > > > > > > > > > 311522203 0 > > > > > > > > > > > > 311522325 1 > > > > > > > > > > > > 311522657 0 > > > > > > > > > > > > 311522623 0 > > > > > > > > > > > > 311522521 0 > > > > > > > > > > > > 2014-09-05 11:49 GMT-03:00 Marco Greco <marco@4glworks.com>: > > > > > > > > > > > > > On 05/09/14 15:46, Art Kagel wrote: > > >

↪ replying to Fernando Nunes

Paul Watson — Mon, 08 Sep 2014 14:54:31 — source: IIUG Forums & Mailing Lists

Off - total throughout when the machine is max'd out is the same but you can drive the server harder sooner with SMT off. I was told the issue is AIX SMT threading/scheduling doesn't play nice with the oninit processes. The sysadmins always say it should be on and fight against turning it off and will show you graphs showing the CPU are not that busy so it just can't be an OS problem :) But if you can convince them to test it then ..... it normally stays off - at least on a dedicated DB server. Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Monday, September 08, 2014 9:42 AM > To: ids@iiug.org > Subject: Re: Mutex - guard ? [33724] > > Off or SMT-2?! > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > > > You might want to turn off SMT completely, my testing with 11.7 and 6.1 > > showed greater throughput with SMT off > > > > Cheers > > Paul > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Cesar Martins > > > Sent: Monday, September 08, 2014 9:05 AM > > > To: ids@iiug.org > > > Subject: Re: Mutex - guard ? [33720] > > > > > > Hi David , > > > > > > I've checked my history of emails and this is what I have : > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > > > configuration > > > > > > What they was explained , this is because of internal architecture of P7 > > > > > > in SMT-4 configuration. > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > SMT-4 > > > > > > to SMT-2 > > > > > > - The ontat -g stk bellow > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > stack for thread: 9063619 sqlexec > > > base: 0x0700001cdecb7000 > > > len: 266240 > > > > > > pc: 0x000000010003618c > > > tos: 0x0700001cdecf21e0 > > > state: mutex wait > > > > > > vp: 49 > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > 0x0000000100045a2c (oninit)mt_lock > > > 0x0000000100d72958 (oninit)smi_network_io > > > 0x000000010030a0fc (oninit)pstread > > > 0x00000001002f1c00 (oninit)pst_rsread > > > 0x00000001002fafd8 (oninit)rsread > > > 0x00000001004c0c18 (oninit)fmread > > > 0x0000000100668588 (oninit)readseq_single > > > 0x0000000100668cd0 (oninit)gettupl > > > 0x000000010066c488 (oninit)scan_next > > > 0x0000000100d1182c (oninit)next_row > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > 0x0000000100d13f9c (oninit)group_open > > > 0x000000010065f428 (oninit)filltemp > > > 0x000000010066cd40 (oninit)scan_open > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > 0x000000010040be94 (oninit)prepselect > > > 0x000000010067ad80 (oninit)subqprep > > > 0x000000010067b2f4 (oninit)exsubq > > > 0x00000001005953d4 (oninit)geval > > > 0x000000010075f308 (oninit)eval_projection_list > > > 0x00000001007646c8 (oninit)dodmlrow > > > 0x0000000100767254 (oninit)dodelupd > > > 0x000000010041d030 (oninit)aud_dodelupd > > > 0x000000010042593c (oninit)excommand > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > 0x0000000100248f78 (oninit)runproc > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > 0x0000000100581ea4 (oninit)udr_execute > > > 0x00000001005bb768 (oninit)exroutine > > > 0x00000001004240d0 (oninit)execproc > > > 0x0000000100418120 (oninit)aud_execproc > > > 0x0000000100426288 (oninit)excommand > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > 0x0000000100248f78 (oninit)runproc > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > 0x0000000100581ea4 (oninit)udr_execute > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > 0x0000000100139a5c (oninit)destroy_session > > > 0x00000001001f9894 (oninit)sqsetconerr > > > 0x0000000100217148 (oninit)asf_recv > > > 0x00000001002184c0 (oninit)_iread > > > 0x000000010021883c (oninit)_igetint > > > 0x000000010022451c (oninit)sqmain > > > 0x000000010037c160 (oninit)listen_verify > > > 0x000000010037a608 (oninit)spawn_thread > > > 0x0000000100dd528c (oninit)startup > > > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > > <david@smooth1.co.uk>: > > > > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of the > > > > holders or waiters of the mutex? > > > > > > > > And an APAR/release numbers when it was fixed? > > > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > > > Kind Regards, > > > > David. > > > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > > > Hi Fernando , > > > > > > > > > > Yes!! and your question just help me remember a PMR open by my > > self... > > > > > after check my own history of PMR (because the limited IBM Support > > site > > > > not > > > > > able to keep the history of your customers PMR) I already get into > > this > > > > > situation before and just forgot... > > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > > > At my sysdbopen/close I collect lot of information of the session and > > > > one > > > > > of them is selecting the sysnetworkio . > > > > > The solution what work at that time is avoid access the sysnetworkio > > , > > > > but > > > > > few months later I reactive this access because our "overhead" of > > this > > > > > parallel process was minimized for other reasons... now it's heavy > > > > again, > > > > > the problem just come back. > > > > > > > > > > Not sure, but as far I remember, this issue (sysnetworkio VS guard > > > > mutex) > > > > > was resolved at new versions... I just need upgrade my instance... > > > > > > > > > > Thank you! > > > > > > > > > > Bests Regards > > > > > Cesar > > > > > > > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes > > > <domusonline@gmail.com>: > > > > > > > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > > > > > > > > On Fri, Sep 5, 2014 at 4:21 PM, Cesar Martins < > > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > Hi Art, Hi Marco, > > > > > > > > > > > > > > Hmm... make sense... > > > > > > > sometimes we got nfs.lock too , but this I know is related with > > > > parallel > > > > > > > open/close connections... > > > > > > > But the "guard" too...is new for me.... > > > > > > > > > > > > > > $ og wmx> > > > > > > > > > > > > > IBM Informix Dynamic Server Version 11.50.FC9X6 -- On-Line --Up > > 28 > > >

↪ replying to Paul Watson

Fernando Nunes — Mon, 08 Sep 2014 15:05:00 — source: IIUG Forums & Mailing Lists

Hmmmm... I find it relatively easy to understand if we put SMT-2 vs SMT-4. I find it much harder to understand off... But ok. On Mon, Sep 8, 2014 at 3:54 PM, Paul Watson <paul@oninit.com> wrote: > Off - total throughout when the machine is max'd out is the same but you > can > drive the server harder sooner with SMT off. I was told the issue is AIX > SMT threading/scheduling doesn't play nice with the oninit processes. The > sysadmins always say it should be on and fight against turning it off and > will show you graphs showing the CPU are not that busy so it just can't be > an OS problem :) But if you can convince them to test it then ..... it > normally stays off - at least on a dedicated DB server. > > Cheers > Paul > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Fernando Nunes > > Sent: Monday, September 08, 2014 9:42 AM > > To: ids@iiug.org > > Subject: Re: Mutex - guard ? [33724] > > > > Off or SMT-2?! > > > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > > > > > You might want to turn off SMT completely, my testing with 11.7 and 6.1 > > > showed greater throughput with SMT off > > > > > > Cheers > > > Paul > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > > > > Cesar Martins > > > > Sent: Monday, September 08, 2014 9:05 AM > > > > To: ids@iiug.org > > > > Subject: Re: Mutex - guard ? [33720] > > > > > > > > Hi David , > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > > > > > configuration > > > > > > > > What they was explained , this is because of internal architecture of > P7 > > > > > > > > in SMT-4 configuration. > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > SMT-4 > > > > > > > > to SMT-2 > > > > > > > > - The ontat -g stk bellow > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > stack for thread: 9063619 sqlexec > > > > base: 0x0700001cdecb7000 > > > > len: 266240 > > > > > > > > pc: 0x000000010003618c > > > > tos: 0x0700001cdecf21e0 > > > > state: mutex wait > > > > > > > > vp: 49 > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > 0x0000000100045a2c (oninit)mt_lock > > > > 0x0000000100d72958 (oninit)smi_network_io > > > > 0x000000010030a0fc (oninit)pstread > > > > 0x00000001002f1c00 (oninit)pst_rsread > > > > 0x00000001002fafd8 (oninit)rsread > > > > 0x00000001004c0c18 (oninit)fmread > > > > 0x0000000100668588 (oninit)readseq_single > > > > 0x0000000100668cd0 (oninit)gettupl > > > > 0x000000010066c488 (oninit)scan_next > > > > 0x0000000100d1182c (oninit)next_row > > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > > 0x0000000100d13f9c (oninit)group_open > > > > 0x000000010065f428 (oninit)filltemp > > > > 0x000000010066cd40 (oninit)scan_open > > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > 0x000000010040be94 (oninit)prepselect > > > > 0x000000010067ad80 (oninit)subqprep > > > > 0x000000010067b2f4 (oninit)exsubq > > > > 0x00000001005953d4 (oninit)geval > > > > 0x000000010075f308 (oninit)eval_projection_list > > > > 0x00000001007646c8 (oninit)dodmlrow > > > > 0x0000000100767254 (oninit)dodelupd > > > > 0x000000010041d030 (oninit)aud_dodelupd > > > > 0x000000010042593c (oninit)excommand > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > 0x0000000100248f78 (oninit)runproc > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > 0x00000001005bb768 (oninit)exroutine > > > > 0x00000001004240d0 (oninit)execproc > > > > 0x0000000100418120 (oninit)aud_execproc > > > > 0x0000000100426288 (oninit)excommand > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > 0x0000000100248f78 (oninit)runproc > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > > 0x0000000100139a5c (oninit)destroy_session > > > > 0x00000001001f9894 (oninit)sqsetconerr > > > > 0x0000000100217148 (oninit)asf_recv > > > > 0x00000001002184c0 (oninit)_iread > > > > 0x000000010021883c (oninit)_igetint > > > > 0x000000010022451c (oninit)sqmain > > > > 0x000000010037c160 (oninit)listen_verify > > > > 0x000000010037a608 (oninit)spawn_thread > > > > 0x0000000100dd528c (oninit)startup > > > > > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > > > <david@smooth1.co.uk>: > > > > > > > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of > the > > > > > holders or waiters of the mutex? > > > > > > > > > > And an APAR/release numbers when it was fixed? > > > > > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > > > > > Kind Regards, > > > > > David. > > > > > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > > > > > > Hi Fernando , > > > > > > > > > > > > Yes!! and your question just help me remember a PMR open by my > > > self... > > > > > > after check my own history of PMR (because the limited IBM > Support > > > site > > > > > not > > > > > > able to keep the history of your customers PMR) I already get > into > > > this > > > > > > situation before and just forgot... > > > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > > > > > At my sysdbopen/close I collect lot of information of the session > and > > > > > one > > > > > > of them is selecting the sysnetworkio . > > > > > > The solution what work at that time is avoid access the > sysnetworkio > > > , > > > > > but > > > > > > few months later I reactive this access because our "overhead" of > > > this > > > > > > parallel process was minimized for other reasons... now it's > heavy > > > > > again, > > > > > > the problem just come back. > > > > > > > > > > > > Not sure, but as far I remember, this issue (sysnetworkio VS > guard > > > > > mutex) > > > > > > was resolved at new versions... I just need upgrade my > instance... > > > > > > > > > > > > Thank you! > > > > > > > > > > > > Bests Regards > > > > > > Cesar > > > > > > > > > > > > 2014-09-05 13:18 GMT-03:00 Fernando Nunes > > > > <domusonline@gmail.com>: > > > > > > > > > > > > > Do you have sysdbopen/sysdbclose() procedures? > > > > > >

↪ replying to Fernando Nunes

Paul Watson — Mon, 08 Sep 2014 15:07:11 — source: IIUG Forums & Mailing Lists

Like I say to sysadmins - test it and see what happens - such a trivial test to undertake > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Monday, September 08, 2014 10:05 AM > To: ids@iiug.org > Subject: Re: Mutex - guard ? [33726] > > Hmmmm... I find it relatively easy to understand if we put SMT-2 vs SMT-4. > I find it much harder to understand off... But ok. > > On Mon, Sep 8, 2014 at 3:54 PM, Paul Watson <paul@oninit.com> wrote: > > > Off - total throughout when the machine is max'd out is the same but you > > can > > drive the server harder sooner with SMT off. I was told the issue is AIX > > SMT threading/scheduling doesn't play nice with the oninit processes. The > > sysadmins always say it should be on and fight against turning it off and > > will show you graphs showing the CPU are not that busy so it just can't be > > an OS problem :) But if you can convince them to test it then ..... it > > normally stays off - at least on a dedicated DB server. > > > > Cheers > > Paul > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Fernando Nunes > > > Sent: Monday, September 08, 2014 9:42 AM > > > To: ids@iiug.org > > > Subject: Re: Mutex - guard ? [33724] > > > > > > Off or SMT-2?! > > > > > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > > > > > > > You might want to turn off SMT completely, my testing with 11.7 and > 6.1 > > > > showed greater throughput with SMT off > > > > > > > > Cheers > > > > Paul > > > > > > > > > -----Original Message----- > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of > > > > > Cesar Martins > > > > > Sent: Monday, September 08, 2014 9:05 AM > > > > > To: ids@iiug.org > > > > > Subject: Re: Mutex - guard ? [33720] > > > > > > > > > > Hi David , > > > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > > > - The problem occur with exclusive with Power7 processors using > SMT-4 > > > > > > > > > > configuration > > > > > > > > > > What they was explained , this is because of internal architecture of > > P7 > > > > > > > > > > in SMT-4 configuration. > > > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > > SMT-4 > > > > > > > > > > to SMT-2 > > > > > > > > > > - The ontat -g stk bellow > > > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > > > stack for thread: 9063619 sqlexec > > > > > base: 0x0700001cdecb7000 > > > > > len: 266240 > > > > > > > > > > pc: 0x000000010003618c > > > > > tos: 0x0700001cdecf21e0 > > > > > state: mutex wait > > > > > > > > > > vp: 49 > > > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > > 0x0000000100045a2c (oninit)mt_lock > > > > > 0x0000000100d72958 (oninit)smi_network_io > > > > > 0x000000010030a0fc (oninit)pstread > > > > > 0x00000001002f1c00 (oninit)pst_rsread > > > > > 0x00000001002fafd8 (oninit)rsread > > > > > 0x00000001004c0c18 (oninit)fmread > > > > > 0x0000000100668588 (oninit)readseq_single > > > > > 0x0000000100668cd0 (oninit)gettupl > > > > > 0x000000010066c488 (oninit)scan_next > > > > > 0x0000000100d1182c (oninit)next_row > > > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > > > 0x0000000100d13f9c (oninit)group_open > > > > > 0x000000010065f428 (oninit)filltemp > > > > > 0x000000010066cd40 (oninit)scan_open > > > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > > 0x000000010040be94 (oninit)prepselect > > > > > 0x000000010067ad80 (oninit)subqprep > > > > > 0x000000010067b2f4 (oninit)exsubq > > > > > 0x00000001005953d4 (oninit)geval > > > > > 0x000000010075f308 (oninit)eval_projection_list > > > > > 0x00000001007646c8 (oninit)dodmlrow > > > > > 0x0000000100767254 (oninit)dodelupd > > > > > 0x000000010041d030 (oninit)aud_dodelupd > > > > > 0x000000010042593c (oninit)excommand > > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > > 0x0000000100248f78 (oninit)runproc > > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > > 0x00000001005bb768 (oninit)exroutine > > > > > 0x00000001004240d0 (oninit)execproc > > > > > 0x0000000100418120 (oninit)aud_execproc > > > > > 0x0000000100426288 (oninit)excommand > > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > > 0x0000000100248f78 (oninit)runproc > > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > > > 0x0000000100139a5c (oninit)destroy_session > > > > > 0x00000001001f9894 (oninit)sqsetconerr > > > > > 0x0000000100217148 (oninit)asf_recv > > > > > 0x00000001002184c0 (oninit)_iread > > > > > 0x000000010021883c (oninit)_igetint > > > > > 0x000000010022451c (oninit)sqmain > > > > > 0x000000010037c160 (oninit)listen_verify > > > > > 0x000000010037a608 (oninit)spawn_thread > > > > > 0x0000000100dd528c (oninit)startup > > > > > > > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > > > > <david@smooth1.co.uk>: > > > > > > > > > > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of > > the > > > > > > holders or waiters of the mutex? > > > > > > > > > > > > And an APAR/release numbers when it was fixed? > > > > > > > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > > > > > > > Kind Regards, > > > > > > David. > > > > > > > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > > > > > > > > > Hi Fernando , > > > > > > > > > > > > > > Yes!! and your question just help me remember a PMR open by > my > > > > self... > > > > > > > after check my own history of PMR (because the limited IBM > > Support > > > > site > > > > > > not > > > > > > > able to keep the history of your customers PMR) I already get > > into > > > > this > > > > > > > situation before and just forgot... > > > > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > > > > > > > At my sysdbopen/close I collect lot of information of the session > > and > > > > > > one > > > > > > > of them is selecting the sysnetworkio . > > > > > > > The solution what work at that time is avoid access the > > sysnetworkio > > > > , > > > > > > but > > > > > > > few months later I reactive this access b

↪ replying to Paul Watson

Art Kagel — Mon, 08 Sep 2014 15:08:57 — source: IIUG Forums & Mailing Lists

Another thing to look at is the processor folding when SMT is enabled. The machine will disable and enable SMT threads dynamically and that is costly. You need to turn that off for Informix. Whatever SMT level you choose has to be fixed to perform well. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Sep 8, 2014 at 10:54 AM, Paul Watson <paul@oninit.com> wrote: > Off - total throughout when the machine is max'd out is the same but you > can > drive the server harder sooner with SMT off. I was told the issue is AIX > SMT threading/scheduling doesn't play nice with the oninit processes. The > sysadmins always say it should be on and fight against turning it off and > will show you graphs showing the CPU are not that busy so it just can't be > an OS problem :) But if you can convince them to test it then ..... it > normally stays off - at least on a dedicated DB server. > > Cheers > Paul > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Fernando Nunes > > Sent: Monday, September 08, 2014 9:42 AM > > To: ids@iiug.org > > Subject: Re: Mutex - guard ? [33724] > > > > Off or SMT-2?! > > > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > > > > > You might want to turn off SMT completely, my testing with 11.7 and 6.1 > > > showed greater throughput with SMT off > > > > > > Cheers > > > Paul > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > > > > Cesar Martins > > > > Sent: Monday, September 08, 2014 9:05 AM > > > > To: ids@iiug.org > > > > Subject: Re: Mutex - guard ? [33720] > > > > > > > > Hi David , > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > > > > > configuration > > > > > > > > What they was explained , this is because of internal architecture of > P7 > > > > > > > > in SMT-4 configuration. > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > SMT-4 > > > > > > > > to SMT-2 > > > > > > > > - The ontat -g stk bellow > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > stack for thread: 9063619 sqlexec > > > > base: 0x0700001cdecb7000 > > > > len: 266240 > > > > > > > > pc: 0x000000010003618c > > > > tos: 0x0700001cdecf21e0 > > > > state: mutex wait > > > > > > > > vp: 49 > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > 0x0000000100045a2c (oninit)mt_lock > > > > 0x0000000100d72958 (oninit)smi_network_io > > > > 0x000000010030a0fc (oninit)pstread > > > > 0x00000001002f1c00 (oninit)pst_rsread > > > > 0x00000001002fafd8 (oninit)rsread > > > > 0x00000001004c0c18 (oninit)fmread > > > > 0x0000000100668588 (oninit)readseq_single > > > > 0x0000000100668cd0 (oninit)gettupl > > > > 0x000000010066c488 (oninit)scan_next > > > > 0x0000000100d1182c (oninit)next_row > > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > > 0x0000000100d13f9c (oninit)group_open > > > > 0x000000010065f428 (oninit)filltemp > > > > 0x000000010066cd40 (oninit)scan_open > > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > 0x000000010040be94 (oninit)prepselect > > > > 0x000000010067ad80 (oninit)subqprep > > > > 0x000000010067b2f4 (oninit)exsubq > > > > 0x00000001005953d4 (oninit)geval > > > > 0x000000010075f308 (oninit)eval_projection_list > > > > 0x00000001007646c8 (oninit)dodmlrow > > > > 0x0000000100767254 (oninit)dodelupd > > > > 0x000000010041d030 (oninit)aud_dodelupd > > > > 0x000000010042593c (oninit)excommand > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > 0x0000000100248f78 (oninit)runproc > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > 0x00000001005bb768 (oninit)exroutine > > > > 0x00000001004240d0 (oninit)execproc > > > > 0x0000000100418120 (oninit)aud_execproc > > > > 0x0000000100426288 (oninit)excommand > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > 0x0000000100248f78 (oninit)runproc > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > > 0x0000000100139a5c (oninit)destroy_session > > > > 0x00000001001f9894 (oninit)sqsetconerr > > > > 0x0000000100217148 (oninit)asf_recv > > > > 0x00000001002184c0 (oninit)_iread > > > > 0x000000010021883c (oninit)_igetint > > > > 0x000000010022451c (oninit)sqmain > > > > 0x000000010037c160 (oninit)listen_verify > > > > 0x000000010037a608 (oninit)spawn_thread > > > > 0x0000000100dd528c (oninit)startup > > > > > > > > 2014-09-05 18:12 GMT-03:00 david@smooth1.co.uk > > > > <david@smooth1.co.uk>: > > > > > > > > > > > > > > For interest (and the FAQ!) do you have an onstat -g stk for any of > the > > > > > holders or waiters of the mutex? > > > > > > > > > > And an APAR/release numbers when it was fixed? > > > > > > > > > > Marco/IBM can you confirm what the mutex is used for? > > > > > > > > > > Kind Regards, > > > > > David. > > > > > > > > > > > On 05 September 2014 at 18:33 Cesar Martins < > > > > > cesar.inacio.martins@gmail.com> wrote: > > > > > > > > > > > > > > > > > > Hi Fernando , > > > > > > > > > > > > Yes!! and your question just help me remember a PMR open by my > > > self... > > > > > > after check my own history of PMR (because the limited IBM > Support > > > site > > > > > not > > > > > > able to keep the history of your customers PMR) I already get > into > > > this > > > > > > situation before and just forgot... > > > > > > ( 2011-10-17 - 40939,228,631) > > > > > > > > > > > > At my sysdbopen/close I collect lot of information of the session > and > > > > > one > > > > > > of them is selecting the sysnetworkio . > > > > > > The solution what work at that time is avoid access the > sysnetworkio > > > , > > > > > but > > > > > > few months later I reactive this access because our "overhead" of > > > this > > > > > > parallel process w

↪ replying to Cesar Martins

Marco Greco — Mon, 08 Sep 2014 17:05:45 — source: IIUG Forums & Mailing Lists

On 08/09/14 15:05, Cesar Martins wrote: > Hi David , > > I've checked my history of emails and this is what I have : > > - There is no FIX , only a suggestion for workaround. > > - The problem occur with exclusive with Power7 processors using SMT-4 > > configuration > > What they was explained , this is because of internal architecture of P7 > > in SMT-4 configuration. > > - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 > > to SMT-2 > > - The ontat -g stk bellow > > - This is into version 11.50 FC9X6 , AIX 6.1 > > stack for thread: 9063619 sqlexec > base: 0x0700001cdecb7000 > len: 266240 > > pc: 0x000000010003618c > tos: 0x0700001cdecf21e0 > state: mutex wait > > vp: 49 > > 0x000000010003618c (oninit)yield_processor_mvp > 0x0000000100038b6c (oninit)mt_lock_wait > 0x0000000100045a2c (oninit)mt_lock > 0x0000000100d72958 (oninit)smi_network_io > 0x000000010030a0fc (oninit)pstread > 0x00000001002f1c00 (oninit)pst_rsread > 0x00000001002fafd8 (oninit)rsread > 0x00000001004c0c18 (oninit)fmread > 0x0000000100668588 (oninit)readseq_single > 0x0000000100668cd0 (oninit)gettupl > 0x000000010066c488 (oninit)scan_next > 0x0000000100d1182c (oninit)next_row > 0x0000000100d1151c (oninit)get_first_row_from_producer > 0x0000000100d10498 (oninit)hash_process_all_groups > 0x0000000100d13f9c (oninit)group_open > 0x000000010065f428 (oninit)filltemp > 0x000000010066cd40 (oninit)scan_open > 0x000000010066dc58 (oninit)materialize_viewtmp > 0x000000010066dcdc (oninit)materialize_viewtmp > 0x000000010066dcc8 (oninit)materialize_viewtmp > 0x000000010066dcc8 (oninit)materialize_viewtmp > 0x000000010040be94 (oninit)prepselect > 0x000000010067ad80 (oninit)subqprep > 0x000000010067b2f4 (oninit)exsubq > 0x00000001005953d4 (oninit)geval > 0x000000010075f308 (oninit)eval_projection_list > 0x00000001007646c8 (oninit)dodmlrow > 0x0000000100767254 (oninit)dodelupd > 0x000000010041d030 (oninit)aud_dodelupd > 0x000000010042593c (oninit)excommand > 0x000000010023d4c0 (oninit)ip_evalsql > 0x0000000100248f78 (oninit)runproc > 0x0000000100243c7c (oninit)udrlm_spl_execute > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > 0x0000000100581ea4 (oninit)udr_execute > 0x00000001005bb768 (oninit)exroutine > 0x00000001004240d0 (oninit)execproc > 0x0000000100418120 (oninit)aud_execproc > 0x0000000100426288 (oninit)excommand > 0x000000010023d4c0 (oninit)ip_evalsql > 0x0000000100248f78 (oninit)runproc > 0x0000000100243c7c (oninit)udrlm_spl_execute > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > 0x0000000100581ea4 (oninit)udr_execute > 0x00000001003d8230 (oninit)exec_sysdbproc > 0x0000000100cef16c (oninit)sqscb_cleanup > 0x0000000100139a5c (oninit)destroy_session > 0x00000001001f9894 (oninit)sqsetconerr > 0x0000000100217148 (oninit)asf_recv > 0x00000001002184c0 (oninit)_iread > 0x000000010021883c (oninit)_igetint > 0x000000010022451c (oninit)sqmain > 0x000000010037c160 (oninit)listen_verify > 0x000000010037a608 (oninit)spawn_thread > 0x0000000100dd528c (oninit)startup > Roughly speaking, from your stack: a client disconnected uncleanly, and as part of your sysdbclose() you are executing a procedure that is doing an update in which there's a subquery which materializes a view which queries sysmaster:sysnetworkio, and it's the query on sysnetworkio which is waiting on the guard mutex, which I guessing is being used to prevent the netscbs from changing as your query gets the next record. If you have lots of sysdbclose querying sysnetworkio that at the same time - that's your bottleneck. Shout at your developer and tell him to never, ever, ever write anything like that again. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

↪ replying to Marco Greco

Fernando Nunes — Mon, 08 Sep 2014 17:35:21 — source: IIUG Forums & Mailing Lists

This is sysnetworkio: create table sysnetworkio ( net_id int, { Net ID } sid int, { session id } net_netscb int8, { address of netscb } net_client_type int, { client type } net_client_name char(12), { client protocal name } net_read_cnt int8, { number of read operations } net_read_bytes int8, { # of bytes txfr to server } net_write_cnt int8, { number of write operations } net_write_bytes int8, { # of bytes txfr to client } net_open_time int, { time connection was made } net_last_read int, { time of last network read } net_last_write int, { time of last network write } net_state int, { state of network connection } net_options int, { sqlhost options } net_prot_id int, { id for protocal name } net_protocol char(10), { prtocal name } net_server_fd int, { poll server fd } net_poll_thread int { poll thread id } ); I understand the need for this to monitor idle sessions for example. Other than that I think the net_client_name or maybe net_protocol can be interesting in an open/close context... But what is the purpose of this sysdbopen()/sysdbclose()? And why would you need to read rows from it besides the one for your own session? Regards. On Mon, Sep 8, 2014 at 6:05 PM, Marco Greco <marco@4glworks.com> wrote: > On 08/09/14 15:05, Cesar Martins wrote: > > Hi David , > > > > I've checked my history of emails and this is what I have : > > > > - There is no FIX , only a suggestion for workaround. > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > configuration > > > > What they was explained , this is because of internal architecture of P7 > > > > in SMT-4 configuration. > > > > - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 > > > > to SMT-2 > > > > - The ontat -g stk bellow > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > stack for thread: 9063619 sqlexec > > base: 0x0700001cdecb7000 > > len: 266240 > > > > pc: 0x000000010003618c > > tos: 0x0700001cdecf21e0 > > state: mutex wait > > > > vp: 49 > > > > 0x000000010003618c (oninit)yield_processor_mvp > > 0x0000000100038b6c (oninit)mt_lock_wait > > 0x0000000100045a2c (oninit)mt_lock > > 0x0000000100d72958 (oninit)smi_network_io > > 0x000000010030a0fc (oninit)pstread > > 0x00000001002f1c00 (oninit)pst_rsread > > 0x00000001002fafd8 (oninit)rsread > > 0x00000001004c0c18 (oninit)fmread > > 0x0000000100668588 (oninit)readseq_single > > 0x0000000100668cd0 (oninit)gettupl > > 0x000000010066c488 (oninit)scan_next > > 0x0000000100d1182c (oninit)next_row > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > 0x0000000100d10498 (oninit)hash_process_all_groups > > 0x0000000100d13f9c (oninit)group_open > > 0x000000010065f428 (oninit)filltemp > > 0x000000010066cd40 (oninit)scan_open > > 0x000000010066dc58 (oninit)materialize_viewtmp > > 0x000000010066dcdc (oninit)materialize_viewtmp > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > 0x000000010040be94 (oninit)prepselect > > 0x000000010067ad80 (oninit)subqprep > > 0x000000010067b2f4 (oninit)exsubq > > 0x00000001005953d4 (oninit)geval > > 0x000000010075f308 (oninit)eval_projection_list > > 0x00000001007646c8 (oninit)dodmlrow > > 0x0000000100767254 (oninit)dodelupd > > 0x000000010041d030 (oninit)aud_dodelupd > > 0x000000010042593c (oninit)excommand > > 0x000000010023d4c0 (oninit)ip_evalsql > > 0x0000000100248f78 (oninit)runproc > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > 0x0000000100581ea4 (oninit)udr_execute > > 0x00000001005bb768 (oninit)exroutine > > 0x00000001004240d0 (oninit)execproc > > 0x0000000100418120 (oninit)aud_execproc > > 0x0000000100426288 (oninit)excommand > > 0x000000010023d4c0 (oninit)ip_evalsql > > 0x0000000100248f78 (oninit)runproc > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > 0x0000000100581ea4 (oninit)udr_execute > > 0x00000001003d8230 (oninit)exec_sysdbproc > > 0x0000000100cef16c (oninit)sqscb_cleanup > > 0x0000000100139a5c (oninit)destroy_session > > 0x00000001001f9894 (oninit)sqsetconerr > > 0x0000000100217148 (oninit)asf_recv > > 0x00000001002184c0 (oninit)_iread > > 0x000000010021883c (oninit)_igetint > > 0x000000010022451c (oninit)sqmain > > 0x000000010037c160 (oninit)listen_verify > > 0x000000010037a608 (oninit)spawn_thread > > 0x0000000100dd528c (oninit)startup > > > > Roughly speaking, from your stack: a client disconnected uncleanly, and as > part of your sysdbclose() you are executing a procedure that is doing an > update in which there's a subquery which materializes a view which queries > sysmaster:sysnetworkio, and it's the query on sysnetworkio which is > waiting on > the guard mutex, which I guessing is being used to prevent the netscbs from > changing as your query gets the next record. > > If you have lots of sysdbclose querying sysnetworkio that at the same time > - > that's your bottleneck. > Shout at your developer and tell him to never, ever, ever write anything > like > that again. > -- > Ciao, > Marco > > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7bacbe6a009f620502913ec1

↪ replying to Fernando Nunes

Cesar Martins — Mon, 08 Sep 2014 17:52:47 — source: IIUG Forums & Mailing Lists

Hi Fernando, The mostly important for me into sysnetworkio is get how much I/O occur (bytes) . With it I able to collect the overhead of network for each application (4GL) , identify the application and then start a investigation over it , why have high network I/O and what can be improved . This way I already detected applications which run for 2 hours and transfers lots of GBytes with database... and frequently the reason of this is the easy way to program into 4GL writing embedded SQL creating a huge overhead for repeated SQLs and the bad behave of the programmer to fetch data copying the SQL for an information which the program already get few lines before into the code. But sometimes is just a bad filter which able to user print a report with all data of the database and tons of paper pages... I save all statistics of all access of our database and I already have 3 years of historical. This already help me to identify why a program becomes slow from certain date where I able to see , before X date they consumption of CPU, Disk and Network have a differ behave.... I've adapted the core of our 4GL system to run procedures into the database to identify it self , saving the name of program and what function into global variables of the session at critical points of the code and sysdbclose . This way I know exactly which code is running with that statistics.... 2014-09-08 14:35 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > This is sysnetworkio: > > create table sysnetworkio> > ( > > net_id int, { Net > ID } > > sid int, { session > id } > > net_netscb int8, { address of > netscb } > > net_client_type int, { client > type } > > net_client_name char(12), { client protocal > name } > > net_read_cnt int8, { number of read > operations } > > net_read_bytes int8, { # of bytes txfr to > server } > > net_write_cnt int8, { number of write > operations } > > net_write_bytes int8, { # of bytes txfr to > client } > > net_open_time int, { time connection was > made } > > net_last_read int, { time of last network > read } > > net_last_write int, { time of last network > write } > > net_state int, { state of network > connection } > > net_options int, { sqlhost > options } > > net_prot_id int, { id for protocal > name } > > net_protocol char(10), { prtocal > name } > > net_server_fd int, { poll server > fd } > > net_poll_thread int { poll thread > id } > > ); > > I understand the need for this to monitor idle sessions for example. > Other than that I think the net_client_name or maybe net_protocol can be > interesting in an open/close context... > But what is the purpose of this sysdbopen()/sysdbclose()? And why would you > need to read rows from it besides the one for your own session? > > Regards. > > On Mon, Sep 8, 2014 at 6:05 PM, Marco Greco <marco@4glworks.com> wrote: > > > On 08/09/14 15:05, Cesar Martins wrote: > > > Hi David , > > > > > > I've checked my history of emails and this is what I have : > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > > > configuration > > > > > > What they was explained , this is because of internal architecture of > P7 > > > > > > in SMT-4 configuration. > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc SMT-4 > > > > > > to SMT-2 > > > > > > - The ontat -g stk bellow > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > stack for thread: 9063619 sqlexec > > > base: 0x0700001cdecb7000 > > > len: 266240 > > > > > > pc: 0x000000010003618c > > > tos: 0x0700001cdecf21e0 > > > state: mutex wait > > > > > > vp: 49 > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > 0x0000000100045a2c (oninit)mt_lock > > > 0x0000000100d72958 (oninit)smi_network_io > > > 0x000000010030a0fc (oninit)pstread > > > 0x00000001002f1c00 (oninit)pst_rsread > > > 0x00000001002fafd8 (oninit)rsread > > > 0x00000001004c0c18 (oninit)fmread > > > 0x0000000100668588 (oninit)readseq_single > > > 0x0000000100668cd0 (oninit)gettupl > > > 0x000000010066c488 (oninit)scan_next > > > 0x0000000100d1182c (oninit)next_row > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > 0x0000000100d13f9c (oninit)group_open > > > 0x000000010065f428 (oninit)filltemp > > > 0x000000010066cd40 (oninit)scan_open > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > 0x000000010040be94 (oninit)prepselect > > > 0x000000010067ad80 (oninit)subqprep > > > 0x000000010067b2f4 (oninit)exsubq > > > 0x00000001005953d4 (oninit)geval > > > 0x000000010075f308 (oninit)eval_projection_list > > > 0x00000001007646c8 (oninit)dodmlrow > > > 0x0000000100767254 (oninit)dodelupd > > > 0x000000010041d030 (oninit)aud_dodelupd > > > 0x000000010042593c (oninit)excommand > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > 0x0000000100248f78 (oninit)runproc > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > 0x0000000100581ea4 (oninit)udr_execute > > > 0x00000001005bb768 (oninit)exroutine > > > 0x00000001004240d0 (oninit)execproc > > > 0x0000000100418120 (oninit)aud_execproc > > > 0x0000000100426288 (oninit)excommand > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > 0x0000000100248f78 (oninit)runproc > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > 0x0000000100581ea4 (oninit)udr_execute > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > 0x0000000100139a5c (oninit)destroy_session > > > 0x00000001001f9894 (oninit)sqsetconerr > > > 0x0000000100217148 (oninit)asf_recv > > > 0x00000001002184c0 (oninit)_iread > > > 0x000000010021883c (oninit)_igetint > > > 0x000000010022451c (oninit)sqmain > > > 0x000000010037c160 (oninit)listen_verify > > > 0x000000010037a608 (oninit)spawn_thread > > > 0x0000000100dd528c (oninit)startup > > > > > > > Roughly speaking, from your stack: a client disconnected uncleanly, and > as > > part of your sysdbclose() you are executing a procedure that is doing an > > update in which there's a subquery which materializes a view which > queries > > sysmaster:sysnetworkio, and it's the query on sysnetworkio which is > > waiting on > > the guard mutex, which I guessing is being used to prevent the netscbs > from > > changing as your query gets the next record. > > > > If you have lots of sysdbclose querying sysnetworkio that at the same > time > > - > > that's your bottleneck. > > Shout at your developer and tell him to never, ever, ever write anything > > like > > that a

↪ replying to Cesar Martins

Fernando Nunes — Mon, 08 Sep 2014 17:58:51 — source: IIUG Forums & Mailing Lists

But don't you use SID in your queries? Or even so, the server reads all the rows in sysnetworkio? On Mon, Sep 8, 2014 at 6:52 PM, Cesar Martins < cesar.inacio.martins@gmail.com> wrote: > Hi Fernando, > > The mostly important for me into sysnetworkio is get how much I/O occur > (bytes) . > With it I able to collect the overhead of network for each application > (4GL) , identify the application and then start a investigation over it , > why have high network I/O and what can be improved . > > This way I already detected applications which run for 2 hours and > transfers lots of GBytes with database... and frequently the reason of this > is the easy way to program into 4GL writing embedded SQL creating a huge > overhead for repeated SQLs and the bad behave of the programmer to fetch > data copying the SQL for an information which the program already get few > lines before into the code. > But sometimes is just a bad filter which able to user print a report with > all data of the database and tons of paper pages... > > I save all statistics of all access of our database and I already have 3 > years of historical. This already help me to identify why a program becomes > slow from certain date where I able to see , before X date they consumption > of CPU, Disk and Network have a differ behave.... > > I've adapted the core of our 4GL system to run procedures into the database > to identify it self , saving the name of program and what function into > global variables of the session at critical points of the code and > sysdbclose . This way I know exactly which code is running with that > statistics.... > > 2014-09-08 14:35 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > > > This is sysnetworkio: > > > > create table sysnetworkio> > > > ( > > > > net_id int, { Net > > ID } > > > > sid int, { session > > id } > > > > net_netscb int8, { address of > > netscb } > > > > net_client_type int, { client > > type } > > > > net_client_name char(12), { client protocal > > name } > > > > net_read_cnt int8, { number of read > > operations } > > > > net_read_bytes int8, { # of bytes txfr to > > server } > > > > net_write_cnt int8, { number of write > > operations } > > > > net_write_bytes int8, { # of bytes txfr to > > client } > > > > net_open_time int, { time connection was > > made } > > > > net_last_read int, { time of last network > > read } > > > > net_last_write int, { time of last network > > write } > > > > net_state int, { state of network > > connection } > > > > net_options int, { sqlhost > > options } > > > > net_prot_id int, { id for protocal > > name } > > > > net_protocol char(10), { prtocal > > name } > > > > net_server_fd int, { poll server > > fd } > > > > net_poll_thread int { poll thread > > id } > > > > ); > > > > I understand the need for this to monitor idle sessions for example. > > Other than that I think the net_client_name or maybe net_protocol can be > > interesting in an open/close context... > > But what is the purpose of this sysdbopen()/sysdbclose()? And why would > you > > need to read rows from it besides the one for your own session? > > > > Regards. > > > > On Mon, Sep 8, 2014 at 6:05 PM, Marco Greco <marco@4glworks.com> wrote: > > > > > On 08/09/14 15:05, Cesar Martins wrote: > > > > Hi David , > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > - The problem occur with exclusive with Power7 processors using SMT-4 > > > > > > > > configuration > > > > > > > > What they was explained , this is because of internal architecture of > > P7 > > > > > > > > in SMT-4 configuration. > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > SMT-4 > > > > > > > > to SMT-2 > > > > > > > > - The ontat -g stk bellow > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > stack for thread: 9063619 sqlexec > > > > base: 0x0700001cdecb7000 > > > > len: 266240 > > > > > > > > pc: 0x000000010003618c > > > > tos: 0x0700001cdecf21e0 > > > > state: mutex wait > > > > > > > > vp: 49 > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > 0x0000000100045a2c (oninit)mt_lock > > > > 0x0000000100d72958 (oninit)smi_network_io > > > > 0x000000010030a0fc (oninit)pstread > > > > 0x00000001002f1c00 (oninit)pst_rsread > > > > 0x00000001002fafd8 (oninit)rsread > > > > 0x00000001004c0c18 (oninit)fmread > > > > 0x0000000100668588 (oninit)readseq_single > > > > 0x0000000100668cd0 (oninit)gettupl > > > > 0x000000010066c488 (oninit)scan_next > > > > 0x0000000100d1182c (oninit)next_row > > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > > 0x0000000100d13f9c (oninit)group_open > > > > 0x000000010065f428 (oninit)filltemp > > > > 0x000000010066cd40 (oninit)scan_open > > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > 0x000000010040be94 (oninit)prepselect > > > > 0x000000010067ad80 (oninit)subqprep > > > > 0x000000010067b2f4 (oninit)exsubq > > > > 0x00000001005953d4 (oninit)geval > > > > 0x000000010075f308 (oninit)eval_projection_list > > > > 0x00000001007646c8 (oninit)dodmlrow > > > > 0x0000000100767254 (oninit)dodelupd > > > > 0x000000010041d030 (oninit)aud_dodelupd > > > > 0x000000010042593c (oninit)excommand > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > 0x0000000100248f78 (oninit)runproc > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > 0x00000001005bb768 (oninit)exroutine > > > > 0x00000001004240d0 (oninit)execproc > > > > 0x0000000100418120 (oninit)aud_execproc > > > > 0x0000000100426288 (oninit)excommand > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > 0x0000000100248f78 (oninit)runproc > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > 0x00000001003d8230 (oninit)exec_sysdbproc > > > > 0x0000000100cef16c (oninit)sqscb_cleanup > > > > 0x0000000100139a5c (oninit)destroy_session > > > > 0x00000001001f9894 (oninit)sqsetconerr > > > > 0x0000000100217148 (oninit)asf_recv > > > > 0x00000001002184c0 (oninit)_iread > > > > 0x000000010021883c (oninit)_igetint > > > > 0x000000010022451c (oninit)sqmain > > > > 0x000000010037c160 (oninit)listen_verify > > > > 0x000000010037a608 (oninit)spawn_thread > > > > 0x0000000100dd528c (oninit)startup > > > > > > > > > > Roughly speaking, from your stack: a client disconnected uncleanly, and > > as > > > part of your sysdbclose() you are executing a

↪ replying to Cesar Martins

Fernando Nunes — Mon, 08 Sep 2014 18:07:35 — source: IIUG Forums & Mailing Lists

Forget.... it doesn't have an index on sid (in any case, "indexes" on SMI "tables" are a complex matter by itself) The only "index" on this pseudo-table is on net_id... If you happen to access sysnetworkio on sysdbopen you could save that in a global variable and use that on sysbdclose(). That would minimize the issue, but by no chance would it solve the bottleneck... Regards. On Mon, Sep 8, 2014 at 6:58 PM, Fernando Nunes <domusonline@gmail.com> wrote: > But don't you use SID in your queries? Or even so, the server reads all > the rows in sysnetworkio? > > On Mon, Sep 8, 2014 at 6:52 PM, Cesar Martins < > cesar.inacio.martins@gmail.com> wrote: > >> Hi Fernando, >> >> The mostly important for me into sysnetworkio is get how much I/O occur >> (bytes) . >> With it I able to collect the overhead of network for each application >> (4GL) , identify the application and then start a investigation over it , >> why have high network I/O and what can be improved . >> >> This way I already detected applications which run for 2 hours and >> transfers lots of GBytes with database... and frequently the reason of >> this >> is the easy way to program into 4GL writing embedded SQL creating a huge >> overhead for repeated SQLs and the bad behave of the programmer to fetch >> data copying the SQL for an information which the program already get few >> lines before into the code. >> But sometimes is just a bad filter which able to user print a report with >> all data of the database and tons of paper pages... >> >> I save all statistics of all access of our database and I already have 3 >> years of historical. This already help me to identify why a program >> becomes >> slow from certain date where I able to see , before X date they >> consumption >> of CPU, Disk and Network have a differ behave.... >> >> I've adapted the core of our 4GL system to run procedures into the >> database >> to identify it self , saving the name of program and what function into >> global variables of the session at critical points of the code and >> sysdbclose . This way I know exactly which code is running with that >> statistics.... >> >> 2014-09-08 14:35 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: >> >> > This is sysnetworkio: >> > >> > create table sysnetworkio>> > >> > ( >> > >> > net_id int, { Net >> > ID } >> > >> > sid int, { session >> > id } >> > >> > net_netscb int8, { address of >> > netscb } >> > >> > net_client_type int, { client >> > type } >> > >> > net_client_name char(12), { client protocal >> > name } >> > >> > net_read_cnt int8, { number of read >> > operations } >> > >> > net_read_bytes int8, { # of bytes txfr to >> > server } >> > >> > net_write_cnt int8, { number of write >> > operations } >> > >> > net_write_bytes int8, { # of bytes txfr to >> > client } >> > >> > net_open_time int, { time connection was >> > made } >> > >> > net_last_read int, { time of last network >> > read } >> > >> > net_last_write int, { time of last network >> > write } >> > >> > net_state int, { state of network >> > connection } >> > >> > net_options int, { sqlhost >> > options } >> > >> > net_prot_id int, { id for protocal >> > name } >> > >> > net_protocol char(10), { prtocal >> > name } >> > >> > net_server_fd int, { poll server >> > fd } >> > >> > net_poll_thread int { poll thread >> > id } >> > >> > ); >> > >> > I understand the need for this to monitor idle sessions for example. >> > Other than that I think the net_client_name or maybe net_protocol can be >> > interesting in an open/close context... >> > But what is the purpose of this sysdbopen()/sysdbclose()? And why would >> you >> > need to read rows from it besides the one for your own session? >> > >> > Regards. >> > >> > On Mon, Sep 8, 2014 at 6:05 PM, Marco Greco <marco@4glworks.com> wrote: >> > >> > > On 08/09/14 15:05, Cesar Martins wrote: >> > > > Hi David , >> > > > >> > > > I've checked my history of emails and this is what I have : >> > > > >> > > > - There is no FIX , only a suggestion for workaround. >> > > > >> > > > - The problem occur with exclusive with Power7 processors using >> SMT-4 >> > > > >> > > > configuration >> > > > >> > > > What they was explained , this is because of internal architecture >> of >> > P7 >> > > > >> > > > in SMT-4 configuration. >> > > > >> > > > - workaround suggested (from IBM Support) , change the LPAR proc >> SMT-4 >> > > > >> > > > to SMT-2 >> > > > >> > > > - The ontat -g stk bellow >> > > > >> > > > - This is into version 11.50 FC9X6 , AIX 6.1 >> > > > >> > > > stack for thread: 9063619 sqlexec >> > > > base: 0x0700001cdecb7000 >> > > > len: 266240 >> > > > >> > > > pc: 0x000000010003618c >> > > > tos: 0x0700001cdecf21e0 >> > > > state: mutex wait >> > > > >> > > > vp: 49 >> > > > >> > > > 0x000000010003618c (oninit)yield_processor_mvp >> > > > 0x0000000100038b6c (oninit)mt_lock_wait >> > > > 0x0000000100045a2c (oninit)mt_lock >> > > > 0x0000000100d72958 (oninit)smi_network_io >> > > > 0x000000010030a0fc (oninit)pstread >> > > > 0x00000001002f1c00 (oninit)pst_rsread >> > > > 0x00000001002fafd8 (oninit)rsread >> > > > 0x00000001004c0c18 (oninit)fmread >> > > > 0x0000000100668588 (oninit)readseq_single >> > > > 0x0000000100668cd0 (oninit)gettupl >> > > > 0x000000010066c488 (oninit)scan_next >> > > > 0x0000000100d1182c (oninit)next_row >> > > > 0x0000000100d1151c (oninit)get_first_row_from_producer >> > > > 0x0000000100d10498 (oninit)hash_process_all_groups >> > > > 0x0000000100d13f9c (oninit)group_open >> > > > 0x000000010065f428 (oninit)filltemp >> > > > 0x000000010066cd40 (oninit)scan_open >> > > > 0x000000010066dc58 (oninit)materialize_viewtmp >> > > > 0x000000010066dcdc (oninit)materialize_viewtmp >> > > > 0x000000010066dcc8 (oninit)materialize_viewtmp >> > > > 0x000000010066dcc8 (oninit)materialize_viewtmp >> > > > 0x000000010040be94 (oninit)prepselect >> > > > 0x000000010067ad80 (oninit)subqprep >> > > > 0x000000010067b2f4 (oninit)exsubq >> > > > 0x00000001005953d4 (oninit)geval >> > > > 0x000000010075f308 (oninit)eval_projection_list >> > > > 0x00000001007646c8 (oninit)dodmlrow >> > > > 0x0000000100767254 (oninit)dodelupd >> > > > 0x000000010041d030 (oninit)aud_dodelupd >> > > > 0x000000010042593c (oninit)excommand >> > > > 0x000000010023d4c0 (oninit)ip_evalsql >> > > > 0x0000000100248f78 (oninit)runproc >> > > > 0x0000000100243c7c (oninit)udrlm_spl_execute >> > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine >> > > > 0x0000000100581ea4 (oninit)udr_execute >> > > > 0x00000001005bb768 (oninit)exroutine >> > > > 0x00000001004240d0 (oninit)execproc >> > > > 0x0000000100418120 (oninit)aud_execproc >> > > > 0x0000000100426288 (oninit)excommand >> > > > 0x000000010023d4c0 (oninit)ip_evalsql >> > > > 0x0000000100248f78 (oninit)runproc >> > > > 0x0000000100243c7c (oninit)udrlm_spl_execute >> > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine@@N

↪ replying to Fernando Nunes

Cesar Martins — Mon, 08 Sep 2014 18:13:58 — source: IIUG Forums & Mailing Lists

Yes, we use SID to filter . But naturally the query what we use to read the statistics have joins with other sys* to get all statistics from a single SQL . The SQL which get into the problem is the bellow: I cut off the INTO clause and just don't ask me why the decode() functions... I don't remember why use it.I need to review this decodes... select vData , vData , vData - dbinfo('utc_to_datetime', s.connected) , vData , vData , p.lockreqs , p.lockwts , p.deadlks , p.lktouts , p.longtxs , p.logrecs , p.logspused / 1024 , p.maxlogsp / 1024 , n.net_read_cnt , n.net_read_bytes , n.net_read_mb , n.rea_avg_pkt_byte , n.net_write_cnt , n.net_write_bytes , n.net_write_mb , n.wrt_avg_pkt_byte , n.net_open_time , n.net_last_read , n.net_last_write , p.isreads , p.iswrites , p.isrewrites , p.isdeletes , p.iscommits , p.isrollbacks , p.seqscans , p.pagreads , p.pagwrites , p.total_sorts , p.dsksorts , p.max_sortdiskspace , t.cpu_time , t.last_run_time from sysmaster:syssesprof p , sysmaster:syssessions s , ( select us_sid , sum(t.cpu_time) , max(dbinfo('utc_to_datetime', last_run_time)) from sysmaster:sysuserthreads u , sysmaster:systcblst t where us_tid = t.tid and us_sid = lSesid group by 1 ) as t (sid, cpu_time, last_run_time) , ( select sid , sum(net_read_cnt) , sum(net_read_bytes) , sum((decode(net_read_bytes, 0, 0, net_read_bytes / 1024/1024))::dec(10, 2) ) , sum((decode(net_read_bytes, 0, 0, net_read_bytes / net_read_cnt))::dec(10, 2) ) , sum(net_write_cnt) , sum(net_write_bytes) , sum((decode(net_write_bytes, 0, 0, net_write_bytes / 1024/1024))::dec(10, 2) ) , sum((decode(net_write_bytes, 0, 0, net_write_bytes / net_write_cnt))::dec(10, 2) ) , max(dbinfo('utc_to_datetime', net_open_time)), max(dbinfo('utc_to_datetime', net_last_read)), max(dbinfo('utc_to_datetime', net_last_write)) from sysmaster:sysnetworkio n where sid = lSesid group by 1 ) as n ( sid, net_read_cnt, net_read_bytes, net_read_mb, rea_avg_pkt_byte, net_write_cnt, net_write_bytes, net_write_mb, wrt_avg_pkt_byte, net_open_time, net_last_read, net_last_write ) where s.sid = p.sid and t.sid = p.sid and n.sid = p.sid and s.sid = lSesid; 2014-09-08 14:58 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > But don't you use SID in your queries? Or even so, the server reads all the > rows in sysnetworkio? > > On Mon, Sep 8, 2014 at 6:52 PM, Cesar Martins < > cesar.inacio.martins@gmail.com> wrote: > > > Hi Fernando, > > > > The mostly important for me into sysnetworkio is get how much I/O occur > > (bytes) . > > With it I able to collect the overhead of network for each application > > (4GL) , identify the application and then start a investigation over it , > > why have high network I/O and what can be improved . > > > > This way I already detected applications which run for 2 hours and > > transfers lots of GBytes with database... and frequently the reason of > this > > is the easy way to program into 4GL writing embedded SQL creating a huge > > overhead for repeated SQLs and the bad behave of the programmer to fetch > > data copying the SQL for an information which the program already get few > > lines before into the code. > > But sometimes is just a bad filter which able to user print a report with > > all data of the database and tons of paper pages... > > > > I save all statistics of all access of our database and I already have 3 > > years of historical. This already help me to identify why a program > becomes > > slow from certain date where I able to see , before X date they > consumption > > of CPU, Disk and Network have a differ behave.... > > > > I've adapted the core of our 4GL system to run procedures into the > database > > to identify it self , saving the name of program and what function into > > global variables of the session at critical points of the code and > > sysdbclose . This way I know exactly which code is running with that > > statistics.... > > > > 2014-09-08 14:35 GMT-03:00 Fernando Nunes <domusonline@gmail.com>: > > > > > This is sysnetworkio: > > > > > > create table sysnetworkio> > > > > > ( > > > > > > net_id int, { Net > > > ID } > > > > > > sid int, { session > > > id } > > > > > > net_netscb int8, { address of > > > netscb } > > > > > > net_client_type int, { client > > > type } > > > > > > net_client_name char(12), { client protocal > > > name } > > > > > > net_read_cnt int8, { number of read > > > operations } > > > > > > net_read_bytes int8, { # of bytes txfr to > > > server } > > > > > > net_write_cnt int8, { number of write > > > operations } > > > > > > net_write_bytes int8, { # of bytes txfr to > > > client } > > > > > > net_open_time int, { time connection was > > > made } > > > > > > net_last_read int, { time of last network > > > read } > > > > > > net_last_write int, { time of last network > > > write } > > > > > > net_state int, { state of network > > > connection } > > > > > > net_options int, { sqlhost > > > options } > > > > > > net_prot_id int, { id for protocal > > > name } > > > > > > net_protocol char(10), { prtocal > > > name } > > > > > > net_server_fd int, { poll server > > > fd } > > > > > > net_poll_thread int { poll thread > > > id } > > > > > > ); > > > > > > I understand the need for this to monitor idle sessions for example. > > > Other than that I think the net_client_name or maybe net_protocol can > be > > > interesting in an open/close context... > > > But what is the purpose of this sysdbopen()/sysdbclose()? And why would > > you > > > need to read rows from it besides the one for your own session? > > > > > > Regards. > > > > > > On Mon, Sep 8, 2014 at 6:05 PM, Marco Greco <marco@4glworks.com> > wrote: > > > > > > > On 08/09/14 15:05, Cesar Martins wrote: > > > > > Hi David , > > > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > > > - The problem occur with exclusive with Power7 processors using > SMT-4 > > > > > > > > > > configuration > > > > > > > > > > What they was explained , this is because of internal architecture > of > > > P7 > > > > > > > > > > in SMT-4 configuration. > > > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > SMT-4 > > > > > > > > > > to SMT-2 > > > > > > > > > > - The ontat -g stk bellow

↪ replying to Art Kagel

Cesar Martins — Tue, 09 Sep 2014 14:37:35 — source: IIUG Forums & Mailing Lists

Hi Art, Fernando , Paul, Art , yes, the proc folding is off... we already get this few years ago. I do not intend to keep this discussion... but I'm very glad about the "rain" of messages saying "get out of smt-4" . I already try convince the support team about this , without success... so... . Trying keep short : We have lot of problem when we start to use this P7 (few years ago) and even a IBM AIX expert become at the company to check our configuration, because the system performance do not reach what the seller told (120% faster than our previus P6 ... I prefer not say my opinion here about this fact) . At that time I've been "forced" to try make this environment perform as was sold... we open PMRs , make a conference call with Informix support, try some configurations, get AIX specialist... and at the end we stuck with this configuration (recommended). A year later of all this IBM publish A Technical White Paper : IBM Informix on POWER7 Best Practices https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W6461741b f7e8_47d7_89fc_a0a9233f3ca9/page/IBM%20Informix%20on%20POWER7 Quoting the page 12 : "Recommendation If you are most concerned about overall throughput for your Informix server, use SMT4, because using SMT4 can more fully utilize the core. While tests showed a 60% increase in throughput, keep in mind that single-thread response time does not scale linearly as more SMT threads are used. If you want to optimize for response time, you can start with SMT4 for the increased throughput, but if you see single-thread response time suffer, move to SMT2." Like your guys, I try run out of SMT , but unfortunately IBM , someway, always tell us to keep at SMT-4 . I do not have autonomy , time and opportunity to try and prove the SMT is "bad" for databases.... so.. there is... our database production running into SMT-4 .. That's all ... 2014-09-08 12:08 GMT-03:00 Art Kagel <art.kagel@gmail.com>: > Another thing to look at is the processor folding when SMT is enabled. The > machine will disable and enable SMT threads dynamically and that is costly. > You need to turn that off for Informix. Whatever SMT level you choose has > to be fixed to perform well. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Sep 8, 2014 at 10:54 AM, Paul Watson <paul@oninit.com> wrote: > > > Off - total throughout when the machine is max'd out is the same but you > > can > > drive the server harder sooner with SMT off. I was told the issue is AIX > > SMT threading/scheduling doesn't play nice with the oninit processes. The > > sysadmins always say it should be on and fight against turning it off and > > will show you graphs showing the CPU are not that busy so it just can't > be > > an OS problem :) But if you can convince them to test it then ..... it > > normally stays off - at least on a dedicated DB server. > > > > Cheers > > Paul > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Fernando Nunes > > > Sent: Monday, September 08, 2014 9:42 AM > > > To: ids@iiug.org > > > Subject: Re: Mutex - guard ? [33724] > > > > > > Off or SMT-2?! > > > > > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > > > > > > > You might want to turn off SMT completely, my testing with 11.7 and > 6.1 > > > > showed greater throughput with SMT off > > > > > > > > Cheers > > > > Paul > > > > > > > > > -----Original Message----- > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of > > > > > Cesar Martins > > > > > Sent: Monday, September 08, 2014 9:05 AM > > > > > To: ids@iiug.org > > > > > Subject: Re: Mutex - guard ? [33720] > > > > > > > > > > Hi David , > > > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > > > - The problem occur with exclusive with Power7 processors using > SMT-4 > > > > > > > > > > configuration > > > > > > > > > > What they was explained , this is because of internal architecture > of > > P7 > > > > > > > > > > in SMT-4 configuration. > > > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > > SMT-4 > > > > > > > > > > to SMT-2 > > > > > > > > > > - The ontat -g stk bellow > > > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > > > stack for thread: 9063619 sqlexec > > > > > base: 0x0700001cdecb7000 > > > > > len: 266240 > > > > > > > > > > pc: 0x000000010003618c > > > > > tos: 0x0700001cdecf21e0 > > > > > state: mutex wait > > > > > > > > > > vp: 49 > > > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > > 0x0000000100045a2c (oninit)mt_lock > > > > > 0x0000000100d72958 (oninit)smi_network_io > > > > > 0x000000010030a0fc (oninit)pstread > > > > > 0x00000001002f1c00 (oninit)pst_rsread > > > > > 0x00000001002fafd8 (oninit)rsread > > > > > 0x00000001004c0c18 (oninit)fmread > > > > > 0x0000000100668588 (oninit)readseq_single > > > > > 0x0000000100668cd0 (oninit)gettupl > > > > > 0x000000010066c488 (oninit)scan_next > > > > > 0x0000000100d1182c (oninit)next_row > > > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > > > 0x0000000100d13f9c (oninit)group_open > > > > > 0x000000010065f428 (oninit)filltemp > > > > > 0x000000010066cd40 (oninit)scan_open > > > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > > 0x000000010040be94 (oninit)prepselect > > > > > 0x000000010067ad80 (oninit)subqprep > > > > > 0x000000010067b2f4 (oninit)exsubq > > > > > 0x00000001005953d4 (oninit)geval > > > > > 0x000000010075f308 (oninit)eval_projection_list > > > > > 0x00000001007646c8 (oninit)dodmlrow > > > > > 0x0000000100767254 (oninit)dodelupd > > > > > 0x000000010041d030 (oninit)aud_dodelupd > > > > > 0x000000010042593c (oninit)excommand > > > > > 0x000000010023d4c0 (oninit)ip_evalsql > > > > > 0x0000000100248f78 (oninit)runproc > > > > > 0x0000000100243c7c (oninit)udrlm_spl_execute > > > > > 0x0000000100ce98e4 (oninit)udrlm_exec_routine > > > > > 0x0000000100581ea4 (oninit)udr_execute > > > > > 0x00000001005bb768 (oninit)exroutine > > > > > 0x00000001004240d0 (oninit)execproc > > > > > 0

↪ replying to Cesar Martins

Paul Watson — Tue, 09 Sep 2014 15:39:30 — source: IIUG Forums & Mailing Lists

Get Informix Support AND AIX support on the same call :) > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Cesar Martins > Sent: Tuesday, September 09, 2014 9:38 AM > To: ids@iiug.org > Subject: Re: Mutex - guard ? [33746] > > Hi Art, Fernando , Paul, > > Art , yes, the proc folding is off... we already get this few years ago. > > I do not intend to keep this discussion... but I'm very glad about the > "rain" of messages saying "get out of smt-4" . > I already try convince the support team about this , without success... > so... . > > Trying keep short : > We have lot of problem when we start to use this P7 (few years ago) and > even a IBM AIX expert become at the company to check our configuration, > because the system performance do not reach what the seller told (120% > faster than our previus P6 ... I prefer not say my opinion here about this > fact) . > At that time I've been "forced" to try make this environment perform as was > sold... we open PMRs , make a conference call with Informix support, try > some configurations, get AIX specialist... and at the end we stuck with > this configuration (recommended). > > A year later of all this IBM publish A Technical White Paper : IBM > Informix on POWER7 Best Practices > > https://www.ibm.com/developerworks/community/wikis/home?lang=en#! > /wiki/W6461741bf7e8_47d7_89fc_a0a9233f3ca9/page/IBM%20Informix%20o > n%20POWER7 > > Quoting the page 12 : > > "Recommendation > If you are most concerned about overall throughput for your Informix > server, use SMT4, > because using SMT4 can more fully utilize the core. While tests showed a > 60% increase in > throughput, keep in mind that single-thread response time does not scale > linearly as more SMT > threads are used. If you want to optimize for response time, you can start > with SMT4 for the > increased throughput, but if you see single-thread response time suffer, > move to SMT2." > > Like your guys, I try run out of SMT , but unfortunately IBM , someway, > always tell us to keep at SMT-4 . > I do not have autonomy , time and opportunity to try and prove the SMT is > "bad" for databases.... so.. there is... our database production running > into SMT-4 .. > > That's all ... > > 2014-09-08 12:08 GMT-03:00 Art Kagel <art.kagel@gmail.com>: > > > Another thing to look at is the processor folding when SMT is enabled. The > > machine will disable and enable SMT threads dynamically and that is costly. > > You need to turn that off for Informix. Whatever SMT level you choose has > > to be fixed to perform well. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Mon, Sep 8, 2014 at 10:54 AM, Paul Watson <paul@oninit.com> wrote: > > > > > Off - total throughout when the machine is max'd out is the same but you > > > can > > > drive the server harder sooner with SMT off. I was told the issue is AIX > > > SMT threading/scheduling doesn't play nice with the oninit processes. > The > > > sysadmins always say it should be on and fight against turning it off and > > > will show you graphs showing the CPU are not that busy so it just can't > > be > > > an OS problem :) But if you can convince them to test it then ..... it > > > normally stays off - at least on a dedicated DB server. > > > > > > Cheers > > > Paul > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > > > > Fernando Nunes > > > > Sent: Monday, September 08, 2014 9:42 AM > > > > To: ids@iiug.org > > > > Subject: Re: Mutex - guard ? [33724] > > > > > > > > Off or SMT-2?! > > > > > > > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> > wrote: > > > > > > > > > You might want to turn off SMT completely, my testing with 11.7 and > > 6.1 > > > > > showed greater throughput with SMT off > > > > > > > > > > Cheers > > > > > Paul > > > > > > > > > > > -----Original Message----- > > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf > > > Of > > > > > > Cesar Martins > > > > > > Sent: Monday, September 08, 2014 9:05 AM > > > > > > To: ids@iiug.org > > > > > > Subject: Re: Mutex - guard ? [33720] > > > > > > > > > > > > Hi David , > > > > > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > > > > > - The problem occur with exclusive with Power7 processors using > > SMT-4 > > > > > > > > > > > > configuration > > > > > > > > > > > > What they was explained , this is because of internal architecture > > of > > > P7 > > > > > > > > > > > > in SMT-4 configuration. > > > > > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > > > SMT-4 > > > > > > > > > > > > to SMT-2 > > > > > > > > > > > > - The ontat -g stk bellow > > > > > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > > > > > stack for thread: 9063619 sqlexec > > > > > > base: 0x0700001cdecb7000 > > > > > > len: 266240 > > > > > > > > > > > > pc: 0x000000010003618c > > > > > > tos: 0x0700001cdecf21e0 > > > > > > state: mutex wait > > > > > > > > > > > > vp: 49 > > > > > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > > > 0x0000000100045a2c (oninit)mt_lock > > > > > > 0x0000000100d72958 (oninit)smi_network_io > > > > > > 0x000000010030a0fc (oninit)pstread > > > > > > 0x00000001002f1c00 (oninit)pst_rsread > > > > > > 0x00000001002fafd8 (oninit)rsread > > > > > > 0x00000001004c0c18 (oninit)fmread > > > > > > 0x0000000100668588 (oninit)readseq_single > > > > > > 0x0000000100668cd0 (oninit)gettupl > > > > > > 0x000000010066c488 (oninit)scan_next > > > > > > 0x0000000100d1182c (oninit)next_row > > > > > > 0x0000000100d1151c (oninit)get_first_row_from_producer > > > > > > 0x0000000100d10498 (oninit)hash_process_all_groups > > > > > > 0x0000000100d13f9c (oninit)group_open > > > > > > 0x000000010065f428 (oninit)filltemp > > > > > > 0x000000010066cd40 (oninit)scan_open > > > > > > 0x000000010066dc58 (oninit)materialize_viewtmp > > > > > > 0x000000010066dcdc (oninit)materialize_viewtmp > > > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > > > 0x000000010066dcc8 (oninit)materialize_viewtmp > > > > > > 0x000000010040be94 (oninit)prepselect > > > > > > 0x000000010067ad80 (oninit)subqprep > > > > > > 0x0

↪ replying to Cesar Martins

Fernando Nunes — Tue, 09 Sep 2014 16:28:14 — source: IIUG Forums & Mailing Lists

From what I recall the PMR recommendation was to change to SMT-2... I have no direct experience with performance issues on P7 with SMT-4. As with all "smt*", they're SMTs and not cores for a reason. That's why I'm not surprised with SMT-2 recommendation, but I'm a bit surprised with the "no SMT" recommendation. Something that would require analisys is the LPAR configurations versus the Informix (CPU VPs) configuration. Very recently I had contact with a customer issue (with Oracle in that case, but that's really not very relevant) where the support verdict was: You're being slowed down by huge context switch times. This was a VM environment, and the ratio of processes to virtual CPUs and the ratio of virtual CPUs to physical CPUs was very relevant. So, in short... SMT of whatever kind has to be taken as best value for money, but cannot be confused with cores. Virtualization brings a whole (and big) degree of complexity into this. Whenever we "slice" a physical CPU into virtual CPUs we're doing a sort of time sharing.... It's roughly the same as working with a lot slower CPU. And then we have the process context switching... all this together may translate that a single process runs less time into the CPU than before. On Tue, Sep 9, 2014 at 3:37 PM, Cesar Martins < cesar.inacio.martins@gmail.com> wrote: > Hi Art, Fernando , Paul, > > Art , yes, the proc folding is off... we already get this few years ago. > > I do not intend to keep this discussion... but I'm very glad about the > "rain" of messages saying "get out of smt-4" . > I already try convince the support team about this , without success... > so... . > > Trying keep short : > We have lot of problem when we start to use this P7 (few years ago) and > even a IBM AIX expert become at the company to check our configuration, > because the system performance do not reach what the seller told (120% > faster than our previus P6 ... I prefer not say my opinion here about this > fact) . > At that time I've been "forced" to try make this environment perform as was > sold... we open PMRs , make a conference call with Informix support, try > some configurations, get AIX specialist... and at the end we stuck with > this configuration (recommended). > > A year later of all this IBM publish A Technical White Paper : IBM > Informix on POWER7 Best Practices > > > https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W6461741b f7e8_47d7_89fc_a0a9233f3ca9/page/IBM%20Informix%20on%20POWER7 > > Quoting the page 12 : > > "Recommendation > If you are most concerned about overall throughput for your Informix > server, use SMT4, > because using SMT4 can more fully utilize the core. While tests showed a > 60% increase in > throughput, keep in mind that single-thread response time does not scale > linearly as more SMT > threads are used. If you want to optimize for response time, you can start > with SMT4 for the > increased throughput, but if you see single-thread response time suffer, > move to SMT2." > > Like your guys, I try run out of SMT , but unfortunately IBM , someway, > always tell us to keep at SMT-4 . > I do not have autonomy , time and opportunity to try and prove the SMT is > "bad" for databases.... so.. there is... our database production running > into SMT-4 .. > > That's all ... > > 2014-09-08 12:08 GMT-03:00 Art Kagel <art.kagel@gmail.com>: > > > Another thing to look at is the processor folding when SMT is enabled. > The > > machine will disable and enable SMT threads dynamically and that is > costly. > > You need to turn that off for Informix. Whatever SMT level you choose has > > to be fixed to perform well. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Mon, Sep 8, 2014 at 10:54 AM, Paul Watson <paul@oninit.com> wrote: > > > > > Off - total throughout when the machine is max'd out is the same but > you > > > can > > > drive the server harder sooner with SMT off. I was told the issue is > AIX > > > SMT threading/scheduling doesn't play nice with the oninit processes. > The > > > sysadmins always say it should be on and fight against turning it off > and > > > will show you graphs showing the CPU are not that busy so it just can't > > be > > > an OS problem :) But if you can convince them to test it then ..... it > > > normally stays off - at least on a dedicated DB server. > > > > > > Cheers > > > Paul > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > > > > Fernando Nunes > > > > Sent: Monday, September 08, 2014 9:42 AM > > > > To: ids@iiug.org > > > > Subject: Re: Mutex - guard ? [33724] > > > > > > > > Off or SMT-2?! > > > > > > > > On Mon, Sep 8, 2014 at 3:13 PM, Paul Watson <paul@oninit.com> wrote: > > > > > > > > > You might want to turn off SMT completely, my testing with 11.7 and > > 6.1 > > > > > showed greater throughput with SMT off > > > > > > > > > > Cheers > > > > > Paul > > > > > > > > > > > -----Original Message----- > > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf > > > Of > > > > > > Cesar Martins > > > > > > Sent: Monday, September 08, 2014 9:05 AM > > > > > > To: ids@iiug.org > > > > > > Subject: Re: Mutex - guard ? [33720] > > > > > > > > > > > > Hi David , > > > > > > > > > > > > I've checked my history of emails and this is what I have : > > > > > > > > > > > > - There is no FIX , only a suggestion for workaround. > > > > > > > > > > > > - The problem occur with exclusive with Power7 processors using > > SMT-4 > > > > > > > > > > > > configuration > > > > > > > > > > > > What they was explained , this is because of internal > architecture > > of > > > P7 > > > > > > > > > > > > in SMT-4 configuration. > > > > > > > > > > > > - workaround suggested (from IBM Support) , change the LPAR proc > > > > SMT-4 > > > > > > > > > > > > to SMT-2 > > > > > > > > > > > > - The ontat -g stk bellow > > > > > > > > > > > > - This is into version 11.50 FC9X6 , AIX 6.1 > > > > > > > > > > > > stack for thread: 9063619 sqlexec > > > > > > base: 0x0700001cdecb7000 > > > > > > len: 266240 > > > > > > > > > > > > pc: 0x000000010003618c > > > > > > tos: 0x0700001cdecf21e0 > > > > > > state: mutex wait > > > > > > > > > > > > vp: 49 > > > > > > > > > > > > 0x000000010003618c (oninit)yield_processor_mvp > > > > > > 0x0000000100038b6c (oninit)mt_lock_wait > > > > > > 0x0000000100045a2c (oninit)mt_lock > > > > > > 0x00000

Related threads

Glossary

4GL (Informix-4GL)