Re: Unused Index Search

The criteria I use to determine if an index is being used is if bfrds it significantly higher than bfwrts then the index is being "read" and not just updated. If bfrds are about the same or bfwrts is higher than bfrds then the index is not being accessed by SELECT, DELETE, or UPDATE statements to find rows in the table nor to check uniqueness during INSERTs. However, be careful of that one index that was added to make the year-end report run in minutes instead of hours since it won't be accessed for another month or so and by then if you've dropped it the CIO will be pissed off! ;-) 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 Thu, Dec 13, 2012 at 3:59 PM, JACQUES RENAUT <jrenaut@us.ibm.com> wrote: > Original post: > > I'm looking at index usage and have a question about onstat -g ppf output. > > It has both a "Reads" and "Buffer Reads" column. I have some indexes with > zero > "Reads" but, in a few cases, many thousands (and even millions) of "Buffer > Reads". > > This doesn't make much sense to me if I assume "Buffer Reads" are a subset > of > "Reads", which I expect to be the sum of disk+buffer)....so I have to > assume > this logic isn't right. > > So, the question is, why are Buffer Reads > 0 when Reads are = 0, and are > these really in use. > > This is 11.70.FC2. > > Response: > > Ok, in a quick test that I did (on 11.50.FC9 was the first server I found > that > I had up already). I found the isrd column appeared to only be bumped if I > used a query that actually used the index (it would bump both isrd and > bfrd's). However, if I just inserted a row into the table, it has to > insert a > item into the index, and in that case, bfrd + bfwrt + iswrt would get > bumped. > So my quick testing would seem to show that if you have an index but the > index > isn't getting used for any queries, isrd wouldn't be getting bumped, but > bfrd's would if you are inserting/updating/deleting rows (well updates that > would update the indexed column). > > All I did to test this was create a table (2 columns) with 1 detached index > (on 1 of the 2 columns). Get the hex partnums, inserted a couple rows into > the > table. Zero'd out my stats with onstat -z. Then used set explain and ran a > query that I believed would use the index I had. After running, checked > onstat> -g ppf. Zero'd stats, then ran a sequential scan (verifying in my set > explain > output that the index was used or not in both the above cases). Checked the > stats. Zero'd stats, and then inserted another row...checked stats. > > Jacques Renaut > IBM Informix Advanced Support > APD Team > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9341185ecb38f04d0c37270

We are several months away from doing anything...so those dreaded year end indexes will get a chance to prove their worth here in the next 6 weeks... Here is an example from my DB that doesn't entirely follow JACQUES above: Table Index Part Num Lock Requests Read Count Write Cnt Re-Write Cnt Deletes Buffer Reads Buffer Writes Seq Scans Disk/Buff Read Ratio acctavg i_acctavg1 0x500451 815,806 - 406,273 - 236 8,857,353 419,873 - 92 acctavg i_acctavg3 0x500453 1,088,955 274,738 406,273 - 236 4,684,066 412,682 - 94 acctavg i_acctavg2 0x500452 1,970,296,634 11,620,998 406,273 - 236 56,839,496 421,036 - 99 acctavg 0x500450 39,644,071 27,275,081 406,273 - 236 47,572,029 424,734 1 100 I am fairly certain the i_acctavg1 index is never used. It got added long ago to create a primary key for old-school replication (that required them). That key isn't used by the app that uses this table at all. Note the write/delete counts are identical for all entries for this table. Art, your comment about buff reads/writes makes sense...until I look at numbers in my DB. I see a lot of indexes that have a small number of buf-reads (<2000) with zero buf-writes and zeros in every column of onstat -g ppf except buff-reads. I was jotting these down as absolutely un-used....assuming the buff-reads are coming from some internal process that really isn't doing anything other than buffer maintenance. I can't justify having buff reads (only) on these tables any other way. The weird part is the Disk/Buff ratio on some of them is 90+...which also doesn't really make sense.

Buffer reads should happen for INSERTs and DELETEs On Thu, Dec 13, 2012 at 10:41 PM, <> wrote: > We are several months away from doing anything...so those dreaded year end > indexes will get a chance to prove their worth here in the next 6 weeks... > > Here is an example from my DB that doesn't entirely follow JACQUES above: > > Table Index Part Num Lock Requests Read Count Write Cnt Re-Write Cnt > Deletes > Buffer Reads Buffer Writes Seq Scans Disk/Buff Read Ratio > acctavg i_acctavg1 0x500451 815,806 - 406,273 - 236 8,857,353 419,873 - 92 > acctavg i_acctavg3 0x500453 1,088,955 274,738 406,273 - 236 4,684,066 > 412,682 > - 94 > acctavg i_acctavg2 0x500452 1,970,296,634 11,620,998 406,273 - 236 > 56,839,496 > 421,036 - 99 > acctavg 0x500450 39,644,071 27,275,081 406,273 - 236 47,572,029 424,734 1 > 100 > > I am fairly certain the i_acctavg1 index is never used. It got added long > ago > to create a primary key for old-school replication (that required them). > That > key isn't used by the app that uses this table at all. Note the > write/delete > counts are identical for all entries for this table. > > Art, your comment about buff reads/writes makes sense...until I look at > numbers in my DB. I see a lot of indexes that have a small number of > buf-reads > (<2000) with zero buf-writes and zeros in every column of onstat -g ppf > except > buff-reads. I was jotting these down as absolutely un-used....assuming the > buff-reads are coming from some internal process that really isn't doing > anything other than buffer maintenance. I can't justify having buff reads > (only) on these tables any other way. The weird part is the Disk/Buff > ratio on > some of them is 90+...which also doesn't really make sense. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300fac89e4572a04d0c3cf8d