 |
IDS Forum
Re: Mutex - guard ?
Posted By: Cesar Martins
Date: Monday, 8 September 2014, at 2:13 p.m.
In Response To: Re: Mutex - guard ? (Fernando Nunes)
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
> > > > >
> > > > > - 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
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --047d7bdc9e282316130502917c95
> >
> >
> >
> >
>
> *******************************************************************************
> > 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...
>
> --089e013cbb8a16a0ce05029192d5
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba614d4817be81050291c883
Messages In This Thread
- Mutex - guard ?
Cesar Martins -- Friday, 5 September 2014, at 10:34 a.m.
- Re: Mutex - guard ?
Art Kagel -- Friday, 5 September 2014, at 10:46 a.m.
- Re: Mutex - guard ?
Marco Greco -- Friday, 5 September 2014, at 10:49 a.m.
- Re: Mutex - guard ?
Cesar Martins -- Friday, 5 September 2014, at 11:21 a.m.
- Re: Mutex - guard ?
Fernando Nunes -- Friday, 5 September 2014, at 12:18 p.m.
- Re: Mutex - guard ?
Cesar Martins -- Friday, 5 September 2014, at 1:33 p.m.
- Re: Mutex - guard ?
Fernando Nunes -- Friday, 5 September 2014, at 2:21 p.m.
- Re: Mutex - guard ?
david@smooth1.co.uk -- Friday, 5 September 2014, at 5:12 p.m.
- Re: Mutex - guard ?
Cesar Martins -- Monday, 8 September 2014, at 10:05 a.m.
- RE: Mutex - guard ?
Paul Watson -- Monday, 8 September 2014, at 10:13 a.m.
- Re: Mutex - guard ?
Marco Greco -- Monday, 8 September 2014, at 1:05 p.m.
- Re: Mutex - guard ?
Fernando Nunes -- Monday, 8 September 2014, at 1:35 p.m.
- Re: Mutex - guard ?
Cesar Martins -- Monday, 8 September 2014, at 1:52 p.m.
- Re: Mutex - guard ?
Fernando Nunes -- Monday, 8 September 2014, at 1:58 p.m.
- Re: Mutex - guard ?
Cesar Martins -- Monday, 8 September 2014, at 2:13 p.m.
- Re: Mutex - guard ?
Fernando Nunes -- Monday, 8 September 2014, at 2:07 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
