RE: Performance degraded after detaching indexes / fragmentation

Two things come to mind. First; You may consider striping your disk array. This usually helps with I/O performance, but I don't know if it will make as dramatic of a change as you seem to be needing. Second; Did you update statistics after you made the changes? This may help considerably if it needs to be done. Oh, and I have a technical white paper from SUN called 'Tuning Informix for OLTP Workloads'. It was written by a SUN engineer named Denis Sheahan and has a tremendous amount of very good information regarding Informix on Solaris. You may try to get a copy from your SUN rep, I don't know if it is available on their web site or not. Hope this helps. > -----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.

In article <77l3ep$bbs$1@news.xmission.com>, Russ_Evans@doh.state.fl.us writes > > >Hope this helps. > >> -----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 Correct, check the FAQ at www.smooth1.demon.co.uk Section 6.31 Do detached indexes take up more space?.... hence once you detach the index you will get more page reads. Are the indexes detached onto totally separate disks from the data? What are the queries and how is the data spread across the disks? We need to know the onstat -d output, table schemas and fragmentation expressions for the tables... >> the >> fragmentation. The usercpu and syscpu values in the onstat -p output >> >> increased significantly due to the fragmentation. However, the two True since online probably creates more than one thread and also needs to combine the results from each fragment. You only really get performance improvement from a) fragment elimination b) accessing fragments in parallel i.e. PDQPRIORITY >0 I would try the tests using fragmentation with PDQPRIORITY=1 (parallel scans). >> 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. >> Probably since more than one thread is active. > >> 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 ? >> What does the sqexplain.out say for each version. Are you using PDQ to scan the fragments in parallel? That is where the performance increase occurs. Is each fragment on a separate physical disk to avoid disk I/O contention? >> Kind regards, >> Mario Opsomer >> >> PS : We use Informix 7.24UC3 on Solaris 2.5.1/2.6 for all SAP >> systems. I would upgrade to 7.30.UC5-1 if possible. -- David Williams