IDS Forum

RE: > btree pages in cache

Posted By: Willem Roos
Date: Thursday, 10 February 2005, at 7:40 a.m.

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

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

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.