Performance degraded after detaching indexes / fragmentation
Possibly you have hit the limits of your little SMP (4 CPU?). Or if you got them, jack NUMCPUVPS up and check the parallel processing parameters in the ONCONFIG. Time for more CPUs. Fragmentation splits up stuff so more parallelization can happen. but it won't happen if you don't have any CPUs. >> I used the same ONCONFIG file during all tests (NUMCPUVPS=3; LRU=32; >> BUFFERS=100000; SHMVIRTSIZE=128000; RA_PAGES=32; RA_THRESHOLD=26). -- --------------------------------------------------------- Steven Hauser, hause011@tc.umn.edu Phone: (612)626-7135 Fax: (612)625-6853 ---------------------------------------------------------
Sorry I do not have any answers, but I find it amusing that my reply is
hours ahead of your question.
MOPSOMER@raychem.com wrote in message <77if28$evv$1@news.xmission.com>...
>
> 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.
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.
Hi, I think it's very difficult to find out what's causing the problems in your environment but, maybe the following can help you: + Detached indexes need a longer record than ordinary indexes. The data row pointer uses 4 byte for ordinary indexes and 8 bytes for detached indexes. The "read ahead" formula for index-sequential reads depends on the number of data row pointers in an index leave page. And there will be less data row pointers if you use a detached index. + If you place your detached index on a single disk, all sessions have to use the same disk and you ran into the risk of disk contention. In your previous non-fragmented solution the index was spread across several disks. + Additional CPU times is required because now you have more tablespaces ( one for each fragment ). When data is requested the server must look at several extent-tables and needs more internal locks now. ( You can find it out by yourself. Use the Wait Statistics ( I guess it's enabled in your SAP environment ) and query the "sysseswts" table before you terminate your sessions. It will tell you the amount of time needed for the different internal events. ) I would recommend to look at the output of "sar -d" during your tests. Maybe it's just the disk contention. Best regards, Stefan Weideneder