Re: HPL and index creation times

In article <6a5803$qk0@cssun.mathcs.emory.edu>, Martyn Hodgson <martyn.hodgson@eaglestar.co.uk> writes >Hi, > >I=92ve been doing some test loads using the HPL into a fragmented and an = >unfragmented table. Basically, the test platform is as follows: HP K410 = >dual cpu machine, 256Mbyte memory, with the database built on 4 (mirrored= >) EMC disks. > >Two tables are loaded. They have the same columns and each have the same = >3 indexes. One table is unfragmented, the other has 3 fragments. Each fra= >gment is on a different disk, with the indexes detached, residing on the = >fourth disk. Round Robin fragmentation is used. The tables are loaded wit= >h the same 1.4 million rows from a single source file. > >The test loads are run at night when the machine has no other work on it,= > the tables are dropped and recreated (including the indexes) just prior = >to loading. The tables are sized to get all the data in the first extent.= > In the unfragmented table, the first extent includes the indexes. I=92ve= > tried varying each of the parameters in the plconfig file over a range = >of values. The only one that seems to make a difference is CONVERTTHREADS= >. If this is set to 1 then the load times are: > >Fragmented table: 504 seconds to load, 624 seconds to build the indexes >Unfragmented table: 507 second to load, 234 seconds to build the indexes > >If CONVERTTHREADS is set to 2 or more then the load times are: > >Fragmented table: 265 seconds to load, 613 seconds to build the indexes >Unfragmented table: 267 second to load, 239 seconds to build the indexes > >The other variables in plconfig can be varied from the default, but there= > is no significant difference in the times. Now we come to my question: = >why does the fragmented table take 2.5 times longer to build its indexes = >compared with the unfragmented equivalent? > How is DBSPACETEMP setup. Do you have 2 or more dbspaces listed, each dbspace having the TEMP flag set to Y? When the index is built, the index keys must be sorted. Online does this by sorting groups of keys then merge pairs of sorted lists. If the lists get too large then they overflow onto disk (I think the limit is ~5-10Mb). Hence this must be happening. i.e. dbspaces in DBSPACETEMP are being used. If they are all on the same disk then a) Assuming temp space for sorts is allocate in extents as it is for temp tables. I assume it is as this is how the rest of Online disk allocation works. b) When >1 is being used on the same disk, the disk heads end up wasting time seeking back and forth betweent the two locations. SInce more time is spent in disk seeks, performance goes down. Check the above!! What does onstat -d and how does this map to physical disks? Run onstat -z a few seconds after the index builds start then wait 30 seconds and then run onstat -D What are the results for 1 thread and 2 threads? >I notice from using oncheck -pt that the detached indexes each have their= > own space allocated. Does this mean that detached indexes are built one = >at a time, each requiring a scan of the data? If this is so, does the unf= >ragmented table build its indexes via a single pass of the data, collecti= >ng all the key values as it goes? -I=92m guessing here. Whatever the answ= >er, I seem to be paying a very heavy cost for using detached indexes. > >Thanks for any responses, as the HPL seems to be largely ignored by this = >news group. > >Martyn Hodgson > >martyn.hodgson@eaglestar.co.uk > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care