sysadmin monitoring
A DBA found that cumulative IS write and delete counters in sysadmin's mon_table_profile sometimes decreased between 15-minute samples, even though reads kept rising. Art Kagel listed the usual causes: an onstat/onmode -z reset, counter wrap (fixed in the Perl script), and the task's purge of rows older than 7 days. John Miller suggested joining mon_table_profile to ph_run and comparing run_ztime between samples, so readings taken after a stats reset are skipped (with an example query). Fernando Nunes asked which tables and version were involved. The poster noted one drop matched a server bounce, but no final root cause for the remaining drops is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm working on some scripts that use the data in the mon_table_profile 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_Omaha>; Linkedin<https://www.linkedin.com/company/west-corporation>; [West]
There are three reasons that I am aware of that will cause the values in
mon_table_profile to decrease:
1. an onmode -z reset
2. the value wrapped
3. weekly the mon_table_profile task deletes all the rows in the table
(take a look at the ph_task table entry for the task)
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_table_profile
> 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_Omaha
> >
> Linkedin<https://www.linkedin.com/company/west-corporation>;
>
> [West]
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3b542ab394f052939f46a
Correction, it deletes all rows that are more than 7 days old by default. Check the value of the tk_delete column in the ph_task record for what you have it set to. Reread your question: So, you have the mon_table_profile task polling the sysptprof table every 15 minutes and inserting a row with the current settings into the mon_table_profile 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_table_profile > 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_Omaha > > > Linkedin<https://www.linkedin.com/company/west-corporation>; > > [West] > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f9a8c198bae05293a1292
I'm pulling the data via a Perl script - here's a sample of the report (many
rows omitted - just wanted to show the decreases).
Record Count, Read, Write, and Scan Deltas for none. from 2016-01-13 00:26:08
to 2016-01-13 11:41:08
Date/Time IS Reads IS Writes IS Rewrites IS Deletes
--------------------------------------------------------------------------------
-
*rows deleted*
2016-01-13 10:11:07 314565976 8314230 21922142 8367617
2016-01-13 10:26:07 337545332 8457941 22296732 8380331
2016-01-13 10:41:07 362606528 8613370 22712814 8386545
2016-01-13 10:56:08 384011298 10837927 22991842 10556430
2016-01-13 11:11:08 412123444 8953798 23596692 8407449
We do issue an onstat -z nightly, but this is happening mid-morning (and not
all columns show the decrease). We did see a wrap, but only when it got to
over 2 billion (and fixed that in the Perl script). Also, this is for today,
but we do see data "disappears" after 7 days.
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:50 AM
To: ids@iiug.org
Subject: Re: sysadmin monitoring [36369]
There are three reasons that I am aware of that will cause the values in
mon_table_profile to decrease:
1. an onmode -z reset
2. the value wrapped
3. weekly the mon_table_profile task deletes all the rows in the table
(take a look at the ph_task table entry for the task)
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_table_profile
> 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_Omaha
> >
> Linkedin<https://www.linkedin.com/company/west-corporation>;
>
> [West]
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3b542ab394f052939f46a
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Dumb question, but to get it out-of-the-way, is the report for a single
partnum or a summary/total of all partnums?
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 12:03 PM, Mitchell, Jeffrey J. <JJMitchell@west.com>
wrote:
> I'm pulling the data via a Perl script - here's a sample of the report
> (many
> rows omitted - just wanted to show the decreases).
>
> Record Count, Read, Write, and Scan Deltas for none. from 2016-01-13
> 00:26:08
> to 2016-01-13 11:41:08
>
> Date/Time IS Reads IS Writes IS Rewrites IS Deletes
>
>
>
--------------------------------------------------------------------------------
-
> *rows deleted*
> 2016-01-13 10:11:07 314565976 8314230 21922142 8367617
> 2016-01-13 10:26:07 337545332 8457941 22296732 8380331
> 2016-01-13 10:41:07 362606528 8613370 22712814 8386545
> 2016-01-13 10:56:08 384011298 10837927 22991842 10556430
> 2016-01-13 11:11:08 412123444 8953798 23596692 8407449
>
> We do issue an onstat -z nightly, but this is happening mid-morning (and
> not
> all columns show the decrease). We did see a wrap, but only when it got to
> over 2 billion (and fixed that in the Perl script). Also, this is for
> today,
> but we do see data "disappears" after 7 days.
>
> 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:50 AM
> To: ids@iiug.org
> Subject: Re: sysadmin monitoring [36369]
>
> There are three reasons that I am aware of that will cause the values in
> mon_table_profile to decrease:
>
> 1. an onmode -z reset
>
> 2. the value wrapped
>
> 3. weekly the mon_table_profile task deletes all the rows in the table
>
> (take a look at the ph_task table entry for the task)
>
> 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_table_profile
> > 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_Omaha
> > >
> > Linkedin<https://www.linkedin.com/company/west-corporation>;
> >
> > [West]
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11c3b542ab394f052939f46a
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0149449ade436d05293a3cec
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_delete value for the task in question is '7 00:00:00'. The tk_frequency 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_delete column in the ph_task record for what you have it set to. Reread your question: So, you have the mon_table_profile task polling the sysptprof table every 15 minutes and inserting a row with the current settings into the mon_table_profile 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_table_profile > 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_Omaha > > > Linkedin<https://www.linkedin.com/company/west-corporation>; > > [West] > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f9a8c198bae05293a1292 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Can you identify the tables? And compare the drops with statistics being run? What version are you using? On Wed, Jan 13, 2016 at 4:19 PM, Mitchell, Jeffrey J. <JJMitchell@west.com> wrote: > I'm working on some scripts that use the data in the mon_table_profile > 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_Omaha > > > Linkedin<https://www.linkedin.com/company/west-corporation>; > > [West] > > > > ******************************************************************************* > 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... --001a11405e320d21e905293aef60
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=5Fztime (the time when statistics where last cleared).
select * from mon=5Ftable=5Fprofile M , ph=5Frun R where R.run=5Ftask=5Fid==3D (select
tk=5Fid from ph=5Ftask where tk=5Fname matches "mon=5Ftable=5Fprofile") and
R.run=5Ftask=5Fseq =3D 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=5Fdelete value for the task in question is '7 00:00:00'. The
tk=5Ffrequency
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=5Fdelete column in the ph=5Ftask record for what =
you
have it set to.
Reread your question: So, you have the mon=5Ftable=5Fprofile task polling t=
he
sysptprof table every 15 minutes and inserting a row with the current
settings
into the mon=5Ftable=5Fprofile 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=5Ftable=5Fprofile
> 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=5FOmaha
> >
> Linkedin<https://www.linkedin.com/company/west-corporation>;
>
> [West]
>
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113f9a8c198bae05293a1292
***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.
***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.
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=5Fztime
(the time when statistics where last cleared).
select * from mon=5Ftable=5Fprofile M , ph=5Frun R where R.run=5Ftask=5Fid==3D (select tk=5Fid from ph=5Ftask where tk=5Fname matches
"mon=5Ftable=5Fprofile") and R.run=5Ftask=5Fseq =3D 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=5Fdelete value for the task in question is '7 00:00:00'. The
tk=5Ffrequency 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=5Fdelete column in the ph=5Ftask record for what =
you have it set to.
Reread your question: So, you have the mon=5Ftable=5Fprofile task polling t=
he sysptprof table every 15 minutes and inserting a row with the current
settings into the mon=5Ftable=5Fprofile 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=5Ftable=5Fprofile 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=5FOmaha
> >
> Linkedin<https://www.linkedin.com/company/west-corporation>;
>
> [West]
>
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113f9a8c198bae05293a1292
***************************************************************************=
****
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.
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
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
This is handy (and may help with troubleshooting) showing the last time onstat
-z was executed:
echo "select dbinfo('UTC_TO_DATETIME', sh_pfclrtime) from sysshmvals;" |
dbaccess sysmaster 2>/dev/null | tail -2 | line
The lore is in sysshmvals we called them "profilers", not "statistics", thus
the "sh_pfclrtime" column name, and also thus the -p in onstat -p.
Mark Scranton
The Mark Scranton Group
mark@markscranton.com