IDS Forum

Re: sysadmin monitoring

Posted By: Art Kagel
Date: Wednesday, 13 January 2016, at 3:57 p.m.

In Response To: RE: sysadmin monitoring (John Miller iii)

Here's John's query again without the "net-isms":

SELECT ROUND( (MAX(nbuffs) / (sum(PA.value) - sum(PB.value))) /

((MAX(RA.run_mttime) - MAX(RB.run_mttime))/3600.0),3) BTR,

" ", dbinfo( 'utc_to_datetime', MAX(RB.run_mttime)) TIME
FROM sysadmin:ph_task T,

sysadmin:mon_profile PB,

sysadmin:ph_run RB,

sysadmin:mon_profile PA,

sysadmin:ph_run RA,

sysmaster:sysbufpool BUFF
WHERE tk_name = 'mon_profile'
AND PB.name in ( 'pagreads_2K', 'flushes_2K', 'fgwrites_2K',
'lruwrites_2K')
AND BUFF.bufsize = 2048
AND PB.name = PA.name
AND RB.run_task_id = T.tk_id
AND RA.run_task_id = T.tk_id
AND RB.run_task_id = T.tk_id
AND RA.run_task_id = T.tk_id
AND RA.run_task_seq = PA.id
AND RB.run_task_seq = PB.id
AND RB.run_ztime = RA.run_ztime
AND PB.id = PA.id - 1
GROUP BY PB.id
ORDER BY TIME desc;

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 Wed, Jan 13, 2016 at 2:39 PM, John Miller iii <miller3@us.ibm.com> wrote:

> What I was trying to get across is that you can check each reading and
> ensure the ztime is the same. As an example, I will use Art's Apple Turn
> Over recipe (oops Buffer Turn Over) in which I tried to apply it to the
> sysadmin database. Please note how we check the ztime ( RB.run=5Fztime =3D
> RA.run=5Fztime) to ensure they have not changed.
>
> RB is the Runtime Before Table
> RA is the Runtime After Table
>
> Art's BTR 2.0 Buffer Turnover Ratio
> -----------------------------------
> SELECT
> ROUND( (MAX(nbuffs) / (sum(PA.value) - sum(PB.value))) /
>
> ((MAX(RA.run=5Fmttime) - MAX(RB.run=5Fmttime))/3600.0),3) BTR
>
> ," "
> ,dbinfo( 'utc=5Fto=5Fdatetime', MAX(RB.run=5Fmttime)) TIME
> FROM sysadmin:ph=5Ftask T, sysadmin:mon=5Fprofile PB, sysadmin:ph=5Frun RB,
>
> sysadmin:mon=5Fprofile PA, sysadmin:ph=5Frun RA,
>
> sysmaster:sysbufpool BUFF
> WHERE tk=5Fname =3D 'mon=5Fprofile'
> AND PB.name in ( 'pagreads=5F2K', 'flushes=5F2K', 'fgwrites=5F2K',
> 'lruwrites=5F2K')
> AND BUFF.bufsize =3D 2048
> AND PB.name =3D PA.name
> AND RB.run=5Ftask=5Fid =3D T.tk=5Fid
> AND RA.run=5Ftask=5Fid =3D T.tk=5Fid
> AND RB.run=5Ftask=5Fid =3D T.tk=5Fid
> AND RA.run=5Ftask=5Fid =3D T.tk=5Fid
> AND RA.run=5Ftask=5Fseq =3D PA.id
> AND RB.run=5Ftask=5Fseq =3D PB.id
> AND RB.run=5Fztime =3D RA.run=5Fztime
> AND PB.id =3D PA.id - 1
> GROUP BY PB.id
> ORDER BY TIME desc
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> From: "Mitchell, Jeffrey J." <JJMitchell@west.com>
> To: ids@iiug.org
> Date: 01/13/2016 10:15 AM
> Subject: RE: sysadmin monitoring [36377]
> Sent by: ids-bounces@iiug.org
>
> To answer Fernando's question, I should be able to identify the tables,
> using
> other scripts we have.
>
> As for the onstat -z, if that was the case, all of the columns would show
> the
> drop. We did, in fact, find that to be the case for one of the days I'm
> looking at - it just so happens that we bounced informix at that time - and
>
> all four columns in question showed the drop at that time.
>
> Jeff Mitchell
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
> Miller iii
> Sent: Wednesday, January 13, 2016 12:06 PM
> To: ids@iiug.org
> Subject: RE: sysadmin monitoring [36376]
>
> While many people think that onstat -z happens at regular time. Many times
> it
> is found that someone runs onstat -z or it is unknowing embedded in a
> script.
> For this reason each capture of statistics records the time of the last
> onstat
> -z happened. I would suggest using a select and looking at the
> run=3D5Fztime
> (the time when statistics where last cleared).
>
> select * from mon=3D5Ftable=3D5Fprofile M , ph=3D5Frun R where
> R.run=3D5Fta=
> sk=3D5Fid=3D
>
> =3D3D (select tk=3D5Fid from ph=3D5Ftask where tk=3D5Fname matches
> "mon=3D5Ftable=3D5Fprofile") and R.run=3D5Ftask=3D5Fseq =3D3D M.id;
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> From: "Mitchell, Jeffrey J." <JJMitchell@west.com>
> To: ids@iiug.org
> Date: 01/13/2016 09:20 AM
> Subject: RE: sysadmin monitoring [36374] Sent by: ids-bounces@iiug.org
>
> I'll try to answer both questions at once.
>
> For this latest script, I'm looking at all activity across all tables for a
>
> given day (knowing that the counts are reset (roughly) at midnight.
>
> The tk=3D5Fdelete value for the task in question is '7 00:00:00'. The
> tk=3D5Ffrequency value is '0 00:15:00'
>
> Jeff Mitchell
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Wednesday, January 13, 2016 10:58 AM
> To: ids@iiug.org
> Subject: Re: sysadmin monitoring [36370]
>
> Correction, it deletes all rows that are more than 7 days old by default.
> Check the value of the tk=3D5Fdelete column in the ph=3D5Ftask record for
> w=
> hat
> =3D
> you have it set to.
>
> Reread your question: So, you have the mon=3D5Ftable=3D5Fprofile task
> polli=
> ng
> t=3D
> he sysptprof table every 15 minutes and inserting a row with the current
> settings into the mon=3D5Ftable=3D5Fprofile table. So, is your script
> always
> fetching the latest row from the table and that row's values are
> occassionally
> smaller than the row from the previous 15 minute interval?
>
> 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 Wed, Jan 13, 2016 at 11:19 AM, Mitchell, Jeffrey J.
> <JJMitchell@west.com>
> wrote:
>
> > I'm working on some scripts that use the data in the
> > mon=3D5Ftable=3D5Fprofile table in the sysadmin database, and I've seen a
> > couple of anomalies that I need explained.
> >
> > If I look at the IS reads, writes, rewrites, and deletes - for the
> > most part, these numbers tend to increase over the course of a day. I
> > assume that's because a "running total" of these counts is being done.
> > However, if I look at the IS writes, I've seen them drop from one time
> > period to the next (we tweaked the schedule so it runs every 15
> > minutes).
> >
> > I've seen these drops in the IS deletes as well, and I can't
> > understand why the "total" for the day will drop in the middle of the
> > day for the writes and deletes.
> >
> > Thanks,
> >
> > [West]<http://www.west.com/>
> >
> > Jeffrey J Mitchell
> >
> > Informix Database Administrator
> >
> > Interactive Serivces
> >
> > o 402.716.0500 c 402.321.7443 e
> > jjmitchell@west.com<mailto:jjmitchell@west.com> west.com<
> > http://www.west.com/>
> >
> > Facebook<https://www.facebook.com/WestCorporation>
> > Blog<http://www.west.com/blog/>
> > Twitter<https://twitter.com/WestCorp=3D5FOmaha
> > >
> > Linkedin<https://www.linkedin.com/company/west-corporation>
> >
> > [West]
> >
> >
> >
> >
>
>
> ***************************************************************************=
> =3D
>
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a113f9a8c198bae05293a1292
>
>
> ***************************************************************************=
> =3D
>
> ****
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***************************************************************************=
> =3D
>
> ****
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***************************************************************************=
> ****
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ***************************************************************************=
> ****
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a1141c1e0fbfe2405293d6843

Messages In This Thread

• sysadmin monitoring
  Mitchell, Jeffrey J. -- Wednesday, 13 January 2016, at 11:19 a.m.
  • Re: sysadmin monitoring
    Art Kagel -- Wednesday, 13 January 2016, at 11:49 a.m.
    • RE: sysadmin monitoring
      Mitchell, Jeffrey J. -- Wednesday, 13 January 2016, at 12:03 p.m.
      • Re: sysadmin monitoring
        Art Kagel -- Wednesday, 13 January 2016, at 12:11 p.m.
  • Re: sysadmin monitoring
    Art Kagel -- Wednesday, 13 January 2016, at 11:58 a.m.
    • RE: sysadmin monitoring
      Mitchell, Jeffrey J. -- Wednesday, 13 January 2016, at 12:19 p.m.
      • RE: sysadmin monitoring
        John Miller iii -- Wednesday, 13 January 2016, at 1:05 p.m.
        • RE: sysadmin monitoring
          Mitchell, Jeffrey J. -- Wednesday, 13 January 2016, at 1:14 p.m.
          • RE: sysadmin monitoring
            John Miller iii -- Wednesday, 13 January 2016, at 2:39 p.m.
            • Re: sysadmin monitoring
              Art Kagel -- Wednesday, 13 January 2016, at 3:57 p.m.
  • Re: sysadmin monitoring
    Fernando Nunes -- Wednesday, 13 January 2016, at 12:59 p.m.
    • Re: sysadmin monitoring
      MARK SCRANTON -- Wednesday, 17 February 2016, at 1:24 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.