Hi all, in the past few days we've encountered a situation where we see Informix run high CPU across all assigned CPU cores (16) for extended periods of time (up to 2 hours).  When we run TOP it shows all the oninit processes running at ~97% while user sessions etc. are only running with 1-2%.  We've tried to identify which process or system activities might be causing this, but nothing appears to be showing consistently as a long running process or activity.   We've run onstat -g act -r 2 but can't identify any threads that are running consistently.  We're struggling to see how a user processes could affect ALL the CPUs so wondering if there is a more fundamental issues with tables, extents or indexes?  or something else?  Any suggestions for additional commands or tools we could use to investigate the high CPU or tools we can use assess health of the DB?  It's the first time these issues have occurred for us, but happened twice now in 3 days.  We're on 12.10 FC13.  

Any suggestions or help appreciated.

Kind regards, Mark

------------------------------
Mark Clayton
------------------------------

These can be tough, but you have to start somewhere.

First thing - check to see if the high usage oninit processes are CPU VPs and not some other class of VP.  Run onstat -g glo and match the PID there with the PIDs shown in "top".

Are you using any PDQ?  Have any procedures been compiled with PDQPRIORITY set?

You can use a query like this against sysmaster to see what sessions have consumed the most CPU:

SELECT
s.sid ,
s.username ,
s.uid ,
s.pid ,
s.hostname ,
t.tid,
t.name,
t.statedesc ,
t.statedetail,
t.cpu_time
FROM syssessions s, systcblst t, sysrstcb r
WHERE t.tid = r.tid AND s.sid = r.sid
order by t.cpu_time desc;

Running this twice, say 60 seconds apart, and comparing the results may help determine what has used the most CPU in that interval.

Does the timing of these slowdowns coincide with an archive?

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------

Hi Mike, thanks for the reply and advice.  We modified that SQL slightly to include the SQL statement and managed to identify a particular application process and table that the application was hanging on - we could see users across multiple CPUs all trying to execute the same SQL.  We did a stats rebuild on the table being referenced and it cleared and hung statements.. appears the table had a number of extents, so an out of hours defrag has been done on two related tables as well as a high stats rebuild.  We'll find out once we hit heavier user load on Monday whether we've got the right thing.  Appreciate your advice as it got us pointed in the right direction.  many thanks. Mark

------------------------------
Mark Clayton
------------------------------

Original Message:
Sent: Thu November 21, 2024 03:39 PM
From: Mike Walker
Subject: High CPU across all Informix processes

These can be tough, but you have to start somewhere.

First thing - check to see if the high usage oninit processes are CPU VPs and not some other class of VP.  Run onstat -g glo and match the PID there with the PIDs shown in "top".

Are you using any PDQ?  Have any procedures been compiled with PDQPRIORITY set?

You can use a query like this against sysmaster to see what sessions have consumed the most CPU:

SELECT
s.sid ,
s.username ,
s.uid ,
s.pid ,
s.hostname ,
t.tid,
t.name,
t.statedesc ,
t.statedetail,
t.cpu_time
FROM syssessions s, systcblst t, sysrstcb r
WHERE t.tid = r.tid AND s.sid = r.sid
order by t.cpu_time desc;

Running this twice, say 60 seconds apart, and comparing the results may help determine what has used the most CPU in that interval.

Does the timing of these slowdowns coincide with an archive?

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------

Mark:

Check into the btree cleaner threads (onstat -C)

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: 11/21/2024 3:41:00 PM
From: Art Kagel
Subject: RE: High CPU across all Informix processes

Mark:

Check into the btree cleaner threads (onstat -C)

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Thanks Art.. appreciate the advice, as always, but I think we got it as per above.  Many thanks!

------------------------------
Mark Clayton
------------------------------

Original Message:
Sent: Thu November 21, 2024 03:41 PM
From: Art Kagel
Subject: High CPU across all Informix processes

Mark:

Check into the btree cleaner threads (onstat -C)

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

thanks too, Marcus for your reply and suggestions. Much appreciated.  Cheers. Mark

------------------------------
Mark Clayton
------------------------------

Original Message:
Sent: Thu November 21, 2024 03:43 PM
From: Marcus Haarmann
Subject: High CPU across all Informix processes

Hi, we have seen a comparable behaviour from btree cleaner. 

Try to stop the cleaner process, not sure if this was supported in 12.10 with onmode.

Marcus

Gesendet von Outlook für Android

Just one guess: this might be a case of a specific, yet very common SQL suddenly misbehaving, so not a specific session or thread is causing this, but rather pretty much everyone is contributing.

In any case, "onstat -g ses Active", repeated a couple of times when you're seeing the problem, might be of help (you're lucky since it's 12.10.xC13 that introduced this option): 

• it is like "onstat -g ses all", but limited to only those sessions that really are trying to do, or doing, work in this moment
• it includes their Current SQL statement - which is what you'd be interested in most, esp. if certain (types of) statements occur frequently
• with that capital 'A' (in Active), it would also include a snapshot of each session's thread stacks - which typically won't be too accurate if the stack is in "running" state, but e.g. the "ready" ones might provide a clue too (maybe mostly to tech support)

Note that this "active", or "Active", is a superset of a number of other more specific states (s. "onstat --" for those) you might want to play with.

All this, while likely not immediately providing root cause and solution, might help guiding you in the right direction.

HTH,
 Andreas

------------------------------
Andreas Legner
Dev Engineer
HCL Software
------------------------------

Hi Andreas.  Thanks for the reply and suggestion.  We'll try that command out and see how that looks on our system - appreciate the help.  Cheers!

mark

------------------------------
Mark Clayton
------------------------------

Original Message:
Sent: Fri November 22, 2024 06:43 AM
From: Andreas Legner
Subject: High CPU across all Informix processes

Just one guess: this might be a case of a specific, yet very common SQL suddenly misbehaving, so not a specific session or thread is causing this, but rather pretty much everyone is contributing.

In any case, "onstat -g ses Active", repeated a couple of times when you're seeing the problem, might be of help (you're lucky since it's 12.10.xC13 that introduced this option): 

• it is like "onstat -g ses all", but limited to only those sessions that really are trying to do, or doing, work in this moment
• it includes their Current SQL statement - which is what you'd be interested in most, esp. if certain (types of) statements occur frequently
• with that capital 'A' (in Active), it would also include a snapshot of each session's thread stacks - which typically won't be too accurate if the stack is in "running" state, but e.g. the "ready" ones might provide a clue too (maybe mostly to tech support)

Note that this "active", or "Active", is a superset of a number of other more specific states (s. "onstat --" for those) you might want to play with.

All this, while likely not immediately providing root cause and solution, might help guiding you in the right direction.

HTH,
 Andreas

------------------------------
Andreas Legner
Dev Engineer
HCL Software
------------------------------

Hi,

onstat -g cpu https://www.ibm.com/docs/en/informix-servers/14.10?topic=options-onstat-g-cpu-print-runtime-statistics

Then use sysmaster query to tie that back to sessions if possible.

That will cover non-user session threads as well.

Regards,
David.

------------------------------
David Williams
------------------------------

Original Message:
Sent: Sat November 23, 2024 02:44 AM
From: Mark Clayton
Subject: High CPU across all Informix processes

Hi Andreas.  Thanks for the reply and suggestion.  We'll try that command out and see how that looks on our system - appreciate the help.  Cheers!

mark

------------------------------
Mark Clayton
------------------------------


Original Message:
Sent: Fri November 22, 2024 06:43 AM
From: Andreas Legner
Subject: High CPU across all Informix processes

Just one guess: this might be a case of a specific, yet very common SQL suddenly misbehaving, so not a specific session or thread is causing this, but rather pretty much everyone is contributing.

In any case, "onstat -g ses Active", repeated a couple of times when you're seeing the problem, might be of help (you're lucky since it's 12.10.xC13 that introduced this option): 

• it is like "onstat -g ses all", but limited to only those sessions that really are trying to do, or doing, work in this moment
• it includes their Current SQL statement - which is what you'd be interested in most, esp. if certain (types of) statements occur frequently
• with that capital 'A' (in Active), it would also include a snapshot of each session's thread stacks - which typically won't be too accurate if the stack is in "running" state, but e.g. the "ready" ones might provide a clue too (maybe mostly to tech support)

Note that this "active", or "Active", is a superset of a number of other more specific states (s. "onstat --" for those) you might want to play with.

All this, while likely not immediately providing root cause and solution, might help guiding you in the right direction.

HTH,
 Andreas

------------------------------
Andreas Legner
Dev Engineer
HCL Software

Thanks all for your suggestions and advice. 

we used the below query which gave us visibility of sql being processed 

SELECT s.sid, s.username, s.uid, s.pid, s.hostname, t.tid,t.name, t.statedesc, t.statedetail, q.sqs_statement, t.cpu_time
FROM syssessions s, systcblst t, sysrstcb r, syssqlstat q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.sqs_sessionid
ORDER BY t.cpu_time desc;

From this we determined that the same SQL was present across almost all Informix CPUs at any point in time - this SQL was related to an application function for stock descriptions and it is frequently called by several hundred users throughout the day.  With so many users calling this function it was then spread across almost all Informix assigned CPUs.   We then determined a table that was supposed to be self clearing, but had it's record size grow in excess of 1M rows (possibly due to growing abandoned sessions before this incident).  We think the query on this table was taking too long and hence becoming a bottleneck with a backlog of transactions waiting - hence high CPU across all CPUs.  Truncating the table and clearing out abandoned sessions helped bring it back under control.  We now need to discuss with the application developers and put in place some mitigations to avoid for future.

just sharing the outcome of this incident in case its helpful for someone else in future.

Again, many thanks for the advise and counsel.

Cheers, Mark

------------------------------
Mark Clayton
------------------------------

What you describe is something that can happen when a frequently-called SQL statement becomes even moderately expensive.

onstat -g ses active

is a quicker and easier to remember way of spotting this type of issue (look for same SQL running across multiple active sessions).

If your engine doesn't have the 'active' filter which 12.10 might not, you can do

onstat -g ses 0

and look for sessions with state "running-" - this does the same thing.

Ben.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: Sun December 08, 2024 07:53 PM
From: Mark Clayton
Subject: High CPU across all Informix processes

Thanks all for your suggestions and advice. 

we used the below query which gave us visibility of sql being processed 

SELECT s.sid, s.username, s.uid, s.pid, s.hostname, t.tid,t.name, t.statedesc, t.statedetail, q.sqs_statement, t.cpu_time
FROM syssessions s, systcblst t, sysrstcb r, syssqlstat q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid = q.sqs_sessionid
ORDER BY t.cpu_time desc;

From this we determined that the same SQL was present across almost all Informix CPUs at any point in time - this SQL was related to an application function for stock descriptions and it is frequently called by several hundred users throughout the day.  With so many users calling this function it was then spread across almost all Informix assigned CPUs.   We then determined a table that was supposed to be self clearing, but had it's record size grow in excess of 1M rows (possibly due to growing abandoned sessions before this incident).  We think the query on this table was taking too long and hence becoming a bottleneck with a backlog of transactions waiting - hence high CPU across all CPUs.  Truncating the table and clearing out abandoned sessions helped bring it back under control.  We now need to discuss with the application developers and put in place some mitigations to avoid for future.

just sharing the outcome of this incident in case its helpful for someone else in future.

Again, many thanks for the advise and counsel.

Cheers, Mark

------------------------------
Mark Clayton
------------------------------