Thanks Mark,
Â* I'm glad it also can be done on the fly, but is there an onconfig parameter or environment variable for this function ?
I will definitely give this parameter some tries.

Floyd




----- Original Message -----
Subject: Re: long checkpoint duration
From: "Mark Jamison" >;majp51@yahoo.com>
Date: Tue, February 12, 2008 15:32










Hi Floyd,

Think of a leaf scan as if it were a sequential scan.
Within that context A range scan is an asynchronous bulk scan of pages from the Lowest page with potneitally deleted itesm to the Highest page of the same.

So if you have a lot of bulk deletes or at least deletes that occur within a certain range, then range scan can have a huge impact.

Of course, even on large indexes that don't have a lot of bulk deletes, Range scanning can help, because it will still be a more efficient scanning of the index versus a leaf scan.

Unfortunately rangesize did not get documented until the 11.10 manuals. It has been around since 9.40.UC1 though.

Here is the definition of rangesize:

rangesize = The size, in kilobytes, an index or index fragment must exceed before the index is cleaned with range scanning. To allow small indexes to be scanned by the leaf scan method set rangesize to 100. The default is OFF (-1).



Thanks,

Mark




----- Original Message ----
From: Floyd Wellershaus >;floyd@fwellers.com>
To: Mark Jamison >;majp51@yahoo.com>; informix-list@iiug.org
Sent: Tuesday, February 12, 2008 12:52:18 PM
Subject: Re: long checkpoint duration


Thanks Mark.
I find very little information in the manual about what rangesize does or is. The actual rangsize=num usage is not even documented.

I would love to do trace checkpoint but I can't bounce my instance.



----- Original Message -----
Subject: Re: long checkpoint duration
From: "Mark Jamison" >;majp51@yahoo.com>
Date: Tue, February 12, 2008 13:30









Well, it is by no means a smoking gun, but eh number of leaf scans you are doing could result in some performance degradation.

I would definitely sugest enabling range scanning, since it is more efficient.
Quickest way to do this would be

onmode -C rangesize 100
onmode -C stop 1
onmode -C start 1


I would also consider enabling TRACECKPT, it is documented in the following article:

http://www-1.ibm.com/support/docview...id=swg21250366

That would at least help identify where the bottleneck is during long checkpoints.



-Mark




----- Original Message ----
From: Floyd Wellershaus >;floyd@fwellers.com>
To: Mark Jamison >;majp51@yahoo.com>; informix-list@iiug.org
Sent: Tuesday, February 12, 2008 12:15:34 PM
Subject: Re: long checkpoint duration


onstat -C
Btree Cleaner Info
BT scanner profile Information
==============================
Active ThreadsÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â*Â*Â*Â*Â*Â*Â* 1
Global CommandsÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* Â*Â*Â*Â*Â*Â*Â*Â* 0Â*
Number of partition scansÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 5462
Main BlockÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â* 0x07000002a17bde10
BTC AdminÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â*Â* 0x0000000000000000

BTS infoÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* idÂ*Â* PrioÂ*Â*Â* PartnumÂ*Â*Â*Â*Â* KeyÂ*Â*Â*Â* CmdÂ*
0x7000002a17e1b40Â*Â*Â*Â* 0Â*Â* HighÂ*Â* 0x0010002CÂ*Â*Â*Â* 2 10000000Â* Scan index
Â*Â*Â* Number of leaves pages scannedÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 35107643
Â*Â*Â* Number of leaves with deleted itemsÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 314238
Â*Â*Â* Time spent cleaning (sec)Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â*Â* 59868
Â*Â*Â* Number of index compressesÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* Â*Â*Â*Â* 290562
Â*Â*Â* Number of deleted itemsÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â* 23819976
Â*Â*Â* Number of index range scansÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â*Â*Â*Â* 0
Â*Â*Â* Number of index leaf scansÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â*Â* 1427
Â*Â*Â* Number of index alice scansÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â *Â*Â*Â*Â*Â* 0
Â*Â*Â* Scan TypeÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* Leaf

Onstat -C hot
Btree Cleaner Info
Index Hot List
==============
Â*Â*Â* Current ItemÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â* 1Â*Â*Â*Â* List CreatedÂ*Â*Â*Â*Â*Â*Â*Â*Â* 13:07:10
Â*Â*Â* List SizeÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 1Â*Â*Â*Â* List expires inÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 0 sec
Â*Â*Â* Hit ThresholdÂ*Â*Â*Â*Â*Â* 5000Â*Â*Â*Â* Range Scan ThresholdÂ*Â*Â*Â*Â*Â*Â* -1

PartnumÂ*Â*Â*Â*Â*Â*Â* KeyÂ*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* Hits
0x0010002CÂ*Â*Â*Â*Â*Â* 2Â*Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 5022 *





----- Original Message -----
Subject: Re: long checkpoint duration
From: "Mark Jamison" >;majp51@yahoo.com>
Date: Tue, February 12, 2008 12:53









Hi Floyd,

tell me what onstat -C shows you?
Also onstat -C hot |wc -l would be nice as well.

One of the bigger culprits in the old days was the btcleaner thread since it has to clean indices for the who instance, and the current defaults help make the btscanner thread just as big of a potential road block.

The good news is that with the btscanner, you can fire off more threads. You can also enable range scanning as well.

-Mark




----- Original Message ----
From: Floyd Wellershaus >;floyd@fwellers.com>
To: informix-list@iiug.org
Sent: Tuesday, February 12, 2008 11:16:01 AM
Subject: long checkpoint duration


Hi,
Â* The age old question I guess. But our checkpoint durations are consistently near 20seconds.

ids10.0 fc5 on ibm p570 running aix 5.3 7 cpus 41g ram.
ckptintvl is 300, I am considering halving that.
We have 128 lru's and 128 cleaners
Art's ratio tool shows all good except a high btr.
We have 2557440 buffers, but I am pretty sure if we add much more we'll start paging. Anyway , that probably isn't affecting the checkpoint.

onstat -g iov shows the inverted pyramid with 7 kio threads.

Aside from worrying about fragmentation and data distribution issues in our FastT900 san, is there anything I'm missing that I should look at real quick to reduce the chkpoint interval ?

Thanks,
Floyd





-----Inline Attachment Follows-----

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list