lock ovflow
Mark saw an Informix test instance dynamically allocating huge numbers of lock structures (1,000,000 locks at a time, ~99 allocations in 4 hours) and wanted a quick way to identify the culprit. Suggestions: onstat -u to spot sessions holding many locks, onstat -k, a query on sysmaster:syslocks grouped by owner, and onstat -g tpf (though Art noted it shows cumulative lock requests, not current). Mark confirmed onstat -u (plus sysmaster sysessions/sysesprof and OAT/SQLTRACE) gave him the session. Another poster shared an ALARMPROGRAM script that traps the "dynamically allocated locks" message and captures onstat -u output automatically.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Seeing a test engine with a high number of locks structures being allocated (99 lock structures of 1M each allocated over about a 4 hour period). I can think of a few approaches to figuring out WHO is causing the allocations. Any ideas though of a simple, fast way? 06/15/15 19:09:18 dynamically allocated 1000000 locks Ideas? Thanks - Mark Scranton The Mark Scranton Group mark@markscranton.com
The engine should only allocate locks when all are being used.
Simple and fast would be to run onstat -u and find who/what is using a really
large number of locks.
George.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK
SCRANTON
Sent: Thursday, June 18, 2015 9:27 AM
To: ids@iiug.org
Subject: lock ovflow [35281]
Seeing a test engine with a high number of locks structures being allocated
(99 lock structures of 1M each allocated over about a 4 hour period). I can
think of a few approaches to figuring out WHO is causing the allocations. Any
ideas though of a simple, fast way?
06/15/15 19:09:18 dynamically allocated 1000000 locks
Ideas?
Thanks -
Mark Scranton
The Mark Scranton Group
mark@markscranton.com
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
This electronic message transmission contains information from the Company
that may be proprietary, confidential and/or privileged. The information is
intended only for the use of the individual(s) or entity named above. If you
are not the intended recipient, be aware that any disclosure, copying or
distribution or use of the contents of this information is prohibited. If you
have received this electronic transmission in error, please notify the sender
immediately by replying to the address listed in the "From:" field.
How about look at onstat -g tpf and see the
thread which has requested a high number
of locks.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 06/18/2015 08:26:52 AM:
> From: "MARK SCRANTON" <mark@markscranton.com>
> To: ids@iiug.org
> Date: 06/18/2015 08:27 AM
> Subject: lock ovflow [35281]
> Sent by: ids-bounces@iiug.org
>
> Seeing a test engine with a high number of locks structures being
allocated
> (99 lock structures of 1M each allocated over about a 4 hour period). I
can
> think of a few approaches to figuring out WHO is causing the allocations.
Any
> ideas though of a simple, fast way?
>
> 06/15/15 19:09:18 dynamically allocated 1000000 locks
>
> Ideas?
>
> Thanks -
> Mark Scranton
> The Mark Scranton Group
> mark@markscranton.com
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
onstat -k and look for session(s) holding lots of locks
- or -
select owner, count(*) num_locks
from sysmaster:syslocks
group by 1
having count(*) > 100
order by 2 desc, 1;
Art
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
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 Thu, Jun 18, 2015 at 10:26 AM, MARK SCRANTON <mark@markscranton.com>
wrote:
> Seeing a test engine with a high number of locks structures being allocated
> (99 lock structures of 1M each allocated over about a 4 hour period). I can
> think of a few approaches to figuring out WHO is causing the allocations.
> Any
> ideas though of a simple, fast way?
>
> 06/15/15 19:09:18 dynamically allocated 1000000 locks
>
> Ideas?
>
> Thanks -
> Mark Scranton
> The Mark Scranton Group
> mark@markscranton.com
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f64277ae996b90518cd3e30
Thanks for the inputs everyone. Slightly embarrassed - as George pointed out
... onstat -u has the info. Can trace back to session, etc.
I have been using OAT for watching a number of issues like this lately.
Refining SQLTRACE as well for catching offenders.
Thanks -
Mark
Just to clarify:
John's suggestion (onstat -g tpf) may or may not do what Mark needs since
it shows total lock requests not current number, so a long running
thread/session that grabs a few locks many times will be included.
On the other hand, it will catch sessions that still exist that have
released the 4million locks they once held.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
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 Thu, Jun 18, 2015 at 10:59 AM, John Miller iii <miller3@us.ibm.com>
wrote:
> How about look at onstat -g tpf and see the
> thread which has requested a high number
> of locks.
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 06/18/2015 08:26:52 AM:
>
> > From: "MARK SCRANTON" <mark@markscranton.com>
> > To: ids@iiug.org
> > Date: 06/18/2015 08:27 AM
> > Subject: lock ovflow [35281]
> > Sent by: ids-bounces@iiug.org
> >
> > Seeing a test engine with a high number of locks structures being
> allocated
> > (99 lock structures of 1M each allocated over about a 4 hour period). I
> can
> > think of a few approaches to figuring out WHO is causing the allocations.
> Any
> > ideas though of a simple, fast way?
> >
> > 06/15/15 19:09:18 dynamically allocated 1000000 locks
> >
> > Ideas?
> >
> > Thanks -
> > Mark Scranton
> > The Mark Scranton Group
> > mark@markscranton.com
> >
> >
> >
>
> ***************************************************************************=
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bea35c07926090518cd53f0
sysmaster:sysessions and sysmaster:sysesprof has a wealth of info. I know many of us have used them for scripts for years. How soon we forget. Mark
I've trapped for it in my ALARM script before, and fire off a sub-shell to
grab 'onstat -u' info. Maybe sysmaster should be a faster, internal way to get
info, hopefully the process name too. The "subj_txt" in the snippet below is
for texts and/or emails for warnings. Sorry about pasting in my email is
squeezing out all spacing!
AL_SEVER=$1
AL_CLASS=$2
AL_MESG="$3"
AL_ADD_TEXT="$4"
AL_XTRA_FILE="$5"
case $AL_CLASS in
6 ) # 5, 6 internal subsyst failure
subj_txt="SUBSYS FAIL"
;;
15 ) # 3,15 for HDR fail
subj_txt="HDR FAIL"
;;
20 ) # 3,20 for logs full
subj_txt="LOGS FULL "
;;
21 ) # 3,21 for locks
subj_txt="RESOURCES"
;;
22 ) # 3,22 for LTX
subj_txt="LONG TX"
;;
30|31|32|33|34|35|37|38|39 ) # Supposed to be for ER alarms
subj_txt="ER FAIL "
;;
* ) # (5,6)
subj_txt=" ($AL_SEVER,$AL_CLASS) "
;;
esac
Or checking the text of serverity=1 messages:
# If severity=1, check text for important stuff
if [ $AL_SEVER -eq 1 ]
then
$ECHO "\\
******* ALARM MESSAGE ***** \\
" > $MAIL_MESG
msg_str=${AL_ADD_TEXT:0:25}
case "$msg_str" in
"CDR connection to server " )
subj_txt="sees CDR DOWN"
;;
"CDR: Re-connected to serv" )
subj_txt="CDR BACK UP"
;;
"DR: Primary server operat" )
subj_txt="HDR PRI BACK UP"
;;
"DR: Secondary server oper" )
subj_txt="HDR SEC BACK UP"
;;
# dynamically allocated 100000 locks
"dynamically allocated 100" )
subj_txt="Added 100K locks"
info_file=$LOG_DIR/alarm.$run_min.$AL_SEVER.$AL_CLASS; export info_file
$LOG_DIR/alarm_info.sh $info_file LOCKS
;;
* ) # (5,6)
subj_txt=" ($AL_SEVER,$AL_CLASS) "
exit 0
;;
esac
Bob
----- Original Message -----
From: "MARK SCRANTON" <mark@markscranton.com>
To: ids@iiug.org
Sent: Thursday, June 18, 2015 12:33:51 PM
Subject: Re: lock ovflow [35287]
sysmaster:sysessions and sysmaster:sysesprof has a wealth of info. I know many
of us have used them for scripts for years. How soon we forget.
Mark
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.