Informix

Expand all | Collapse all

idle session consuming lots of cpu time, but doing nothing apparently

  • 1.  idle session consuming lots of cpu time, but doing nothing apparently

    Posted Thu September 16, 2021 01:14 PM

    Hi Folks

     

    I am trying to identify any global perf issue on a system where users suddenly complain about a drastical loss of performance "with apparently NO CHANGE done on the system".

    This is about a series of  heavy load tasks that, according to customer's 'feeling', took around 4 hours to complete 'before' and now 17 hours.

    I don't have any solid figures on those response times before but have to rely on 'impressions'

    Obviously, no one changed anything in the system's configuration, that I can believe, or not...

     

    Analyzing a full day of activity with bunches on onstat catches, I can see that I have an idle session starting at 9:20 AM and finishes by 12:30 PM

    that (according to sqltrace running during the whole period): does NOTHING, i.e no query at all caught by sqltrace.

     

    Nevertheless, onstat -g cpu for that session's sqlexec thread reports 258 secs of cpu time.

     

    Is this expected ???

    tracking onstat -g ath for that thread every 5 minutes says cond wait norm, which is expected, but it is normal that the thread consumes that much time ?

    this is ifmx_14.10.FC2

    red hat 6

    VMWARE ESX in a government cloud ( not sure at all about how much dedicated are the system resources, for me all shared .... )

     

    Any clue ?

     

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    KandooERP Founder and CTO
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:
    eric.vercelletto@begooden-it.com
    www :
    http://www.vercelletto.com
    www  https://kandooerp.org

     

     



  • 2.  RE: idle session consuming lots of cpu time, but doing nothing apparently

    Posted Fri September 17, 2021 01:58 AM
    Hi Éric
    Have you been able to identify when during the life of this session the CPU seems to be consumed based on  onstat -g cpu output ?
    Also did you check the overall system performance statistics for instance with nmon ? Maybe other processes on the server are using CPU or I/o resources or you could have memory issues.
    Regards
    Fabrice


    ------------------------------
    Fabrice PLATEL
    ------------------------------



  • 3.  RE: idle session consuming lots of cpu time, but doing nothing apparently

    Posted Fri September 17, 2021 02:06 AM
    Is "Estimate Compression" thread activated ?
    I noticed a similar cpu consumption when the sysadmin task "Estimate Compression" was scanning all rows in all tables.
    In some versions this taks is enabled by default, even if you have a Workgroup license.

    Mit freundlichen Grüßen / Kind regards


    Gerd Kaluzinski

    DataOps Application Consultant
    IBM Cloud and Cognitive Software
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland
    Mies-van-der-Rohe-Straße 6
    80807 München
    Phone: +49-175-228 1983
    E-Mail: gerd.kaluzinski@de.ibm.com
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland GmbH

    Vorsitzender des Aufsichtsrats: Sebastian Krause
    Geschäftsführung: Gregor Pillen (Vorsitzender), Agnes Heftberger, Gabriele Schwarenthorer, Christian Noll, Nicole Reimer, Frank Theisen
    Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562 / WEEE-Reg.-Nr. DE 99369940







  • 4.  RE: idle session consuming lots of cpu time, but doing nothing apparently

    Posted Fri September 17, 2021 04:11 AM
    What have you got for NETTYPE settings and what protocol is this connection?

    ------------------------------
    Jon Ritson
    ------------------------------



  • 5.  RE: idle session consuming lots of cpu time, but doing nothing apparently

    Posted Mon September 20, 2021 05:12 AM
    Hi Eric,

    did you exclude memory swapping/paging on your system?

    Best Regards
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021
    ------------------------------



  • 6.  RE: idle session consuming lots of cpu time, but doing nothing apparently

    Posted Fri September 17, 2021 04:19 AM

    Hi JJ

     

    Those connections are tcp running on NET nettype

     

    Is there any remote possibility that sqltrace "forgets" catching all queries during close to 4 hours for one or 2 sessions among 40 ?

    I catch millions of queries during that lapse, but none for this session...

     

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    KandooERP Founder and CTO
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:
    eric.vercelletto@begooden-it.com
    www :
    http://www.vercelletto.com
    www  https://kandooerp.org

     

     






  • 7.  RE: idle session consuming lots of cpu time, but doing nothing apparently

    Posted Mon September 20, 2021 05:45 AM
    Hi Eric,
    I had the same problem two months ago with version 12.10FC14 in a Solaris virtual environment. I was able to find a multi sessions with a sql query that has the wait status, these sql used PDQ, and looked like they were waiting for each other because the session had not received a flag. I asked the developers to either lower the PDQ or stop using the PDQ in a sql, and then we got better performance. Note that the PDQ in the server is 40 and sql-PDQ = 5. onstat -g mgm shows which sessions use PDQ. It got better performance but the problem remains, when customer ran "update statistics medium force" against some table we got even better performance, but the day after when an auto stat was run during the night, so got worse performance, there was a defect on FC14 related to statistics so we upgraded to FC15, but it did not help, last week I disabled "AUTO_STAT_MODE 0" even though it is part of "AUTO_TUNE", and aus which is run via sysadmin table ph_task id (44,45), and we have a lot better performance and we run a manual "update statistics" have downloaded Arts dostats and will try to compile it in Solaris (hate Solaris :)). in addition to that, i have found some tables that have a large record size that do not have indexes that show high page readings. I found later the read and write for "cached" "BUFFERPOOL for 4K" is around 80%. I have not changed yet, but I am considering doubling the number of buffers. by the way it is rare for a system to be slow by itself, something must have changed :). hope this can be helpful for you, good luck. // Layth

    ------------------------------
    Layth Yousif
    ------------------------------