RE: Performance degraded after detaching indexes / fragmentation

I am going to take a stab at this. You have "Detached" your index pages into a seperate disk from the disks that comprise where your table is stored. When the engine goes to execute your select request, it reads in the index data pages into memory from the drive(s) where you index pages lie. Perhaps when those index pages are read into memory, the datapages that those idx pages point to, are also read in to memory as well (read ahead). Remember that an extent on disk can comprise data pages as well as index pages. This might explain why your Ixda-RA values are large, before the fragmentation. The Informix Admin guide says that Ixda-RA is read-aheads "going from index leaves to data pages." So with your Original Configuration, the engine does not need to perform another read from disk to get those data pages. But with your detached indexs, when the engine performs a Read-Ahead, its only getting index pages, not data pages. So to get the data page that your index page points to, the engine must perform another disk read. This might explain why your sched calls and thread switches and blah blah blah are also higher when you detach you index(s). The less lockrequests , hummm can't think of that one at the moment. > -----Original Message----- > From: MOPSOMER@raychem.com [SMTP:MOPSOMER@raychem.com] > Sent: Wednesday, January 13, 1999 7:16 PM > To: informix-list@iiug.org > Subject: Performance degraded after detaching indexes / fragmentation > > We have to reorganize our biggest SAP R/3 table as soon as possible > because it is close to the 32 GB limit (for non-fragmented tables). > > > I have already done a couple of performance tests to see the effect > of > the two possible solutions, being detaching the indexes of the table > and fragmenting the table (round-robin fragmentation - other types of > > fragmentation are not supported yet by SAP), in a stand-alone system > similar to the production system (database is a copy of production). > > The database is fully mirrorred (using SolStice Disksuite). We use 4 > > GB disks which are divided in 2 raw devices of 2GB each (no > striping). > > I ran a couple of queries (SELECTs) in parallel against the table > before implementing the two possible solutions. I then detached the > indexes and reran the same queries. I also ran the same queries > after > implementing the second solution (round-robin fragmentation). In > both > solutions, the indexes were placed in a separate dbspace on separate > disks. For the second solution, I created 10 dbspaces of 4 GB each > (each dbspace was put on a separate disk, with nothing else on them). > > The results of the tests were disappointing. The queries ran 50% > longer with the "detached indexes" solution and 30% longer with the > "round-robin fragmentation" solution. So it looks like I will need > to > choose between two solutions which will seriously degrade > performance. > Nothing else was running on the server while doing the tests and I > each time recycled the Informix instance before starting the queries. > > I repeated the tests a few times, but the results remained the same. > I used the same ONCONFIG file during all tests (NUMCPUVPS=3; LRU=32; > BUFFERS=100000; SHMVIRTSIZE=128000; RA_PAGES=32; RA_THRESHOLD=26). > The "SET EXPLAIN" output indicates that the optimizer did not choose > another index after implementing each of the two alternatives. > > While looking at the output of onstat commands, I notice that the > number of page reads is higher once the table got fragmented (10% > higher). The read cache percentage dropped from 75% to 62% due to > the > fragmentation. The usercpu and syscpu values in the onstat -p output > > increased significantly due to the fragmentation. However, the two > most significant changes in the onstat -p output are for read-aheads > and lockreqs. While ixda-RA is 1.830.048 after the tests on the > non-fragmented table, it is only 380.552 after the tests on the > fragmented table [ixda-read-aheads are heavily used by SAP]. It > looks > as if the round-robin fragmentation sometimes disables the > read-aheads. The opposite happens with the lockreqs value in onstat > -p : it is very high before the fragmentation (> 5,000,000) and is > very low (30,000) after running the tests on the fragmented table. > > Also the onstat -g glo output is quite different after the tests > against the fragmented table. The "sched calls", "threadswitches" > and > "yield forever" values are three times higher after the > fragmentation. > > QUESTIONS : I have no idea at all what is causing this performance > degradation. Has anyone an idea what is causing this ? Has anyone > an explanation for the big differences in the onstat -p and onstat -g > > glo output ? Has anyone seen a similar performance degradation due to > > detaching the indexes of a table or doing round-robin fragmentation ? > > Kind regards, > Mario Opsomer > > PS : We use Informix 7.24UC3 on Solaris 2.5.1/2.6 for all SAP > systems.