Re: Slow index builds

Also sprach newc0571@sable.ox.ac.uk (Stephen Walmsley) : :1.31 million of the 6.69 million values in bar1 are unique :2.48 " " " " " " bar2 " " If the key values are distributed evenly (i.e you have ~5 rows for every key in bar1, ~3 for bar2) then dups should not be a big problem. On the other hand, if you have most keys with 1, 2, or3 rows, and some with hundreds or thousands, then it will be a big problem. If you can make the key more unique (by adding another column, for example) that could speed things up considerably. :So, perhaps it is the extra length of bar2 which is slowing down the :duplicate list-build drastically. Does Online handle CHAR(8) specially? Shoot, I don't recall the length of bar2. All other things being equal, it should be approximately proportional, i.e. an index on a 12 char columns should not take more than 50% longer than an 8 char column, if that much. There is nothing magic about a char(8). :Or is it because I'm running out of efficient tempspace when building the :index for the larger column which isn't happening for the smaller one? What :does 4.10 use as tempspace when building indices if it needs any? DBTEMP :is pointing to /var/tmp which has only half a gig free. As far as I can recall, 4.10 does no sorting prior to the index build, so no temp space should be needed at all. Reaching back into one of those cobweb-covered corners of my mind, I recall we used to tune OnLine special for big index builds during benchmarks. Here's what we used to do: - Set checkpoint interval to be huge. You want to avoid any checkpoints during the index build. Physical log size could be a limiting factor, as that will drive checkpoints too. - Turn logging off. - Max out BUFFERS; set them as high as you can without causing Unix to page. - Set LRU_MAX_DIRTY to 100. Let fg writes occur. Now before everyone jumps in and tells us how bad fg writes are, understand the following: when you are building an index, you don't care about fg writes. You want to avoid locking up LRUs as during LRU writes, and locking up the system during checkpoints. WIth LRU queues, the liklihood that a flushed page will be needed again any time soon is low, and it will be the LRU buffer that gets flushed by the fg write. Believe me, a lot of testing and benchmarking proved this as the best strategy for building indexes. - When the index was built, set everything back the way it was. Note that all this applied to 4.10 and family only. With 5.X, it is a whole different story. With 7.X, it is different yet again. : :I know 4.10 is rather long in the tooth and there are lots of reasons (this :ineffient way of building indices now added to them) why I plan to upgrade :as soon as possible. I.e. as soon as Informix gets its act together and :releases a Linux port. Don't hold your breath. Dave Dave (posting from home, speaking as a representative of nobody but himself) ********** "Everybody plays the fool, sometimes."