Informix thread working with a index

Hi all, I was trying to monitor an internal informix thread. I get a table with 8 million records. I do between 1000 and 5000 insert to this table daily. Every night I update statistics to whole database. There is a thread that it gets a session id but I can't see what it's doing. I reviewed a report from server studio that shows IO behavior on indexes and tables. And report shows a high activity in reading pages and buffers read/write. After I finished inserts this thread continues with a lock in that index and doing something with it. It consumes a considerable part of cpu. I dropped index and I recreated it. This thread now shows less use of cpu. Can anyone give me a tip about it? How can I view or understand what this thread do? thanks

PAULO RAFAEL PADILLA VELAZQUEZ wrote: > Hi all, > > I was trying to monitor an internal informix thread. > I get a table with 8 million records. > I do between 1000 and 5000 insert to this table daily. > Every night I update statistics to whole database. > > There is a thread that it gets a session id but I can't see what it's doing. > > I reviewed a report from server studio that shows IO behavior on indexes and > tables. And report shows a high activity in reading pages and buffers > read/write. > > After I finished inserts this thread continues with a lock in that index and > doing something with it. > > It consumes a considerable part of cpu. > > I dropped index and I recreated it. > > This thread now shows less use of cpu. > > Can anyone give me a tip about it? > How can I view or understand what this thread do? > Sounds like the BTree Cleaner/Scanner thread. Find all threads related to that session and see what kind of threads they are. THat should tell you. Art S. Kagel Oninit > thanks > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

Thank you Art I saw with "onstat -g ath" that my thread is btscanner_0 Why does this thread suddenly start consuming cpu? What should I review in my instance? thanks in advance

Hi, The BTree cleaner threads, of which btscanner_0 is one, are the threads that do the work of maintaining indexes. When you insert a row into a table that has indexes, the btree cleaner threads do the work of adding the row to the appropriate indexes. That may include re-balancing the btree. So the work of the btree cleaner is a little indeterminate. The btree cleaner does need to lock parts of the index some of the time in order to correctly re-arrange the keys. The only thing you might want to review is whether or not you have the right number of btree cleaners to keep up with the volume of index changes. The number of btree cleaners was fixed at one in some releases, but its configurable in more recent releases. What IDS release are you using? Cheers, Dick Snoke IBM Software Group - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "PAULO RAFAEL PADILLA VELAZQUEZ" <rafaelpadilla@grupomayan.com> To: ids@iiug.org Date: 02/07/2008 09:21 PM Subject: Re: Informix thread working with a index [11236] Thank you Art I saw with "onstat -g ath" that my thread is btscanner_0 Why does this thread suddenly start consuming cpu? What should I review in my instance? thanks in advance ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Hi Paulo, Is the thread staying active? What does a an onstat -C show you? What does an onstat -C part | sort -nk 3 show? ----- Original Message ---- From: PAULO RAFAEL PADILLA VELAZQUEZ <rafaelpadilla@grupomayan.com> To: ids@iiug.org Sent: Thursday, February 7, 2008 8:17:21 PM Subject: Re: Informix thread working with a index [11236] Thank you Art I saw with "onstat -g ath" that my thread is btscanner_0 Why does this thread suddenly start consuming cpu? What should I review in my instance? thanks in advance ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Hi, What version are you working with ? We ran into this problem I think with 10.00FC4 or FC6. There is a known bug in one of these releases that the btree scanner thread takes a lot of cpu. There was a workaround to set the btree scanner prio to low (entry in onconfig). BTSCANNER num=1,priority=low,threshold=50000,rangesize=10000 You can control the btree scanner also with some onmode options (switch it off, restart at low prio at runtime). Marcus -----Original Message----- From: Mark Jamison [mailto:majp51@yahoo.com] Sent: Friday, February 08, 2008 6:26 AM To: ids@iiug.org Subject: Re: Informix thread working with a index [11239] Hi Paulo, Is the thread staying active? What does a an onstat -C show you? What does an onstat -C part | sort -nk 3 show? ----- Original Message ---- From: PAULO RAFAEL PADILLA VELAZQUEZ <rafaelpadilla@grupomayan.com> To: ids@iiug.org Sent: Thursday, February 7, 2008 8:17:21 PM Subject: Re: Informix thread working with a index [11236] Thank you Art I saw with "onstat -g ath" that my thread is btscanner_0 Why does this thread suddenly start consuming cpu? What should I review in my instance? thanks in advance ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Thank you everybody for your answers. I will track your questions in this response. I'm using INFORMIX 9.40.FC9 on Solaris 8 with 4 cpus. Yes, my BT SCANNER is staying active. I realized that it's establishing HIGH PRIORITY. What does it make that switch priority? Is it BTSCANNER configurable on this version? "onstat -C part" is much longer. What should I review with its output? onstat -C shows: Btree Cleaner Info BT scanner profile Information ============================== Active Threads 1 Global Commands 0 Number of partition scans 161 Main Block 0x0000000112117ce0 BTC Admin 0x0000000000000000 BTS info id Prio Partnum Key Cmd 0x1121478b0 0 High 0x00700006 1 100000 Scan index Number of leaves pages scanned 325184389 Number of leaves with deleted items 324448700 Time spent cleaning (sec) 1237 Number of index compresses 324438524 Number of deleted items 45462 Number of index range scans 0 Number of index leaf scans 24 Scan Type Leaf

Hi Paulo, I recognize that behavior. Looking at the below, I'm going to presume BTSCANNER is running with defaults. do the following: onmode -C rangesize 100 onmode -C stop 1 onmode -C start 1 It appears that there are a lot of upadates and deletes going on, and as such the Default of using only Leaf Scans will absolutely kill you on a large index. How many CPUVP's do you have? If you normally do this much work you may also want to increase the number of btscanner threads. ----- Original Message ---- From: PAULO RAFAEL PADILLA VELAZQUEZ <rafaelpadilla@grupomayan.com> To: ids@iiug.org Sent: Friday, February 8, 2008 11:25:12 AM Subject: Re: Informix thread working with a index [11256] Thank you everybody for your answers. I will track your questions in this response. I'm using INFORMIX 9.40.FC9 on Solaris 8 with 4 cpus. Yes, my BT SCANNER is staying active. I realized that it's establishing HIGH PRIORITY. What does it make that switch priority? Is it BTSCANNERconfigurable on this version? "onstat -C part" is much longer. What should I review with its output? onstat -C shows: Btree Cleaner Info BT scanner profile Information ============================== Active Threads 1 Global Commands 0 Number of partition scans 161 Main Block 0x0000000112117ce0 BTC Admin 0x0000000000000000 BTS info id Prio Partnum Key Cmd 0x1121478b0 0 High 0x00700006 1 100000 Scan index Number of leaves pages scanned 325184389 Number of leaves with deleted items 324448700 Time spent cleaning (sec) 1237 Number of index compresses 324438524 Number of deleted items 45462 Number of index range scans 0 Number of index leaf scans 24 Scan Type Leaf ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Yes Mark You're right. My instance is default configured. Where can I read about tuning this parameters? Can you give an little feedback about that changes that you suggest? I looked at on-line manuals and I only found basic information. Additionally information for onmode -C rangesize 100 there isn't available. That's why I'm asking for more.

Hi Paulo, We actually do a pretty decent job discussing the btscanner threads in the documentation for IDS 11.10, however some of that is not valid for you version. Basically in your version, you have two methods by which to scan an index. Leaf scan, or Range Scan. By default we use leaf scan, which if you biggest database always was the size of say the stores_demo database, you would never have a need for anything else. However on larger tables, especially something that performs a lot of activity, something better is needed. Ultimately the answer is to use ALICE mode, but that doesn't become available until 10.00.UC/FC5. However there is a nice third option, which is range scanning. Now range scanning can still net you a full index scan, but it is far less likely than a leaf scan. The IDS 11 doc describes it as the following: ========================== Range scan mode. The index partition has a record of the lowest and highest positions where deleted items have been found. In range scan mode, the leaf level of the index partition is only scanned within this range, potentially saving many unnecessary read operations. The server performs light scans, which do not immediately use and strain the buffer pool, even though cleaning occurs through the buffer pool. This scan mode is preferable to leaf scan mode for indexes that receive delete operations only in a specific region. ========================== Setting rangesize to 100, allows for smaller tables to still have a leaf scan performed, but for everything else it uses a rangescan. That should alleviate a lot of your btscanner issues. You may also want to take a look at the output of your onstat -C hot, it is very likely given the size of the tables you have mentioned that you need more than the 1 btscanner thread. Hope this helps, Mark ----- Original Message ---- From: PAULO RAFAEL PADILLA VELAZQUEZ <rafaelpadilla@grupomayan.com> To: ids@iiug.org Sent: Friday, February 8, 2008 5:33:12 PM Subject: Re: Informix thread working with a index [11263] Yes Mark You're right. My instance is default configured. Where can I read about tuning this parameters? Can you give an little feedback about that changes that you suggest? I looked at on-line manuals and I only found basic information. Additionally information for onmode -Crangesize 100 there isn't available. That's why I'm asking for more. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Thank you Mark I thinks it's very helpful. why aren't this parameters documented in version 9.40? And a last question: How many btscanner threads are recommended per table or cpu? thanks for your help again.

Hi Paulo, Honestly I think it, lack of documentation, was because we didn't originally have an ONCONFIG parameter for them. I would have to check as to where it was added, but I believe it was aroun 9.40.xC6 that we had it added. I would at this juncture recommend that the btscanner thread be around (CPUVP -1) until you get to 10.00.xC7 or 11.10.xC1, at which point you should probabbly have them tied to your AIO/KAIO VP's, since ultimately that is likely to be your bottleneck. ----- Original Message ---- From: PAULO RAFAEL PADILLA VELAZQUEZ <rafaelpadilla@grupomayan.com> To: ids@iiug.org Sent: Tuesday, February 12, 2008 2:23:43 PM Subject: Re: Informix thread working with a index [11286] Thank you Mark I thinks it's very helpful. why aren't this parameters documented in version 9.40? And a last question: How many btscanner threads are recommended per table or cpu? thanks for your help again. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

Hi Mark I've been reviewing your tips. I update to version 9.40FC9 and when it's done is when i get a big scanners activities. You mentioned in your last response that depends on version could exist documentation, so I give it to you above. And other question. Is any aside effect if I move scannner paremeter as you recommended in your other responses? I have a 4 CPU SUN SERVER with IDS 9.40 FC9 Thanks for your help it's really solving my issues.