SEO

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