IDS Forum

RE: > btree pages in cache

Posted By: Willem Roos
Date: Friday, 11 February 2005, at 3:19 a.m.

In Response To: > btree pages in cache (Willem Roos)

Hi Robert,

Forgot the onstat -R:

---- 8< ----
...
252 f 3277 99.3% 3253 3253 0 0 0
253 m 0.7% 24 24 0 0 0
254 f 3876 100.0% 3875 3875 0 0 0
255 m 0.0% 1 1 0 0 0
560 dirty, 424798 queued, 425000 total, 524288 hash buckets, 4096 buffer size
start clean at 2% (of pair total) dirty, or 66 buffs dirty, stop at 1%
0 priority downgrades, 0 priority upgrades
---- 8< ----

Some more questions if i may:

1) From the sapnote i gather that rangesize affects light scans. How does it work & what is the meaning of the value in 'onmode -C rangesize #value#'? None of the 9.40 docs have any information on rangesize.
2) What does '0 priority downgrades' mean & what does it have to do with LRUAGE?
3) Only one scanner will work on a partition? I did the configuration changes the way you suggested & can indeed see more scanners, but always only one per partition.

Art suggested it might be the old buffer aging logic bug from way back when. I tried setting NOLRUPRIO in the environment & restarted but it has no effect. Also, betree pages in cache increase and decrease over time so the btree pages do age. Trying to find out whether bug 121515 (LRUAGE business) has been fixed in 7.31.UD8...

Thank you for your help,

> -----Original Message-----
> From: Robert Seifert [mailto:robert.seifert@de.ibm.com]
> Sent: 10 February 2005 16:17
> To: Willem Roos
> Cc: ids@iiug.org
> Subject: RE: > btree pages in cache [4224]
>
> Hi Willem,
>
> Due to your latest memo it looks to me that the system performance
> decreases dramatically. This is the reason for reported db restart?
>
> I did not notice the onstat -R output and I also do not know your set
> environment. Anyway, since I currently do not have a 7.31.UD8 running
> please look at the last line of onstat -R output. If there is
> mentioned '0
> priority downgrades' then set LRUAGE to value 1 in environment of user
> informix. In this case a restart of db is needed.
>
> As far as I know there are some fixes in this 7.31.UD8 related to some
> important DR product defects.
> Anyway, I would like to know whether the same situation
> reoccurring when DR
> is switched off?
>
> Nevertheless I will try to give a suggestion for current situation.
> Please note that I do have some experience of IDS db in a SAP
> environment.
> By the way, since your company is a SAP customer as well, you
> probably will
> be able to read SAP note 805973 which contains some
> additional information
> and will be updated frequently.
>
> Please consider that the following configuration steps are
> set on the fly.
> This means in case of a needed db restart you have to repeat
> the actions as
> mentioned below:
>
> - Add some additonal 3 btree scanner threads by command
> 'onmode -C start
> 3'. Now you have 4 btree scanner threads configured.
> - Configure a rangesize by command 'onmode -C rangesize 100'
> - Set also a threshold by command 'onmode -C threshold 200000'
>
> The following recommendation depends on the configured number
> of NUMCPUVPS:
>
> <= 4 : Leave btree scanner in LOW mode
> >4 : Change btree scanner to HIGH mode by command 'onmode -C high'
>
> If you experience some problems which could have something to
> do with btree
> config changes as mentioned above using onmode -C command
> options you
> will be able to set back to defaults or switch btree scanner off.
>
> Unfortunately I do not know whether the performance is fine
> as well in a DR
> environment.
> Probably you could deal some more with threshold and number
> of scanners...
>
> Again please remember to repeat the action if a db restart
> has been done,
> since the onconfig parameter BTSCANNER which has been introduced in
> 9.40.xC5 (167883) is unfortunately not available in this
> 7.31.UD8 vanilla
> release.
>
> The second step which could help, if the 1st step as
> described above does
> not help:
> If there are big tables with attached indices in your system,
> schedule a
> maintenance for detaching.
> This action will allow the btree scanner to bypass the buffer
> (light scans)
> on detached indices when rangesize for btree scanners is enabled.
>
> HTH,
>
> Robert
>
> Robert Seifert
> _________________________________________________________________
> IBM Deutschland GmbH, Data Management Solutions, Software,
> Informix Products, Advanced Support
>
> Elite VAR support engineer
> Development Support for IBM Informix Products on-site @ SAP
> c/o SAP AG, -TechDev05-,Neurottstrasse 16, D-69190 Walldorf, Germany
>
> Phone +49-6227-7-45713, Fax +49-6227-7-47147
> mailto:robert.seifert@de.ibm.com or mailto:robert.seifert@sap.com
> http://www.ibm.com/software/data/informix
>
>
> "Willem Roos" <wroos@shoprite.co.za> wrote on 10.02.2005 13:35:52:
>
> >
> > Hi Robert,
> >
> > Thank you for your reply.
> >
> > Buffer related configuration (i might miss some & probably there are
> > some extra):
> >
> > ---- 8< ----
> > BUFFERS 425000 # Maximum number of shared buffers
> > NUMAIOVPS 64 # Number of IO vps
> > PHYSBUFF 64 # Physical log buffer size (Kbytes)
> > LOGBUFF 32 # Logical log buffer size (Kbytes)
> > CLEANERS 128 # Number of buffer cleaner
> processes
> > CKPTINTVL 120 # Check point interval (in sec)
> > LRUS 128 # Number of LRU queues
> > LRU_MAX_DIRTY 2 # LRU percent dirty begin
> cleaning limit
> > LRU_MIN_DIRTY 1 # LRU percent dirty end
> cleaning limit
> > ---- 8< ----
> >
> > This instance has 155 chunks on cooked file space.
> >
> > The engine has been restarted (production unfortunately takes
> > precedence over my personal academic interests :-) but after +- 1
> > hour all symptoms are back in all their glory.
> >
> > 'onmode -C clean' shows that deletes are occurring on the tables
> > whose indexes take up most of the cache.
> >
> > I'll appreciate info on how to tune this (just up the number of
> > threads?). I realize it'll be in the manual but this is production
> > and bedtime (for reading) is still a few hours away :-)
> >
> > Here's some more info:
> > ---- 8< ----
> > $ onstat -C prof
> >
> > IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line (Prim)
> > -- Up 01:19:15 -- 2158512 Kbytes
> >
> > Btree Cleaner Info
> > BT scanner profile Information
> > ==============================
> > Active Threads 1
> > Global Commands 0
> > Number of partition scans 593
> > Main Block 0xb0f8fab8
> > BTC Admin 0x00000000
> >
> >
> > BTS info id Prio Partnum Key Cmd
> > 0xb0f8fc80 0 Low 0x00C00002 1 100000 Scan index
> > Number of leaves pages scanned 3668034
> > Number of leaves with deleted items 4468
> > Time spent cleaning (sec) 1412
> > Number of index compresses 1165
> > Number of deleted items 288013
> > Number of index range scans 0
> > Number of index leaf scans 528
> > Scan Type Leaf
> > ---- 8< ----
> >
> > Thanks & regards,
> >
> > > -----Original Message-----
> > > From: Robert Seifert [mailto:robert.seifert@de.ibm.com]
> >
> > > Sent: 10 February 2005 13:02
> > > To: Willem Roos
> > > Cc: ids@iiug.org
> > > Subject: Re: > btree pages in cache [4224]
> > >
> >
> > > Hi Willem,
> > >
> >
> > > Please let us know the output of 'onstat -R' which shows the
> >
> > > status of LRU
> > > queues.
> > > What's the current buffer related configuration?
> > > In general I suspect that this might be caused by btree
> >
> > > scanner activity in
> > > status cleaning index of mentioned table(s) which might
> have attached
> > > indices.
> > > You will be able to monitor the btree scanner activity
> with 'onstat -C
> > > [options]'.
> > > Tuning could be done by 'onmode -C [options]', but as always
> >
> > > this should be
> > > tested before applying productive.
> > > The default configuration is one running btree scanner in
> low mode.
> > > Depending on workload, database configuration and btree
> >
> > > related status of
> > > tables this probably needs to be tuned.
> > > The btree scanner has been introduced with 7.31.UD8 to do
> >
> > > same job as btree
> > > cleaner did in ealier 7.31 releases.
> > > Please have a look for 7.31.UD8 release notes Chapter IV,
> >
> > > Section A for
> > > more details:
> > >
> >
> > > http://www-306.ibm.com/software/data/informix/pubs/libary/note
> > > s/relnotes/rn_7.31.ud8.html#Btree
> > >
> >
> > > You could monitor current btree scanner clean activity by
> >
> > > using command
> > > onstat -C clean [-r] | egrep 'C'.
> > >
> >
> > > The output of 'onstat -C hot ' shows the hotlist which will
> >
> > > be done by the
> > > btree scanner. The last asteric at the end of a line
> shows on which
> > > position in the list the btree scanner is or was currently
> >
> > > working on...
> > >
> >
> > >
> >
> > > HTH,
> > >
> >
> > > Robert
> > >
> >
> > > Robert Seifert
> > > _________________________________________________________________
> > > IBM Deutschland GmbH, Data Management Solutions, Software,
> > > Informix Products, Advanced Support
> > >
> >
> > > Elite VAR support engineer
> > > Development Support for IBM Informix Products on-site @ SAP
> > > c/o SAP AG, -TechDev05-,Neurottstrasse 16, D-69190
> Walldorf, Germany
> > >
> >
> > > Phone +49-6227-7-45713, Fax +49-6227-7-47147
> > > mailto:robert.seifert@de.ibm.com or mailto:robert.seifert@sap.com
> > > http://www.ibm.com/software/data/informix
> > >
> >
> > >
> >
> > > forum.subscriber@iiug.org wrote on 10.02.2005 08:15:36:
> > >
> >
> > > >
> > > > Hi list,
> > > >
> > > > I have an excessive # of btree pages in cache (on 7.31.UD8,
> >
> > > AIX 5.2).
> > > > onstat -P shows:
> > > >
> > > > ---- 8< ----
> > > > Percentages:
> > > > Data 19.76
> > > > Btree 78.54
> > > > Other 1.70
> > > > ---- 8< ----
> > > >
> > > > I have TBLSPACE_STATS switched on in $ONCONFIG. Does anyone
> >
> > > have an idea
> > > > how i can tie up a session with pages in cache. Eg, i have
> >
> > > a script that
> > > > will map partnum (from onstat -P) to table names (via
> >
> > > oncheck -pt) like
> > > > below. Now i need a way to see who is accessing which
> >
> > > table. I'm using
> > > > syssqexplain (columns sqx_sqlstatement and sqx_estcost) in
> >
> > > sysmaster but
> > > > i'm not sure if a) i'm interpreting it correctly, b) it's
> >
> > > telling me the
> > > > truth, c) i'm barking up the correct tree.
> > > >
> > > > Any help/pointers appreciated.
> > > >
> > > > Here's my script output, you'll see table
> >
> > > corp_p_db:t150_range owns 21%
> > > > of cache, etc. So how is using this table?
> > > >
> > > > ---- 8< ----
> > > > Cache statistics:
> > > >
> >
> > > --------------------------------------------------------------
> > > ----------
> > > > --------
> > > > dskreads pagreads bufreads Êched dskwrits pagwrits
> bufwrits Êched
> > > > 51445307 103093471 2397742886 97.85 2894015 6230366
> >
> > > 232509393 98.76
> > > > Finding partition pages in buffer pool...
> > > > Database Table + Total % Btree %
> >
> > > Data % Dirty
> > > > %
> > > >
> >
> > > --------------------------------------------------------------
> > > ----------
> > > > --------
> > > > corp_p_db t150_range + 91874 21 91872 33
> >
> > > 2 0 0
> > > > 0
> > > > corp_p_db t414_sellpriceins - 85702 20 1040 0
> >
> > > 84662 57 30
> > > > 16
> > > > corp_p_db t415_itemsellprce + 33615 7 0 0
> >
> > > 33614 22 0
> > > > 0
> > > > corp_p_db t168_locdepotitem = 29035 6 29035 10
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t415_itemsellprce = 13275 3 13275 4
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t415_itemsellprce + 12972 3 12972 4
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t168_locdepotitem + 10158 2 10158 3
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t318_orderpack = 9314 2 9314 3
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t150_range - 6734 1 6734 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t173_depitemprof = 6255 1 6255 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t856_eventhandler - 6085 1 0 0
> >
> > > 6084 4 2
> > > > 1
> > > > corp_p_db t800_slbrnitemstat = 6073 1 6070 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t318_orderpack + 5872 1 5872 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t415_itemsellprce = 5649 1 5649 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t168_locdepotitem + 5507 1 5507 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t415_itemsellprce = 5432 1 5432 2
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t168_locdepotitem = 4759 1 4759 1
> >
> > > 0 0 0
> > > > 0
> > > > corp_p_db t403_contractitem - 4524 1 1112 0
> >
> > > 3410 2 7
> > > > 3
> > > > corp_p_db t978_contract - 4399 1 463 0
> >
> > > 3935 2 0
> > > > 0
> > > > corp_p_db t168_locdepotitem + 4278 1 0 0
> >
> > > 4278 2 8
> > > > 4
> > > > corp_p_db t168_locdepotitem + 3997 0 3993 1
> >
> > > 0 0 7
> > > > 3
> > > > <...lots more go here...>
> > > >
> >
> > > --------------------------------------------------------------
> > > ----------
> > > > --------
> > > > Totals: 425000 -- 270678 63
> >
> > > 147167 34 180
> > > > 0
> > > >
> > > > ------
> > > > Willem Roos
> > > > "Per sercas vi malkovri" - JS Bach (freely translated)
> > > >
> > > > Disclaimer
> > > > http://www.shoprite.co.za/disclaimer.html
> > > >
> > > >
> > >
> >
> > >
> >
> >
> > Disclaimer
> >
> > http://www.shoprite.co.za/disclaimer.html
>
>
Disclaimer
http://www.shoprite.co.za/disclaimer.html

Messages In This Thread

• > btree pages in cache
  Willem Roos -- Thursday, 10 February 2005, at 2:15 a.m.
  • Re: > btree pages in cache
    Robert Seifert -- Thursday, 10 February 2005, at 6:09 a.m.
  • RE: > btree pages in cache
    Willem Roos -- Thursday, 10 February 2005, at 7:40 a.m.
    • Re: > btree pages in cache
      Michael Mueller -- Monday, 14 February 2005, at 5:35 a.m.
  • RE: > btree pages in cache
    Willem Roos -- Friday, 11 February 2005, at 3:19 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.