Re: long checkpoint duration

Oh never mind, I see it. BTSCANNER. :-)) Thanks again. ----- 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.wss?uid=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 -CBtree 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