Help on index root node contentions

Hello, my friends! (Informix 11.70.FC2 on a linux_64 machine) Following manual suggestions, I´ve checked two indexes with root node contention, ok.... My onstat -g spi output (ordered) is: 5168 1250608 241.99 fast mutex, 7:bf[229275] 0x6014a0 0x12cee8000 5002 498279 99.62 fast mutex, 7:bf[226648] 0x6014a0 0x12a5dc000 4668 1078878 231.12 fast mutex, 7:bf[175267] 0x6014a0 0xf8308000 ....... ---------------------- As suggested, my query indicates my table is: tabname my_table1 (expression) 0x006014A0 But how can I realize what index is really causing the root contention? The one with the biggest levels, on that table?? (supposing my table has 3 indexes, I mean...) I must identify it, then I´ll test changing it from B-tree to FOT index... Thanks a lot.

If the partnum's displayed by onstat -g spi are pointing to a table and not to an index, then these are spin locks waiting on table data buffer contention not index root node contention. The only exception would be if the indexes on that table are legacy indexes created "attached" by an early Informix version 7.xx engine or which were created explicitly IN TABLE (running dbschema -ss -d <database> -t <table> will tell you if that is the case). So, unless there are attached indexes on this table, then using Forest of Trees indexes on this table will not help the contention. This kind of contention is best dealt with be either or both of: 1) Increase the number of LRU queues and/or 2) Increase the number of buffers. What do the Bufwaits Ratio (BR) and Buffer Turnover Rate (BTR) tell you is happening? Those two metrics will tell you which is the problem (or if both are) and the magnitude of those metrics will give you a guestimate of how big a fix is needed (ie how many more LRU queues or buffers to add). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 28, 2011 at 1:44 PM, ALEXANDRE MARINI <amarini@fazenda.ms.gov.br > wrote: > Hello, my friends! > (Informix 11.70.FC2 on a linux_64 machine) > > Following manual suggestions, I´ve checked two indexes with root node > contention, ok.... > > My onstat -g spi output (ordered) is: > 5168 1250608 241.99 fast mutex, 7:bf[229275] 0x6014a0 0x12cee8000 > 5002 498279 99.62 fast mutex, 7:bf[226648] 0x6014a0 0x12a5dc000 > 4668 1078878 231.12 fast mutex, 7:bf[175267] 0x6014a0 0xf8308000 > ........ > ---------------------- > As suggested, my query indicates my table is: > tabname my_table1 > (expression) 0x006014A0 > > But how can I realize what index is really causing the root contention? The > one with the biggest levels, on that table?? (supposing my table has 3 > indexes, I mean...) > I must identify it, then I´ll test changing it from B-tree to FOT index... > > Thanks a lot. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071d066b7257f04a6ca0f60

Thanks, Art! That really indicated me the table data buffer contention, since defragment was running here on some tables. Thanks a lot. Em 28/06/2011 14:44, Art Kagel escreveu: > If the partnum's displayed by onstat -g spi are pointing to a table and not > to an index, then these are spin locks waiting on table data buffer > contention not index root node contention. The only exception would be if > the indexes on that table are legacy indexes created "attached" by an early > Informix version 7.xx engine or which were created explicitly IN TABLE > (running dbschema -ss -d<database> -t<table> will tell you if that is the > case). So, unless there are attached indexes on this table, then using > Forest of Trees indexes on this table will not help the contention. > > This kind of contention is best dealt with be either or both of: 1) > Increase the number of LRU queues and/or 2) Increase the number of buffers. > What do the Bufwaits Ratio (BR) and Buffer Turnover Rate (BTR) tell you is > happening? Those two metrics will tell you which is the problem (or if both > are) and the magnitude of those metrics will give you a guestimate of how > big a fix is needed (ie how many more LRU queues or buffers to add). > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Tue, Jun 28, 2011 at 1:44 PM, ALEXANDRE MARINI<amarini@fazenda.ms.gov.br >> wrote: >> Hello, my friends! >> (Informix 11.70.FC2 on a linux_64 machine) >> >> Following manual suggestions, I´ve checked two indexes with root node >> contention, ok.... >> >> My onstat -g spi output (ordered) is: >> 5168 1250608 241.99 fast mutex, 7:bf[229275] 0x6014a0 0x12cee8000 >> 5002 498279 99.62 fast mutex, 7:bf[226648] 0x6014a0 0x12a5dc000 >> 4668 1078878 231.12 fast mutex, 7:bf[175267] 0x6014a0 0xf8308000 >> ........ >> ---------------------- >> As suggested, my query indicates my table is: >> tabname my_table1 >> (expression) 0x006014A0 >> >> But how can I realize what index is really causing the root contention? The >> one with the biggest levels, on that table?? (supposing my table has 3 >> indexes, I mean...) >> I must identify it, then I´ll test changing it from B-tree to FOT index... >> >> Thanks a lot. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --20cf3071d066b7257f04a6ca0f60 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 / V11.70 IBM Information Management Informix Technical Professional v3